BUG: DataFrame.update doesn't preserve dtypes

[aaron-robeson-8451]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

df1 = pd.DataFrame({
    'idx': [1, 2],
    'val': [True]*2,
}).set_index('idx')

df2 = pd.DataFrame({
    'idx': [1],
    'val': [True],
}).set_index('idx')

print(df1.dtypes['val'])
print(df2.dtypes['val'])

df1.update(df2)

print(df1.dtypes['val'])

Issue Description

Related to issue #4094. When calling df1.update(df2) the datatype of column val is converted to object, despite the fact that it is type bool on both df1 and df2. This occurs when the index of df1 contains values not present in the index of df2.

Expected Behavior

The expected output of the example is:

bool
bool
bool

In practice the output is:

bool
bool
object

Installed Versions

INSTALLED VERSIONS

commit : e86ed37
python : 3.10.13.final.0
python-bits : 64
OS : Darwin
OS-release : 22.6.0
Version : Darwin Kernel Version 22.6.0: Wed Jul 5 22:21:56 PDT 2023; root:xnu-8796.141.3~6/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.UTF-8

pandas : 2.1.1
numpy : 1.26.0
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 68.1.2
pip : 23.2.1
Cython : None
pytest : 7.4.2
hypothesis : None
sphinx : 7.2.6
blosc : None
feather : None
xlsxwriter : 3.1.6
lxml.etree : 4.9.3
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.5.0
pandas_datareader : None
bs4 : 4.12.2
bottleneck : None
dataframe-api-compat: None
fastparquet : None
fsspec : 2023.9.2
gcsfs : None
matplotlib : 3.7.3
numba : None
numexpr : None
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : 13.0.0
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.11.3
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

[rhshadrach]

Thanks for the report! Confirmed on main.

In the current implementation of DataFrame.update, we are reindexing the passed other, coercing its dtype. I wonder if we can instead modify the caller inplace (via loc) with an appropriate mask. Further investigations and PRs to fix are welcome!

[MichaelTiemannOSC]

This is much trickier than I thought. In the bool case the udpate result comes back as object but the final values are all bool. But in the int case the updated results come back as float64 because the nan converts the reindexed array to float in order to hold the nan. In the string case the val column df1 starts with type object.

Here's the test case I'm constructing (in pandas/tests/frame/methods/test_update.py:

    @pytest.mark.parametrize(
        "value ,dtype",
        [
            (True, bool),
            (1, int),
            (np.uint64(2), np.dtype("uint64")),
            (3.0, float),
            (4.0 + 1j, complex),
            ("a", pd.StringDtype()),
            (pd.to_timedelta("1 ms"), np.dtype("timedelta64[ns]")),
            (np.datetime64("2000-01-01T00:00:00"), np.dtype("datetime64[ns]")),
        ],
    )
    def test_update_preserve_dtype(self, value, dtype):
	# GH#55509                                                                                                                                                                                                                  
        from pandas.core.dtypes.common import is_string_dtype

        df1 = pd.DataFrame(
            {
                "idx": [1, 2],
                "val": [value] * 2,
            }
        ).set_index("idx")

        df2 = pd.DataFrame(
            {
                "idx": [1],
                "val": [value],
            }
        ).set_index("idx")

        if is_string_dtype(dtype):
            # Should we test for string, given its default behavior?                                                                                                                                                                
            df1 = df1.astype("string")
            df2 = df2.astype("string")

        assert df1.dtypes["val"] == dtype
        assert df2.dtypes["val"] == dtype

        df1.update(df2)

        assert df1.dtypes["val"] == dtype

Amy I trying too hard?

[aureliobarbosa]

Hey @MichaelTiemannOSC,

I have been studying the problem for a few days now and got a solution following the suggestions made above by @rhshadrach. As it changed the algorithm a little bit, I implemented a prototype and I still need to move it to the codebase. But it is already passing my own tests and the ones you suggested above (except for strings, which I think is more delicate as it involves checking a type and casting).

Would you mind if I take this issue and made another PR? I am open to discuss. Regards

[MichaelTiemannOSC]

Feel free. We all work together 😊

[aureliobarbosa]

take

[aureliobarbosa]

It seems that I am ready to make a PR, but I have a question about the tests.

The proposed code is passing all tests regarding the DataFrame.update() method, and it also passed a longer test similar to the one proposed above by @MichaelTiemannOSC. But I am not sure whether I should include this longer test case or should I just create an smaller one testing only for the issue proposed by @aaron-robeson-8451?

@rhshadrach

[rhshadrach]

More thorough testing, as long as it is not duplicative, is preferred.

[aureliobarbosa]

@MichaelTiemannOSC

I confirm that reindexing is changing the dtype as mentioned and that this introduces problems when using reindex inside the update method. If update is rewritten without using reindex, those problems could be avoided (once the new code passes pull all tests, which is not the case yet).

The problem with reindex changing the dtype of the data is possibly related to issue #47503, and may deserve further studies.
Here follows a minimal example code which captures this in the current context (which seems simpler than that shown in #47503):

df = pd.DataFrame({"a": [2, 2]}, index=[0, 1], dtype=np.int64)
other = pd.DataFrame({"a": [1, 1]}, index=[1, 2], dtype=np.int64)
other_reindex = other.reindex(df.index)

print(other["a"].dtype, other_reindex["a"].dtype)

[rickersilva]

Is this ever going out?

I am facing this issue but I am not using update method. I left here the code that changes an int64 field to float64

def update_dataframe(existing_df, input_df):
#df_new_records = input_df[~input_df.set_index(['addr_id', 'pers_id']).index.isin(existing_df.set_index(['addr_id', 'pers_id']).index)]
#print("SCRIPT: New records identified")

#final_df = pd.concat([existing_df, df_new_records], ignore_index=True)
#print("SCRIPT: new records appended")

# Merge dataframes to identify updates and new records
combined_df = existing_df.merge(input_df, on=['addr_id', 'pers_id'], how='outer', suffixes=('_existing', '_incoming'))
print(f"SCRIPT: dataframes merged")

# Update existing records
for col in existing_df.columns:
    if col not in ['addr_id', 'pers_id']:
        combined_df[col] = combined_df[col + '_incoming'].combine_first(combined_df[col + '_existing'])
print(f"SCRIPT: existing records, updated")

# Drop the extra columns created during merge
combined_df = combined_df[existing_df.columns]
print(f"SCRIPT: extra columns, removed")

# Separate updated existing records and new records
df_updated_existing = combined_df[combined_df[['addr_id', 'pers_id']].isin(existing_df[['addr_id', 'pers_id']].to_dict(orient='list')).all(axis=1)]
print(f"SCRIPT: updated existing records separated")
df_new_records = combined_df[~combined_df[['addr_id', 'pers_id']].isin(existing_df[['addr_id', 'pers_id']].to_dict(orient='list')).all(axis=1)]
print(f"SCRIPT: new records separated")

# Combine updated existing records with new records
df_final = pd.concat([df_updated_existing, df_new_records], ignore_index=True)
print(f"SCRIPT: records combined")

return df_final

and the scheme

SCRIPT: Existing DataFrame schema:
addr_id object
pers_id object
type_code object
street1 object
street2 object
city_county object
state_region object
zip object
country_code object
validity_range object
created_by object
created_dtime datetime64[ns]
modified_by object
modified_dtime datetime64[ns]
updated_dtime datetime64[ns]
message_dtime datetime64[ns]
index_level_0 int64