[FIXED] Combine rows in pandas with multi-index

Issue

I have a pandas DataFrame and I want to merge the rows with grade E, F, G into a group named 'Others'.

                   loan_amnt
grade   term    
A   36 months   3.813408e+09
    60 months   4.295580e+08
B   36 months   4.002551e+09
    60 months   1.929438e+09
C   36 months   3.149354e+09
    60 months   3.069289e+09
D   36 months   1.369196e+09
    60 months   1.570700e+09
E   36 months   3.551430e+08
    60 months   7.395742e+08
F   36 months   5.330298e+07
    60 months   3.049789e+08
G   36 months   2.120165e+07
    60 months   1.140946e+08

This can be formed using the following code:

pd.Series({('A', ' 36 months'): 3813408350.0,
 ('A', ' 60 months'): 429558050.0,
 ('B', ' 36 months'): 4002550875.0,
 ('B', ' 60 months'): 1929438125.0,
 ('C', ' 36 months'): 3149353700.0,
 ('C', ' 60 months'): 3069288675.0,
 ('D', ' 36 months'): 1369196500.0,
 ('D', ' 60 months'): 1570700325.0,
 ('E', ' 36 months'): 355143050.0,
 ('E', ' 60 months'): 739574225.0,
 ('F', ' 36 months'): 53302975.0,
 ('F', ' 60 months'): 304978875.0,
 ('G', ' 36 months'): 21201650.0,
 ('G', ' 60 months'): 114094550.0})

Is it possible to do this using Pandas?

Solution

Suppose you want to merge E, F, G using mean values:

others = ['E', 'F', 'G']

# sr is your pd.Series
s1 = sr[~sr.index.isin(others, level=0)]
s2 = pd.concat([sr[others].groupby(level=1).mean()], keys=['Others'])

out = pd.concat([s1, s2])

Note: you can replace mean by sum or whatever you want with apply / agg

Output:

>>> out
A        36 months    3.813408e+09
         60 months    4.295580e+08
B        36 months    4.002551e+09
         60 months    1.929438e+09
C        36 months    3.149354e+09
         60 months    3.069289e+09
D        36 months    1.369196e+09
         60 months    1.570700e+09
Others   36 months    1.432159e+08
         60 months    3.862159e+08
dtype: float64

Answered By - Corralien