How to create a heatmap of month/hour combinations count from datetime column?

My dataframe column looks like this:

df["created_at"].head()

0    2016-06-01T11:42:22.908Z
1    2016-06-01T11:42:25.111Z
2    2016-06-01T11:42:25.900Z
3    2016-06-01T11:42:26.184Z
4    2016-06-01T11:42:26.350Z
Name: created_at, dtype: object

I would like a heatmap of this data with hours from 1 to 23 on x-axis and months from 1 to 12 on y-axis. Here's one of my attempts:

created_hours = pd.to_datetime(df.created_at).dt.hour
created_months = pd.to_datetime(df.created_at).dt.month
df_new = pd.DataFrame({"Month": created_months, "Hour": created_hours})
grouped = df_new.groupby(["Month", "Hour"])["Hour"].count().to_frame()
grouped.rename(columns={"Hour":"Count"})

res = grouped.pivot_table(index="Month", columns="Hour", values="Count", aggfunc=sum)
# -> ERROR

I also tried without grouping, creating a series and not converting to a dataframe, pivot instead of pivot_table, sns.heatmap() function from variations of the above but nothing works. As always I looked at many Q&A and tried to adapt the code, no luck, and the code above does not look to me as the right way to do it. I don't know if I am supposed to convert this to a dataframe, group or not, or even create 2 columns for month/hour. How can I get this heatmap to be displayed?

Solution

One option using a crosstab and seaborn.heatmap:

import seaborn as sns

s = pd.to_datetime(df['created_at'])

sns.heatmap(pd.crosstab(s.dt.month.rename('month'),
                        s.dt.hour.rename('hour'))
              .reindex(index=range(1, 13), columns=range(1, 24), fill_value=0))

Output:

Used input:

                 created_at
0  2016-06-01T11:42:22.908Z
1  2016-06-01T11:42:25.111Z
2  2016-06-01T12:42:25.900Z
3  2016-06-01T13:42:26.184Z
4  2016-07-01T11:42:26.350Z