我们都知道SQL数据库的语句很简洁，python中的pandas库也很好用，但是如何将两者给关联起来？？？

本文将通过pandas来实现类似于SQL中的【过滤】、【排序】、【关联】、【合并】、【更新】、【删除】等操作。

用到的数据集

from sklearn.datasets import load_boston
import pandas as pd

boston = load_boston()
df = pd.DataFrame(boston.data,columns = boston.feature_names)
df['target'] =pd.Series(boston.target) 

df

506 rows × 14 columns

简单的字段查询

‘’‘SQL语句’’’

• SELECT CRIM,ZN,CHAS,NOX,RM,RAD FROM boston LIMIT 2;

实现返回每行记录的CRIM,ZN,CHAS,NOX,RM,RAD字段，返回2行。

search = df[["CRIM","ZN","CHAS","NOX","RM","RAD"]].head(2)
search

简单的条件过滤 WHERE

‘’‘SQL语句’’’

• SELECT * FROM boston WHERE CHAS=1 LIMIT 2;

set(df['CHAS']) # 我们将CHAS特征作为分类标签

{0.0, 1.0}

search = df[df['CHAS']==1].head(2)
search

多条件与或查询WHERE AND|OR

如上满足SQL实现查询同时满足CHAS=1和CRIM>1.0两个条件的记录，返回两行

与关系&

‘’‘SQL语句’’’

• SELECT * FROM boston WHERE CHAS=1 AND CRIM>=1.0 LIMIT 2;

search = df[(df['CHAS']==1) & (df['CRIM'] >= 1.0)] .head(2) # 注意这里条件是用的圆括号！！！不是方括号
search

或关系|

‘’‘SQL语句’’’

• SELECT * FROM boston WHERE CHAS=1 OR CRIM>=1.0 LIMIT 2;

search = df[(df['CHAS']==1) | (df['CRIM'] >= 1.0)] .head(2) # 注意这里条件是用的圆括号！！！不是方括号
search

条件过滤 控制判断

空判断 查询null的记录

‘’‘SQL语句’’’

• SELECT * FROM boston WHERE CHAS IS NULL;

search = df[df['CHAS'].isna()]# 注意这里条件是用的圆括号！！！不是方括号
search

非空判断is not null

查询不为null的记录

‘’‘SQL语句’’’

• SELECT * FROM boston WHERE CHAS IS NOT NULL;

search = df[df['CHAS'].notna()]# 注意这里条件是用的圆括号！！！不是方括号
search

506 rows × 14 columns

排序 ORDER BY ASC|DESC

‘’‘SQL语句’’’

• SELECT * FROM boston WHERE INDUS>10 ORDER BY DESC CHAS;

满足INDUS>10的值按照CHAS进行降序排列

search = df[(df['INDUS']>10)].sort_values(by='CHAS',ascending=False)
search

236 rows × 14 columns

更新UPDATE

‘’‘SQL语句’’’

• UPDATE boston SET CHAS = 1 WHERE INDUS >10 AND ZN = 0

将满足INDUS >10 和ZN > 0的CHAS字段更新为1

df.loc[(df['INDUS']>10) & (df['ZN']==0),'CHAS']=1
df[df['INDUS']>=10]

236 rows × 14 columns

分组统计

根据CHAS进行分组，返回CHAS和每组的数量

‘’‘SQL语句’’’

• SELECT CHAS，COUNT(*) FROM boston GROUP BY CHAS;

search = df.groupby('CHAS').size()
search

CHAS
0.0    261
1.0    245
dtype: int64

分组统计 聚合输出

根据CHAS进行分组，返回CHAS，每个组的INDUS和NOX的平均值、最大值

‘’‘SQL语句’’’

• SELECT CHAS,avg(INDUS),max(INDUS),avg(NOX),max(NOX) FROM boston GROUP BY CHAS

import numpy as np
search = df.groupby('CHAS').agg({'INDUS':[np.mean,np.max],'NOX':[np.mean,np.max]})
search

删除

‘’‘SQL语句’’’

• DELETE FROM boston WHERE WHERE TAX=305 AND RAD = 4;

drop = df.drop(df[(df['TAX']=305) & (df['RAD']=4)].index)

  File "<ipython-input-90-fff36bdbf4c7>", line 1
    drop = df.drop(df[(df['TAX']=305) & (df['RAD']=4)].index)
                                ^
SyntaxError: invalid syntax