Splitting Pandas Dataframe into chunks by Timestamp

user_4115123 0
Viewed 646

Let's say I have a pandas dataframe df

DF

Timestamp     Value
Jan 1 12:32   10
Jan 1 12:50   15
Jan 1 13:01   5
Jan 1 16:05   17
Jan 1 16:10   17
Jan 1 16:22   20

The result I want back, is a dataframe with per-hour (or any user specified time-segment, really) averages. Let's say my specified timesegment is 1 hour here. I want back something like

Jan 1 12:00 12.5
Jan 1 13:00 5
Jan 1 14:00 0
Jan 1 15:00 0
Jan 1 16:00 18

Is there a simple way built into pandas to segment like this? It feels like there should be, but my googling of "splitting pandas dataframe" in a variety of ways is failing me.

pandas dataframe python split
1 Answers

We need to convert to datetime first then do resample 

df.Timestamp=pd.to_datetime('2020 '+df.Timestamp)

df.set_index('Timestamp').Value.resample('1H').mean().fillna(0)
Timestamp
2020-01-01 12:00:00     7.5
2020-01-01 13:00:00     5.0
2020-01-01 14:00:00     0.0
2020-01-01 15:00:00     0.0
2020-01-01 16:00:00    18.0
Freq: H, Name: Value, dtype: float64

Convert the index 

newdf.index=newdf.index.strftime('%B %d %H:%M')
newdf
Timestamp
January 01 12:00     7.5
January 01 13:00     5.0
January 01 14:00     0.0
January 01 15:00     0.0
January 01 16:00    18.0
Name: Value, dtype: float64
user_79645270
Related