python: grouping or splitting up time series data based on conditions

Question

I work a lot with time series data at my job and I have been trying to use python--specifically pandas--to make some of the work a little faster. I have some code that reads through data in a DataFrame and identifies segments where specified conditions are met. It then separates those segments into individual DataFrames.

I have a sample DataFrame here:

        Date      Time  Pressure   Temp  Flow  Valve Position
0   3/5/2020  12:00:01      5.32  22.12   199            1.00
1   3/5/2020  12:00:02      5.36  22.25   115            0.95
2   3/5/2020  12:00:03      5.33  22.18   109            0.92
3   3/5/2020  12:00:04      5.38  23.51   103            0.90
4   3/5/2020  12:00:05      5.42  24.27    99            0.89
5   3/5/2020  12:00:06      5.49  25.91    92            0.85
6   3/5/2020  12:00:07      5.55  26.78    85            0.82
7   3/5/2020  12:00:08      5.61  29.88    82            0.76
8   3/5/2020  12:00:09      5.69  31.16    87            0.79
9   3/5/2020  12:00:10      5.72  32.01    97            0.87
10  3/5/2020  12:00:11      5.59  29.68   104            0.90
11  3/5/2020  12:00:12      5.53  24.55   111            0.93
12  3/5/2020  12:00:13      5.48  23.54   116            0.96
13  3/5/2020  12:00:14      5.44  23.11   119            1.00
14  3/5/2020  12:00:15      5.41  23.08   121            1.00

The code I have written does what I want but is really difficult to follow and I am sure its offensive to experienced python users.

Here is what it does though: I more or less create a mask based on a set of conditions and I take the index locations for all the True values in the mask. Then it uses NumPy's .diff() function to identify discontinuity in the indices. Inside the for loop it splits up the mask at the location of each identified discontinuity. Once that is complete I can use the now separate sets of indices to slice out the desired segments of data from my original DataFrame. See the code below:

import pandas as pd
import numpy as np

df = pd.read_csv('sample_data.csv')

idx = np.where((df['Temp'] > 23) & (df['Temp'] < 30))[0]
discontinuity = np.where(np.diff(idx) > 1)[0]
intervals = {}

for i in range(len(discontinuity)+1):
    if i == 0:
        intervals[i] = df.iloc[idx[0]:idx[discontinuity[i]],1]
        if len(intervals[i].values) < 1:
            del intervals[i]
    elif i == len(discontinuity):
        intervals[i] = df.iloc[idx[discontinuity[i-1]+1]:idx[-1],1]
        if len(intervals[i].values) < 1:
            del intervals[i]
    else:
        intervals[i] = df.iloc[idx[discontinuity[i-1]+1]:idx[discontinuity[i]],1] 
        if len(intervals[i].values) < 1:
            del intervals[i]


df1 = df.loc[intervals[0].index, :]
df2 = df.loc[intervals[1].index, :]

df1 and df2 contain all the data in the original DataFrame corresponding with the times (rows) that 'Temp' is between 23 and 30.

df1:

       Date      Time  Pressure   Temp  Flow  Valve Position
3  3/5/2020  12:00:04      5.38  23.51   103            0.90
4  3/5/2020  12:00:05      5.42  24.27    99            0.89
5  3/5/2020  12:00:06      5.49  25.91    92            0.85
6  3/5/2020  12:00:07      5.55  26.78    85            0.82

df2:

        Date      Time  Pressure   Temp  Flow  Valve Position
10  3/5/2020  12:00:11      5.59  29.68   104            0.90
11  3/5/2020  12:00:12      5.53  24.55   111            0.93
12  3/5/2020  12:00:13      5.48  23.54   116            0.96
13  3/5/2020  12:00:14      5.44  23.11   119            1.00

I am glad I was able to get this to work for me and I can live with the couple lines that get lost using this method but I know this is a really pedestrian approach and I can't help but think someone who is not a python beginning could do the same thing much more cleanly and efficiently.

Could groupby from itertools or pandas work for this? I haven't been able to find a way to make that work.

user_10841164 · Answer

Welcome to Stack Overflow.

I think your code can be simplified as such:

# Get the subset that fulfills your conditions

df_conditioned = df.query('Temp > 23 and Temp < 30').copy()

# Check for discontinuities by looking at the indices
# I created a new column called 'Group' to keep track of the continuous indices

indices = df_conditioned.index.to_series()
df_conditioned['Group'] = ((indices - indices.shift(1)) != 1).cumsum()

# Store the groups (segments with same group number) as individual frames in a list

df_list = []
for group in df_conditioned['Group'].unique():
    df_list.append(df_conditioned.query('Group == @group').drop(columns='Group'))

Hope it helps!

python: grouping or splitting up time series data based on conditions

1 Answers