CODEWITHSUNDEEP
pythonpandasdataframemerge
tibi3000
tibi3000

Reputation: 618

pandas: updating column values when merging DataFrames

Is there an elegant way to merge the values of two columns with identical names in two different dataframes by updating the values in the first dataframe with the values of the column in the second dataframe if values are available (i.e. not null) in the second dataframe?

So if the two dataframes are:

df1 = pd.DataFrame([ 1,    2,    None, 3], columns=["a"], index=[0,1,2,3])
df2 = pd.DataFrame([ None, None, 4,    5], columns=["a"], index=[0,1,2,3])

then the desired output would be:

df3 = pd.DataFrame([ 1,    2,    4,    5], columns=["a"], index=[0,1,2,3])

While I can conceive of a way doing this by performing first a merge() and then using 'apply()' with a custom function to merge the values of the resulting columns together, this seems overly complex. Moreover, I need to do this for multiple columns. Is there a more elegant way to do this?

The function I am looking for would behave as if performing a left merge with 'overwrite_values=True', except for the fact that the latter parameter does not exist of course.

A slightly more complex example with two columns:

df1 = pd.DataFrame([ [1, 'a'],    [2, 'b'],    [None, None], [3, None] ], 
                   columns=["a", "b"], index=[0,1,2,3])
df2 = pd.DataFrame([ [None, None],[None, 'x'],[4, 'c'], [5, 'd'] ],
                   columns=["a", "b"], index=[0,1,2,3])

then the desired output would be:

df3 = pd.DataFrame([ [1, 'a'],    [2, 'x'],    [4, 'c'], [5, 'd']], 
                   columns=["a", "b"], index=[0,1,2,3])

Many thanks!

Upvotes: 1

Views: 2167

Answers (1)

EdChum
EdChum

Reputation: 393893

You want combine_first:

In [19]:
df1 = pd.DataFrame([ [1, 'a'],    [2, 'b'],    [None, None], [3, None] ], 
                   columns=["a", "b"], index=[0,1,2,3])
df2 = pd.DataFrame([ [None, None],[None, 'x'],[4, 'c'], [5, 'd'] ],
                   columns=["a", "b"], index=[0,1,2,3])
df1.combine_first(df2)

Out[19]:
     a  b
0  1.0  a
1  2.0  b
2  4.0  c
3  3.0  d

Upvotes: 2

Related Questions