Python: compare strings of a column between two excel sheets and find the matching columns and write to another data frame

user_14326441 0
Viewed 576

I need to read two spreadsheets(lets say SS1 and SS2). Then have to read the description column in SS1 and search for similar description on latest date in SS2. Have to write the output in another spreadsheet with the unique matching descriptions with its latest date and price.

Input and Output samples below for reference: SS1:

S.No Product Product_Description
1 Shirt Monte Carlo Men Shirt
2 Shirt Belmonte Shirt Cotton
3 Shirt US Polo tshirt men
4 Shirt Monte Carlo tshirt
5 Shirt Monte Carlo Men Shirt
6 Suit Louis Philippe wrinkle free
7 Suit Park Avenue
8 Suit Van Heusen
9 Watches Titan Men Wrist Type
10 Watches Casio
11 Watches Titan Women Wrist Type
12 Watches Rolex
13 Watches Casio

SS2:

S.No Product Product_Description Purchase_Date Quantity Price Net Value
1 Watches Casio Jan-19 10 5000 50000
2 Watches Rolex May-20 2 500000 1000000
3 Shirt Monte Carlo tshirt Feb-20 20 2000 40000
4 Suit Raymond Jan-20 50 10000 500000
5 Watches Lois Moinet May-21 3 60000 180000
6 Shirt Peter England Apr-21 40 1800 72000
7 Watches Casio Mar-19 30 5500 165000
8 Shirt Monte Carlo Men Shirt Jun-19 10 3000 30000
9 Shirt Monte Carlo Men Shirt Apr-20 12 3100 37200
10 Watches Rolex Dec-20 4 505000 2020000
11 Suit Louis Philippe wrinkle free suit Jun-21 9 20000 180000
12 Suit Allen Solly Jan-21 12 4000 48000
13 Shirt Monte Carlo tshirt Apr-21 15 2500 37500

Output:

S.No Product Product_Description Purchase_Date Price
1 Shirt Monte Carlo Men Shirt Apr-20 3100
2 Shirt Monte Carlo tshirt Apr-21 2500
3 Suit Louis Philippe wrinkle free suit Jun-12 20000
4 Watches Casio Mar-19 5500
5 Watches Rolex Dec-20 505000
pandas text python comparison string
1 Answers

The following should work:

temp = SS1.merge(SS2, on=['Product', 'Product_Description'])[['S.No_x', 'Product', 'Product_Description', 'Purchase_Date', 'Price']]
    
res = temp.sort_values(['Product_Description','Purchase_Date']).drop_duplicates('Product_Description', keep='last')    
res=res.rename(columns={'S.No_x':'S.No'})
res=res.sort_values('S.No')
res.reset_index(drop=True, inplace=True)
        
print(res)

Output:

   S.No  Product    Product_Description Purchase_Date   Price
0     4    Shirt     Monte Carlo tshirt        Feb-20    2000
1     5    Shirt  Monte Carlo Men Shirt        Jun-19    3000
2    12  Watches                  Rolex        May-20  500000
3    13  Watches                  Casio        Mar-19    5500

If you want the ACTUAL LATEST DATE (not latest in dataset), insert the following line as 2nd in the above code:

temp['Purchase_Date'] = pd.to_datetime(temp['Purchase_Date'], format='%b-%d')
user_82285580
Related