主流电商订单API接口||订单详情|订单列表||电商销售数据分析实例

电商销售数据分析实例

有两份CSV表格数据【主流电商订单类API接口】，表一为部分的商城订单数据（已脱敏处理）（shape=220*16），包含了订单号、客户id、客户性别、订单创建时间、商品id、商品父类、购买数量、价格信息、是否成交代码、支付时间、订单的省份和城市代码以及购买会员等级；表二为全国省份及下辖区、城市代码及其对应的中文名称数据（shape=458*5）。数据预览如下：

订单数据

order_id	user_id	gender	订单时间	商品id	第一品类	购买数量	优惠前售价	优惠后售价	实际售价	是否成交	支付时间	优惠金额	city_id	province_id	会员等级
39858	72ef	0	19:00:02	10113	甲类	1	36	31	31	0	19:14:51	2	1657	19	3
38196	aadca7f	0	22:52:31	11321	丙类	1	289	238	249	0	22:53:16	40	1983	22	2
38056	sc99	1	16:38:13	20211	乙类	1	369	350	339	1	19:38:38	19	1954	22	3
38023	hucheng	1	19:34:25	10113	甲类	1	36	31	31	0	20:34:25	5	1874	21	1

省份数据

city	city_name	province	province_name	city_level
72	朝阳区	1	北京	一线
78	黄浦区	2	上海	一线
113	万州区	4	重庆	二线
114	涪陵区	4	重庆	二线
115	梁平区	4	重庆	二线

1.将数据导入数据库

1.1 将订单数据及省份数据两个csv文件导入数据库，表名分别命名为order_info和city

load data infile 'D:\\MySQL\\work_order_utf8.csv'
into table work.order_info character set utf8 
fields terminated by ',' 
lines terminated by '\r\n'
ignore 1 lines;
load data infile 'D:\\MySQL\\work_city_utf8.csv'
into table work.city character set utf8 
fields terminated by ',' 
lines terminated by '\r\n'
ignore 1 lines;

1.2【支付时间】、【订单时间】列数据由字符串改为时间格式

ALTER TABLE order_info MODIFY COLUMN 支付时间  time(0);
ALTER TABLE order_info MODIFY COLUMN 订单时间  time(0);

1.3 查看缺失值和重复值

SELECT COUNT(*),COUNT(是否成交),COUNT(支付时间),COUNT(city_id)
FROM order_info;
select * from order_info 
group by order_id,user_id,gender,订单时间,商品id,第一品类,购买数量,优惠前售价,优惠后售价,实际售价,是否成交,支付时间,优惠金额,city_id,province_id,会员等级 
having count(*)>1;

结果如下

可知支付时间列存在空值，且订单数据不存在重复值。

1.4 添加每个订单的总金额，及提取【支付时间】列数据的小时数据到新建的【total】、【hours】两列

ALTER TABLE order_info ADD COLUMN total int(10);
UPDATE order_info SET  total = 实际售价*购买数量;
ALTER TABLE order_info ADD COLUMN hours INT(10);
UPDATE order_info SET hours=DATE_FORMAT(支付时间,'%H')

1.5将order_info和city联立并保存为表order_city

CREATE TABLE order_city 
AS
SELECT * FROM (
SELECT i.order_id,user_id,gender,订单时间,商品id,第一品类,购买数量,优惠前售价,优惠后售价,实际售价,是否成交,支付时间,hours,优惠金额,total,会员等级,c.city_name,province_name,city_level 
FROM order_info i
LEFT JOIN city c ON i.city_id=c.city AND i.province_id=c.province)demo;

清洗实际售价=0，支付时间为NULL和是否成交为0的数据，保存清洗后的表为order_data

CREATE TABLE order_data
AS SELECT * FROM order_city;
DELETE FROM order_data WHERE 实际售价=0 ;
DELETE FROM order_data WHERE 是否成交=0 or 支付时间 IS NULL;
SELECT * FROM order_data;

2.Python连接数据库

2.1 导入依赖包

import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import pymysql
%matplotlib inline