python pandas sort columns after merge

Question

I have merged two pandas dataframes with multiple overlapping columns. I would like to put the overlapping columns side by side.

merge = df1.merge(df2, how='outer')

Output:

A,B,C,D,A_x,B_x,C_x,D_x

I would like the output to be:

A,A_x,B,B_x,C,C_x,D,D_x

I can do this explicitly but I have many columns and would like a 'dynamic' solution.

Answer 1

Use .sort_index(axis=1):

result = df1.merge(df2, how='outer').sort_index(axis=1)

Answer 2

# Create initial random data.
np.random.seed(0)
df1 = pd.DataFrame(np.random.randn(5, 3), columns=list('ABx'))
df2 = pd.DataFrame(np.random.randn(5, 3), columns=list('ABy'))
df = df1.merge(df2, how='outer', suffixes=['', '_x'], left_index=True, right_index=True)


col_order = []
common_columns = df1.columns & df2.columns
for c in common_columns:
    col_order.append(c)
    col_order.append(c + '_x')
# Add non-common columns to right side of dataframe.
col_order.extend([c for c in df if c not in common_columns and not c.endswith('_x')])
>>> df[col_order]
          A       A_x         B       B_x         x         y
0  1.764052  0.333674  0.400157  1.494079  0.978738 -0.205158
1  2.240893  0.313068  1.867558 -0.854096 -0.977278 -2.552990
2  0.950088  0.653619 -0.151357  0.864436 -0.103219 -0.742165
3  0.410599  2.269755  0.144044 -1.454366  1.454274  0.045759
4  0.761038 -0.187184  0.121675  1.532779  0.443863  1.469359