5

I am slowly moving from R to python + pandas, and I am facing a problem I cannot solve...

I need to discretize values from one column, by assigning them to bins and adding a column with those bin names to original DataFrame. I am trying to use pandas.qcut, but the resulting Categorical object seems to be not playing well with DataFrame.

An example:

import pandas as pd
df1 = pd.DataFrame(np.random.randn(10), columns=['a'])
df1['binned_a'] = pd.qcut(df1['a'],4)

Now when trying to invoke describe on df1 I cannot see the new column:

>>> df1.describe()
               a
count  10.000000
mean    0.594072
std     1.109981
min    -0.807307
25%    -0.304550
50%     0.545839
75%     1.189487
max     2.851922

However, it apparently is there:

>>> df1
          a          binned_a
0  0.190015   (-0.305, 0.546]
1  0.140227   (-0.305, 0.546]
2  1.380000    (1.189, 2.852]
3 -0.522530  [-0.807, -0.305]
4 -0.452810  [-0.807, -0.305]
5  2.851922    (1.189, 2.852]
6 -0.807307  [-0.807, -0.305]
7  0.901663    (0.546, 1.189]
8  1.010334    (0.546, 1.189]
9  1.249205    (1.189, 2.852]

What am I doing wrong? My desired result is to get a column with 4 unique string values describing the bins (like factors in R).

EDIT:

As correctly spotted by Dan, the summary() method won't show column with text-only data and so the mysterious problem is solved :) Thanks a lot!

Paweł Rumian
  • 3,676
  • 3
  • 21
  • 27

1 Answers1

3

I've never been an R user, but if I understand you, you want to group the data into bins and describe each bin.

In [9]: df.groupby('binned_a').describe().unstack()
Out[9]:               a                                                   \
                  count      mean       std       min       25%       50%   
binned_a                                                                    
(-0.113, 0.109]       2  0.025114  0.010264  0.017856  0.021485  0.025114   
(-0.337, -0.113]      2 -0.282838  0.056445 -0.322751 -0.302794 -0.282838   
(0.109, 0.563]        3  0.354481  0.214402  0.134978  0.250027  0.365076   
[-1.842, -0.337]      3 -1.003969  0.765167 -1.841622 -1.335073 -0.828523   


                   75%       max  
binned_a                              
(-0.113, 0.109]   0.028742  0.032371  
(-0.337, -0.113] -0.262882 -0.242925  
(0.109, 0.563]    0.464233  0.563390  
[-1.842, -0.337] -0.585142 -0.341762

To avoid Categoricals altogether, see https://stackoverflow.com/a/17150734/1221924

Community
  • 1
  • 1
Dan Allan
  • 34,073
  • 6
  • 70
  • 63
  • Not exactly, in fact I am just wondering why the new `binned_a` column is not showing in the `describe` result... However, I have tried doing `df1['binned_a'] = pd.Series(pd.qcut(df1['a'],4))` and still no result... – Paweł Rumian Jul 03 '13 at 14:28
  • 1
    The "bins" in ``binned_a`` are strings, so ``describe()`` is ignoring them. Would exactly about the bins would you like to describe? – Dan Allan Jul 03 '13 at 14:31
  • Ahhh, indeed... I thought that when a column is not showing in `describe()` then something is wrong (in R text columns are also shown in summry)... So it seems to be clear now, thanks a lot! – Paweł Rumian Jul 03 '13 at 14:38