Merged implementation of pandas dataframe (append merge concat)
- 2021-07-01 07:41:45
- OfStack
Create 2 DataFrame:
>>> df1 = pd.DataFrame(np.ones((4, 4))*1, columns=list('DCBA'), index=list('4321'))
>>> df2 = pd.DataFrame(np.ones((4, 4))*2, columns=list('FEDC'), index=list('6543'))
>>> df3 = pd.DataFrame(np.ones((4, 4))*3, columns=list('FEBA'), index=list('6521'))
>>> df1
D C B A
4 1.0 1.0 1.0 1.0
3 1.0 1.0 1.0 1.0
2 1.0 1.0 1.0 1.0
1 1.0 1.0 1.0 1.0
>>> df2
F E D C
6 2.0 2.0 2.0 2.0
5 2.0 2.0 2.0 2.0
4 2.0 2.0 2.0 2.0
3 2.0 2.0 2.0 2.0
>>> df3
F E B A
6 3.0 3.0 3.0 3.0
5 3.0 3.0 3.0 3.0
2 3.0 3.0 3.0 3.0
1 3.0 3.0 3.0 3.0
1. concat
pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
keys=None, levels=None, names=None, verify_integrity=False,
copy=True) Example:
>>> pd.concat([df1, df2])
A B C D E F
4 1.0 1.0 1.0 1.0 NaN NaN
3 1.0 1.0 1.0 1.0 NaN NaN
2 1.0 1.0 1.0 1.0 NaN NaN
1 1.0 1.0 1.0 1.0 NaN NaN
6 NaN NaN 2.0 2.0 2.0 2.0
5 NaN NaN 2.0 2.0 2.0 2.0
4 NaN NaN 2.0 2.0 2.0 2.0
3 NaN NaN 2.0 2.0 2.0 2.0
1.1, axis
Default: axis=0
axis=0: Vertical direction (index) merges, merging direction index makes list addition, and non-merging direction columns takes union
axis=1: Transverse direction (columns) merging, merging direction columns for list addition, non-merging direction index for union
axis=0:
>>> pd.concat([df1, df2], axis=0)
A B C D E F
4 1.0 1.0 1.0 1.0 NaN NaN
3 1.0 1.0 1.0 1.0 NaN NaN
2 1.0 1.0 1.0 1.0 NaN NaN
1 1.0 1.0 1.0 1.0 NaN NaN
6 NaN NaN 2.0 2.0 2.0 2.0
5 NaN NaN 2.0 2.0 2.0 2.0
4 NaN NaN 2.0 2.0 2.0 2.0
3 NaN NaN 2.0 2.0 2.0 2.0
axis=1:
>>> pd.concat([df1, df2], axis=1)
D C B A F E D C
1 1.0 1.0 1.0 1.0 NaN NaN NaN NaN
2 1.0 1.0 1.0 1.0 NaN NaN NaN NaN
3 1.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0
4 1.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0
5 NaN NaN NaN NaN 2.0 2.0 2.0 2.0
6 NaN NaN NaN NaN 2.0 2.0 2.0 2.0
Note: In the original df, the row/column names of union set cannot have duplicate items, that is, columns cannot have duplicate items when axis=0, and index cannot have duplicate items when axis=1:
>>> df1.columns = list('DDBA')
>>> pd.concat([df1, df2], axis=0)
ValueError: Plan shapes are not aligned
1.2, join
Default: join = 'outer'
Row/column names in non-merge directions: take intersection (inner) and union (outer).
When axis=0, join= 'inner', columns takes the intersection:
>>> pd.concat([df1, df2], axis=0, join='inner')
D C
4 1.0 1.0
3 1.0 1.0
2 1.0 1.0
1 1.0 1.0
6 2.0 2.0
5 2.0 2.0
4 2.0 2.0
3 2.0 2.0
When axis=1, join= 'inner', index takes the intersection:
>>> pd.concat([df1, df2], axis=1, join='inner')
D C B A F E D C
4 1.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0
3 1.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0
1.3, join_axes
Default value: join_axes=None, take union
After merging, you can set the row/column name of the non-merging direction, and use the row/column name of an df
join_axes= [df1.columns] when axis=0, combined columns using df1:
>>> pd.concat([df1, df2], axis=0, join_axes=[df1.columns])
D C B A
4 1.0 1.0 1.0 1.0
3 1.0 1.0 1.0 1.0
2 1.0 1.0 1.0 1.0
1 1.0 1.0 1.0 1.0
6 2.0 2.0 NaN NaN
5 2.0 2.0 NaN NaN
4 2.0 2.0 NaN NaN
3 2.0 2.0 NaN NaN
When axis=1, axes= [df1.index], the combined index uses df2:
pd.concat([df1, df2], axis=1, join_axes=[df1.index])
D C B A F E D C
4 1.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0
3 1.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0
2 1.0 1.0 1.0 1.0 NaN NaN NaN NaN
1 1.0 1.0 1.0 1.0 NaN NaN NaN NaN
If join and join_axes are set at the same time, join_axes shall prevail:
pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
keys=None, levels=None, names=None, verify_integrity=False,
copy=True)
1.4, ignore_index
Default: ignore_index=False
Whether the merge direction ignores the original row/column name and uses the system default index, int starting from 0.
ignore_index=True when axis=0, index takes the system default index:
>>> pd.concat([df1, df2], axis=0, ignore_index=True)
A B C D E F
0 1.0 1.0 1.0 1.0 NaN NaN
1 1.0 1.0 1.0 1.0 NaN NaN
2 1.0 1.0 1.0 1.0 NaN NaN
3 1.0 1.0 1.0 1.0 NaN NaN
4 NaN NaN 2.0 2.0 2.0 2.0
5 NaN NaN 2.0 2.0 2.0 2.0
6 NaN NaN 2.0 2.0 2.0 2.0
7 NaN NaN 2.0 2.0 2.0 2.0
ignore_index=True when axis=1, columns takes the system default index:
>>> pd.concat([df1, df2], axis=1, ignore_index=True)
0 1 2 3 4 5 6 7
1 1.0 1.0 1.0 1.0 NaN NaN NaN NaN
2 1.0 1.0 1.0 1.0 NaN NaN NaN NaN
3 1.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0
4 1.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0
5 NaN NaN NaN NaN 2.0 2.0 2.0 2.0
6 NaN NaN NaN NaN 2.0 2.0 2.0 2.0
1.5, keys
Default: keys=None
You can add a layer 1 label to identify which df the row/column name belongs to.
Set keys when axis=0:
pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
keys=None, levels=None, names=None, verify_integrity=False,
copy=True) Set keys when axis=1:
pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
keys=None, levels=None, names=None, verify_integrity=False,
copy=True) You can also send a dictionary instead of keys:
pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
keys=None, levels=None, names=None, verify_integrity=False,
copy=True)
1.6, levels
Default: levels=None
Clarify the value range of row/column names:
pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
keys=None, levels=None, names=None, verify_integrity=False,
copy=True)
1.7, sort
Default value: sort=True, prompting that the new version will set the default to False and cancel this parameter
However, in 0.22. 0, although it was cancelled, it was set to True
Whether row/column names in non-merge directions are sorted. For example, in 1.1, columns is sorted by default when axis=0, and index is sorted when axis=1.
When axis=0, sort=False and columns are not sorted:
>>> pd.concat([df1, df2], axis=0, sort=False)
D C B A F E
4 1.0 1.0 1.0 1.0 NaN NaN
3 1.0 1.0 1.0 1.0 NaN NaN
2 1.0 1.0 1.0 1.0 NaN NaN
1 1.0 1.0 1.0 1.0 NaN NaN
6 2.0 2.0 NaN NaN 2.0 2.0
5 2.0 2.0 NaN NaN 2.0 2.0
4 2.0 2.0 NaN NaN 2.0 2.0
3 2.0 2.0 NaN NaN 2.0 2.0
When axis=1, sort=False and index are not sorted:
>>> pd.concat([df1, df2], axis=1, sort=False)
D C B A F E D C
4 1.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0
3 1.0 1.0 1.0 1.0 2.0 2.0 2.0 2.0
2 1.0 1.0 1.0 1.0 NaN NaN NaN NaN
1 1.0 1.0 1.0 1.0 NaN NaN NaN NaN
6 NaN NaN NaN NaN 2.0 2.0 2.0 2.0
5 NaN NaN NaN NaN 2.0 2.0 2.0 2.0
1.8. concat Multiple DataFrame
pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
keys=None, levels=None, names=None, verify_integrity=False,
copy=True)
2. append
append(self, other, ignore_index=False, verify_integrity=False)
Merge df vertically without axis attribute
It is not modified in place, but a copy is created
Example:
>>> df1.append(df2) # Equivalent to pd.concat([df1, df2])
A B C D E F
4 1.0 1.0 1.0 1.0 NaN NaN
3 1.0 1.0 1.0 1.0 NaN NaN
2 1.0 1.0 1.0 1.0 NaN NaN
1 1.0 1.0 1.0 1.0 NaN NaN
6 NaN NaN 2.0 2.0 2.0 2.0
5 NaN NaN 2.0 2.0 2.0 2.0
4 NaN NaN 2.0 2.0 2.0 2.0
3 NaN NaN 2.0 2.0 2.0 2.0
2.1. ignore_index Attribute
>>> df1.append(df2, ignore_index=True)
A B C D E F
0 1.0 1.0 1.0 1.0 NaN NaN
1 1.0 1.0 1.0 1.0 NaN NaN
2 1.0 1.0 1.0 1.0 NaN NaN
3 1.0 1.0 1.0 1.0 NaN NaN
4 NaN NaN 2.0 2.0 2.0 2.0
5 NaN NaN 2.0 2.0 2.0 2.0
6 NaN NaN 2.0 2.0 2.0 2.0
7 NaN NaN 2.0 2.0 2.0 2.0
2.2. append Multiple DataFrame
Like concat, append also supports multiple DataFrame of append
>>> df1.append([df2, df3], ignore_index=True)
A B C D E F
0 1.0 1.0 1.0 1.0 NaN NaN
1 1.0 1.0 1.0 1.0 NaN NaN
2 1.0 1.0 1.0 1.0 NaN NaN
3 1.0 1.0 1.0 1.0 NaN NaN
4 NaN NaN 2.0 2.0 2.0 2.0
5 NaN NaN 2.0 2.0 2.0 2.0
6 NaN NaN 2.0 2.0 2.0 2.0
7 NaN NaN 2.0 2.0 2.0 2.0
8 3.0 3.0 NaN NaN 3.0 3.0
9 3.0 3.0 NaN NaN 3.0 3.0
10 3.0 3.0 NaN NaN 3.0 3.0
11 3.0 3.0 NaN NaN 3.0 3.0
3. 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)
Example:
>>> left = pd.DataFrame({'A': ['a0', 'a1', 'a2', 'a3'],
'B': ['b0', 'b1', 'b2', 'b3'],
'k1': ['x', 'x', 'y', 'y']})
>>> right = pd.DataFrame({'C': ['c1', 'c2', 'c3', 'c4'],
'D': ['d1', 'd2', 'd3', 'd4'],
'k1': ['y', 'y', 'z', 'z']})
>>> left
A B k1
0 a0 b0 x
1 a1 b1 x
2 a2 b2 y
3 a3 b3 y
>>> right
C D k1
0 c1 d1 y
1 c2 d2 y
2 c3 d3 z
3 c4 d4 z
For df1 and df2, merge:
>>> pd.merge(left, right)
A B k1 C D
0 a2 b2 y c1 d1
1 a2 b2 y c2 d2
2 a3 b3 y c1 d1
3 a3 b3 y c2 d2
It can be seen that only the rows of key1=y for df1 and df2 are retained, that is, only the rows with common column items and equal values (that is, intersections) are retained after the default merge.
In this example, there are two k1=y for left and right, which finally constitute 2*2=4 rows.
If there is no common column, an error will be reported:
>>> del left['k1']
>>> pd.merge(left, right)
pandas.errors.MergeError: No common columns to perform merge on
3.1. on Attributes
Add 1 common column, but no equal value, and find that the merge returns an empty list, because only rows where all common columns are equal are kept by default:
>>> left['k2'] = list('1234')
>>> right['k2'] = list('5678')
>>> pd.merge(left, right)
Empty DataFrame
Columns: [B, A, k1, k2, F, E]
Index: []
You can specify on, set the merge base column, and merge according to k1, and the common column k2 of left and right will be changed at the same time and retained:
>>> pd.merge(left, right, on='k1')
A B k1 k2_x C D k2_y
0 a2 b2 y 3 c1 d1 5
1 a2 b2 y 3 c2 d2 6
2 a3 b3 y 4 c1 d1 5
3 a3 b3 y 4 c2 d2 6
Default: The default value for on is all common columns, in this case: on= ['k1', 'k2']
3.2. how Attributes
how value range: 'inner', 'outer', 'left', 'right'
Default: how = 'inner'
'inner': Common column values must be exactly equal:
>>> pd.merge(left, right, on='k1', how='inner')
A B k1 k2_x C D k2_y
0 a2 b2 y 3 c1 d1 5
1 a2 b2 y 3 c2 d2 6
2 a3 b3 y 4 c1 d1 5
3 a3 b3 y 4 c2 d2 6
'outer': The values of common columns are retained, and the difference set of left or right on common columns assigns NaN to the values of their missing column entries:
>>> pd.merge(left, right, on='k1', how='outer')
A B k1 k2_x C D k2_y
0 a0 b0 x 1 NaN NaN NaN
1 a1 b1 x 2 NaN NaN NaN
2 a2 b2 y 3 c1 d1 5
3 a2 b2 y 3 c2 d2 6
4 a3 b3 y 4 c1 d1 5
5 a3 b3 y 4 c2 d2 6
6 NaN NaN z NaN c3 d3 7
7 NaN NaN z NaN c4 d4 8
'left': Determine the reserved value of the common column based on DataFrame on the left, and assign NaN to the value of the missing column item on the right:
pd.merge(left, right, on='k1', how='left')
A B k1 k2_x C D k2_y
0 a0 b0 x 1 NaN NaN NaN
1 a1 b1 x 2 NaN NaN NaN
2 a2 b2 y 3 c1 d1 5
3 a2 b2 y 3 c2 d2 6
4 a3 b3 y 4 c1 d1 5
5 a3 b3 y 4 c2 d2 6
'right': Determine the reserved value of the common column according to the DataFrame on the right, and assign NaN to the value of the missing column item on the left:
>>> pd.merge(left, right, on='k1', how='right')
A B k1 k2_x C D k2_y
0 a2 b2 y 3 c1 d1 5
1 a3 b3 y 4 c1 d1 5
2 a2 b2 y 3 c2 d2 6
3 a3 b3 y 4 c2 d2 6
4 NaN NaN z NaN c3 d3 7
5 NaN NaN z NaN c4 d4 8
3.3. indicator
Default value: indicator=False, merge method is not displayed
Setting True indicates the display merge mode, that is, left/right/both:
>>> pd.merge(left, right, on='k1', how='outer', indicator=True)
A B k1 k2_x C D k2_y _merge
0 a0 b0 x 1 NaN NaN NaN left_only
1 a1 b1 x 2 NaN NaN NaN left_only
2 a2 b2 y 3 c1 d1 5 both
3 a2 b2 y 3 c2 d2 6 both
4 a3 b3 y 4 c1 d1 5 both
5 a3 b3 y 4 c2 d2 6 both
6 NaN NaN z NaN c3 d3 7 right_only
7 NaN NaN z NaN c4 d4 8 right_only