pandas&SQL乱炖 || pandas预处理用SQL如何实现 ||

复盘一下pandas数据预处理部分，最近写SQL有点上头，顺带思考了一下各种情况下的SQL应该怎么写🤔

---

数据集👇

基础DataFrame部分

import pandas as pd
import numpy as np
##DataFrame多维数组（以某一数据源为例）
#读取数据源，以日期列（第一列为索引）
data = pd.read_excel('D:\pycharm\project\data.xlsx',sheet_name='Sheet1',index_col=0)
data_ = pd.read_excel('D:\pycharm\project\data.xlsx',sheet_name='Sheet2',index_col=0)
#转成表格
data = pd.DataFrame(data)
#查看部分数据
data.head()
#查看列名
data.columns

#将1月7号的信息调取出来
data.loc['2020-01-07']

#sheet1和sheet2两表匹配合并
data['无效订单'] = data_["无效订单"]

#添加新列new，如果门店下单量大于50则返回1，否则返回0
data['new'] = np.where(data['门店下单量']>50,1,0)

#删除neW列 ——— 删除原数据
del data['new']

#修改index的列名为索引
data.index.name = '索引'

#删除1月3号的数据（行）——— 不删除原数据
data.drop('2020-01-03')

#删除门店曝光列（列）
data.drop('门店曝光量',axis=1)

部分需求的SQL写法

#将1月7号的信息调取出来
select * from data 
where date_format(日期, '%Y%m%d' ) = 20200107;

#添加新列new，如果门店下单量大于30则返回1，否则返回0
select *,
case when 门店下单量>50 then 1 else 0 end as new 
from data;

#删除neW列 ——— 删除原数据
alter table data drop column new;

---

数据集👇

数据基础清洗

#导入数据，形成DF
data = pd.read_excel('D:\pycharm\project\data.xlsx',sheet_name='Sheet3')
data = pd.DataFrame(data)

#缺失值处理
data = data.dropna()    #去除含有缺失值的行（无论有几个缺失值）
data = data.dropna(how='all')    #去除含有缺失值的行（全部缺失时）
data = data.dropna(axis=1)    #去除含有缺失值的列（无论有几个缺失值）
data = data.fillna(0)    #将缺失值全部填充为0
data = data.fillna(method='ffill',limit=2)    #将缺失值向上填充，且只向上填充两次

#删除重复值
data = data.drop_duplicates(['日期'])    #删除只有日期重复的行
data = data.drop_duplicates(['日期','GMV'])    #删除日期和GMV重复的行

#分箱门店下单量，并输出每个区间的计数
data = pd.cut(data['门店下单量'],[30,40,50,60])    #左开右闭区间
data = pd.cut(data['门店下单量'],[30,40,50,60],right=False)    #左闭又开区间
data = pd.cut(data['门店下单量'],[30,40,50,60],labels=['低','中','高'])    #给各个区间贴上标签
data = pd.qcut(data['门店下单量'],4)    #等额分成4组
pd.value_counts(data)    #查看计数

部分需求SQL写法

##缺失值处理
#去除含有缺失值的行（无论有几个缺失值）---有点蠢哈哈~
delete from data
	where GMV is null or 商家实收 is null 
	or 门店曝光量 is null or 门店访问量 is null 
	or 门店下单量 is null;
#去除含有缺失值的行（全部缺失时）
delete from data
	where GMV is null and 商家实收 is null 
	and 门店曝光量 is null and 门店访问量 is null 
	and 门店下单量 is null;
#将缺失值全部填充为0  ----以GMV列为例，其他列同理
update data 
set GMV=0 where GMV is null;


##删除重复值 ----即留下出现频率为1次的数据
select * from data 
	group by 日期 
	having count(*)=1;


##分箱门店下单量
#左开右闭区间，分为[30,40,50,60]
select '(30,40]' as cut,count(*) num from data where 门店下单量>30 and 门店下单量<=40
union all 
select '(40,50]' as cut,count(*) num from data where 门店下单量>40 and 门店下单量<=50
union all 
select '(50,60]' as cut,count(*) num from data where 门店下单量>50 and 门店下单量<=60;
#给各个区间贴上标签 -----把上面的'(30,40]'改成'低'，'(40,50]'改成'中'，'(50,60]'改成'高'即可