CODEWITHSUNDEEP
pythonpandasdatetime
ailsa_naismith
ailsa_naismith

Reputation: 343

Unusual formatting for date and times: wish to convert to datetime objects

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!

Upvotes: 0

Views: 62

Answers (1)

jezrael
jezrael

Reputation: 863361

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

Upvotes: 3

Related Questions