Excel VLOOKUP equivalent in pandas

Question

I have following dataframe:

               A           B            C
 Index
2001-06-30    100       2001-08-31     (=value of A at date B)
2001-07-31    200       2001-09-30      ...
2001-08-31    300       2001-10-31      ...
2001-09-30    400       2001-11-30      ...

Column B consists of dates from column A shifted forward by some. I would like to generate column C that consists of the values from column A on date B. (preferably in the logic the Excel VLOOKUP formula would do it. I am not looking for simply shift(-2) here because in reality the shift between B and Index is not always equal).

I tried df.loc['B', 'A'] but this most probably too simplistic and produced an error.

jezrael · Accepted Answer · 2016-07-10 12:40:28Z

18

I think you need map by column A:

df['C'] = df.B.map(df.A)
print (df)
              A          B      C
Index                            
2001-06-30  100 2001-08-31  300.0
2001-07-31  200 2001-09-30  400.0
2001-08-31  300 2001-10-31    NaN
2001-09-30  400 2001-11-30    NaN

It is same as:

df['C'] = df.B.map(df.A.to_dict())
print (df)
              A          B      C
Index                            
2001-06-30  100 2001-08-31  300.0
2001-07-31  200 2001-09-30  400.0
2001-08-31  300 2001-10-31    NaN
2001-09-30  400 2001-11-30    NaN

answered Jul 10, 2016 at 12:40

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Al_Iskander Over a year ago

thank you @jezrael, this is working perfectly fine. I wonder why this solution was not easier to find elsewhere.

jezrael Over a year ago

Hard question. Maybe because in excel it has different name as in pandas, but it is same - vlookup vs map.

Veki Over a year ago

This works perfectly if the column to look into is the index. How does this work if the column is not an index? Both the look into (value to lookup) and the look at column (value to fetch) are both separate columns.

jezrael Over a year ago

@Meet Then use df.index instead df.B

Veki Over a year ago

Sorry @jezrael, I guess my question was not clear. I need to look into col2 and get value from col3 from a df1 into the col4 of a df2. There's no index involved in both these dfs. So how to do that? For instance, in above example, say the 'index' of dates was rather a column and not an index. Same code would work in that case?

|

Collectives™ on Stack Overflow

Excel VLOOKUP equivalent in pandas

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related