Concatenating DataFrames with similar and disjoint columns

Question

Problem is simplified for ease of discussion.

Take 3 dataframes with similar and disjoint columns, but the same column values. How does one concatenate them in such a way that there are no repeated columns, all unique columns are retained (i.e. not doing an inner join), and new rows are not created if column values are the same?

Individual dataframes:

df1:

    a  b  c
0   1  2  3
1  11 22 33

df2:

    b  c  d
0   2  3  4
1  22 33 44

df3:

    c  d  e
0   3  4  5
1  33 44 55

Desired output:

    a   b   c   d   e
0   1   2   3   4   5
1  11  22  33  44  55

However, simply using

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

includes duplicate columns.

cs95 cs95 · Accepted Answer · 2017-12-18T15:16:24

Option 1
Using concat + groupby -

pd.concat([df1, df2, df3], 1).groupby(axis=1, level=0).first()

    a   b   c   d   e
0   1   2   3   4   5
1  11  22  33  44  55

Option 2
merge -

df1.merge(df2).merge(df3)

    a   b   c   d   e
0   1   2   3   4   5
1  11  22  33  44  55

In general, for n dataframes, if you have a list of them, you can perform an n-way merge with a loop -

df_list = [df1, df2, df3]

df = df_list[0]

for d in df_list[1:]:
    df = df.merge(d)

df
    a   b   c   d   e
0   1   2   3   4   5
1  11  22  33  44  55

Concatenating DataFrames with similar and disjoint columns

1 Answers