pandas合并df的方法
pandas合并文件共有四种方法,分别是merge, append,join与concat
merge与join处理df的效果类似,可以用来根据特定列取交、并、补集,一个特殊的用法就是对表格进行注释。
append合并df更体现在两个完全平行独立的表格的合并。例如:将一个表格写到另一个表格下面。
concat的用法具有merge和append的特点
merge的用法
merge函数为:
pd.merge(left, right, how=‘inner’, on=None, left_on=None, right_on=None,
left_index=False, right_index=False, sort=True,
suffixes=(’_x’, ‘_y’), copy=True, indicator=False)
默认how=‘inner’,how还可以为outer,left,right
首先,构建dataframe
import pandas as pd
d={'gene':{'a':'gene1','b':'gene1','c':'gene2','d':'gene3'},'isoform':{'a':'isoform1','b':'isoform2','c':'isoform1','d':'isoform1'},'expression':{'a':'low:0','b':'mid:3','c':'mid:4','d':'high:9'}}
df=pd.DataFrame(d)
print(df)
gene isoform expression
a gene1 isoform1 low:0
b gene1 isoform2 mid:3
c gene2 isoform1 mid:4
d gene3 isoform1 high:9
d2={'gene':{'a':'gene1','b':'gene3','c':'gene4','d':'gene8'},'description':{'a':'transposon element','b':'nuclear genes','c':'retrotransposon','d':'unknown'}}
df2=pd.DataFrame(d2)
print(df2)
gene description
a gene1 transposon element
b gene3 nuclear genes
c gene4 retrotransposon
d gene8 unknown
根据gene这一列(on=‘gene’)分别取两个dataframe的交并补集
#交集(how='inner')
df1=pd.merge(df,df2,how='inner',on='gene')
print(df1)
gene isoform expression description
0 gene1 isoform1 low:0 transposon element
1 gene1 isoform2 mid:3 transposon element
2 gene3 isoform1 high:9 nuclear genes
#并集(how='outer')
df1=pd.merge(df,df2,how='outer',on='gene')
print(df1)
gene isoform expression description
0 gene1 isoform1 low:0 transposon element
1 gene1 isoform2 mid:3 transposon element
2 gene2 isoform1 mid:4 NaN
3 gene3 isoform1 high:9 nuclear genes
4 gene4 NaN NaN retrotransposon
5 gene8 NaN NaN unknown
#左补(how='left',对应df)
df1=pd.merge(df,df2,how='left',on='gene')
print(df1)
gene isoform expression description
0 gene1 isoform1 low:0 transposon element
1 gene1 isoform2 mid:3 transposon element
2 gene2 isoform1 mid:4 NaN
3 gene3 isoform1 high:9 nuclear genes
#右补(how='right',对应df2)
df1=pd.merge(df,df2,how='right',on='gene')
print(df1)
gene isoform expression description
0 gene1 isoform1 low:0 transposon element
1 gene1 isoform2 mid:3 transposon element
2 gene3 isoform1 high:9 nuclear genes
3 gene4 NaN NaN retrotransposon
4 gene8 NaN NaN unknown
如果两个表格中没有相同的列名,则可以通过left_on指定第一个df中的列,right_on指定第二个df中的列
df2.rename(columns={'gene':'id'},inplace=True)
print(df2)
id description
a gene1 transposon element
b gene3 nuclear genes
c gene4 retrotransposon
d gene8 unknown
df1=pd.merge(df,df2,how='inner',left_on='gene',right_on='id')
print(df1)
gene isoform expression id description
0 gene1 isoform1 low:0 gene1 transposon element
1 gene1 isoform2 mid:3 gene1 transposon element
2 gene3 isoform1 high:9 gene3 nuclear genes
merge中on参数也可以是多列
d2={'gene':{'a':'gene1','b':'gene1','c':'gene3','d':'gene8'},'isoform':{'a':'isoform2','b':'isoform3','c':'isoform1','d':'isoform2'},'description':{'a':'transposon element','b':'nuclear genes','c':'retrotransposon','d':'unknown'}}
df2=pd.DataFrame(d2)
print(df2)
gene isoform description
a gene1 isoform2 transposon element
b gene1 isoform3 nuclear genes
c gene3 isoform1 retrotransposon
d gene8 isoform2 unknown
print(df)
gene isoform expression
a gene1 isoform1 low:0
b gene1 isoform2 mid:3
c gene2 isoform1 mid:4
d gene3 isoform1 high:9
#根据gene和isoform这两列将df和df2合并
df1=pd.merge(df,df2,how='inner',on=['gene','isoform'])
print(df1)
gene isoform expression description
0 gene1 isoform2 mid:3 transposon element
1 gene3 isoform1 high:9 retrotransposon
join方法
join方法就不再赘述了
具体使用方法与merge类似:
result = left.join(right, on=['key1', 'key2'], how='inner')
#等价于
result=pd.merge(left,right,on=['key1','key2'],how='inner')
append方法
append函数
df.append(other, ignore_index=False, verify_integrity=False, sort=None)
other可以是df,series,list,dict等
ignore_index:合并之后创建新的索引,drop旧的索引,默认为False
verify_integrity:如果合并的两个数据具有相同的index就会报错,默认为False
sort:对合并之后的列的顺序进行调整,默认为None(和True相同的功能)
d={'gene':{'a':'gene1','b':'gene2','c':'gene3','d':'gene4'},'expression':{'a':'0','b':'3','c':'4','d':'9'}}
df=pd.DataFrame(d)
d2={'gene':{'a':'gene4','b':'gene5','c':'gene6','d':'gene7'},'expression':{'a':'9','b':'2','c':'3','d':'3'},'description':{'a':'transposon element','b':'nuclear genes','c':'retrotransposon','d':'unknown'}}
df2=pd.DataFrame(d2)
print(df)
gene expression
a gene1 0
b gene2 3
c gene3 4
d gene4 9
print(df2)
gene expression description
a gene4 9 transposon element
b gene5 2 nuclear genes
c gene6 3 retrotransposon
d gene7 3 unknown
df1=df.append(df2,sort=False)
print(df1)
gene expression description
a gene1 0 NaN
b gene2 3 NaN
c gene3 4 NaN
d gene4 9 NaN
a gene4 9 transposon element
b gene5 2 nuclear genes
c gene6 3 retrotransposon
d gene7 3 unknown
#如果不加sort=False参数或者sort=True或者sort=None时,列的顺序会发生改变
df1=df.append(df2,sort=True)
print(df1)
description expression gene
a NaN 0 gene1
b NaN 3 gene2
c NaN 4 gene3
d NaN 9 gene4
a transposon element 9 gene4
b nuclear genes 2 gene5
c retrotransposon 3 gene6
d unknown 3 gene7
#加上ignore_index=False
df1=df.append(df2,sort=False,ignore_index=True)
print(df1)
gene expression description
0 gene1 0 NaN
1 gene2 3 NaN
2 gene3 4 NaN
3 gene4 9 NaN
4 gene4 9 transposon element
5 gene5 2 nuclear genes
6 gene6 3 retrotransposon
7 gene7 3 unknown
concat方法
concat方法不做详细的介绍了
concat函数功能如下:
concat(objs, axis=0, join=‘outer’, join_axes=None, ignore_index=False,
keys=None, levels=None, names=None, verify_integrity=False, copy=True)
一些简单用法:
pd.concat([df1,df2]) 的效果与 df1.append(df2) 相同
此时默认axis=0
pd.concat([df1,df2],axis=1) 的效果与 pd.merge(df1,df2,left_index=True,right_index=True,how=‘outer’) 相同