Adding col to pd.df with value looked up from second df

Question

I am looking to add a new column to a df which I look up from a second df (df2). The df:

             code       date  settlement  strike type
0  CBT_21_G2015_S 2015-01-02    1.343750   126.0    C
1  CBT_21_G2015_S 2015-01-02    4.359375   131.5    P
2  CBT_21_G2015_S 2015-01-02   24.671875   102.5    C
3  CBT_21_G2015_S 2015-01-02    0.015625   110.5    P
4  CBT_21_G2015_S 2015-01-02    0.015625   101.0    P
5  CBT_21_G2015_S 2015-01-02    0.015625   140.5    C
6  CBT_21_G2015_S 2015-01-02   10.671875   116.5    C
7  CBT_21_G2015_S 2015-01-02    0.015625   123.5    P
8  CBT_21_F2015_S 2015-01-02    3.875000   131.0    P
9  CBT_21_F2015_S 2015-01-02    0.015625   145.0    C

The second df (df2):

               code expiry_date
id                             
319  CBT_21_F2013_S  2012-12-21
320  CBT_21_F2014_S  2013-12-27
321  CBT_21_F2015_S  2014-12-26
324  CBT_21_G2012_S  2012-01-27
325  CBT_21_G2013_S  2013-01-25
326  CBT_21_G2014_S  2014-01-24
327  CBT_21_G2015_S  2015-01-23
330  CBT_21_H2012_S  2012-02-24
331  CBT_21_H2013_S  2013-02-22
332  CBT_21_H2014_S  2014-02-21

the column to add to df is the 'expiry_date' for the 'code'. To look up the expiry_date: df2.loc[df2.code == df.code].expiry_date

So the desired output should look like this:

             code       date  settlement  strike type     expiry
0  CBT_21_G2015_S 2015-01-02    1.343750   126.0    C 2015-01-23
1  CBT_21_G2015_S 2015-01-02    4.359375   131.5    P 2015-01-23
2  CBT_21_G2015_S 2015-01-02   24.671875   102.5    C 2015-01-23
3  CBT_21_G2015_S 2015-01-02    0.015625   110.5    P 2015-01-23
4  CBT_21_G2015_S 2015-01-02    0.015625   101.0    P 2015-01-23
5  CBT_21_G2015_S 2015-01-02    0.015625   140.5    C 2015-01-23
6  CBT_21_G2015_S 2015-01-02   10.671875   116.5    C 2015-01-23
7  CBT_21_G2015_S 2015-01-02    0.015625   123.5    P 2015-01-23
8  CBT_21_F2015_S 2015-01-02    3.875000   131.0    P 2014-12-26
9  CBT_21_F2015_S 2015-01-02    0.015625   145.0    C 2014-12-26

what is the easiest way to do that?

score 1 · Accepted Answer · answered Aug 05 '18 at 04:02

IIUC, you can use index matching

df = df.set_index('code')
df['expiry'] = df2.set_index('code')['expiry_date']
df.reset_index()

    code            date        settlement  strike  type    expiry
0   CBT_21_G2015_S  2015-01-02  1.343750    126.0   C   2015-01-23
1   CBT_21_G2015_S  2015-01-02  4.359375    131.5   P   2015-01-23
2   CBT_21_G2015_S  2015-01-02  24.671875   102.5   C   2015-01-23
3   CBT_21_G2015_S  2015-01-02  0.015625    110.5   P   2015-01-23
4   CBT_21_G2015_S  2015-01-02  0.015625    101.0   P   2015-01-23
5   CBT_21_G2015_S  2015-01-02  0.015625    140.5   C   2015-01-23
6   CBT_21_G2015_S  2015-01-02  10.671875   116.5   C   2015-01-23
7   CBT_21_G2015_S  2015-01-02  0.015625    123.5   P   2015-01-23
8   CBT_21_F2015_S  2015-01-02  3.875000    131.0   P   2014-12-26
9   CBT_21_F2015_S  2015-01-02  0.015625    145.0   C   2014-12-26

Adding col to pd.df with value looked up from second df

1 Answers1