text_html_output_1.png application_vnd.jupyter.stderr_output_1.png
from sklearn.preprocessing import StandardScaler

import matplotlib.pyplot as plt

import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

data = pd.read_excel('../input/online-retail-data-set-from-uci-ml-repo/Online Retail.xlsx')
data['InvoiceDate'] = pd.to_datetime(data['InvoiceDate'], format='%d-%m-%Y %H:%M')

data.shape

data.isnull().sum() * 100 / data.shape[0]

order_wise = data.dropna()

order_wise.shape
order_wise.isnull().sum()

amount = pd.DataFrame(order_wise['Quantity'] * order_wise['UnitPrice'], columns=['Amount'])

order_wise = pd.concat(objs=[order_wise, amount], axis=1, ignore_index=False)
monetary = order_wise.groupby('CustomerID').Amount.sum()
monetary = monetary.reset_index()

frequency = order_wise[['CustomerID', 'InvoiceNo']]
k = frequency.groupby('CustomerID').InvoiceNo.count()
k = pd.DataFrame(k)
k = k.reset_index()
k.columns = ['CustomerID', 'Frequency']

master = monetary.merge(k, on='CustomerID', how='inner')

recency = order_wise[['CustomerID', 'InvoiceDate']]
maximum = max(recency.InvoiceDate)
maximum = maximum + pd.DateOffset(days=1)
recency['diff'] = maximum - recency.InvoiceDate

a = recency.groupby('CustomerID')
a['diff'].min()

df = pd.DataFrame(recency.groupby(['CustomerID', 'diff']).min())
df = df.reset_index()
df = df.drop('InvoiceDate', axis=1)
df = df.rename(columns={'diff': 'Recency'})

RFM = k.merge(monetary, on='CustomerID')
RFM = RFM.merge(df, on='CustomerID')

Q1 = RFM.Amount.quantile(0.25)
Q3 = RFM.Amount.quantile(0.75)
IQR = Q3 - Q1
RFM = RFM[(RFM.Amount >= Q1 - 1.5 * IQR) & (RFM.Amount <= Q3 + 1.5 * IQR)]

RFM_norm1 = RFM.drop('CustomerID', axis=1)
RFM_norm1.Recency = RFM_norm1.Recency.dt.days
from sklearn.preprocessing import StandardScaler
standard_scaler = StandardScaler()
RFM_norm1 = standard_scaler.fit_transform(RFM_norm1)
RFM_norm1 = pd.DataFrame(RFM_norm1)
RFM_norm1.columns = ['Frequency', 'Amount', 'Recency']
RFM_norm1.head(5)