# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     name: python3
# ---

# + id="Wz8GhVln3j_c"
# !pip install pandas

# + id="bYqX0V_Gvji1"
import pandas as pd
from datetime import datetime, timedelta, date
from sklearn.cluster import KMeans

##Read the data and filter out data that belongs to country other than UK
retail_data = pd.read_csv('OnlineRetail.csv', encoding= 'unicode_escape')
retail_data['InvoiceDate'] = pd.to_datetime(retail_data['InvoiceDate'], errors = 'coerce')
uk_data = retail_data.query("Country=='United Kingdom'").reset_index(drop=True)

# + id="i-rpSsV61JvS"
t1 = pd.Timestamp("2011-09-01 00:00:00.054000")
t2 = pd.Timestamp("2011-03-01 00:00:00.054000")
t3 = pd.Timestamp("2011-12-01 00:00:00.054000")
uk_data_6m = uk_data[(uk_data.InvoiceDate < t1) & (uk_data.InvoiceDate >= t2)].reset_index(drop=True)
uk_data_next = uk_data[(uk_data.InvoiceDate >= t1) & (uk_data.InvoiceDate < t3)].reset_index(drop=True)

# + colab={"base_uri": "https://localhost:8080/"} id="4MVscfQX2LgF" outputId="f1755b55-05a3-4c86-f60e-74c30b5e7a97"
uk_day_order = uk_data_6m[['CustomerID','InvoiceDate']]#convert Invoice Datetime to day
uk_day_order['InvoiceDay'] = uk_data_6m['InvoiceDate'].dt.date
uk_day_order = uk_day_order.sort_values(['CustomerID','InvoiceDate'])#drop duplicates
uk_day_order = uk_day_order.drop_duplicates(subset=['CustomerID','InvoiceDay'],keep='first')

# + id="eLOnV_cr2osT"
uk_day_order['PrevInvoiceDate'] = uk_day_order.groupby('CustomerID')['InvoiceDay'].shift(1)
uk_day_order['T2InvoiceDate'] = uk_day_order.groupby('CustomerID')['InvoiceDay'].shift(2)
uk_day_order['T3InvoiceDate'] = uk_day_order.groupby('CustomerID')['InvoiceDay'].shift(3)

# + colab={"base_uri": "https://localhost:8080/", "height": 206} id="_zxZmhzD2uCs" outputId="c4bcac75-a9ca-4b2f-8195-ae3e578f2e27"
uk_day_order.head()

# + id="5Vcxj1Wq2zPl"
uk_day_order['DayDiff'] = (uk_day_order['InvoiceDay'] - uk_day_order['PrevInvoiceDate']).dt.days
uk_day_order['DayDiff2'] = (uk_day_order['InvoiceDay'] - uk_day_order['T2InvoiceDate']).dt.days
uk_day_order['DayDiff3'] = (uk_day_order['InvoiceDay'] - uk_day_order['T3InvoiceDate']).dt.days

# + id="ZrruAORf3A-Z"
uk_day_diff = uk_day_order.groupby('CustomerID').agg({'DayDiff': ['mean','std']}).reset_index()
uk_day_diff.columns = ['CustomerID', 'DayDiffMean','DayDiffStd']

# + id="s-445FbE3Hri"
uk_day_order_last = uk_day_order.drop_duplicates(subset=['CustomerID'],keep='last')

# + id="ttR-eFKh3Nwg"
uk_day_order_last = uk_day_order_last.dropna()
uk_day_order_last = pd.merge(uk_day_order_last, uk_day_diff, on='CustomerID')
uk_day_order_last = uk_day_order_last[['CustomerID','DayDiff','DayDiff2','DayDiff3','DayDiffMean','DayDiffStd']]
uk_day_order_last.columns = ['customerid','daydiff','daydiff2','daydiff3','daydiffmean','daydiffstd']

# + colab={"base_uri": "https://localhost:8080/", "height": 206} id="JNaNCr1v3W6f" outputId="678c6284-0bb4-493e-b365-921291bf91ce"
uk_day_order_last.head()

StarcoderdataJupyterScriptsDedupFiltered