Pandas groupby and select top 5 and bottom 5 values from each of positive and negative types-CodePudding

I have a dataframe df sorted as follows:

Month   Col2
A       -16
A       -4
A       -3
A        2
A        7
B       -17
B       -13
B       -10
B        17
C        1

I am trying to get top 2 from each group of Month for positive and negative of Col2 to get the following:

Month   Col2
    A   -16
    A   -4
    A    2
    A    7
    B   -17
    B   -13
    B    17
    C    1

I am not sure how to approach this.

CodePudding user response：

One approach is sort by absolute value, then groupby on month/sign and extract:

(df.assign(Abs=df['Col2'].abs())
   .sort_values('Abs')
   .groupby(['Month', np.sign(df['Col2'])])
   .tail(2)
   .sort_index()
)

Output:

  Month  Col2  Abs
0     A   -16   16
1     A    -4    4
3     A     2    2
4     A     7    7
5     B   -17   17
6     B   -13   13
8     B    17   17
9     C     1    1

Without alternating your data, you can use reindex/loc on the sorted series:

(df.reindex(df['Col2'].abs().sort_values().index)
   .groupby(['Month', np.sign(df['Col2'])])
   .tail(2)
   .sort_index()
)

Output:

  Month  Col2
0     A   -16
1     A    -4
3     A     2
4     A     7
5     B   -17
6     B   -13
8     B    17
9     C     1