pandas mysql数据统计_Pandas数据分析 vs SQL统计实践

1. 目的：SQL语句的Pandas实现

数据分析师最基础的技能之一：写SQL统计数据； 然..不够使，又须多一个能力， 数据分析师最基础的技能之一：Pandas数据处理

2. 环境python2.7

python须安装ipython, pandas, numpy, mysqldb

如果不想花时间搭建环境, 我已经在云端搭建了数据学习环境，按照下面的文章最后的说明，申请远程访问权限：数据民工来取经儿：2019年，数据民工的大白话规划​zhuanlan.zhihu.com

2.1 练习环境ipython

import MySQLdb

import pandas as pd

conn = MySQLdb.connect(host='127.0.0.1', user='usertest', passwd='usertest',port=3306, db='employees', charset="utf8")

cur = conn.cursor()

df_employees = pd.read_sql('select * from employees', con=conn)备注:

python MySQLdb连接数据库，为防止查询结果中文乱码，connect参数注意增加charset="utf8"(因为一般表创建字符charset为utf8)

3. SQL实例

下面开始一步一步对照SQL的统计语句和Pandas实现方法：

3.1 单表查询

3.1.1 查询前几行sql

select * from emloyees limit 10pandas

# 方法一：

df_employees.iloc[0:10]

# 方法二：

df_employees.head(10)

3.1.2 查询某个字段sql

select emp_no, birth_date from emloyeespandas

df_employees[['emp_no', 'birth_date']]

3.1.3 查询某字段前几行sql

select emp_no, birth_date from emloyees limit 10pandas

df_employees[['emp_no', 'birth_date']].iloc[0:10]

df_employees[['emp_no', 'birth_date']].head(10)

3.1.4 单字段筛选特殊值sql

select * from emloyees where hire_date='1986-06-26'

select * from emloyees where hire_date <> '1986-06-26'pandas

df_employees.loc[df_employees['hire_date'].astype(str)=='1986-06-26']

df_employees.loc[df_employees['hire_date'].astype(str) != '1986-06-26']

df_employees.loc[df_employees['hire_date'].astype(str) <> '1986-06-26']

3.1.5 and 和 orsql

select * from employees where hire_date='1986-06-26' and gender='F'

select * from employees where hire_date='1986-06-26' or gender='F'pandas 方法一

df_employees.loc[(df_employees['hire_date'].astype(str)=='1986-06-26') & (df_employees['gender'].astype(str)=='M')]

df_employees.loc[(df_employees['hire_date'].astype(str)=='1986-06-26') | (df_employees['gender'].astype(str)=='M')]pandas 方法二

df1 = df_employees.loc[df_employees['hire_date'].astype(str)=='1986-06-26']

df2 = df1.loc[df1['gender'].astype(str)=='F']

print df2

3.1.6 is null 和 is not nullsql

select * from employees where first_name is nullpandas

# 当然数据可能没有空值，所以结果为空

df_employees[df_employees['first_name'].isnull()]

df_employees[df_employees['first_name'].notnull()].head(10)

3.1.6 in操作sql

select * from employees where first_name in ('Parto', 'Anneke', 'Saniya')pandas

df_employees.loc[df_employees['first_name'].isin(['Parto', 'Anneke', 'Saniya'])]

3.1.7 not in操作sql

select * from employees where first_name not in ('Parto', 'Anneke', 'Saniya')pandas

df_employees.loc[~df_employees['first_name'].isin(['Parto', 'Anneke', 'Saniya'])]

# 希望通过某列转化的list作为in的输出参数

df_employees.loc[~df_employees['first_name'].isin(df.colexample.tolist())]

3.1.8 聚合操作：count()sql

select first_name, count(*) as tt from employees group by first_name order by ttpandas

df_employees.groupby('first_name').size()

# 注意比较size()与count()的区别，count()把每个字段都汇总，所以用size()即可

df_employees.groupby('first_name').count()

3.1.9 order bysql

select emp_no from employees order by emp_no descpandas

df_employees.sort_values(by='emp_no', ascending=False)

3.1.10 聚合操作：count()和count(distinct)sql

select first_name, count(*) as tt from employees group by first_name order by tt

select gender,count(DISTINCT first_name) from employees group by genderpandas

df_employees.groupby('gender').count().sort_values('first_name', ascending=False)

df_employees.groupby('gender').first_name.nunique()

3.1.11 聚合操作：sum()sql

select gender, sum(emp_no) as tt from employees group by gender order by ttpandas

df_employees.[['emp_no', 'gender']].groupby('gender').sum()

# pandas对非int字段，不会进行sum操作，如下结果与上面代码运行结果一样：

df_employees.groupby('gender').sum()

3.1.12 多种聚合操作：count()与sum()sql

select gender, sum(emp_no) as tt1, count(*) as tt2 from employees group by gender order by tt1 desc

select gender,first_name, sum(emp_no) as tt1, count(*) as tt2

from employees group by gender,first_namepandas

df_employees.groupby('gender').agg({'emp_no':np.sum, 'first_name':np.size}).sort_values(by='emp_no', ascending=False)

df_employees.groupby(['gender','first_name']).agg({'emp_no':[np.sum, np.size]})

3.2 多表查询

3.2.1 连接：Left join，Rigth join，Full join新读入薪水表

df_salaries = pd.read_sql('select * from salaries', con=conn)sql

select * from employees t1 left join salaries t2 on t1.emp_no=t2.emp_no

select * from employees t1 right join salaries t2 on t1.emp_no=t2.emp_no

select * from employees t1 full join salaries t2 on t1.emp_no=t2.emp_nopandas

pd.merge(df_employees, df_salaries , on='emp_no', how='left')

pd.merge(df_employees, df_salaries , on='emp_no', how='right')

pd.merge(df_employees, df_salaries , on='emp_no', how='outer')

# 如果是多列作为连接条件:

pd.merge(df_employees, df_salaries , left_on=['emp_no', 'hire_date'], right_on=['emp_no', 'to_date'], how='inner')join 函数

# 如果两个dataframe待连接字段名字不同

# 如下: 通过df_employees的columnName 与 df_salaries的newColumn进行连接

df_employees.set_index('columnName').join(df_salaries.set_index('newColumn'))

df_employees.join(df_salaries.set_index("birth_date"), on='hire_date', lsuffix='_one', rsuffix='_another', how='left')

3.2.2 union allsql

(select * from employees t1 limit 10)

union all

(select * from employees t1 limit 20,10)pandas

pd.concat([df_employees.iloc[0:10], df_employees.iloc[10:21]])

3.2.2 union all 和 unionsql

(select * from employees t1 limit 10)

union all

(select * from employees t1 limit 20,10)pandas

pd.concat([df_employees.iloc[0:10], df_employees.iloc[10:21]])

pd.concat([df_employees.iloc[0:10], df_employees.iloc[10:21]])

3.2.3 row_number()sql

# mysql没有row_number()功能，oracle有, 如下模拟oracle写法(实际mysql无法运行哈，意会下精神，咳咳)

SELECT * FROM

( SELECT t.*, ROW_NUMBER() OVER(PARTITION BY gender ORDER BY emp_no DESC) AS rn FROM employees t)

WHERE rn < 5

ORDER BY gender ,rn;pandas

(df_employees.assign(rn=df_employees.sort_values(['emp_no'], ascending=False).groupby(['gender']).cumcount()+1).query('rn<5').sort_values(['gender','rn']))

3.2.4 updatesql

update employees

set emp_no=100

where emp_no=10001pandas

df_employees.loc[df_employees['emp_no']==10001, 'emp_no'] = 99

3.2.5 deletesql

delete employees

where emp_no=10003pandas

df1 = df_employees.loc[df_employees['emp_no'] != 10003]

待更新..

欢迎骚扰，个人微信: ITlooker , 微信公众号: workindata增加一些最近写的数据分析相关文章:

不赞不赏我都懂，可不加我微信(ITlooker), 就是你的不对了:)开通了微信公共账号(workindata), 欢迎关注数据民工的干货分享