Multi tool use

Convert pandas dataframe column of UTC time string to floats

I have a pandas dataframe with a column of strings, with datetimes in UTC format, but need to convert them to floats. I'm having trouble doing this. Here is a view of my column:

df['time'][0:3]

0 2018-04-18T19:00:00.000000000Z
1 2018-04-18T19:15:00.000000000Z
2 2018-04-18T19:30:00.000000000Z
Name: time, dtype: object


I've been trying this, but isn't working for me:

import datetime
for i in range(1,len(df)):
df['time'][i] = datetime.datetime.strptime(df['time'][i], '%Y-%m-%dT%H:%M:%S.%f000Z')


Here is the error I'm trying to fix:

execfile(filename, namespace)

exec(compile(f.read(), filename, 'exec'), namespace)

unsup.fit(np.reshape(df,(-1,df.shape[1])))

X = _check_X(X, self.n_components)

X = check_array(X, dtype=[np.float64, np.float32])

array = np.array(array, dtype=dtype, order=order, copy=copy)

ValueError: could not convert string to float: '2018-06-29T20:45:00.000000000Z'


Many thanks in advance.

1 Answer
1

I think you can use to_datetime with parameter format:

to_datetime

format

df['time1'] = pd.to_datetime(df['time'], format='%Y-%m-%dT%H:%M:%S.%f000Z')
print (df)
time time1
0 2018-04-18T19:00:00.000000000Z 2018-04-18 19:00:00
1 2018-04-18T19:15:00.000000000Z 2018-04-18 19:15:00
2 2018-04-18T19:30:00.000000000Z 2018-04-18 19:30:00


For assign back:

df['time'] = pd.to_datetime(df['time'], format='%Y-%m-%dT%H:%M:%S.%f000Z')
print (df)
time
0 2018-04-18 19:00:00
1 2018-04-18 19:15:00
2 2018-04-18 19:30:00


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.