1

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 defined Y-m-d? The second question is that I am currently plotting all the dat in one plot but want to have for every Valuegroup one subplot.

My code looks like the following:

import pandas as pd
import matplotlib.pyplot as plt

csv_loader = pd.read_csv('C:/Test.csv', encoding='cp1252', sep=';', index_col=0).dropna()

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

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

plt.grid(True)

The csv file looks like the following:

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
EdChum
  • 376,765
  • 198
  • 813
  • 562
JonDoe
  • 107
  • 7

2 Answers2

2

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   1 2008-01-03   0.10
Group1             A   1 2008-01-04   0.30
Group1             A   1 2008-01-07   0.50
Group1             A   1 2008-01-08   0.90
Group1             B   1 2008-01-03   0.50
Group1             B   1 2008-01-04   1.30
Group1             B   1 2008-01-07   2.00
Group1             B   1 2008-01-08   0.15
Group1             C   1 2008-01-03   1.90
Group1             C   1 2008-01-04   2.10
Group1             C   1 2008-01-07   2.90
Group1             C   1 2008-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:

enter image description here

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:

enter image description here

EdChum
  • 376,765
  • 198
  • 813
  • 562
  • Thanks a lot, somehow the x-axis still don't show the proper dates but the format as shown in your figure above. I would like that the format shown on the x-axis is "03-01-2008". I also wanted to know how to show every Valuegroup in one subplot? – JonDoe Jul 11 '18 at 07:45
  • See related https://stackoverflow.com/questions/14946371/editing-the-date-formatting-of-x-axis-tick-labels-in-matplotlib, I'll update my code and plot – EdChum Jul 11 '18 at 08:44
0

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.

Joel
  • 1,564
  • 7
  • 12
  • 20