How to create a seaborn heatmap by hour/day from timestamp with multiple data points per hour

Question

I have a data frame with a date column which is a timestamp. There are multiple data points per hour of a day eg 2014-1-1 13:10, 2014-1-1 13:20 etc. I want to group the data points from the same hour of a specific day and then create a heatmap using seaborn and plot a different column.

I have tried to use groupby but I'm not sure how to specify i want the hour and day

date             data
2014-1-1 13:10  50
2014-1-1 13:20  51
2014-1-1 13:30  51
2014-1-1 13:40  56
2014-1-1 13:50  67
2014-1-1 14:00  43
2014-1-1 14:10  78
2014-1-1 14:20  45
2014-1-1 14:30  58

I want to combine the data by its mean value

How do you want to combine the data points within the hour? Also you should include some sample data in your question. — Quang Hoang
– Quang Hoang, Commented Oct 16, 2020 at 2:22
@QuangHoangSorry about that, I have edited the question with some sample data. I want to combine the data by the hours mean value — TBolton
– TBolton, Commented Oct 16, 2020 at 2:27

Quang Hoang · Accepted Answer · 2020-10-16 03:28:39Z

5

You can use dt.strftime('%H') to get the hours, and dt.strftime('%Y-%m-%D') or dt.normalize() for the days

sns.heatmap(df.groupby([df.date.dt.normalize(), df.date.dt.strftime('%H:00')])
   ['data'].mean()
   .rename_axis(index=['day','hour'])
   .unstack(level=0)
)

Output:

Update: for the weeks, we can use a similar approach

s = (df.groupby([df.date.dt.isocalendar().week,
                 df.date.dt.strftime('%Y-%m-%d'), 
                 df.date.dt.strftime('%H:00')])
       ['data'].mean()
       .rename_axis(index=['week','day','hour'])
    )

fig, axes = plt.subplots(2,2, figsize=(10,10))
for w, ax in zip(s.index.unique('week'), axes.ravel()):
    sns.heatmap(s.loc[w].unstack(level='day'), ax=ax)
    ax.set_title(f'Week {w}')

Output:

edited Oct 16, 2020 at 3:28

answered Oct 16, 2020 at 2:34

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TBolton Over a year ago

For a larger data set (I'm plotting 4 weeks of data) is it possible to split up the heatmaps by week?

Quang Hoang Over a year ago

@TBolton what do you mean split by week?

TBolton Over a year ago

I am plotting 4 weeks of data, instead of having one large heatmap I want to split it into 4 heatmaps, one for each week of data.

Collectives™ on Stack Overflow

How to create a seaborn heatmap by hour/day from timestamp with multiple data points per hour

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related