Merge two dataframe with first record without duplicate row

Viewed 51

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

0 Answers
Related