Al_Iskander Al_Iskander - 4 months ago 577
Python Question

Excel VLOOKUP equivalent in pandas

I have following data frame:

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 realtity the shift between
B
and
Index
is not always equal).

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

Answer

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
Comments