python pandas dataframe 合并_python中pandas下dataframe的合并方法小结

python中pandas是数据分析的利器，有两种基本的数据形式，Series、DataFrame两种，Series是单列的，DataFrame是平面的二维或者多维的。

今天遇到一个问题，如何将两个dataFrame合并：

总的来说有两种形式的合并：列名相同的合并，行名相同的合并

列名相同的合并：append()函数，假设有如下数据。

df = pd.DataFrame(np.random.randn(4,3),index=['a','b','c','d'],columns=['one','two','three']) >>> df         one       two     three a  0.865144 -0.270107 -0.930733 b -0.292372 -0.492537 -1.160942 c -1.266364 -2.103726 -1.133904 d  0.023444  0.824730 -1.905882

df2 = pd.DataFrame(np.random.randn(4,3),index=['e','f','g','h'],columns=['one','two','three']) >>> df2         one       two     three e -0.099827  0.547031 -0.102850 f  0.034353 -0.010019  0.132724 g -1.761880  0.284228 -0.834201 h -0.196304  0.107982 -1.143884 >>> df.append(df2)         one       two     three a  0.865144 -0.270107 -0.930733 b -0.292372 -0.492537 -1.160942 c -1.266364 -2.103726 -1.133904 d  0.023444  0.824730 -1.905882 e -0.099827  0.547031 -0.102850 f  0.034353 -0.010019  0.132724 g -1.761880  0.284228 -0.834201 h -0.196304  0.107982 -1.143884

下面是行名相同的合并：利用merge函数还有一个DdataFrame内置的join函数：

df3 = pd.DataFrame(np.random.randn(4,3),index=['a','b','c','d'],columns=['five','six','seven'])

>>> df3        five       six     seven a  0.504269  0.246821  0.273119 b -0.284046  1.346925  1.346248 c -0.829080  0.214783 -0.017546 d -0.879078  0.657936 -1.307960

df.join(df3)         one       two     three      five       six     seven a  0.865144 -0.270107 -0.930733  0.504269  0.246821  0.273119 b -0.292372 -0.492537 -1.160942 -0.284046  1.346925  1.346248 c -1.266364 -2.103726 -1.133904 -0.829080  0.214783 -0.017546 d  0.023444  0.824730 -1.905882 -0.879078  0.657936 -1.307960 上面是join()函数，其实和SQL中的join函数一模一样，merge函数是一种能够设置参数的join函数，具体的函数是这样的：

DataFrame.merge(right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, sort=False, suffixes=('_x', '_y'), copy=True, indicator=False, validate=None)

right : DataFrame(数据框)

how : {‘left’, ‘right’, ‘outer’, ‘inner’}, default ‘inner’

left: 只是用左边表的key值, 相似与sql左外连接; 保留key的顺序right: use only keys from right frame,相当于sql右外连接 preserve key orderouter: use union of keys from both frames, 相似于sql全连接; sort keys lexicographicallyinner: use intersection of keys from both frames, 相当于sql内连接; preserve the order of the left keys

on : label or list

Column or index level names to join on. These must be found in both DataFrames. If on is None and not merging on indexes then this defaults to the intersection of the columns in both DataFrames.

left_on : label or list, or array-like

Column or index level names to join on in the left DataFrame. Can also be an array or list of arrays of the length of the left DataFrame. These arrays are treated as if they are columns.

right_on : label or list, or array-like

Column or index level names to join on in the right DataFrame. Can also be an array or list of arrays of the length of the right DataFrame. These arrays are treated as if they are columns.

left_index : boolean, default False

Use the index from the left DataFrame as the join key(s). If it is a MultiIndex, the number of keys in the other DataFrame (either the index or a number of columns) must match the number of levels

right_index : boolean, default False

Use the index from the right DataFrame as the join key. Same caveats as left_index

sort : boolean, default False

Sort the join keys lexicographically in the result DataFrame. If False, the order of the join keys depends on the join type (how keyword)

suffixes : 2-length sequence (tuple, list, …)

Suffix to apply to overlapping column names in the left and right side, respectively

copy : boolean, default True

If False, do not copy data unnecessarily

indicator : boolean or string, default False

If True, adds a column to output DataFrame called “_merge” with information on the source of each row. If string, column with information on source of each row will be added to output DataFrame, and column will be named value of string. Information column is Categorical-type and takes on a value of “left_only” for observations whose merge key only appears in ‘left’ DataFrame, “right_only” for observations whose merge key only appears in ‘right’ DataFrame, and “both” if the observation’s merge key is found in both.

validate : string, default None

If specified, checks if merge is of specified type.

“one_to_one” or “1:1”: check if merge keys are unique in both left and right datasets.“one_to_many” or “1:m”: check if merge keys are unique in left dataset.“many_to_one” or “m:1”: check if merge keys are unique in right dataset.“many_to_many” or “m:m”: allowed, but does not result in checks.

>>> df3 = pd.DataFrame(np.random.randn(4,3),index=['a','b','c','d'],columns=['five','six','seven']) >>> df3        five       six     seven a  0.275944 -0.731434  1.380727 b  0.222201  1.384872  0.183260 c -1.533621 -1.730659 -0.176278 d  1.303702 -0.191982 -0.577617 >>> df         one       two     three a  0.865144 -0.270107 -0.930733 b -0.292372 -0.492537 -1.160942 c -1.266364 -2.103726 -1.133904 d  0.023444  0.824730 -1.905882

>>> df.merge(df3,left_index=True,right_index=True)         one       two     three      five       six     seven a  0.865144 -0.270107 -0.930733  0.275944 -0.731434  1.380727 b -0.292372 -0.492537 -1.160942  0.222201  1.384872  0.183260 c -1.266364 -2.103726 -1.133904 -1.533621 -1.730659 -0.176278 d  0.023444  0.824730 -1.905882  1.303702 -0.191982 -0.577617

这是两张表的连接其他的参数，left_on right_on 表示两张表join的主键所在的列命。