[Python] Pandas multi-column multi-assignment merge / update

I want to update the base pandas DataFrame with data from the update DataFrame. This applies to multiple rows of the base DataFrame and I cannot use the index matching techniques of pd.join() or pd.merge() because I want to overwrite the columns. Using df.loc[] and df.update() doesn't work because usind ['id','date'] as an index of base is non unique.

Previous solutions end up with null values or using list comprehensions to create bool lists for indexing. There must be a better way!

Note, I often do this where base has up to 20M rows and update has up to 5K rows, and often the matching is on 3-5 columns. Using list comps can be sloooow unless I do laborious jiggery pokery of multiple separate list comps for each conditional column

base = pd.DataFrame({
'grp':['A','A','B','B','A','B','C'],
'id': ['a','b','a','b','a','a','c'],
'date': ['2025-01-01']*4+['2025-01-02']*3,
'cat1': [0]*7,
'cat2': [0]*7,
})
base:
grp id date cat1 cat2
0 A a 2025-01-01 0 0
1 A b 2025-01-01 0 0
2 B a 2025-01-01 0 0
3 B b 2025-01-01 0 0
4 A a 2025-01-02 0 0
5 B a 2025-01-02 0 0
6 C c 2025-01-02 0 0

update = pd.DataFrame({
'date': ['2025-01-01', '2025-01-01', '2025-01-02', '2025-01-02'],
'id': ['a', 'b', 'a', 'b'],
'cat1': [1, 0, 1, 0],
'cat2': [0, 1, 1, 1],
})
update:
date id cat1 cat2
0 2025-01-01 a 1 0
1 2025-01-01 b 0 1
2 2025-01-02 a 1 1
3 2025-01-02 b 0 1

desired_output = pd.DataFrame({
'grp':['A','A','B','B','A','B','C'],
'id': ['a','b','a','b','a','a','c'],
'date': ['2025-01-01']*4+['2025-01-02']*3,
'cat1': [1,0,1,0,1,1,0],
'cat2': [0,1,0,1,1,1,0],
})
desired_output:
grp id date cat1 cat2
0 A a 2025-01-01 1 0
1 A b 2025-01-01 0 1
2 B a 2025-01-01 1 0
3 B b 2025-01-01 0 1
4 A a 2025-01-02 1 1
5 B a 2025-01-02 1 1
6 C c 2025-01-02 0 0

Continue reading...