1

I have the following txt file with 2 columns:

Date, Time
2013/1/4, 07:00:00.0
2013/1/4, 07:00:00.1
2013/1/4, 07:00:00.2
2013/1/4, 07:00:00.3
2013/1/4, 07:00:00.4
2013/1/4, 07:00:00.5
2013/1/4, 07:00:00.6
2013/1/4, 07:00:00.7
2013/1/4, 07:00:00.8
2013/1/4, 07:00:00.9
2013/1/4, 07:00:00.10
2013/1/4, 07:00:00.11
2013/1/4, 07:00:00.12
2013/1/4, 07:00:00.13
2013/1/4, 07:00:00.14
2013/1/4, 07:00:00.15
2013/1/4, 07:00:00.16

I need to convert the object into time format. For the "date" used (and it's working as expected):

df['Date'] = pd.to_datetime(df['Date'])

For the "time" I used to following (all failed, and yes I tried to search out and read pandas documentation)

df['Time']= (pd.to_datetime(df['Time'].str.strip(), format='%H:%M:%S:%F'))
df['Time'] = datetime.time(df['Time'], '%H:%M:%S,%f')
df['Time'] = datetime.datetime.strptime("%H:%M:%S,%f").timestamp()

even tried this:

df['DateTime'] = pd.to_datetime(df.pop('Date')) + pd.to_timedelta(df.pop('Time'))

Please advice what have I done wrong here in order to complete the time foramt. Your help is much appreciated!

Giladbi
  • 1,822
  • 3
  • 19
  • 34
  • I notice that your 3 time attempts all use different format strings, which means it's hard to tell whether it's the technique or the string. – Ann L. Dec 25 '18 at 21:12
  • 1
    I also notice that you don't seem to have tried it with a period between `%S` and `%F` or `%f`. – Ann L. Dec 25 '18 at 21:13
  • df['Time'] = pd.to_datetime(df['Time'],format='%H:%M:%S.%f').dt.time converts the column to object. is that what you were looking for? if you want to have a timestamp property, you can do something like pd.to_datetime(df['Time'],format='%H:%M:%S.%f').apply(pd.TimeStamp) – Bala Dec 25 '18 at 21:39
  • it's an object now and trying to convert to float64. when trying "pd.to_datetime(df['Time'],format='%H:%M:%S.%f').apply(pd.TimeStamp)" i am getting an error: KeyError: 'Time' – Giladbi Dec 26 '18 at 07:44
  • `KeyError: Time` means that `df` doesn't have a key with the value "Time". It's got nothing to do with format strings. – Ann L. Dec 29 '18 at 23:46
  • when using df.set_index('Time', inplace=True) i get the same error. how can i set time as index without error? – Giladbi Jan 12 '19 at 19:57

1 Answers1

1

It is probably simpler to concatenate the columns as strings, turn them into date/time objects, and then manipulate them further (if needed).

For your data, the following works for me:

pd.to_datetime(df.Date + ' ' + df.Time)

(Note that df.Date + ' ' + df.Time makes a string series in a format that pandas understands, in your case.)

To get the hour, for example:

df['foo'] = pd.to_datetime(df.Date + ' ' + df.Time)
df.foo.dt.hour
Ami Tavory
  • 74,578
  • 11
  • 141
  • 185
  • 1
    Good to see you back! – cs95 Dec 26 '18 at 04:45
  • I am getting an error: "TypeError: data type "datetime" not understood". any idea? how would i get full time (including millis) and not only hours? df.foo.dt.milliseconds ? – Giladbi Dec 26 '18 at 07:41
  • @Giladbi Which line is giving you the error: the one in my answer, or something after? Googling the error, it seems to be something that upgrading pandas and numpy could resolve. About "full time (including millis)" - full time in day, full time since epoch, or something else? – Ami Tavory Dec 26 '18 at 09:01
  • If you have a sec, please do consider weighing in on my post here: https://stackoverflow.com/questions/53927460/how-do-i-slice-or-filter-mutliindex-dataframe-levels thanks :) – cs95 Dec 26 '18 at 15:25
  • @AmiTavory this line: "df['foo'] = pd.to_datetime(df.Date + ' ' + df.Time)" and the error is: TypeError: data type "datetime" not understood. i also updated pandas and numpy to latest – Giladbi Dec 26 '18 at 16:33