如何创建一个透视表，其中索引和列都是同一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