CODEWITHSUNDEEP
pythonpandasdataframe
Aqua 4
Aqua 4

Reputation: 871

Pandas convert Column to time

Here is my DF

             Start-Time Running-Time Speed-Avg HR-Avg
0    2016-12-18 10:8:14       0:24:2        20    138
1    2016-12-18 10:8:14       0:24:2        20    138
2    2016-12-23 8:52:36      0:31:19        16    134
3    2016-12-23 8:52:36      0:31:19        16    134
4     2016-12-25 8:0:51      0:30:10        50    135
5     2016-12-25 8:0:51      0:30:10        50    135
6    2016-12-26 8:41:26       0:10:1        27    116
7    2016-12-26 8:41:26       0:10:1        27    116
8      2017-1-7 11:16:9      0:26:15        22    124
9      2017-1-7 11:16:9      0:26:15        22    124
10    2017-1-10 19:2:54      0:53:51         5    142
11    2017-1-10 19:2:54      0:53:51         5    142

and i have been trying to format this column in H:M:S format using

timeDF=(pd.to_datetime(cleanDF['Running-Time'],format='%H:%M:%S'))

but i have been getting ValueError: time data ' 0:24:2' does not match format '%M:%S' (match) this error

Thank you in advance.

Upvotes: 9

Views: 24256

Answers (1)

jezrael
jezrael

Reputation: 862481

There is problem trailing whitespaces, so need str.strip:

Or if create DataFrame from file by read_csv add parameter skipinitialspace=True:

cleanDF = pd.read_csv(file, skipinitialspace = True)

timeDF=(pd.to_datetime(cleanDF['Running-Time'].str.strip(), format='%H:%M:%S'))
print (timeDF)
0    1900-01-01 00:24:02
1    1900-01-01 00:24:02
2    1900-01-01 00:31:19
3    1900-01-01 00:31:19
4    1900-01-01 00:30:10
5    1900-01-01 00:30:10
6    1900-01-01 00:10:01
7    1900-01-01 00:10:01
8    1900-01-01 00:26:15
9    1900-01-01 00:26:15
10   1900-01-01 00:53:51
11   1900-01-01 00:53:51
Name: Running-Time, dtype: datetime64[ns]

But maybe better is convert it to timedeltas by to_timedelta:

timeDF=(pd.to_timedelta(cleanDF['Running-Time'].str.strip()))
print (timeDF)
0    00:24:02
1    00:24:02
2    00:31:19
3    00:31:19
4    00:30:10
5    00:30:10
6    00:10:01
7    00:10:01
8    00:26:15
9    00:26:15
10   00:53:51
11   00:53:51
Name: Running-Time, dtype: timedelta64[ns]

Upvotes: 11

Related Questions