1

I'm trying to merge two pandas dataframes but I can't figure out how to get the result I need. These are the example versions of dataframes I'm looking at:

df1 = pd.DataFrame([["09/10/2019",None],["10/10/2019",None], ["11/10/2019",6],
                    ["12/10/2019",5], ["13/10/2019",3], ["14/10/2019",3],
                    ["15/10/2019",5],
                    ["16/10/2019",None]], columns = ['Date', 'A'])

df2 = pd.DataFrame([["10/10/2019",3], ["11/10/2019",5], ["12/10/2019",6],
                    ["13/10/2019",1], ["14/10/2019",2], ["15/10/2019",4]],
                    columns = ['Date', 'A'])

I have checked the Pandas merging 101 but still can't find the way to do it correctly. Essentially what I need using the same graphics as in the guide is this:

enter image description here

i.e. I want to keep the data from df1 that falls outside the shared keys section, but within shared area I want df2 data from column 'A' to overwrite data from df1. I'm not even sure that merge is the right tool to use.

I've tried using df1 = pd.merge(df1, df2, how='right', on='Date') with different options, but in most cases it creates two separate columns - A_x and A_y in the output.

This is what I want to get as the end result:

         Date    A
0  09/10/2019  NaN
1  10/10/2019  3.0
2  11/10/2019  5.0
3  12/10/2019  6.0
4  13/10/2019  1.0
5  14/10/2019  2.0
6  15/10/2019  4.0
7  16/10/2019  NaN

Thanks in advance!

NotAName
  • 3,821
  • 2
  • 29
  • 44

1 Answers1

1

here is a way using combine_first:

df2.set_index('Date').combine_first(df1.set_index('Date')).reset_index()

Or reindex_like:

df2.set_index('Date').reindex_like(df1.set_index('Date')).reset_index()

         Date    A
0  09/10/2019  NaN
1  10/10/2019  3.0
2  11/10/2019  5.0
3  12/10/2019  6.0
4  13/10/2019  1.0
5  14/10/2019  2.0
6  15/10/2019  4.0
7  16/10/2019  NaN
anky
  • 74,114
  • 11
  • 41
  • 70