如何创建一个透视表，其中索引和列都是同一df列的唯一值？

Question

我的df如下所示：

QUESTIONCODE      SUBJETCS
    1            English  
    1            French
    2            English
    3            Japanese
    4            English
    4            Japanese

我想创建一个透视表，其中索引和列都是来自df的我的主题的唯一值，它将由属于每个索引和列所代表的主题组合的问题代码的数量填充。然后，结果将是：

         English      French        Japanese
English     3            1              1
French      1            1              0
Japanese    1            0              2

我已经尝试过使用pandas函数作为groupby、pivot_table和crosstab的一些方法，但仍然无法得到上面显示的结果

谁能帮我一下吗

Answer 1

这就是我在griggy回答的评论中提到的代码

import pandas as pd
import itertools
import collections

# Creating the dataframe
df = pd.DataFrame({"QUESTIONCODE": [1,1,2,3,4,4], 
                   "SUBJETCS": ["English", "French", "English", "Japanese", "English", "Japanese"]})


# Pivoting to get the counts of each subject
dfPivot = pd.pivot_table(df, values='QUESTIONCODE', index='SUBJETCS', columns='SUBJETCS', aggfunc='count', fill_value=0)

# Creating a dataframe with each QUESTIONCODE and its SUBJECTs
x = df.groupby("QUESTIONCODE")["SUBJETCS"].apply(",".join).reset_index() 

# Mapping QUESTIONCODE to its SUBJECTs as a dictionary
dictCombos = dict(zip(x["QUESTIONCODE"].tolist(), [s.split(",") for s in x["SUBJETCS"].tolist()])) 

# Creating a list with all possible pair of SUBJECTs
list_all_pairs = []
for k, v in dictCombos.items():
    prm = list(itertools.permutations(v, 2))
    list_all_pairs.extend(prm)

# Creating a dictionary of all pairs of Materias and its counts
dictCounts = dict(collections.Counter(list_all_pairs)) 

# Filling the dfPivot dataframe with all pairs of Materias and its counts
for pairs, counts in dictCounts.items():
    dfPivot.loc[pairs] = counts

Answer 2

我能够找到一个解决方案，但这绝不是最好的解决方案。我相信它将允许我们开始。我在代码中提供了一些注释。如果你有任何问题，请告诉我。干杯

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