384
GLHeatmaps are a great way to spot patterns in data at a glance. This example uses data in A1:C69 to visualise shop footfall by day and hour:
df = xl("A1:C69", headers=True)
df["Day"] = pd.Categorical(
df["Day"],
["Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"],
ordered=True
)
sns.heatmap(
df.pivot(
index="Day",
columns="Hour",
values="Customers in shop"
),
cmap="RdYlGn_r",
annot=True
).set_title("Shop footfall by day and hour")
Start by typing =PY( to enter Python mode. Assign the whole data range to a DataFrame called df to make the data available to Python.
Next, convert the 'Day' column into a categorical and explicitly define the order from Monday through Sunday. Setting the category as ordered ensures the rows appear in a natural sequence rather than alphabetical order.
Reshape the data using a pivot: place 'Day' on the vertical axis, 'Hour' across the bottom, and use 'Customers in shop' as the grid values.
Pass this pivoted data into Seaborn’s heatmap function. Apply a reversed red–yellow–green colour map, so red highlights the busiest periods and green the quietest.
Switch annotations on to display the values, and finish by setting a clear title.
#excel #exceltips #exceleration #globalexcelsummit
@globalexcelsummit
