select/filter bins after qcut decile

2.5k views Asked by At

I am trying to access the labels (i.e. positional indicator) after binning my data by decile:

q = pd.qcut(df["revenue"], 10)


q.head():

7     (317.942, 500.424]
81    (317.942, 500.424]
83     (150.65, 317.942]
84        [0.19, 150.65]
85    (317.942, 500.424]
Name: revenue, dtype: category
    Categories (10, object): [[0.19, 150.65] < (150.65, 317.942] < (317.942, 500.424] < (500.424, 734.916] ... (1268.306, 1648.35] 
< (1648.35, 1968.758] < (1968.758, 2527.675] < (2527.675, 18690.2]]
    In [233]:

This post link shows that you can do the following to access the labels:

>>> q.labels

But when I do that I get:

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
<ipython-input-246-e806c96b1ab2> in <module>()
----> 1 q.labels

C:\Users\blah\Anaconda3\lib\site-packages\pandas\core\generic.py in __getattr__(self, name)
   2666         if (name in self._internal_names_set or name in self._metadata or
   2667                 name in self._accessors):
-> 2668             return object.__getattribute__(self, name)
   2669         else:
   2670             if name in self._info_axis:

AttributeError: 'Series' object has no attribute 'labels'

In any case, what I want to do is use the labels to filter my data - likely by adding a new column in df which represents the positional label of the result of the decile (or quantile).

1

There are 1 answers

0
piRSquared On BEST ANSWER

I personally like using the labels parameter in pd.qcut to specify clean looking and consistent labels.

np.random.seed([3,1415])
df = pd.DataFrame(dict(revenue=np.random.randint(1000000, 99999999, 100)))
df['decile'] = pd.qcut(df.revenue, 10, labels=range(10))
print(df.head())

As @jeremycg pointed out, you access category information via the cat accessor attribute

df.decile.cat.categories

Int64Index([0, 1, 2, 3, 4, 5, 6, 7, 8, 9], dtype='int64')

You can quickly describe each bin

df.groupby('decile').describe().unstack()

enter image description here


You can filter

df.query('decile >= 8')

     revenue decile
4   98274570      9
6   99418302      9
19  89598752      8
20  88877661      8
22  90789485      9
29  83126518      8
31  90700517      9
33  96816407      9
40  89937348      8
54  83041116      8
65  83399066      8
66  97055576      9
79  87700403      8
81  88592657      8
82  91963755      9
83  82443566      8
84  84880509      8
88  98603752      9
95  92548497      9
98  98963891      9

you can z-score within deciles

df = df.join(df.groupby('decile').revenue.agg(dict(Mean='mean', Std='std')), on='decile')
df['revenue_zscore_by_decile'] = df.revenue.sub(df.Mean).div(df.Std)
df.head()

    revenue decile           Std      Mean  revenue_zscore_by_decile
0  32951600      2  2.503325e+06  29649669                  1.319018
1  70565451      6  9.639336e+05  71677761                 -1.153928
2   6602402      0  5.395453e+06  11166286                 -0.845876
3  82040251      7  2.976992e+06  78299299                  1.256621
4  98274570      9  3.578865e+06  95513475                  0.771500