0

I have the following dataframe:

Date        Operator 1    Competitor      Price          Sales        
1/1/2021      Store1      Store2           15             100
1/1/2021      Store2      Store1           25             110
2/1/2021      Store1      Store2           15             105
2/1/2021      Store2      Store1           25             103
3/1/2021      Store1      Store2           15             120
3/1/2021      Store2      Store1           25             130

My desired output is to create two new columns, of the competitors price and sales for that day:

Date        Operator 1    Competitor      Price          Sales        C.Price   C.Sales  
1/1/2021      Store1      Store2           15             100           25         110
1/1/2021      Store2      Store1           25             110           25         100
2/1/2021      Store1      Store2           15             105           25         103
2/1/2021      Store2      Store1           25             103           25         105
3/1/2021      Store1      Store2           15             120           25         130
3/1/2021      Store2      Store1           25             130           25         120

How can I lookup the Competitor within Operator 1 and return those values? I can do it in excel (via VLOOKUP) but not sure in pandas. Thanks very much!

SOK
  • 1,732
  • 2
  • 15
  • 33

1 Answers1

1

A simple pandas.DataFrame.merge() can do what you want

merged = pd.merge(df, df, left_on=['Date', 'Operator 1'], right_on=['Date', 'Competitor'])

       Date Operator 1_x Competitor_x  Price_x  Sales_x Operator 1_y Competitor_y  Price_y  Sales_y
0  1/1/2021     Store1       Store2       15      100     Store2       Store1       25      110
1  1/1/2021     Store2       Store1       25      110     Store1       Store2       15      100
2  2/1/2021     Store1       Store2       15      105     Store2       Store1       25      103
3  2/1/2021     Store2       Store1       25      103     Store1       Store2       15      105
4  3/1/2021     Store1       Store2       15      120     Store2       Store1       25      130
5  3/1/2021     Store2       Store1       25      130     Store1       Store2       15      120

After that, drop unnecessary columns and rename columns to what you want with

merged.drop(['Operator 1_y', 'Competitor_y'], axis=1, inplace=True)
merged.rename({'Operator 1_x': 'Operator 1', 'Competitor_x': 'Competitor', 'Price_x': 'Price', 'Price_y': 'CPrice', 'Sales_x': 'Sales', 'Sales_y': 'CSales'}, axis=1, inplace=True)
Ynjxsjmh
  • 28,441
  • 6
  • 34
  • 52
  • great - is there a way to specify what columns you want to bring through first? i have a list `cols` of 20-30 columns I want to bring in. Something like `merged = pd.merge(df, df[cols], left_on=['Date', 'Operator 1'], right_on=['Date', 'Competitor'])`? – SOK Apr 05 '21 at 11:25
  • @SOK I don't understand what do you mean by saying 'bring through first'. But `merged = pd.merge(df, df[cols], left_on=['Date', 'Operator 1'], right_on=['Date', 'Competitor'])` looks ok with me. – Ynjxsjmh Apr 05 '21 at 11:28
  • thanks, yeah i just meant specify the columns before the merge – SOK Apr 05 '21 at 11:48