pandas Timestamp to datetime.date

Question

I have problem with converting pandas Series to datetime.datetime.

I got DataFrame - df, with column Timestamp of type: pandas._libs.tslibs.timestamps.Timestamp and column Timestamp-end of type: pandas._libs.tslibs.timedeltas.Timedelta

I found that topic on SO: Converting pandas.tslib.Timestamp to datetime python but the suggestions on this topic did not work.

Is there any possibility to convert it into datetime? If no, how can I subtract Timestamp-end from Timestamp column of type to get date and time into Timestamp and Timedelta type?

How I created Timestamp column:

import adodbapi
import pandas as pd
import numpy as np
import datetime as dt

cursor = myConn.cursor()
cursor.execute(query)
# every row in query_list is type of SQLrow
query_list = [row for row in cursor]
df = pd.DataFrame({'TagAddress':[row[0] for row in query_list], 'Timestamp':[row[1] for row in query_list], 'Value':[row[3] for row in query_list]})

Timestamp-end column:

df['Timestamp-end'] = pd.NaT
# in for loop, dict values are type of timestamps.Timestamp
df['Timestamp-end'].iloc[i] = df['Timestamp'].iloc[i] - current_errors_timestamp[curr_fault_key]

My expected output (column Result):

I just want to subtract Timedelta from Timestamp to get new column Timestamp. With type datetime.datetime I can do it without any problems.

Timestamp               ErrorValue  Machine Station FAULT   Timestamp-end           Result
2020-06-20 08:01:09.562 370         T1      R1      1       0 days 00:00:06         2020-06-20 08:01:03
2020-06-20 08:01:21.881 370         T1      R1      0       0 days 00:00:12.319000  2020-06-20 08:01:09
2020-06-20 08:07:06.708 338         T1      R1      0       0 days 00:00:24.623000  2020-06-20 08:06:42
2020-06-20 08:07:31.041 338         T1      R1      0       0 days 00:00:18.333000  2020-06-20 08:07:13

jezrael · Accepted Answer · 2020-08-19T09:20:14.620

2

I beleive you need convert column to dates:

df['Timestamp1'] = df['Timestamp'].dt.date

Or beter should be remove times, set them to 00:00:00:

df['Timestamp1'] = df['Timestamp'].dt.normalize()

And then subtract.

EDIT: You can subtract values and then use Series.dt.floor for seconds:

df['Timestamp-end'] = pd.to_timedelta(df['Timestamp-end'])
df['Result'] = df['Timestamp'].sub(df['Timestamp-end']).dt.floor('S')
print (df)
                Timestamp  ErrorValue Machine Station  FAULT   Timestamp-end  \
0 2020-06-20 08:01:09.562         370      T1      R1      1        00:00:06   
1 2020-06-20 08:01:21.881         370      T1      R1      0 00:00:12.319000   
2 2020-06-20 08:07:06.708         338      T1      R1      0 00:00:24.623000   
3 2020-06-20 08:07:31.041         338      T1      R1      0 00:00:18.333000   

               Result  
0 2020-06-20 08:01:03  
1 2020-06-20 08:01:09  
2 2020-06-20 08:06:42  
3 2020-06-20 08:07:12

edited Aug 19 '20 at 09:20

answered Aug 17 '20 at 06:26

jezrael

822,522
95
1,334
1,252

I need date and time into one column. – aozk Aug 17 '20 at 06:46
Not understand, is possible create some sample data, 4-5 rows and expected output? – jezrael Aug 17 '20 at 06:47
@aozk - Is possible edit question with input data, output data, becasue bad formatiing in comments? – jezrael Aug 19 '20 at 05:48
I added expected output into question. – aozk Aug 19 '20 at 05:52
@aozk - [Please don't post images of code/data (or links to them)](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question), better is text version of data – jezrael Aug 19 '20 at 05:54
I added text version :) – aozk Aug 19 '20 at 06:03
I got following error: `UFuncTypeError: ufunc 'subtract' cannot use operands with types dtype(' – aozk Aug 19 '20 at 09:19
@aozk - Because sample data, add `df['Timestamp-end'] = pd.to_timedelta(df['Timestamp-end'])` for convert strings to timedeltas, answer was edited. – jezrael Aug 19 '20 at 09:20
When i used pd.to_datetime() i got this error: `TypeError: is not convertible to datetime` – aozk Aug 19 '20 at 10:29
@aozk - What code return error? For me working perfectly, what is `print (df[['Timestamp','Timestamp-end'].dtypes)` ? – jezrael Aug 19 '20 at 10:31
@aozk - do youi convert to `datetime` or to `timedelta` ? like `df['Timestamp-end'] = pd.to_timedelta(df['Timestamp-end'])` or `df['Timestamp-end'] = pd.to_datetime(df['Timestamp-end'])` ? – jezrael Aug 19 '20 at 10:32
1

My fault! i used `to_datetime` instead of `to_timedelta`, now everything works fine, I got everything I wanted. Thank you for your help! – aozk Aug 19 '20 at 10:39

pandas Timestamp to datetime.date

1 Answers1