CODEWITHSUNDEEP
pythonpandasmulti-index
BhishanPoudel
BhishanPoudel

Reputation: 17164

Pandas groupby chaining: rename multi-index column to one row column

I was doing some continuous operations on pandas dataframe where I need to chain the rename operation. The situation is like this:

import numpy as np
import pandas as pd
import seaborn as sns

df = sns.load_dataset('tips')

g = (df.groupby(['sex','time','smoker'])
     .agg({'tip': ['count','sum'],
           'total_bill': ['count','mean']})
     .reset_index()
    )

print(g.head())

This gives:

      sex    time smoker   tip         total_bill           
                         count     sum      count       mean
0    Male   Lunch    Yes    13   36.28         13  17.374615
1    Male   Lunch     No    20   58.83         20  18.486500
2    Male  Dinner    Yes    47  146.79         47  23.642553
3    Male  Dinner     No    77  243.17         77  20.130130
4  Female   Lunch    Yes    10   28.91         10  17.431000

without chaining
I can do it manually in another line:

g.columns = [i[0] + '_' + i[1] if i[1] else i[0] 
             for i in g.columns.ravel()]

It works fine, but I would like to chain this rename column process so that I can chain further other operations.

But I want inside chaining

How to do so?

Required output:

g = (df.groupby(['sex','time','smoker'])
     .agg({'tip': ['count','sum'],
           'total_bill': ['count','mean']})
     .reset_index()
     .rename(something here)
     # or .set_axis(something here)
     # or, .pipe(something here)  I am not sure.
    ) # If i could do this this, i can do further chaining

     sex    time     smoker tip_count tip_sum  total_bill_count total_bill_mean
0    Male   Lunch    Yes    13   36.28         13  17.374615
1    Male   Lunch     No    20   58.83         20  18.486500
2    Male  Dinner    Yes    47  146.79         47  23.642553
3    Male  Dinner     No    77  243.17         77  20.130130
4  Female   Lunch    Yes    10   28.91         10  17.431000

Upvotes: 3

Views: 689

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

You can use pipe to handle this:

import numpy as np
import pandas as pd
import seaborn as sns

df = sns.load_dataset('tips')

g = (df.groupby(['sex','time','smoker'])
     .agg({'tip': ['count','sum'],
           'total_bill': ['count','mean']})
     .reset_index()
     .pipe(lambda x: x.set_axis([f'{a}_{b}' if b == '' else f'{a}' for a,b in x.columns], axis=1, inplace=False))
    )

print(g.head())

Output:

      sex    time smoker  tip_count  tip_sum  total_bill_count  total_bill_mean
0    Male   Lunch    Yes         13    36.28                13        17.374615
1    Male   Lunch     No         20    58.83                20        18.486500
2    Male  Dinner    Yes         47   146.79                47        23.642553
3    Male  Dinner     No         77   243.17                77        20.130130
4  Female   Lunch    Yes         10    28.91                10        17.431000

Note I am using f-string formatting python 3.6+ is required.

Upvotes: 4

Related Questions