python - 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.

Answer 1

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