Keyerror when trying to modify column values based on column from another datafrme-CodePudding

I have two pandas dataframes (df1 and df2).

df1

address     mon    tue    wed    ...
address1     40     40     40    ...
address2     20     20     20    ...
address3     30     30      0    ...
address3      0      0     30    ...
...         ...    ...    ...    ...

df2

address     mon    tue    wed    ...
address1      0     15      0    ...
address2      0      6      0    ...
address3     15      0      0    ...
...         ...    ...    ...    ...

What I want to do is when the value in a column of df1 (eg. mon) is greather than 0, if the value in df2 is also greather than 0 then replace the value of df1 by the value of df2:

df1 modified

address     mon    tue    wed    ...
address1     40     15     40    ...
address2     20      6     20    ...
address3     15     30      0    ...
address3      0      0     30    ...
...         ...    ...    ...    ...

I'm trying this code based on this:

for index, _ in df1.iterrows():
    if df1.loc[index, 'mon'] > 0:
        df1.loc[index, 'mon'] = float(
            df2.loc[(df2['address'] == df1[index, 'address']), 'mon'])

But I get a KeyError: (4, 'address')

Traceback (most recent call last):
  File "/usr/lib64/python3.8/site-packages/pandas/core/indexes/base.py", line 3361, in get_loc
    return self._engine.get_loc(casted_key)
  File "pandas/_libs/index.pyx", line 76, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/index.pyx", line 108, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/hashtable_class_helper.pxi", line 5198, in pandas._libs.hashtable.PyObjectHashTable.get_item
  File "pandas/_libs/hashtable_class_helper.pxi", line 5206, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: (4, 'address')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/mnt/project/script.py", line 78, in <module>
    df2.loc[(df2['address'] == df1[index, 'address']), 'mon'])
  File "/usr/lib64/python3.8/site-packages/pandas/core/frame.py", line 3458, in __getitem__
    indexer = self.columns.get_loc(key)
  File "/usr/lib64/python3.8/site-packages/pandas/core/indexes/base.py", line 3363, in get_loc
    raise KeyError(key) from err
KeyError: (4, 'address')

What could I be doing wrong?

Thanks in advance.

CodePudding user response：

Use mask and combine_first.

Set address column as index of both dataframes then create a boolean mask where df1 and df2 values are greater than 0. Use mask to set each cell that match condition to NaN and use combine_first to fill NaN values of df1 by values of df2.

df1 = df1.set_index('address')
df2 = df2.set_index('address').reindex(df1.index)
mask = df1.gt(0) & df2.gt(0)
df1 = df1.mask(mask).combine_first(df2).reset_index()

Output:

>>> df1
    address   mon   tue  wed
0  address1  40.0  15.0   40
1  address2  20.0   6.0   20
2  address3  15.0  30.0    0
3  address3   0.0   0.0   30