CODEWITHSUNDEEP
pythonpandasmachine-learninggroup-by
Vishal Anand
Vishal Anand

Reputation: 178

Is there any way to ungroup the groupby dataframe with adding an additional column

Suppose we take a pandas dataframe...

    item  MRP     sold
0   A     10       10
1   A     36       4
2   B     32       6
3   A     26       7
4   B     30       9

Then do a groupby('item').mean()

it becomes 

   item  MRP     sold
0   A     24      7
1   B     31      7.5

Is there a way to retain the mean values of MRP, of all the unique items and make another column which will contain those values when ungrouped.

Basically what i want is

    item  MRP     sold  Mean_MRP
0   A     10       10    24
1   A     36       4     24
2   B     32       6     31
3   A     26       7     24
4   B     30       9     31

There are a lot of items, so i need a faster and optimised way to do this

Upvotes: 2

Views: 214

Answers (2)

sammywemmy
sammywemmy

Reputation: 28659

Use the Transform function : 

df = (df
      .assign(Mean_MRP = lambda x:x.groupby('item')['MRP']
                                   .transform('mean')))

df


  item  MRP     sold    Mean_MRP
0   A   10      10       24
1   A   36      4        24
2   B   32      6        31
3   A   26      7        24
4   B   30      9        31

You could also use the pyjanitor module, which makes the code a bit cleaner:

import janitor

df.groupby_agg(by='item',
               agg='mean',
               agg_column_name="MRP",
               new_column_name='Mean_MRP')

Upvotes: 4

U13-Forward
U13-Forward

Reputation: 71560

Try using transform:

df['Mean_MRP'] = df.groupby('item').transform('mean')

Upvotes: 3

Related Questions