Filter a cell Dataframe by cell based on a dynamic threshold

user_16975281 0
Viewed 55

I hope you are doing very well and have a good end of the year. First of all, excuse me for my English as I am not a native speaker.

My problem is that having a Dataframe on python (for example 30 row and 6 columns), I try to filter cell by cell based on the average of the values on each row (as example: if the value of the it is lower than the average of its row, I keep it otherwise I replace it with 0), what makes it difficult for me is that the threshold is dynamic, unfortunately I cannot apply the applymap method which I used in other cases.

    Data = {
    '2021' : [12, 12, 14],
    '2022' : [10, 20, 25],
    '2023' : [100, 10, 35]}
df = pd.DataFrame.from_dict(Data, orient='index')
df['mean'] = df.mean(axis=1)

for that case I want to replace 14 for the first row, 20 and 25 for he second and 100 for the last one, because they are higher than the average of the values of their rows.

pandas dataframe python
1 Answers

If need replace values by DataFrame.mean use DataFrame.clip:

df1 = df.clip(upper=df.mean(axis=1), axis=0)
print (df1)
              0          1          2
2021  12.000000  12.000000  12.666667
2022  10.000000  18.333333  18.333333
2023  48.333333  10.000000  35.000000

If need replace by 0 use DataFrame.mask:

df2 = df.mask(df.gt(df.mean(axis=1), axis=0), 0)
print (df2)
       0   1   2
2021  12  12   0
2022  10   0   0
2023   0  10  35
user_29010020
Related