Pandas进阶陆 连接操作

Pandas进阶陆 连接操作

pandas进阶系列根据datawhale远昊大佬的joyful pandas教程写一些自己的心得和补充，本文部分引用了原教程，并参考了《利用Python进行数据分析》、pandas官网

另注：本文是对joyful pandas教程的延伸，完整理解需先阅读[joyful pandas教程第六章]https://datawhalechina.github.io/joyful-pandas/build/html/%E7%9B%AE%E5%BD%95/ch6.html)

目前进度：内容学习完了，题目全部做完了，今日学习时间用完了我不学习啦！（明天补一下习题过程，然后调整一下文章结构加点自己的理解和感悟）

一、关系型连接

1. 连接的基本概念

把两张相关的表按照某一个或某一组键连接起来是一种常见操作，例如学生期末考试各个科目的成绩表按照$姓名$和$班级$连接成总的成绩表，又例如对企业员工的各类信息表按照$员工ID号$进行连接汇总。由此可以看出，在关系型连接中，$键$是十分重要的，往往用on参数表示。

另一个重要的要素是连接的形式。在pandas中的关系型连接函数merge和join中提供了how参数来代表连接形式，分为左连接left、右连接right、内连接inner、外连接outer，它们的区别可以用如下示意图表示：

从图中可以看到，所谓左连接即以左边的键为准，如果右边表中的键于左边存在，那么就添加到左边，否则则处理为缺失值，右连接类似处理。内连接只负责合并两边同时出现的键，而外连接则会在内连接的基础上包含只在左边出现以及只在右边出现的值，因此外连接又叫全连接。

上面这个简单的例子中，同一个表中的键没有出现重复的情况，那么如果出现重复的键应该如何处理？只需把握一个原则，即只要两边同时出现的值，就以笛卡尔积的方式加入，如果单边出现则根据连接形式进行处理。其中，关于笛卡尔积可用如下例子说明：设左表中键张三出现两次，右表中的张三也出现两次，那么逐个进行匹配，最后产生的表必然包含2*2个姓名为张三的行。下面是一个对应例子的示意图：

显然在不同的场合应该使用不同的连接形式。其中左连接和右连接是等价的，由于它们的结果中的键是被一侧的表确定的，因此常常用于有方向性地添加到目标表。内外连接两侧的表，经常是地位类似的，想取出键的交集或者并集，具体的操作还需要业务的需求来判断。

2. 值连接

在上面示意图中的例子中，两张表根据某一列的值来连接，事实上还可以通过几列值的组合进行连接，这种基于值的连接在pandas中可以由merge函数实现，例如第一张图的左连接：

import numpy as np
import pandas as pd
df1 = pd.DataFrame({'Name':['San Zhang','Si Li'], 'Age':[20,30]})
df2 = pd.DataFrame({'Name':['Si Li','Wu Wang'], 'Gender':['F','M']})
df1.merge(df2, on='Name', how='left')

	Name	Age	Gender
0	San Zhang	20	NaN
1	Si Li	30	F

如果两个表中想要连接的列不具备相同的列名，可以通过left_on和right_on指定：

df1 = pd.DataFrame({'df1_name':['San Zhang','Si Li'], 'Age':[20,30]})
df2 = pd.DataFrame({'df2_name':['Si Li','Wu Wang'], 'Gender':['F','M']})
df1.merge(df2, left_on='df1_name', right_on='df2_name', how='left')

	df1_name	Age	df2_name	Gender
0	San Zhang	20	NaN	NaN
1	Si Li	30	Si Li	F

如果两个表中的列出现了重复的列名，那么可以通过suffixes参数指定。例如合并考试成绩的时候，第一个表记录了语文成绩，第二个是数学成绩：

df1 = pd.DataFrame({'Name':['San Zhang'],'Grade':[70]})
df2 = pd.DataFrame({'Name':['San Zhang'],'Grade':[80]})
df1.merge(df2, on='Name', how='left', suffixes=['_Chinese','_Math'])

	Name	Grade_Chinese	Grade_Math
0	San Zhang	70	80

在某些时候出现重复元素是麻烦的，例如两位同学来自不同的班级，但是姓名相同，这种时候就要指定on参数为多个列使得正确连接：

df1 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],
                    'Age':[20, 21],
                    'Class':['one', 'two']})
df2 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],
                    'Gender':['F', 'M'],
                    'Class':['two', 'one']})
df1

	Name	Age	Class
0	San Zhang	20	one
1	San Zhang	21	two

df2

	Name	Gender	Class
0	San Zhang	F	two
1	San Zhang	M	one

df1.merge(df2, on='Name', how='left') # 错误的结果

	Name	Age	Class_x	Gender	Class_y
0	San Zhang	20	one	F	two
1	San Zhang	20	one	M	one
2	San Zhang	21	two	F	two
3	San Zhang	21	two	M	one

df1.merge(df2, on=['Name', 'Class'], how='left') # 正确的结果

	Name	Age	Class	Gender
0	San Zhang	20	one	M
1	San Zhang	21	two	F

从上面的例子来看，在进行基于唯一性的连接下，如果键不是唯一的，那么结果就会产生问题。举例中的行数很少，但如果实际数据中有几十万到上百万行的进行合并时，如果想要保证唯一性，除了用duplicated检查是否重复外，merge中也提供了validate参数来检查连接的唯一性模式。这里共有三种模式，即一对一连接1:1，一对多连接1:m，多对一连接m:1连接，第一个是指左右表的键都是唯一的，后面两个分别指左表键唯一和右表键唯一。

【练一练】

上面以多列为键的例子中，错误写法显然是一种多对多连接，而正确写法是一对一连接，请修改原表，使得以多列为键的正确写法能够通过validate='1:m'的检验，但不能通过validate='m:1'的检验。

【我的思路】

修改右边的表，使其主键（在这里是Name和Class两列联合作为主键）不唯一，即可看出validate的效果，下面展示示例

df1 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],
                    'Age':[20, 21],
                    'Class':['one', 'two']})
df2 = pd.DataFrame({'Name':['San Zhang', 'San Zhang'],
                    'Gender':['F', 'M'],
                    'Class':['one', 'one']})

df1.merge(df2, on=['Name', 'Class'], how='left', validate='1:m') # 正确的结果

	Name	Age	Class	Gender
0	San Zhang	20	one	F
1	San Zhang	20	one	M
2	San Zhang	21	two	NaN

try:
    df1.merge(df2, on=['Name', 'Class'], how='left', validate='m:1')
except Exception as e:
    print(e)

Merge keys are not unique in right dataset; not a many-to-one merge

【END】

3. 索引连接

所谓索引连接，就是把索引当作键，因此这和值连接本质上没有区别，pandas中利用join函数来处理索引连接，它的参数选择要少于merge，除了必须的on和how之外，可以对重复的列指定左右后缀lsuffix和rsuffix。其中，on参数指索引名，单层索引时省略参数表示按照当前索引连接。

df1 = pd.DataFrame({'Age':[20,30]}, index=pd.Series(['San Zhang','Si Li'],name='Name'))
df2 = pd.DataFrame({'Gender':['F','M']}, index=pd.Series(['Si Li','Wu Wang'],name='Name'))
df1.join(df2, how='left')

	Age	Gender
Name
San Zhang	20	NaN
Si Li	30	F

仿照第2小节的例子，写出语文和数学分数合并的join版本：

df1 = pd.DataFrame({'Grade':[70]}, index=pd.Series(['San Zhang'], name='Name'))
df2 = pd.DataFrame({'Grade':[80]}, index=pd.Series(['San Zhang'], name='Name'))
df1.join(df2, how='left', lsuffix='_Chinese', rsuffix='_Math')

	Grade_Chinese	Grade_Math
Name
San Zhang	70	80

如果想要进行类似于merge中以多列为键的操作的时候，join需要使用多级索引，例如在merge中的最后一个例子可以如下写出：

df1 = pd.DataFrame({'Age':[20,21]}, index=pd.MultiIndex.from_arrays([['San Zhang', 'San Zhang'],['one', 'two']], names=('Name','Class')))
df2 = pd.DataFrame({'Gender':['F', 'M']}, index=pd.MultiIndex.from_arrays([['San Zhang', 'San Zhang'],['two', 'one']], names=('Name','Class')))
df1

		Age
Name	Class
San Zhang	one	20
	two	21

df2

		Gender
Name	Class
San Zhang	two	F
	one	M

df1.join(df2)

		Age	Gender
Name	Class
San Zhang	one	20	M
	two	21	F

二、方向连接

1. concat

用于按列或按行合并数据

原教程中给出的数据比较规则，这里我将两个DF的结构设置的稍有区别，看看连接的效果如何

df1 = pd.DataFrame({'Name':['San Zhang','Si Li'], 'Age':[20,30]})
df2 = pd.DataFrame({'Name':['Wu Wang'], 'aGE':[40]})
pd.concat([df1, df2])

	Name	Age	aGE
0	San Zhang	20.0	NaN
1	Si Li	30.0	NaN
0	Wu Wang	NaN	40.0

这里结果是和预计的比较符合的，纵向合并在原表不存在的地方会填充空值

横向合并各表中的字段：

df2 = pd.DataFrame({'Grade':[80, 90,100]})
df3 = pd.DataFrame({'Gender':['M', 'F']})
pd.concat([df1, df2, df3], 1)

	Name	Age	Grade	Gender
0	San Zhang	20.0	80	M
1	Si Li	30.0	90	F
2	NaN	NaN	100	NaN

横向也会填充空值

concat默认状态下join=outer，表示保留所有的列，并将不存在的值设为缺失；join=inner，表示保留两个表都出现过的列。横向拼接则根据行索引对齐，join参数可以类似设置。

df2 = pd.DataFrame({'Name':['Wu Wang'], 'Gender':['M']})
pd.concat([df1, df2])

	Name	Age	Gender
0	San Zhang	20.0	NaN
1	Si Li	30.0	NaN
0	Wu Wang	NaN	M

df2 = pd.DataFrame({'Grade':[80, 90]}, index=[1, 2])
pd.concat([df1, df2], 1)

	Name	Age	Grade
0	San Zhang	20.0	NaN
1	Si Li	30.0	80.0
2	NaN	NaN	90.0

pd.concat([df1, df2], axis=1, join='inner')

	Name	Age	Grade
1	Si Li	30	80

因此，当确认要使用多表直接的方向合并时，尤其是横向的合并，可以先用reset_index方法恢复默认整数索引再进行合并，防止出现由索引的误对齐和重复索引的笛卡尔积带来的错误结果。

最后，keys参数的使用场景在于多个表合并后，用户仍然想要知道新表中的数据来自于哪个原表，这时可以通过keys参数产生多级索引进行标记。例如，第一个表中都是一班的同学，而第二个表中都是二班的同学，可以使用如下方式合并：

df1 = pd.DataFrame({'Name':['San Zhang','Si Li'], 'Age':[20,21]})
df2 = pd.DataFrame({'Name':['Wu Wang'],'Age':[21]})
pd.concat([df1, df2], keys=['one', 'two'])

		Name	Age
one	0	San Zhang	20
	1	Si Li	21
two	0	Wu Wang	21

2. 序列与表的合并

利用concat可以实现多个表之间的方向拼接，如果想要把一个序列追加到表的行末或者列末，则可以分别使用append和assign方法。

在append中，如果原表是默认整数序列的索引，那么可以使用ignore_index=True对新序列对应索引的自动标号，否则必须对Series指定name属性。

s = pd.Series(['Wu Wang', 21], index = df1.columns)
df1.append(s, ignore_index=True)

	Name	Age
0	San Zhang	20
1	Si Li	30
2	Wu Wang	21

对于assign而言，虽然可以利用其添加新的列，但一般通过df['new_col'] = ...的形式就可以等价地添加新列。同时，使用[]修改的缺点是它会直接在原表上进行改动，而assign返回的是一个临时副本：

s = pd.Series([80, 90])
df1.assign(Grade=s)

	Name	Age	Grade
0	San Zhang	20	80
1	Si Li	21	90

df1['Grade'] = s
df1

	Name	Age	Grade
0	San Zhang	20	80
1	Si Li	21	90

三、类连接操作

除了上述介绍的若干连接函数之外，pandas中还设计了一些函数能够对两个表进行某些操作，这里把它们统称为类连接操作。

1. 比较

compare是在1.1.0后引入的新函数，它能够比较两个表或者序列的不同处并将其汇总展示：

df1 = pd.DataFrame({'Name':['San Zhang', 'Si Li', 'Wu Wang'],
                    'Age':[20, 21 ,21],
                    'Class':['one', 'two', 'three']})
df2 = pd.DataFrame({'Name':['San Zhang', 'Li Si', 'Wu Wang'],
                    'Age':[20, 21 ,21],
                    'Class':['one', 'two', 'Three']})
print(df1)
print(df2)
df1.compare(df2)

        Name  Age  Class
0  San Zhang   20    one
1      Si Li   21    two
2    Wu Wang   21  three
        Name  Age  Class
0  San Zhang   20    one
1      Li Si   21    two
2    Wu Wang   21  Three

	Name		Class
	self	other	self	other
1	Si Li	Li Si	NaN	NaN
2	NaN	NaN	three	Three

结果中返回了不同值所在的行列，如果相同则会被填充为缺失值NaN，其中other和self分别指代传入的参数表和被调用的表自身。

如果想要完整显示表中所有元素的比较情况，可以设置keep_shape=True：

df1.compare(df2, keep_shape=True)

	Name		Age		Class
	self	other	self	other	self	other
0	NaN	NaN	NaN	NaN	NaN	NaN
1	Si Li	Li Si	NaN	NaN	NaN	NaN
2	NaN	NaN	NaN	NaN	three	Three

2. 组合

combine函数能够让两张表按照一定的规则进行组合，在进行规则比较时会自动进行列索引的对齐。对于传入的函数而言，每一次操作中输入的参数是来自两个表的同名Series，依次传入的列是两个表列名的并集，例如下面这个例子会依次传入A,B,C,D四组序列，每组为左右表的两个序列。同时，进行A列比较的时候，s1指代的就是一个全空的序列，因为它在被调用的表中并不存在，并且来自第一个表的序列索引会被reindex成两个索引的并集。具体的过程可以通过在传入的函数中插入适当的print方法查看。

下面的例子表示选出对应索引位置较小的元素：

【练一练】

请在上述代码的基础上修改，保留df2中4个未被df1替换的相应位置原始值。

【我的思路】

def choose_min(s1, s2):
    s2 = s2.reindex_like(s1)
    res = s1.where(s1<s2, s2)
#     res = res.mask(s1.isna()) # isna表示是否为缺失值，返回布尔序列
    return res
df1 = pd.DataFrame({'A':[1,2], 'B':[3,4], 'C':[5,6]})
df2 = pd.DataFrame({'B':[5,-6], 'C':[7,8], 'D':[9,10]}, index=[1,2])
df1.combine(df2, choose_min)

	A	B	C	D
0	NaN	NaN	NaN	NaN
1	NaN	4.0	6.0	9.0
2	NaN	-6.0	8.0	10.0

【END】

此外，设置overtwrite参数为False可以保留$被调用表$中未出现在传入的参数表中的列，而不会设置未缺失值：

df1.combine(df2, choose_min, overwrite=False)

	A	B	C	D
0	1.0	NaN	NaN	NaN
1	2.0	4.0	6.0	NaN
2	NaN	NaN	NaN	NaN

【练一练】

除了combine之外，pandas中还有一个combine_first方法，其功能是在对两张表组合时，若第二张表中的值在第一张表中对应索引位置的值不是缺失状态，那么就使用第一张表的值填充。下面给出一个例子，请用combine函数完成相同的功能。

【END】

df1 = pd.DataFrame({'A':[1,2], 'B':[3,np.nan]})
df2 = pd.DataFrame({'A':[5,6], 'B':[7,8]}, index=[1,2])
df1.combine_first(df2)

	A	B
0	1.0	3.0
1	2.0	7.0
2	6.0	8.0

四、练习

Ex1：美国疫情数据集

现有美国4月12日至11月16日的疫情报表，请将New York的Confirmed, Deaths, Recovered, Active合并为一张表，索引为按如下方法生成的日期字符串序列：

date = pd.date_range('20200412', '20201116').to_series()
date = date.dt.month.astype('string').str.zfill(2) +'-'+ date.dt.day.astype('string').str.zfill(2) +'-'+ '2020'
date = date.tolist()
date[:5]

['04-12-2020', '04-13-2020', '04-14-2020', '04-15-2020', '04-16-2020']

#先随便选一个文件看看结构
tmp = pd.read_csv('../data/us_report/04-12-2020.csv')
tmp.head()

	Province_State	Country_Region	Last_Update	Lat	Long_	Confirmed	Deaths	Recovered	Active	FIPS	Incident_Rate	People_Tested	People_Hospitalized	Mortality_Rate	UID	ISO3	Testing_Rate	Hospitalization_Rate
0	Alabama	US	2020-04-12 23:18:15	32.3182	-86.9023	3563	93	NaN	3470	1.0	75.988020	21583.0	437.0	2.610160	84000001	USA	460.300152	12.264945
1	Alaska	US	2020-04-12 23:18:15	61.3707	-152.4044	272	8	66.0	264	2.0	45.504049	8038.0	31.0	2.941176	84000002	USA	1344.711576	11.397059
2	Arizona	US	2020-04-12 23:18:15	33.7298	-111.4312	3542	115	NaN	3427	4.0	48.662422	42109.0	NaN	3.246753	84000004	USA	578.522286	NaN
3	Arkansas	US	2020-04-12 23:18:15	34.9697	-92.3731	1280	27	367.0	1253	5.0	49.439423	19722.0	130.0	2.109375	84000005	USA	761.753354	10.156250
4	California	US	2020-04-12 23:18:15	36.1162	-119.6816	22795	640	NaN	22155	6.0	58.137726	190328.0	5234.0	2.812020	84000006	USA	485.423868	22.961176

#确认一下每个文件里New York是不是只有一行，应该是每个文件里只有一个纽约，偷懒了没有一一测试o.o
tmp[tmp.Province_State=='New York']

	Province_State	Country_Region	Last_Update	Lat	Long_	Confirmed	Deaths	Recovered	Active	FIPS	Incident_Rate	People_Tested	People_Hospitalized	Mortality_Rate	UID	ISO3	Testing_Rate	Hospitalization_Rate
35	New York	US	2020-04-12 23:18:15	42.1657	-74.9481	189033	9385	23887.0	179648	36.0	1121.124012	461601.0	42594.0	4.964742	84000036	USA	2737.680538	22.532574

import os
path = "../data/us_report/" 
files= os.listdir(path)
origin = pd.DataFrame(columns=['Confirmed', 'Deaths', 'Recovered', 'Active'])
for file in files:
    df = pd.read_csv(path + file)
    df = df[df.Province_State=='New York'][['Confirmed', 'Deaths', 'Recovered', 'Active']]
    index_no = df.index[0]
    df.rename(index={index_no:file[:-4]}, inplace=True)
    origin = origin.append(df)
origin.head()

	Confirmed	Deaths	Recovered	Active
06-07-2020	378097	30374	67544.0	280179.0
06-06-2020	377316	30280	67261.0	279775.0
10-01-2020	460031	33332	76834.0	350038.0
09-22-2020	451227	33263	76246.0	341891.0
09-23-2020	451892	33264	76306.0	342496.0

panel = pd.DataFrame(index=date)
panel.head()

04-12-2020
04-13-2020
04-14-2020
04-15-2020
04-16-2020

res = panel.join(origin, how='left')
res.head()

	Confirmed	Deaths	Recovered	Active
04-12-2020	189033	9385	23887.0	179648.0
04-13-2020	195749	10058	23887.0	185691.0
04-14-2020	203020	10842	23887.0	192178.0
04-15-2020	214454	11617	23887.0	202837.0
04-16-2020	223691	14832	23887.0	208859.0

Ex2：实现join函数

请实现带有how参数的join函数

• 假设连接的两表无公共列
• 调用方式为 join(df1, df2, how="left")
• 给出测试样例

#懒人版测试样例0.0
df1 = res.sample(100)[['Confirmed', 'Deaths']]
df2 = res.sample(100)[['Recovered', 'Active']]

def my_join(df1, df2, how='left'):
    df2 = df2.reset_index().rename(columns={'index':'Date'})
    df1 = df1.reset_index().rename(columns={'index':'Date'})
    df = df1.merge(df2, how='left')
    df = df.set_index('Date').rename(index={'Date':''})
    return df

df = my_join(df1, df2)
df.head()

	Confirmed	Deaths	Recovered	Active
Date
04-28-2020	295106	22912	NaN	NaN
08-02-2020	416298	32710	NaN	NaN
05-25-2020	362764	29229	64280.0	269255.0
07-12-2020	401706	32350	NaN	NaN
05-22-2020	358154	28853	NaN	NaN

#用真正的join测试一下
real_df = df1.join(df2, how='left')
real_df.head()

	Confirmed	Deaths	Recovered	Active
04-28-2020	295106	22912	NaN	NaN
08-02-2020	416298	32710	NaN	NaN
05-25-2020	362764	29229	64280.0	269255.0
07-12-2020	401706	32350	NaN	NaN
05-22-2020	358154	28853	NaN	NaN

real_df.equals(df)

True

官方的join和我的join结果一致！
不过我用merge做的join感觉有点违背出题人的意思
测试一下速度

%%timeit
df = my_join(df1, df2)

6.53 ms ± 301 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
df = df1.join(df2, how='left')

807 µs ± 17 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

差距有些过大…我仅仅是投机取巧实现了而已，明天再看看pandas是怎么join的，但是我又把改索引的操作练了一遍感觉熟练了！