0

I have a .csv file with three columns, one representing date, one time, and a third associated values. The columns are as shown:

date time value 02112017 143335 1.79 03112017 153540 0.86 04112017 164015 2.79 05112017 135150 3.50

After uploading the .csv file into Python and querying data types, "date" and "time" are integer values and "value" are float values.

I would like to combine the "date" and "time" columns into a single column of datetime objects, that I can then plot against "value" column to make a timeseries. As such:

date time datetime 02112017 143335 02-11-2017 14:33:35

I've tried to concatenate these values and then use pd.to_datetime to convert them to datetime objects, but get a series of error messages thrown up - including the message: 

ValueError: unconverted data remains: 00

If anyone could shine a light on what I'm missing, I'd be grateful!

Improve this question

1 Answer 1

Reset to default
3

First join columns and then use to_datetime:

s = df['date'] + ' ' + df['time'].astype(str) df['datetime'] = pd.to_datetime(s, format='%d%m%Y %H%M%S') print (df) date time value datetime 0 02112017 143335 1.79 2017-11-02 14:33:35 1 03112017 153540 0.86 2017-11-03 15:35:40 2 04112017 164015 2.79 2017-11-04 16:40:15 3 05112017 135150 3.50 2017-11-05 13:51:50
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Nice one. In my case i have to replace first statement by : s = df['date'].astype(str) + ' ' + df['time'].astype(str) or s = df['date'].map(str) + ' ' + df['time'].map(str) because csv data not in str format. Anyway thx.
Thanks! I also had the same issue, that I had to include 'astype(str)' for my "date" column. But otherwise it worked. Now to work out how to plot this!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.