<p>我能够找到一个解决方案,但这绝不是最好的解决方案。我相信它将允许我们开始。我在代码中提供了一些注释。如果你有任何问题,请告诉我。干杯</p>
<pre><code>import pandas as pd
import itertools
import collections
# this is your data
df = pd.DataFrame({"QUESTIONCODE": [1,1,2,3,4,4],
"SUBJETCS": ["English", "French", "English", "Japanese", "English", "Japanese"]})
df["SUBJETCS_"] = df["SUBJETCS"] # I am duplicating the subject column here
# pivoting to get the counts of each subject
dfPivot = df.pivot_table(index="SUBJETCS", columns="SUBJETCS_", values="QUESTIONCODE", aggfunc=["count"], fill_value=0).reset_index()
dfPivot.columns = ["SUBJETCS"] + sorted(df["SUBJETCS"].unique())
x = df.groupby("QUESTIONCODE")["SUBJETCS"].apply(",".join).reset_index() # for each QUESTIONCODE taking its subjects as a DataFrame
dictCombos = dict(zip(x["QUESTIONCODE"].tolist(), [s.split(",") for s in x["SUBJETCS"].tolist()])) # this will map QUESTIONCODE to its subject as a dictionary
list_all_pairs = [] # this will have all possible pair of subjects
for k, v in dictCombos.items():
# v = list(set(v.split(",")))
prm = list(itertools.permutations(v, 2))
list_all_pairs.extend(prm)
dictMap = {c: i for i, c in enumerate(dfPivot.columns[1:])} # just maps each subject to an index
dictCounts = dict(collections.Counter(list_all_pairs)) # dictionary of all pairs to its counts
dictCoords = {} # indexing each subjects i.e. English 0, French 1, ..., this will allow to load as matrix
for pairs, counts in dictCounts.items():
coords = (dictMap[pairs[0]], dictMap[pairs[1]])
dictCoords[coords] = counts
x = dfPivot.iloc[:, 1:].values # saving the content of the pivot into an 2 dimensional array
for coords, counts in dictCoords.items():
x[coords[0], coords[1]] = counts
dfCounts = pd.DataFrame(x, columns=dfPivot.columns[1:]) # loading the content of array into a DataFrame
df = pd.concat([dfPivot.iloc[:, 0], dfCounts], axis=1) # and finally putting it all together
</code></pre>