CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
Denver Dang
Denver Dang

Reputation: 2615

Add column with certain value when doing groupby

I have a DataFrame that looks something like:

df:

date                          price     bool
---------------------------------------------
2022-01-03 22:00:00+01:00     109.65    False
2022-01-03 22:00:00+01:00      80.00    False
2022-01-03 22:00:00+01:00      65.79    True
2022-01-03 22:00:00+01:00      50.00    True
2022-01-03 23:00:00+01:00      47.00    False
2022-01-03 23:00:00+01:00      39.95    True
2022-01-03 23:00:00+01:00      39.47    False
2022-01-03 23:00:00+01:00      29.96    False
2022-01-03 23:00:00+01:00      22.47    True

If I do a df.groupby("date") my output will be 2 groupby objects separated by date. This is fine. But what I would like is to add a new column to both of these with the max price where bool == True for the entire column. Hence, the resulting data frames would become:

df_groupby_object1:

date                          price     bool      max_price
-----------------------------------------------------------
2022-01-03 22:00:00+01:00     109.65    False      65.79
2022-01-03 22:00:00+01:00      80.00    False      65.79
2022-01-03 22:00:00+01:00      65.79    True       65.79
2022-01-03 22:00:00+01:00      50.00    True       65.79

df_groupby_object2:

date                           price     bool      max_price
-----------------------------------------------------------
2022-01-03 23:00:00+01:00      47.00    False      39.95
2022-01-03 23:00:00+01:00      39.95    True       39.95
2022-01-03 23:00:00+01:00      39.47    False      39.95
2022-01-03 23:00:00+01:00      29.96    False      39.95
2022-01-03 23:00:00+01:00      22.47    True       39.95

I could probably just iterate through the groupby objects as create a extra column that way, but I was wondering if this could be done directly in the groupby function ?

Upvotes: 1

Views: 38

Answers (1)

jezrael
jezrael

Reputation: 862661

Use GroupBy.transform for get maximal values only if Trues values in price. If not match price is NaN created by Series.where:

df['max_price'] = df['price'].where(df['bool']).groupby(df['date']).transform('max')

Details:

print (df['price'].where(df['bool']))
0      NaN
1      NaN
2    65.79
3    50.00
4      NaN
5    39.95
6      NaN
7      NaN
8    22.47
Name: price, dtype: float64

Upvotes: 1

Related Questions