导包
import os
import sqlite3
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from IPython.display import display_htmlpd.set_option('max_colwidth',200)%matplotlib inline
前期准备
转义路径
# 获取Windows系统下的路径
win_path = r"\会员消费报表.xlsx"
win_path1 = r"\会员信息查询.xlsx"
win_path2 = r"\门店信息表.xlsx"
win_path3 = r"\全国销售订单数量表.xlsx"# 使用os.path进行转义
unix_path = os.path.normpath(win_path) # 或者 os.path.abspath(win_path)
unix_path1 = os.path.normpath(win_path1) # 或者 os.path.abspath(win_path)
unix_path2 = os.path.normpath(win_path2) # 或者 os.path.abspath(win_path)
unix_path3 = os.path.normpath(win_path3) # 或者 os.path.abspath(win_path)# 现在unix_path变量将是 'C:/Users/username/Documents/data.csv'
调整表格样式
html = f"""
<html>
<head><style>div{{background-color: rgba(224, 255, 255, 0.03); /* 包裹表格的 div 背景色 */}}th{{background-color: #79CDCD; /* 表头背景色 */color: black; /* 表头文字颜色 */}}</style>
</head>
</html>
"""
display_html(html, raw=True)
1.1读入表格
1.1.1会员消费表vip_consume
vip_consume = pd.read_excel(unix_path)
vip_consume.head(5)
1.1.2会员信息查询vip_info
vip_info = pd.read_excel(unix_path1)
vip_info.head(5)
1.1.3门店信息表shop_info
shop_info = pd.read_excel(unix_path2)
shop_info.head(5)
1.1.4全国销售订单数量表order_list
order_list = pd.read_excel(unix_path3)
order_list.head(5)
2数据预处理
2.1查看是否有缺失值、重复值
vip_consume.info()#无缺失值
2.2转换数据类型，在查找重复值时，有些可能识别不到
2.2.1对vip_consume
#转换日期
vip_consume['订单日期'] = vip_consume['订单日期'].astype("str")
vip_consume['订单日期'] = pd.to_datetime(vip_consume['订单日期'])#名称转化为字符串
vip_consume['卡号'] = vip_consume['卡号'].astype("str")
vip_consume['订单号'] = vip_consume['订单号'].astype("str")
vip_consume['订单类型'] = vip_consume['订单类型'].astype("str")
vip_consume['店铺代码'] = vip_consume['店铺代码'].astype("str")
vip_consume['款号'] = vip_consume['款号'].astype("str")vip_consume.info()
#验证每个元素是否转化为str
for card_id in vip_consume['卡号']:print(type(card_id))
#vip_consume.groupby('卡号').count()
dup = vip_consume.copy().duplicated(keep= 'first')
print(dup[dup == True])
print(len(dup[dup == True]))
vip_consume.copy()[dup]
vip_consume[vip_consume['卡号'] == "BL6093096660436300333" ]
vip_consume = vip_consume.drop_duplicates(keep='first')
print(len(vip_consume))
2.2.2对vip_info
vip_info.info()
#创建缺失率函数
def missing (df):"""计算每一列的缺失值及占比"""missing_number = df.isnull().sum().sort_values(ascending=False) # 每一列的缺失值求和后降序排序 missing_percent = (df.isnull().sum()/df.isnull().count()).sort_values(ascending=False) # 每一列缺失值占比missing_values = pd.concat([missing_number, missing_percent], axis=1, keys=['Missing_Number', 'Missing_Percent']) # 合并为一个DataFramereturn missing_values
missing(vip_info)
vip_info_origin = vip_info.copy()
#drop掉缺失率达到0.7以上的列
vip_info = vip_info_origin
vip_info = vip_info.drop(columns=['门店店员编码','城市', '省份'])
vip_info = vip_info.dropna(axis= 0, subset=['生日', '年齡'])
print(vip_info.isnull().sum())
print(len(vip_info))
vip_info
#去掉重复值
vip_info = vip_info.drop_duplicates(keep='first')
print(len(vip_info))
2.2.3对shop_info
shop_info.info()
#计算缺失率
missing(shop_info)
#备份
shop_info_origin = shop_info.copy()
#删除缺失值
shop_info = shop_info.dropna(axis = 0)
print(shop_info.isnull().sum())
#删除重复值
print(len(shop_info))
shop_info.drop_duplicates(keep= 'first')
print(len(shop_info))
2.2.4对orderlist
order_list.info()#无缺失值
order_list
#计算缺失率
missing(order_list)
#删除零值，零值可能是没有会员
order_list.dropna(axis = 0, inplace=True)
order_list['年'] = order_list['年月'].astype(str).str[:4]
order_list['月'] = order_list['年月'].astype(str).str[4:].astype(int).astype(str)
order_list
3表连接
a = vip_info.sample(3).to_html()
b = vip_consume.sample(3).to_html()
c = shop_info.sample(3).to_html()
d = order_list.sample(3).to_html()# 写规范点的格式，当然可以简单实现 html_str = f"<div>{a}</div><div>{b}</div><div>{c}</div><div>{d}</div>"
html_str = f"""
<html>
<head><style>.cen{{display: flex;flex-direction: column;align-items: center;}}.t{{font-size: 24px;font-weight: bold;}}</style>
</head>
<body><div class="cen"><span class="t">【会员消费表】</span><div>{a}</div><span class="t">【会员信息表】</span><div>{b}</div><span class="t">【门店信息表】</span><div>{c}</div><span class="t">【全国订单表】</span><div>{d}</div></div>
</body>
</html>
"""
display_html(html_str, raw=True)
3几种可能的表连接情况
3.1会员信息表+会员消费表
info_consume = pd.merge(vip_info,vip_consume,left_on = ['会员卡号'],right_on = ['卡号'], how = 'inner')
print(vip_info.shape,vip_consume.shape,info_consume.shape)
info_consume.head()
3.1.1订单数量根据时间的变化
有效订单数量、下单数量、退单数量、退单数量、退货率随时间的变化
pd.to_datetime(info_consume['订单日期'])
。。。。
#后续源码请点击下面链接：
https://mbd.pub/o/bread/Z52Umplt
#后续源码请点击下面链接：
https://mbd.pub/o/bread/Z52Umplt