CODEWITHSUNDEEP
pythonpandas
cmp
cmp

Reputation: 568

Merge two dataframes with overlapping index, keeping column values from left DataFrame

How can I join/merge two Pandas DataFrames with partially overlapping indexes, where I wish the resulting joined DataFrame to retain the column values in the first DataFrame i.e. dropping the duplicates in df2?

import pandas as pd
import io  
    
df1 = """
date; count
'2020-01-01'; 210
'2020-01-02'; 189
'2020-01-03'; 612
'2020-01-04'; 492
'2020-01-05'; 185
'2020-01-06'; 492
'2020-01-07'; 155
'2020-01-08'; 62
'2020-01-09'; 15
"""
df2 = """
date; count
'2020-01-04'; 21
'2020-01-05'; 516
'2020-01-06'; 121
'2020-01-07'; 116
'2020-01-08'; 82
'2020-01-09'; 121
'2020-01-10'; 116
'2020-01-11'; 82
'2020-01-12'; 116
'2020-01-13'; 82
"""


df1 = pd.read_csv(io.StringIO(df1), sep=";")
df2 = pd.read_csv(io.StringIO(df2), sep=";")
print(df1)
print(df2)

I have tried using

df1.reset_index().merge(df2, how='outer').set_index('date')

however, this drops the joined df2 values. Is there a method to keep the duplicated rows of the first dataframe?

Desired outcome:

print(df3)
 date        count
'2020-01-01' 210
'2020-01-02' 189
'2020-01-03' 612
'2020-01-04' 492
'2020-01-05' 185
'2020-01-06' 492
'2020-01-07' 155
'2020-01-08' 62
'2020-01-09' 15
'2020-01-10' 116
'2020-01-11' 82
'2020-01-12' 116
'2020-01-13' 82

Any help greatly appreciated, thank you.

Upvotes: 3

Views: 1994

Answers (3)

G.G
G.G

Reputation: 765

df1.merge(df2,on='date',how="outer",suffixes=[None,'_2']).bfill(axis=1).drop(" count_2",axis=1)

output:

            date   count
0   '2020-01-01'     210
1   '2020-01-02'     189
2   '2020-01-03'     612
3   '2020-01-04'     492
4   '2020-01-05'     185
5   '2020-01-06'     492
6   '2020-01-07'     155
7   '2020-01-08'      62
8   '2020-01-09'      15
9   '2020-01-10'     116
10  '2020-01-11'      82
11  '2020-01-12'     116
12  '2020-01-13'      82

Upvotes: 0

eshirvana
eshirvana

Reputation: 24568

here is another way usingconcat and drop_duplicates:

df3=pd.concat([df1, df2]).drop_duplicates(["date"], keep="first", ignore_index=True)

output:

            date   count
0   '2020-01-01'     210
1   '2020-01-02'     189
2   '2020-01-03'     612
3   '2020-01-04'     492
4   '2020-01-05'     185
5   '2020-01-06'     492
6   '2020-01-07'     155
7   '2020-01-08'      62
8   '2020-01-09'      15
9   '2020-01-10'     116
10  '2020-01-11'      82
11  '2020-01-12'     116
12  '2020-01-13'      82

Upvotes: 0

mozway
mozway

Reputation: 260300

Use combine_first:

df3 = (df1.set_index('date')
          .combine_first(df2.set_index('date'))
          .reset_index()
      )

Output:

            date   count
0   '2020-01-01'     210
1   '2020-01-02'     189
2   '2020-01-03'     612
3   '2020-01-04'     492
4   '2020-01-05'     185
5   '2020-01-06'     492
6   '2020-01-07'     155
7   '2020-01-08'      62
8   '2020-01-09'      15
9   '2020-01-10'     116
10  '2020-01-11'      82
11  '2020-01-12'     116
12  '2020-01-13'      82

Upvotes: 2

Related Questions