Pandas date parsing from CSV file

Question

I have one csv file:

year;month;day;hour;min;sec;temperature
2022;10;27;13;36;42;5.835
2022;10;27;14;36;42;6.435
2022;10;27;15;36;42;6.335
2022;10;27;16;36;42;6.435

And I would like to plot a simple graph from it. I am not able to combine separate datetime parts. This is my code:

import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime

def parser(year, month, day, hour, minute, second):
    return pd.to_datetime(day + '.' + month + '.' + year + ' ' + hour + ':' + minute + ':' + second

df = pd.read_csv('temp_data.csv',sep=';',parse_dates={'datetime':['year', 'month', 'day', 'hour', 'min', 'sec']}, date_parser=parser,index_col=0)

time = df['datetime'].values
temp = df['temperature'].values

plt.plot(time, temp)

Note that `time = df['datetime'].values"` can't work as `datetime` is the index and not a column. — Corralien, Mar 04 '23 at 19:50
See also [How to combine multiple columns in a Data Frame to Pandas datetime](https://stackoverflow.com/q/49718863/10197418) — FObersteiner, Mar 05 '23 at 11:02

Corralien · Accepted Answer · 2023-03-05T09:18:11.020

1

Update

I would like the x-axis date format to be: 27.10.22

You have to handle manually formatting.

Simply use df.plot:

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

# df = pd.read_csv(...)

ax = df.plot()
loc = mdates.DayLocator()
fmt = mdates.DateFormatter('%d.%m.%y')
ax.xaxis.set_major_locator(loc)
ax.xaxis.set_major_formatter(fmt)
plt.show()

Output:

edited Mar 05 '23 at 09:18

answered Mar 04 '23 at 19:49

Corralien

109,409
8
28
52

Thank you, I also get the graph but unfortunately cannot get the x-axis dates right. Pleas see the picture: [Link](https://i.ibb.co/4sCbYrx/py.png) – fred Mar 05 '23 at 07:56
I would like the x-axis date format to be: 27.10.22 – fred Mar 05 '23 at 08:01
Thank you! The x-axis format is right, but the numbers are wrong. I think the problem is in function: parse_dates. Does Pandas know, that for example "second" means it is part on time... Here is picture: [Link](https://ibb.co/hDxzCkj) – fred Mar 05 '23 at 15:02
Also tried: ax = df.plot(x='datetime',y='tempc') – fred Mar 05 '23 at 15:05
If I write in console: df.info(), it says that datetime type is "object" but should be "datetime[ns]" – fred Mar 05 '23 at 15:23
I was able to solve it by this line: df['datetime'] = pd.to_datetime(df[['year','month','day','hour','minute','second']] .astype(str).apply(' '.join, 1), format='%Y %m %d %H %M %S') – fred Mar 05 '23 at 16:25

score 1 · Answer 2 · answered Mar 04 '23 at 19:55

I couldn't make your code work on my pc using python 3.11

df = pd.read_csv(
    "temp_data.csv",
    sep=";",
    parse_dates={"datetime": ["year", "month", "day", "hour", "min", "sec"]},
    date_parser=parser,
    # index_col=0, this line was causing the problem
)

My plot wouldn't show as well, so I had to do this too:

plt.plot(time, temp)
plt.show(block=True)

Pandas date parsing from CSV file

2 Answers2