Why is the difference of datetime = zero in the following dataframe?

Question

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

Answer 1

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:

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.