Pandas Groupby And Update With Min Value

My Dataframe: dfd = pd.DataFrame({'A': ['Apple','Apple', 'Apple','Orange','Orange','Orange','Pears','Pears'], 'B': [1,2,9,6,4,3,2,1] })

Solution 1:

Use GroupBy.transform for Series with same size as original df:

dfd['new_B'] = dfd.groupby('A')['B'].transform('min')
print (dfd)
        A  B  new_B
0   Apple  1      1
1   Apple  2      1
2   Apple  9      1
3  Orange  6      3
4  Orange  4      3
5  Orange  3      3
6   Pears  2      1
7   Pears  1      1

If order of columns is important use insert and rename:

dfd.insert(1, 'new_B', dfd.groupby('A')['B'].transform('min'))
dfd = dfd.rename(columns={'B':'old_B'})
print (dfd)
        A  new_B  old_B
0   Apple      1      1
1   Apple      1      2
2   Apple      1      9
3  Orange      3      6
4  Orange      3      4
5  Orange      3      3
6   Pears      1      2
7   Pears      1      1

If transform is not possible use here is alternative solution:

#aggregate by min
s = dfd.groupby('A')['B'].min()
print (s)
A
Apple     1
Orange    3
Pears     1
Name: B, dtype: int64

#insert and map
dfd.insert(1, 'new_B', dfd['A'].map(s))
dfd = dfd.rename(columns={'B':'old_B'})
print (dfd)
        A  new_B  old_B
0   Apple      1      1
1   Apple      1      2
2   Apple      1      9
3  Orange      3      6
4  Orange      3      4
5  Orange      3      3
6   Pears      1      2
7   Pears      1      1

Solution 2:

I think below script work for it

import pandas as pd

dfd = pd.DataFrame({'A': ['Apple','Apple', 'Apple','Orange','Orange','Orange','Pears','Pears'],
                    'B': [1,2,9,6,4,3,2,1]
                   })

dfd_1 = dfd.groupby(['A'], as_index=False).agg({'B': 'min'})

dfd = pd.merge(dfd_1, dfd, how='left', left_on=['A'], right_on=['A'])

dfd.columns = ['A', 'new_B','old_B']