Dataframe: How to get the minimum number between 2 identical numbers in the same column?-CodePudding

Definitely easier to show via an example:

{'Close': {Timestamp('1980-12-12 00:00:00'): 28.75, Timestamp('1980-12-15 00:00:00'): 27.25, Timestamp('1980-12-16 00:00:00'): 25.25, Timestamp('1980-12-17 00:00:00'): 25.87, Timestamp('1980-12-18 00:00:00'): 26.63, Timestamp('1980-12-19 00:00:00'): 28.25, Timestamp('1980-12-22 00:00:00'): 29.63, Timestamp('1980-12-23 00:00:00'): 30.88, Timestamp('1980-12-24 00:00:00'): 32.5, Timestamp('1980-12-26 00:00:00'): 35.5, Timestamp('1980-12-29 00:00:00'): 36.0, Timestamp('1980-12-30 00:00:00'): 35.12, Timestamp('1980-12-31 00:00:00'): 34.13, Timestamp('1981-01-02 00:00:00'): 34.5, Timestamp('1981-01-05 00:00:00'): 33.75, Timestamp('1981-01-06 00:00:00'): 32.25, Timestamp('1981-01-07 00:00:00'): 30.88, Timestamp('1981-01-08 00:00:00'): 30.25, Timestamp('1981-01-09 00:00:00'): 31.88, Timestamp('1981-01-12 00:00:00'): 31.62, Timestamp('1981-01-13 00:00:00'): 30.5, Timestamp('1981-01-14 00:00:00'): 30.63, Timestamp('1981-01-15 00:00:00'): 31.25, Timestamp('1981-01-16 00:00:00'): 31.0, Timestamp('1981-01-19 00:00:00'): 32.87, Timestamp('1981-01-20 00:00:00'): 31.88, Timestamp('1981-01-21 00:00:00'): 32.5, Timestamp('1981-01-22 00:00:00'): 32.87, Timestamp('1981-01-23 00:00:00'): 32.75, Timestamp('1981-01-26 00:00:00'): 32.25}, 'Roll_Max': {Timestamp('1980-12-12 00:00:00'): 28.75, Timestamp('1980-12-15 00:00:00'): 28.75, Timestamp('1980-12-16 00:00:00'): 28.75, Timestamp('1980-12-17 00:00:00'): 28.75, Timestamp('1980-12-18 00:00:00'): 28.75, Timestamp('1980-12-19 00:00:00'): 28.75, Timestamp('1980-12-22 00:00:00'): 29.63, Timestamp('1980-12-23 00:00:00'): 30.88, Timestamp('1980-12-24 00:00:00'): 32.5, Timestamp('1980-12-26 00:00:00'): 35.5, Timestamp('1980-12-29 00:00:00'): 36.0, Timestamp('1980-12-30 00:00:00'): 36.0, Timestamp('1980-12-31 00:00:00'): 36.0, Timestamp('1981-01-02 00:00:00'): 36.0, Timestamp('1981-01-05 00:00:00'): 36.0, Timestamp('1981-01-06 00:00:00'): 36.0, Timestamp('1981-01-07 00:00:00'): 36.0, Timestamp('1981-01-08 00:00:00'): 36.0, Timestamp('1981-01-09 00:00:00'): 36.0, Timestamp('1981-01-12 00:00:00'): 36.0, Timestamp('1981-01-13 00:00:00'): 36.0, Timestamp('1981-01-14 00:00:00'): 36.0, Timestamp('1981-01-15 00:00:00'): 36.0, Timestamp('1981-01-16 00:00:00'): 36.0, Timestamp('1981-01-19 00:00:00'): 36.0, Timestamp('1981-01-20 00:00:00'): 36.0, Timestamp('1981-01-21 00:00:00'): 36.0, Timestamp('1981-01-22 00:00:00'): 36.0, Timestamp('1981-01-23 00:00:00'): 36.0, Timestamp('1981-01-26 00:00:00'): 36.0}, 'Daily_Drawdown': {Timestamp('1980-12-12 00:00:00'): 0.0, Timestamp('1980-12-15 00:00:00'): -0.05217391304347829, Timestamp('1980-12-16 00:00:00'): -0.12173913043478257, Timestamp('1980-12-17 00:00:00'): -0.10017391304347822, Timestamp('1980-12-18 00:00:00'): -0.07373913043478264, Timestamp('1980-12-19 00:00:00'): -0.017391304347826098, Timestamp('1980-12-22 00:00:00'): 0.0, Timestamp('1980-12-23 00:00:00'): 0.0, Timestamp('1980-12-24 00:00:00'): 0.0, Timestamp('1980-12-26 00:00:00'): 0.0, Timestamp('1980-12-29 00:00:00'): 0.0, Timestamp('1980-12-30 00:00:00'): -0.02444444444444449, Timestamp('1980-12-31 00:00:00'): -0.05194444444444435, Timestamp('1981-01-02 00:00:00'): -0.04166666666666663, Timestamp('1981-01-05 00:00:00'): -0.0625, Timestamp('1981-01-06 00:00:00'): -0.10416666666666663, Timestamp('1981-01-07 00:00:00'): -0.14222222222222225, Timestamp('1981-01-08 00:00:00'): -0.1597222222222222, Timestamp('1981-01-09 00:00:00'): -0.11444444444444446, Timestamp('1981-01-12 00:00:00'): -0.12166666666666659, Timestamp('1981-01-13 00:00:00'): -0.1527777777777778, Timestamp('1981-01-14 00:00:00'): -0.14916666666666667, Timestamp('1981-01-15 00:00:00'): -0.13194444444444442, Timestamp('1981-01-16 00:00:00'): -0.13888888888888884, Timestamp('1981-01-19 00:00:00'): -0.08694444444444449, Timestamp('1981-01-20 00:00:00'): -0.11444444444444446, Timestamp('1981-01-21 00:00:00'): -0.09722222222222221, Timestamp('1981-01-22 00:00:00'): -0.08694444444444449, Timestamp('1981-01-23 00:00:00'): -0.09027777777777779, Timestamp('1981-01-26 00:00:00'): -0.10416666666666663}, 'Max_Daily_Drawdown': {Timestamp('1980-12-12 00:00:00'): 0.0, Timestamp('1980-12-15 00:00:00'): -0.05217391304347829, Timestamp('1980-12-16 00:00:00'): -0.12173913043478257, Timestamp('1980-12-17 00:00:00'): -0.12173913043478257, Timestamp('1980-12-18 00:00:00'): -0.12173913043478257, Timestamp('1980-12-19 00:00:00'): -0.12173913043478257, Timestamp('1980-12-22 00:00:00'): -0.12173913043478257, Timestamp('1980-12-23 00:00:00'): -0.12173913043478257, Timestamp('1980-12-24 00:00:00'): -0.12173913043478257, Timestamp('1980-12-26 00:00:00'): -0.12173913043478257, Timestamp('1980-12-29 00:00:00'): -0.12173913043478257, Timestamp('1980-12-30 00:00:00'): -0.12173913043478257, Timestamp('1980-12-31 00:00:00'): -0.12173913043478257, Timestamp('1981-01-02 00:00:00'): -0.12173913043478257, Timestamp('1981-01-05 00:00:00'): -0.12173913043478257, Timestamp('1981-01-06 00:00:00'): -0.12173913043478257, Timestamp('1981-01-07 00:00:00'): -0.14222222222222225, Timestamp('1981-01-08 00:00:00'): -0.1597222222222222, Timestamp('1981-01-09 00:00:00'): -0.1597222222222222, Timestamp('1981-01-12 00:00:00'): -0.1597222222222222, Timestamp('1981-01-13 00:00:00'): -0.1597222222222222, Timestamp('1981-01-14 00:00:00'): -0.1597222222222222, Timestamp('1981-01-15 00:00:00'): -0.1597222222222222, Timestamp('1981-01-16 00:00:00'): -0.1597222222222222, Timestamp('1981-01-19 00:00:00'): -0.1597222222222222, Timestamp('1981-01-20 00:00:00'): -0.1597222222222222, Timestamp('1981-01-21 00:00:00'): -0.1597222222222222, Timestamp('1981-01-22 00:00:00'): -0.1597222222222222, Timestamp('1981-01-23 00:00:00'): -0.1597222222222222, Timestamp('1981-01-26 00:00:00'): -0.1597222222222222}}

In the Daily_Drawdown column, there are instances in which the value is 0.000. I want to extract the minimum value of between the 0.000s in Daily_Drawdown, preferably to a new column.

For example, the minimum value between the first two 0.000s is: -0.121739. Later on the same column, there will be another 0.000, and I would like it to pick out that minimum value between the 2nd and 3rd 0.000s, and etc.

CodePudding user response：

Create a grouper using cumsum to identify the groups of rows separated by 0, then use groupby and transform with min

g = df['Daily_Drawdown'].eq(0).cumsum()
df['Min_Daily_Drawdown'] = df['Daily_Drawdown'].groupby(g).transform('min')

            Close  Roll_Max  Daily_Drawdown  Max_Daily_Drawdown  Min_Daily_Drawdown
1980-12-12  28.75     28.75        0.000000            0.000000           -0.121739
1980-12-15  27.25     28.75       -0.052174           -0.052174           -0.121739
1980-12-16  25.25     28.75       -0.121739           -0.121739           -0.121739
...
1981-01-21  32.50     36.00       -0.097222           -0.159722           -0.159722
1981-01-22  32.87     36.00       -0.086944           -0.159722           -0.159722
1981-01-23  32.75     36.00       -0.090278           -0.159722           -0.159722
1981-01-26  32.25     36.00       -0.104167           -0.159722           -0.159722