CODEWITHSUNDEEP
pythonpandasdataframemerge
svlad
svlad

Reputation: 53

How to merge dataframe between dates

I have one dataframe data contains daily data of sales (DF). I have another dataframe that contains quarterly data (DF1).

This is what the quarterly dataframe looks like DF1.

Date        Computer Sale   In Person Sales   Net Sales
1/29/2021   1                  2              3
4/30/2021   2                  4              6
7/29/2021   3                  6              9
1/29/2022   4                  8              12
5/1/2022    5                  10             15
7/30/2022   6                  12             18

This is what the daily Data frame looks like: DF

Date            Num of people
1 / 30 / 2021    45
1 / 31 / 2021    35
2 / 1 / 2021     25

5 / 1 / 2021     20
5 / 2 / 2021     15

I have columns Computer Sales, In Person Sales, Net Sales in the quarterly dataframe.

How to I merge the columns from above to the daily dataframe so that I can see on the daily dataframe the quarterly data. I want the final result to look like this

Date        Num of people   Computer Sale   In Person Sales  Net Sales
1/30/2021   45               1              2                3
1/31/2021   35               1              2                3
2/1/2021    25               1              2                3

5/1/2021    20               2              4                6
5/2/2021    15               2              4                6

So, for example. I want 1/30/2021 to be the figure that is 1/29/2021 and once the daily data goes past 4/30/2021 then merge the new quarterly Data.

Please let me know if I need to be more specific.

Upvotes: 0

Views: 54

Answers (1)

PaulS
PaulS

Reputation: 25313

A possible solution:

df1['Date'] = pd.to_datetime(df1['Date'])
df2['Date'] = pd.to_datetime(df2['Date'])

pd.merge_asof(df2, df1, on='Date', direction='backward')

Output:

        Date  Num of people  Computer Sale  In Person Sales  Net Sales
0 2021-01-30             45              1                2          3
1 2021-01-31             35              1                2          3
2 2021-02-01             25              1                2          3
3 2021-05-01             20              2                4          6
4 2021-05-02             15              2                4          6

Upvotes: 1

Related Questions