Cap the value of each column depending on a cap value in pandas-CodePudding

I am a newbie to Python. I am working with Python 3.6.

I have the following pandas dataframe:

import pandas as pd
data = [[1.5, 2,1.5,0.8], [1.2, 2,1.5,3], [2, 2,1.5,1]]
df = pd.DataFrame(data, columns = ['Floor', 'V1','V2','V3'])
df

Essentially, for each row, if the value in the column V1 is lower than the value of Floor, then I want to set the value of V1 equal to Floor .

The operation needs to be expanded for each row and for each column (i.e. from column V1 to column V3 where for each row there is the same Floor ).

The result would be the following in this example:

data = [[1.5, 2,1.5,1.5], [1.2, 2,1.5,3], [2, 2,2,2]]

Any idea how to achieve this? I was looking at the function where but I am not sure how to deploy it.

Many thanks in advance.

CodePudding user response：

You can use clip:

df = df.clip(lower=df['Floor'], axis=0)

output:

>>> df
      Floor  V1        V2        V3
0    1.5000   2    1.5000    1.5000
1    1.2000   2    1.5000    3.0000
2    2.0000   2    2.0000    2.0000

if you have other columns

cols = df.filter(regex='V\d ').columns
df[cols] = df[cols].clip(lower=df['Floor'], axis=0)

CodePudding user response：

I would suggest using numpy's np.where(). This allows to compare and update a column base on an if/else criteria. Kindly try:

df['V1'] = np.where(df['V1'] < df['Floor'],df['Floor'],df['V1']

Followed by the same for V2 and V3.

CodePudding user response：

Use:

df.update( df.mask(df.loc[:, 'V1':'V3'].lt(df['Floor'], axis=0), df['Floor'], axis=0))
print (df)
   Floor  V1   V2   V3
0    1.5   2  1.5  1.5
1    1.2   2  1.5  3.0
2    2.0   2  2.0  2.0