I am trying to update a pandas dataframe with another dataframe in python. I want to update NaN values in a column of one dataframe, when matching equal values of other column in another dataframe.
The structure and procedure looks exactly like in the minimal example below
- int64 type column (IDX), which is used as index for the join
- float64 type column (VAL), which is used for the actual update
- datetime64 type column (DAT), only in df1 which doesn't play any role in the update, but contains NaT entries
- df1 contains a NaN value in VAL which should be updated by the matching IDX entries in df2
- only NaN values shall be updated, no overwriting of non-NaN values in VAL in df1 (therefore
overwrite=False)
Minimal Example:
import numpy as np
import pandas as pd
import tabulate
#build df1
df1 = pd.DataFrame({'IDX':[1,2],'VAL':[1,np.nan],'DAT':['2021-07-28','']})
df1['IDX'] = df1['IDX'].apply(int)
df1['VAL'] = df1['VAL'].apply(float)
df1['DAT'] = pd.to_datetime(df1['DAT'], errors='coerce')
#build df2
df2 = pd.DataFrame({'IDX':[1,2],'VAL':[1.1,2.2]})
df2['IDX'] = df2['IDX'].apply(int)
df2['VAL'] = df2['VAL'].apply(float)
#check input
print(">>> INPUT")
print("*"*15, "df1", "*"*15)
print(df1.to_markdown(tablefmt="grid"))
print(df1.dtypes)
print("*"*15, "df2", "*"*15)
print(df2.to_markdown(tablefmt="grid"))
print(df2.dtypes)
# set indices for update
df1.set_index('IDX', inplace=True)
df2.set_index('IDX', inplace=True)
# apply update
df1.update(
other=df2,
join='left',
overwrite=False
)
# reset indices
df1.reset_index(inplace=True)
df2.reset_index(inplace=True)
# check result
print(">>> RESULT")
print("*"*15, "df1", "*"*15)
print(df1.to_markdown(tablefmt="grid"))
If I use overwrite=True the procedure works, but also overwrites the existing VAL value in df1
>>> INPUT
*************** df1 ***************
+----+-------+-------+---------------------+
| | IDX | VAL | DAT |
+====+=======+=======+=====================+
| 0 | 1 | 1 | 2021-07-28 00:00:00 |
+----+-------+-------+---------------------+
| 1 | 2 | nan | NaT |
+----+-------+-------+---------------------+
IDX int64
VAL float64
DAT datetime64[ns]
dtype: object
*************** df2 ***************
+----+-------+-------+
| | IDX | VAL |
+====+=======+=======+
| 0 | 1 | 1.1 |
+----+-------+-------+
| 1 | 2 | 2.2 |
+----+-------+-------+
IDX int64
VAL float64
dtype: object
>>> RESULT
*************** df1 ***************
+----+-------+-------+---------------------+
| | IDX | VAL | DAT |
+====+=======+=======+=====================+
| 0 | 1 | 1.1 | 2021-07-28 00:00:00 | <- VAL should not be overwritten
+----+-------+-------+---------------------+
| 1 | 2 | 2.2 | NaT | <- VAL updated correctly
+----+-------+-------+---------------------+
If I use desired setup with overwrite=False, the procedure fails with below error message.
---------------------------------------------------------------------------
TypeError
Traceback (most recent call last)
<ipython-input-53-55c462741178> in <module>
27 other=df2,
28 join='left',
---> 29 overwrite=False
30 )
31 # reset indices
~\.conda\envs\MyEnv\lib\site-packages\pandas\core\frame.py in update(self, other, join, overwrite, filter_func, errors)
6594
6595 In the following example, we will use ``nlargest`` to select the three
-> 6596 rows having the largest values in column "population".
6597
6598 >>> df.nlargest(3, 'population')
~\.conda\envs\MyEnv\lib\site-packages\pandas\core\computation\expressions.py in where(cond, a, b, use_numexpr)
250 a : return if cond is True
251 b : return if cond is False
--> 252 use_numexpr : bool, default True
253 Whether to try to use numexpr.
254
~\.conda\envs\MyEnv\lib\site-packages\pandas\core\computation\expressions.py in _where_standard(cond, a, b)
160 operator.or_: "|",
161 roperator.ror_: "|",
--> 162 operator.xor: "^",
163 roperator.rxor: "^",
164 divmod: None,
<__array_function__ internals> in where(*args, **kwargs)
TypeError: invalid type promotion
Checking for other posts I found a relation with the datetime64 column, containing NaT values. Indeed if I drop this column in df1 or fill it with valid dates, the procedure also works with overwrite=False. But since I am using the IDX column as joining index and only use the VAL column in df2, my expectation would be, that the DAT column in df1 is not affected by the join at all?
I am on Anaconda with
python 3.7.9 (default, Aug 31 2020, 17:10:11) [MSC v.1916 64 bit (AMD64)]
pandas 1.2.2
numpy 1.19.2