CODEWITHSUNDEEP
pythondatetimepandas
Alex
Alex

Reputation: 1008

How to subtract timedelta from Timestamp, support negative returns

I have a dataframe containing two columns : timestamp and arrival_time, of Timestamp and timedelta types: 

        timestamp   arrival_time
0   2015-01-28 05:30:47 0 days 05:30:33.000000000
1   2015-01-28 05:31:50 0 days 05:31:00.000000000
2   2015-01-28 05:32:21 0 days 05:31:43.000000000
3   2015-01-28 05:32:53 0 days 05:31:43.000000000
4   2015-01-28 05:33:57 0 days 05:32:58.000000000
5   2015-01-28 05:34:28 0 days 05:33:39.000000000
6   2015-01-28 05:35:00 0 days 05:34:23.000000000
7   2015-01-28 05:35:31 0 days 05:34:23.000000000
8   2015-01-28 05:36:02 0 days 05:36:00.000000000
9   2015-01-28 05:36:34 0 days 05:36:00.000000000
10  2015-01-28 05:37:07 0 days 05:36:38.000000000
11  2015-01-28 05:37:38 0 days 05:36:38.000000000
12  2015-01-28 05:38:41 0 days 05:39:00.000000000
13  2015-01-28 05:39:13 0 days 05:39:00.000000000
14  2015-01-28 05:39:44 0 days 05:40:12.000000000

I want to find the mean difference of the two. However, simple subtraction will cause a problem for rows like row 12: It will return, correctly, 23:59:41. But I want it to return a negative value (i.e. -00:00:19) or sth like that.

Should I just completely change their types from dates to integers or is there a better solution?

EDIT: it acts weird again when the difference is more than a day:

3694    2015-01-29 01:10:27 1 days 01:11:27

Here it now returns 

3694   -2 days +23:59:00

where it should return -00:01:00

Upvotes: 0

Views: 663

Answers (1)

Robert Rodkey
Robert Rodkey

Reputation: 423

EDITED per comments If I'm reading this right, you basically want to know what the time difference is relative to start of the day in each timestamp. I set up a test case here and provided another snippet of code. Let me know if we're on the same page now.

In [123]: test_df
Out[123]:
            timestamp  arrival_time
0 2015-01-28 05:30:47      05:31:34

In [124]: test_df.dtypes
Out[124]:
timestamp        datetime64[ns]
arrival_time    timedelta64[ns]
dtype: object

In [125]: test_df['result'] = (test_df['timestamp'] - test_df['arrival_time']) - test_df['timestamp'].apply(pd.Timestamp.date).apply(pd.Timestamp)

In [126]: test_df
Out[126]:
            timestamp  arrival_time    result
0 2015-01-28 05:30:47      05:31:34 -00:00:47

Upvotes: 1

Related Questions