My Dataframe:
dfd = pd.DataFrame({'A': ['Apple','Apple', 'Apple','Orange','Orange','Orange','Pears','Pears'],
'B': [1,2,9,6,4,3,2,1]
})
A B
0 Apple 1
1 Apple 2
2 Apple 9
3 Orange 6
4 Orange 4
5 Orange 3
6 Pears 2
7 Pears 1
Expected:
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
The Expected dataframe: the new_values contains the minimum value of that group, For Apple the min column B value is 1 so all new values for Apple is 1 and similarly for Orange min value for column B is 3 which is replaced in new_b column.
2nd Expected Output:
Once above Expected output is achieved, I have to create sql statement for each group and write to file:
basically, iterate each row and write sql query:
sql_query= "update test_tbl "
"set id = {0}"
"where id = {1}"
"and A = '{2}' ".format(new_b,old_b,A)
print(sql_query, file=open("output.sql", "a"))
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
6
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']
1