group by pandas dataframe and select maximun value within sequence

user_2801057 0
Viewed 127

I have a pandas dataframe that represents elevation differences between points every 10 degrees for several target Turbines. I have selected the elevation differences that follow a criteria and I have added a column that represents if they are consecutive or not (metDegDiff = 10 represents consecutive points).

How can I select the maximum value of elevDif by targTurb in 3 or more consecutive 10 degree points?

ridgeDF2 = pd.DataFrame(data = {
    'MetID':['A06_40','A06_50','A06_60','A06_70','A06_80','A06_100','A06_110','A06_140','A07_110','A07_130','A07_140','A08_100','A08_110','A08_120','A08_130','A08_220'],
    'targTurb':['A06','A06','A06','A06','A06','A06','A06','A06','A07','A07','A07','A08','A08','A08','A08','A08'],
    'metDeg':[30,50,60,70,80,100,110,140,110,130,140,100,110,120,130,220],
    'elevDif':[1.433234, 1.602997,3.227997,2.002991,2.414001,2.96402,1.513,1.793976,1.612,2.429993,1.639008,1.500977,3.048004,2.174011,1.813995,1.527008],
    'metDegDiff':[20,10,10,10,10,20,10,30,-30,20,10,-40,10,10,10,30]})

[Dbg]>>> ridgeDF2
      MetID targTurb  metDeg   elevDif  metDegDiff
0    A06_40      A06      30  1.433234          20
1    A06_50      A06      50  1.602997          10
2    A06_60      A06      60  3.227997          10
3    A06_70      A06      70  2.002991          10
4    A06_80      A06      80  2.414001          10
5   A06_100      A06     100  2.964020          20
6   A06_110      A06     110  1.513000          10
7   A06_140      A06     140  1.793976          30
8   A07_110      A07     110  1.612000         -30
9   A07_130      A07     130  2.429993          20
10  A07_140      A07     140  1.639008          10
11  A08_100      A08     100  1.500977         -40
12  A08_110      A08     110  3.048004          10
13  A08_120      A08     120  2.174011          10
14  A08_130      A08     130  1.813995          10
15  A08_220      A08     220  1.527008          30

In the example, for A06 there are 4 rows that have consecutive 10 metDeg values (rows 1, 2, 3, and 4) and for A8 there are 3 rows (rows 12, 13 and 14). Note that those 2 series have a length of 3 or more.

So, the output would be the maximum elevDif inside those two selected series. Like this:

MetID  targTurb  metDeg   elevDif  metDegDiff
A06_60      A06      60  3.227997          10
A08_110     A08     110  3.048004          10
pandas-groupby pandas python
3 Answers

Chained solution

   ridgeDF2.loc[ridgeDF2[((ridgeDF2.assign(group=(ridgeDF2.metDegDiff!=10).cumsum())).groupby('group')['metDegDiff'].transform(lambda x: (x==10)& (x.count()>=3)))].groupby('targTurb')['elevDif'].idxmax()]

Step by step solution

.cumsum() metDegDiff to create groups where the first element is not 10.

ridgeDF2=ridgeDF2.assign(group=(ridgeDF2.metDegDiff!=10).cumsum())

Apply multiple filter to get rid of metDegDiff not equal to 10 in groups generated above and to retain groups where count of consecutive values=10 is equal or more than 3. I chain groupby() ,.transform() and boolean selection to a achieve this

g=ridgeDF2[ridgeDF2.groupby('group')['metDegDiff'].transform(lambda x: (x==10)& (x.count()>=3))]

From what remains above, select indexes with maximum values

g.loc[g.groupby('targTurb')['elevDif'].idxmax()]

Outcome

  MetID targTurb  metDeg   elevDif  metDegDiff
2    A06_60      A06      60  3.227997          10
12  A08_110      A08     110  3.048004          10

Timing

%timeit ridgeDF2.loc[ridgeDF2[((ridgeDF2.assign(group=(ridgeDF2.metDegDiff!=10).cumsum())).groupby('group')['metDegDiff'].transform(lambda x: (x==10)& (x.count()>=3)))].groupby('targTurb')['elevDif'].idxmax()]
9.01 ms ± 1.84 ms per loop (mean ± std. dev. of 7 runs, 100 loops each)
user_89869750

The code below should work. You can run each line separately to see what is happening.

ridgeDF2['t/f'] = ridgeDF2['metDegDiff'] != 10
ridgeDF2['t/f'] = ridgeDF2['t/f'].shift().fillna(0).cumsum()
ridgeDF2['count'] = ridgeDF2.groupby('t/f')['t/f'].transform(len)-1
ridgeDF2['count'] = np.where(ridgeDF2['count'] >= 3,True,False)
ridgeDF2.loc[ridgeDF2['metDegDiff'] != 10,'count'] = False
highest = ridgeDF2.loc[ridgeDF2['count'] == True]
highest = highest.loc[highest.groupby(['targTurb','metDegDiff','t/f'])['elevDif'].idxmax()]
highest.drop(columns = ['t/f','count'])
user_138021150

What you can do is to create a group column for the same consecutive value in metDegDiff and same targTurb, using shift and cumsum. Then you can use this group column to select where the group has more or equal (ge) 3 values obtained by map the group number with the value_counts of the group number, and where the value in metDegDiff is equal (eq) to 10. Now that you have only the groups of interest, you can sort_values on elevDif and drop_duplicates on the group column to keep the maximum value per group. You end with drop the column gr and sort_values per targTurb if necessary.

ridgeDF2['metDegDiff'] = ridgeDF2['metDeg'].diff() #I assume calculated this way

#create a group number with same consecutive values and same targTurb
ridgeDF2['gr'] = (ridgeDF2['metDegDiff'].ne(ridgeDF2['metDegDiff'].shift())
                  |(ridgeDF2['targTurb'].ne(ridgeDF2['targTurb'].shift()))
                 ).cumsum()

#get the result dataframe
res_ = (ridgeDF2.loc[ridgeDF2['metDegDiff'].eq(10) #row with 10 in metDegDiff
                     &ridgeDF2['gr'].map(ridgeDF2['gr'].value_counts()).ge(3)] #and row with group of greater equal 3 values
                .sort_values(by='elevDif') # ascending sort of the elevDif
                .drop_duplicates('gr', keep='last') #keep the last row pergroup having higher number
                .drop('gr', axis=1) #remove the extra group column
                .sort_values('targTurb') #if you need
       )

and you get the rows you want

print (res_)
      MetID targTurb  metDeg   elevDif  metDegDiff
2    A06_60      A06      60  3.227997        10.0
12  A08_110      A08     110  3.048004        10.0
user_92747320
Related