In [1]:
import os
import pandas as pd
In [2]:
for root, dirs, files in os.walk("data/chapter_1"):
for filename in files:
print(filename)
In [8]:
import pandas as pd
cohort_counts = pd.read_csv("data/chapter_1/cohort_counts.csv",index_col='CohortMonth')
In [26]:
cohort_counts.head()
Out[26]:
Use loc, idea from Stack Overflow
In [29]:
cohort_counts.loc[cohort_counts.index.values == '2011-01-01']
Out[29]:
Thus, in Jan, there are 332 transactions.
In [38]:
online = pd.read_csv("data/chapter_1/online.csv",index_col = 0)
In [46]:
online.head()
Out[46]:
In [89]:
online.InvoiceDate.describe()
Out[89]:
Use apply function.
Because .date() only supports elements instead of series.
For example,
In [93]:
import pandas as pd
date=pd.to_datetime('2015-01-08 22:44:09').date()
print(date)
In [91]:
def get_date(x):
return x.date()
online['InvoiceDay'] = pd.to_datetime(online['InvoiceDate']).apply(get_date)
In [111]:
online['CohortDay'] = online.groupby('CustomerID')['InvoiceDay'].transform('min')
In [113]:
online.head()
Out[113]:
Get the first transcation date per customer.
In [133]:
def get_date_int(df,col):
year = df[col].map(lambda x: x.year)
month = df[col].map(lambda x: x.month)
day = df[col].map(lambda x: x.day)
return year, month, day
In [134]:
online.head()
Out[134]:
In [136]:
invoice_year, invoice_month, invoice_day = get_date_int(online, 'InvoiceDay')
cohort_year, cohort_month, cohort_day = get_date_int(online, 'CohortDay')
Calculate time offset in days.
In [141]:
online['CohortIndex'] = \
(invoice_year - cohort_year) * 365 + \
(invoice_month - cohort_month) * 30 + \
(invoice_day - cohort_day) * 1 + \
1
When you get time cohort and cohort index, the last one we do is to get the metrics. Let's think about the customer retention.
In [144]:
grouping = online.groupby(['CohortDay', 'CohortIndex'])
In [147]:
cohort_data = grouping['CustomerID'].apply(pd.Series.nunique).reset_index()
cohort_data.head()
Out[147]:
.reset_index() helps get CohortDay and CohortIndex as normal columns.
In [149]:
cohort_counts = cohort_data.pivot(index='CohortDay', columns='CohortIndex', values='CustomerID')
cohort_counts.head()
Out[149]:
In [151]:
cohort_sizes = cohort_counts.iloc[:,0]
retention = cohort_counts.divide(cohort_sizes, axis=0)
In [153]:
retention.head()
Out[153]:
CohortDay is under daily base. I try a monthly base.
In [187]:
online['CohortMonth'] = online['CohortDay'].values.astype('datetime64[M]')
Get the first day of month from Stack Overflow
In [189]:
online['CohortIndexMonth'] = \
(invoice_year - cohort_year) * 12 + \
(invoice_month - cohort_month) * 1 + \
1
In [193]:
grouping = online.groupby(['CohortMonth', 'CohortIndexMonth'])
cohort_data_month = grouping['CustomerID'].apply(pd.Series.nunique).reset_index()
cohort_data_month.head()
Out[193]:
In [195]:
cohort_counts_month = cohort_data_month.pivot(index='CohortMonth', columns='CohortIndexMonth', values='CustomerID')
cohort_counts_month.head()
Out[195]:
In [196]:
cohort_sizes_month = cohort_counts_month.iloc[:,0]
retention_month = cohort_counts_month.divide(cohort_sizes_month, axis=0)
In [198]:
retention_month.head()
Out[198]:
We get the number of customer above, next we do the cohort for average price.
In [220]:
online
Out[220]:
In [226]:
average_quantity = online.groupby(['CohortMonth','CohortIndexMonth'])['UnitPrice'].mean().reset_index() \
.pivot(index='CohortMonth', columns='CohortIndexMonth', values='UnitPrice') \
.round(1)
# online.groupby(['CohortMonth','CohortMonth'])['UnitPrice'].apply(pd.Series.mean)
average_quantity
Out[226]:
Visualization
In [228]:
# Import seaborn package as sns
import seaborn as sns
import matplotlib.pyplot as plt
# Initialize an 8 by 6 inches plot figure
plt.figure(figsize=(8, 6))
# Add a title
plt.title('Average Spend by Monthly Cohorts')
# Create the heatmap
sns.heatmap(average_quantity, annot=True, cmap='Blues')
plt.show()
