Wrong Dates In Dataframe And Subplots

I am trying to plot my data in the csv file. Currently my dates are not shown properly in the plot also if i am converting it. How can I change it to show the proper dat format as

Solution 1:

You can just tell pandas to parse that column as a datetime and it will just work:

In[151]:
import matplotlib.pyplot as plt
t="""Calcgroup;Valuegroup;id;Date;Value
Group1;A;1;20080103;0.1
Group1;A;1;20080104;0.3
Group1;A;1;20080107;0.5
Group1;A;1;20080108;0.9
Group1;B;1;20080103;0.5
Group1;B;1;20080104;1.3
Group1;B;1;20080107;2.0
Group1;B;1;20080108;0.15
Group1;C;1;20080103;1.9
Group1;C;1;20080104;2.1
Group1;C;1;20080107;2.9
Group1;C;1;20080108;0.45"""
df = pd.read_csv(io.StringIO(t), parse_dates=['Date'], sep=';', index_col=0)
df

Out[151]: 
          Valuegroup  id       Date  Value
Calcgroup                                 
Group1             A   12008-01-03   0.10
Group1             A   12008-01-04   0.30
Group1             A   12008-01-07   0.50
Group1             A   12008-01-08   0.90
Group1             B   12008-01-03   0.50
Group1             B   12008-01-04   1.30
Group1             B   12008-01-07   2.00
Group1             B   12008-01-08   0.15
Group1             C   12008-01-03   1.90
Group1             C   12008-01-04   2.10
Group1             C   12008-01-07   2.90
Group1             C   12008-01-08   0.45

fig, ax = plt.subplots()
df.groupby('Valuegroup').plot(x='Date', y='Value', ax=ax, legend=False, kind='line')
plt.grid(True)    
plt.show()

results in:

Besides your format string was incorrect anyway, it should be:

csv_loader['Date'] = pd.to_datetime(csv_loader['Date'], format="%Y%m%d")

however, this won't work as that column will have been loaded as int dtype so you would've needed to convert to string first:

csv_loader['Date'] = pd.to_datetime(csv_loader['Date'].astype(str), format="%Y%m%d")

To format the dates on the x-axis you can use DateFormatter from matplotlib see related: Editing the date formatting of x-axis tick labels in matplotlib

from matplotlib.dates import DateFormatter
fig, ax = plt.subplots()
df.groupby('Valuegroup').plot(x='Date', y='Value', ax=ax, legend=False, kind='line')

plt.grid(True)

myFmt = DateFormatter("%d-%m-%Y")
ax.xaxis.set_minor_formatter(myFmt)
plt.show()

now gives plot:

Solution 2:

You're parsing your dates wrong; "%Y-%m-%d" would work for dates like 2017-12-11 (which is Dec 12, 2017). Your dates are of the form "%Y%m%d", without the hyphen.