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.
CodePudding user response:
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