[FIXED] python pandas | conditional aggregation- revised question

Issue

volume	price	datetime
100	3	2021-09-29 04:00:00-04:00
300	2	2021-09-29 04:30:00-04:00
700	5	2021-09-29 05:30:00-04:00
500	9	2021-09-29 06:00:00-04:00
900	22	2021-09-29 06:30:00-04:00
400	29	2021-09-29 07:00:00-04:00

posted this questionbefore, but got an error may be because i omitted some detail. This is a revised one

be noted that datatype for datetime is datetime64[ns, America/New_York]. and the dataframe i stored in a variable "df"

Can you help me with the below aggregation?

Add the volume before 2021-09-29 06:15:00 and after 2021-09-29 04:15:00 (expected result should be 1500)

Add the volume if price is greater than 20. (Expected result is 1300)

Solution

If your column datetime isn't already in datetime format run the following line:

df['datetime'] = pd.to_datetime(df['datetime'])

Then if you want to store start and ending dates/time as variables (as requested in the comment below), you can make them strings and concatenate them before passing them to the .between function, then sum the volume column:

target_date = '2021-09-29'
target_from = '04:15:00'
target_to = '06:15:00'
start_datetime = f"{target_date} {target_from}"
end_datetime = f"{target_date} {target_to}"
df[df['datetime'].between(start_datetime, end_datetime)].volume.sum()

Output:

1500

Answered By - Derek O