How to convert a dictionary with list values into dataframe showing the counts of values of dictiona-CodePudding

I have a dictionary like this:

Dictionary:
{1:['A','B','B','C'],
 2:['A','B','C','D','D','E','E','E','E'],
 3:['C','C','C','D','D','D','D']
}

I want to convert this dictionary into a data frame that has keys on the index and its list values on columns and display the count of the list values like this:

DataFrame:
    A  B  C  D  E
1   1  2  1  0  0
2   1  1  1  2  4
3   0  0  3  4  0

Please help me with how I can achieve this data frame!

CodePudding user response：

You can utilize Counter here:

from collections import Counter
import pandas as pd

d = {1:['A','B','B','C'],
 2:['A','B','C','D','D','E','E','E','E'],
 3:['C','C','C','D','D','D','D']
}

count_dict = {k: Counter(v) for k, v in d.items()}

res = pd.DataFrame.from_dict(count_dict, orient='index').fillna(0).astype('int')

print(res)
#   A   B   C   D   E
# 1 1   2   1   0   0
# 2 1   1   1   2   4
# 3 0   0   3   4   0

CodePudding user response：

You can do it like this also:

pd.DataFrame.from_dict(d, orient='index').T.apply(pd.Series.value_counts).T.fillna(0)

Output:

     A    B    C    D    E
1  1.0  2.0  1.0  0.0  0.0
2  1.0  1.0  1.0  2.0  4.0
3  0.0  0.0  3.0  4.0  0.0

CodePudding user response：

Let us try explode with pd.crosstab

s = pd.Series(d).explode()
out = pd.crosstab(s.index,s)
Out[257]: 
col_0  A  B  C  D  E
row_0               
1      1  2  1  0  0
2      1  1  1  2  4
3      0  0  3  4  0