Python Pandas: vlookup equivalent with multiple columns

Question

This question is very similar to an old one, with an important difference: I have a key based on multiple columns. Anyway, like in the linked question, I don't want a merge of 2 dataframes, but an insert of a new column (based on a vlookup equivalent). As a concrete example let's say I have a dataframe df2.dtypes

Fruit    object
Qty       int64
Year      int64

and another dataframe df3.dtypes

Manager    object
Fruit      object
Year        int64

and I assume I can build a key composed by Fruit and Year for both of them. I've tried to do

df2.insert(1, 'Manager', df2.set_index(['Fruit','Year']).map(df2.set_index(['Fruit','Year'])['Manager']))

But I'm getting

AttributeError: 'DataFrame' object has no attribute 'map'

How can I rephrase it (in order to detour the issue)?

score 0 · Answer 1 · 2020-06-14T21:17:20.093

Well, you're very close to the solution, but let me first summarize what you tried to do, till the point it is correct. So, you can indeed (as per your assumption) construct an indexed version of the dataframe df2:

df2indexed = df2.set_index(['Fruit', 'Year'])

and of the other dataframe df3 as well:

df3indexed = df3.set_index(['Fruit', 'Year'])

So far so good, now the only part you were missing is that you can't apply the map directly to a DataFrame, but... you can do that to its index!

insertManager = df2indexed.index.map(df3indexed.Manager)

In conclusion, you will add the above said new column as you prefer:

df2indexed.insert(1,'Manager', insertManager)

Python Pandas: vlookup equivalent with multiple columns

1 Answers1