How to create bins and assign labels based on a given condition pandas

user_11131258 0
Viewed 660

I have a pandas Dataframe and I want to create a new columns (new1,new2,new3,new4,new5,new6,new7,new8,new9,new10) from the original columns(A-J). The condition is to create new columns by assigning bins to the values in columns (A-J).

bins to be assigned:

0
1.0 - 1.19
1.2 – 1.39
1.4 – 1.59
1.6 – 1.79
1.8 – 1.99
2.0 – 2.99
3.0 – 3.99
4.0 – 4.99
5.0 – 5.99
6.0 – 6.99
7.0 – 7.99
8.0 – 8.99
9.0 – 9.99
10.0+

DataFrame:

    A         B       C        D      E      F       G       H       I       J
0   34.00   5.18    19.10   70.00   0.00    1.10    3.30    2.96    0.00    1.45
1   3.65    7.20    0.00    3.15    14.00   0.00    8.20    13.50   0.00    0.00
2   6.60    12.00   104.94  3.60    24.00   6.54    23.00   4.83    0.00    0.00
3   2.16    5.30    0.00    8.40    31.48   0.00    6.40    26.44   0.00    0.00
4   4.66    268.86  160.00  19.14   1.58    17.77   47.57   88.01   0.00    0.00
5   7.40    2.64    0.00    4.90    3.53    0.00    46.00   0.00    0.00    0.00
6   6.18    60.00   8.06    13.50   6.20    3.05    9.00    38.90   0.00    0.00
7   6.51    5.30    6.20    17.00   27.00   15.00   4.51    11.50   0.00    0.00
8   3.25    40.72   4.37    8.02    0.00    110.00  16.25   3.95    75.00   0.00

The new columns to be added to the Dataframe should have the form. Expected Output form

new1          new2     new3  new4      new5    new6       new7      new8     new9   new10

10+         5.0–5.99   10+   10+       0.00   1.0-1.19  3.0–3.99    2.0–2.99  0.00  1.4–1.59
3.0–3.99    7.0–7.99  0.00  3.0–3.99   10+    0.00      8.0 – 8.99  10+ 0.00  0.00  0.00

I tried to create the new DataFrame by using the pd.cut function in pandas, and also assign labels to this bin. but the number of bins created was up to 30 instead of 15. I also tried to assign labels to the bin but I get an error in the process

labels = ['0','1.0 - 1.19' ,'1.2 – 1.39','1.4 – 1.59', '1.6 – 1.79','1.8 – 1.99','2.0 – 2.99','3.0 – 3.99','4.0 – 4.99','5.0 – 5.99',\
          '6.0 – 6.99','7.0 – 7.99','8.0 – 8.99','9.0 – 9.99','10.0+']
bins = [0,1.0,1.19,1.2,1.39,1.4,1.59,1.6,1.79,1.8,1.99,2.0,2.99,3.0,3.99,4.0,4.99,5.0,5.99,6.0,6.99,7.0,7.99,8.0,8.99,9.0,9.99,10.0,np.inf]

df[['new1','new2','new3','new4','new5','new6','new7','new8','new9','new10']] = \
                          df[['A', 'B', 'C','D','E','F','G','H','I','J']].apply(lambda x: \
                         pd.cut(x,bins),axis=1).apply(lambda x: x.cat.add_categories([0.00])).fillna(0.00)

ValueError: Bin labels must be one fewer than the number of bin edges

pandas dataframe python
1 Answers

The solution involves editing your bins list:

# Same labels as yours
labels = ['0', '1.0 - 1.19', '1.2 – 1.39', '1.4 – 1.59', '1.6 – 1.79', 
          '1.8 – 1.99', '2.0 – 2.99', '3.0 – 3.99', '4.0 – 4.99', '5.0 – 5.99', 
          '6.0 – 6.99', '7.0 – 7.99', '8.0 – 8.99', '9.0 – 9.99', '10.0+']

# Define the edges between bins
bins = [0, 1.0, 1.2, 1.4, 1.6, 1.8, 2.0, 3.0, 4.0, 
        5.0, 6.0, 7.0, 8.0, 9.0, 10.0, np.inf]

# pd.cut each column, with each bin closed on left and open on right
res = df.apply(lambda x: pd.cut(x, bins=bins, labels=labels, right=False))

# rename columns and print result
res.columns = [f'new{i+1}' for i in range(df.shape[1])]

print(res)

         new1        new2        new3        new4        new5        new6        new7        new8   new9       new10
0       10.0+  5.0 – 5.99       10.0+       10.0+           0  1.0 - 1.19  3.0 – 3.99  2.0 – 2.99      0  1.4 – 1.59
1  3.0 – 3.99  7.0 – 7.99           0  3.0 – 3.99       10.0+           0  8.0 – 8.99       10.0+      0           0
2  6.0 – 6.99       10.0+       10.0+  3.0 – 3.99       10.0+  6.0 – 6.99       10.0+  4.0 – 4.99      0           0
3  2.0 – 2.99  5.0 – 5.99           0  8.0 – 8.99       10.0+           0  6.0 – 6.99       10.0+      0           0
4  4.0 – 4.99       10.0+       10.0+       10.0+  1.4 – 1.59       10.0+       10.0+       10.0+      0           0
5  7.0 – 7.99  2.0 – 2.99           0  4.0 – 4.99  3.0 – 3.99           0       10.0+           0      0           0
6  6.0 – 6.99       10.0+  8.0 – 8.99       10.0+  6.0 – 6.99  3.0 – 3.99  9.0 – 9.99       10.0+      0           0
7  6.0 – 6.99  5.0 – 5.99  6.0 – 6.99       10.0+       10.0+       10.0+  4.0 – 4.99       10.0+      0           0
8  3.0 – 3.99       10.0+  4.0 – 4.99  8.0 – 8.99           0       10.0+       10.0+  3.0 – 3.99  10.0+           0

Explanation

The sequence of scalars passed as bins to pd.cut() "defines the bin edges allowing for non-uniform width": https://pandas.pydata.org/docs/reference/api/pandas.cut.html.

By default, each bin is open on the left and closed on the right. To switch this, pass right=False (which also closes the left edge of each bin).

For example, bins=[0, 1.0, 1.19, 1.2] causes pd.cut to make 3 intervals: [0.0, 1.0) < [1.0, 1.19) < [1.19, 2.0).

user_80087760
Related