5.pandas进阶(SQL对照，不定时更新)

本章开始，进入实战

0 实验数据

环境：mysql+python 3.6
表名：EMP

CREATE TABLE if not exists EMP(empno int,ename VARCHAR(10),job VARCHAR(9),mgr int,hiredate varchar(10),sal double,comm double,deptno int);

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902,'1980-12-17', 800, NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698,'1981-02-10', 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698,'1981-02-22', 1250, 500, 30);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839,'1981-04-02', 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,'1981-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,'1981-05-01', 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,'1981-07-09', 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,'1982-12-09', 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,'1981-11-17', 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,'1983-01-12', 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,'1981-12-03', 950, NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,'1981-12-03', 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,'1982-11-23', 1300, NULL, 10);

1 SQL和pandas对照

#连接mysql数据库
from sqlalchemy import create_engine
ce="mysql+mysqldb://password@ip:port/dbname?charset=utf8"
from_conn = create_engine(ce, echo=True, encoding='utf-8')
df = pd.read_sql_query("select * from emp",con=from_conn)

#--emp表的sal*10
#select a.sal*10 from emp a;
df["sal"] = df["sal"] * df["sal"] / df["sal"] * 10

#--emp表各部门的sal的平均值，总和，max和min（遇到缺失数值时，需特殊处理）
#select a.deptno,avg(a.sal) as avg_sal,sum(a.sal) as sum_sal,max(a.sal) as max_sal,min(a.sal) as min_sal from emp a group by a.deptno;
grouped = df.groupby(["deptno"])
grouped["sal"].agg([np.mean, np.sum, np.max, np.min]).reset_index()

#--emp表中sal<1000的信息
#select * from emp where sal<1000;
df = df[df["sal"]<1000]

#--ename中含有A的信息
#select ename  from emp where ename like'%A%';
df = df.ix[df["ename"].str.contains("A"),"ename"]

#--emp中ename不含有A的信息
#select * from emp where ename not like'%A%';
df = df[~df["ename"].str.contains("A")]

#--计算各部门中薪资小于5000的员工的工资总和
#select deptno,sum(sal) from emp where sal<5000 group by deptno;
df = df[df["sal"]<5000].groupby(["deptno"]).agg({"sal":np.sum}).reset_index()

#--计算各部门中工资不高于其部门平均工资的工资总和
#select a.deptno,sum(a.sal) from emp as a join (select c.deptno, avg(c.sal) as avg_sal from emp as c group by c.deptno) as b where a.deptno=b.deptno and a.sal<b.avg_sal group by a.deptno;
dfg = df.groupby(["deptno"]).agg({"sal":np.mean}).reset_index()
dfg = dfg.rename(columns={"sal":"avg_sal"})
df = pd.merge(left=df,right=dfg,on="deptno", how="inner")
df = df[df["sal"]<df["avg_sal"]].groupby(["deptno"]).agg({"sal":np.sum})

#--计算各部门中工资小于4000的员工的平均工资
#select a.deptno, avg(a.sal) from (select b.deptno, b.sal from emp as b where b.sal < 4000) as a group by a.deptno;
df = df[df["sal"]<4000].groupby(["deptno"]).agg({"sal":np.mean}).reset_index()

#--查询薪水低于10000的员工姓名和sal
#select ename,sal from emp where  sal<10000;
df = df.ix[df["sal"]<10000,["ename","sal"]]

#--查询工资大于1000，并且是20号部门的员工的全部信息
#select * from emp where sal>1000 and deptno=20;
df = df[df["sal"]>1000 & df["deptno"]==20]

#--根据用户名"SMITH"查找他所在的部门
#select deptno from emp where ename='SMITH';
df = df.ix[df["ename"].isin(["SMITH"]),"deptno"]

#--查询每个部门的最高工资的人员名称
#select e.ename,e.deptno,e.sal from (select deptno as did ,max(sal) as m from emp group by deptno)  s,emp e where e.sal=s.m and s.did=e.deptno;
df_max = df.groupby(["deptno"]).agg({"sal":np.max}).reset_index()
df_max = df_max.rename(columns={"sal":"sal_max"})
df = pd.merge(left=df, right=df_max, on="deptno", how="inner")
df = df.ix[df["sal"]==df["sal_max"],["ename","deptno","sal"]]

#--查询“Jones”之后第一个进入公司的人
#select * from emp where hiredate=(select min(hiredate) from emp where hiredate>(select hiredate from emp where ename='JONES')) ;
df = df.sort_values(by=["ename","hiredate"])
df = df.groupby(["ename"]).head(1).reset_index()
hiredate = df.ix[df["ename"].isin(["JONES"]),"hiredate"].tolist()[0]
df = df[df["hiredate"]>hiredate ]
df = df.sort_values(by=["hiredate"])
df = df.head(1)

#--查找工资最高的部门名称和工资最低的部门名称及工资
#select d.dname,e.sal from emp e,dept d where e.deptno=d.deptno and sal=(select max(m) from (select deptno,max(sal) as m from emp e group by deptno) s) union select d.dname,e.sal from emp e,dept d where e.deptno=d.deptno and sal=(select min(m) from (select deptno,min(sal) as m from emp e group by deptno) s);
df = df.sort_values(by=["sal"],)
df = pd.concat([df.head(1),df.tail(1)])
df = df[["deptno","sal"]]

#--查找从事特定工作的人数（分组数人头）
#select case 
#               when job='ANALYST' then 'Y' 
#               when job='MANAGER' then 'Y' 
#               when job='SALESMAN' then 'N' 
#               when job='CLERK' then 'N' 
#               else 'N' end as is_target, count(distinct empno) as csr_cnt
#   from EMP 
#   group by 1
job_map = {'ANALYST':'Y',
           'MANAGER':'Y'}
df['is_target'] = df['job'].map(job_map)
new_df = df.groupby(['is_target']).agg({'empno':pd.Series.nunique}).reset_index()

#--公司有多少个部门（去重）
#select distinct job from EMP group by 1
new_df = df.drop_duplicates(subset=['job'], keep='first')

#--公司根据部门和员工工号排序（分组排序）
#select job, empno from EMP order by 1,2
df = df.sort_values(['job','empno'], ascending=[0,0]).reset_index()

#--公司部门员工名单拉平（行列转换，拉平）
#展现结果如下：
# CLERK ADAMS,JAMES,MILLER,SMITH
# ANALYST   FORD,SCOTT
df.sort_values(['job','ename'], ascending=[0,0]).reset_index()
new_df = df.groupby('job')['ename'].agg(lambda x: ', '.join(x.unique())).reset_index()

#--非去重计数
#select ename, count(empno) from EMP group by 1
df.groupby(['ename'])['empno'].size()

#--去重计数
#select ename, count(distinct empno) from EMP group by 1
df.groupby(['ename']).agg({'empno':pd.Series.nunique})

2 pandas效率

#--数据过大、内存吃紧时，需对原数据分块
#经测试，分块与不分块，在抽取时间上差异不大
sql = "select BIN_SaleRecordID,BIN_BrandInfoID,SaleType,TicketType,SaleRecordCode,BillCode,BillCodePre,SaleTime as SaleDate,MemberCode,BIN_OrganizationID,EmployeeCode,InvoiceFlag from Sale.BIN_SaleRecord where SaleDate >= '" + s_day + "' and SaleDate < '" + e_day + "'"
for BIN_SaleRecord in pd.read_sql_query(sql, con=sql_server_engine, chunksize=batch_no):
  (obs_n, _) = BIN_SaleRecord.shape
  if obs_n > 0:
    BIN_SaleRecord = BIN_SaleRecord[BIN_SaleRecord['InvoiceFlag'] != 'NO']
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 218,122评论 6赞 505
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 93,070评论 3赞 395
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 164,491评论 0赞 354
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,636评论 1赞 293
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,676评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,541评论 1赞 305
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,292评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,211评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,655评论 1赞 314
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,846评论 3赞 336
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,965评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,684评论 5赞 347
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,295评论 3赞 329
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,894评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,012评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,126评论 3赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,914评论 2赞 355