Not able to create data buckets in Python dataframe

Question

I am new to Python and not able to create categorical data buckets for continuous data column. I am using the following nested-if combination but its not working. Can someone tell me what am I doing wrong?

POS_Bucket = []
for elements in range(0,len(df['CURRENT_BALANCE'])):
    if elements <= 25000:
        POS_Bucket.append('0-25k')
    elif elements <= 50000:
        POS_Bucket.append('25k-50k')
    elif elements <= 75000:
        POS_Bucket.append('50k-75k')
    else:
        POS_Bracket.append('100k+')
df['POS_Bucket'] = POS_Bucket

I get the following output which is not correct:

    CURRENT_BALANCE POS_Bucket
0   14563.60    0-25k
1   11821.56    0-25k
2   15137.84    0-25k
3   19230.03    0-25k
4   16465.54    0-25k
5   24075.32    0-25k
6   32743.42    0-25k
7   20940.93    0-25k
8   7587.90 0-25k
9   5996.65 0-25k
10  9880.96 0-25k
11  88134.33    0-25k
12  9877.01 0-25k
13  9871.71 0-25k
14  134955.88   0-25k
15  9880.96 0-25k
16  30336.07    0-25k
17  4792.58 0-25k
18  9244.03 0-25k
19  22151.48    0-25k
20  9028.78 0-25k
21  9847.32 0-25k
22  16970.26    0-25k
23  325830.34   0-25k
24  9847.32 0-25k

score 1 · Accepted Answer · answered Sep 05 '22 at 05:58

Use Series.apply for a custom row-wise mapping function:

import pandas as pd


def get_bucket(balance):
    if balance <= 25000:
        return "0-25k"
    if balance <= 50000:
        return "25k-50k"
    if balance <= 75000:
        return "50k-75k"
    return "100k+"


df = pd.DataFrame(
    {
        "CURRENT_BALANCE": [
            14563.60,
            11821.56,
            15137.84,
            19230.03,
            16465.54,
            124075.32,
            32743.42,
            60940.93,
        ],
    }
)

df["POS_Bucket"] = df.CURRENT_BALANCE.apply(get_bucket)

print(df)

   CURRENT_BALANCE POS_Bucket
0         14563.60      0-25k
1         11821.56      0-25k
2         15137.84      0-25k
3         19230.03      0-25k
4         16465.54      0-25k
5        124075.32      100k+
6         32743.42    25k-50k
7         60940.93    50k-75k

Not able to create data buckets in Python dataframe

1 Answers1