CODEWITHSUNDEEP
pythonpandasdataframe
Kshitij Sachan
Kshitij Sachan

Reputation: 45

How to dynamically remove rows from a DataFrame Pandas

I have the following NFL tracking data:

    Event  PlayId  FrameId   x-coord  y-coord
0   Start       1       1       20.2     20.0
1     NaN       1       2       21.0     19.1
2     NaN       1       3       21.3     18.3
3     NaN       1       4       22.0     17.5
4     End       1       5       22.5     17.2
4     NaN       1       6       22.5     17.2
4     NaN       1       7       22.5     17.2
4     NaN       1       8       22.5     17.2
4     NaN       1       9       22.5     17.2
4     NaN       1       10      22.5     17.2
5     NaN       2       1       23.0     16.9
6   Start       2       2       23.6     16.7
7     End       2       3       25.1     34.1
8     NaN       2       4       25.9     34.2
10    NaN       3       1       22.7     34.2
11    Nan       3       2       21.5     34.5
12    NaN       3       3       21.1     37.3
13  Start       3       4       21.2     44.3
14    NaN       3       5       20.4     44.6
15    End       3       6       21.9     42.7

How can I filter this list to only get the rows in between the "Start" and "End" values for the Event column? To clarify, this is the data I want to filter for:

    Event  PlayId  FrameId   x-coord  y-coord
0   Start       1       1       20.2     20.0
1     NaN       1       2       21.0     19.1
2     NaN       1       3       21.3     18.3
3     NaN       1       4       22.0     17.5
4     End       1       5       22.5     17.2
6   Start       2       2       23.6     16.7
7     End       2       3       25.1     34.1
13  Start       3       4       21.2     44.3
14    NaN       3       5       20.4     44.6
15    End       3       6       21.9     42.7

An explicit solution will not work because the actual dataset is very large and there is no way to predict where the Start and End values fall.

Upvotes: 1

Views: 153

Answers (1)

BENY
BENY

Reputation: 323396

Doing with slice and ffill then concat back , Also you have Nan in your df , should it be NaN ? 

df1=df.copy()

newdf=pd.concat([df1[df.Event.ffill()=='Start'],df1[df.Event=='End']]).sort_index()
newdf
    Event  PlayId  FrameId  x-coord  y-coord
0   Start       1        1     20.2     20.0
1     NaN       1        2     21.0     19.1
2     NaN       1        3     21.3     18.3
3     NaN       1        4     22.0     17.5
4     End       1        5     22.5     17.2
6   Start       2        2     23.6     16.7
7     End       2        3     25.1     34.1
13  Start       3        4     21.2     44.3
14    NaN       3        5     20.4     44.6
15    End       3        6     21.9     42.7

Or 

newdf=df[~((df.Event.ffill()=='End')&(df.Event.isna()))]
newdf
    Event  PlayId  FrameId  x-coord  y-coord
0   Start       1        1     20.2     20.0
1     NaN       1        2     21.0     19.1
2     NaN       1        3     21.3     18.3
3     NaN       1        4     22.0     17.5
4     End       1        5     22.5     17.2
6   Start       2        2     23.6     16.7
7     End       2        3     25.1     34.1
13  Start       3        4     21.2     44.3
14    NaN       3        5     20.4     44.6
15    End       3        6     21.9     42.7

Upvotes: 2

Related Questions