Pandas group by and perform calculations on multiple columns

Question

I have a real estate data

Reg  Area  Price
A    20    356
B    30    98
A    50    900

and I want to get

Reg  Area  Price   AvgUnitPrice
A    20    356     17.9
B    30    98      3.26
A    50    900     17.9

For each region get all properties and calculate average price per unit area in that region and create new column with that value

e.g for region A we have

1. Area = 20, Price= 356 => Price per Area = 17.8
2. Area = 50, Price= 900 => Price per Area = 18

so avg price per unit area for region A becomes

(17.8 + 18) / 2 = 17.9

and then this value to all the region A properties

https://stackoverflow.com/questions/13331698/how-to-apply-a-function-to-two-columns-of-pandas-dataframe — mpnm, Jun 10 '20 at 04:47
@DavidDong Thanks for this but it is helpful when i need to find price per are for each property whereas i need to find avg of price per are for all properties in a region. — irtazaakram, Jun 10 '20 at 04:55

score 2 · Answer 1 · answered Jun 10 '20 at 05:08

One more approach

a = (df.groupby('Reg', sort=False)['Price'].sum()/df.groupby('Reg',sort=False)['Area'].sum()).reset_index(name='AuP')
df.merge(a, on= 'Reg',sort=False)

(Output

Reg Area    Price   AuP
A   20        356   17.942857
A   50        900   17.942857
B   30         98   3.266667

score 1 · Accepted Answer · answered Jun 10 '20 at 05:04

Try this:

import pandas as pd

df = pd.DataFrame({'Reg':['A', 'B', 'A'], 'Area': [20, 30, 50], 'Price': [356, 98, 900]})
df['AvgUnitPrice'] = df.Price / df.Area
df['AvgUnitPrice'] = df.groupby('Reg')['AvgUnitPrice'].transform('mean') 
print(df)

Output:

  Reg  Area  Price  AvgUnitPrice
0   A    20    356     17.900000
1   B    30     98      3.266667
2   A    50    900     17.900000

Pandas group by and perform calculations on multiple columns

2 Answers2