Resetting lowest low and counts in a Pandas dataframe-CodePudding

Here is my small sample dataframe code

df = pd.DataFrame.from_dict(
{
    'Low': [3001,2984,2973,2976,2977,2980,2985,2970,2956,2960],
 'Close' : [3004,2986,2980,2985,2996,2990,2992,2975,2970,2965],
'Condition'  : ['Above', 'Below', 'Below', 'Below', 'Above', 
 'Above','Below','Below','Below','Above']
})

     Low    Close   Condition
0   3001    3004    Above
1   2984    2986    Below
2   2973    2980    Below
3   2976    2985    Below
4   2977    2996    Above
5   2980    2990    Above
6   2985    2992    Below
7   2970    2975    Below
8   2956    2970    Below
9   2960    2965    Above

My question is, "how do I loop through to find the lowest low for each block of data where the condition is equal to 'Below', but then resets when the condition goes back to 'Above'"?

My hope is that I would get some sort of data frame, maybe through a for loop, that looked like this

  Low
2 2973
8 2956

Any suggestions as to how I might loop through to achieve this?

Thanks!

CodePudding user response：

We can add a temporary extra group column by checking if the condition changes:

df["Group"] = (df.Condition != df.Condition.shift()).cumsum()

Giving:

    Low  Close Condition  group
0  3001   3004     Above      1
1  2984   2986     Below      2
2  2973   2980     Below      2
3  2976   2985     Below      2
4  2977   2996     Above      3
5  2980   2990     Above      3
6  2985   2992     Below      4
7  2970   2975     Below      4
8  2956   2970     Below      4
9  2960   2965     Above      5

Now the question is rather simple to answer using groupby:

below_rows = df[df["Condition"] == "Below"]
out = below_rows.groupby("Group")["Low"].agg(['idxmin', 'min'])

Giving:

       idxmin   min
Group
2           2  2973
4           8  2956

CodePudding user response：

# Make groups, defining every time Condition == Above as a new group:
df.loc[df.Condition.eq('Above'), 'group'] = 1
df.group = df.group.fillna(0).cumsum()

# Find the min of each group:
out = (df[df.Condition.eq('Below')].groupby('group')['Low']
                                   .agg(['idxmin', 'min'])
                                   .reset_index(drop=True))
print(out)

Output:

   idxmin   min
0       2  2973
1       8  2956