Is there a faster way for finding the range of constant values in a dataframe?

user_11241651 0
Viewed 190

I want to find the longest duration of the constant values in a dataframe. For example, given a dataframe below, the longest duration should be 30 minutes (when value = 2).

import pandas as pd

d = {'date_time': ['2016-01-01 12:00:00', '2016-01-01 12:15:00',
       '2016-01-01 12:30:00', '2016-01-01 12:45:00',
       '2016-01-01 13:00:00', '2016-01-01 13:15:00',
       '2016-01-01 13:30:00', '2016-01-01 13:45:00'], 
     'value': [1,2,2,2,4,5,5,7]}

df = pd.DataFrame(data=d)

df['date_time'] = pd.to_datetime(df['date_time'])

print(df)
            date_time  value
0 2016-01-01 12:00:00      1
1 2016-01-01 12:15:00      2
2 2016-01-01 12:30:00      2
3 2016-01-01 12:45:00      2
4 2016-01-01 13:00:00      4
5 2016-01-01 13:15:00      5
6 2016-01-01 13:30:00      5
7 2016-01-01 13:45:00      7

(Note: the date_time interval is not always consistent.)

I managed to find it by finding the indexes of df.value.diff().abs()==0, build a complex function to iterate through that list and compute the range.

Since the actual dataframe is much larger than this example, is there a shortcut function or a faster way to get this without multiple iterations?

Thank you.

EDIT:

In my case, the same value can appear in other streaks. A more appropriate example would be

d = {'date_time': ['2016-01-01 12:00:00', '2016-01-01 12:15:00',
       '2016-01-01 12:30:00', '2016-01-01 12:45:00',
       '2016-01-01 13:00:00', '2016-01-01 13:15:00',
       '2016-01-01 13:30:00', '2016-01-01 13:45:00',
       '2016-01-01 14:00:00', '2016-01-01 14:05:00'], 
     'value': [1,2,2,2,4,5,5,7,5,5]}

df = pd.DataFrame(data=d)

df['date_time'] = pd.to_datetime(df['date_time'])

print(df)

            date_time  value
0 2016-01-01 12:00:00      1
1 2016-01-01 12:15:00      2
2 2016-01-01 12:30:00      2
3 2016-01-01 12:45:00      2
4 2016-01-01 13:00:00      4
5 2016-01-01 13:15:00      5
6 2016-01-01 13:30:00      5
7 2016-01-01 13:45:00      7
8 2016-01-01 14:00:00      5
9 2016-01-01 14:05:00      5

The longest duration, in this case, remains 30 minutes when value = 2.

pandas dataframe datetime python
3 Answers

groupby + nlargest

Create a grouping series that tracks changes.

groupr = df.value.ne(df.value.shift()).cumsum()

Create a mapping dictionary that can translate from the groupr key to the actual value in the df.value column.

mapper = dict(zip(groupr, df.value))

Now we group and use ptp and nlargest. Finally, we use rename and mapper to translate the index value, which is the groupr value, back to the value value (phew, that's a tad confusing).

df.groupby(groupr).date_time.apply(np.ptp).nlargest(1).rename(mapper)

value
2   0 days 00:30:00
Name: date_time, dtype: timedelta64[ns]

The 2 in the index is the value with the longest duration. The 0 days 00:30:00 is the longest duration.

References

np.ptp
nlargest

user_23366540

You can groupby the value column and use .size() to get the size/length of each group.

>>> groups = df.groupby('value')
>>> groups.size()
value
1    1
2    3
4    1
5    2
7    1
dtype: int64

.idxmax() will give you the index of the largest group which you can pass to .get_groups()

>>> groups.get_group(groups.size().idxmax())
             date_time  value
1  2016-01-01 12:15:00      2
2  2016-01-01 12:30:00      2
3  2016-01-01 12:45:00      2

Then you can diff the last and first dates (assuming they are sorted - if not you can sort them)

>>> max_streak = groups.get_group(groups.size().idxmax())
>>> max_streak.iloc[-1].date_time - max_streak.iloc[0].date_time
Timedelta('0 days 00:30:00')

If value can repeat in other streaks you can groupby using:

groups = df.groupby((df.value != df.value.shift()).cumsum())

Update: Maximum duration of any streak

>>> groups = df.groupby((df.value != df.value.shift()).cumsum())
>>> last = groups.last()
>>> max_duration = (last.date_time - groups.first().date_time).nlargest(1)
>>> max_duration.iat[0]
Timedelta('0 days 00:30:00')
>>> last.loc[max_duration.index].value.iat[0]
2
Admin1

You could use pd.pivot_table to get the minimum and maximum datetime value for each value, then calculate the duration between them and extract the longest.

import pandas as pd
import numpy as np

d = {'date_time': ['2016-01-01 12:00:00', '2016-01-01 12:15:00',
       '2016-01-01 12:30:00', '2016-01-01 12:45:00',
       '2016-01-01 13:00:00', '2016-01-01 13:15:00',
       '2016-01-01 13:30:00', '2016-01-01 13:45:00'], 
     'value': [1,2,2,2,4,5,5,7]}

df = pd.DataFrame(data=d)

df['date_time'] = pd.to_datetime(df['date_time'])

df_pivot = pd.pivot_table(df, index='value', values='date_time', aggfunc=[np.min,np.max])

df_pivot['duration'] = df_pivot.iloc[:, 1] - df_pivot.iloc[:, 0]

print(df_pivot[df_pivot['duration'] == max(df_pivot['duration'])])
user_28500260
Related