用写sql的思路写dataframe：把sql语法转化成dataframe语法

测试数据：

import pandas as pd
import random

title = ['class', 'name', 'project', 'score']
data_list = [
    ['一班', '张三', '语文', 80],
    ['一班', '张三', '数学', 90],
    ['二班', '李四', '语文', 80],
    ['二班', '李四', '数学', 90]]
a_table = pd.DataFrame(data=data_list, columns=title)
a_table


_class = ['a','b','c','d']
_type = ['东','西','南' ,'北', '中', '东南', '西北', '西南', '东北',None]
demo_df=pd.DataFrame([[x,
                       random.randrange(1,1000),
                       random.choice(_class),
                       random.choice(_type),
                       [random.randrange(1,1000),
                        random.randrange(1,1000),
                        random.randrange(1,1000),
                        random.randrange(1,1000)]
                      ] for x in range(100)
                     ]
                     ,columns=['number','_score','_class','_type','list_score'])

class	name	project	score
一班	张三	语文	80
一班	张三	数学	90
二班	李四	语文	85
二班	李四	数学	70

1. 查询筛选

1.1 普通等值筛选、数值筛选

多个筛选条件的时候，需要先把每个条件先用小括号()括起来，再用&、|来代表且、或的关系。
在一个有很多字段列的df中，取其中某个或者某几个作为新的df时，需要在df后的双重方括号中填写要选择的字段名，像这样 df[['feild1','feild2']]。

select name,score
from a_table
where class='一班' and project='语文' and score>10

a_table = pd.DataFrame('a_table')
# 注意1：如果是只有一个条件直接a_table[a_table.class=='一班']就ok了
# 但是如果有多个条件，需要用到&或者|，每个条件需要加括号后再&或|
# 注意2：如果查询结果选取的字段只有一个，可以直接a_table['name']
# 但是如果有多个字段需要拿出来，需要传入的是一个list，例如：
# a_table[['name','score']]
select_r = a_table[
	(a_table['class']=='一班')&
	(a_table['project']=='语文') &
	(a_table.score>10)
	][['name','score']]
# or 
select_r = a_table[(a_table.class=='一班')&(a_table.project=='语文')][['name','score']]

这块相关内容还是挺多听细碎的，先挖坑，慢慢填。2020年01月03日17:29:14

1.2 空值筛选

# select * from a_table where class is null;
a_table[a_table.class.isna()]
a_table[a_table.class.isnull()]

# select * from a_table where class is not null;
a_table[a_table.class.notna()]
a_table[a_table.class.notnull()]

1.3 SQL的instr()函数，在pd中需要用str.contains()

# select * from a_table where instr(class,'班')>0;
a_table[a_table.class.str.contains('班')]

1.4 SQL的in查询

使用 Dataframe的isin()方法判断
sql查询筛选表述：where class in ('一班'，'二班')
where class not in ('一班'，'二班')

class_list = ['一班'，'二班']
a_table[a_table.class.isin(class_list)]
a_table[~a_table.class.isin(class_list)]

1.5 新增字段

# select *,udf_a(score) as double_score from a_table;
# 1. 简单的字段变化
a_table['double_score']=a_table['score']*2

# 2. 假设有复杂逻辑的udf如下
def udf_a(score):
	if score:
		return score*2
	else:
		return None
a_table['double_score']=a_table['score'].apply(udf_a)

# 3. 使用lambda函数
a_table['double_score']=a_table['score'].apply(lambda x:x*2)

# 4. apply使用的函数需要参数
def udf_a(score,times=2):
	if score:
		return score*times
	else:
		return None
a_table['triple_score']=a_table['score'].apply(udf_a,args=(3,))

a_table

1.6 对数据排序，加排名

排序使用sort_index，sort_values
计算排名使用rank

a_table.sort_index()
a_table.sort_values(by=['socre'], ascending=False)

demo_df['rn'] = demo_df['_score'].rank(ascending=0,method='first')

都有参数ascending=False，来控制正序(True)还是倒序(False)，。
rank的 参数 method具体使用说明如下：

method : {'average', 'min', 'max', 'first', 'dense'}, default 'average'
    How to rank the group of records that have the same value (i.e. ties):

    * average: average rank of the group
    * min: lowest rank in the group
    * max: highest rank in the group
    * first: ranks assigned in order they appear in the array
    * dense: like 'min', but rank always increases by 1 between groups.

可以参考 pandas排序以及获取序号 - 小君~ - 博客园，解释很清楚。

1.7 字段为list，包含子项

TODO

2. group by 聚合

2.1 基础的agg聚合

dataframe的groupby基本格式：
df.groupby(by=['class','name']).agg({"socre":[sum,max,min],"name":len})
这里groupby的参数by，输入的就是要分组的字段；
agg的参数是一个字典，字典的key是要统计的字段，value是对这个字段用什么函数统计，
value可以是list，包含多个方法，也可以是一个单独的方法。

select class,name,sum(score),count(score)
from a_table
group by class,name

# 注意：如果by的列的内容不能是list类型的，否则会报错
# pandas dataframe groupby 多个字段
a_table.groupby(by=['class','name']).agg({'score':[sum,len], 'score':len})
# pandas groupby的结果index是by的内容，by的这俩字段不是一个dataframe的列，
# 这时候要想正常查询筛选，需要先把dataframe打平一下，之前手动处理，
# 现在发现用reset_index()更方便
a_table.groupby(by=['class','name']).agg({'score':pd.DataFrame.sum,'score':pd.DataFrame.__len__}).reset_index()

SQL 中的 count(distinct id)可以使用 lambda x:len(set(x))。
如果x内容无法序列化，比如x的子项是dict，可以先转成str再len(set())

2.2 进阶apply()聚合

Dataframe的apply()函数有非常大的自由度，
前面新增字段中对复杂的字段处理，需要用udf，就是和apply()函数结合使用的，
groupby()的时候也同样可以使用，这里输入的参数就是一个类似hive的udaf了。
想用apply输出多个字段，可以用pd.Series()转换下输出的格式。

demo_df.groupby(by=['_class']).apply(
	lambda x: pd.Series({'score_sum':sum(x['_score']),'list_score_sum':sum([sum(xx) for xx in x['list_score']])})
)

2.3 进阶apply()聚合，apply()高级妙用

这里还可以使用apply()实现 窗口函数 的功能。
SQL 表述：row_number() over(partition by xx,order by yy) as rn

def add_rank(x):
    x['rn'] = x['_score'].rank(ascending=False,method='first')
    return x
demo_df.groupby(by=['_class']).apply(lambda x: add_rank(x)).reset_index(drop=True)

3. join关联

select a.id, a.name, a.xxx, b.yyy
from a
[left, full [outer]] join b 
on a.id = b.id and a.name=b.name

# 1 改方法的结果最容易理解,
# 用on关联时,需要关联的俩字段的name是一样的
# 如果不一样可以用类似`left_on='key',right_on='name'`,
# 这时候结果会把left_on.key和right_on.name都列出来
a_15.merge(a_15,on='key',how='outer',suffixes=('_15', '_16'),)
# 2 结果同1
pd.marge(a_15,a_16, how='outer', on = ['key', 'name'])

# 3 这个接结果有点丑,就是把俩table拼接到一块儿
pd.concat([a_15,a_16] ,axis =1,join='outer', keys='key')
# 4 join的方法本地测试没有成功,但是有join方法,需要用a_15的index join a_16的index或者on='key'
# 这个就稍微有点麻烦了,不如merger好用,直接在on上关联对应的字段
a_15.join(a_15,on='key',how='outer',suffixes=('_15', '_16'),)

4. 展开一个字段到多行

使用explode()就可以直接吧需要展开的字段，转成多行

demo_df.head(5).explode('list_score')

4.1 dataframe groupby之后展平 .reset_index()

group_table = a_table.groupby(['class', 'name']).agg({'score': sum})

group_table.reset_index()

这个结果数据是这种格式:

展开之后结果数据是这种格式:

如果把班级都替换成一班，对比效果，理解起来更清晰：

9. 设置常用的¹

pandas在jupyter notebook中字段内容太长，只显示前n个字符，后面用...代替的问题。顺便解决了200字段只显示10个字段问题。

#显示所有列
pd.set_option('display.max_columns', 40)
#显示所有行
pd.set_option('display.max_rows', 100)
#设置value的显示长度为100，默认为50
pd.set_option('max_colwidth',100)

代码

import pandas as pd

title = ['class', 'name', 'project', 'score']
data_list = [
    ['一班', '张三', '语文', 80],
    ['一班', '张三', '数学', 90],
    ['二班', '李四', '语文', 80],
    ['二班', '李四', '数学', 90]]

a_table = pd.DataFrame(data=data_list, columns=title)
print(a_table)
print('-' * 100)
print(a_table[a_table.project == '语文']['name'])
print('-' * 100)
print(a_table[['name', 'score']])

print('-' * 100)
print(a_table[(a_table['class'] == '一班') | (a_table.project == '语文')])

参考链接：

还可以参考下面的文章：

1. 使用Pandas实现SQL基本操作
2. 像写SQL一样学习pandas 中 DataFrame

---

1. pandas中关于DataFrame行，列显示不完全（省略）的解决办法 ↩︎