pandas合并dataframe之merge,join,append,concat方法

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’) 相同