I have a Pandas dataframe with some values and a Series with corresponding maximum allowed values.
Next, I check dataframe and see, that some values are greater maximum allowed values and I need to replace them with values from Series.

How can I do this maximum Python and Pandas way?

Thank you!

Example code:

import pandas as pd

max_vals = pd.Series([3, 4, 5, 6, 7], index=['a', 'b', 'c', 'd', 'e'])
data = pd.DataFrame([[1,2,7,4,5],
                     [2,3,4,5,6],
                     [7,8,5,10,11]], 
                     columns=max_vals.index)

mask = data > max_vals
data[mask]

    a   b   c   d   e
0   NaN NaN 7.0 NaN NaN
1   NaN NaN NaN NaN NaN
2   7.0 8.0 NaN 10.0 11.0

# How can I do something like this?

data[mask] = max_vals[mask]

I have a Pandas dataframe with some values and a Series with corresponding maximum allowed values.
Next, I check dataframe and see, that some values are greater maximum allowed values and I need to replace them with values from Series.

How can I do this maximum Python and Pandas way?

Thank you!

Example code:

import pandas as pd

max_vals = pd.Series([3, 4, 5, 6, 7], index=['a', 'b', 'c', 'd', 'e'])
data = pd.DataFrame([[1,2,7,4,5],
                     [2,3,4,5,6],
                     [7,8,5,10,11]], 
                     columns=max_vals.index)

mask = data > max_vals
data[mask]

    a   b   c   d   e
0   NaN NaN 7.0 NaN NaN
1   NaN NaN NaN NaN NaN
2   7.0 8.0 NaN 10.0 11.0

# How can I do something like this?

data[mask] = max_vals[mask]

• python
• pandas
• dataframe

Share Follow asked Mar 4 at 8:37 Alexander MoshninAlexander Moshnin 2122 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

If need replace values less like maximum by Series use DataFrame.where or DataFrame.clip:

mask = data > max_vals
out  = data.where(mask, max_vals, axis=1)

---

out = data.clip(lower=max_vals, axis=1)
print (out)
   a  b  c   d   e
0  3  4  7   6   7
1  3  4  5   6   7
2  7  8  5  10  11

If need replace maximum use DataFrame.mask or clip:

mask = data > max_vals
out  = data.mask(mask, max_vals, axis=1)

---

out = data.clip(upper=max_vals, axis=1)
print (out)
   a  b  c  d  e
0  1  2  5  4  5
1  2  3  4  5  6
2  3  4  5  6  7