CODEWITHSUNDEEP
pandasdataframemerge
Arief Hidayat
Arief Hidayat

Reputation: 967

Merge multiple Dataframe with Different Order Columns

I have three pandas.Dataframe like this:

df1
    A_1 A_2 A_3 
MAC                                                     
ID1 1.0 1.0 2.0 
ID2 6.0 6.0 7.0 
ID3 1.0 2.0 3.0 

df2
    B_1 B_2 B_3 
MAC                                                     
ID1 1.0 1.0 1.0 
ID2 2.0 2.0 3.0 
ID3 1.0 1.0 1.0 

df3
    C_1 C_2 C_3 
MAC                                                     
ID1 1.0 1.0 2.0 
ID2 5.0 5.0 5.0 
ID3 1.0 2.0 3.0

I want to pd.merge or pd.concat with an expected result looks like this:

MAC A_1 B_1 C_1 A_2 B_2 C_2 A_3 B_3 C_3
ID1 1.0 1.0 1.0 1.0 1.0 1.0 2.0 1.0 2.0
ID2 6.0 2.0 5.0 6.0 2.0 5.0 7.0 3.0 5.0
ID3 1.0 1.0 1.0 2.0 1.0 2.0 3.0 1.0 3.0

I have searched in this link https://pandas.pydata.org/pandas-docs/stable/merging.html, but the result is not as I expected. Thank you in advance for your answer.

Upvotes: 1

Views: 664

Answers (1)

jezrael
jezrael

Reputation: 862601

I think you need concat with reindex by custom lambda function:

df = pd.concat([df1, df2, df3], axis=1) 
df = df.reindex(columns=sorted(df.columns, key=lambda x: int(x.split('_')[1])))
print (df)
     A_1  B_1  C_1  A_2  B_2  C_2  A_3  B_3  C_3
MAC                                             
ID1  1.0  1.0  1.0  1.0  1.0  1.0  2.0  1.0  2.0
ID2  6.0  2.0  5.0  6.0  2.0  5.0  7.0  3.0  5.0
ID3  1.0  1.0  1.0  2.0  1.0  2.0  3.0  1.0  3.0

Upvotes: 2

Related Questions