pandas与sql 对比,持续更新...

由于工作关系 , 经常会用到sql和python的pandas模块,两者有很多相似之处,我将整理出系列的对比文章,并保持更新. 若有不尽之处,敬请指出.

文章目录
基础说明
select 操作
where操作
in操作
regexp正则操作
group by操作
order by 操作
limit 操作
case when操作
left join (rigth join, inner join,) 等操作
union all操作
distinct 操作
多列算术运算操作
多个DF左右合并[^footnote3]
删除特定条件的列: [^footnote4]
基础说明
本文代码环境基于windows 10 和 anaconda3创建的.python3.6虚拟环境

下表的名字,在sql中数据叫table, 在pandas中叫df, 在一般的df中id这个字符是不显示的,在df中,这列叫做index
id    colA    colB    colC    colD
0    A    X    100    90
1    A        50    60
2    B    Ya    30    60
3    C    Yb    50    80
4    A    Xa    20    50
生成上表的语句是:
df = pd.DataFrame({'colA' : list('AABCA'), 'colB' : ['X',np.nan,'Ya','Xb','Xa'],'colC' : [100,50,30,50,20], 'colD': [90,60,60,80,50]})

select 操作
等效说明

sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
select * from table    df    
select colA,colB from table    df.loc[:,['colA', 'colB']]或df[['colA', 'colB']]    关键字: loc
脚注1.

where操作
sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
select * from table where colA = 'B'    df[df['colA']=='B']    在DF中使用==表示两者比较关系
select * from table where colA = 'A' and colC = 50    df[(df['colA']=='A') & (df['colC'] == 50)]    and操作用&符号,且&前后的语句需用()括起来
in操作
sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
select * from table where colA in ('A','B')    df[df['colA'].isin(['A','B'])]    关键字:isin
regexp正则操作
因markdown使用不熟, 下表中的{竖线} 应替换为:|

sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
select * from table where colB regexp 'a{竖线}b'    df[df['colB'].str.contains('a{竖线}b')]    关键字:str.contains
group by操作
单独group by 没有意义 ,一般会和其它应用组合起来使用, 比如sql中的AVG(), COUNT(), SUM(), MAX()等函数,以后的工作中有更多的groupby操作,将会持续更新到这里.

sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
select colA, sum(colC) from table group by colA    df.groupby(['colA'])['colC'].sum().reset_index()    关键字:reset_index()的作用是将groupby后的index进行重置,以保持数据的二维表结构,
order by 操作
sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
select colA, sum(colC) from table group by colA order by sum(colC)    df.groupby(['colA'])['colC'].sum().reset_index().sort_values(by=['colC'],ascending=True)    关键字:sort_values()
limit 操作
sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
select colA, sum(colC) from table group by colA order by sum(colC) limit 2    df.groupby(['colA'])['colC'].sum().reset_index().sort_values(by=['colC'],ascending=True)[:2]    关键: DF的切片即可实现
case when操作
sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
select colA, (case when colC > 60 then '及格' else '叫家长' end) as 成绩 from table    df['成绩'] = df.colC.map(lambda x: '及格' if x > 60 else '叫家长' ); df    关键字: map()函数非常好用, 同理的还apply(), applymap()
select colA, 
(case when colC > 85 then '优秀' 
when colC >= 60 then '及格' 
else '叫家长' end) as 成绩 
from table 

等效于

def func(x):
    if x >= 85:
        return '优秀'
    elif x >= 60:
        return '及格'
    else:
        return '叫家长'
df['成绩']  = df.colC.map(func)

left join (rigth join, inner join,) 等操作
left join 操作涉及到多表操作,个人常使用的场景: 两个表通过key键左右连接,达到扩展字段的目的

sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
select A.*,B.* from table A left join table2 B on A.colA = B.colA    df3 = pd.merge(df,df2,how='left', on=['colA'])    关键字: pd.merge()参数比较多,关键在于熟练运用,一般来说,以上几个参数够用了
union all操作
个人将此操作称为两个表的上下连接,以增加记录行数,

sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
A union all B    pd.concat([A, B])    关键字: pd.concat() 可以连接多个表
A union all B union all C    pd.concat([A, B, C])    很简单吧
distinct 操作
新增日期:2

sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
select distinct colA from tabel where colB is null    df.loc[df['colB'].isnull(), 'colA'].unique()或者df['colA'][df['colB'].isnull()].unique()    关键字: unique
多列算术运算操作
sql操作(table)为数据表名    pandas操作(df)为DataFrame框架数据    说明
select colA, colB, colC, colD, (colC + colD) as summary from tabel    df['summary'] = df.apply(lambda x: x.colC + x.colD, axis=1);df 或者 df['summary'] = df.colC + df.colD;df    关键字: axis=1 是必不可少的, 优先推荐第二种写法, 简单易懂
多个DF左右合并3
最近在工作中碰个使用场景:就是多个df左右拼接,每个df的结构内容都是一致的, 但结果值不一样.
若使用sql, 语句如下, 若有N个table, 那么将会有N个left join, sql语句将会更长

SELECT 
X.datetime as 日期,
X.type as 户型,
X.volume_total as X表结果1,
X.volume_totalarea as X表结果2,
Y.volume_total as Y表结果1,
Y.volume_totalarea as Y表结果2
from
(SELECT
datetime,
type,
volume_total,
volume_totalarea
from table1
) X
left join
(
SELECT
datetime,
type,
volume_total,
volume_totalarea
from table2
) Y on X.datetime = Y.datetime and X.type = Y.type
left join
(
SELECT
datetime,
type,
volume_total,
volume_totalarea
from table3
) Z on X.datetime = Z.datetime and X.type = Z.type

如果使用pandas.DataFrame来处理
将X,Y,Z三个字表的结果查询出来, 结果存放在df_list中, 这些df有共有的['datetime', 'type']列,将作为合并的键列

df_list = [df1, df2, df3]
df_finish = df_list[0].set_index(['datetime', 'type']).join([d.set_index(['datetime', 'type']) for d in df_list[1:]]).reset_index()

或者

df_list = [df1, df2, df3]
df_finish = df_list[0].join([d.set_index(['datetime', 'type']) for d in df_list[1:]], on=['datetime', 'type']).reset_index()

或者

df_finish = pd.concat([d.set_index(['datetime']) for d in df_list], axis=1, sort=False).reset_index()

说明: 关键字: .join(),
解决思路是:将键列转为index后再时行合并,
通过文档可以看出.这是index-on-index的合并方法,
若需要使用columns-on-columns的合并方法,请参照pd.merge()操作, 个人认为pd.merge()方法只能合并两个DF, 若有N个DF刚需要合并,则需要进行n-1次操作.

删除特定条件的列: 4
最近遇到个需求:一个df共有10个列, 有些列的数据值全部为0, 目标是要找到这些列,并把它删除,所以关键是:找到这些列
– 解决思路1: 既然全为0,那就使用np.sum()求得每个列的值,值为0的则是我要找的.结果显示,这样会报错, 因为其它列的值不一定是能用np.sum()
# 解决思路2(下面的代码):
# 统计出每列里出现次数最多的值及出现在次数, 值==0 且次数==整个df的长度时,就是我要找的那个列.

from collections import Counter

def counter(arr):
    return Counter(arr).most_common(1) # 找出最多的那1个值,如果为2,则就是最多的那2个值

cols = [x for x in main_df.columns if counter(
            main_df[x])[0][0] == 0 and counter(main_df[x])[0][1] == len(main_df)]
main_df = main_df.drop(cols, axis=1) 

2018-08-09 新增df[['colA', 'colB']]方法 ↩︎

2018-08-10 新增 distinct 操作方法 ↩︎

2018-08-13新增 DF.join()方法 ↩︎

2018-10-17新增 ↩︎
--------------------- 
作者：诸葛老刘 
来源：优快云 
原文：https://blog.youkuaiyun.com/weixin_39791387/article/details/81391621 
版权声明：本文为博主原创文章，转载请附上博文链接！