<jupyter_start><jupyter_text>Product and Retail Analysis<jupyter_code># Import modules
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import gc
import datetime

sns.set_style('whitegrid')
plt.style.use('ggplot')
%matplotlib inline
# Use zipfile
# from urllib.request import urlopen
# from zipfile import ZipFile
# zipurl = 'https://raw.githubusercontent.com/rajeevratan84/datascienceforbusiness/master/ecommerce_data.zip'
# zipresp = urlopen(zipurl)
# tempzip = open('tempfile.zip', 'wb')
# tempzip.write(zipresp.read())
# tempzip.close()
# zf = ZipFile('tempfile.zip')
# zf.extractall(path='')
# zf.close()
df = pd.read_csv('ecommerce_data.csv', encoding='ISO=8859-1')
df.head()
df.info()
# Check for null columns
df.isnull().sum().sort_values(ascending=False)
# Check for null rows
df[df.isnull().any(axis=1)].head()
# Change InvoiceDate from object to datetime
df.InvoiceDate = pd.to_datetime(df.InvoiceDate, format='%m/%d/%Y %H:%M')
df.info()
# Drop missing values and check values
df.dropna(inplace=True)
df.isnull().sum()
# Change CustomerID from type String to Int
df.CustomerID = df.CustomerID.astype('int64')
df.info()
# Describe dataframe
df.describe().round(2)
# Plot quantity
ax = sns.violinplot(x=df.Quantity)
# Remove negative quantities
df = df[df.Quantity > 0]
df.describe().round(2)
# Now plot quantity
ax = sns.violinplot(x=df.Quantity)
# Create AmountSpent column
df['AmountSpent'] = df.Quantity * df.UnitPrice
df.head()
# Add Month, Day, and Hour columns
# https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html#timeseries-offset-aliases
# M is offset alias
df['month_year'] = df.InvoiceDate.dt.to_period('M')
datetimes = ['year', 'month', 'day', 'dayofweek', 'dayofyear', 'weekofyear', 'quarter']
df = df.join(pd.concat((getattr(df.InvoiceDate.dt, i).rename(i) for i in datetimes), axis=1))
df.head()
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 397924 entries, 0 to 541908
Data columns (total 17 columns):
InvoiceNo      397924 non-null object
StockCode      397924 non-null object
Description    397924 non-null object
Quantity       397924 non-null int64
InvoiceDate    397924 non-null datetime64[ns]
UnitPrice      397924 non-null float64
CustomerID     397924 non-null int64
Country        397924 non-null object
AmountSpent    397924 non-null float64
month_year     397924 non-null period[M]
year           397924 non-null int64
month          397924 non-null int64
day            397924 non-null int64
dayofweek      397924 non-null int64
dayofyear      397924 non-null int64
weekofyear     397924 non-null int64
quarter        397924 non-null int64
dtypes: datetime64[ns](1), float64(2), int64(9), object(4), period[M](1)
memory usage: 64.6+ MB<jupyter_text>Exploratory Data Analysis<jupyter_code># Create sales per customer
# as_index=False is effectively “SQL-style” grouped output
sales_per_customer = df.groupby(['CustomerID', 'Country'], as_index=False)['InvoiceNo'].count().sort_values('InvoiceNo', ascending=False)
sales_per_customer.columns = ['CustomerID', 'Country', 'NumberOfSales']
sales_per_customer.head()
# Plot sales per customer
orders = df.groupby(['CustomerID', 'Country'], as_index=False)['InvoiceNo'].count()
plt.plot(orders.CustomerID, orders.InvoiceNo)
plt.xlabel('Customer')
plt.ylabel('Sales')
plt.title('Sales per Customer')
plt.show()
# Amount spent by Customers
money_spent = df.groupby(['CustomerID', 'Country'], as_index=False)['AmountSpent'].sum()
plt.plot(money_spent.CustomerID, money_spent.AmountSpent)
plt.xlabel('Customer')
plt.ylabel('Amount Spent')
plt.title('Amount Spent by Customer')
plt.show()
# Money made per customer
spent_per_customer = df.groupby(['CustomerID', 'Country'], as_index=False)['AmountSpent'].sum().sort_values('AmountSpent', ascending=False)
spent_per_customer.columns = ['CustomerID', 'Country', 'AmountSpent']
spent_per_customer.reset_index(inplace=True, drop=True)
spent_per_customer.head()
# Create year_month, hour columns
df.insert(loc=2, column='year_month', value=df.InvoiceDate.map(lambda x: 100*x.year + x.month))
df.insert(loc=5, column='hour', value=df.InvoiceDate.dt.hour)
df.head()
# Show order per month
ax = df.groupby('InvoiceNo')['year_month'].unique().value_counts().sort_index().plot(kind='bar', figsize=(10,5))
ax.set_xlabel('Month')
ax.set_ylabel('Orders per Month')
ax.set_xticklabels(('Dec_10', 'Jan_11', 'Feb_11', 'Mar_11', 'Apr_11', 'May_11', 'Jun_11', 'July_11', 'Aug_11', 'Sep_11', 'Oct_11', 'Nov_11', 'Dec_11'), rotation='horizontal')
plt.show()
# Orders by day
df.groupby('InvoiceNo')['dayofweek'].unique().value_counts().sort_index()
# Plot orders by day
ax = df.groupby('InvoiceNo')['dayofweek'].unique().value_counts().sort_index().plot.bar()
ax.set_xlabel('Day')
ax.set_ylabel('Orders')
ax.set_title('Orders by Day')
ax.set_xticklabels(('Mon', 'Tues', 'Wed', 'Thur', 'Fri', 'Sat'), rotation='horizontal')
plt.show()
# Orders by hour
ax = df.groupby('InvoiceNo')['hour'].unique().value_counts().iloc[:-1].sort_index().plot.bar()
ax.set_xlabel('Hour')
ax.set_ylabel('Orders')
ax.set_title('Orders by Hour')
ax.set_xticklabels(range(6,21), rotation='horizontal')
plt.show()
# Orders by week
ax = df.groupby('InvoiceNo')['weekofyear'].unique().value_counts().iloc[:-1].sort_index().plot.bar(figsize=(12,5))
ax.set_xlabel('Week')
ax.set_ylabel('Orders')
ax.set_title('Orders by Week')
ax.set_xticklabels(range(0,52), rotation='horizontal')
plt.show()<jupyter_output><empty_output><jupyter_text>Analysis of Unit Prices per Item<jupyter_code># Describe Unit Prices
df.UnitPrice.describe()
# Plot distribution
sns.boxplot(df.UnitPrice)
plt.show()
# Show the 0 priced items
free = df[df.UnitPrice == 0]
print(len(free))
free.head()
# Free items by year
free.year_month.value_counts().sort_index()
# Plot the free items by year
ax = free.year_month.value_counts().sort_index().plot.bar()
ax.set_xlabel('Month')
ax.set_ylabel('Count')
ax.set_title('Free Items by Year')
ax.set_xticklabels(('Dec_10', 'Jan_11', 'Feb_11', 'Mar_11', 'Apr_11', 'May_11', 'Jun_11', 'Jul_11', 'Aug_11', 'Sep_11', 'Oct_11', 'Nov_11'))
plt.show()<jupyter_output><empty_output><jupyter_text>Country Analysis<jupyter_code># Orders by country
ax = df.groupby('Country')['InvoiceDate'].count().sort_values().plot.barh(figsize=(10, 8))
ax.set_xlabel('Country')
ax.set_ylabel('Orders')
ax.set_title('Orders by Country')
plt.show()
ax = df[df.Country != 'United Kingdom'].groupby('Country')['InvoiceDate'].count().sort_values().plot.barh(figsize=(10, 8))
ax.set_xlabel('Country')
ax.set_ylabel('Orders')
ax.set_title('Orders by Country Without UK')
plt.show()
# Amount spent by country
ax = df[df.Country != 'United Kingdom'].groupby('Country')['AmountSpent'].sum().sort_values().plot.barh(figsize=(10,10))
ax.set_xlabel('Amount')
ax.set_ylabel('Country')
ax.set_title('Amount Spent by Country')
plt.show()<jupyter_output><empty_output><jupyter_text>Repeat Customers<jupyter_code># Get date range
print(f'Date Range: {df.InvoiceDate.min()} to {df.InvoiceDate.max()}')
# Amount spent by repeat customer
invoice_customer = df[df.InvoiceDate < '2011-12-01'].groupby(['InvoiceNo', 'InvoiceDate']).agg({'AmountSpent': sum, 
                                                                             'CustomerID': max, 
                                                                             'Country': max}).reset_index().sort_values('AmountSpent', ascending=False)
invoice_customer.head()
repeat_customers_df = df[df.InvoiceDate < '2011-12-01'].groupby(['InvoiceNo', 'InvoiceDate']).agg({'AmountSpent': sum,
                                                       'CustomerID': max,
                                                       'Country': max}).reset_index()
repeat_customers = repeat_customers_df.set_index('InvoiceDate').groupby([pd.Grouper(freq='M'),
                                          'CustomerID']).filter(lambda x: len(x) > 1).resample('M').nunique()['CustomerID']
repeat_customers
# Unique Customers by Month (M)
unique_customers = df[df.InvoiceDate < '2011-12-01'].set_index('InvoiceDate')['CustomerID'].resample('M').nunique()
print(unique_customers)
# Ratio of repeat to unique customers
monthly_percentages = repeat_customers/unique_customers*100.0
print((monthly_percentages).round(2))
# Plot repeat, unique customers
ax = pd.DataFrame(repeat_customers.values).plot()
pd.DataFrame(unique_customers.values).plot(ax=ax, grid=True)
ax.set_xlabel('Date')
ax.set_ylabel('Customers')
ax.set_title('Unique vs Repeat Customers')

ax2 = pd.DataFrame(monthly_percentages.values).plot.bar(ax=ax, grid=True, secondary_y=True)
ax2.set_ylabel('Percentage')

ax.legend(['Repeat Customers', 'All Customers'])
ax2.legend(['Percentage of Repeat Customers'], loc='upper right')
ax2.set_ylim([0, 100])

plt.xticks(range(len(repeat_customers.index)), [x.strftime('%m.%Y') for x in repeat_customers.index])
plt.show()
# Monthly revenue and repeat customers
monthly_revenue = df[df.InvoiceDate < '2011-12-01'].set_index('InvoiceDate')['AmountSpent'].resample('M').sum()
repeat_customer_revenue = (invoice_customer.set_index('InvoiceDate')
                                            .groupby([pd.Grouper(freq='M'), 'CustomerID'])
                                            .filter(lambda x: len(x) > 1)
                                            .resample('M').sum()['AmountSpent'])
revenue_repeat_customer = repeat_customer_revenue/monthly_revenue*100.0
revenue_repeat_customer.round(2)
# Plot revenue repeat customer revenue
ax = pd.DataFrame(monthly_revenue.values).plot(figsize=(10, 5))
pd.DataFrame(repeat_customer_revenue.values).plot(ax=ax, grid=True)
ax.set_xlabel('Date')
ax.set_ylabel('Revenue')
ax.set_title('Total Revenue vs Repeat Customer Revenue')
ax.legend(['Total Revenue', 'Repeat Customer Revenue'])
ax.set_ylim([0, max(monthly_revenue.values)+100000])

ax2 = ax.twinx()
pd.DataFrame(revenue_repeat_customer.values).plot.bar(ax=ax2)
ax2.set_ylim([0, max(revenue_repeat_customer.values)+30])
ax2.set_ylabel('Percentage')
ax2.legend(['Repeat Revenue Percentage'])
ax.set_xticklabels([x.strftime('%m.%Y') for x in revenue_repeat_customer.index], rotation='vertical')

plt.show()<jupyter_output><empty_output><jupyter_text>Item Trends<jupyter_code># Items sold by month
item_month = df.set_index('InvoiceDate').groupby([pd.Grouper(freq='M'), 'StockCode'])['Quantity'].sum()
item_month.head()
# Sort items by 2011-11-30
last_months_items = item_month.loc['2011-11-30'].reset_index().sort_values('Quantity', ascending=False)
last_months_items.head()
# Top 5 items' sales per month
top_five = (df.loc[df['StockCode']
                   .isin(['23084', '84826', '22197', '22086', '85099B'])]
                   .set_index('InvoiceDate')
                   .groupby([pd.Grouper(freq='M'), 'StockCode'])['Quantity'].sum())
                   
top_five
# Monthly trend of top 5
trending_items = (top_five.reset_index()
                    .pivot('InvoiceDate', 'StockCode')
                    .fillna(0)
                    .reset_index()
                    .set_index('InvoiceDate'))

trending_items
# Plot top five trends
ax = pd.DataFrame(trending_items.values).plot(grid=True)
ax.set_xlabel('Date')
ax.set_ylabel('Purchases')
ax.set_title('Top Five Items Trend')
ax.legend(trending_items.columns, loc='upper left')
plt.xticks(range(len(trending_items.index)), [x.strftime('%m.%Y') for x in trending_items.index], rotation='vertical')
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16005579286897392, 'fraction_numerical': 0.03190654694446866, 'mean_word_length': 5.006282722513089, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 28, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '153954', 'license': 'BSD-2-Clause', 'n_tokens_mistral': 4223, 'n_tokens_neox': 3902, 'n_words': 729, 'path': 'ProductRetailAnalysis.ipynb', 'repo_name': 'cliffwhitworth/regression-notebooks', 'size': '11471'}