连接
import numpy as np
import pandas as pd
一、关系型连接
1. 连接的概念
on参数:连接的键值 how参数:连接形式:merge、join(left、right、inner、outer)
只要两边同时出现的值,就以笛卡尔积的方式加入,如果单边出现则根据连接形式进行处理
2. 值连接
1.上面是两张表根据某一列的值来连接,事实上还可以通过几列值的组合进行连接,这种基于值的连接在 pandas 中可以由 merge 函数实现
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
2.连接条件不具备相同列名 如果两个表中想要连接的列不具备相同的列名,可以通过 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
3.出现非连接条件的重复列名 通过 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
4.出现重复元素 指定 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 连接,第一个是指左右表的键都是唯一的,后面两个分别指左表键唯一和右表键唯一
3. 练一练
上面以多列为键的例子中,错误写法显然是一种多对多连接,而正确写法是一对一连接,请修改原表,使得以多列为键的正确写法能够通过 validate=‘1:m’ 的检验,但不能通过 validate=‘m:1’ 的检验。
思考:满足一对多,不满足多对一,则应该是左边可以在右边匹配到多个值,右表存在左表没有的值。
df1 = pd. DataFrame( { 'Name' : [ 'San Zhang' ] ,
'Age' : [ 20 ] ,
'Class' : [ 'one' ] } )
df2 = pd. DataFrame( { 'Name' : [ 'San Zhang' , 'San Zhang' , 'Si Li' ] ,
'Gender' : [ 'F' , 'M' , 'F' ] ,
'Class' : [ 'two' , 'one' , 'one' ] } )
df1. merge( df2, on= [ 'Name' ] , how= 'left' , validate= '1:m' )
Name Age Class_x Gender Class_y 0 San Zhang 20 one F two 1 San Zhang 20 one M one
try :
df1. merge( df2, on= [ 'Name' ] , how= 'left' , validate= 'm:1' )
except Exception as e:
Err_Msg = e
Err_Msg
pandas.errors.MergeError('Merge keys are not unique in right dataset; not a many-to-one merge')
4. 索引连接
把索引当作键,利用 join 函数来处理索引连接。 参数: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
1.lsuffix、rsuffix
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
2.多级索引
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. join( df2)
Age Gender Name Class San Zhang one 20 M two 21 F
二、方向连接
1. concat
不关心以哪一列为键来合并,只是希望把两个表或者多个表按照纵向或者横向拼接,使用concat。 参数: axis:拼接的方向。默认axis=0:纵向拼接 join:连接形式 keys:新表中指示来自于哪一张旧表的名字 注意: join 和 keys 与之前提到的 join 函数和键的概念没有任何关系。
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 0 San Zhang 20 1 Si Li 30 0 Wu Wang 40
df2 = pd. DataFrame( { 'Grade' : [ 80 , 90 ] } )
df3 = pd. DataFrame( { 'Gender' : [ 'M' , 'F' ] } )
pd. concat( [ df1, df2, df3] , 1 )
Name Age Grade Gender 0 San Zhang 20 80 M 1 Si Li 30 90 F
虽然说 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' )
因此,当确认要使用多表直接的方向合并时,尤其是横向的合并,可以先用 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. 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 21 2 Wu Wang 21
3. assign
对于 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
四、类连接操作
1. 比较
compare:能够比较两个表或者序列的不同处并将其汇总展示
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' ] } )
df1. compare( df2)
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. 组合
1.combine 函数能够让两张表按照一定的规则进行组合,在进行规则比较时会自动进行列索引的对齐。对于传入的函数而言,每一次操作中输入的参数是来自两个表的同名 Series ,依次传入的列是两个表列名的并集
例:依次传入 A,B,C,D 四组序列,每组为左右表的两个序列。同时,进行 A 列比较的时候, s1 指代的就是一个全空的序列,因为它在被调用的表中并不存在,并且来自第一个表的序列索引会被 reindex 成两个索引的并集
def choose_min ( s1, s2) :
s2 = s2. reindex_like( s1)
res = s1. where( s1< s2, s2)
res = res. mask( s1. 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 NaN 2 NaN NaN NaN NaN
3. 练一练1
请在上述代码的基础上修改,保留 df2 中4个未被 df1 替换的相应位置原始值。
思路:直接在mask中将df2的是na的值替换为s2
def choose_min1 ( s1, s2) :
s2 = s2. reindex_like( s1)
res = s1. where( s1< s2, s2)
res = res. mask( s1. isna( ) , s2)
return res
df1. combine( df2, choose_min1)
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
2.overwrite参数 设置 overtwrite 参数为 False 可以保留 被调用表 中未出现在传入的参数表中的列,而不会设置未缺失值:
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, 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
4. 练一练2
除了 combine 之外, pandas 中还有一个 combine_first 方法,其功能是在对两张表组合时,若第二张表中的值在第一张表中对应索引位置的值不是缺失状态,那么就使用第一张表的值填充。下面给出一个例子,请用 combine 函数完成相同的功能。
思路:当s1不为空的时候取s1,当s1为空的时候取s2
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)
def choose_min2 ( s1, s2) :
s2 = s2. reindex_like( s1)
res = s1. where( s1. isna( ) , s1)
res = res. mask( s1. isna( ) , s2)
return res
df1. combine( df2, choose_min2, overwrite= False )
五、练习:美国疫情数据集
现有美国4月12日至11月16日的疫情报表,请将 New York 的 Confirmed, Deaths, Recovered, Active 合并为一张表,索引为按如下方法生成的日期字符串序列
思路:发现4月12日至11月16存在多张表格中,首先将多张表中我们需要的数据合并到,观察数据发现数据存储时文件名只有日期不一致,先使用循环将所有的数据写入一个列表中,在循环的过程中将索引值修改为我们设置的日期。因为使用concat会将前面的作为列索引,所以建立了一张空的带列明的数据框,外部读取的数据就可以直接向下插入了 其中遇到了一个小问题:list(‘foo’)*n 与[‘foo’]*n(list[‘foo’]n)的区别,list(‘foo’)会将所有的元素分开 n, [‘foo’]*n则会将foo看作一个整体
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']
df = pd. read_csv( 'data/us_report/04-12-2020.csv' )
df. head( 2 )
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
new = pd. DataFrame( columns= [ 'Confirmed' , 'Deaths' , 'Recovered' , 'Active' ] )
new
Confirmed Deaths Recovered Active
for i in date:
df = pd. read_csv( 'data/us_report/' + i + '.csv' )
df2 = df[ [ 'Confirmed' , 'Deaths' , 'Recovered' , 'Active' ] ]
df2. index = [ i] * df2. shape[ 0 ]
new = pd. concat( [ new, df2] )
new. head( )
Confirmed Deaths Recovered Active 04-12-2020 3563 93 NaN 3470 04-12-2020 272 8 66.0 264 04-12-2020 3542 115 NaN 3427 04-12-2020 1280 27 367.0 1253 04-12-2020 22795 640 NaN 22155