pandas: How to merge multiple dataframes with same column names on one column?

Question

I have N dataframes:

df1:
time  data
1.0   a1
2.0   b1
3.0   c1


df2:
time  data
1.0   a2
2.0   b2
3.0   c2



df3:
time  data
1.0   a3
2.0   b3
3.0   c3

I want to merge all of them on id, thus getting

time  data1    data2    data3
1.0   a1       a2       a3
2.0   b1       b2       b3
3.0   c1       c2       c3

I can assure all the ids are the same in all dataframes.

How can I do this in pandas?

Answer 1

One idea is use concat for list of DataFrames - only necessary create index by id for each DaatFrame. Also for avoid duplicated columns names is added keys parameter, but it create MultiIndex in output. So added map with format for flatten it:

dfs = [df1, df2, df3]
dfs = [x.set_index('id') for x in dfs]

df = pd.concat(dfs, axis=1, keys=range(1, len(dfs) + 1))
df.columns = df.columns.map('{0[1]}{0[0]}'.format)
df = df.reset_index()
print (df)
   id data1 data2 data3
0   1    a1    a2    a3
1   2    b1    b2    b3
2   3    c1    c2    c3