Of a column, get the indices of first and last occurrences in certain context

user_6206512 0
Viewed 56

df

index y position result
0 1
1 2
2 3
3 2
4 1
5 0 3
6 2
7 3
8 2 1
9 5
10 0 5
11 0 *

* = Special value to be filled later

I'm calculating the total distance taken only when its y coordinate values drop. When it's decreasing subsequently, I only take the last index that has all the previous (continued) distance summed up (as shown in the column desired result).

If the distances are the same subsequently, then I would like to put in some unique value so that I can replace all of them later with one value.

There are millions of rows to go through so for loop will take too long. Is there be a faster way without using a for loop?

pandas dataframe indexing python
1 Answers

Let's try cumsum to identify blocks of continuous strictly decreasing values:

# Δy
d = df['y position'].diff()

# Δy < 0
m = d.lt(0)

# identify blocks 
b = (~m).cumsum()

# group + transform
s = d.mask(~m).abs().groupby(b).transform('sum')

# select last values per group
df['result'] = np.select([~b.duplicated(keep='last') & m, d.eq(0)], [s, '*'], '')

Details:

Calculate Series.diff on y position:

print(d)

0     NaN
1     1.0
2     1.0
3    -1.0
4    -1.0
5    -1.0
6     2.0
7     1.0
8    -1.0
9     3.0
10   -5.0
11    0.0
Name: y position, dtype: float64

Identify continuous blocks of strictly decreasing values using Series.cumsum:

print(b)

0     1
1     2
2     3
3     3
4     3
5     3
6     4
7     5
8     5
9     6
10    6
11    7
Name: y position, dtype: int64

group the consecutive differences on continuous blocks (b) and transform using sum:

print(s)

0     0.0
1     0.0
2     3.0
3     3.0
4     3.0
5     3.0
6     0.0
7     1.0
8     1.0
9     5.0
10    5.0
11    0.0
Name: y position, dtype: float64

Then use np.select with conditions and corresponding choices so as to keep only the last summed distance per block of continuous decreasing values:

print(df)

    y position   result
0            1        
1            2        
2            3        
3            2        
4            1        
5            0     3.0
6            2        
7            3        
8            2     1.0
9            5        
10           0     5.0
11           0       *
user_128331660
Related