python groupby with two conditions and calculate the mean

user_8525711 0
Viewed 147

I have one dataframe as below. I want to groupby this dataframe by two conditions (column 'date' and column 'price') and add one column to calculate the mean of "price2"(which has the same date and price). Thanks.

import pandas as pd
import numpy as np
df2 = pd.DataFrame({
    'date': [20130101,20130101, 20130105, 20130105, 20130101, 20130108],
    'price': [25, 25, 23.5, 27, 40, 8],
     'price2':[23,56,45,67,33,2]
})
       date  output  price  price2
0  20130101    39.5   25.0      23
1  20130101    39.5   25.0      56
2  20130105    45.0   23.5      45
3  20130105    67.0   27.0      67
4  20130101    33.0   40.0      33
5  20130108     2.0    8.0       2
group-by python
2 Answers

Try this, using pandas.DataFrame.groupby.transform:

newdf=df2.groupby(['date','price']).transform('mean')

Now:

print(newdf)

Is:

   price2
0    39.5
1    39.5
2    45.0
3    67.0
4    33.0
5     2.0

Now to get extra columns, do:

newdf.columns=['output']
newdf=pd.concat([newdf,df2],axis=1)

Then now:

print(newdf)

Is:

   output      date  price  price2
0    39.5  20130101   25.0      23
1    39.5  20130101   25.0      56
2    45.0  20130105   23.5      45
3    67.0  20130105   27.0      67
4    33.0  20130101   40.0      33
5     2.0  20130108    8.0       2

Then also add:

df2=df2.insert(1, 'output', newdf)

If wanted correct index for columns

Then now:

print(newdf)

Is:

       date  output  price  price2
0  20130101    39.5   25.0      23
1  20130101    39.5   25.0      56
2  20130105    45.0   23.5      45
3  20130105    67.0   27.0      67
4  20130101    33.0   40.0      33
5  20130108     2.0    8.0       2
user_87083640

You can use the groupby function of pandas:

grp = df2.groupby(['date', 'price']).agg('price2':'mean').rename(columns={'price2':'output'})
pd.merge(df2, grp, on=['date', 'price'])
user_80037060
Related