CODEWITHSUNDEEP
pythonpandasdataframe
Ramón J Romero y Vigil
Ramón J Romero y Vigil

Reputation: 17933

Aggregate pandas Series/DataFrame with MultiLevel Index And Insert Result

Given a pandas Series (or DataFrame) with a multi-level index:

name          month          
A             2019-05        8
              2019-06        8
              2019-07        3
              2019-08        4
              2019-09        7
B             2019-06       10
              2019-07        5
              2019-08       23
              2019-09       10
              2019-10       13

How can the data be aggregated on the second level with the result inserted back into the data?

One example aggregation would be sum which would result in:

name          month          
A             sum            30         #added entry for A
              2019-05        8
              2019-06        8
              2019-07        3
              2019-08        4
              2019-09        7
B             sum           61          #added entry for B
              2019-06       10
              2019-07        5
              2019-08       23
              2019-09       10
              2019-10       13

Upvotes: 0

Views: 72

Answers (2)

Quang Hoang
Quang Hoang

Reputation: 150765

You just need to create the new dataframe and concat:

new_df = (df.groupby(level='name')
   .sum()
   .rename(columns={'count':'sum'})
   .stack()
   .to_frame(name='count')
)

pd.concat((new_df,df)).sort_index()

Output:

              count
name               
A    2019-05      8
     2019-06      8
     2019-07      3
     2019-08      4
     2019-09      7
     sum         30
B    2019-06     10
     2019-07      5
     2019-08     23
     2019-09     10
     2019-10     13
     sum         61

Upvotes: 1

BENY
BENY

Reputation: 323306

Here is one way use unstack 

s=df['count'].unstack()
s['sum']=s.sum(1)
s=s.stack()
name  month  
A     2019-05     8.0
      2019-06     8.0
      2019-07     3.0
      2019-08     4.0
      2019-09     7.0
      sum        30.0
B     2019-06    10.0
      2019-07     5.0
      2019-08    23.0
      2019-09    10.0
      2019-10    13.0
      sum        61.0
dtype: float64

Upvotes: 1

Related Questions