i have a dataframe contains millions of records like:
ID input Output Price Category
1 19 10 50 A
2 20 57 70 A
3 30 58 55 A
4 40 19 40 B
5 19 17 10 A
6 40 20 70 B
7 11 19 10 B
8 10 20 60 B
and i want a new output like :
IDA inputA OutputA PriceA CategoryA IDB inputB OutputB PriceB CategoryB
1 19 10 40 A 4 40 19 40 B
1' 19 10 10 A 7 11 19 10 B
2 20 57 20 A 6 40 20 70 B
2' 20 57 50 A 8 10 20 50 B
Nan Nan Nan Nan Nan 8' 10 20 10 B
3 30 58 55 A Nan Nan Nan Nan Nan
5 19 17 10 A Nan Nan Nan Nan Nan
i need to iterate records with category A and B ,
if InputA==OutputB then merge row B with the first record with Category A and Check the price A ,
-if price A= price B : merge row A and row B
-if price A> price B : duplicate row A , and priceA = priceA-priceB
-if price A< price B : duplicate row B , and priceB = priceB-priceA
i tried this code :
category = df.groupby('category')
transform_df = []
for index, frame in category:
frame.reset_index(drop=True, inplace=True)
transform_df.append(frame.copy())
A = pd.DataFrame([transformed_df_list[0])
B= pd.DataFrame([transformed_df_list[1])
for i , row in A.iterrows():
for i, row1 in B.iterrows():
if row['input'] == row1['output']:
if row['price'] == row1['price']:
row_df = pd.DataFrame([row1])
output = pd.merge(A ,B, how='left' , left_on =['input'] , right_on =['output'] )
the problem is that the rowB is merged with all rowA InputA==OutputB , not only the first records