Pandas: recreation of pivot_tables using groupby

Question

I have a dataframe like this:

df = pd.DataFrame({'country': ['usa','canada','usa','canada','mexico','usa'],
                   'color':   ['silver','brown','brown','black','silver','black'],
                   'car':     ['honda','honda','nissan','toyota','honda','toyota'],
                   'value': range(60,66)})
      car   color country  value
0   honda  silver     usa     60
1   honda   brown  canada     61
2  nissan   brown     usa     62
3  toyota   black  canada     63
4   honda  silver  mexico     64
5  toyota   black     usa     65

I can pivot by two indices like this:
df.pivot_table(index=['color','car'], columns='country', values='value')\
  .rename_axis(None, axis=1).reset_index()

    color     car  canada  mexico   usa
0   black  toyota    63.0     NaN  65.0
1   brown   honda    61.0     NaN   NaN
2   brown  nissan     NaN     NaN  62.0
3  silver   honda     NaN    64.0  60.0

I was wondering how can we achieve the same result using GROUPBY ?

Help is appreciated.

Similar questions:
Pandas column values to columns?

score 1 · Accepted Answer · answered Mar 28 '19 at 22:30

1

Group the dataframe by color, car and country and find mean of column value. unstack and reset_index.

new_df = df.groupby(['color', 'car', 'country']).value.mean().unstack().reset_index()
new_df.columns.name = None


    color   car     canada  mexico  usa
0   black   toyota  63.0    NaN     65.0
1   brown   honda   61.0    NaN     NaN
2   brown   nissan  NaN     NaN     62.0
3   silver  honda   NaN     64.0    60.0

answered Mar 28 '19 at 22:30

Vaishali

37,545
5
58
86

setting the column name to None is optional – Vaishali Mar 28 '19 at 22:35
1

Thanks a lot. I also added `.rename_axis(None, axis=1)` to make it one-liner. – BhishanPoudel Mar 28 '19 at 22:35

Pandas: recreation of pivot_tables using groupby

1 Answers1