If I merge on two dataframes as shown
df1=pd.DataFrame({"Name": ["Alex","Ben","Cat","Dog"],'A':[1,2,3,3],'B':[2,3,4,4]})
df2=pd.DataFrame({"Name": ["Alex","Ben","Cat","Dog"],'A':[2,3,4,4],'B':[1,2,3,3]})
df_merged = pd.merge(df1,df2, on = "Name", suffixes= ["L","R"])
this is what my merged dataframe df_merged is like
Name AL BL AR BR
0 Alex 1 1 2 2
1 Ben 2 3 3 2
2 Cat 3 4 4 3
3 Dog 3 4 4 3
But is there a way to get df_merged to look like
Name AL AR BL BR
0 Alex 1 2 1 2
1 Ben 2 3 3 2
2 Cat 3 4 4 3
3 Dog 3 4 4 3
where the columns of the dataframes are next to one other.
This is just an interest for cosmetic reasons rather than anything else. I couldn’t find an example for it anywhere.
>Solution :
You need change order of columns, here is dynamic approach:
#get all columns names in both df, removed Name used for key in merge
same = df1.columns.intersection(df2.columns, sort=False).difference(['Name'])
#generate columns in order
cols = [f'{a}{b}' for a in same for b in ['L','R']]
#get different like `cols` from merged df and add cols in order
order = df_merged.columns.difference(cols, sort=False).union(cols, sort=False)
#changed order by list
df = df_merged[order]
print (df)
Name AL AR BL BR
0 Alex 1 2 2 1
1 Ben 2 3 3 2
2 Cat 3 4 4 3
3 Dog 3 4 4 3
Manually approach:
df = df_merged[['Name', 'AL', 'AR', 'BL', 'BR']]