I’m trying to merge two dataframes in Pandas based on a common column, ID. After merging, I want to update a column, Target Value, in my original dataframe (df) using a column from the second dataframe (reference). However, not all rows are being updated as expected, even though the ID values are exact match.
For example, it would update only 4 out of 10 rows I have in my dataframe.

This is my original dataframe (df):

        ID  Other Column  Target Value
0  ID00123       Value A          NaN
1  ID00456       Value B          NaN
2  ID00789       Value C          NaN
3  ID01012       Value D          NaN
4  ID01345       Value E          NaN

This is my reference dataframe (reference):

        ID  Reference Value
0  ID00123         RefVal1
1  ID00456         RefVal2
2  ID00789         RefVal3
3  ID01012         RefVal4
4  ID01345         RefVal5

This is my last attempt of merging two dataframes and updating the original dataframe:

# Merge dataframes on 'ID'
merged = df.merge(reference, on='ID', how='left', indicator=True)

# Create a mask for rows to update
mask = (
    (merged['_merge'] == 'both') &  # Exists in both DataFrames
    (df['Target Value'].isna())    # 'Target Value' is NaN
)

# Update 'Target Value' in df
df.loc[mask, 'Target Value'] = merged.loc[mask, 'Reference Value']

This is how merged dataframe looks like:

        ID  Other Column  Target Value Reference Value _merge
0  ID00123       Value A          NaN         RefVal1   both
1  ID00456       Value B          NaN         RefVal2   both
2  ID00789       Value C          NaN         RefVal3   both
3  ID01012       Value D          NaN         RefVal4   both
4  ID01345       Value E          NaN         RefVal5   both

And this is how my original dataframe (df) looks like after ‘updating’ it:

        ID  Other Column  Target Value
0  ID00123       Value A       RefVal1
1  ID00456       Value B       RefVal2
2  ID00789       Value C          NaN  # This row remains unchanged
3  ID01012       Value D          NaN  # This row remains unchanged
4  ID01345       Value E          NaN  # This row remains unchanged

I have tried inner join as well, still only 4 out of 10 Target Values would be updated.

I have checked datatypes of columns, both were Object.

Stripped leading/trailing whitespace and converted all values to lowercase:

df['ID'] = df['ID'].str.strip().str.lower()
reference['ID'] = reference['ID'].str.strip().str.lower()

Checked for unmatched values:

unmatched_df = set(df['ID']).difference(reference['ID'])
unmatched_reference = set(reference['ID']).difference(df['ID'])
print(unmatched_df, unmatched_reference)  # Both sets are empty

Furthermore, there are no duplicate rows: there are literally only 10 rows in the entire dataframe.

This is not the first time I encountered this issue, but I have managed to work around it. The same issues. It matches everything – merge table gives me all matched rows, however, it updates only part of the matched rows. (For some reason, once it was not working on the deduplicated table, but it worked fine on the table with duplicated – and I am talking about table with over 50k records).

Thank you for help!