CODEWITHSUNDEEP
pythonpandasdataframe
SirC
SirC

Reputation: 2209

Filling a pandas dataframe with values from a second dataframe with some rows and columns in common

Let us suppose I have two pandas dataframes df1 and df2

df1
         s1   s2   s3
    bob  nan  nan  nan
    john nan  nan  nan
    matt nan  nan  nan

and 

df2
         s1   s3   s4
    bob  32   11   22
    matt 1   nan    2

I would to fill df1 with values from df2 rows and columns which exist in df1 so that my output is 

     s1   s2   s3
bob  32   nan   11
john nan  nan  nan
matt 1    nan  nan

This means, in this toy case, I'm not interested in column s4 ofdf2 to fill df1. All my attempt to use merge have sadly failed and I always end up with a dataframe with all nan.

Upvotes: 1

Views: 249

Answers (1)

piRSquared
piRSquared

Reputation: 294258

Inplace Operation
Use pd.DataFrame.update
This overwrites all positions in df1 where there was a non-null value in df2

df1.update(df2)

df1

        s1  s2    s3
bob   32.0 NaN  11.0
john   NaN NaN   NaN
matt   1.0 NaN   NaN

Produce a Copy 1
Use pd.DataFrame.align, pd.DataFrame.fillna, and pd.DataFrame.reindex_like
fillna won't work unless the index and columns are aligned.

pd.DataFrame.fillna(*df1.align(df2)).reindex_like(df1)

        s1  s2    s3
bob   32.0 NaN  11.0
john   NaN NaN   NaN
matt   1.0 NaN   NaN

Produce a Copy 2
pd.DataFrame.combine_first and pd.DataFrame.reindex_like
It's debatable which one you put first. Considering df1 is all nan it doesn't really matter. But this will keep any pre-existing non-null values in df1. Otherwise, you could switch the positions to df2.combine_first(df1).

df1.combine_first(df2).reindex_like(df1)

        s1  s2    s3
bob   32.0 NaN  11.0
john   NaN NaN   NaN
matt   1.0 NaN   NaN

Upvotes: 4

Related Questions