pandas - merge and uniquely rename the columns of two data-frames with same column names

Question

I have two dataframes like the ones below:

dataframeA
bagle scom others
111   222  333
111   222  333

dataframeB
bagle scom others
444   555  666
444   555  666

I'd like to merge dataframeA & dataframeB (same columns' name) into:

dataframeA&B
 A     B     C    D    E      F
111   444   222  555  333    666
111   444   222  555  333    666

what should I do?

Answer 1

I assume you're not interested with just getting this specific result, but with a more generic solution in which you're required to:

1. Merge 2 DFs with similar column names
2. Re-order columns' position by similarity
3. Preserve the original outer order (bagle, scom, others) - the gist used here requires python >= 3.7 (in which OrderedDict key insertion order is guaranteed)
4. Rename the similar columns with some kind of rolling naming convention (here I used your A-Z convention with the obvious limitation of crossing the Z..)

Code below:

import numpy as np
import pandas as pd
from collections import OrderedDict


# create the DFs
df_1 = pd.DataFrame({'bagle': [111, 111], 'scom': [222, 222], 'others': [333, 333]})
df_2 = pd.DataFrame({'bagle': [444, 444], 'scom': [555, 555], 'others': [666, 666]})

# concat them horizontally
df_3 = pd.concat([df_1, df_2], axis=1)
columns = df_3.columns

# unique list for the builtin pandas renaming to work with similar names
unique_columns = list(OrderedDict.fromkeys(columns))

# final renaming
columns_fixed = [ chr(65 + i) for i in range(len(columns)) ]

# pandas-re-ordering columns before renaming
df_3 = df_3[unique_columns]

# the actual renaming to char-based
df_3.columns = columns_fixed
df_3

##############################
    A   B   C   D   E   F
0   111 444 222 555 333 666
1   111 444 222 555 333 666

References:

1. pandas' concat
2. SO "Renaming columns in a Pandas dataframe with duplicate column names"
3. Builtin chr function
4. SO "how-do-you-remove-duplicates-from-a-list-whilst-preserving-order"