Get Names of Column with Max Value for Each Row

For each row return the column name of the largest value

One option using your data (for future reference, use set.seed() to make examples using sample reproducible):

DF <- data.frame(V1=c(2,8,1),V2=c(7,3,5),V3=c(9,6,4))

colnames(DF)[apply(DF,1,which.max)]
[1] "V3" "V1" "V2"

A faster solution than using apply might be max.col:

colnames(DF)[max.col(DF,ties.method="first")]
#[1] "V3" "V1" "V2"

...where ties.method can be any of "random" "first" or "last"

This of course causes issues if you happen to have two columns which are equal to the maximum. I'm not sure what you want to do in that instance as you will have more than one result for some rows. E.g.:

DF <- data.frame(V1=c(2,8,1),V2=c(7,3,5),V3=c(7,6,4))
apply(DF,1,function(x) which(x==max(x)))

[[1]]
V2 V3
2 3

[[2]]
V1
1

[[3]]
V2
2

Find the column name which has the maximum value for each row

You can use idxmax with axis=1 to find the column with the greatest value on each row:

>>> df.idxmax(axis=1)
0 Communications
1 Business
2 Communications
3 Communications
4 Business
dtype: object

To create the new column 'Max', use df['Max'] = df.idxmax(axis=1).

To find the row index at which the maximum value occurs in each column, use df.idxmax() (or equivalently df.idxmax(axis=0)).

Finding the maximum value for each row and extract column names

You can use apply like

maxColumnNames <- apply(x,1,function(row) colnames(x)[which.max(row)])

Since you have a numeric matrix, you can't add the names as an extra column (it would become converted to a character-matrix).
You can choose a data.frame and do

resDf <- cbind(data.frame(x),data.frame(maxColumnNames = maxColumnNames))

resulting in

resDf
A B C maxColumnNames
X 1 4 7 C
Y 2 5 8 C
Z 3 6 9 C

How to use dplyr to get column with max value for each row

I think you can try max.col like below

mat_hard %>%
mutate(Class = names(.)[max.col(.)])

which gives

           V1         V2          V3         V4         V5 Class
1 2.1253660 26.4273345 13.72893486 1.7513873 6.2979783 V2
2 1.8323489 10.2414409 5.30846484 0.3347235 29.2477737 V5
3 6.1496678 5.6894422 5.45460715 4.5035747 11.6467207 V5
4 3.5326145 10.3974636 6.55605448 4.4221171 1.7139087 V2
5 21.1107027 9.9280219 0.22849661 0.2101213 1.0334978 V1
6 2.8714553 4.7813566 3.32465853 15.8878010 4.0049670 V4
7 0.6601019 14.6976125 1.37343714 13.4155430 7.5144204 V2
8 5.3986340 9.9330388 28.30681662 5.9243824 8.6695885 V3
9 7.1672128 0.1135649 0.02006355 7.4839158 27.4311080 V5
10 0.3579145 3.3261009 3.59446750 11.3528078 31.4819959 V5
11 3.5569986 1.4915687 11.81571650 12.5108163 10.5650964 V4
12 15.6411692 14.9843178 13.01627289 1.4870455 13.9162441 V1
13 4.0105209 11.6297626 14.03933859 9.1182125 16.6013583 V5
14 0.8267777 19.6671308 25.39573774 1.5730764 22.6813765 V3
15 16.0518859 7.9446867 5.52230477 6.9886905 31.3423870 V5
16 11.1804892 1.2474887 32.80866682 6.0927374 5.4666769 V3
17 1.9020065 0.8736180 0.76056537 6.2290362 22.8229062 V5
18 0.4354699 4.8834713 1.48728908 2.7705605 5.1947573 V5
19 13.9564746 0.4376033 32.46160917 33.5775243 3.6361463 V4
20 0.9488887 11.3126093 21.76888266 1.1800891 9.1619501 V3
21 0.4105029 30.8768108 6.77986834 6.4456033 3.3375528 V2
22 4.8383899 3.3213757 8.96105336 5.3539974 2.9596863 V3
23 23.5980692 0.8854953 15.77773701 17.3438544 3.6268837 V1
24 5.7302813 20.6837055 0.23557108 3.8622885 1.9313057 V2
25 23.7223308 1.6956027 1.29271191 3.6884809 3.7486600 V1
26 0.8390799 11.1018979 16.73708472 1.0896291 5.1491888 V3
27 6.4742757 15.4374730 8.76199843 0.3349979 2.2843753 V2
28 3.0712249 2.8939230 8.65244642 3.1096128 1.3245159 V3
29 8.4365271 30.2740673 30.79814652 5.8697589 1.8603535 V3
30 15.6024932 5.5718871 4.07631202 24.6346215 35.3187257 V5
31 3.7759064 1.6237925 13.80958004 7.4002858 10.5098296 V3
32 2.3559053 8.5405451 11.09127093 16.6616195 10.9618053 V4
33 21.7985378 18.3840789 1.24258382 32.7283077 1.8425573 V4
34 0.5718545 22.2466535 7.35903634 5.6994226 31.8928204 V5
35 0.8731764 11.4922204 1.36448644 0.2167550 8.1839797 V2
36 4.7162801 10.8743625 33.72675944 1.7916643 4.5028127 V3
37 13.7097611 16.1319530 0.84351757 8.1407995 5.7692484 V2
38 0.5347331 7.1313409 10.23327786 24.1837711 0.2850878 V4
39 0.3738863 12.0495186 4.61309257 6.2158783 5.7180108 V2
40 18.9056686 1.7171729 4.53560492 0.8193901 7.8306692 V1

R Create column which holds column name of maximum value for each row

You can use rowwise in dplyr and get the column names of a row that have maximum value.

library(dplyr)

df %>%
rowwise() %>%
mutate(Max = paste0(names(.)[c_across() == max(c_across())], collapse = '_'))

# V1 V2 V3 Max
# <dbl> <dbl> <dbl> <chr>
#1 2 7 7 V2_V3
#2 8 3 6 V1
#3 1 5 4 V2
#4 5 7 5 V2
#5 6 3 1 V1

In base R, you could use apply -

df$Max <- apply(df, 1, function(x) paste0(names(df)[x == max(x)],collapse = '_'))

Column name of maximum of each row in a dataframe

You can select the columns starting by Patch and then just keep the column names where the value is equal to the max:

>> s = df.iloc[:, df.columns.str.startswith('Patch')].apply(
lambda s: s.index[s.eq(s.max())].tolist(), axis=1)

>> s

0 [Patch_1, Patch_2]
1 [Patch_1, Patch_2]
2 [Patch_1, Patch_2]
3 [Patch_1, Patch_2]
4 [Patch_1, Patch_2]
5 [Patch_1, Patch_2]
6 [Patch_1, Patch_2]
7 [Patch_1, Patch_2]
8 [Patch_1, Patch_2]
9 [Patch_1, Patch_2]

Or joining as string:

>> s = s.apply(lambda s: ','.join(s))

>> s
0 Patch_1,Patch_2
1 Patch_1,Patch_2
2 Patch_1,Patch_2
3 Patch_1,Patch_2
4 Patch_1,Patch_2
5 Patch_1,Patch_2
6 Patch_1,Patch_2
7 Patch_1,Patch_2
8 Patch_1,Patch_2
9 Patch_1,Patch_2
dtype: object

Then just assign the new column:

df['Highest_Rew_patch'] = s

Update

To select other order than the max, we can use nlargest, take advantage that it remove duplicates and then select the last element:

>> n_order = 2
>> s = df.iloc[:, df.columns.str.startswith('Patch')].apply(
lambda s: s.index[s.eq(s.nlargest(n_order)[-1])].tolist(), axis=1)

>> s
0 [Patch_1, Patch_2]
1 [Patch_1, Patch_2]
2 [Patch_1, Patch_2]
3 [Patch_1, Patch_2]
4 [Patch_1, Patch_2]
5 [Patch_1, Patch_2]
6 [Patch_1, Patch_2]
7 [Patch_1, Patch_2]
8 [Patch_1, Patch_2]
9 [Patch_1, Patch_2]
dtype: object

Getting list of all columns corresponding to maximum value in each row in a Dataframe

You could try these codes (bot approaches do the same):

max_cols_df = pd.DataFrame({"max_cols": [list(df[df==mv].iloc[i].dropna().index) for i, mv in enumerate(df.max(axis=1))]})
max_cols_df = pd.DataFrame({"max_cols": [list(df.iloc[i][v].index) for i, v in df.eq(df.max(axis=1), axis=0).iterrows()]})

max_cols_df
----------------
max_cols
0 [a, b]
1 [b]
2 [a, b]
3 [a]
----------------

But I think you asked a related question to another post and cannot upgrade pandas. Therefore, the second could throw an error.

Select column names based on max value by row

You can use the function GREATEST() in a CASE expression:

SELECT id,
CASE GREATEST(cars_cat1, cars_cat2, cars_cat_3)
WHEN cars_cat1 THEN 'cars_cat1'
WHEN cars_cat2 THEN 'cars_cat2'
WHEN cars_cat3 THEN 'cars_cat3'
END max_category
FROM tablename


Related Topics



Leave a reply



Submit