python pandas groupby sorting

Question

I would like to sort the following dataset in descending order (largest to smallest) based on whether 'total_sales' is positive or negative for each state. For example, for AK, the total sales are positive for all 4 regions. So the regions would be sorted starting with the largest sales for all positive regions in descending order and then would move to the negative regions in descending order. This would be the reverse for NC, as NC has a negative 'total_sales' net number.

Also, if possible, in order to denote the first region/largest region that offsets that states 'total_sales' trend, I would like to create a column that states 'offset' for that first value that is counter to the overall 'total_sales' trend for each state.

State   Region   Sales    Total_sales    
AK      North    10       20
AK      South    25       20 
AK      East     -30      20
AK      West     15       20
NC      North    20       -35
NC      South    -50      -35
NC      East     -20      -35 
NC      West     15       -35

Desired Output

State   Region   Sales    Total_sales    Note
AK      South     25        20
AK      West      15        20 
AK      North     10        20
AK      East      -30       20           offset
NC      South     -50       -35
NC      East      -20       -35
NC      North     20        -35          offset
NC      West      15        -35

Thank you! Any help/direction you can provide is greatly appreciated!

BENY · Accepted Answer · 2018-10-08 02:48:15Z

2

First we need to get the sort value, by using np.sign create the sort key

df['sign']=np.sign(df.Total_sales)
df['sign']=df.sign*np.sign(df.Sales)
df['sign2']=df.Sales.abs()
df=df.sort_values(['State','sign','sign2'],ascending=[True,False,False])

Then we get the offset position

df['SaleSign']=np.sign(df.Sales)
df['note']=df.groupby('State').SaleSign.diff().fillna(0).ne(0).map({True:'offset',False:''})
df
Out[427]: 
  State Region  Sales  Total_sales  sign  SaleSign    note
1    AK  South     25           20     1         1        
3    AK   West     15           20     1         1        
0    AK  North     10           20     1         1        
2    AK   East    -30           20    -1        -1  offset
6    NC   East    -20          -35     1        -1        
5    NC  South    -50          -35     1        -1        
4    NC  North     20          -35    -1         1  offset
7    NC   West     15          -35    -1         1

edited Oct 8, 2018 at 2:48

answered Oct 8, 2018 at 1:51

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Over a year ago

Wen thank you very much for your help! I think we are in the ballpark, but the output you presented shows that it is sorted by ascending(which can easily fixed (ascending=False), but I was attempting to sort in descending order by the sign of total sales for that state. So for AK, it was positive, so sales would go in order:25,15,10,-30 and -30 would be the offset, and for NC, which had negative net sales, the order would be -50,-20,20,15 and 20 would be the offset.

John Over a year ago

Wen!!! This is great, everything looks great! The only thing i am noticing is that for NC, it is ordered as -20,-50. I understand that -1 > -5 in math, but for accounting purposes, -5 >-1 with regards to a loss. Do you know of anyway around this? And thanks again for your help!!!

BENY Over a year ago

@John create another column , with sign2 = df.Sales.abs()

Collectives™ on Stack Overflow

python pandas groupby sorting

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related