2

I want to subtract two columns in order to get the time, but my columns are object types.

This are my initial columns dtypes:

Column1      object
Column2      object
EVS_START    object
Column3      object
time         object
dtype: object

I changed EVS_START and time to datetime64[ns] like this:

df['time'] = pd.to_datetime(df['time'])
df['EVS_START'] = pd.to_datetime(df['EVS_START'])

I checked again with df.dtypes and they were changed:

Column1      object
Column2      object
EVS_START    datetime64[ns]
Column3      object
time         datetime64[ns]
dtype: object

But when I am subtracting them I get TypeError: ufunc subtract cannot use operands with types dtype('<M8[ns]') and dtype('O')

df['Time_duration'] = df['time'] - df['EVS_START']

What am I doing wrong? I did something similar with a df and it worked fine I am using python 2.x

cyberbemon
  • 3,000
  • 11
  • 37
  • 62
Christian
  • 459
  • 4
  • 12
  • It should working nice, not typo `df['timp']` ? – jezrael Jan 23 '18 at 11:11
  • What is `timp`? Also, can you print 5-10 rows of your data? That would help. Also, you might want to use `errors='coerce'` as the second argument to `to_datetime`. – cs95 Jan 23 '18 at 11:11
  • timp is time(my original code it's romanian, forgot to change that). corected – Christian Jan 23 '18 at 11:15
  • column1 column2 EVS_START column3 time SC1 108 23/01/2018 11:28 str4 23/01/2018 12:35 SC2 321 24/01/2018 11:38 str87 24/01/2018 12:12 SC3 242 25/01/2018 11:45 ab75 25/01/2018 12:05 SC4 245 26/01/2018 11:56 doc56 26/01/2018 12:13 – Christian Jan 23 '18 at 11:19
  • This can sometimes happen if the column is set to the correct datatype, but a row item does not have a valid datetime value. Can you validate that both columns don't contain any invalid data? – Phil Sheard Jan 23 '18 at 11:20
  • Please add that data to your question as an edit. It doesn't make sense in the comments. – cs95 Jan 23 '18 at 11:21

1 Answers1

0

There is a similar question here, discussing incompatibility between datetime64[ns] and <M8[ns]:

Difference between data type 'datetime64[ns]' and '<M8[ns]'?

One suggestion offered is to update both Pandas and Numpy at the same time, so that they are working with the same datetime representation. Could you try that?

Phil Sheard
  • 2,102
  • 1
  • 17
  • 38
  • I did this but the result it's the same. What is real strange is that sometimes it works perfectly and sometimes it returns the erorr – Christian Feb 01 '18 at 15:33
  • Are you still having the issue? I can have another look if you still need help. – Phil Sheard Feb 01 '18 at 16:11
  • Yes, the issue is still there. I've made an app that query a db, returns the df and then in python I do the subtraction. The query runs every 2 minutes. 80% of the time it works perfect but in the 20% it returns the error: TypeError: ufunc subtract cannot use operands with types dtype(' – Christian Feb 02 '18 at 06:10
  • Thanks @Christian - is it possible to add logging to the routine scripts so that the queries that fail are recorded somewhere? My guess is that on some occasions the data coming back isn't in the right format but it would be helpful to understand why. – Phil Sheard Feb 02 '18 at 12:54
  • Very good idea. I'll do this and came back with, hopefully the answer. Thanks a lot Phil – Christian Feb 02 '18 at 13:03