1
$\begingroup$

This issue that I am facing is very simple yet weird and has troubled me to no end.

I have a dataframe as follows :

df['datetime'] = df['datetime'].dt.tz_convert('US/Pacific')
df.head()

        vehicle_id  trip_id                                 datetime    
6760612 1000500 4f874888ce404720a203e36f1cf5b716    2017-01-01 10:00:00-08:00       
6760613 1000500 4f874888ce404720a203e36f1cf5b716    2017-01-01 10:00:01-08:00    
6760614 1000500 4f874888ce404720a203e36f1cf5b716    2017-01-01 10:00:02-08:00      
6760615 1000500 4f874888ce404720a203e36f1cf5b716    2017-01-01 10:00:03-08:00       
6760616 1000500 4f874888ce404720a203e36f1cf5b716    2017-01-01 10:00:04-08:00   

I am trying to find out the datatime difference as follows ( in two different ways) :

df['datetime_diff'] = df['datetime'].diff()

df['time_diff'] = (df['datetime'] - df['datetime'].shift(1)).astype('timedelta64[s]')

For a particular trip_id, I have the results as follows :

df[trip_frame['trip_id'] == '4f874888ce404720a203e36f1cf5b716'][['datetime','datetime_diff','time_diff']].head()

                   datetime         datetime_diff   time_diff
6760612 2017-01-01 10:00:00-08:00   NaT             NaN
6760613 2017-01-01 10:00:01-08:00   00:00:01        1.0
6760614 2017-01-01 10:00:02-08:00   00:00:01        1.0
6760615 2017-01-01 10:00:03-08:00   00:00:01        1.0
6760616 2017-01-01 10:00:04-08:00   00:00:01        1.0

But for some other trip_ids like the below, you can observe that I am having the datetime difference as zero (for both the columns) when it is actually not.There is a time difference in seconds.

df[trip_frame['trip_id'] == '01b8a24510cd4e4684d67b96369286e0'][['datetime','datetime_diff','time_diff']].head(4)

         datetime            datetime_diff  time_diff
3236107 2017-01-28 03:00:00-08:00   0 days  0.0
3236108 2017-01-28 03:00:01-08:00   0 days  0.0
3236109 2017-01-28 03:00:02-08:00   0 days  0.0
3236110 2017-01-28 03:00:03-08:00   0 days  0.0

df[df['trip_id'] == '01c2a70c25e5428bb33811ca5eb19270'][['datetime','datetime_diff','time_diff']].head(4)

        datetime             datetime_diff  time_diff
8915474 2017-01-21 10:00:00-08:00   0 days  0.0
8915475 2017-01-21 10:00:01-08:00   0 days  0.0
8915476 2017-01-21 10:00:02-08:00   0 days  0.0
8915477 2017-01-21 10:00:03-08:00   0 days  0.0

Any leads as to what the actual issue is ? I will be very grateful.

Update - I tried out @n1k31t4's approach and got the following results for those problematic rows. Did not even do the time-zone conversion. It is still so weird and surprising.

                        datetime    timestamps    timestamp_diffs    date_diffs
3236107 2017-01-28 11:00:00+00:00   1485601200000000    0.0          0 days
3236108 2017-01-28 11:00:01+00:00   1485601201000000    0.0          0 days
3236109 2017-01-28 11:00:02+00:00   1485601202000000    0.0          0 days
3236110 2017-01-28 11:00:03+00:00   1485601203000000    0.0          0 days
3236111 2017-01-28 11:00:04+00:00   1485601204000000    0.0          0 days
$\endgroup$
  • $\begingroup$ Quite possibly, the error is in your assignment of the seconds to the dates in the later filters. Do a quick inspection of those values before the calculation / assignment, what do they yield? $\endgroup$ – Yaakov Bressler Jul 8 '19 at 6:08
  • $\begingroup$ Additionally, pandas' groupby function might be helpful here for assigning days. Of which, values can be set using df.loc[] $\endgroup$ – Yaakov Bressler Jul 8 '19 at 6:09
  • $\begingroup$ The values are 0 nevertheless, both before converting into seconds and after converting into seconds. I am trying to find out the difference between the datatimes for a row as compared against the previous row. It works fine for certain rows but for other rows, it goes haywire. It's behaving the same for both the methods. $\endgroup$ – dravid07 Jul 8 '19 at 6:33
  • $\begingroup$ How should one understand this format: 2017-01-01 10:00:00-08:00 ? January 1st 2017, 10hr 0min 0sec, but then what does -08:00 mean exactly? $\endgroup$ – n1k31t4 Jul 8 '19 at 14:02
  • 1
    $\begingroup$ -08:00 indicates 8hours 'back' from GMT. It is for Pacific Standard Time. $\endgroup$ – dravid07 Jul 9 '19 at 2:00
0
$\begingroup$

It is hard to see where the problem is with your code; given your comments, my guess is a problem in the timezone conversion.

I cannot see a problem exactly, buy my suggestion to help debug your situation would be to convert all time to timestamps - by default that will be seconds since the epoch (January 1st 1970). These are then simply normal float64 values (I convert to integer microseconds below). If your differences are still returning zero, subtracting simple number, then the problem cannot be in the differences.

Here is a minimal working example. A simple dateframe with timestamps with millisecond frequency:

import pandas as pd
from datetime import datetime

In [1]: df = pd.DataFrame(pd.date_range(start=datetime(2016,1,1,0,0,1), 
    ...:     end=datetime(2016,1,1,0,0,2), freq='ms'), columns=["dates"]).head(10)    # just take first 10 rows for simplicity

Make a new column, converting the dates into microseconds since the epoch, as an integer:

In [2]: df["timestamps"] = df.dates.apply(lambda x: int(datetime.timestamp(x) * 1e6))                              

In [3]: df                                                                                                         
Out[3]: 
                    dates        timestamps
0 2016-01-01 00:00:01.000  1451602801000000
1 2016-01-01 00:00:01.001  1451602801001000
2 2016-01-01 00:00:01.002  1451602801002000
3 2016-01-01 00:00:01.003  1451602801003000
4 2016-01-01 00:00:01.004  1451602801004000
5 2016-01-01 00:00:01.005  1451602801005000
6 2016-01-01 00:00:01.006  1451602801006000
7 2016-01-01 00:00:01.007  1451602801007000
8 2016-01-01 00:00:01.008  1451602801008000
9 2016-01-01 00:00:01.009  1451602801009000

You could at this point check that there are no duplicates in any of your columns, using:

In [4]: df.T.duplicated()                                                                                          
Out[4]: 
dates         False
timestamps    False
dtype: bool

If there are duplicates, that could be the cause of differences equal to zero.

Now compute the differences, in my case all 1-millisecond differences (1000 microseconds):

In [5]: df[["date_diffs", "timestamp_diffs"]] = df.diff(1)                                                        

In [6]: df                                                                                                        
Out[6]: 
                    dates        timestamps      date_diffs  timestamp_diffs
0 2016-01-01 00:00:01.000  1451602801000000             NaT              NaN
1 2016-01-01 00:00:01.001  1451602801001000 00:00:00.001000           1000.0
2 2016-01-01 00:00:01.002  1451602801002000 00:00:00.001000           1000.0
3 2016-01-01 00:00:01.003  1451602801003000 00:00:00.001000           1000.0
4 2016-01-01 00:00:01.004  1451602801004000 00:00:00.001000           1000.0
5 2016-01-01 00:00:01.005  1451602801005000 00:00:00.001000           1000.0
6 2016-01-01 00:00:01.006  1451602801006000 00:00:00.001000           1000.0
7 2016-01-01 00:00:01.007  1451602801007000 00:00:00.001000           1000.0
8 2016-01-01 00:00:01.008  1451602801008000 00:00:00.001000           1000.0
9 2016-01-01 00:00:01.009  1451602801009000 00:00:00.001000           1000.0

Add a sample zero difference and retrieve the index of zero differences:

In [7]: df.iloc[3, 3] = 0.0                                                                                       

In [8]: np.where(df == 0)                                                                                         
Out[8]: (array([3]), array([3]))

Hopefully that will be enough to find where there could actually be zero difference. If all of that works out without zero differences, I would either look into your timezone conversion (maybe they do some rounding there?) or report a bug to the pandas issues


EDIT

After your update trying my debugging method, and seeing this:

difference between non-unique integers is never zero

I believe there must be either a bug or an inherent limitation to your system.

Pandas Bug

A bug might be in your specific version of Pandas and its df.diff() method. Check your version of Pandas with pd.__version__ and look on the issues page I linked above for any clues ... maybe just try the latest stable version anyway.

32-bit system

Another possible solution would be that you are running on a 32-bit system and so could actually lose the precision required for my example above. A 32-bit integer can only retain precision for 10 digits. My timestamps above require 12 digits. You can find out on like this, on Linux or Windows or Mac.

Additionally, you could retry my example, but only look at seconds instead of microseconds, just to be sure.

| improve this answer | |
$\endgroup$
  • $\begingroup$ I tried your approach and have updated the results in my question. It is still so weird. $\endgroup$ – dravid07 Jul 9 '19 at 2:02
  • $\begingroup$ @dravid07 - Please see my edit. $\endgroup$ – n1k31t4 Jul 9 '19 at 6:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.