Data frame - adding index with count of values under each column

Question

df1 = pd.DataFrame({'Region': ['E', 'E', 'U', 'E'], 'Id': [1,None,None,None], 'Ids': [1,2,3,4]})
df2 = pd.DataFrame({'Region': ['E', 'U', 'U', 'E'], 'Id': [1,2,3,4], 'Ids': [1,2,3,4]})
x = df1.groupby(['Region']).count()
y = df2.groupby(['Region']).count()


c = pd.concat([x['Id'], y['Id']], axis=1, keys=['Here', 'There'])

I have the table with two rows (like indices, 'E' and 'U') which count the number of E and U for each data frame and concatenate them with different keys: Here and There. Now I want to add another index, let's call it 'Total' and next to it I want get the total number of values under 'Here' and 'There'.

Now:

Region	Here	There
E	1	2
U	0	2

Now I want to add another index, let's call it 'Total' and next to it I want get the total number of values under 'Here' and 'There'.

I want to achieve:

Region	Here	There
E	1	2
U	0	2
Total	1	4

Thank you.

score 1 · Accepted Answer · edited Nov 10 '22 at 10:48

1

You can add it simply by defining it as the sum:

c.loc['Total'] = c.sum()

Output:

        Here  There
Region             
E        1.0    2.0
U        0.0    2.0
Total    1.0    4.0

edited Nov 10 '22 at 10:48

jezrael

822,522
95
1,334
1,252

answered Nov 10 '22 at 10:46

Celius Stingher

17,835
6
23
53

1

@Jezrael Thanks for the quick edit :) - Rustam feel free to accept the answer if this solved the issue. Thanks – Celius Stingher Nov 10 '22 at 10:49

score 1 · Answer 2 · answered Nov 10 '22 at 10:46

1

c.loc['total'] = c.sum(axis=0)

Output:

    Here    There
Region      
E   1   2
U   0   2
total   1   4

answered Nov 10 '22 at 10:46

Will

1,619
5
23

@RustamKarimov if it's good mark it as accepted – Will Nov 10 '22 at 10:59

Data frame - adding index with count of values under each column

2 Answers2