How to convert merge two Dataframes on several conditions?

Question

I want to merge two dataframes based on two conditions.

The first Dataframe looks like this:

    Year    Cusip9      Name               Assets      Debt Sales       Income     SIC-Code LogSales Leverage Asset Turnover    Profit Margin   Revenue Growth
0   2010.0  825690100   SHUTTERSTOCK INC    138630.0    0.0 189380.0    829730.0    7375.0  12.151511   0.0 1.366082    4.381297    NaN
1   2011.0  825690100   SHUTTERSTOCK INC    248550.0    0.0 218640.0    1202710.0   7375.0  12.295182   0.0 0.879662    5.500869    0.154504
2   2012.0  825690100   SHUTTERSTOCK INC    1471140.0   0.0 475430.0    1696160.0   7375.0  13.071975   0.0 0.323171    3.567634    1.174488
3   2013.0  825690100   SHUTTERSTOCK INC    2784880.0   0.0 264790.0    2355150.0   7375.0  12.486692   0.0 0.095081    8.894407    -0.443052
4   2014.0  825690100   SHUTTERSTOCK INC    3837770.0   0.0 220890.0    3279710.0   7375.0  12.305420   0.0 0.057557    14.847707   -0.165792

And another one:

    Cusip9      Issuer                      Filing Date Year
0   521893107   Lear Seating Corp           1994-03-08  1994
1   605288208   Mississippi Chemical Corp   1994-07-14  1994
2   549463107   Lucent Technologies Inc     1996-02-05  1996
3   783755101   Ryerson Tull Inc            1996-05-07  1996
4   74377P203   Providence Journal Co       1996-04-22  1996

I would like two merge this two dataframes based on Cusip9 and Year. So that I will have the financial figures from DF1 in DF2. But I only want the figures from this specific year.

Does anyone knows how to solve this?

BR and thanks in advance.

Try `df1.merge(df, on=['Cusip9','Year'], how='inner')`. If it answers you I will post it as an answer — Youness Saadna, Aug 15 '20 at 15:28

Surya Lohia. · Answer 1 · 2020-08-15T15:37:16.987

0

Firstly change the datatype of the Year column in the first dataframe. It's in float. use:

df = df['Year'].astype('datetime64[ns]')

The following code will merge both the dataframe based on 'Cusip9' and 'Year'.

import pandas as pd
new_df = dataframe_1.merge(dataframe_2,on=['Cusip9','Year'],how='inner')

edited Aug 15 '20 at 15:37

answered Aug 15 '20 at 15:28

Surya Lohia.

446
4
16

Youness Saadna · Accepted Answer · 2020-08-15T15:48:19.800

0

Use pandas merge() to achieve what you want.

Code:

df['Year'] = df['Year'].astype('datetime64[ns]')
df1['Year'] = df1['Year'].astype('datetime64[ns]')

df1.merge(df, on=['Cusip9','Year'], how='inner')

edited Aug 15 '20 at 15:48

answered Aug 15 '20 at 15:31

Youness Saadna

792
2
8
25

How to convert merge two Dataframes on several conditions?

2 Answers2