Group-specific calculation on Pandas DataFrame

user_4987404 0
Viewed 39

I'm wondering what is the most elegant/pythonic way of subtracting the brand-specific mean price from the price in the following DataFrame.

Put differently, I want to create a second column equal to the original price minus 1200 for Apple products, and equal to the original price minus 700 for Lenovo products.

import pandas as pd
from io import StringIO

csv = StringIO('''product,brand,price
macbook,Apple,1000
macbook air,Apple,1200
macbook pro,Apple,1400
thinkbook,Lenovo,600
thinkpad,Lenovo,800
''')

df = pd.read_csv(csv)

Thanks in advance for your help!

pandas-groupby pandas python
2 Answers

You can subtract the grouped by mean from the price to create a new column called Price_Diff_Mean. Use .transform('mean') to create a series of values of same length to the column price and subtract those values from price:

df['Price_Diff_Mean'] = df['price'] - df.groupby('brand')['price'].transform('mean')
df
Out[6]: 
       product   brand  price  Price_Diff_Mean
0      macbook   Apple   1000             -200
1  macbook air   Apple   1200                0
2  macbook pro   Apple   1400              200
3    thinkbook  Lenovo    600             -100
4     thinkpad  Lenovo    800              100

Alternatively, you can add a column in with .assign , which will give you the same result:

df = df.assign(Price_Diff_Mean = df['price'] - df.groupby('brand')['price'].transform('mean'))
user_63667700

This is a slightly more elegant way, in my opinion:

df['newcolumn'] = df.groupby('brand').transform(lambda x: x - x.mean())
user_49874040
Related