Python pandas calculates the time until a value in a column is greater than in the current period

Question

I have a pandas dataframe in python with multiple columns and a date and time stamp. I want to create a new column that calculates the time to output less than in the current period.

My current table looks something like this:

 datetime               output
 2014-05-01 01:00:00    3
 2014-05-01 01:00:01    2
 2014-05-01 01:00:02    3
 2014-05-01 01:00:03    2
 2014-05-01 01:00:04    1

I am trying to make the table have an extra column and look like this:

 datetime               output     secondsuntildecrease
 2014-05-01 01:00:00    3         1
 2014-05-01 01:00:01    2         3
 2014-05-01 01:00:02    3         1
 2014-05-01 01:00:03    2         1
 2014-05-01 01:00:04    1         

early!

Answer 1

upper_triangle = np.triu(df.output.values < df.output.values[:, None])
df['s_until_dec'] = df['datetime'][upper_triangle.argmax(axis=1)].values - df['datetime']
df.loc[~upper_triangle.any(axis=1), 's_until_dec'] = np.nan
df
             datetime  output           s_until_dec
0 2014-05-01 01:00:00       3              00:00:01
1 2014-05-01 01:00:01       2              00:00:03
2 2014-05-01 01:00:02       3              00:00:01
3 2014-05-01 01:00:03       2              00:00:01
4 2014-05-01 01:00:04       1                   NaT

Here's how it works:

df.output.values < df.output.values[:, None]this creates a paired comparison matrix with brodcasting ( [:, None]creates a new axis):

df.output.values < df.output.values[:, None]
Out: 
array([[False,  True, False,  True,  True],
       [False, False, False, False,  True],
       [False,  True, False,  True,  True],
       [False, False, False, False,  True],
       [False, False, False, False, False]], dtype=bool)

, , output[0] , output[1], (0, 1) True. , np.triu . argmax() True. iloc, . , . False, np.nan. .loc np.nan.

Answer 2

df = pd.DataFrame([3, 2, 3, 2, 1], index=pd.DatetimeIndex(start='2014-05-01 01:00:00', periods=5, freq='S'), columns=['output'])

def f(s):
    s = s[s & (s.index > s.name)]
    if s.empty:
        return np.nan
    else:
        return (s.index[0] - s.name).total_seconds()

df['secondsuntildecrease'] = df['output'].apply(lambda x: df['output'] < x).apply(f, axis=1)

df

                     output  secondsuntildecrease
2014-05-01 01:00:00       3                   1.0
2014-05-01 01:00:01       2                   3.0
2014-05-01 01:00:02       3                   1.0
2014-05-01 01:00:03       2                   1.0
2014-05-01 01:00:04       1                   NaN

Answer 3

df['seconds_until'] = df.apply(lambda x: pd.to_datetime(df.loc[(df['output'] < x['output']) & (df['datetime'] > x['datetime']), 'datetime'].min()) - pd.to_datetime(x[
'datetime']), axis=1)

              datetime  output  seconds_until
0  2014/05/01 01:00:00       3       00:00:01
1  2014/05/01 01:00:01       2       00:00:03
2  2014/05/01 01:00:02       3       00:00:01
3  2014/05/01 01:00:03       2       00:00:01
4  2014/05/01 01:00:04       1            NaT

Answer 4

numpy external subtract .

numpy triangle, , .

numpy, , False

, .

df = pd.DataFrame(
    dict(output=[3, 2, 3, 2, 1],
         datetime=pd.DatetimeIndex(start='2014-05-01 01:00:00', periods=5, freq='S'))
)

gt0 = np.triu(np.subtract.outer(df.output, df.output), 1) > 0
idx = np.where(gt0.any(1), gt0.argmax(1), np.nan)
-(df.datetime - df.loc[idx, 'datetime'].values).dt.total_seconds()

0    1.0
1    3.0
2    1.0
3    1.0
4    NaN
Name: datetime, dtype: float64

---

Timing

Mine and ayhan seem to be most effective compared to a small sample

ayhan best over 10,000 lines