1

I need a total column next to my side index (Center_Name). There's margins function in crosstab, but it is limited to one column only . I have the following code to have multiple columns in a crosstab, but unable to add a total column.

The code I'm using(Deriving data from Excel):

cols=['Gender','QAge_Post']
q2=(nb.melt(id_vars='Center_Name',value_vars=cols)
       .groupby([pd.Grouper(key='Center_Name'),'value'])
       .size()
       .unstack(fill_value=0))
q2

I have 50 rows with columns Center_Name, Gender, QAge_Post. I'm using Center_name as index and the remaining two as columns.

The output:

Center_Name 18 - 25 Years 26 - 35 years Male Female
Delhi 8 5 3 10
Kolkata 2 6 4 4
Lucknow 7 5 5 7
Mumbai 0 11 2 9
Pune 3 3 5 1

I need a total row in the end as well

SeaBean
  • 22,547
  • 3
  • 13
  • 25
Benjamin Roy
  • 37
  • 4

1 Answers1

1

Use DataFrame.insert with select only Male and Female columns for sum and also DataFrame.loc for sum for new row:

df.insert(0, 'Total', df[['Male','Female']].sum(axis=1))
df.loc['Total'] = df.sum()
print (df)
             Total  18 - 25 Years  26 - 35 years  Male  Female
Center_Name                                                   
Delhi           13              8              5     3      10
Kolkata          8              2              6     4       4
Lucknow         12              7              5     5       7
Mumbai          11              0             11     2       9
Pune             6              3              3     5       1
Total           50             20             30    19      31
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252