CODEWITHSUNDEEP
pythonpandas
Jack
Jack

Reputation: 1754

how to get min values of columns by rolling another columns?

    GROUP_NAV_DATE  GROUP_REH_VALUE target
0   2018/11/29  1       1.06
1   2018/11/30  1.0029  1.063074
2   2018/12/3   1.03    1.0918
3   2018/12/4   1.032   1.09392
4   2018/12/5   1.0313  1.093178
5   2020/12/6   1.034   1.09604
6   2020/12/8   1.062   1.12572
7   2020/12/9   1.07    1.1342
8   2020/12/10  1       1.06
9   2020/12/11  0.99    1.0494
10  2020/12/12  0.96    1.0176
11  2020/12/13  1.062   1.12572

goal

Note: for each row, target values is 1.06*GROUP_REH_VALUE.

Expect

GROUP_NAV_DATE  GROUP_REH_VALUE target  first_date
0   2018/11/29  1   1.06    2020/12/8
1   2018/11/30  1.0029  1.063074    2020/12/9
2   2018/12/3   1.03    1.0918  NA
3   2018/12/4   1.032   1.09392 NA
4   2018/12/5   1.0313  1.093178    NA
5   2020/12/6   1.034   1.09604 NA
6   2020/12/8   1.062   1.12572 NA
7   2020/12/9   1.07    1.1342  NA
8   2020/12/10  1   1.06    2020/12/13
9   2020/12/11  0.99    1.0494  2020/12/13
10  2020/12/12  0.96    1.0176  2020/12/13
11  2020/12/13  1.062   1.12572 NA

Try

Upvotes: 0

Views: 56

Answers (1)

Corralien
Corralien

Reputation: 120559

You can use expanding but this code works only because:

  1. There is a direct relation between GROUP_REH_VALUE and target columns 1.06*GROUP_REH_VALUE so the target column is useless.

  2. You have a numeric index because expanding checks if the return value is numeric else you will raise an TypeError: must be real number, not str if GROUP_NAV_DATE is the index.

def f(sr):
    m = sr.iloc[-1]*1.06 < sr
    return sr[m].last_valid_index() if sum(m) else np.nan

# Need to reverse dataframe because you are looking forward.
idx = df.loc[::-1, 'GROUP_REH_VALUE'].expanding().apply(f).dropna()

# Set dates
df.loc[idx.index, 'first_time'] = df.loc[idx, 'GROUP_NAV_DATE'].tolist()

Output:

>>> df
   GROUP_NAV_DATE  GROUP_REH_VALUE    target  first_time
0      2018/11/29           1.0000  1.060000   2020/12/8
1      2018/11/30           1.0029  1.063074   2020/12/9
2       2018/12/3           1.0300  1.091800         NaN
3       2018/12/4           1.0320  1.093920         NaN
4       2018/12/5           1.0313  1.093178         NaN
5       2020/12/6           1.0340  1.096040         NaN
6       2020/12/8           1.0620  1.125720         NaN
7       2020/12/9           1.0700  1.134200         NaN
8      2020/12/10           1.0000  1.060000  2020/12/13
9      2020/12/11           0.9900  1.049400  2020/12/13
10     2020/12/12           0.9600  1.017600  2020/12/13
11     2020/12/13           1.0620  1.125720         NaN

Upvotes: 1

Related Questions