用户群组分析Cohort analysis、RFM用户分层模型、Kmeans用户聚类模型

import pandas as pd
import numpy as np

import seaborn as sns
sns.set_style("darkgrid")
import matplotlib.pyplot as plt
from mpl_toolkits import mplot3d
import plotly_express as px
import plotly.graph_objects as go

from sklearn.cluster import KMeans   # Kmeans聚类模型
from sklearn.metrics import silhouette_score # 聚类效果评价：轮廓系数
from sklearn.preprocessing import StandardScaler # 数据标准化

from wordcloud import WordCloud  
import jieba 
import nltk
from nltk.corpus import stopwords  
from nltk.tokenize import word_tokenize  
nltk.download('stopwords')
import string  

import warnings
warnings.filterwarnings("ignore")

df = pd.read_excel("Online Retail.xlsx")
df.head()

df.shape 

(541909, 8)

df.dtypes  # 每个字段的类型

InvoiceNo              object
StockCode              object
Description            object
Quantity                int64
InvoiceDate    datetime64[ns]
UnitPrice             float64
CustomerID            float64
Country                object
dtype: object

df.columns 

Index(['InvoiceNo', 'StockCode', 'Description', 'Quantity', 'InvoiceDate',
       'UnitPrice', 'CustomerID', 'Country'],
      dtype='object')

df.info()  # 字段名、非缺失值个数、字段类型
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 541909 entries, 0 to 541908
Data columns (total 8 columns):
 #   Column       Non-Null Count   Dtype         
---  ------       --------------   -----         
 0   InvoiceNo    541909 non-null  object        
 1   StockCode    541909 non-null  object        
 2   Description  540455 non-null  object        
 3   Quantity     541909 non-null  int64         
 4   InvoiceDate  541909 non-null  datetime64[ns]
 5   UnitPrice    541909 non-null  float64       
 6   CustomerID   406829 non-null  float64       
 7   Country      541909 non-null  object        
dtypes: datetime64[ns](1), float64(2), int64(1), object(4)
memory usage: 33.1+ MB

df.isnull().sum()

InvoiceNo           0
StockCode           0
Description      1454
Quantity            0
InvoiceDate         0
UnitPrice           0
CustomerID     135080
Country             0
dtype: int64

df.isnull().sum() / len(df)

InvoiceNo      0.000000
StockCode      0.000000
Description    0.002683
Quantity       0.000000
InvoiceDate    0.000000
UnitPrice      0.000000
CustomerID     0.249267
Country        0.000000
dtype: float64

df[df.duplicated() == True].head()

df.duplicated().sum()

5268

print(f"数据中总共的重复行数 {df.duplicated().sum()} 条")
数据中总共的重复行数 5268 条

df.shape

(541909, 8)

df = df[~df.duplicated()]
df.shape

(536641, 8)

536641 + 5268

541909

df["InvoiceNo"].dtype  # 字符类型

dtype('O')

df["InvoiceNo"].value_counts()  # 取值的数量

InvoiceNo
573585     1114
581219      749
581492      731
580729      721
558475      705
           ... 
570518        1
C550935       1
550937        1
550940        1
C558901       1
Name: count, Length: 25900, dtype: int64

df[df["InvoiceNo"].str.startswith("C") == True].head(10)      # 出现取消或退货的数据

# 选择非C开头的用户

df = df[df["InvoiceNo"].str.startswith("C") != True]
df.shape

(527390, 8)

print(f"总共不同的InvoiceNo数量为: {df['InvoiceNo'].nunique()}")
总共不同的InvoiceNo数量为: 22064

df["StockCode"].dtype  # 字符类型

dtype('O')

print(f"总共不同的StockCode数量为: {df['StockCode'].nunique()}")
总共不同的StockCode数量为: 4059

df["StockCode"].value_counts()

StockCode
85123A    2259
85099B    2112
22423     2012
47566     1700
20725     1582
          ... 
22143        1
44242A       1
35644        1
90048        1
23843        1
Name: count, Length: 4059, dtype: int64

df.columns

Index(['InvoiceNo', 'StockCode', 'Description', 'Quantity', 'InvoiceDate',
       'UnitPrice', 'CustomerID', 'Country'],
      dtype='object')

des_list = df["Description"].tolist()  # 全部的Description列表
des_list[:5]

['WHITE HANGING HEART T-LIGHT HOLDER',
 'WHITE METAL LANTERN',
 'CREAM CUPID HEARTS COAT HANGER',
 'KNITTED UNION FLAG HOT WATER BOTTLE',
 'RED WOOLLY HOTTIE WHITE HEART.']

des_list = [str(i) for i in des_list]  # 评价中可能出现的数值强制转成字符串

text = " ".join(des_list)  # 所有数据构成的文本信息
text[:500]

"WHITE HANGING HEART T-LIGHT HOLDER WHITE METAL LANTERN CREAM CUPID HEARTS COAT HANGER KNITTED UNION FLAG HOT WATER BOTTLE RED WOOLLY HOTTIE WHITE HEART. SET 7 BABUSHKA NESTING BOXES GLASS STAR FROSTED T-LIGHT HOLDER HAND WARMER UNION JACK HAND WARMER RED POLKA DOT ASSORTED COLOUR BIRD ORNAMENT POPPY'S PLAYHOUSE BEDROOM  POPPY'S PLAYHOUSE KITCHEN FELTCRAFT PRINCESS CHARLOTTE DOLL IVORY KNITTED MUG COSY  BOX OF 6 ASSORTED COLOUR TEASPOONS BOX OF VINTAGE JIGSAW BLOCKS  BOX OF VINTAGE ALPHABET BLOCK"

# 初始化NLTK的停用词集  
nltk_stopwords = set(stopwords.words('english'))  
  
# 添加额外的停用词，比如标点符号  
additional_stopwords = set(string.punctuation)  
  
# 合并停用词集  
stopwords_set = nltk_stopwords.union(additional_stopwords)  
  
# 分词  
words = word_tokenize(text)  
  
# 去除停用词  
filtered_words = [word for word in words if word.lower() not in stopwords_set]  
  
# 将过滤后的单词连接成字符串，用空格分隔  
filtered_text = ' '.join(filtered_words)  
  
# 创建词云对象  
wordcloud = WordCloud(width=800, height=400, background_color='white', min_font_size=10).generate(filtered_text)  
  
# 显示词云图  
plt.figure(figsize=(10, 5), facecolor=None)  
plt.imshow(wordcloud)  
plt.axis("off")  
plt.tight_layout(pad=0)  
  
plt.show()

df["Quantity"].dtype  # 数值类型 

dtype('int64')

df["Quantity"].describe()  

count    527390.000000
mean         10.311272
std         160.367285
min       -9600.000000
25%           1.000000
50%           3.000000
75%          11.000000
max       80995.000000
Name: Quantity, dtype: float64

sns.boxplot(df["Quantity"])

plt.show()

df = df[(df["Quantity"] > 0) & (df["Quantity"] < 70000)]  # 只要大于0且小于70000的部分
df.shape

(526052, 8)

df.columns

Index(['InvoiceNo', 'StockCode', 'Description', 'Quantity', 'InvoiceDate',
       'UnitPrice', 'CustomerID', 'Country'],
      dtype='object')

df["InvoiceDate"].dtype  # 时间类型数据

dtype('<M8[ns]')

print("最早出现的时间：",df["InvoiceDate"].min())  # 最早时间
print("最近出现的时间：",df["InvoiceDate"].max())  # 最近时间
最早出现的时间： 2010-12-01 08:26:00
最近出现的时间： 2011-12-09 12:50:00

df["InvoiceDate"].value_counts()  

InvoiceDate
2011-10-31 14:41:00    1114
2011-12-08 09:28:00     749
2011-12-09 10:03:00     731
2011-12-05 17:24:00     721
2011-06-29 15:58:00     705
                       ... 
2011-10-06 10:53:00       1
2011-01-07 14:44:00       1
2011-10-06 10:34:00       1
2011-05-27 16:23:00       1
2011-03-22 11:54:00       1
Name: count, Length: 19050, dtype: int64

df["UnitPrice"].dtype  # 浮点型

dtype('float64')

df["UnitPrice"].describe()  

count    526052.000000
mean          3.871756
std          42.016640
min      -11062.060000
25%           1.250000
50%           2.080000
75%           4.130000
max       13541.330000
Name: UnitPrice, dtype: float64

df = df[df["UnitPrice"] > 0]  # 只要大于0的部分
df.shape

(524876, 8)

df["Country"].value_counts()[:20]

Country
United Kingdom     479983
Germany              9025
France               8392
EIRE                 7879
Spain                2479
Netherlands          2359
Belgium              2031
Switzerland          1958
Portugal             1492
Australia            1181
Norway               1071
Italy                 758
Channel Islands       747
Finland               685
Cyprus                603
Sweden                450
Unspecified           442
Austria               398
Denmark               380
Poland                330
Name: count, dtype: int64

# 转化成比例

df["Country"].value_counts(normalize=True)[:20]

Country
United Kingdom     0.914469
Germany            0.017195
France             0.015989
EIRE               0.015011
Spain              0.004723
Netherlands        0.004494
Belgium            0.003869
Switzerland        0.003730
Portugal           0.002843
Australia          0.002250
Norway             0.002040
Italy              0.001444
Channel Islands    0.001423
Finland            0.001305
Cyprus             0.001149
Sweden             0.000857
Unspecified        0.000842
Austria            0.000758
Denmark            0.000724
Poland             0.000629
Name: proportion, dtype: float64

df["Country"] = df["Country"].apply(lambda x: "UK" if x == "United Kingdom" else "Others")

df["Country"].value_counts()

Country
UK        479983
Others     44893
Name: count, dtype: int64

df.isnull().sum()

InvoiceNo           0
StockCode           0
Description         0
Quantity            0
InvoiceDate         0
UnitPrice           0
CustomerID     132186
Country             0
dtype: int64

df = df[~df.CustomerID.isnull()]

df.shape

(392690, 8)

df.info()
<class 'pandas.core.frame.DataFrame'>
Index: 392690 entries, 0 to 541908
Data columns (total 8 columns):
 #   Column       Non-Null Count   Dtype         
---  ------       --------------   -----         
 0   InvoiceNo    392690 non-null  object        
 1   StockCode    392690 non-null  object        
 2   Description  392690 non-null  object        
 3   Quantity     392690 non-null  int64         
 4   InvoiceDate  392690 non-null  datetime64[ns]
 5   UnitPrice    392690 non-null  float64       
 6   CustomerID   392690 non-null  float64       
 7   Country      392690 non-null  object        
dtypes: datetime64[ns](1), float64(2), int64(1), object(4)
memory usage: 27.0+ MB

# 总金额
df['Amount'] = df['Quantity']*df['UnitPrice']

# 时间特征
df['year'] = df['InvoiceDate'].dt.year # 年-月-日-小时-星期几
df['month'] = df['InvoiceDate'].dt.month
df['day'] = df['InvoiceDate'].dt.day
df['hour'] = df['InvoiceDate'].dt.hour
df['day_of_week'] = df['InvoiceDate'].dt.dayofweek

df.columns

Index(['InvoiceNo', 'StockCode', 'Description', 'Quantity', 'InvoiceDate',
       'UnitPrice', 'CustomerID', 'Country', 'Amount', 'year', 'month', 'day',
       'hour', 'day_of_week'],
      dtype='object')

df[df["Country"] == "UK"].groupby("Description")["InvoiceNo"].nunique().sort_values(ascending=False)

Description
WHITE HANGING HEART T-LIGHT HOLDER     1884
JUMBO BAG RED RETROSPOT                1447
REGENCY CAKESTAND 3 TIER               1410
ASSORTED COLOUR BIRD ORNAMENT          1300
PARTY BUNTING                          1290
                                       ... 
GLASS AND BEADS BRACELET IVORY            1
GIRLY PINK TOOL SET                       1
BLUE/GREEN SHELL NECKLACE W PENDANT       1
WHITE ENAMEL FLOWER HAIR TIE              1
MIDNIGHT BLUE VINTAGE EARRINGS            1
Name: InvoiceNo, Length: 3843, dtype: int64

column = ['InvoiceNo','Amount']

# 设置图片大小
plt.figure(figsize=(15,5))

for i,j in enumerate(column):
    plt.subplot(1,2,i+1)  # 绘制子图
    # 基于Description分组统计InvoiceNo 或者 Amount下的唯一值个数，降序排列，取出前10个数据
    # x-数值（唯一值数据的个数）  y-index（具体名称）
    sns.barplot(x = df[df["Country"] == "UK"].groupby("Description")[j].nunique().sort_values(ascending=False).head(10).values,
                y = df[df["Country"] == "UK"].groupby("Description")[j].nunique().sort_values(ascending=False).head(10).index,
                color="blue"
               )
    
    plt.ylabel("")  # y轴label
    
    if i == 0:  # x轴label和挑剔设置
        plt.xlabel("Sum of quantity")
        plt.title("Top10 products purchased by customers in UK",size=12)
    else:
        plt.xlabel("Total Sales")
        plt.title("Top10 products with most sales in UK", size=12)

plt.tight_layout()
plt.show()

Country =["Others","UK"]

# 设置图片大小
plt.figure(figsize=(15,5))

for i,j in enumerate(Country):
    plt.subplot(1,2,i+1)  # 绘制子图
    # 基于Description分组统计UnitPrice的均值，降序排列，取出前10个数据
    # x-数值（唯一值数据的个数）  y-index（具体名称）
    sns.barplot(x = df[df["Country"] == j].groupby("Description")["UnitPrice"].mean().sort_values(ascending=False).head(10).values,
                y = df[df["Country"] == j].groupby("Description")["UnitPrice"].mean().sort_values(ascending=False).head(10).index,
                color="yellow"
               )
    
    plt.ylabel("")  # y轴label
    
    if i == 0:  # x轴label和挑剔设置
        plt.xlabel("Unit Price")
        plt.title("Top10 products outside UK",size=12)
    else:
        plt.xlabel("Unit Price")
        plt.title("Top10 products in UK", size=12)

plt.tight_layout()
plt.show()

# 4个统计值信息：偏度、峰度、均值、中位数

skewness = round(df.Quantity.skew(),2)
kurtosis = round(df.Quantity.kurtosis(),2)
mean = round(np.mean(df.Quantity),0)
median = np.median(df.Quantity)

skewness, kurtosis, mean, median

(29.87, 1744.24, 13.0, 6.0)

plt.figure(figsize=(10,7))

# 第一个图体现完整数据信息
plt.subplot(2,2,1)
sns.boxplot(y=df.Quantity)
plt.title('Boxplot\n Mean:{}\n Median:{}\n Skewness:{}\n Kurtosis:{}'.format(mean,median,skewness,kurtosis))

# 第二个图体现小于5000的信息
plt.subplot(2,2,2)
sns.boxplot(y=df[df.Quantity<5000]['Quantity'])
plt.title('Quantity<5000')

# 第二个图体现小于200的信息
plt.subplot(2,2,3)
sns.boxplot(y=df[df.Quantity<200]['Quantity'])
plt.title('Quantity<200')

# 第二个图体现小于50的信息
plt.subplot(2,2,4)
sns.boxplot(y=df[df.Quantity<50]['Quantity'])
plt.title('Quantity<50')

plt.show()

plt.figure(figsize=(15,5))

# 子图1
plt.subplot(1,2,1)
# x-index(具体名称)  y-和的大小排序，取前10
sns.barplot(x = df[df['Country']=='UK'].groupby('CustomerID')['Amount'].sum().sort_values(ascending=False).head(10).index,
            y = df[df['Country']=='UK'].groupby('CustomerID')['Amount'].sum().sort_values(ascending=False).head(10).values, 
            color='green')

plt.xlabel('Customer IDs')
plt.ylabel('Sales')
plt.xticks(rotation=45)
plt.title('Top10 customers in terms of sales in UK',size=15)

# 子图2
plt.subplot(1,2,2)
# x-index(具体名称)  y-唯一值的大小排序，取前10
sns.barplot(x = df[df['Country']=='UK'].groupby('CustomerID')['InvoiceNo'].nunique().sort_values(ascending=False).head(10).index,
            y = df[df['Country']=='UK'].groupby('CustomerID')['InvoiceNo'].nunique().sort_values(ascending=False).head(10).values, 
            color='green')
plt.xlabel('Customer IDs')
plt.ylabel('Number of visits')
plt.xticks(rotation=45)
plt.title('Top10 customers in terms of frequency in UK',size=15)

plt.show()

# 分组聚合统计

df[df["Country"] == "UK"].groupby(["year","month"])["Amount"].sum()

year  month
2010  12       496477.340
2011  1        363692.730
      2        354618.200
      3        465784.190
      4        408733.111
      5        550359.350
      6        523775.590
      7        484545.591
      8        497194.910
      9        794806.692
      10       821220.130
      11       975251.390
      12       302912.220
Name: Amount, dtype: float64

plt.figure(figsize=(12,5))

# 按照年月分组统计总额Amount
df[df["Country"] == "UK"].groupby(["year","month"])["Amount"].sum().plot(kind="line", label="UK", color="red")
df[df["Country"] == "Others"].groupby(["year","month"])["Amount"].sum().plot(kind="line", label="Others", color="blue")

plt.xlabel("Year-Month", size=12)
plt.ylabel("Total Sales", size=12)
plt.title("Sales in each year-month",size=12)
plt.legend(fontsize=12)
plt.show()

plt.figure(figsize=(12,5))

# 按照day分组统计总额Amount
df[df["Country"] == "UK"].groupby(["day"])["Amount"].sum().plot(kind="line", label="UK", color="red")
df[df["Country"] == "Others"].groupby(["day"])["Amount"].sum().plot(kind="line", label="Others", color="blue")

plt.xlabel("Day", size=12)
plt.ylabel("Total Sales", size=12)
plt.title("Sales on each day of a month",size=12)
plt.legend(fontsize=12)
plt.show()

plt.figure(figsize=(12,5))

# 按照hour分组统计总额Amount
df[df["Country"] == "UK"].groupby(["hour"])["Amount"].sum().plot(kind="line", label="UK", color="red")
df[df["Country"] == "Others"].groupby(["hour"])["Amount"].sum().plot(kind="line", label="Others", color="blue")

plt.xlabel("Hours", size=12)
plt.ylabel("Total Sales", size=12)
plt.title("Sales on each hour in a day",size=12)
plt.legend(fontsize=12)
plt.show()

df_c = df.copy() # 副本
df_c = df_c.iloc[:,:9]
df_c.head()

df_c["InvoiceMonth"] = df_c["InvoiceDate"].dt.strftime("%Y-%m")    # 字符类型
df_c["InvoiceMonth"] = pd.to_datetime(df_c["InvoiceMonth"])        # 转成时间类型

df_c.columns

Index(['InvoiceNo', 'StockCode', 'Description', 'Quantity', 'InvoiceDate',
       'UnitPrice', 'CustomerID', 'Country', 'Amount', 'InvoiceMonth'],
      dtype='object')

# 基于客户分组，再取出InvoiceMonth的最小值
df_c["CohortMonth"] = df_c.groupby("CustomerID")["InvoiceMonth"].transform("min")
df_c["CohortMonth"] = pd.to_datetime(df_c["CohortMonth"])        # 转成时间类型

df_c.info()
<class 'pandas.core.frame.DataFrame'>
Index: 392690 entries, 0 to 541908
Data columns (total 11 columns):
 #   Column        Non-Null Count   Dtype         
---  ------        --------------   -----         
 0   InvoiceNo     392690 non-null  object        
 1   StockCode     392690 non-null  object        
 2   Description   392690 non-null  object        
 3   Quantity      392690 non-null  int64         
 4   InvoiceDate   392690 non-null  datetime64[ns]
 5   UnitPrice     392690 non-null  float64       
 6   CustomerID    392690 non-null  float64       
 7   Country       392690 non-null  object        
 8   Amount        392690 non-null  float64       
 9   InvoiceMonth  392690 non-null  datetime64[ns]
 10  CohortMonth   392690 non-null  datetime64[ns]
dtypes: datetime64[ns](3), float64(3), int64(1), object(4)
memory usage: 36.0+ MB

def diff(t1,t2):
    """
    df1和df2的时间差：以月计算
    """
    return (t1.dt.year - t2.dt.year) * 12 + t1.dt.month - t2.dt.month

# t1：df_c[""InvoiceMonth]
# t2：df_c[""CohortMonth]

df_c["CohortPeriod"] = diff(df_c["InvoiceMonth"], df_c["CohortMonth"])

df_c.sample(3)  # 随机选择3条数据

cohort_matrix = df_c.pivot_table(
    index="CohortMonth",
    columns="CohortPeriod",
    values="CustomerID",   #  基于CustomerID的唯一值
    aggfunc="nunique"
)

cohort_matrix

cohort_size = cohort_matrix.iloc[:, 0]
cohort_size

CohortMonth
2010-12-01    885.0
2011-01-01    416.0
2011-02-01    380.0
2011-03-01    452.0
2011-04-01    300.0
2011-05-01    284.0
2011-06-01    242.0
2011-07-01    188.0
2011-08-01    169.0
2011-09-01    299.0
2011-10-01    358.0
2011-11-01    323.0
2011-12-01     41.0
Name: 0, dtype: float64

retention = cohort_matrix.divide(cohort_size, axis=0)
retention

retention.index = pd.to_datetime(retention.index).date

retention.round(3) * 100  # 转换成百分比对应的大小

plt.figure(figsize=(15,8))
sns.heatmap(data=retention, 
            annot=True, 
            fmt=".0%", 
            cmap="BuGn"  # Blues，BuGn，GnBu，GnBu，PuRd，coolwarm,summer_r
           )  


plt.title("Retention Rates over one year period", size=15)
plt.show()

cohort_amount = df_c.pivot_table(
    index="CohortMonth",
    columns="CohortPeriod",
    values="Amount",   #  基于Amount的均值mean
    aggfunc="mean").round(2)  # 保留两位小数

cohort_amount

cohort_amount.index = pd.to_datetime(cohort_amount.index).date  # 索引的改变

cohort_amount