Finding consecutive minutes in pandas

user_8526986 0
Viewed 36

I am following this article - Pandas output date, start and end time and event status given datetime continuity

An example of testing consecutive hours is in the post. I need to test in consecutive minutes. I modified the line of code from 3600 to 60 (hours to minutes)

#test consecutive minutes
df['g'] = df['Date'].diff().dt.total_seconds().div(60).ne(1)

The end result returns all True for any consecutive minutes.

Date                  meter    g
2009-02-13 13:23:00   53.49    True
2009-02-13 13:24:00   64.91    True
2009-02-13 13:25:00   32.04    True
2009-02-13 13:26:00   45.94    True
2009-02-13 15:45:00   45.94    True

Where the result should be

Date                  meter    g
2009-02-13 13:23:00   53.49    True
2009-02-13 13:24:00   64.91    False
2009-02-13 13:25:00   32.04    False
2009-02-13 13:26:00   45.94    False
2009-02-13 15:45:00   45.94    True

What is wrong here?

pandas python-3.x datetime python
1 Answers

The issue with your code is likely due to floating point approximation? This would be solved if you round the values:

pd.to_datetime(df['Date']).diff().dt.total_seconds().div(60).round().ne(1)

However, there is a much better way, use the Timedelta comparison properties:

df['g'] = pd.to_datetime(df['Date']).diff().ne('1min')

output:

                  Date  meter      g
0  2009-02-13 13:23:00  53.49   True
1  2009-02-13 13:24:00  64.91  False
2  2009-02-13 13:25:00  32.04  False
3  2009-02-13 13:26:00  45.94  False
4  2009-02-13 15:45:00  45.94   True

For your initial question (first and last of group):

forward = pd.to_datetime(df['Date']).diff().ne('1min')
reverse = (-pd.to_datetime(df['Date']).diff(-1)).ne('1min')
df['g'] = forward|reverse

output:

                  Date  meter      g
0  2009-02-13 13:23:00  53.49   True
1  2009-02-13 13:24:00  64.91  False
2  2009-02-13 13:25:00  32.04  False
3  2009-02-13 13:26:00  45.94   True
user_163434640
Related