Lookup values from one dataframe to another?

Question

I'm struggling to lookup values from one dataframe to another. Tried concat and merge but looks like I'm not doing it correctly.

My first data frame d1

       SYMBOL SERIES      OPEN     HIGH       LOW     CLOSE      LAST  PREVCLOSE  TOTTRDQTY     TOTTRDVAL    TIMESTAMP  TOTALTRADES          ISIN       DATE TYPE    DEL      action  price_chng  action_mean   del_mean  delvery_cng  action_cng
0    AARTIIND     EQ    922.00    938.3    915.00    916.90    921.55     919.30     624883  5.791385e+08  23-FEB-2022        25225  INE769A01020 2022-02-23   EQ  37.94   24.772369   -0.261752    24.794340  39.331053    -3.666454   -0.088615
1         ABB     EQ   2076.00   2120.0   2076.00   2097.20   2100.25    2067.30     192678  4.052716e+08  23-FEB-2022        25281  INE117A01022 2022-02-23   EQ  53.21    7.621455    1.425710    10.993800  51.864737     2.528215  -30.674972
2  ABBOTINDIA     EQ  16751.10  17069.9  16625.05  17021.25  17000.15   16714.75      27205  4.608862e+08  23-FEB-2022         7932  INE358A01014 2022-02-23   EQ  36.42    3.429778    1.800690     3.207655  38.241579    -5.001590    6.924786
3   ABCAPITAL     EQ    108.90    109.8    107.40    108.10    108.05     108.00    2906398  3.151837e+08  23-FEB-2022        18569  INE674K01013 2022-02-23   EQ  28.83  156.518822    0.092507   192.114799  33.197368   -15.148694  -18.528493
4       ABFRL     EQ    266.95    271.4    263.50    269.10    269.90     263.00    1658245  4.456953e+08  23-FEB-2022        18279  INE647O01011 2022-02-23   EQ  30.42   90.718584    2.266815    95.409679  30.428421    -0.027683   -4.916791

My second data frame df2

DATE        01-FEB-2022  02-FEB-2022  03-FEB-2022  04-FEB-2022  07-FEB-2022  08-FEB-2022  09-FEB-2022  10-FEB-2022  11-FEB-2022  14-FEB-2022  15-FEB-2022  16-FEB-2022  17-FEB-2022  18-FEB-2022  21-FEB-2022  22-FEB-2022  23-FEB-2022  24-FEB-2022  25-FEB-2022  28-FEB-2022
SYMBOL                                                                                                                                                                                                                                                                        
AARTIIND            NaN     0.252908     0.045227    -0.001366     0.524515     0.064772    -0.124052    -0.071692     0.050795     0.007892    -0.054649    -0.026747    -0.013475     0.042236    -0.013278    -0.005767    -0.031640    -0.411145     0.053021     0.033372
ABB                 NaN     0.109300    -0.042351    -0.006318     0.007266     0.074842     0.010067     0.340532     0.013631     0.046455     0.186916     0.218504    -0.009289    -0.043212    -0.094163     0.014111    -0.068646    -0.370518     0.077532    -0.000734
ABBOTINDIA          NaN     0.120245     0.156900     0.032663     0.001146     0.023581     0.264818    -0.057471    -0.044841    -0.037910    -0.026950     0.018044     0.001855     0.019338    -0.038951    -0.092608    -0.144642    -0.534091     0.104530     0.137224
ABCAPITAL           NaN     0.110602     0.029893    -0.017158    -0.015174     0.007787     0.024166     0.004494     0.000959    -0.011973    -0.003716     0.014272     0.014711     0.002679     0.009430    -0.015102    -0.004584    -0.266000     0.019905     0.014001
ABFRL               NaN     0.017650     0.127609     0.142428    -0.023205    -0.000112    -0.034069    -0.006149     0.040276     0.014252     0.001881     0.014909    -0.010664    -0.023097    -0.042108    -0.061119    -0.019154    -0.310147     0.122109     0.020115

I want to create a new column called oi in the first data frame df1, which will be the value looked up based on Symbol & Timestamp of df1 found in df2. For example,

Adanient 28-Feb-22 should return the value from df2 row Adanient and column 28-Feb-22 in df1['oi']

user_15239951 · Answer

Use melt to flatten your second dataframe then merge to the first one on SYMBOL and TIMESTAMP columns:

out = (
  df1.merge(df2.reset_index().melt('SYMBOL', var_name='TIMESTAMP', value_name='oi'), 
            how='left', on=['SYMBOL', 'TIMESTAMP'])
)

Output:

>>> out[['SYMBOL', 'TIMESTAMP', 'oi']]
       SYMBOL    TIMESTAMP        oi
0    AARTIIND  23-FEB-2022 -0.031640
1         ABB  23-FEB-2022 -0.068646
2  ABBOTINDIA  23-FEB-2022 -0.144642
3   ABCAPITAL  23-FEB-2022 -0.004584
4       ABFRL  23-FEB-2022 -0.019154

Lookup values from one dataframe to another?

1 Answers