Plot graph with two x label index - day and time

Question

I have a lot of quarter-hourly data (consumption versus time). I have to make averages on these data and I would have liked to display the averages according to the days of the week + time.

So I am looking to put on a graphic the day and the time at the same time. The expected result is possible in Excel but I'm looking to do it in python with matplotlib (and using dataframes).

If you have any idea, thanks a lot! Guillaume

Here is a code that displays a decent result but I would like better. I'm sorry but I can't put an image attached directly because I'm new on the forum.

import pandas as pd
import datetime
import matplotlib.pyplot as plts

columns = ["Date/Time","Value"]
new_df = pd.DataFrame(columns = columns)
Jour1 = pd.to_datetime('02/01/2021')
value = np.random.randint(100, 150, size=(672,))

for x in range(672):
    TimeStamp = Jour1
    Jour1 = Jour1 + datetime.timedelta(minutes=15)  
    new_df = new_df.append(pd.Series([TimeStamp,value[x]],  index = columns) ,ignore_index=True) 

new_df['Day of week Name'] = new_df['Date/Time'].dt.dayofweek.astype(str) + ' - '+ new_df['Date/Time'].dt.day_name()
new_df["Time"] = new_df['Date/Time'].dt.time
new_df = new_df.groupby(['Day of week Name','Time'])['Value'].sum().reset_index()
new_df['TimeShow'] = new_df['Day of week Name'] +' '+ new_df['Time'].astype(str)

fig = plt.figure(figsize=(18,10))
ax=fig.add_subplot(111)

ax.plot(new_df['TimeShow'], new_df['Value'], label="Test", linewidth = 2)
plt.xticks(['0 - Monday 00:00:00','1 - Tuesday 00:00:00','2 - Wednesday 00:00:00','3 - Thursday 00:00:00','4 - Friday 00:00:00','5 - Saturday 00:00:00','6 - Sunday 00:00:00'])
plt.show()

Image in python

Image in excel - day not in order

EDIT : Thanks to your help, I finally found something that works for me. I don't know if the code is optimized but it works. here is the code if needed :

fig = plt.figure(figsize=(18,10))
ax=fig.add_subplot(111)

date_rng = pd.date_range('2021-01-01 00:00:00','2021-01-08 00:00:00', freq='6h')
xlabels = pd.DataFrame(index=date_rng)
xlabels = xlabels.index.strftime('%H:%M').tolist()

liste_saisons =  df['Saison'].unique().tolist()
for saisons in liste_saisons :

    df_show = df.loc[(df['Saison'] == saisons)]
    df_show = df_show.groupby(['Jour Semaine Nom','Time'],as_index=False)['SUM(CORR_VALUE)'].mean()
    df_show['TimeShow'] = df_show['Jour Semaine Nom'] +' '+ df_show['Time'].astype(str)
    ax.plot(df_show.index, df_show['SUM(CORR_VALUE)'], label=saisons, linewidth = 3)
    
fig.suptitle('Evolution de la charge BT quart-horaire moyenne semaine', fontsize=20)
plt.xlabel('Jour de la semaine + Heure', fontsize=20)
plt.ylabel('Charge BT quart-horaire moyenne [MW]', fontsize = 20)
plt.rc('legend', fontsize=16)
ax.legend(loc='upper left')
plt.grid(color='k', linestyle='-.', linewidth=1)


ax.set_xticklabels(xlabels)
plt.xticks(np.arange(0, 96*7, 4*6))
plt.ylim(50,350)

xdays = df_show["Jour Semaine Nom"].tolist()
graph_pos = plt.gca().get_position()
points = np.arange(48, len(xdays), 96)
day_points = np.arange(0, len(xdays), 96)
offset = -65.0
trans = ax.get_xaxis_transform()
for i,d in enumerate(xdays):
    if i in points:
         ax.text(i, graph_pos.y0 - offset, d, ha='center',bbox=dict(facecolor='cyan', edgecolor='black', boxstyle='round'), fontsize=12)

plt.show()

Result

Answer 1

There are many possible approaches to this kind of task, but I used the text and plot functions to deal with it. to add the first date, I took the size of the graph and subtracted the offset value from the y0 value to determine the position. To add the first date, I took the size of the graph and subtracted an offset value from the y0 value, and for each date, I manually set the y1 value to position the vertical line.

PS: For a faster answer, I will present it even with unfinished code. Attach an image instead of a link. Attach the toy data in text. This is necessary.

import pandas as pd
import numpy as np

date_rng = pd.date_range('2021-01-01','2021-03-01', freq='1h')
value = np.random.randint(100, 150, size=(1417,))
df = pd.DataFrame({'date':pd.to_datetime(date_rng),'value':value})

import matplotlib.pyplot as plt

w = 0.7
fig,ax = plt.subplots(figsize=(20,4))

ax.bar(df.date[:100].apply(lambda x:x.strftime('%Y-%m-%d %H:%M:%S')), df.value[:100], color='C0', width=w, align="center")

xlabels = df.date[:100].apply(lambda x:x.strftime('%H:%M:%S')).tolist()
xdays = df.date[:100].apply(lambda x:x.strftime('%d-%b')).tolist()
ax.set_xticklabels(xlabels, rotation=90)

graph_pos = plt.gca().get_position()
points = np.arange(12, len(xlabels), 24)
day_points = np.arange(0, len(xlabels), 24)
offset = 50.0
trans = ax.get_xaxis_transform()
for i,d in enumerate(xdays):
    if i in points:
        ax.text(i, graph_pos.y0 - offset, d, ha='center')
    if i in day_points:
        ax.plot([i, i], [0, -0.3], color='gray', transform=trans, clip_on=False)
ax.set_xlim(-1, len(xlabels))
plt.show()