1
\$\begingroup\$

Goal

I want to get the index of the min of last 3 rows based on low column.

     date   hy_code low
0   1990/12/19  A   95.79
1   1990/12/20  A   99.98
2   1990/12/21  A   103.73
3   1990/12/24  A   109.13
4   1990/12/25  A   114.55
5   1990/12/26  A   120.25
6   1990/12/27  A   125.27
7   1990/12/28  A   125.28
8   1990/12/31  A   126.48
9   1991/1/2    A   127.61
10  1990/12/19  B   95.79
11  1990/12/20  B   99.98
12  1990/12/21  B   103.73
13  1990/12/24  B   109.13
14  1990/12/25  B   114.55
15  1990/12/26  B   120.25
16  1990/12/27  B   125.27
17  1990/12/28  B   125.28
18  1990/12/31  B   126.48
19  1991/1/2    B   127.61

Try

df.assign(l1m_lowest_idx=df.rolling(3)['low'].agg(lambda x:x.idxmin()))

It runs slow when data become large.

\$\endgroup\$
1
  • \$\begingroup\$ Other than the speed, does your "try" do what you want it to? \$\endgroup\$
    – Reinderien
    Commented Feb 2, 2022 at 13:06

1 Answer 1

1
\$\begingroup\$

So far as I can tell, your "try" does indeed do what you describe: an index-of-rolling-minimum. You're also correct to identify that it has efficiency issues; whenever a lambda appears in Pandas that spells trouble.

Unfortunately, I don't see a good, built-in way to do this in Pandas. What I'm going to suggest is that you drop down to Numpy, which should be able to do this efficiently:

  • Create a padded array where the first few values are equal to the first value of low
  • Use sliding_window_view to get your rolling effect, vectorised over a two-dimensional array
  • Apply a vectorised argmin over one axis

Also take care to have the output column be integer-typed, which it was not.

What you're "actually doing"

A word of caution: what you're doing currently probably doesn't make sense. Your sliding window steamrolls over a hy_code boundary with a chronological discontinuity. I would expect date and hy_code to form a multi-index, and for your rolling min to respect that, which it doesn't. I have not shown how to handle this.

Suggested

In practice this would look like:

import numpy as np
import pandas as pd


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

n = len(df)
WINDOW = 3
padded = np.empty(n + WINDOW - 1)
padded[WINDOW:] = df.low.values[1:]

# Rather than NaN for the partial-window start, just fill with the first value
padded[:WINDOW] = df.low.values[0]

# Will be of shape n x WINDOW, each row shifting over by one more index
slid = np.lib.stride_tricks.sliding_window_view(padded, window_shape=WINDOW)

# Get minimum indices within each window, and then convert from a relative
# offset to an absolute offset
min_offsets = np.argmin(slid, axis=1) + np.arange(n) - WINDOW + 1

# Rather than NaN for the starting values, apply the minimum to the partial window.
# This requires a piecewise operation on the first few indices; otherwise they'll
# be negative.
min_offsets[:WINDOW] = np.clip(min_offsets[:WINDOW], a_min=0, a_max=None)
df['l1m_lowest_idx'] = min_offsets
print(df)

Output

          date hy_code     low  l1m_lowest_idx
0   1990/12/19       A   95.79               0
1   1990/12/20       A   99.98               0
2   1990/12/21       A  103.73               0
3   1990/12/24       A  109.13               1
4   1990/12/25       A  114.55               2
5   1990/12/26       A  120.25               3
6   1990/12/27       A  125.27               4
7   1990/12/28       A  125.28               5
8   1990/12/31       A  126.48               6
9     1991/1/2       A  127.61               7
10  1990/12/19       B   95.79              10
11  1990/12/20       B   99.98              10
12  1990/12/21       B  103.73              10
13  1990/12/24       B  109.13              11
14  1990/12/25       B  114.55              12
15  1990/12/26       B  120.25              13
16  1990/12/27       B  125.27              14
17  1990/12/28       B  125.28              15
18  1990/12/31       B  126.48              16
19    1991/1/2       B  127.61              17
\$\endgroup\$
3
  • 1
    \$\begingroup\$ Very nice. With 10K rows on my machine, striding takes 219 µs ± 28.8 µs while rolling takes 826 ms ± 49.8 ms. \$\endgroup\$
    – tdy
    Commented Feb 2, 2022 at 18:35
  • \$\begingroup\$ @Reinderien I wanna keep np.nan so just not run padded[:WINDOW] = df.low.values[0]? \$\endgroup\$
    – Jack
    Commented Feb 3, 2022 at 12:25
  • \$\begingroup\$ What is the motivation for keeping NaN? If the very first output value considers a window size of one, is the minimum of that window not the first element? \$\endgroup\$
    – Reinderien
    Commented Feb 3, 2022 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.