Mapping negative values in a second dataframe, keep the coordinates and replace first dataframe-CodePudding

I have the following dataframes:

df = pd.DataFrame([[1, 5, 1], [2, 6, 2], [3, 7, 3], [4, 8, 4]])
df2 = pd.DataFrame([[1, 5, 10], [3, 6, 2], [1, 9, 3], [4, 8, 4]])

I need to check the subtraction of them (df - df2):

df_sub = pd.DataFrame([[0, 0, -9], [-1, 0, 0], [2, -2, 0], [0, 0, 0]])

When there are negative values, I need to keep their coordinates and substitute for 0 in the first dataframe:

df = pd.DataFrame([[1, 5, 0], [0, 6, 2], [3, 0, 3], [4, 8, 4]])

I thought of a function like this:

def find_negative(df):
  coordinates = []
  for column in df.columns:
    for index in df.index:
      if df.loc[index, column] < 0:
        coordinates.append((index, column))
  return coordinates

But how can I apply it to the first dataframe?

CodePudding user response：

You can subtract df2 from df and use df.where:

new_df = df.where(df - df2 >= 0, 0)

Output:

>>> new_df 
   0  1  2
0  1  5  0
1  0  6  2
2  3  0  3
3  4  8  4

>>> new_df.to_numpy().tolist()
[[1, 5, 0], [0, 6, 2], [3, 0, 3], [4, 8, 4]]

CodePudding user response：

For dataframes:

# mask
mask = df < df2

# update df
df[mask] = 0

# coordinate:
row_idx, col_idx = np.where(mask)
rows = df.index[row_idx]
cols = df.columns[col_idx]

CodePudding user response：

df = np.array([[1, 5, 1], [2, 6, 2], [3, 7, 3], [4, 8, 4]])
df2 = np.array([[1, 5, 10], [3, 6, 2], [1, 9, 3], [4, 8, 4]])

df = np.where(df-df2 < 0, 0, df)
df # array([[1, 5, 0],[0, 6, 2],[3, 0, 3],[4, 8, 4]],dtype=int64)

Works for pd.DataFrame as well

CodePudding user response：

Can use fillna

df[df_sub >= 0].fillna(0)

CodePudding user response：

Alternative using mask:

>>> df.mask(df < df2, other=0)
   0  1  2
0  1  5  0
1  0  6  2
2  3  0  3
3  4  8  4