dataframe的concatenate 与 merge

import numpy as np
import pandas as pd
import matplotlib as mpl

#concatenate 函数
df1 = pd.DataFrame(np.random.randn(4,3),index=list("abcd"),columns=list("ABC"))
df1

	A	B	C
a	0.283456	0.605428	0.145223
b	1.782473	-1.043145	0.844106
c	0.236397	2.063387	-0.573410
d	0.204641	0.561990	1.709260

df2 = pd.DataFrame(np.random.randn(3,3),index=list("abd"),columns=list("CDE"))
df2

	C	D	E
a	0.044766	-1.580057	-0.512200
b	-1.187690	0.870686	-1.442228
d	0.390094	0.222911	-0.807610

pd.concat(
    objs:Union[Iterable[~FrameOrSeries], Mapping[collections.abc.Hashable, ~FrameOrSeries]],
    axis=0,
    join='outer',
    ignore_index:bool=False,
    keys=None,
    levels=None,
    names=None,
    verify_integrity:bool=False,
    sort:bool=False,
    copy:bool=True,
)

pd.concat([df1,df2],axis=0) #axis=0表示按行堆叠

	A	B	C	D	E
a	0.283456	0.605428	0.145223	NaN	NaN
b	1.782473	-1.043145	0.844106	NaN	NaN
c	0.236397	2.063387	-0.573410	NaN	NaN
d	0.204641	0.561990	1.709260	NaN	NaN
a	NaN	NaN	0.044766	-1.580057	-0.512200
b	NaN	NaN	-1.187690	0.870686	-1.442228
d	NaN	NaN	0.390094	0.222911	-0.807610

pd.concat([df1,df2],axis=0,keys=["X","Y"]) #keys真的好用，相当于给dataframe命名;当axis=1时相当于给columns命名

		A	B	C	D	E
X	a	0.283456	0.605428	0.145223	NaN	NaN
	b	1.782473	-1.043145	0.844106	NaN	NaN
	c	0.236397	2.063387	-0.573410	NaN	NaN
	d	0.204641	0.561990	1.709260	NaN	NaN
Y	a	NaN	NaN	0.044766	-1.580057	-0.512200
	b	NaN	NaN	-1.187690	0.870686	-1.442228
	d	NaN	NaN	0.390094	0.222911	-0.807610

pd.concat([df1,df2],axis=0,ignore_index=True) #index 消除了

	A	B	C	D	E
0	0.283456	0.605428	0.145223	NaN	NaN
1	1.782473	-1.043145	0.844106	NaN	NaN
2	0.236397	2.063387	-0.573410	NaN	NaN
3	0.204641	0.561990	1.709260	NaN	NaN
4	NaN	NaN	0.044766	-1.580057	-0.512200
5	NaN	NaN	-1.187690	0.870686	-1.442228
6	NaN	NaN	0.390094	0.222911	-0.807610

pd.concat([df1,df2],axis=0,join="inner") #如果concatenate方式为inner，则只取重叠的数据列

	C
a	0.145223
b	0.844106
c	-0.573410
d	1.709260
a	0.044766
b	-1.187690
d	0.390094

#换个轴试一下
pd.concat([df1,df2],axis=1) #算是左右合并吧，注意默认的join方式是outer

	A	B	C	C	D	E
a	0.283456	0.605428	0.145223	0.044766	-1.580057	-0.512200
b	1.782473	-1.043145	0.844106	-1.187690	0.870686	-1.442228
c	0.236397	2.063387	-0.573410	NaN	NaN	NaN
d	0.204641	0.561990	1.709260	0.390094	0.222911	-0.807610

pd.concat([df1,df2],axis=1,join="inner") #仅把共有的行左右合并了

	A	B	C	C	D	E
a	0.283456	0.605428	0.145223	0.044766	-1.580057	-0.512200
b	1.782473	-1.043145	0.844106	-1.187690	0.870686	-1.442228
d	0.204641	0.561990	1.709260	0.390094	0.222911	-0.807610

#concat与reindex联用
pd.concat([df1,df2.reindex(df1.index)],axis=1)  #reindex先把df1与df2共有的部分提取出来，然后在左右合并

	A	B	C	C	D	E
a	0.283456	0.605428	0.145223	0.044766	-1.580057	-0.512200
b	1.782473	-1.043145	0.844106	-1.187690	0.870686	-1.442228
c	0.236397	2.063387	-0.573410	NaN	NaN	NaN
d	0.204641	0.561990	1.709260	0.390094	0.222911	-0.807610

df1.append(df2)  #append方法其实是concat的实例，axis=0 ，join=outer

	A	B	C	D	E
a	0.283456	0.605428	0.145223	NaN	NaN
b	1.782473	-1.043145	0.844106	NaN	NaN
c	0.236397	2.063387	-0.573410	NaN	NaN
d	0.204641	0.561990	1.709260	NaN	NaN
a	NaN	NaN	0.044766	-1.580057	-0.512200
b	NaN	NaN	-1.187690	0.870686	-1.442228
d	NaN	NaN	0.390094	0.222911	-0.807610

#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,
    validate=None,
)
#其实最重要的问题是搞懂，左右dataframe merge时关键列是一对一，还是一对多或者多对一或者多对多
#上述四种情况下merge函数是如何处理的呢？

left = pd.DataFrame({"key1": ["K0", "K0", "K1", "K2"], "key2": ["K0", "K1", "K0", "K1"],"A": ["A0", "A1", "A2", "A3"],"B": ["B0", "B1", "B2", "B3"]})
left

	key1	key2	A	B
0	K0	K0	A0	B0
1	K0	K1	A1	B1
2	K1	K0	A2	B2
3	K2	K1	A3	B3

right = pd.DataFrame({"key1": ["K0", "K1", "K1", "K2"],"key2": ["K0", "K0", "K0", "K0"],"C": ["C0", "C1", "C2", "C3"],"D": ["D0", "D1", "D2", "D3"]})
right

	key1	key2	C	D
0	K0	K0	C0	D0
1	K1	K0	C1	D1
2	K1	K0	C2	D2
3	K2	K0	C3	D3

pd.merge(left,right,on=["key1","key2"]) #how="inner" ,注意这里的的一对多的关系，个人理解how="inner"会把两个dataframe的keys的交际列出来

	key1	key2	A	B	C	D
0	K0	K0	A0	B0	C0	D0
1	K1	K0	A2	B2	C1	D1
2	K1	K0	A2	B2	C2	D2

pd.merge(left,right,on=["key1","key2"],how="outer") #how="outer" ，个人理解how="outer"会把两个dataframe的keys都列出来

	key1	key2	A	B	C	D
0	K0	K0	A0	B0	C0	D0
1	K0	K1	A1	B1	NaN	NaN
2	K1	K0	A2	B2	C1	D1
3	K1	K0	A2	B2	C2	D2
4	K2	K1	A3	B3	NaN	NaN
5	K2	K0	NaN	NaN	C3	D3

pd.merge(left,right,on=["key1","key2"],how="left")#how="left",应该是key以左边的为主，而且一对多的行也会列出来，但是右边多的key则不会列出来

	key1	key2	A	B	C	D
0	K0	K0	A0	B0	C0	D0
1	K0	K1	A1	B1	NaN	NaN
2	K1	K0	A2	B2	C1	D1
3	K1	K0	A2	B2	C2	D2
4	K2	K1	A3	B3	NaN	NaN

pd.merge(left,right,on=["key1","key2"],how="right")#how="right",应该是key以右边的为主，而且一对多的行也会列出来，但是左边多的key则不会列出来

	key1	key2	A	B	C	D
0	K0	K0	A0	B0	C0	D0
1	K1	K0	A2	B2	C1	D1
2	K1	K0	A2	B2	C2	D2
3	K2	K0	NaN	NaN	C3	D3

#Here is another example with duplicate join keys in DataFrames:
left = pd.DataFrame({"A": [1, 2], "B": [2, 2]})
right = pd.DataFrame({"A": [4, 5, 6], "B": [2, 2, 2]})
result = pd.merge(left, right, on="B", how="outer")
print(left)
print(right)
print(result)

   A  B
0  1  2
1  2  2
   A  B
0  4  2
1  5  2
2  6  2
   A_x  B  A_y
0    1  2    4
1    1  2    5
2    1  2    6
3    2  2    4
4    2  2    5
5    2  2    6

#the merge indicator
df1 = pd.DataFrame({"col1": [0, 1], "col_left": ["a", "b"]})
df2 = pd.DataFrame({"col1": [1, 2, 2], "col_right": [2, 2, 2]})
pd.merge(df1, df2, on="col1", how="outer", indicator=True)

	col1	col_left	col_right	_merge
0	0	a	NaN	left_only
1	1	b	2.0	both
2	2	NaN	2.0	right_only
3	2	NaN	2.0	right_only