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

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

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、数据shape df.shape

(564169, 11)

# 2、数据字段类型 df.dtypes

event_timeobject order_idobject product_idobject category_id object category_code object brand object pricefloat64 user_id object ageint64 sex object local object dtype: object

# 3、数据描述统计信息 df.describe()

# 4、总共多少个不同客户 df["user_id"].nunique()

6908

# 5、总共多少个不同品牌 df["brand"].nunique()

868

# 6、总共多少个订单 df["order_id"].nunique()

234232

# 7、总共多少个产品 df["product_id"].nunique()

3756

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

df.isnull().sum()

event_time0 order_id0 product_id0 category_id 0 category_code129344 brand 27215 price 0 user_id 0 age 0 sex 0 local 0 dtype: int64

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

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

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

event_time 0 order_id 0 product_id 0 category_id0 category_code0 brand0 price0 user_id0 age0 sex0 local0 dtype: int64

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

02020-04-24 11:50:39 UTC 12020-04-24 11:50:39 UTC 22020-04-24 14:37:43 UTC 32020-04-24 14:37:43 UTC 42020-04-24 19:16:21 UTC Name: event_time, dtype: object

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

# 时间数据类型转化：字符类型---->指定时间格式 df['event_time'] = pd.to_datetime(df['event_time'], format="%Y-%m-%d %H:%M:%S")

# 提取多个时间相关字段 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

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

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]

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

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()

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

fig = px.line(order_by_month,x="month",y="order_id") fig.update_layout(height=500, width=1000, title_text="每月成交订单量") fig.show()

# nunique：对每个user_id进行去重：消费人数 # count：统计user_id 的次数；消费人次（存在一人多次购买） people_by_month = df.groupby("month")["user_id"].agg(["nunique","count"]).reset_index() people_by_month

fig = px.line(people_by_month,x="month",y="nunique") fig.update_layout(height=500, width=1000, title_text="每月成交人数") fig.show()

fig = px.line(people_by_month,x="month",y="count") fig.update_layout(height=500, width=1000, title_text="每月成交人次") fig.show()

amount_by_month# 每月成交金额

order_by_month# 每月订单数

amount_by_userid = pd.merge(amount_by_month,order_by_month) amount_by_userid

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

fig = px.line(amount_by_userid,x="month",y="average") fig.update_layout(height=500, width=1000, title_text="每月客单价") fig.show()

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

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

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

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

fig = px.pie(df1, names="local",labels="local",values="price") fig.update_traces( textpositinotallow="inside", textinfo="percent+label" ) fig.show()

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

# 整体的可视化效果 fig = px.scatter_3d(local, x="order_id", y="user_id", z="price", color="order_id", hover_name="local" ) fig.show()

local_brand = df.groupby(["local","brand"]).size().to_frame().reset_index() local_brand.columns = ["local","brand","number"]# 修改字段名 local_brand

# 根据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

fig = px.bar(local_brand, x="brand", y="number", color="number", facet_col="local") fig.update_layout(height=500,width=1000) fig.show()

df.columns

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

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

plt.figure(figsize=(12,7)) df2["order_id"].plot.bar() plt.xticks(range(7),['周一','周二','周三','周四','周五','周六','周日'],rotatinotallow=0) plt.xlabel('星期') plt.ylabel('订单量') plt.title('订单数随星期变化') plt.show()

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

plt.figure(figsize=(14,8)) df3["order_id"].plot() plt.xlabel('小时') plt.ylabel('订单数量') plt.title('订单随小时数变化') plt.grid() plt.show()

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()

# 用户消费周期 # shift函数：移动一个单位 purchase_time=df.groupby('user_id').apply(lambda x: x['event_time'] - x['event_time'].shift()).dt.days purchase_time

user_id 151591562543995000096014NaN 1515915625440030000374760 NaN 48492735.0 1515915625440050000463812 NaN 473430 1.0 ... 1515915625514880000564132 0.0 564143 0.0 564164 0.0 1515915625514890000564158 NaN 564165 0.0 Name: event_time, Length: 564130, dtype: float64

purchase_time[purchase_time>0].describe()

count120629.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

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

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 100.609963 110.419592 dtype: float64

(pivoted_counts_map.sum()/pivoted_counts_map.count()).plot(figsize=(12,6)) plt.xticks(range(11),columns_month) plt.title('复购率') plt.show()