CODEWITHSUNDEEP
pythonpandas
Kamo
Kamo

Reputation: 13

Pandas: Joining dataframes based on different date periods

I have a problem with joining two dataframes. First one has rows that change day by day, and the other changes quarterly with a specific date that starts the quarterly data. I would like to add the quarterly data to daily data but don't know how could I join them.

First df:

Date Daily_value
01.01.2000 val1
... ...
13.03.2000 val54
14.03.2000 val55
15.03.2000 val56

Second df:

Date Qarterly_value
01.01.2000 quarter1_val
14.03.2000 quarter2_val
02.06.2000 quarter3_val

Output df:

Date Daily_value Qarterly_value
01.01.2000 val1 quarter1_val
... ... ...
13.03.2000 val54 quarter1_val
14.03.2000 val55 quarter2_val
15.03.2000 val56 quarter2_val

So every quarter data is added to dates from the beginning of the quarter to a day before the beggining of next quarter.

Upvotes: 0

Views: 179

Answers (1)

SeaBean
SeaBean

Reputation: 23237

You can merge the 2 dataframes with outer merge using .merge(). Then, forward fill for undefined values of Qarterly_value using ffill(), as follows:

df_out = df1.merge(df2, how='outer')

df_out['Qarterly_value'] = df_out['Qarterly_value'].ffill()

Result:

print(df_out)

         Date Daily_value Qarterly_value
0  01.01.2000        val1   quarter1_val
1  13.03.2000       val54   quarter1_val
2  14.03.2000       val55   quarter2_val
3  15.03.2000       val56   quarter2_val
4  02.06.2000       valxx   quarter3_val

Upvotes: 1

Related Questions