Binning in python pandas dataframe (not manually setting bins)

user_9648665 0
Viewed 4104

I have a data frame. i want to bin values and append it to the new column. i can do it with pd.cut. But the thing is that, with pd.cut i set labels and bins manually. But, i want to just set step size (not bin number). i tried also np.linespace, np.arange but there i have to specify the start and end point also bin count. but there can be a dataframe which i would not be able to know the max and min number in dataframe

df = pd.DataFrame([10, 10, 23, 42, 51, 33, 52, 42,44, 67, 65, 12, 10, 2, 3, 2, 77, 76],columns=['values'])
bins = [0, 10, 20,30, 40, 50, 60, 70]
labels = ['0-10', '10-20', '20-30', '30-40', '40-50', '50-60', '60-70']
df['bins'] = pd.cut(df['values'], bins, labels=labels)
print (df)

    values   bins
0       10   0-10
1       10   0-10
2       23  20-30
3       42  40-50
4       51  50-60
5       33  30-40
6       52  50-60
7       42  40-50
8       44  40-50
9       67  60-70
10      65  60-70
11      12  10-20
12      10   0-10
13       2   0-10
14       3   0-10
15       2   0-10
16      77    NaN
17      76    NaN

Here is my output, i want to get same output but not with manually setting bins and labels p.s. As u see from here if i have the values greater than 70 it will be Nan. So it is also reason why i want to just set step size "10". I can have continues values, so i want it to label is automatically by using steps size 10

I would really aprreciate your helps

Thanks!!!

pandas dataframe bin numpy python
3 Answers

Just a slight variation to your code, note that I added a row with value 93 at the end of your df.

df = pd.DataFrame([10, 10, 23, 42, 51, 33, 52, 42,44, 67, 65, 12, 10, 2, 3, 2, 77, 76, 93],columns=['values'])
bins = np.arange(0,df['values'].max() + 10, 10)
df['bins'] = pd.cut(df['values'], bins)

values  bins
0   10  (0, 10]
1   10  (0, 10]
2   23  (20, 30]
3   42  (40, 50]
4   51  (50, 60]
5   33  (30, 40]
6   52  (50, 60]
7   42  (40, 50]
8   44  (40, 50]
9   67  (60, 70]
10  65  (60, 70]
11  12  (10, 20]
12  10  (0, 10]
13  2   (0, 10]
14  3   (0, 10]
15  2   (0, 10]
16  77  (70, 80]
17  76  (70, 80]
18  93  (90, 100]

Edit: To include zeros in the bins as asked in the comments, set the parameter include_lowest to True

df = pd.DataFrame([0, 0, 0, 10, 10, 23, 42, 51, 33, 52, 42,44, 67, 65, 12, 10, 2, 3, 2, 77, 76, 93],columns=['values'])
bins = np.arange(0,df['values'].max() + 10, 10)
df['bins'] = pd.cut(df['values'], bins, include_lowest=True)

You get

   values   bins
0   0   (-0.001, 10.0]
1   0   (-0.001, 10.0]
2   0   (-0.001, 10.0]
3   10  (-0.001, 10.0]
4   10  (-0.001, 10.0]
5   23  (20.0, 30.0]
6   42  (40.0, 50.0]
7   51  (50.0, 60.0]
8   33  (30.0, 40.0]
9   52  (50.0, 60.0]
10  42  (40.0, 50.0]
11  44  (40.0, 50.0]
12  67  (60.0, 70.0]
13  65  (60.0, 70.0]
14  12  (10.0, 20.0]
15  10  (-0.001, 10.0]
16  2   (-0.001, 10.0]
17  3   (-0.001, 10.0]
18  2   (-0.001, 10.0]
19  77  (70.0, 80.0]
20  76  (70.0, 80.0]
21  93  (90.0, 100.0]
user_62873080

@Vaishali basically answered the question, but just to add that in order to get your desired labels programmatically, you can use the bin values in a list comprehension, resulting in the string labels below (matching your desired frame)

df = pd.DataFrame([10, 10, 23, 42, 51, 33, 52, 42,44, 67, 65, 12, 10, 2, 3, 2, 77, 76],columns=['values'])
bins = np.arange(0,df['values'].max() + 10, 10)
labels = ['-'.join(map(str,(x,y))) for x, y in zip(bins[:-1], bins[1:])]

df['bins'] = pd.cut(df['values'], bins = bins, labels=labels)

>>> df
    values   bins
0       10   0-10
1       10   0-10
2       23  20-30
3       42  40-50
4       51  50-60
5       33  30-40
6       52  50-60
7       42  40-50
8       44  40-50
9       67  60-70
10      65  60-70
11      12  10-20
12      10   0-10
13       2   0-10
14       3   0-10
15       2   0-10
16      77  70-80
17      76  70-80
user_66711760

Here we solve this problem, simply using the Binning Function and bit help of numpy function.

df = pd.DataFrame([10, 10, 23, 42, 51, 33, 52, 42,44, 67, 65, 12, 10, 2, 3, 2,  77, 76],columns=['values'])
max = df['value'].max()
df['Bins'] = pd.cut(df['value'], np.arange(0, max + 10, 10))
print(df)
user_114576250
Related