Pandas: Merge dataframe into another dataframe if entries have changed

user_6743021 0
Viewed 93

I have two Pandas dataframes (Master & Input) with the same Index but a different number of columns, e.g.

Dataframe 1 (Master):

ID  | A  | B  | C
101 | .. | .. | ..
102 | .. | .. | ..
103 | .. | .. | ..

Dataframe 2 (Input):

ID  | A  | B  | C  | E  | F 
101 | .. | .. | .. | .. | ..
102 | .. | XY | YZ | .. | ..
223 | .. | .. | .. | .. | ..
351 | .. | .. | .. | .. | ..

I want to merge the Input DF into the Master DF if new rows have been added (see ID 223, 351) OR an item in the Input DF has been updated (see ID 102).

Additional columns in the Input DF can be ignored.

Goal:

ID  | A  | B  | C
101 | .. | .. | ..
102 | .. | XY | YZ
103 | .. | .. | ..
223 | .. | .. | ..
351 | .. | .. | ..
pandas python
2 Answers

Use .update() to update already existing rows and columns.
Then I try to find out what the new rows are that are not yet in your df1 and add those using pd.concat():

import pandas as pd

df1 = pd.DataFrame(
    {'ID': [101, 102, 103],
     'A': ['..', '..', '..'],
     'B': ['..', '..', '..'],
     'C': ['..', '..', '..'],    
}).set_index('ID')

df2 = pd.DataFrame(
    {'ID': [101, 102, 223],
     'A': ['..', '..', '..'],
     'B': ['..', 'XY', '..'],
     'C': ['..', 'ZZ', '..'], 
     'F': ['..', '..', '..'],
}).set_index('ID')

# update existing rows    
df1.update(df2)

# find out which ids are new    
ids_of_new_rows = set(df2.index) - set(df1.index)

# get new rows that should be added to master   
rows_to_add = df2.loc[ids_of_new_rows, df1.columns & df2.columns]

# add new rows to existing master
df_result = pd.concat([df1, rows_to_add])

df_result
user_34891550

Assuming, that the columns in Input are actually A,B,C,D:

useless_col = set(master.columns) ^ set(input.columns)
input = input.drop(columns=useless_col)

master_only = master[~master.id.isin(input.id)]
master_overriden = input[input.id.isin(master.id)]

master_only.append(master_overriden)

I tried to find a way using join but couldn't find anything as simple as above.

Below is a demo in a shell :

>>> master
    id   A   B   C
0  101  ..  ..  ..
1  102  ..  ..  ..
2  103  ..  ..  ..
>>> input
    id   A   B   C   D
0  101  ..  ..  ..  ..
1  102  ..  XY  YZ  ..
2  223  ..  ..  ..  ..
3  251  ..  ..  ..  ..
>>> 
>>> input = input.drop(columns=set(master.columns) ^ set(input.columns))
>>> 
>>> master_only = master[~master.id.isin(input.id)]
>>> master_overriden = input[input.id.isin(master.id)]
>>> 
>>> master_only.append(master_overriden)
    id   A   B   C
2  103  ..  ..  ..
0  101  ..  ..  ..
1  102  ..  XY  YZ
>>> d = {'A': '..', 'B': '..', 'C': '..'}
>>> master = pd.DataFrame([{'id': 101, **d}, {'id': 102, **d}, {'id': 103, **d}])
>>> input = pd.DataFrame([{'id': 101, **d, 'D': '..'}, {'id': 102, 'A': '..', 'B':'XY', 'C': 'YZ', 'D': '..'}, {'id': 223, **d, 'D': '..'}, {'id':251, **d, 'D': '..'}])
>>> 
>>> master
    id   A   B   C
0  101  ..  ..  ..
1  102  ..  ..  ..
2  103  ..  ..  ..
>>> input
    id   A   B   C   D
0  101  ..  ..  ..  ..
1  102  ..  XY  YZ  ..
2  223  ..  ..  ..  ..
3  251  ..  ..  ..  ..
>>> 
>>> useless_col = set(master.columns) ^ set(input.columns)
>>> useless_col
{'D'}
>>> 
>>> input = input.drop(columns=useless_col)
>>> 
>>> master_only = master[~master.id.isin(input.id)]
>>> master_overriden = input[input.id.isin(master.id)]
>>> 
>>> master_only.append(master_overriden)
    id   A   B   C
2  103  ..  ..  ..
0  101  ..  ..  ..
1  102  ..  XY  YZ
user_145725240
Related