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
Combine Multiple PDF Plots into One File
How to Calculate Total Least Squares in R? (Orthogonal Regression)
Count Common Words in Two Strings
Convert Table into Matrix by Column Names
Colors Lost in Legend When Using Scale_Shape_Manual
How to Fill Histogram with Color Gradient
Why Does Mapply Not Return Date-Objects
R Group By, Counting Non-Na Values
In R, How to Plot into a Memory Buffer Instead of a File
Fastest Way to Sort Each Row of a Large Matrix in R
Are Data Tables with More Than 2^31 Rows Supported in R with the Data Table Package Yet
3D Equivalent of the Curve Function in R
Change the Order of Stacked Fill Columns in Ggplot2
Find Most Frequent Combination of Values in a Data.Frame
Documentation for Special Variables in Ggplot (..Count.., ..Density.., etc.)
How to Pass Column Name as Argument to Function for Dplyr Verbs