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.
CodePudding user response:
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