Merge and reorder dataframes based on columns

user_7824826 0
Viewed 74

I have a two datasets like this (simplified example)

id A B C
1  1 2 3 
2  4 1 3
3  2 5 3

And

id A B C
1  2 2 2 
2  3 3 3
3  4 4 4

How can I join then such that I get

id A1 A2 B1 B2 C1 C2
1  1  2  2  2  3  2 
2  4  3  1  3  3  3
3  2  4  5  4  3  4

SO I want to keep the Id and combine the dfs with the first column of df1 as the first and the first from the second df as the 2 and so on...

pandas python
3 Answers

You can do a merge:

print (df.merge(df2, on="id", suffixes=('1', '2')).set_index("id").sort_index(axis=1).reset_index())

   id  A1  A2  B1  B2  C1  C2
0   1   1   2   2   2   3   2
1   2   4   3   1   3   3   3
2   3   2   4   5   4   3   4
user_92844230

If you have multiple dataframes in a list, you can do below with add_suffix and join

l = [df1,df2,..]
#assign suffixes on column names based on enumerator
l1 = [i.set_index("id").add_suffix(e) for e,i in enumerate(l,1)]
out = l1[0].join(l1[1:]).sort_index(axis=1).reset_index()

print(out)

   id  A1  A2  B1  B2  C1  C2
0   1   1   2   2   2   3   2
1   2   4   3   1   3   3   3
2   3   2   4   5   4   3   4
user_98406370

Assuming the id is the same for all dfs, you can create a dictionary, and concatenate:

df = [df1, df2]
df = {str(ind) : frame.set_index('id') for ind, frame in enumerate(df, 1)}
df = pd.concat(df, axis = 1).swaplevel(axis=1).sort_index(axis=1)
df.columns = df.columns.map("".join)

df
     A1  A2  B1  B2  C1  C2
id                        
1    1   2   2   2   3   2
2    4   3   1   3   3   3
3    2   4   5   4   3   4
user_71757130
Related