CODEWITHSUNDEEP
pythonpandasdataframegroup-bypandas-groupby
Lucas Dresl
Lucas Dresl

Reputation: 1170

Create new column with max value with groupby

From the following dataframe, I am trying to add a new column, with the condition that for every id check the maximium value. Then place the maximium value for each row of every id in the new column.

df

id  value
1   0
1   0
1   0
2   0
2   1
3   1
3   1

Expected result:

id  value new_column
1   0     0
1   0     0
1   0     0
2   0     1
2   1     1
3   1     1
3   1     1

I have tried:

df['new_column'] = df.groupby(['id'])['value'].idxmax()

Or:

df['new_column'] = df.groupby(['id'])['value'].max()

But neither of these give the desired result.

Upvotes: 4

Views: 3037

Answers (1)

jpp
jpp

Reputation: 164713

You need to use transform for this:

df['new_column'] = df.groupby(['id'])['value'].transform('max')

This more succinctly replicates the following:

df['new_column'] = df['id'].map(df.groupby(['id'])['value'].max())

Remember that the result of a groupby operation is a series with index set to grouper column(s).

Since indices are not aligned between your original dataframe and the groupby object, assignment will not happen automatically.

Upvotes: 6

Related Questions