CODEWITHSUNDEEP
pythonpython-3.xpandas
Ajay Chinni
Ajay Chinni

Reputation: 850

Groupby replace values with there max value pandas

I have this DataFrame

lst = [['AAA',15],['BBB',16],['BBB',22],['AAA',20],['CCC',11],['AAA',10]]
df = pd.DataFrame(lst,columns = ['name','val'])

which looks like this

  name  val
0  AAA   15
1  BBB   16
2  BBB   22
3  AAA   20
4  CCC   11
5  AAA   10

I want this

  name  val
0  AAA   20
1  BBB   22
2  BBB   22
3  AAA   20
4  CCC   11
5  AAA   20

replaced all val with the maximum of there name group

I did this so far

dd = df.groupby('name')['val'].max().to_dict()

which will give me the dictionary of all the max val now i have to replace them using this dictionary.

If i do this after this will replace all the name with val but i want to replace all the val according to there name

df.replace({"name": dd})

Upvotes: 1

Views: 1416

Answers (2)

Patrick Artner
Patrick Artner

Reputation: 51683

If you want/need to go through a dictionary, you could DataFrame.apply() a function to facilitate the changes:

import pandas as pd

lst = [['AAA',15],['BBB',16],['BBB',22],['AAA',20],['CCC',11],['AAA',10]]
df = pd.DataFrame(lst,columns = ['name','val'])    
dd = df.groupby('name')['val'].max().to_dict()

df["val"] = df["name"].apply(lambda d: dd[d])

print (df)

or as pointed out by Ch3steR in the comment

df["val"] = df["name"].map(dd)

looks even smarter. See Series.map().

Output:

  name  val
0  AAA   20
1  BBB   22
2  BBB   22
3  AAA   20
4  CCC   11
5  AAA   20

Upvotes: 4

user8560167
user8560167

Reputation: 

lst = [['AAA',15],['BBB',16],['BBB',22],['AAA',20],['CCC',11],['AAA',10]]
df = pd.DataFrame(lst,columns = ['name','val'])

max = df.groupby('name').max()
df=df.merge(max,on='name')
del df['val_x']

print(df)
  name  val_y
0  AAA     20
1  AAA     20
2  AAA     20
3  BBB     22
4  BBB     22
5  CCC     11

Upvotes: 4

Related Questions