Split time series into equally sized "slices" based on one column

user_14500576 0
Viewed 118

I have a time series with 3 sensors, one of them is a proximity switch that tells me revolutions.

Depending at what speed the machine is spinning, the distance in time of the "blips" in this sensor will change, however, regardless of how far apart these blips are, I want to divide all the data that occurred between them in 10 equally sized blocks.

I understand that the exact number of rows might not be divisible by 10 exactly but that's fine.

  Timestamp     Sensor_1  Sensor_2  Sensor_3
1636496284130    60.875    35.946       0
1636496284132    58.467    33.889       0
1636496284134    58.668    37.253       0
1636496284136    57.966    30.540       0
1636496284138    56.712    33.254       0

I would like to have the resulting DataFrame to have an additional Label column with what interval is taking place.

  Timestamp     Sensor_1  Sensor_2  Sensor_3    Label
1636496284130    60.875    35.946       0         A
1636496284132    58.467    33.889       0         A
1636496284134    58.668    37.253       0         A
1636496284136    57.966    30.540       0         A
1636496284138    56.712    33.254       0         A

Here's a visual of what I'm trying to achieve.

enter image description here

pandas time-series python
2 Answers

Supposing df is your input dataframe and you want to split it in n_parts (i.e. only 2 for sake of demonstration):

# Number of equally sized blocks
n_parts = 2

# Number of blocks needed to achieve that
n_blocks = df.shape[0] / n_parts

df['Label'] = ['{}'.format(i//n_blocks) for i in range(0, df.shape[0])]

# Output
    Timestamp       Sensor_1    Sensor_2    Sensor_3    Label
0   1636496284130   60.875       35.946        0        0.0
1   1636496284132   58.467       33.889        0        0.0
2   1636496284134   58.668       37.253        0        0.0
3   1636496284136   57.966       30.540        0        1.0
4   1636496284138   56.712       33.254        0        1.0

The // (floor operator) makes sure you get the same label if the current i is less than n_blocks:

# n_blocks is 2.5 here, therefore at i=3 we will see the value 1.0

print(['{}'.format(i//n_blocks) for i in range(0, df.shape[0])])

['0.0', '0.0', '0.0', '1.0', '1.0']
user_108523960

What I ended up doing is breaking the DataFrame into a series of smaller chunks based on Sensor_3. Since it first goes up and then down what I did was only detect when it drops and treat that as a boolean flag.

def sensor3flag(val):
    if val >= 0:
        return 0
    else:
        return 1


df['Sensor_3_diff'] = df['Sensor_3'].diff().bfill(0).apply(lambda x:sensor3flag(x))

Then I went ahead and created a helper list with all the indexes when "Sensor_3_diff" == 1.

df_locs = dfi[dfi['Sensor_3_diff'] == 1]

locList = list(df_locs.index)
locList.insert(0,0)

Lastly I'd loop over that list of indexes and slice the main DataFrame accordingly, then I'd split each of these slices into 10 equal parts based on the length of the slice. Lastly append it to another final dataframe.

(labelAssigner() is a method that takes two parameters and returns a label based on them. The first parameter is the index of the record, the second is the size of the slice DataFrame)

df_final = pd.DataFrame()

for i in range(0,len(locList)-1):
    sl = dfi.iloc[locList[i]:locList[i+1]]
    sl.reset_index(inplace=True, drop=True)
    sl['Index'] = sl.index
    sl['Label'] = sl['Index'].apply(lambda x:labelAssigner(x, sl.shape[0]))
    df_final = df_final.append(sl)
user_145005760
Related