pandas calculate time series resampling

Question

If I had some random data created on a one hour sample..

import pandas as pd 
import numpy as np 
from numpy.random import randint 

np.random.seed(10)  # added for reproductibility                                                                                                                                                                 

rng = pd.date_range('10/9/2018 00:00', periods=1000, freq='1H') 
df = pd.DataFrame({'Random_Number':randint(1, 100, 1000)}, index=rng)

I can use the groupby to break out each day:

for idx, day in df.groupby(df.index.date):
    print(day)

Now is there a way to calculate the time difference between daily min & max value based on the timestamp in hours? for each day record the daily min & max & time difference?

Actually, that throws an error. You can do: `df.index.to_series().groupby(df.index.date).apply(lambda x: x.max()-x.min())` — Quang Hoang, Feb 14 '20 at 21:25
That is wrong @QuangHoang, it takes the min and max of the date and he want the time difference of the min and max of `Random_Number` — Erfan, Feb 14 '20 at 21:28
I know I am asking alot here, but is it possible to resample per like `np.ptp` but use `np.percentile()` to print daily resampled 2.5 & 97.5 percentiles of the data? — bbartling, Feb 14 '20 at 21:29
ideally im trying to find the time difference in hours between the daily 2.5 & 97.5 percentiles, but daily mix & max works too — bbartling, Feb 14 '20 at 21:30
I know @QuangHoang, but he wants the **time difference** of the min & max of **Random_Number** per group. — Erfan, Feb 14 '20 at 21:31
Ah, didn't get that part, hence the comment. But that can be fixed :-) — Quang Hoang, Feb 14 '20 at 21:32

score 2 · Accepted Answer · edited Feb 14 '20 at 21:45

After some discussion (thanks @Erfan):

(df.Random_Number
   .groupby(df.index.date)
   .agg(['idxmin','idxmax'])
   .diff(axis=1).iloc[:,1]
   .div(pd.to_timedelta('1H'))
)

Output:

2018-10-09    -4.0
2018-10-10    -1.0
2018-10-11    -4.0
2018-10-12    12.0
2018-10-13    21.0
2018-10-14     6.0
2018-10-15    -6.0
2018-10-16   -18.0
2018-10-17    -8.0
2018-10-18     9.0
2018-10-19   -10.0
2018-10-20     3.0
2018-10-21    10.0
2018-10-22     2.0
2018-10-23     9.0
2018-10-24     2.0
2018-10-25     3.0
2018-10-26     2.0
2018-10-27   -22.0
2018-10-28     6.0
2018-10-29    -8.0
2018-10-30    -1.0
2018-10-31   -11.0
2018-11-01    19.0
2018-11-02     7.0
2018-11-03     4.0
2018-11-04    18.0
2018-11-05    -1.0
2018-11-06    15.0
2018-11-07   -14.0
2018-11-08   -16.0
2018-11-09    -2.0
2018-11-10    -7.0
2018-11-11   -14.0
2018-11-12    12.0
2018-11-13   -14.0
2018-11-14     2.0
2018-11-15     2.0
2018-11-16     6.0
2018-11-17    -7.0
2018-11-18     5.0
2018-11-19     9.0
Name: idxmax, dtype: float64

This gives me the resampled 2.5 & 97.5 percentiles, is it possible to grab the time difference? — bbartling, Feb 14 '20 at 21:37
Could you ever help me with this SO post? https://stackoverflow.com/questions/74908952/pandas-calculating-time-deltas-from-index — bbartling, Dec 24 '22 at 17:35

score 0 · Answer 2 · answered Feb 14 '20 at 22:05

Alternatively, should you want to retain all columns with data frame output, consider merging on an aggregated dataset:

# ADJUST FOR DATETIME AND DATE AS COLUMNS
df = (df.reset_index()
        .assign(date = df.index.date)
     )

# AGGREGATION + MERGE ON MIN/MAX + CALCULATION
agg_df =  (df.groupby('date')['Random_Number']
             .agg(["min", "max"])
             .reset_index()
             .merge(df, left_on=['date', 'max'], right_on=['date', 'Random_Number'])               
             .merge(df, left_on=['date', 'min'], right_on=['date', 'Random_Number'], 
                    suffixes=['', '_min'])
             .assign(diff = lambda x: (x['index'] - x['index_min']) / pd.to_timedelta('1H'))
          )

print(agg_df.head(10))   

#          date  min  max               index  Random_Number           index_min  Random_Number_min  diff
# 0  2018-10-09    1   94 2018-10-09 05:00:00             94 2018-10-09 09:00:00                  1  -4.0
# 1  2018-10-10   12   95 2018-10-10 20:00:00             95 2018-10-10 21:00:00                 12  -1.0
# 2  2018-10-11    5   97 2018-10-11 15:00:00             97 2018-10-11 19:00:00                  5  -4.0
# 3  2018-10-12    7   98 2018-10-12 18:00:00             98 2018-10-12 06:00:00                  7  12.0
# 4  2018-10-13    1   91 2018-10-13 22:00:00             91 2018-10-13 01:00:00                  1  21.0
# 5  2018-10-14    1   97 2018-10-14 10:00:00             97 2018-10-14 04:00:00                  1   6.0
# 6  2018-10-15    9   97 2018-10-15 06:00:00             97 2018-10-15 12:00:00                  9  -6.0
# 7  2018-10-16    3   95 2018-10-16 04:00:00             95 2018-10-16 22:00:00                  3 -18.0
# 8  2018-10-17    2   95 2018-10-17 13:00:00             95 2018-10-17 21:00:00                  2  -8.0
# 9  2018-10-18    1   91 2018-10-18 21:00:00             91 2018-10-18 12:00:00                  1   9.0

pandas calculate time series resampling

2 Answers2