1

I have a df as follows:

dates   values
2020-01-01 00:15:00 87.321
2020-01-01 00:30:00 87.818
2020-01-01 00:45:00 88.514
2020-01-01 01:00:00 89.608
2020-01-01 01:15:00 90.802
2020-01-01 01:30:00 91.896
2020-01-01 01:45:00 92.393
2020-01-01 02:00:00 91.995
2020-01-01 02:15:00 90.504
2020-01-01 02:30:00 88.216
2020-01-01 02:45:00 85.929
2020-01-01 03:00:00 84.238

I want to just keep hourly values when the minute is 00 and the values occurring before it must be added.

Example: For finding the value at 2020-01-01 01:00:00, the values from 2020-01-01 00:15:00 to 2020-01-01 01:00:00 should be added (87.321+87.818+88.514+59.608 = 353.261). Similarly, for finding the value at 2020-01-01 02:00:00, the values from 2020-01-01 01:15:00 to 2020-01-01 02:00:00 should be added (90.802+91.896+92.393+91.995 = 348.887)

Desired output

 dates  values
 2020-01-01 01:00:00    353.261
 2020-01-01 02:00:00    348.887
 2020-01-01 03:00:00    333.67

I used df['dates'].dt.minute.eq(0) to obtain the boolean masking, but I am unable to find a way to add them.

Thanks in advance

some_programmer
  • 3,268
  • 4
  • 24
  • 59
  • This answer: https://stackoverflow.com/a/60460383/509840 deals with 10 ms intervals, but you could easily adopt it to 1 min intervals. – rajah9 Apr 01 '20 at 13:22

1 Answers1

2
hourly = df.set_index('dates') \  # Set the dates as index
           .resample('1H', closed='right', label='right') \  # Resample, so that you have one value for each hour
           .sum()  # Set the sum of values as new value

hourly = hourly.reset_index()  # If you want to have the dates as column again
flurble
  • 1,086
  • 7
  • 21
  • Doing this is giving the wrong answer. I want the values at `00:15, 00:30, 00:45, 01:00` to be added together and be assigned to `2020-01-01 01:00:00` and so on – some_programmer Apr 01 '20 at 13:22
  • 2
    To get intervals as in OP's question, you can add two parameters in `resample`, try with `resample('1H', closed='right', label='right')` – Ben.T Apr 01 '20 at 13:24
  • @cripcate If you can edit your answer according to the one suggested by Ben, I will accept your answer – some_programmer Apr 01 '20 at 13:29