Concatenating 2 DataFrames with DateTimeIndex but different lengths-CodePudding

What I am trying to achieve is basically checking which of the month and year of the 1st DataFrame matches that of the 2nd DataFrame, then entering the values respectively. The 2nd DataFrame will never have a month that is not in the 1st DataFrame as it is derived from some computations in the 1st DataFrame.

The following is the 1st DataFrame I have:

         Date  count
0  2010-01-02     14
1  2010-01-12     14
2  2010-01-23     16
3  2010-02-02     19
4  2010-02-13     20
5  2010-02-24     22
6  2010-03-03     28
7  2010-03-13     40
8  2010-03-24     60
9  2010-04-02     36
10 2010-04-14     24
11 2010-04-25     30
12 2010-05-05     34
13 2010-05-15     25
14 2010-05-26     22
15 2010-06-03     24
16 2010-06-14     29
17 2010-06-25     30

I would like to concatenate the following DataFrame with the above

      Date   new
0  2010-01   NaN
1  2010-02   NaN
2  2010-03  17.5
3  2010-04  25.0
4  2010-05  33.0
5  2010-06  27.5

such that the output becomes

         Date  count  new
0  2010-01-02     14  NaN
1  2010-01-12     14  NaN
2  2010-01-23     16  NaN
3  2010-02-02     19  NaN
4  2010-02-13     20  NaN
5  2010-02-24     22  NaN
6  2010-03-03     28 17.5
7  2010-03-13     40 17.5
8  2010-03-24     60 17.5
9  2010-04-02     36 25.0
10 2010-04-14     24 25.0
11 2010-04-25     30 25.0
12 2010-05-05     34 33.0
13 2010-05-15     25 33.0
14 2010-05-26     22 33.0
15 2010-06-03     24 27.5
16 2010-06-14     29 27.5
17 2010-06-25     30 27.5

Is there any clean way to do this? This solution almost works, but requires me to create 2 new columns for the month and day, as far as I know, to join my 2 DataFrames, which I would like to avoid as they are huge and may take too much time to compute.

CodePudding user response：

This will add a new column to your first dataframe.

df1['new'] = df1['Date'].dt.strftime('%Y-%m').map(df2.set_index(df2['Date'].dt.strftime('%Y-%m'))['new'])