> For the complete documentation index, see [llms.txt](https://zeliang-yao.gitbook.io/my-note-zeliang-yao/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://zeliang-yao.gitbook.io/my-note-zeliang-yao/useful/pandas/merge-join-concat.md). # Merge, Join, Concat ## Merge, Join, Concat,Sort 主要会从以下几个方面和大家分享： 1. Merge 2. Join 3. Concat 4. 源码及GitHub地址 ## 1. Merge 首先merge的操作非常类似sql里面的join，实现将两个Dataframe根据一些共有的列连接起来，当然，在实际场景中，这些共有列一般是Id，\ 连接方式也丰富多样，可以选择inner(默认)，left,right,outer 这几种模式，分别对应的是内连接，左连接，右连接 ### 1.1 InnerMerge (内连接) 首先让我们简单的创建两个DF,分别为DataFrame1,DataFrame2,他们的公有列是key ```python import numpy as np import pandas as pd from pandas import Series, DataFrame ``` ```python dframe1 = DataFrame({'key':['X','Z','Y','Z','X','X'],'value_df1': np.arange(6)}) dframe1 ``` | | key | value\_df1 | | - | --- | ---------- | | 0 | X | 0 | | 1 | Z | 1 | | 2 | Y | 2 | | 3 | Z | 3 | | 4 | X | 4 | | 5 | X | 5 | ```python dframe2 = DataFrame({'key':['Q','Y','Z'],'value_df2':[1,2,3]}) dframe2 ``` | | key | value\_df2 | | - | --- | ---------- | | 0 | Q | 1 | | 1 | Y | 2 | | 2 | Z | 3 | 我们现在可以简单地使用pd.merge(dframe1,dframe2)来实现Merge功能 ```python pd.merge(dframe1,dframe2) ``` | | key | value\_df1 | value\_df2 | | - | --- | ---------- | ---------- | | 0 | Z | 1 | 3 | | 1 | Z | 3 | 3 | | 2 | Y | 2 | 2 | 我们现在需要注意一点，X仅仅是存在于dframe1的key，在dframe2中不存在，因此大家可以发现，当我们调用pd.merge的时候，会自动默认为inner join，\ 我们再换一种方式写一下，大家就明白了： ```python pd.merge(dframe1,dframe2,on='key',how='inner') ``` | | key | value\_df1 | value\_df2 | | - | --- | ---------- | ---------- | | 0 | Z | 1 | 3 | | 1 | Z | 3 | 3 | | 2 | Y | 2 | | ### 1.2 LeftMerge (左连接) ```python pd.merge(dframe1,dframe2,on='key',how='left') ``` | | key | value\_df1 | value\_df2 | | - | --- | ---------- | ---------- | | 0 | X | 0 | NaN | | 1 | Z | 1 | 3.0 | | 2 | Y | 2 | 2.0 | | 3 | Z | 3 | 3.0 | | 4 | X | 4 | NaN | | 5 | X | 5 | NaN | 我们可以看到返回的是dframe1的所有key值对应的结果，如果在dframe2中不存在，显示为Nan空值 ### 1.3 RightMerge (右连接) 右连接的原理和左连接正相反 ```python pd.merge(dframe1,dframe2,on='key',how='right') ``` | | key | value\_df1 | value\_df2 | | - | --- | ---------- | ---------- | | 0 | Z | 1.0 | 3 | | 1 | Z | 3.0 | 3 | | 2 | Y | 2.0 | 2 | | 3 | Q | NaN | 1 | 这里Q只存在于drame2的key中 ### 1.4 OuterMerge (全连接) ```python pd.merge(dframe1,dframe2,on='key',how='outer') ``` | | key | value\_df1 | value\_df2 | | - | --- | ---------- | ---------- | | 0 | X | 0.0 | NaN | | 1 | X | 4.0 | NaN | | 2 | X | 5.0 | NaN | | 3 | Z | 1.0 | 3.0 | | 4 | Z | 3.0 | 3.0 | | 5 | Y | 2.0 | 2.0 | | 6 | Q | NaN | 1.0 | ### 1.5 MultipleKey Merge (基于多个key上的merge) 刚才我们都是仅仅实现的在一个key上的merge，当然我们也可以实现基于多个keys的merge ```python df_left = DataFrame({'key1': ['SF', 'SF', 'LA'], 'key2': ['one', 'two', 'one'], 'left_data': [10,20,30]}) df_left ``` | | key1 | key2 | left\_data | | - | ---- | ---- | ---------- | | 0 | SF | one | 10 | | 1 | SF | two | 20 | | 2 | LA | one | 30 | ```python df_right = DataFrame({'key1': ['SF', 'SF', 'LA', 'LA'], 'key2': ['one', 'one', 'one', 'two'], 'right_data': [40,50,60,70]}) df_right ``` | | key1 | key2 | right\_data | | - | ---- | ---- | ----------- | | 0 | SF | one | 40 | | 1 | SF | one | 50 | | 2 | LA | one | 60 | | 3 | LA | two | 70 | ```python pd.merge(df_left, df_right, on=['key1', 'key2']) ``` | | key1 | key2 | left\_data | right\_data | | - | ---- | ---- | ---------- | ----------- | | 0 | SF | one | 10 | 40 | | 1 | SF | one | 10 | 50 | | 2 | LA | one | 30 | 60 | ```python pd.merge(df_left, df_right, on=['key1', 'key2'],how='outer') ``` | | key1 | key2 | left\_data | right\_data | | - | ---- | ---- | ---------- | ----------- | | 0 | SF | one | 10.0 | 40.0 | | 1 | SF | one | 10.0 | 50.0 | | 2 | SF | two | 20.0 | NaN | | 3 | LA | one | 30.0 | 60.0 | | 4 | LA | two | NaN | 70.0 | 这里还有一个地方非常有意思，大家可以发现现在df\_left,df\_right作为key的两列分别是key1和key2，它们的名字是相同的，刚刚我们是通过制定on=\[‘key1’, ‘key2’],那如果我们只指定一列会怎么样呢？ ```python pd.merge(df_left,df_right,on='key1') ``` | | key1 | key2\_x | left\_data | key2\_y | right\_data | | - | ---- | ------- | ---------- | ------- | ----------- | | 0 | SF | one | 10 | one | 40 | | 1 | SF | one | 10 | one | 50 | | 2 | SF | two | 20 | one | 40 | | 3 | SF | two | 20 | one | 50 | | 4 | LA | one | 30 | one | 60 | | 5 | LA | one | 30 | two | 70 | 大家可以看到pandas自动把key2这一列拆分成了key2\_x和key2\_y，都会显示在最后的merge结果里，如果我们想要给这两列重新命名，也是很容易的： ```python pd.merge(df_left,df_right, on='key1',suffixes=('_lefty','_righty')) ``` | | key1 | key2\_lefty | left\_data | key2\_righty | right\_data | | - | ---- | ----------- | ---------- | ------------ | ----------- | | 0 | SF | one | 10 | one | 40 | | 1 | SF | one | 10 | one | 50 | | 2 | SF | two | 20 | one | 40 | | 3 | SF | two | 20 | one | 50 | | 4 | LA | one | 30 | one | 60 | | 5 | LA | one | 30 | two | 70 | 像这样，可以通过suffixes参数来指定拆分的列的名字。 ### 1.6 Merge on Index (基于index上的merge) ```python df_left = DataFrame({'key': ['X','Y','Z','X','Y'],'data': range(5)}) df_right = DataFrame({'group_data': [10, 20]}, index=['X', 'Y']) ``` ```python df_left ``` | | key | data | | - | --- | ---- | | 0 | X | 0 | | 1 | Y | 1 | | 2 | Z | 2 | | 3 | X | 3 | | 4 | Y | 4 | ``` df_right ``` | | group\_data | | - | ----------- | | X | 10 | | Y | 20 | 现在想要实现两个Dataframe的merge，但是条件是通过df\_left的Key和df\_right的Index ```python pd.merge(df_left,df_right,left_on='key',right_index=True) ``` | | key | data | group\_data | | - | --- | ---- | ----------- | | 0 | X | 0 | 10 | | 3 | X | 3 | 10 | | 1 | Y | 1 | 20 | | 4 | Y | 4 | 20 | 这样我们也可以得到结果。 ```python pd.merge(df_left,df_right,left_on='key',right_index=True,how='outer') ``` | | key | data | group\_data | | - | --- | ---- | ----------- | | 0 | X | 0 | 10.0 | | 3 | X | 3 | 10.0 | | 1 | Y | 1 | 20.0 | | 4 | Y | 4 | 20.0 | | 2 | Z | 2 | NaN | 其他的merge方式就类似啦，这里就不一一说了，只是举一个outer join的例子 ```python pd.merge(df_left,df_right,left_on='key',right_index=True,how='outer') ``` | | key | data | group\_data | | - | --- | ---- | ----------- | | 0 | X | 0 | 10.0 | | 3 | X | 3 | 10.0 | | 1 | Y | 1 | 20.0 | | 4 | Y | 4 | 20.0 | | 2 | Z | 2 | NaN | 我们可以尝试其他的merge，比如如果一个df的index是多层嵌套的情况 ```python df_left_hr = DataFrame({'key1': ['SF','SF','SF','LA','LA'], 'key2': [10, 20, 30, 20, 30], 'data_set': np.arange(5.)}) df_right_hr = DataFrame(np.arange(10).reshape((5, 2)), index=[['LA','LA','SF','SF','SF'], [20, 10, 10, 10, 20]], columns=['col_1', 'col_2']) ``` ``` df_left_hr ``` | | key1 | key2 | data\_set | | - | ---- | ---- | --------- | | 0 | SF | 10 | 0.0 | | 1 | SF | 20 | 1.0 | | 2 | SF | 30 | 2.0 | | 3 | LA | 20 | 3.0 | | 4 | LA | 30 | 4.0 | ``` df_right_hr ``` | | | col\_1 | col\_2 | | -- | -- | ------ | ------ | | LA | 20 | 0 | 1 | | 10 | 2 | 3 | | | SF | 10 | 4 | 5 | | 10 | 6 | 7 | | | 20 | 8 | 9 | | 现在我们穿建了两个Dataframe 分别是df\_left\_hr和df\_right\_hr（Index两层），如果我们想通过使用df\_left\_hr的key1，key2 及df\_right\_hr的Index作为merge 的列，也是没有问题的 ```python pd.merge(df_left_hr,df_right_hr,left_on=['key1','key2'],right_index=True) ``` | | key1 | key2 | data\_set | col\_1 | col\_2 | | - | ---- | ---- | --------- | ------ | ------ | | 0 | SF | 10 | 0.0 | 4 | 5 | | 0 | SF | 10 | 0.0 | 6 | 7 | | 1 | SF | 20 | 1.0 | 8 | 9 | | 3 | LA | 20 | 3.0 | 0 | 1 | 基本到这里，我已经和大家分享了基础的Merge有关的所有操作，如果你平时生活工作中经常使用Excel执行类似操作的话，可以学习一下Merge哈，它会大幅度\ 减轻你的工作强度的！ ## 2.Join 现在我们可以接着来看join相关的操作，先让我们看一个小例子 ```python left = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'], 'B': ['B0', 'B1', 'B2', 'B3']}, index = ['K0', 'K1', 'K2', 'K3']) right = pd.DataFrame({'C': ['C0', 'C1', 'C2', 'C3'], 'D': ['D0', 'D1', 'D2', 'D3']}, index = ['K0', 'K1', 'K2', 'K3']) ``` ``` left ``` | | A | B | | -- | -- | -- | | K0 | A0 | B0 | | K1 | A1 | B1 | | K2 | A2 | B2 | | K3 | A3 | B3 | ``` right ``` | | C | D | | -- | -- | -- | | K0 | C0 | D0 | | K1 | C1 | D1 | | K2 | C2 | D2 | | K3 | C3 | D3 | ```python left.join(right) ``` | | A | B | C | D | | -- | -- | -- | -- | -- | | K0 | A0 | B0 | C0 | D0 | | K1 | A1 | B1 | C1 | D1 | | K2 | A2 | B2 | C2 | D2 | | K3 | A3 | B3 | C3 | D3 | 其实通过这一个小例子大家也就明白了，join无非就是合并，默认是横向，还有一个点需要注意的是，我们其实可以通过join实现和merge一样的效果，但是为了\ 避免混淆，我不会多举其他的例子了，因为我个人认为一般情况下还是用merge函数好一些 ## 3. Concat 为了更加全面彻底地了解Concat函数，大家可以先从一维的Numpy Array开始，首先让我们简单的创建一个矩阵： ```python arr1 = np.arange(9).reshape((3,3)) arr1 ``` ``` array([[0, 1, 2], [3, 4, 5], [6, 7, 8]]) ``` 让我们通过concatenate函数进行横向拼接 ```python np.concatenate([arr1,arr1],axis=1) ``` ```python array([[0, 1, 2, 0, 1, 2], [3, 4, 5, 3, 4, 5], [6, 7, 8, 6, 7, 8]]) ``` 再让我们进行纵向拼接： ```python np.concatenate([arr1,arr1],axis=0) ``` ```python array([[0, 1, 2], [3, 4, 5], [6, 7, 8], [0, 1, 2], [3, 4, 5], [6, 7, 8]]) ``` 有了基础的印象之后，现在让我们看看在pandas中是如何操作的： ```python ser1 = Series([0,1,2],index=['T','U','V']) ser2 = Series([3,4],index=['X','Y']) pd.concat([ser1,ser2]) ``` ```python T 0 U 1 V 2 X 3 Y 4 dtype: int64 ``` 在上面的例子中，我们分别创建了两个没有重复Index的Series,然后用concat默认的把它们合并在一起，这时生成的依然是Series类型，如果我们把axis换成1，那生成的就是Dataframe,像下面一样 ```python pd.concat([ser1,ser2],axis=1,sort =True) ``` | | 0 | 1 | | - | --- | --- | | T | 0.0 | NaN | | U | 1.0 | NaN | | V | 2.0 | NaN | | X | NaN | 3.0 | | Y | NaN | 4.0 | 我们还可以指定在哪些index上进行concat: ```python pd.concat([ser1,ser2],axis=1,join_axes=[['U','V','Y']]) ``` | | 0 | 1 | | - | --- | --- | | U | 1.0 | NaN | | V | 2.0 | NaN | | Y | NaN | 4.0 | 也可以给不同组的index加一层标签 ```python pd.concat([ser1,ser2],keys=['cat1','cat2']) ``` ```python cat1 T 0 U 1 V 2 cat2 X 3 Y 4 dtype: int64 ``` 如果把axis换成是1，那么keys就会变成column的名字： ```python pd.concat([ser1,ser2],axis=1,keys=['cat1','cat2'],sort=True) ``` | | cat1 | cat2 | | - | ---- | ---- | | T | 0.0 | NaN | | U | 1.0 | NaN | | V | 2.0 | NaN | | X | NaN | 3.0 | | Y | NaN | 4.0 | 如果是两个现成的dataframe直接进行concat也是一样： ```python dframe1 = DataFrame(np.random.randn(4,3), columns=['X', 'Y', 'Z']) dframe2 = DataFrame(np.random.randn(3, 3), columns=['Y', 'Q', 'X']) ``` ``` dframe1 ``` | | X | Y | Z | | - | --------- | --------- | --------- | | 0 | 1.119976 | -0.853960 | 0.027451 | | 1 | -0.536831 | 0.982092 | -0.157650 | | 2 | -0.219322 | -1.489809 | 1.607735 | | 3 | 0.767249 | -1.661912 | 0.038837 | ``` dframe2 ``` | | Y | Q | X | | - | --------- | -------- | --------- | | 0 | -0.035560 | 0.875282 | -1.630508 | | 1 | -0.439484 | 0.096247 | 1.335693 | | 2 | 0.746299 | 0.568684 | 1.197015 | ```python pd.concat([dframe1,dframe2],sort=True) ``` | | Q | X | Y | Z | | - | -------- | --------- | --------- | --------- | | 0 | NaN | 1.119976 | -0.853960 | 0.027451 | | 1 | NaN | -0.536831 | 0.982092 | -0.157650 | | 2 | NaN | -0.219322 | -1.489809 | 1.607735 | | 3 | NaN | 0.767249 | -1.661912 | 0.038837 | | 0 | 0.875282 | -1.630508 | -0.035560 | NaN | | 1 | 0.096247 | 1.335693 | -0.439484 | NaN | | 2 | 0.568684 | 1.197015 | 0.746299 | NaN | ## 4. 总结 Github仓库地址： [https://github.com/yaozeliang/pandas\_shar](https://github.com/yaozeliang/pandas_share)