[FIXED] How to group by day, plot it against the same graph and analyse it?

Issue

I am currently try to plot my dataset to obtain the following image. (Dataset : https://drive.google.com/uc?export=download&id=1K53Xn0CnidHCtJYlZW25-9Mt02MV1-rT )

My attempt

X axis is the timespan of a day 0 - 24hrs, and y-axis being the run speed at that duration. I want to eventually calculate the average speed per day and analyse the trend and so forth.

I was able to plot for 1 day as shown below, using df["2023-07-07":"2023-07-07"]['SPEED'].plot()

Shown here

But if i plot it multiple times, it does overlap against the previous plot. Instead this happened. A continuous timeseries. Overlap fail

Solution

Just convert your timestamps to the date with dt.normalize then groupby.mean and plot:

df = pd.read_excel('outputnew.xlsx')

(df.groupby(pd.to_datetime(df['TIMESTAMP'])
              .dt.normalize().rename('day'))
 ['SPEED'].mean()
 .plot()
)

Output:

Or using seaborn.lineplot:

import seaborn as sns
import matplotlib.pyplot as plt

sns.lineplot(data=df.assign(day=pd.to_datetime(df['TIMESTAMP'])
                                  .dt.normalize()),
             x='day', y='SPEED')
plt.xticks(rotation=45)

Output:

Answered By - mozway