Pandas, group dataframe and normalize values in each group

Question

I have a csv file with different groups identified by an ID, something like:

ID,X
aaa,3
aaa,5
aaa,4
bbb,50
bbb,54
bbb,52

I need to:

calculate the mean of x in each group;
divide each value of x by the mean of x for that specific group.

So, in my example above, the mean in the 'aaa' group is 4, while in 'bbb' it's 52. I need to obtain a new dataframe with a third column, where in each row I have the original value of x divided by the group average:

ID,X,x/group_mean
aaa,3,3/4
aaa,5,5/4
aaa,4,4/4
bbb,50,50/52
bbb,54,54/52
bbb,52,52/52

I can group the dataframe and calcualte each group's mean by:

    df_data = pd.read_csv('test.csv', index_col=0)
    df_grouped = df_data.groupby('ID')
    for group_name, group_content in df_grouped:
        mean_x_group = group_content['x'].mean()
        print(f'mean = {mean_x_group}')

but how do I add the third column?

Mayank Porwal · Accepted Answer · 2020-12-21 18:08:03Z

2

Use Groupby.transform:

In [1874]: df['mean']  = df.groupby('ID').transform('mean')

In [1879]: df['newcol'] = df.X.div(df['mean'])

In [1880]: df
Out[1880]: 
    ID   X  mean    newcol
0  aaa   3     4  0.750000
1  aaa   5     4  1.250000
2  aaa   4     4  1.000000
3  bbb  50    52  0.961538
4  bbb  54    52  1.038462
5  bbb  52    52  1.000000

answered Dec 21, 2020 at 18:08

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prayson W. Daniel Over a year ago

It is good to be reminded of .tranform in groupby. I have often just use .agg just to find a way to broadcast the mean to all rows. +Up for that.

Jzbach · Accepted Answer · 2020-12-21 18:13:47Z

1

The idea being in a neat one-liner:

df['new_column'] = df.apply(lambda row: row.X/df.loc[df.ID==row.ID, 'X'].mean(), axis=1)

answered Dec 21, 2020 at 18:13

Jzbach

3685 silver badges19 bronze badges

3 Comments

Mayank Porwal Over a year ago

This is not very neat though!!

Jzbach Over a year ago

It is neat in the sense that it is closer to the spoken language, so to say. "For each row take X and divide it by its mean". Regardless of the neatness or not, I think it's still good to put more approaches to the problem to inspire other ideas for other problems as well.

Mayank Porwal Over a year ago

Sure I agree with that.

Prayson W. Daniel · Accepted Answer · 2020-12-21 18:40:43Z

0

One liner code to do that

# divide X with mean of X group by ID

df['group_mean'] = df.X / df.groupby('ID').transform('mean').X

answered Dec 21, 2020 at 18:40

Prayson W. Daniel

15.8k6 gold badges57 silver badges62 bronze badges

Collectives™ on Stack Overflow

Pandas, group dataframe and normalize values in each group

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related