0

I have a DataFrame containing three columns of interest. I'm trying to fill in values in one column, based on the other two columns of information.

The equivalent in Excel would be to concatenate "Country" and "Service" into another column on the lookup table, then do a vlookup on these columns concatenated on the live data. Translating this into Python/Pandas is proving tricky

From looking up other similar answers (the only ones I've found only pertain to single-column lookups) using .apply and defining a function seems to be where I need to go. I'm struggling to interpret how this works, though. Or would it make more sense to stick the look-up table into a multi-level dictionary of some sort?

Initial Dataframe:

    Price   Country Service
0   0   GB  A
1   0   FR  A
2   0   FR  A
3   0   GB  B
4   0   GB  B
5   0   FR  B
6   0   GB  C
7   0   GB  C

Lookup:

Country Service Price
GB  A   0.1
FR  A   0.2
GB  B   0.8
FR  B   1.2
GB  C   0.5
FR  C   0.2

Resulting DataFrame:

    Price   Country Service
0   0.1 GB  A
1   0.2 FR  A
2   0.2 FR  A
3   0.8 GB  B
4   0.8 GB  B
5   1.2 FR  B
6   0.5 GB  C
7   0.5 GB  C
jbentley
  • 163
  • 4
  • 13
  • 3
    You want a left merge `df.merge(lookup, on=['Country', 'Service'], how='left')` – yatu Mar 29 '19 at 13:31
  • Ah, amazing. Thank you! Like a join in SQL. I was completely missing the "merge" keyword here. Seeing as the answer effectively exists elsewhere - do I need to edit my question or mark it as "answered"? – jbentley Mar 29 '19 at 13:35
  • No, its fine, having set it as a dupe is enough. You're welcome :) – yatu Mar 29 '19 at 13:36

0 Answers0