Adding Specific Days In Python Table

I have a dataset (Product_ID,date_time, Sold) which has products sold on various dates. The dates are not consistent and are given for 9 months with random 13 days or more from a m

Solution 1:

You can first convert dates to dtetimes and get days by dt.day:

df['DATE_LOCATION'] = pd.to_datetime(df['DATE_LOCATION'], dayfirst=True)
days = df['DATE_LOCATION'].dt.day

Then binning by cut:

rng = pd.cut(days, bins=[0,3,7,15,31], labels=['1-3', '4-7','8-15', '>=16'])
print (rng)
01-311-324-738-1548-1551-361-374-788-1598-15108-15
Name: DATE_LOCATION, dtype: category
Categories (4, object): [1-3 < 4-7 < 8-15 < >=16]

And aggregate sum by product and binned Series:

df = df.groupby(["PRODUCT_ID",rng])['Sold'].sum()
print (df)
PRODUCT_ID  DATE_LOCATION
0E4234      1-3              9
            4-7              3
            8-15             3
0G2342      1-3              3
            4-7              1
            8-15             7
Name: Sold, dtype: int64

If need also count per years:

df = df.groupby([df['DATE_LOCATION'].dt.year.rename('YEAR'), "PRODUCT_ID",rng])['Sold'].sum()
print (df)

YEAR  PRODUCT_ID  DATE_LOCATION
2016  0E4234      1-3              9
                  4-7              3
                  8-15             3
      0G2342      1-3              3
                  4-7              1
                  8-15             7
Name: Sold, dtype: int64

Solution 2:

Assume your dataframe named df.

df["DATE_LOCATION"] = pd.to_datetime(df.DATE_LOCATION)
df["DAY"] = df.DATE_LOCATION.dt.day

def flag(x):
    if 1<=x<=3:
        return'1-3'elif 4<=x<=7:
        return'4-7'elif 8<=x<=15:
        return'8-15'else:
        return'>16'# maybe you mean '>=16'.df["Days"] = df.DAY.apply(flag)

df.groupby(["PRODUCT_ID","Days"]).Sold.sum()