pandas right merge doesn't preserve key order

user_4336225 0
Viewed 322

Please see below code. Why do df2 and df3 differ? according to https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html

left: use only keys from left frame, similar to a SQL left outer join; preserve key order

right: use only keys from right frame, similar to a SQL right outer join; preserve key order

I have a feeling that right merge does not preserve key order.. thanks! 

import pandas as pd

print pd.__version__  # 0.20.3

A = pd.DataFrame({'lkey': ['foo1', 'foo2', 'foo4'], 'value': [1, 2, 4]})
B = pd.DataFrame({'rkey': ['foo1', 'foo2', 'foo3', 'foo4'], 'value': [5, 6, 7, 8]})
# A
#    lkey  value
# 0  foo1      1
# 1  foo2      2
# 2  foo4      4
#
# B
#    rkey  value
# 0  foo1      5
# 1  foo2      6
# 2  foo3      7
# 3  foo4      8

print '+++'
df2 = pd.merge(A, B, left_on='lkey', right_on='rkey', how='right', indicator=True)
print df2
#    lkey  value_x  rkey  value_y      _merge
# 0  foo1      1.0  foo1        5        both
# 1  foo2      2.0  foo2        6        both
# 2  foo4      4.0  foo4        8        both
# 3   NaN      NaN  foo3        7  right_only

print '+++'
df3 = pd.merge(B, A, left_on='rkey', right_on='lkey', how='left', indicator=True)
print df3

#    rkey  value_x  lkey  value_y     _merge
# 0  foo1        5  foo1      1.0       both
# 1  foo2        6  foo2      2.0       both
# 2  foo3        7   NaN      NaN  left_only
# 3  foo4        8  foo4      4.0       both
pandas merge
1 Answers

It seems like right merging would first put the intersection of the keys on top of the merged dataframe, then sort it. The 'leftover' keys of the right dataframe will then insert to the bottom of the merged dataframe.

Using the modified example from https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html

A = pd.DataFrame({'key1': ['K0', 'K2', 'K0', 'K1'],
                  'A': ['A0', 'A1', 'A2', 'A3'],
                  'B': ['B0', 'B1', 'B2', 'B3']})
B = pd.DataFrame({'key1': ['K0', 'K2', 'K0', 'K0'],
                  'C': ['C0', 'C1', 'C2', 'C3'],
                  'D': ['D0', 'D1', 'D2', 'D3']})
A
  key1   A   B
0   K0  A0  B0
1   K2  A1  B1
2   K0  A2  B2
3   K1  A3  B3

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

Doing a left join preserves the key order (of dataframe B)

pd.merge(A, B, how='left', on='key1')
  key1   A   B    C    D
0   K0  A0  B0   C0   D0
1   K0  A0  B0   C2   D2
2   K0  A0  B0   C3   D3
3   K2  A1  B1   C1   D1
4   K0  A2  B2   C0   D0
5   K0  A2  B2   C2   D2
6   K0  A2  B2   C3   D3
7   K1  A3  B3  NaN  NaN

However, a right join where the input dataframes is switched gives the following:

pd.merge(B, A, how='right', on='key1')
  key1    C    D   A   B
0   K0   C0   D0  A0  B0
1   K0   C2   D2  A0  B0
2   K0   C3   D3  A0  B0
3   K0   C0   D0  A2  B2
4   K0   C2   D2  A2  B2
5   K0   C3   D3  A2  B2
6   K2   C1   D1  A1  B1
7   K1  NaN  NaN  A3  B3
user_74396350
Related