I have a data set with the rows same name. For example, I would like to do all As in the data (row 0,1,2,3,4) the same price with the last row of A which is 4th row. So all As would be equal to 60. But I would like to this to whole of my data. The same computation for B and C etc. I have lots of data. Is it possible to do this in pandas? I do not want to group them so, I would like to keep all As etc. An example of the data set is below.
Brand Price Color Category
0 A 10 Blue Shoes
1 A 20 Red Shoes
2 A 30 Yellow Shoes
3 A 40 Pink Shoes
4 A 60 Purple Shoes
5 B 100 Red Shoes
6 B 130 Green Shoes
7 B 150 Blue Shoes
8 C 170 Yellow Shoes
9 C 20 Green Shoes
Thank you
Use map
by Series
created by drop_duplicates
with keep='last'
for last values per groups:
mapping = df.drop_duplicates('Brand', keep='last').set_index('Brand')['Price']
df['Price'] = df['Brand'].map(mapping)
print (df)
Brand Price Color Category
0 A 60 Blue Shoes
1 A 60 Red Shoes
2 A 60 Yellow Shoes
3 A 60 Pink Shoes
4 A 60 Purple Shoes
5 B 150 Red Shoes
6 B 150 Green Shoes
7 B 150 Blue Shoes
8 C 20 Yellow Shoes
9 C 20 Green Shoes
If need define groups by multiple columns (e.g. Brand
and Category
) is possible use transform
with last
:
df['Price'] = df.groupby(['Brand', 'Category'])['Price'].transform('last')
print (df)
Brand Price Color Category
0 A 60 Blue Shoes
1 A 60 Red Shoes
2 A 60 Yellow Shoes
3 A 60 Pink Shoes
4 A 60 Purple Shoes
5 B 150 Red Shoes
6 B 150 Green Shoes
7 B 150 Blue Shoes
8 C 20 Yellow Shoes
9 C 20 Green Shoes