# Binning Data With Pandas Between Method

Sharing is caring!

Last Updated on July 14, 2022 by Jay

Sometimes we need to perform data binning and the pandas method between() can help us achieve that goal. Data binning refers to the process in which we place data into discrete intervals or bands/bins like the below example.

We’ll create some random samples that present 100 persons’ age and their net worth in monetary amounts. Then we’ll bin the data into different buckets by age.

``````import pandas as pd
import numpy as np

age = np.random.randint(0,121, size=100)
net_worth = np.random.randint(100,10000, size=100)

df = pd.DataFrame({'Age':age, 'Net_Worth':net_worth})
df
Age  Net_Worth
0   101       2095
1    15       1331
2   100       3138
3    62        518
4    23       8429
..  ...        ...
95   35       7325
96   37       3105
97   22       1887
98  115       2582
99   62       3217

[100 rows x 2 columns]``````

## The Between() Method

The pandas’ between method checks whether data is between two values, and it has the following syntax:

between(left, right, inclusive=’both’)

• left: the lower endpoint for a band/range
• right: the higher endpoint for a band/range
• inclusive: whether we want to include the left and right endpoints. Can take the values of “both”, “neither”, “left” or “right”

The method returns a boolean index containing a list of True and False values.

Let’s check which record is between the ages of 0 to 20:

``````df['Age'].between(0, 20, inclusive='right')
0     False
1      True
2     False
3     False
4     False
...
95    False
96    False
97    False
98    False
99    False
Name: Age, Length: 100, dtype: bool
``````

You probably noticed that the between method is essentially equivalent to the following:

``(df['Age'] > 0) & (df['Age'] <= 20)``

## Assigning Bins

Now we can check whether data is within a band with the help of the boolean index. We also need to use the loc method to access/assign values for individual records matching the criteria. Note the NaN values are because we haven’t assigned a band to them yet.

``````df.loc[df['Age'].between(left=0, right=20, inclusive='right'), 'band'] = '(0, 20]'
Age  Net_Worth     band
0   101       2095      NaN
1    15       1331  (0, 20]
2   100       3138      NaN
3    62        518      NaN
4    23       8429      NaN
..  ...        ...      ...
95   35       7325      NaN
96   37       3105      NaN
97   22       1887      NaN
98  115       2582      NaN
99   62       3217      NaN

[100 rows x 3 columns]``````

Basically, the loc property returns only record matching the criteria, i.e. the True values from the boolean index. See the below example which returns only the records that are between 0 and 20 years old.

``````df.loc[df['Age'].between(left=0, right=20, inclusive='right')]
Age  Net_Worth     band
1    15       1331  (0, 20]
6     8       4230  (0, 20]
9    17       1794  (0, 20]
20   13       9135  (0, 20]
36    9       5553  (0, 20]
52   13       5538  (0, 20]
55    8       7323  (0, 20]
60    2       8619  (0, 20]
62   17       7349  (0, 20]
73    8       9320  (0, 20]
77   15       4368  (0, 20]
79   16       3203  (0, 20]
83   15       2325  (0, 20]
90   14       7467  (0, 20]
93   20       3457  (0, 20]
``````

## Binning Data With Pandas Between Method

Let’s say we want to put all data into the following age bins. Note the parenthesis “()” means not inclusive and square brackets “[]” mean inclusive. so (0, 20] means from ages of 1 to 20.

• (0, 20]
• (20,60]
• (60, 90]
• (90, 120]
``````# age intervals
age_band = [0,20,60,90,120]

# create age bins
age_bins = []
for i in range(4):
age_bins.append([age_band[i],age_band[i+1]])
[[0, 20], [20, 60], [60, 90], [90, 120]]

# binning the data
for b in age_bins:
df.loc[df['Age'].between(left=b[0], right=b[1], inclusive='right'), 'band'] = f'({b[0]},{b[1]}]'
Age  Net_Worth      band
0   101       2095  (90,120]
1    15       1331    (0,20]
2   100       3138  (90,120]
3    62        518   (60,90]
4    23       8429   (20,60]
..  ...        ...       ...
95   35       7325   (20,60]
96   37       3105   (20,60]
97   22       1887   (20,60]
98  115       2582  (90,120]
99   62       3217   (60,90]

[100 rows x 3 columns]``````

Unfortunately, there’s no easy way to bin data using the between and loc methods. Although using a loop is not too bad, this approach can become inefficient when handling a large number of bins because we need to repeat the process N (number of bins) times. An easier approach to bin data is to use the pandas’ cut method. You can find below a link to that tutorial.