Combine pandas dataframes eliminating common columns with python

Question

I have 3 dataframes:

df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],\
                    'B': ['B0', 'B1', 'B2', 'B3'],\
                    'C': ['C0', 'C1', 'C2', 'C3'],\
                    'D': ['D0', 'D1', 'D2', 'D3']},\
                    index=[0,1,2,3])

df2 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],\
                    'E': ['E0', 'E1', 'E2', 'E3']},\
                    index=[0,1,2,3])

df3 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],\
                    'F': ['F0', 'F1', 'F2', 'F3']},\
                    index=[0,1,2,3])

I want to combine them together to get the following results:

    A   B   C   D   E   F
0  A0  B0  C0  D0  E0  F0
1  A1  B1  C1  D1  E1  F1
2  A2  B2  C2  D2  E2  F2
3  A3  B3  C3  D3  E3  F3

When I try to combine them, I keep getting:

    A   B   C   D   A   E   A   F
0  A0  B0  C0  D0  A0  E0  A0  F0
1  A1  B1  C1  D1  A1  E1  A1  F1
2  A2  B2  C2  D2  A2  E2  A2  F2
3  A3  B3  C3  D3  A3  E3  A3  F3

The common column (A) is duplicated once for each dataframe used in the concat call. I have tried various combinations on:

df4 = pd.concat([df1, df2, df3], axis=1, sort=False)

Some variations have been disastrous while some keep giving the undesired result. Any suggestions would be much appreciated. Thanks.

you will have to fix this post-concatenation. https://stackoverflow.com/a/54300430/4909087 — cs95, Jun 13 '19 at 02:22
@QuangHoang Er, I didn't see that it was only A being duplicated. My bad. — cs95, Jun 13 '19 at 02:28

score 1 · Accepted Answer · answered Jun 13 '19 at 02:23

1

Try

df4 = (pd.concat((df.set_index('A') for df in (df1,df2,df3)), axis=1)
         .reset_index()
      )

Output:

    A   B   C   D   E   F
0  A0  B0  C0  D0  E0  F0
1  A1  B1  C1  D1  E1  F1
2  A2  B2  C2  D2  E2  F2
3  A3  B3  C3  D3  E3  F3

answered Jun 13 '19 at 02:23

Quang Hoang

146,074
10
56
74

Combine pandas dataframes eliminating common columns with python

1 Answers1