appending monthly data to dataframe between two dates (multiple entries)

Question

I have two dataframes: one containing replenishment orders for some products, and one containing sales data for the same products by month over multiple years. I have only included the entries for one specific product here. I have already used groupby to calculate the average sales per month per product from the sales data (grouper):

grouper = sales.groupby(['Country Code', 'Product', 'Product Description', 'Month'])['Sales Quantity [QTY]'].mean()

Returns:

index   Country Code   Product  Product Description   Month   Sales Quantity [QTY]
1       Belgium        BE3194   GEL DOUCHE 500ML      1       3.000000
2       Belgium        BE3194   GEL DOUCHE 500ML      2       1.750000
3       Belgium        BE3194   GEL DOUCHE 500ML      3       2.333333
4       Belgium        BE3194   GEL DOUCHE 500ML      4       2.000000
5       Belgium        BE3194   GEL DOUCHE 500ML      5       2.000000
6       Belgium        BE3194   GEL DOUCHE 500ML      6       1.500000
7       Belgium        BE3194   GEL DOUCHE 500ML      7       5.000000
8       Belgium        BE3194   GEL DOUCHE 500ML      8       1.750000
9       Belgium        BE3194   GEL DOUCHE 500ML      9       1.500000
10      Belgium        BE3194   GEL DOUCHE 500ML      10      1.500000
11      Belgium        BE3194   GEL DOUCHE 500ML      11      5.500000
12      Belgium        BE3194   GEL DOUCHE 500ML      12      1.500000

Then I have a second dataframe, products, containing all replenishment orders.

    Product   Date          Quantity
0   BE3194    2020-09-01    600
1   BE3194    2021-06-01    400

I would like to append all monthly sales from grouper to products for the months between the first and last date in products. The output should look something like this:

    Product   Date          Quantity
0   BE3194    2020-09-01    60
1   BE3194    2021-03-01    40
2   BE3194    2020-09-01    -1.5
3   BE3194    2020-10-01    -1.5
4   BE3194    2020-11-01    -5.5
5   BE3194    2020-12-01    -1.5
6   BE3194    2021-01-01    -3
7   BE3194    2021-02-01    -1.75
8   BE3194    2021-03-01    -2.33

My first idea was using a for loop combined with pd.date_range, something like this:

for entry in pd.date_range(product['Date'].min(),product['Date'].max(), freq='MS').tolist():
          products.append(grouper[(grouper['Product'] == products['Product']) & 
          (grouper['Month'] == entry.dt.month)])

But I haven't managed to make it work so far. How can I best achieve this?

user_10035985 · Answer

Try:

# if products["Date"] isn't already converted, convert it:
products["Date"] = pd.to_datetime(products["Date"])

min_date = products["Date"].min()
max_date = products["Date"].max()

tmp = pd.DataFrame({"Date": pd.date_range(min_date, max_date, freq="MS")})
tmp["Month"] = tmp.Date.dt.month
tmp = tmp[:12]  # <--- there are only 12 Months max in grouper df

x = pd.merge(tmp, grouper, on="Month")
x = x[x["Date"] <= max_date]
x["Quantity"] = x["Sales Quantity [QTY]"] * -1

out = pd.concat([products, x[products.columns]])
print(out)

Prints:

  Product       Date    Quantity
0  BE3194 2020-09-01  600.000000
1  BE3194 2021-06-01  400.000000
0  BE3194 2020-09-01   -1.500000
1  BE3194 2020-10-01   -1.500000
2  BE3194 2020-11-01   -5.500000
3  BE3194 2020-12-01   -1.500000
4  BE3194 2021-01-01   -3.000000
5  BE3194 2021-02-01   -1.750000
6  BE3194 2021-03-01   -2.333333
7  BE3194 2021-04-01   -2.000000
8  BE3194 2021-05-01   -2.000000
9  BE3194 2021-06-01   -1.500000

Note: The first two values in your output df should be 600 and 400 respectively. Also, the end date should be 2021-06-01 not 2021-03-01

EDIT: For multiple products:

# if products["Date"] isn't already converted, convert it:
products["Date"] = pd.to_datetime(products["Date"])


def fn(grouper):
    p = products[products.Product == grouper.name]

    min_date = p["Date"].min()
    max_date = p["Date"].max()

    tmp = pd.DataFrame({"Date": pd.date_range(min_date, max_date, freq="MS")})
    tmp["Month"] = tmp.Date.dt.month

    tmp = pd.merge(tmp[:12], grouper, on="Month")
    tmp = tmp[tmp["Date"] <= max_date]
    tmp["Quantity"] = tmp["Sales Quantity [QTY]"] * -1

    return pd.concat([p, tmp[p.columns]])


out = grouper.groupby("Product").apply(fn)
print(out)

Prints:

          Product       Date    Quantity
Product                                 
BE3194  0  BE3194 2020-09-01  600.000000
        1  BE3194 2021-06-01  400.000000
        0  BE3194 2020-09-01   -1.500000
        1  BE3194 2020-10-01   -1.500000
        2  BE3194 2020-11-01   -5.500000
        3  BE3194 2020-12-01   -1.500000
        4  BE3194 2021-01-01   -3.000000
        5  BE3194 2021-02-01   -1.750000
        6  BE3194 2021-03-01   -2.333333
        7  BE3194 2021-04-01   -2.000000
        8  BE3194 2021-05-01   -2.000000
        9  BE3194 2021-06-01   -1.500000
XXXXXX  2  XXXXXX 2020-05-01  600.000000
        3  XXXXXX 2021-02-01  400.000000
        0  XXXXXX 2020-05-01   -2.000000
        1  XXXXXX 2020-06-01   -1.500000
        2  XXXXXX 2020-07-01   -5.000000
        3  XXXXXX 2020-08-01   -1.750000
        4  XXXXXX 2020-09-01   -1.500000
        5  XXXXXX 2020-10-01   -1.500000
        6  XXXXXX 2020-11-01   -5.500000
        7  XXXXXX 2020-12-01   -1.500000
        8  XXXXXX 2021-01-01   -3.000000
        9  XXXXXX 2021-02-01   -1.750000

Dataframes used:

  Product       Date  Quantity
0  BE3194 2020-09-01       600
1  BE3194 2021-06-01       400
2  XXXXXX 2020-05-01       600
3  XXXXXX 2021-02-01       400


   Country Code Product Product Description  Month  Sales Quantity [QTY]
0       Belgium  BE3194    GEL DOUCHE 500ML      1              3.000000
1       Belgium  BE3194    GEL DOUCHE 500ML      2              1.750000
2       Belgium  BE3194    GEL DOUCHE 500ML      3              2.333333
3       Belgium  BE3194    GEL DOUCHE 500ML      4              2.000000
4       Belgium  BE3194    GEL DOUCHE 500ML      5              2.000000
5       Belgium  BE3194    GEL DOUCHE 500ML      6              1.500000
6       Belgium  BE3194    GEL DOUCHE 500ML      7              5.000000
7       Belgium  BE3194    GEL DOUCHE 500ML      8              1.750000
8       Belgium  BE3194    GEL DOUCHE 500ML      9              1.500000
9       Belgium  BE3194    GEL DOUCHE 500ML     10              1.500000
10      Belgium  BE3194    GEL DOUCHE 500ML     11              5.500000
11      Belgium  BE3194    GEL DOUCHE 500ML     12              1.500000
12      Belgium  XXXXXX    GEL DOUCHE 500ML      1              3.000000
13      Belgium  XXXXXX    GEL DOUCHE 500ML      2              1.750000
14      Belgium  XXXXXX    GEL DOUCHE 500ML      3              2.333333
15      Belgium  XXXXXX    GEL DOUCHE 500ML      4              2.000000
16      Belgium  XXXXXX    GEL DOUCHE 500ML      5              2.000000
17      Belgium  XXXXXX    GEL DOUCHE 500ML      6              1.500000
18      Belgium  XXXXXX    GEL DOUCHE 500ML      7              5.000000
19      Belgium  XXXXXX    GEL DOUCHE 500ML      8              1.750000
20      Belgium  XXXXXX    GEL DOUCHE 500ML      9              1.500000
21      Belgium  XXXXXX    GEL DOUCHE 500ML     10              1.500000
22      Belgium  XXXXXX    GEL DOUCHE 500ML     11              5.500000
23      Belgium  XXXXXX    GEL DOUCHE 500ML     12              1.500000

appending monthly data to dataframe between two dates (multiple entries)

1 Answers