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!
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
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
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