Iterate through range of dates and find the sum in each group

user_15492238 0
Viewed 432

I have a dataframe which looks like this:

    ID    Date        Volume      Sales
0   1   2019-01-01  3.333333    1.333333
1   1   2019-02-01  3.333333    1.333333
2   1   2019-03-01  3.333333    1.333333
3   1   2019-04-01  2.666667    2.000000
4   1   2019-05-01  2.666667    2.000000
5   1   2019-06-01  2.666667    2.000000
6   2   2019-01-01  2.000000    2.666667
7   2   2019-02-01  2.000000    2.666667
8   2   2019-03-01  2.000000    2.666667
9   2   2019-04-01  1.333333    3.333333
10  2   2019-05-01  1.333333    3.333333
11  2   2019-06-01  1.333333    3.333333

I would like to sum up the data (volume and sales) on a set of dates within each group (ID).

From 2019-01-01 to 2019-04-01 AND 2019-02-01 to 2019-05-01

I believe this can be done using for loops, but struggling to get the desired outcome

Note: Question may be phrased poorly. Please comment if you have any additional questions

Excepted output:

ID         Date                 Volume      Sales
1   2019-01-01 to 2019-04-01    12.67        6
1   2019-02-01 to 2019-05-01    12           6.66
2   2019-01-01 to 2019-04-01    7.33         11.33
2   2019-02-01 to 2019-05-01    6.66         12

Having the date column in the output is not necessary

pandas dataframe numpy datetime python
2 Answers
df["group_1"] = (df["Date"] >= pd.Timestamp("2019-01-01")) & (
    df["Date"] <= pd.Timestamp("2019-04-01")
)
df["group_2"] = (df["Date"] >= pd.Timestamp("2019-02-01")) & (
    df["Date"] <= pd.Timestamp("2019-05-01")
)

g1 = df[df["group_1"] == True].groupby("ID")
g2 = df[df["group_2"] == True].groupby("ID")

df = pd.concat(
    [
        g1.agg({"Volume": "sum", "Sales": "sum"}),
        g2.agg({"Volume": "sum", "Sales": "sum"}),
    ]
).sort_index()

print(df)

Prints:

       Volume      Sales
ID                      
1   12.666666   5.999999
1   12.000000   6.666666
2    7.333333  11.333334
2    6.666666  12.000000

If you want Date column:

...

df = pd.concat(
    [
        g1.agg({"Volume": "sum", "Sales": "sum"}).assign(
            Date="2019-01-01 to 2019-04-01"
        ),
        g2.agg({"Volume": "sum", "Sales": "sum"}).assign(
            Date="2019-02-01 to 2019-05-01"
        ),
    ]
).sort_index()

Prints:

       Volume      Sales                      Date
ID                                                
1   12.666666   5.999999  2019-01-01 to 2019-04-01
1   12.000000   6.666666  2019-02-01 to 2019-05-01
2    7.333333  11.333334  2019-01-01 to 2019-04-01
2    6.666666  12.000000  2019-02-01 to 2019-05-01

EDIT: To generalize:

df["Date"] = pd.to_datetime(df["Date"])

# add dates to this group:
groups = [
    [pd.Timestamp("2019-01-01"), pd.Timestamp("2019-04-01")],
    [pd.Timestamp("2019-02-01"), pd.Timestamp("2019-05-01")],
    [pd.Timestamp("2019-03-01"), pd.Timestamp("2019-06-01")],
]

grouped = []
for i, (t1, t2) in enumerate(groups, 1):
    df["group_{}".format(i)] = (df["Date"] >= t1) & (df["Date"] <= t2)
    grouped.append(
        df[df["group_{}".format(i)] == True]
        .groupby("ID")
        .agg({"Volume": "sum", "Sales": "sum"})
        .assign(Date="{} to {}".format(t1.date(), t2.date()))
    )

df = pd.concat(grouped).sort_index()

print(df)

Prints:

       Volume      Sales                      Date
ID                                                
1   12.666666   5.999999  2019-01-01 to 2019-04-01
1   12.000000   6.666666  2019-02-01 to 2019-05-01
1   11.333334   7.333333  2019-03-01 to 2019-06-01
2    7.333333  11.333334  2019-01-01 to 2019-04-01
2    6.666666  12.000000  2019-02-01 to 2019-05-01
2    5.999999  12.666666  2019-03-01 to 2019-06-01
user_100359850

You can set the range of dates as (start, end) tuples in dts list:

dts = [('2019-01-01', '2019-04-01'), ('2019-02-01', '2019-05-01')]

df_out = pd.concat([
    (df
         .loc[df['Date'].between(dt_start, dt_end)]   # filter dates in range
         .groupby('ID', as_index=False).sum()         # calculate sum
         .assign(Date=f'{dt_start} to {dt_end}'))     # set `Dates` values
  for (dt_start, dt_end) in dts], ignore_index=True)[df.columns]

df_out

Output:

   ID                      Date     Volume      Sales
0   1  2019-01-01 to 2019-04-01  12.666666   5.999999
1   2  2019-01-01 to 2019-04-01   7.333333  11.333334
2   1  2019-02-01 to 2019-05-01  12.000000   6.666666
3   2  2019-02-01 to 2019-05-01   6.666666  12.000000
user_67927430
Related