[FIXED] Python: Plotting average Weekdays in succession for different Seasons

Issue

I have a DateTimeSeries Dataframe with 15 Minutes values. Index is a DatetimeIndex. First Column holds the Values, 2nd the Day of the Week, 3rd the season. (I have custom defined seasons)

I would like to plot all the Season with their average hourly value per weekday in succession, Monday to Sunday. Something like this:

I have managed to create 4 subplots, where each subplot represents one season, and all days of the week are depicted as an individual line. However, I can’t figure out how to plot one day after the other.

Here is what I got:

This is my code, but I created some random numbers, so the plot results don't make much sense. But I don’t want to bother anyone with my csv files:

import pandas as pd
import datetime as dt
import matplotlib.pyplot as plt
import matplotlib as mpl
import numpy as np

#Create DataFrame
df = pd.DataFrame()

df["date"] = pd.date_range(start='2018', end='2019', freq = "15Min")
df=df.set_index(["date"])

df['Day'] = df.index.weekday

dw_mapping={0: 'Mon', 1: 'Tue', 2: 'Wed', 3: 'Thu', 4: 'Fri', 5: 'Sat', 6: 'Sun'} 
df['Day']=df.index.weekday.map(dw_mapping)

df['season'] = df.index.month
season_mapping={1: 'Winter',     #Winter
            2: 'Winter',         #Winter
            3: 'eS/lF',    #EarlySpring / LateFall
            4: 'eS/lF',    #EarlySpring / LateFall
            5: 'lS/eF',       #LateSpring / EarlyFall
            6: 'Summer',  #Summer
            7: 'Summer',  #Summer
            8: 'Summer',  #Summer
            9: 'lS/eF',       #LateSpring / EarlyFall 
            10:'eS/lF', 
            11:'eS/lF',     #EarlySpring / LateFall
            12:'Winter'}          #Winter
df['season']=df.index.month.map(season_mapping)

df["B1W"] = 1
df['B1W'] = np.where(df['season'] == 'Winter', df['B1W'] * np.random.randint(30, 60, df.shape[0]), df["B1W"])
df['B1W'] = np.where(df['season'] == 'eS/lF', df['B1W'] *  np.random.randint(20, 50, df.shape[0]), df["B1W"])
df['B1W'] = np.where(df['season'] == 'lS/eF', df['B1W'] *  np.random.randint(10, 30, df.shape[0]), df["B1W"])
df['B1W'] = np.where(df['season'] == 'Summer', df['B1W'] * np.random.randint(0, 10, df.shape[0]), df["B1W"])

#### Create Plot #### 
mpl.rcParams['figure.dpi'] = 100
plt.style.use('ggplot')

ymin, ymax = 0, 50

fig, axes = plt.subplots(2, 2)
plt.subplots_adjust(wspace=0.2, hspace=0.3)
fig.suptitle("Building 1", fontsize=16)

fig.set_figheight(10)
fig.set_figwidth(15)

season = "Summer"
df.loc[df["season"]==season].groupby([df.loc[df["season"]==season].index.hour, 'Day'])["B1W"].mean().unstack().plot(
        ax=axes[0,0], ylim=(ymin,ymax), title = season, legend=False)

season = "eS/lF" #early Spring / late Fall
df.loc[df["season"]==season].groupby([df.loc[df["season"]==season].index.hour, 'Day'])["B1W"].mean().unstack().plot(
        ax=axes[0,1], ylim=(ymin,ymax), title = season, legend=False)

season = "lS/eF" #late Spring / early Fall
df.loc[df["season"]==season].groupby([df.loc[df["season"]==season].index.hour, 'Day'])["B1W"].mean().unstack().plot(
        ax=axes[1,0], ylim=(ymin,ymax), title = season, legend=False)

season = "Winter"
df.loc[df["season"]==season].groupby([df.loc[df["season"]==season].index.hour, 'Day'])["B1W"].mean().unstack().plot(
        ax=axes[1,1], ylim=(ymin,ymax), title = season, legend=False)

for ax in axes.flat:
    ax.set(xlabel='Time', ylabel='Power in MW')
    
for n in range(0,2):
    axes[n, 0].set_xticks([0,4,8,12,16,20])
    axes[n, 1].set_xticks([0,4,8,12,16,20])
    
# LEGEND
handles, labels = plt.gca().get_legend_handles_labels() # reorder Legend Labels
order = [1,5,6,4,0,2,3]      # specify order in Legend
axes[1,1].legend([handles[i] for i in order], [labels[i] for i in order], loc=1, ncol=7, 
                 bbox_to_anchor=(0.638,-0.2), frameon=True) # print and order LEGEND

Solution

I use the df

df2 = df.reset_index()
df3 = df2.groupby([df2['date'].dt.dayofweek, df2['Day'], df2['date'].dt.hour, df2['season']]).mean().unstack()
df3.index.set_names(['dayofweek','weekday_abr','hour'], inplace=True)
df3

Set dayofweek as an index for sorting, since weekday_abr is an unordered categorical index. After dayofweek index rearranged the proper order of Mon,Tue, etc. We can drop it.

df3.reset_index().drop(columns=['dayofweek']).set_index(['weekday_abr','hour']).plot(figsize=(10,5))

Answered By - Baron Legendre