基于机器学习的电商数据挖掘 | 数据探索篇

import pandas as pd
import numpy as np

import time
import os
from datetime import datetime
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline
#设置中文编码和负号的正常显示
plt.rcParams['font.sans-serif']=['SimHei']
plt.rcParams['axes.unicode_minus']=False

import plotly_express as px
import plotly.graph_objects as go

import missingno as ms

from sklearn.cluster import KMeans
from sklearn.preprocessing import MinMaxScaler
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

df = pd.read_csv(
    "ic_sale.csv",
     encoding="utf-8",  # 指定编码
     cnotallow={"order_id":str,"product_id":str,"category_id":str,"user_id":str} # 指定字段类型
)  
df.head()
1.
2.
3.
4.
5.
6.

# 1、数据shape

df.shape
1.
2.
3.

(564169, 11)
1.

# 2、数据字段类型

df.dtypes
1.
2.
3.

event_time        object
order_id          object
product_id        object
category_id       object
category_code     object
brand             object
price            float64
user_id           object
age                int64
sex               object
local             object
dtype: object
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

# 3、数据描述统计信息

df.describe()
1.
2.
3.

# 4、总共多少个不同客户

df["user_id"].nunique()
1.
2.
3.

6908
1.

# 5、总共多少个不同品牌

df["brand"].nunique()
1.
2.
3.

868
1.

# 6、总共多少个订单

df["order_id"].nunique()
1.
2.
3.

234232
1.

# 7、总共多少个产品

df["product_id"].nunique()
1.
2.
3.

3756
1.

df = df[df["price"] > 0]
1.

df.isnull().sum()
1.

event_time            0
order_id              0
product_id            0
category_id           0
category_code    129344
brand             27215
price                 0
user_id               0
age                   0
sex                   0
local                 0
dtype: int64
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

ms.bar(df,color="blue")  # 缺失值可视化

plt.show()
1.
2.
3.

df.fillna("missing",inplace=True)
1.

df.isnull().sum()  # 填充之后无缺失值
1.

event_time       0
order_id         0
product_id       0
category_id      0
category_code    0
brand            0
price            0
user_id          0
age              0
sex              0
local            0
dtype: int64
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

df["event_time"][:5]   # 处理前
1.

0    2020-04-24 11:50:39 UTC
1    2020-04-24 11:50:39 UTC
2    2020-04-24 14:37:43 UTC
3    2020-04-24 14:37:43 UTC
4    2020-04-24 19:16:21 UTC
Name: event_time, dtype: object
1.
2.
3.
4.
5.
6.

# 去掉最后的UTC
df["event_time"] = df["event_time"].apply(lambda x: x[:19])
1.
2.

# 时间数据类型转化：字符类型---->指定时间格式

df['event_time'] = pd.to_datetime(df['event_time'], format="%Y-%m-%d %H:%M:%S")
1.
2.
3.

# 提取多个时间相关字段

df['month']=df['event_time'].dt.month
df['day'] = df['event_time'].dt.day
df['dayofweek']=df['event_time'].dt.dayofweek
df['hour']=df['event_time'].dt.hour
1.
2.
3.
4.
5.
6.

df["event_time"][:5]   # 处理后
1.

0   2020-04-24 11:50:39
1   2020-04-24 11:50:39
2   2020-04-24 14:37:43
3   2020-04-24 14:37:43
4   2020-04-24 19:16:21
Name: event_time, dtype: datetime64[ns]
1.
2.
3.
4.
5.
6.

amount_by_month = df.groupby("month")["price"].sum().reset_index()
amount_by_month
1.
2.

fig = px.scatter(amount_by_month,x="month",y="price",size="price",color="price")

fig.update_layout(height=500, width=1000, title_text="每月成交金额")

fig.show()
1.
2.
3.
4.
5.

order_by_month = df.groupby("month")["order_id"].nunique().reset_index()
order_by_month
1.
2.

fig = px.line(order_by_month,x="month",y="order_id")

fig.update_layout(height=500, width=1000, title_text="每月成交订单量")

fig.show()
1.
2.
3.
4.
5.

# nunique：对每个user_id进行去重：消费人数
# count：统计user_id 的次数；消费人次（存在一人多次购买）

people_by_month = df.groupby("month")["user_id"].agg(["nunique","count"]).reset_index()
people_by_month
1.
2.
3.
4.
5.

fig = px.line(people_by_month,x="month",y="nunique")

fig.update_layout(height=500, width=1000, title_text="每月成交人数")

fig.show()
1.
2.
3.
4.
5.

fig = px.line(people_by_month,x="month",y="count")

fig.update_layout(height=500, width=1000, title_text="每月成交人次")

fig.show()
1.
2.
3.
4.
5.

amount_by_month  # 每月成交金额
1.

order_by_month  # 每月订单数
1.

amount_by_userid = pd.merge(amount_by_month,order_by_month)

amount_by_userid
1.
2.
3.

amount_by_userid["average"] = amount_by_userid["price"] / amount_by_userid["order_id"]

amount_by_userid
1.
2.
3.

fig = px.line(amount_by_userid,x="month",y="average")

fig.update_layout(height=500, width=1000, title_text="每月客单价")

fig.show()
1.
2.
3.
4.
5.

product_by_order = df.groupby("order_id")["product_id"].count().reset_index().sort_values("product_id",ascending=False)

product_by_order.head(10)
1.
2.
3.

fig = px.bar(product_by_order[:20],
             x="order_id",
             y="product_id",
             text="product_id"
            )

fig.show()
1.
2.
3.
4.
5.
6.
7.

local = df.groupby("local").agg({"order_id":"nunique","user_id":"nunique","price":sum}).reset_index()
local.head()
1.
2.

df1 = local.sort_values("order_id",ascending=True)  # 订单量升序
df1
1.
2.

fig = px.pie(df1, names="local",labels="local",values="price")

fig.update_traces(
    textpositinotallow="inside",
    textinfo="percent+label"
)

fig.show()
1.
2.
3.
4.
5.
6.
7.
8.

fig = px.bar(df1,x="order_id",y="local",orientatinotallow="h")

fig.show()
1.
2.
3.

# 整体的可视化效果

fig = px.scatter_3d(local,
              x="order_id",
              y="user_id",
              z="price",
              color="order_id",
              hover_name="local"
             )

fig.show()
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

local_brand = df.groupby(["local","brand"]).size().to_frame().reset_index()

local_brand.columns = ["local","brand","number"]  # 修改字段名

local_brand
1.
2.
3.
4.
5.

# 根据local和number进行排序
local_brand.sort_values(["local","number"],ascending=[True,False],inplace=True,ignore_index=True)
local_brand = local_brand[local_brand["brand"] != "missing"]
# 每个local下面最受欢迎的前3个品牌
local_brand = local_brand.groupby("local").head(3)
local_brand
1.
2.
3.
4.
5.
6.

fig = px.bar(local_brand,
             x="brand",
             y="number",
             color="number",
             facet_col="local")

fig.update_layout(height=500,width=1000)

fig.show()
1.
2.
3.
4.
5.
6.
7.
8.
9.

df.columns
1.

Index(['event_time', 'order_id', 'product_id', 'category_id', 'category_code',
       'brand', 'price', 'user_id', 'age', 'sex', 'local', 'month', 'day',
       'dayofweek', 'hour'],
      dtype='object')
1.
2.
3.
4.

df2 = df.groupby("dayofweek")["order_id"].nunique().reset_index()
df2
1.
2.

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

df2["order_id"].plot.bar()

plt.xticks(range(7),['周一','周二','周三','周四','周五','周六','周日'],rotatinotallow=0)
plt.xlabel('星期')
plt.ylabel('订单量')
plt.title('订单数随星期变化')

plt.show()
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

df3 = df.groupby("hour")["order_id"].nunique().reset_index()
df3.head(10)
1.
2.

plt.figure(figsize=(14,8))
df3["order_id"].plot()

plt.xlabel('小时')
plt.ylabel('订单数量')
plt.title('订单随小时数变化')

plt.grid()
plt.show()
1.
2.
3.
4.
5.
6.
7.
8.
9.

df4 = df.groupby("user_id").agg({"order_id":"nunique", "price":sum})

fig = px.scatter(df4,
                x="order_id",
                y="price",
                color="price",
                size="price")

fig.show()
1.
2.
3.
4.
5.
6.
7.
8.
9.

# 用户消费周期

# shift函数：移动一个单位

purchase_time=df.groupby('user_id').apply(lambda x: x['event_time'] - x['event_time'].shift()).dt.days
purchase_time
1.
2.
3.
4.
5.
6.

user_id                    
1515915625439950000  96014      NaN
1515915625440030000  374760     NaN
                     484927    35.0
1515915625440050000  463812     NaN
                     473430     1.0
                               ... 
1515915625514880000  564132     0.0
                     564143     0.0
                     564164     0.0
1515915625514890000  564158     NaN
                     564165     0.0
Name: event_time, Length: 564130, dtype: float64
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

purchase_time[purchase_time>0].describe()
1.

count    120629.000000
mean         35.494500
std         663.803583
min           1.000000
25%           2.000000
50%           4.000000
75%          12.000000
max       18466.000000
Name: event_time, dtype: float64
1.
2.
3.
4.
5.
6.
7.
8.
9.

pivoted_counts = df.pivot_table(index='user_id',
               columns='month',
               values='order_id',
               aggfunc='nunique').fillna(0)

pivoted_counts
1.
2.
3.
4.
5.
6.

pivoted_counts_map.sum() / pivoted_counts_map.count()

# 结果
month
1     0.406340
2     0.439655
3     0.474640
4     0.700328
5     0.829861
6     0.792990
7     0.891452
8     0.920328
9     0.781153
10    0.609963
11    0.419592
dtype: float64
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

(pivoted_counts_map.sum()/pivoted_counts_map.count()).plot(figsize=(12,6))

plt.xticks(range(11),columns_month)

plt.title('复购率')
plt.show()
1.
2.
3.
4.
5.
6.