is there a way to groupby bulks using python pandas?

user_11041555 0
Viewed 38

I have a DataFrame of different time series of different Trials of an experiment. the data contains columns of a vehicle's acceleration state. the states are -1 for deceleration, 0 for brake, 1 for acceleration.

I want to make a summary of every -1,0,1, and a need a way to group all the adjacent 1's while disregarding the other 1's. and the same for 0 and -1.

for example: this is what I have:

X axis acceleration X axis state
0 0
0 0
0 0
0.87 1
0.88 1
0 0
-0.28 -1
-0.27 -1
0 0
0 0
0.46 1
0.23 1

This what I want to get:

mean X axis acceleration X axis state event number
0 0 1
0.875 1 2
0 0 3
-0.275 -1 4
0 0 5
0.345 1 6
pandas-groupby pandas time-series python
1 Answers

Create groups by consecutive values of column X axis state and aggregate mean, last change order of columns by list:

g = df['X axis state'].ne(df['X axis state'].shift()).cumsum().rename('event number')

df = df.groupby([g, 'X axis state'])['X axis acceleration'].mean().reset_index()
df = df[['X axis acceleration','X axis state','event number']]

print (df)
   X axis acceleration  X axis state  event number
0                0.000             0             1
1                0.875             1             2
2                0.000             0             3
3               -0.275            -1             4
4                0.000             0             5
5                0.345             1             6

EDIT: If compare by shifted values get Trues if values are changed:

print (df['X axis state'].ne(df['X axis state'].shift()))
0      True
1     False
2     False
3      True
4     False
5      True
6      True
7     False
8      True
9     False
10     True
11    False
Name: X axis state, dtype: bool

So if add cunmulative sum get groups:

print (df['X axis state'].ne(df['X axis state'].shift()).cumsum().rename('event number'))
0     1
1     1
2     1
3     2
4     2
5     3
6     4
7     4
8     5
9     5
10    6
11    6
Name: event number, dtype: int32
user_29010020
Related