Python/ Pandas: Finding a left and right max

Question

I've got a pandas dataframe with an area in the first column and 8 years of quarterly data in the rest. There's about 4400 rows. Here is a sample:

idx Q12000      Q22000      Q32000      Q42000      Q12001      Q22001      Q32001     Q42001      Q12002      Q22002      Q32002      Q42002

0   4085280.0   4114911.0   4108089.0   4111713.0   4055699.0   4076430.0   4043219.0  4039370.0   4201158.0   4243119.0   4231823.0   4254681.0
1   21226.0     21566.0     21804.0     22072.0     21924.0     23232.0     22748.0    22258.0     22614.0     22204.0     22500.0     22660.0     
2   96400.0     102000.0    98604.0     97086.0     96354.0     103054.0    97824.0    95958.0     115938.0    123064.0    120406.0    120648.0   
3   23820.0     24116.0     24186.0     23726.0     23504.0     23574.0     23162.0    23078.0     22306.0     22334.0     22152.0     22080.0     
4   7838.0      7906.0      7714.0      7676.0      7480.0      7520.0      7102.0     6722.0      8324.0      8166.0      8208.0      8326.0

Here is an image depicting what I'm trying to calculate: timeline

nadir: the lowest point (min)
nadir_qtr: the quarter at which the nadir happens
pre-peak: the highest point before the nadir
pre-peak_qtr: the quarter at which the pre-peak happens
post-peak: the highest point after the nadir
post-peak_qtr: the quarter at which the post-peak happens recover: the quarter after the nadir where the numbers surpass those of the pre-peak

I'm able to calculate the nadir pretty easily.

df['nadir'] = df.iloc[:,2:].min(axis=1)
df['nadir_qtr'] = df.iloc[:,2:].idxmin(axis=1)

idx Q12000      Q22000      Q32000      Q42000      Q12001      Q22001      Q32001     Q42001      Q12002      Q22002      Q32002      Q42002      nadir      nadir_qtr

0   4085280.0   4114911.0   4108089.0   4111713.0   4055699.0   4076430.0   4043219.0  4039370.0   4201158.0   4243119.0   4231823.0   4254681.0   4039370.0  Q42001
1   21226.0     21566.0     21804.0     22072.0     21924.0     23232.0     22748.0    22258.0     22614.0     22204.0     22500.0     22660.0     21226      Q12000 
2   96400.0     102000.0    98604.0     97086.0     96354.0     103054.0    97824.0    95958.0     115938.0    123064.0    120406.0    120648.0    95958.0    Q42001  
3   23820.0     24116.0     24186.0     23726.0     23504.0     23574.0     23162.0    23078.0     22306.0     22334.0     22152.0     22080.0     22080.0    Q42002
4   7838.0      7906.0      7714.0      7676.0      7480.0      7520.0      7102.0     6722.0      8324.0      8166.0      8208.0      8326.0      6722.0     Q42001

But when it comes to getting the pre or post peak values or quarters, I get stuck hard. The closest I've come is something like this:

df['pre-peak'] = df.loc[:,:df['nadir_qtr'].max(axis=1)
df['pre-peak_qtr'] = df.loc[:,:df['nadir_qtr']].idxmax(axis=1)

Expected output:

idx Q12000      Q22000      Q32000      Q42000      Q12001      Q22001      Q32001     Q42001      Q12002      Q22002      Q32002      Q42002      nadir      nadir_qtr   pre-peak      pre-peak_qtr

0   4085280.0   4114911.0   4108089.0   4111713.0   4055699.0   4076430.0   4043219.0  4039370.0   4201158.0   4243119.0   4231823.0   4254681.0   4039370.0  Q42001      4114911.0     Q22000
1   21226.0     21566.0     21804.0     22072.0     21924.0     23232.0     22748.0    22258.0     22614.0     22204.0     22500.0     22660.0     21226.0    Q12000      NaN           NaN
2   96400.0     102000.0    98604.0     97086.0     96354.0     103054.0    97824.0    95958.0     115938.0    123064.0    120406.0    120648.0    95958.0    Q42001      103054.0      Q22001
3   23820.0     24116.0     24186.0     23726.0     23504.0     23574.0     23162.0    23078.0     22306.0     22334.0     22152.0     22080.0     22080.0    Q42002      24816.0       Q32000
4   7838.0      7906.0      7714.0      7676.0      7480.0      7520.0      7102.0     6722.0      8324.0      8166.0      8208.0      8326.0      6722.0     Q42001      7906.0        Q2200

But any variations of this give me the wrong data or errors (the most common is)

TypeError: reduction operation 'argmax' not allowed for this dtype

I've tried lots of strategies, brute-forcing iterating through each row as a numpy array, splitting each row. I'm really stuck.

related [issue] (https://stackoverflow.com/questions/63962770/python-pandas-calculate-left-max) — CJ H, Sep 18 '20 at 21:01

score 2 · Accepted Answer · answered Sep 14 '20 at 04:03

Here is one way, which uses 'helper' functions:

# create the data frame
from io import StringIO
import pandas as pd

data = ''' Q12000      Q22000      Q32000      Q42000      Q12001      Q22001      Q32001     Q42001      Q12002      Q22002      Q32002      Q42002

0   4085280.0   4114911.0   4108089.0   4111713.0   4055699.0   4076430.0   4043219.0  4039370.0   4201158.0   4243119.0   4231823.0   4254681.0
1   21226.0     21566.0     21804.0     22072.0     21924.0     23232.0     22748.0    22258.0     22614.0     22204.0     22500.0     22660.0     
2   96400.0     102000.0    98604.0     97086.0     96354.0     103054.0    97824.0    95958.0     115938.0    123064.0    120406.0    120648.0   
3   23820.0     24116.0     24186.0     23726.0     23504.0     23574.0     23162.0    23078.0     22306.0     22334.0     22152.0     22080.0     
4   7838.0      7906.0      7714.0      7676.0      7480.0      7520.0      7102.0     6722.0      8324.0      8166.0      8208.0      8326.0      
'''
df = pd.read_csv(StringIO(data), sep='\s+', engine='python')

Second, define the helper functions:

def calc_nadir(s):
    assert isinstance(s, pd.Series)
    return s.min()

def calc_nadir_qtr(s):
    return s.argmin()

def calc_pre_peak(s):
    return s[ : s.argmin()].max()

def calc_pre_peak_quarter(s):
    try:
        qtr = s[ : s.argmin()].argmax()
    except:
        qtr = None
    return qtr

def calc_post_peak(s):
    return s[s.argmin() : ].max()

def calc_post_peak_qtr(s):
    return s[s.argmin() : ].argmax() + s.argmin()

Third, we use the helper functions and assemble the results:

nadir = df.apply(lambda x: calc_nadir(x), axis=1).rename('nadir')
nadir_qtr = df.apply(lambda x: calc_nadir_qtr(x), axis=1).rename('nadir_qtr')

pre_peak = df.apply(lambda x: calc_pre_peak(x), axis=1).rename('pre_peak')
pre_peak_qtr = df.apply(lambda x: calc_pre_peak_quarter(x), axis=1).rename('pre_peak_qtr')

post_peak = df.apply(lambda x: calc_post_peak(x), axis=1).rename('post_peak')
post_peak_qtr = df.apply(lambda x: calc_post_peak_qtr(x), axis=1).rename('post_peak_qtr')

results = pd.concat([nadir, nadir_qtr, pre_peak, pre_peak_qtr, 
                     post_peak, post_peak_qtr], axis=1)
print(results)

       nadir  nadir_qtr   pre_peak  pre_peak_qtr  post_peak  post_peak_qtr
0  4039370.0          7  4114911.0           1.0  4254681.0             11
1    21226.0          0        NaN           NaN    23232.0              5
2    95958.0          7   103054.0           5.0   123064.0              9
3    22080.0         11    24186.0           2.0    22080.0             11
4     6722.0          7     7906.0           1.0     8326.0             11

Great! Please can I ask you to hit the up-vote an/or accept this as the answer. Thanks! — jsmart, Sep 15 '20 at 01:48
related [issue] (https://stackoverflow.com/questions/63962770/python-pandas-calculate-left-max) — CJ H, Sep 18 '20 at 21:01

Python/ Pandas: Finding a left and right max

1 Answers1

Linked