how to find consecutive timestamp and calculate sum in python

Question

I am looking for the sum of sales made by each customer within 24 hours.

For example,

id     timestamp            sales
123   2022-10-01 12:50:55   11
124   2022-10-01 22:50:55   11
123   2022-10-01 13:50:55   11
123   2022-10-02 12:50:55   11
123   2022-10-02 13:50:55   11

Then For ```id = 123``, we select

1. 
id     timestamp            sales
123   2022-10-01 12:50:55   11
123   2022-10-01 13:50:55   11
123   2022-10-02 12:50:55   11

Sum = 11+11+11 = 33

2. 
id     timestamp            sales
123   2022-10-01 13:50:55   11
123   2022-10-02 12:50:55   11
123   2022-10-02 13:50:55   11

Sum = 11+11+11 = 33

3. 
id     timestamp            sales
123   2022-10-02 12:50:55   11
123   2022-10-02 13:50:55   11

Sum = 11+11 = 22

4.
id     timestamp            sales
123   2022-10-02 13:50:55   11

Sum = 11

We get the result for id = 123 is

id     timestamp            sales   sum
123   2022-10-01 12:50:55   11      33
123   2022-10-01 13:50:55   11      33
123   2022-10-02 12:50:55   11      22
123   2022-10-02 13:50:55   11      11

For id = 124, we get 

id     timestamp            sales   sum
124   2022-10-01 22:50:55   11      11

I know cross join could deal with this problem, but this method is time-costing for big datasets.

Is there a better way to achieve this?

Thank you

Answer 1

You can use groupby giving it pd.Groupby(freq="D") and the id column:

df["timestamp"] = pd.to_datetime(df["timestamp"])
df.set_index("timestamp", inplace=True)
newDf = df.groupby([pd.Grouper(freq="D"), "id"]).sum().reset_index()
newDf

The newDf would be:

	timestamp	id	sales
0	2022-10-01 00:00:00	123	22
1	2022-10-01 00:00:00	124	11
2	2022-10-02 00:00:00	123	22

Therfore, by calling newDf[newDf["id"] == 124] you will get:

	timestamp	id	sales
1	2022-10-01 00:00:00	124	11

Note that, the time would not be exactly as you mentioned, since 00:00 usually be considered the beginning of the day, and not any other time.