Python change column values - each row at a time

user_12471904 0
Viewed 48

I have a data frame df that has following columns

             A   B   C   D
06/09/2019   56 67  33  10
06/10/2019   54 66  47  23
06/11/2019   67 1   43  19
06/12/2019   21 38  8   71

I have two data frames that have a lower limit (l) and upper limit (u)

lower_df:

             l   
06/09/2019   20 
06/10/2019   12  
06/11/2019   10   
06/12/2019   15

upper_df:

             u 
06/09/2019   60
06/10/2019   70  
06/11/2019   55  
06/12/2019   50

I want to update the value of first dataframe (df) columns to 0 using the values of two dataframes (lower_df & upper_df) as condition. Here each row is isolated and every column is compared to the corresponding u & l values . The values lower than l and greater than u are set to zero.

Result needed:

             A   B   C   D
06/09/2019   56  0  33  0
06/10/2019   54 66  47  23
06/11/2019   0  0   43  19
06/12/2019   21 38   0  0

I am using following code to extract each row from dataframe and compare columns to this upper and lower limit:

for i in range(0,len(df)):
  ul=upper_df.iloc[i]
  ll=lower_df.iloc[i]
  df.iloc[i][df.iloc[i]<ll[0]]=0
  df.iloc[i][df.iloc[i]>ul[0]]=0

But this is not reflecting any changes and df remains as it is. Is the code correct ? If not what will be the correct way to do it. This dataset is huge (many columns , this is an example) and I am looking for a smarter way to do this.

pandas dataframe python-3.x numpy python
3 Answers

Use DataFrame.mask with compare by DataFrame.lt and DataFrame.gt chained by | for bitwise OR:

df = df.mask(df.lt(lower_df['l'], axis=0) | df.gt(upper_df['u'], axis=0), 0)
print (df)
             A   B   C   D
06/09/2019  56   0  33   0
06/10/2019  54  66  47  23
06/11/2019   0   0  43  19
06/12/2019  21  38   0   0
user_29010020

IIUC, here's another way:

df = df.mul((lower_df.values < df.values) & (
    df.values < upper_df.values).astype(int))

OUTPUT:

             A   B   C   D
06/09/2019  56   0  33   0
06/10/2019  54  66  47  23
06/11/2019   0   0  43  19
06/12/2019  21  38   0   0
user_154380330

Its because you aren´t saving them to the df. Aditionally, there is a neat numpy fuction np.where(), see https://numpy.org/doc/1.20/reference/generated/numpy.where.html.

for i in range(0,len(df)):
  ul=upper_df.iloc[i]
  ll=lower_df.iloc[i]
  df.loc[i] = np.where(np.logical_or(df.loc[i] <= ll, df.loc[i] >= ul), 0, df.loc[i])
user_144934350
Related