Why am I getting junk date values on x-axis in matplotlib?

Question

I am new to Python and learning data visualization using matplotlib. I am trying to plot Date/Time vs Values using matplotlib from this CSV file:

https://drive.google.com/file/d/1ex2sElpsXhxfKXA4ZbFk30aBrmb6-Y3I/view?usp=sharing

Following is the code snippet which I have been playing around with:

import pandas as pd
from matplotlib import pyplot as plt
import matplotlib.dates as mdates

plt.style.use('seaborn')
years = mdates.YearLocator()
months = mdates.MonthLocator()
days = mdates.DayLocator()
hours = mdates.HourLocator()
minutes = mdates.MinuteLocator()
years_fmt = mdates.DateFormatter('%H:%M')

data = pd.read_csv('datafile.csv')
data.sort_values('Date/Time', inplace=True)

fig, ax = plt.subplots()
ax.plot('Date/Time', 'Discharge', data=data)

# format the ticks
ax.xaxis.set_major_locator(minutes)
ax.xaxis.set_major_formatter(years_fmt)
ax.xaxis.set_minor_locator(hours)
datemin = min(data['Date/Time'])
datemax = max(data['Date/Time'])
ax.set_xlim(datemin, datemax)
ax.format_xdata = mdates.DateFormatter('%Y.%m.%d %H:%M')
ax.format_ydata = lambda x: '%1.2f' % x  # format the price.
ax.grid(True)
fig.autofmt_xdate()
plt.show()

The code is plotting the graph but it is not labeling the X-Axis and also giving some unknown values (on mouse over) for x on the bottom right corner as shown in the below screenshot:

Screenshot of matplotlib figure window

Can someone please suggest what changes are needed to plot the x-axis dates and also make the correct values appear when I move the cursor over the graph?

Thanks

Answer 1

I haven't used matplotlib. Instead I used pandas plotting

import pandas as pd

data = pd.read_csv('datafile.csv')
data.sort_values('Date/Time', inplace=True)
data["Date/Time"] = pd.to_datetime(data["Date/Time"], format="%d.%m.%Y %H:%M")

ax = data.plot.line(x='Date/Time', y='Discharge')

Here, you need to convert the Date/Time to pandas datetime type.

Answer 2

The main issue you have there is that the date formats are mixed up - your data uses '%d.%m.%Y %H:%M', but you set '%Y.%m.%d %H:%M' and this is why you saw 'rubbish' values in x ticks labels. Anyway the number of lines in your code can be reduced heavily if you convert your Date/Time column to timestamps, ie.:

import pandas as pd
from matplotlib import pyplot as plt
import matplotlib.dates as mdates

plt.style.use('seaborn')

data = pd.read_csv('datafile.csv')
data.sort_values('Date/Time', inplace=True)
data["Date/Time"] = pd.to_datetime(data["Date/Time"], format="%d.%m.%Y %H:%M")
data.sort_values('Date/Time', inplace=True)

fig, ax = plt.subplots()
ax.plot('Date/Time', 'Discharge', data=data)
ax.format_xdata = mdates.DateFormatter('%Y.%m.%d %H:%M')
ax.tick_params(axis='x', rotation=45)
ax.grid(True)
fig.autofmt_xdate()
plt.show()

Note that the format of labels in the plot will depend on the zoom level, so you will need to enlarge a portion of the graph to see hours and minutes in the tick labels, but the cursor locator on the bottom bar of the window should be always displaying the detailed timestamp under the cursor.