Most efficient way to combine several dataframes with complementary missing values

Question

This is probably a pretty basic question. Suppose I have two dataframes:

>>> df1
   id     a
0   0   5.0
1   1  10.0
2   2  15.0
3   3   NaN
4   4   NaN
5   5   NaN

>>> df2
   id     a
0   0   NaN
1   1   NaN
2   2   NaN
3   3  20.0
4   4  25.0
5   5  30.0

I want to join the dataframes on id to look like:

>>> df_joined
   id     a
0   0   5.0
1   1  10.0
2   2  15.0
3   3  20.0
4   4  25.0
5   5  30.0

So far, I have been doing left merges, e.g. df1.merge(df2, how = 'left', on = 'id) but this results in duplicated columns (see below) that I have to correct by filling the nans, renaming the columns, and then dropping the duplicate. This becomes particularly tedious if I have more than 2 dataframes to merge.

# How do I better merge this so I don't have fill nans, rename, and drop the columns?
   id   a_x   a_y
0   0   5.0   NaN
1   1  10.0   NaN
2   2  15.0   NaN
3   3   NaN  20.0
4   4   NaN  25.0
5   5   NaN  30.0

What's a better way to do this?

Answer 1

IIUC, DataFrame.combine_first

df1.combine_first(df2)

   id     a
0   0   5.0
1   1  10.0
2   2  15.0
3   3  20.0
4   4  25.0
5   5  30.0

EDIT

We can use DataFrame.set_index + DataFrame.combine_first with map and reduce for n DataFrames.

my_list_df = [df1, df2]

from functools import reduce
reduce(lambda new_df,  df_to_combine: new_df.combine_first(df_to_combine), 
       map(lambda df: df.set_index('id'), my_list_df)).reset_index()

Output

   id     a
0   0   5.0
1   1  10.0
2   2  15.0
3   3  20.0
4   4  25.0
5   5  30.0

In this case we only need reduce:

reduce(lambda new_df, df_to_combine: new_df.combine_first(df_to_combine.set_index('id')),
       my_list_df[1:], my_list_df[0].set_index('id')).reset_index()