How to bin time in a pandas dataframe

Question

I am trying to analyze average daily fluctuations in a measurement "X" over several weeks using pandas dataframes, however timestamps/datetimes etc. are proving particularly hellish to deal with. Having spent a good few hours trying to work this out my code is getting messier and messier and I don't think I'm any closer to a solution, hoping someone here can guide me in the right direction.

I have measured X at different times and on different days, saving the daily results to a dataframe which has the form:

    Timestamp(datetime64)         X 

0    2015-10-05 00:01:38          1
1    2015-10-05 06:03:39          4 
2    2015-10-05 13:42:39          3
3    2015-10-05 22:15:39          2

As the time the measurement is made at changes from day to day I decided to use binning to organize the data, and then work out averages and STD for each bin which I can then plot. My idea was to create a final dataframe with bins and the average value of X for the measurements, the 'Observations' column is just to aid understanding:

        Time Bin       Observations     <X>  

0     00:00-05:59      [ 1 , ...]       2.3
1     06:00-11:59      [ 4 , ...]       4.6
2     12:00-17:59      [ 3 , ...]       8.5
3     18:00-23:59      [ 2 , ...]       3.1

However I've run into difficulties with incompatibility between time, datetime, datetime64, timedelta and binning using pd.cut and pd.groupby, basically I feel like I'm making stabs in the dark with no idea as to the the 'right' way to approach this problem. The only solution I can think of is a row-by-row iteration through the dataframe but I'd really like to avoid having to do this.

Answer 1

• The correct way to bin a pandas.DataFrame is to use pandas.cut
• Verify the date column is in a datetime format with pandas.to_datetime.
• Use .dt.hour to extract the hour, for use in the .cut method.
• Tested in python 3.8.11 and pandas 1.3.1

How to bin the data

import pandas as pd
import numpy as np  # for test data
import random  # for test data

# setup a sample dataframe; creates 1.5 months of hourly observations
np.random.seed(365)
random.seed(365)
data = {'date': pd.bdate_range('2020-09-21', freq='h', periods=1100).tolist(),
        'x': np.random.randint(10, size=(1100))}
df = pd.DataFrame(data)

# the date column of the sample data is already in a datetime format
# if the date column is not a datetime, then uncomment the following line
# df.date= pd.to_datetime(df.date)

# define the bins
bins = [0, 6, 12, 18, 24]

# add custom labels if desired
labels = ['00:00-05:59', '06:00-11:59', '12:00-17:59', '18:00-23:59']

# add the bins to the dataframe
df['Time Bin'] = pd.cut(df.date.dt.hour, bins, labels=labels, right=False)

# display(df.head())
                  date  x     Time Bin
0  2020-09-21 00:00:00  2  00:00-05:59
1  2020-09-21 01:00:00  4  00:00-05:59
2  2020-09-21 02:00:00  1  00:00-05:59
3  2020-09-21 03:00:00  5  00:00-05:59
4  2020-09-21 04:00:00  2  00:00-05:59

# display(df.tail())
                    date  x     Time Bin
1095 2020-11-05 15:00:00  2  12:00-17:59
1096 2020-11-05 16:00:00  3  12:00-17:59
1097 2020-11-05 17:00:00  1  12:00-17:59
1098 2020-11-05 18:00:00  2  18:00-23:59
1099 2020-11-05 19:00:00  2  18:00-23:59

Groupby 'Time Bin'

• Use pandas.DataFrame.groupby on 'Time Bin', and then aggregate 'x' into a list and mean.

# groupby Time Bin and aggregate a list for the observations, and mean
dfg = df.groupby('Time Bin', as_index=False)['x'].agg([list, 'mean'])

# change the column names, if desired
dfg.columns = ['X Observations', 'X mean']

# display(dfg)
                      X Observations    X mean
Time Bin                                 
00:00-05:59  [2, 4, 1, 5, 2, 2, ...]  4.416667
06:00-11:59  [9, 8, 4, 0, 3, 3, ...]  4.760870
12:00-17:59  [7, 7, 7, 0, 8, 4, ...]  4.384058
18:00-23:59  [3, 2, 6, 2, 6, 8, ...]  4.459559

Answer 2

Whenever I bin time series data by a time range, which seems to be what you are doing here, I just create an "hour of day" column and slice over that. Also, I normally set the index as datetime values...though that is not necessary here.

# assuming your "timestamp" column is labeled ts: 
df['hod'] = [r.hour for r in df.ts]

# now you can calculate stats for each bin
ave = df[ (df.hod>=0) & (df.hod<6) ].mean()

I would think there is a method of using df.resample here, but with the poorly defined starting/ending points in your time series I think this may require more attention than the above method.

Is this along the lines of what you were wanting?

Answer 3

Not sure I have the best answer but I think it works anyway.
First, I would convert the datetime64 to datetime using this post for example : Converting between datetime, Timestamp and datetime64

Then, if we assume that your first column has datetime and is called TimeStamp, I would do something like this :

def bin_f(x):
    if x.time() < datetime.time(6):
        return "00:00-05:59"
    elif x.time() < datetime.time(12):
        return "06:00-11:59"
    elif x.time() < datetime.time(18):
        return "12:00-17:59"
    else:
        return "18:00-23:59"

df["Bin"] = df["TimeStamp"].apply(bin_f)
grouped = df.groupby("Bin")
grouped['X'].agg(np.std)

With X being the name of your column.

Answer 4

Though this is an old thread, adding another method to it. Using pandas resample method gives the desired result in fewer lines of code.

data = {'date': pd.bdate_range('2020-09-21', freq='h', periods=24).tolist(),
    'x': np.random.randint(10, size=(24))}
df = pd.DataFrame(data)
df
# This line will resample data by 6H timeframe
dfrs=df.resample('6H',on='date').agg({'x':[list,'mean']})
dfrs
                        X Observations    X mean
date                                             
2020-09-21 00:00:00  [2, 4, 1, 5, 2, 2]  2.666667
2020-09-21 06:00:00  [9, 8, 4, 0, 3, 3]  4.500000
2020-09-21 12:00:00  [7, 7, 7, 0, 8, 4]  5.500000
2020-09-21 18:00:00  [3, 2, 6, 2, 6, 8]  4.500000

Answer 5

I found Mathiou's response helpful for my purpose, but modified it as follows:

def bin_f(x):
    h = x.time()
    if h < 6:
        return "00:00-05:59"
    elif h < 12:
        return "06:00-11:59"
    elif h < 18:
        return "12:00-17:59"
    else:
        return "18:00-23:59"