Home > Blockchain >  Concatenate Two DataFrames Based On DateTime Column
Concatenate Two DataFrames Based On DateTime Column

Time:08-06

I have two dataframes.

First one:

Date         B
2021-12-31  NaN
2022-01-31  500
2022-02-28  540

Second one:

Date         A
2021-12-28  520
2021-12-31  530
2022-01-20  515
2022-01-31  529
2022-02-15  544
2022-02-25  522

I want to concatenate both the dataframes based on Year and Month and the resultant dataframe should look like below

Date         A  B
2021-12-28  520 NaN
2021-12-31  530 NaN
2022-01-20  515 500
2022-01-31  529 500
2022-02-15  544 540
2022-02-25  522 540

CodePudding user response:

You need a left merge on the month period:

df2.merge(df1,
          left_on=pd.to_datetime(df2['Date']).dt.to_period('M'),
          right_on=pd.to_datetime(df1['Date']).dt.to_period('M'),
          suffixes=(None, '_'),
          how='left'
          )

Then drop(columns=['key_0', 'Date_']) if needed.

Output:

     key_0        Date    A       Date_      B
0  2021-12  2021-12-28  520  2021-12-31    NaN
1  2021-12  2021-12-31  530  2021-12-31    NaN
2  2022-01  2022-01-20  515  2022-01-31  500.0
3  2022-01  2022-01-31  529  2022-01-31  500.0
4  2022-02  2022-02-15  544  2022-02-28  540.0
5  2022-02  2022-02-25  522  2022-02-28  540.0
  • Related