Pandas Groupby sort by Index

I have negative values in my index column of my groupby. I need to sort these as numbers, not as text. Here is my group by and my attempt at sorting, which has been unsuccessful:

df_counts = df.groupby('DAYSLATE')[['DAYSLATE']].count()
df_counts = df_counts.sort_index().astype(int)

Here is my current output:

          DAYSLATE DAYSLATE_PCT  CUMULATIVE CUM_PCT
DAYSLATE                                           
-1              59        7.73%          59   7.73%
-129             1        0.13%          60   7.86%
-2               7        0.92%          67   8.78%
-230             1        0.13%          68   8.91%
-3               1        0.13%          69   9.04%
0              486        63.7%         555  72.74%
1               98       12.84%         653  85.58%
10               3        0.39%         656  85.98%
11               3        0.39%         659  86.37%
14               2        0.26%         661  86.63%
2               33        4.33%         694  90.96%
260              1        0.13%         695  91.09%
3               18        2.36%         713  93.45%
4               25        3.28%         738  96.72%
5               10        1.31%         748  98.03%
6                3        0.39%         751  98.43%
7                2        0.26%         753  98.69%
70               1        0.13%         754  98.82%
8                4        0.52%         758  99.34%
9                5        0.66%         763  100.0%

1 answer

  • answered 2018-03-20 17:52 piRSquared

    If you want to leave the index as it is but just want to sort as if it were numeric...

    df_counts.iloc[df_counts.index.astype(int).argsort()]
    
              DAYSLATE.1 DAYSLATE_PCT  CUMULATIVE CUM_PCT
    DAYSLATE                                             
    -230               1        0.13%          68   8.91%
    -129               1        0.13%          60   7.86%
    -3                 1        0.13%          69   9.04%
    -2                 7        0.92%          67   8.78%
    -1                59        7.73%          59   7.73%
    0                486        63.7%         555  72.74%
    1                 98       12.84%         653  85.58%
    2                 33        4.33%         694  90.96%
    3                 18        2.36%         713  93.45%
    4                 25        3.28%         738  96.72%
    5                 10        1.31%         748  98.03%
    6                  3        0.39%         751  98.43%
    7                  2        0.26%         753  98.69%
    8                  4        0.52%         758  99.34%
    9                  5        0.66%         763  100.0%
    10                 3        0.39%         656  85.98%
    11                 3        0.39%         659  86.37%
    14                 2        0.26%         661  86.63%
    70                 1        0.13%         754  98.82%
    260                1        0.13%         695  91.09%
    

    Otherwise, you can set the index to numeric

    df_counts.set_index(df_counts.index.astype(int)).sort_index()
    
              DAYSLATE.1 DAYSLATE_PCT  CUMULATIVE CUM_PCT
    DAYSLATE                                             
    -230               1        0.13%          68   8.91%
    -129               1        0.13%          60   7.86%
    -3                 1        0.13%          69   9.04%
    -2                 7        0.92%          67   8.78%
    -1                59        7.73%          59   7.73%
    0                486        63.7%         555  72.74%
    1                 98       12.84%         653  85.58%
    2                 33        4.33%         694  90.96%
    3                 18        2.36%         713  93.45%
    4                 25        3.28%         738  96.72%
    5                 10        1.31%         748  98.03%
    6                  3        0.39%         751  98.43%
    7                  2        0.26%         753  98.69%
    8                  4        0.52%         758  99.34%
    9                  5        0.66%         763  100.0%
    10                 3        0.39%         656  85.98%
    11                 3        0.39%         659  86.37%
    14                 2        0.26%         661  86.63%
    70                 1        0.13%         754  98.82%
    260                1        0.13%         695  91.09%