11

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.

Sean Mooney
  • 169
  • 1
  • 1
  • 11
Al_Iskander
  • 971
  • 5
  • 13
  • 27

1 Answers1

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
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • thank you @jezrael, this is working perfectly fine. I wonder why this solution was not easier to find elsewhere. – Al_Iskander Jul 10 '16 at 12:47
  • Hard question. Maybe because in excel it has different name as in pandas, but it is same - `vlookup` vs `map`. – jezrael Jul 10 '16 at 12:50
  • 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. – Meet Apr 13 '21 at 14:12
  • @Meet Then use `df.index` instead `df.B` – jezrael Apr 13 '21 at 15:38
  • 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? – Meet Apr 14 '21 at 05:21
  • @Meet - what is values for merging? some columns? – jezrael Apr 14 '21 at 05:23
  • @Meet - Maybe help [this](https://stackoverflow.com/q/53010406) – jezrael Apr 14 '21 at 05:23
  • @jezrael Sorry, merging won't work as the one df has repeated values for that column while the other df is the master table for that column. Consider this, a master data of roll number and names. While a daily attendance recorded roll number wise in another df. To get names from master df to attendance df, I need to use roll numbers as common entity between the two dfs. – Meet Apr 14 '21 at 05:26
  • `Sorry, merging won't work as the one df has repeated values for that column while the other df is the master table for that column.` - But merging with left join working nice for repeated values. Honestly. I need see data samples and expected ouput, not sure if understand. Because mapping and merge is possible swap for one column join, like mentioned in [this](https://stackoverflow.com/q/53010406) – jezrael Apr 14 '21 at 05:28