Replace maximum value of each row with mean

Question

I want to replace maximum value for each row in the column with mean value for this row. the method i am using taking a lot of time for complete. i am using pandas DataFrame. replaced mean value need to be an integer but with correct flood division.example: if value is 3.2 then 3 or if value is 3.8 then 4.

my slow solution:

for j in range(0,len(df_train)):
    val = df_train.iloc[j,1:51].mean()
    m = df_train.iloc[j,1:51].max()
    df_train.iloc[j,1:51] = df_train.iloc[j,1:51].replace(m,int(val))

My DataFrame:

id	feature0	feature1	feature2	feature3	feature4
0	0	0	3	1	5
1	4	0	4	0	8
2	1	21	4	0	0
3	0	11	0	0	2

Output i want:

id	feature0	feature1	feature2	feature3	feature4
0	0	0	3	1	`2`
1	4	0	4	0	`3`
2	1	`5`	4	0	0
3	0	`3`	0	0	2

score 3 · Accepted Answer · answered May 05 '21 at 18:06

3

df.values[range(len(df.index)), np.argmax(df.values, axis=1)] = df.mean(axis=1).round()

np.argmax over the rows tells us position of each maximum value per row. Then we use fancy indexing into df.values and assign the mean values over the rows (axis=1) but rounded.

to get

    feature0  feature1  feature2  feature3  feature4
id
0          0         0         3         1         2
1          4         0         4         0         3
2          1         5         4         0         0
3          0         3         0         0         2

answered May 05 '21 at 18:06

Mustafa Aydın

17,645
4
15
38

It giving me error. of `'>' not supported between instance of 'str' and 'int'` – Sudhanshu May 05 '21 at 18:13
@Niro Then before doing above, can you do `df = df.set_index("id").astype(int)`? This will keep `id` column aside and make sure the values are integers. – Mustafa Aydın May 05 '21 at 18:21
Although this method solved the problem but my id columns filled with `nan`. – Sudhanshu May 05 '21 at 18:40
@Niro You did use `df = df.set_index("id").astype(int)`, right? – Mustafa Aydın May 05 '21 at 18:42
@Niro Then you have more than 1 `id` column in the data? – Mustafa Aydın May 05 '21 at 18:44
@Niro, then you can do `df.index = range(len(df.index))` at the end to get back the index.. – Mustafa Aydın May 05 '21 at 18:47
@Niro and it didn't reset? – Mustafa Aydın May 05 '21 at 18:49
@Niro maybe you can restart kernel `:)` – Mustafa Aydın May 05 '21 at 18:49
sorry it was my bad. your solution was fine all along. – Sudhanshu May 05 '21 at 18:54
1

huh, i didn't realize you could assign into `df.values`, interesting – tdy May 05 '21 at 20:00
1

@tdy do you happen to know if there is a way to do it over `df` itself? There is `df.lookup` for this kind of indexing I believe, but its result is not assignable.. – Mustafa Aydın May 05 '21 at 20:06
1

@MustafaAydın only way i know is to melt/groupby/pivot. it's much more verbose than this `.values` method, but i posted the code just in case anyone's interested i guess – tdy May 05 '21 at 21:05
1

@MustafaAydın Just in case you're still interested, I realized it actually is possible to do this over `df` itself via `df.mask`. – tdy Feb 08 '22 at 05:25

tdy · Answer 2 · 2022-01-03T12:43:45.223

Do you happen to know if there is a way to do it over df itself (instead of the df.values numpy array)?

Use DataFrame.mask:

df = df.mask(
    df.eq(df.max(axis=1), axis=0), # the mask (True locations will get replaced)
    df.mean(axis=1).round(),       # the replacements
    axis=0)                        # replace by rows (each replacement value corresponds to one mask row)

#    feature0  feature1  feature2  feature3  feature4
# 0         0         0         3         1         2
# 1         4         0         4         0         3
# 2         1         5         4         0         0
# 3         0         3         0         0         2

Advantages of DataFrame.mask:

can handle ties (whereas the numpy approach will only replace the first one if tied)
can chain with other methods (whereas the numpy approach forces you to modify in place)

For reference, the boolean mask:

df.eq(df.max(axis=1), axis=0)

#    feature0  feature1  feature2  feature3  feature4
# 0     False     False     False     False      True
# 1     False     False     False     False      True
# 2     False      True     False     False     False
# 3     False      True     False     False     False

Note: To replace the column max by column mean, just swap all the axis params:

df.mask(
    df.eq(df.max(axis=0), axis=1),
    df.mean(axis=0).round(),
    axis=1)

#    feature0  feature1  feature2  feature3  feature4
# 0         0         0         3         1         5
# 1         1         0         4         0         4
# 2         1         8         4         0         0
# 3         0        11         0         0         2

@Sudhanshu FYI it turns out pandas has a built-in way of handling this via `df.mask` (answer updated). This is much more idiomatic and also doesn't reorder the columns anymore. — tdy, Feb 08 '22 at 05:27

Replace maximum value of each row with mean

2 Answers2

Linked