How do you replace numbers with np.nan in selected columns if the number falls in between 2 ranges?
| A | B | C | D |
|---|---|---|---|
| 2 | 3 | 5 | 7 |
| 2 | 8 | 9 | 7 |
| 5 | 3 | 6 | 7 |
select columns B & C replace numbers if number is <=5 and >=7
| A | B | C | D |
|---|---|---|---|
| 2 | NaN | 5 | 7 |
| 2 | NaN | NaN | 7 |
| 5 | NaN | 6 | 7 |
-
I'm assuming this is a pandas question and you are dealing with DataFrames?Niko Fohr– Niko Fohr2022-11-29 08:00:58 +00:00Commented Nov 29, 2022 at 8:00
-
yes that's correctHal– Hal2022-11-29 08:01:30 +00:00Commented Nov 29, 2022 at 8:01
-
I also assume, you want to replace values strictly lower than 5 (given the provided output)mozway– mozway2022-11-29 08:05:26 +00:00Commented Nov 29, 2022 at 8:05
-
lower or equal to 5 and bigger or equal to 7 for column b & c onlyHal– Hal2022-11-29 08:06:11 +00:00Commented Nov 29, 2022 at 8:06
Add a comment
|
2 Answers
Use a boolean mask for in place modification (boolean indexing):
cols = ['B', 'C']
m = (df[cols].gt(7)|df[cols].lt(5)).reindex(columns=df.columns, fill_value=False)
df[m] = np.nan
If you need a copy:
cols = ['B', 'C']
out = df.mask((df[cols].gt(7)|df[cols].lt(5))
.reindex(columns=df.columns, fill_value=False))
Output:
A B C D
0 2 NaN 5.0 7
1 2 NaN NaN 7
2 5 NaN 6.0 7
Intermediates:
(df[cols].gt(7)|df[cols].lt(5))
B C
0 True False
1 True True
2 True False
(df[cols].gt(7)|df[cols].lt(5)).reindex(columns=df.columns, fill_value=False)
A B C D
0 False True False False
1 False True True False
2 False True False False
1 Comment
Niko Fohr
I'm assuming you meant to add
cols = ['B', 'C']You can assign back to filtered columns with DataFrame.mask:
cols = ['B', 'C']
df[cols] = df[cols].mask(df[cols].gt(7) | df[cols].lt(5))
print (df)
A B C D
0 2 NaN 5.0 7
1 2 NaN NaN 7
2 5 NaN 6.0 7
Or with numpy.where:
cols = ['B', 'C']
df[cols] = np.where(df[cols].gt(7) | df[cols].lt(5), np.nan, df[cols])
print (df)
A B C D
0 2 NaN 5.0 7
1 2 NaN NaN 7
2 5 NaN 6.0 7
Comments
