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

user_8542237 0
Viewed 1625

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

seaborn matplotlib python
1 Answers

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:

enter image description here

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:

enter image description here

user_42384080
Related