concatenate/merge multiple dataframes - pandas

Question

I have multiple data frames that I would like to concatenate. Some of these do not have certain columns so should be filled with NA.

df1_1 = pd.DataFrame({'id':[1,1,2,2,3,3], 'age':[22,22,55,55,53,53], 'group':1,'y':[1,2,3,4,5,6]})
df1_2 = pd.DataFrame({'id':[1,1,2,2,3,3], 'age':[22,22,55,55,53,53], 'group':1,'w':[7,8,9,10,11,12]})

df2 = pd.DataFrame({'id':[4,4,5,5], 'age':[39,39,54,54], 'group':2,'y':[1,2,3,4]})
df2_2 = pd.DataFrame({'id':[4,4,5,5], 'age':[39,39,54,54], 'group':2,'w':[5,6,7,8]})

df3 = pd.DataFrame({'id':[1,1,6,6,7,7,8,8], 'age':[23,23,63,63,43,43,25,25],'group':3,'w':[1,2,3,4,5,6,7,8]})

Desired output:

id  age  group  y   w
1   22      1   1   7
1   22      1   2   8
2   55      1   3   9
2   55      1   4  10
3   53      1   5  11
3   53      1   6  12
4   39      2   1   5
4   39      2   2   6
5   54      2   3   7
5   54      2   4   8
1   23      3  NA   1
1   23      3  NA   2
6   63      3  NA   3
6   63      3  NA   4
7   43      3  NA   5
7   43      3  NA   6
8   25      3  NA   7
8   25      3  NA   8

I tried

from functools import reduce
dfs = [df1_1,df1_2,df2_1,df2_2,df3]
df_merged = reduce(lambda  left,right: pd.merge(left,right,on=['id','group','age'], how='outer'), dfs)
df_merged = pd.concat(dfs, join='outer', axis=0)

but none of my attempts worked

mozway · Accepted Answer · 2023-02-10 13:10:19Z

You can try to de-duplicate the index with groupby.cumcount, then concat, and aggregate with groupby.first:

keys = ['id', 'age', 'group']

out = (pd
   .concat([x.assign(n=lambda d: d.groupby(keys).cumcount())
             .set_index(keys+['n'])
            for x in dfs])
   .groupby(level=keys+['n'], sort=False).first()
   .reset_index().drop(columns='n')  
)

Output:

    id  age  group    y     w
0    1   22      1  1.0   7.0
1    1   22      1  2.0   8.0
2    2   55      1  3.0   9.0
3    2   55      1  4.0  10.0
4    3   53      1  5.0  11.0
5    3   53      1  6.0  12.0
6    4   39      2  1.0   5.0
7    4   39      2  2.0   6.0
8    5   54      2  3.0   7.0
9    5   54      2  4.0   8.0
10   1   23      3  NaN   1.0
11   1   23      3  NaN   2.0
12   6   63      3  NaN   3.0
13   6   63      3  NaN   4.0
14   7   43      3  NaN   5.0
15   7   43      3  NaN   6.0
16   8   25      3  NaN   7.0
17   8   25      3  NaN   8.0

Collectives™ on Stack Overflow

concatenate/merge multiple dataframes - pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related