Simple pandas dataframe pivot table
I am trying to do a seemingly very simple task. Given the data frame:
daf = pd.DataFrame ({'co': ['g', 'r', 'b', 'r', 'g', 'r', 'b', 'g'], 'sh': [ 'c ",' s', 'R', 'R', 'R', 's',' c ',' r ']})
co sh 0 g c 1 r s 2 b r 3 r r 4 g r 5 r s 6 b c 7 g r
I would like to count the number of records with a unique combination of "co" and "sh" values and output them as a table with rows ['g', 'r', 'b'] and columns [c ", 's', 'r' ]
c s r
g 1 0 2
r 0 1 1
b 1 0 1
Can this be done with pivot_table?
Thank,
+3
source to share
1 answer
This can be made easier with pandas.crosstab
:
>>> pandas.crosstab(d.co, d.sh)
sh c r s
co
b 1 1 0
g 1 2 0
r 0 1 2
You can do it with pivot_table
, but it will give you NaN instead of 0 for missing combos. You need to specify len
as an aggregation function:
>>> d.pivot_table(index='co', columns='sh', aggfunc=len)
sh c r s
co
b 1 1 NaN
g 1 2 NaN
r NaN 1 2
+5
source to share