1
$\begingroup$

Hi Data Science community!

I'm working on some RFM analysis, but while setting up the bins, I'm getting an error. Here's my code and below the exact error.

import modules
import pandas as pd # for dataframes
import matplotlib.pyplot as plt # for plotting graphs
import seaborn as sns # for plotting graphs
import datetime as dt

#Load the file
data = pd.read_excel('Orders.xlsx')
#EDA (Exploratory Data Analysis)
data.head()
data.info()
data.describe()

#Removing null values
data= data[pd.notnull(data['CustomerID'])]

#Delete duplicate records
filtered_data=data[['Country','CustomerID','InvoiceNo','Description']].drop_duplicates()
filtered_data.info()

#Top ten country's customer
filtered_data.Country.value_counts()[:10].plot(kind='bar')

#Filter US customers only
us_data=data[data.Country=='US']
us_data.info()
us_data.describe()

#Filter out unit price <= 0
us_data = us_data[(us_data['UnitPrice']>0)]
us_data.info()
us_data.describe()

#Filter to only required columns for RFM Analysis
us_data=us_data[['CustomerID','InvoiceDate','InvoiceNo','Quantity','UnitPrice']]
#Create TotalPrice column
us_data['TotalPrice'] = us_data['Quantity'] * us_data['UnitPrice']
#Find the oldest and newest dates
us_data['InvoiceDate'].min(),us_data['InvoiceDate'].max()
#Define the present date
PRESENT = dt.datetime(2019,10,1)
#Covert InvoiceDate to to_datetime
us_data['InvoiceDate'] = pd.to_datetime(us_data['InvoiceDate'])
us_data.head()

#RFM Analysis
rfm= us_data.groupby('CustomerID').agg({'InvoiceDate': lambda date: (PRESENT - date.max()).days,
                                        'InvoiceNo': lambda num: len(num),
                                        'TotalPrice': lambda price: price.sum()})
rfm.columns
# Change the name of the columns
rfm.columns=['recency','frequency','monetary']
rfm['recency'] = rfm['recency'].astype(int)
rfm.head()

#Computing Quantile of RFM values
rfm['r_quartile'] = pd.qcut(rfm['recency'], 4, ['1','2','3','4'])
rfm['f_quartile'] = pd.qcut(rfm['frequency'], 4, ['1','2','3','4'])
rfm['m_quartile'] = pd.qcut(rfm['monetary'], 4, ['1','2','3','4'])
rfm.head()

And here's the traceback:

Traceback (most recent call last):

  File "<ipython-input-57-e15dc8d2e29f>", line 2, in <module>
    rfm['f_quartile'] = pd.qcut(rfm['frequency'], 4, ['1','2','3','4'])

  File "/Users/omarmartinez/anaconda3/lib/python3.7/site-packages/pandas/core/reshape/tile.py", line 313, in qcut
    dtype=dtype, duplicates=duplicates)

  File "/Users/omarmartinez/anaconda3/lib/python3.7/site-packages/pandas/core/reshape/tile.py", line 339, in _bins_to_cuts
    "the 'duplicates' kwarg".format(bins=bins))

ValueError: Bin edges must be unique: array([ 1.,  1.,  1.,  1., 37.]).
You can drop duplicate edges by setting the 'duplicates' kwarg

I tried including the duplicates='drop' argument, like this:

#Computing Quantile of RFM values
rfm['r_quartile'] = pd.qcut(rfm['recency'], 4, ['1','2','3','4'], duplicates='drop')
rfm['f_quartile'] = pd.qcut(rfm['frequency'], 4, ['1','2','3','4'],duplicates='drop')
rfm['m_quartile'] = pd.qcut(rfm['monetary'], 4, ['1','2','3','4'],duplicates='drop')
rfm.head()

But then I get another traceback:

Traceback (most recent call last):

  File "<ipython-input-59-bf551522a462>", line 2, in <module>
    rfm['f_quartile'] = pd.qcut(rfm['frequency'], 4, ['1','2','3','4'],duplicates='drop')

  File "/Users/omarmartinez/anaconda3/lib/python3.7/site-packages/pandas/core/reshape/tile.py", line 313, in qcut
    dtype=dtype, duplicates=duplicates)

  File "/Users/omarmartinez/anaconda3/lib/python3.7/site-packages/pandas/core/reshape/tile.py", line 359, in _bins_to_cuts
    raise ValueError('Bin labels must be one fewer than '

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

I'm a little bit lost here, so any help on this will be highly appreciated.

Thank you in advance for the support!

$\endgroup$
0
$\begingroup$

For anyone having this or a similar problem, I'd recommend doing some EDA. This is such an essential step that we might run through without really understanding the distribution of our data.

Essentially, my problem was that the distribution was very skewed, for example, 90% of the observations had the same value. So I used specific percentiles instead to create the 4 buckets.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.