Home > OS >  python pandas | conditional aggregation- revised question
python pandas | conditional aggregation- revised question

Time:11-19

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)

CodePudding user response:

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
  • Related