Replacing values in certain columns based on a dictionary of thresholds?

user_11981718 0
Viewed 57

I am trying to get from this Panda df:

   mag  ip    em   as_ppm  au_ppm
0  820  6447  99    4670      30
1  774  5827  26      35      97
2  800  9089  75    9727      25
3  584  6122  38    2911      80
4  494  7616  78    6673      67
5  742  6626  30    9424      69
6  803  2136  71    4043      73
7  682  8172  43    8806      26
8  132  1369  41    8267      34
9  680  5536  41    4431      16

Using these thresholds:

lowThresholds = {'mag':500,'ip':5000, 'em':0, 'as_ppm':0, 'au_ppm':0}
highThresholds = {'mag':1000,'ip':7500, 'em':90, 'as_ppm':8000, 'au_ppm':90}

To a matrix with the same shape with trues and falses:

   mag   ip    em    as_ppm  au_ppm
0  True  True  False True    True 
1  True  True  True  True    False
2  True  False True  False   True 
3  True  True  True  True    True 
4  True  False True  True    True 
5  True  True  True  False   True 
6  True  False True  True    True 
7  True  False True  False   True 
8  False False True  False   True 
9  True  True  True  True    True

and ideally using:

weights = {'mag':5,'ip':10, 'em':5, 'as_ppm':20, 'au_ppm':30}

end up with:

   mag    ip  em  as_ppm  au_ppm
0  5      10  5   20      30
1  5      10  5   20      0
2  5      0   5   0       30
3  5      10  5   20      30
4  5      0   5   20      30
5  5      10  5   0       30
6  5      0   5   20      30
7  5      0   5   0       30
8  0      0   5   0       30
9  5      10  5   20      30

I have found some terrible ways of doing it by creating various new dataframes, but I know it will scale terribly.

pandas python
3 Answers

Try

s=(df.lt(highThresholds) & df.gt(lowThresholds)).mul(weights)
   mag  ip  em  as_ppm  au_ppm
0    5  10   0      20      30
1    5  10   5      20       0
2    5   0   5       0      30
3    5  10   5      20      30
4    0   0   5      20      30
5    5  10   5       0      30
6    5   0   5      20      30
7    5   0   5       0      30
8    0   0   5       0      30
9    5  10   5      20      30
user_79645270

For mask chain DataFrame.lt and DataFrame.gt and then use DataFrame.where and DataFrame.mask, but I think @YOBEN_S answer should better scaling:

m = df.gt(lowThresholds) & df.lt(highThresholds)

df = df.mask(m, pd.Series(weights), axis=1).where(m, 0)
print (df)
   mag  ip  em  as_ppm  au_ppm
0    5  10   0      20      30
1    5  10   5      20       0
2    5   0   5       0      30
3    5  10   5      20      30
4    0   0   5      20      30
5    5  10   5       0      30
6    5   0   5      20      30
7    5   0   5       0      30
8    0   0   5       0      30
9    5  10   5      20      30
user_29010020 
for key in ['mag', 'ip', 'as_ppm', 'au_ppm', 'em']:
   df[key] = list(map(df[key].values, lambda x: x > lowThresholds[key] and x < highThresholds[key]
user_106560930
Related