1

I'd like to add to a dataframe a column stating the names of those columns in which the maximum value computed across rows in the dataframe is located.

Let's say I have this dataframe:

set.seed(123)
df <- data.frame(
  V1 = rnorm(10),
  V2 = rnorm(10),
  V3 = rnorm(10)
)

Now to create a new column that identifies the maximum value per row I use apply:

df$Max <- apply(df[, 1:3], 1, max, na.rm = TRUE)

This works nicely:

df
            V1         V2         V3       Max
1  -0.56047565  1.2240818 -1.0678237 1.2240818
2  -0.23017749  0.3598138 -0.2179749 0.3598138
3   1.55870831  0.4007715 -1.0260044 1.5587083
4   0.07050839  0.1106827 -0.7288912 0.1106827
5   0.12928774 -0.5558411 -0.6250393 0.1292877
6   1.71506499  1.7869131 -1.6866933 1.7869131
7   0.46091621  0.4978505  0.8377870 0.8377870
8  -1.26506123 -1.9666172  0.1533731 0.1533731
9  -0.68685285  0.7013559 -1.1381369 0.7013559
10 -0.44566197 -0.4727914  1.2538149 1.2538149

Now comes the hard part: I'd like to add another column naming the column in which the maximum value is located. What I've tried so far is this extended applystatement:

df$Location <- apply(df[, 1:3], 1, function(x) names(x[match(df[,4], x)]))

It does seem to capture the names but it scatters them across a large number of additional columns:

df
            V1         V2         V3       Max Location.1 Location.2 Location.3 Location.4 Location.5
1  -0.56047565  1.2240818 -1.0678237 1.2240818         V2       <NA>       <NA>       <NA>       <NA>
2  -0.23017749  0.3598138 -0.2179749 0.3598138       <NA>         V2       <NA>       <NA>       <NA>
3   1.55870831  0.4007715 -1.0260044 1.5587083       <NA>       <NA>         V1       <NA>       <NA>
4   0.07050839  0.1106827 -0.7288912 0.1106827       <NA>       <NA>       <NA>         V2       <NA>
5   0.12928774 -0.5558411 -0.6250393 0.1292877       <NA>       <NA>       <NA>       <NA>         V1
6   1.71506499  1.7869131 -1.6866933 1.7869131       <NA>       <NA>       <NA>       <NA>       <NA>
7   0.46091621  0.4978505  0.8377870 0.8377870       <NA>       <NA>       <NA>       <NA>       <NA>
8  -1.26506123 -1.9666172  0.1533731 0.1533731       <NA>       <NA>       <NA>       <NA>       <NA>
9  -0.68685285  0.7013559 -1.1381369 0.7013559       <NA>       <NA>       <NA>       <NA>       <NA>
10 -0.44566197 -0.4727914  1.2538149 1.2538149       <NA>       <NA>       <NA>       <NA>       <NA>
   Location.6 Location.7 Location.8 Location.9 Location.10
1        <NA>       <NA>       <NA>       <NA>        <NA>
2        <NA>       <NA>       <NA>       <NA>        <NA>
3        <NA>       <NA>       <NA>       <NA>        <NA>
4        <NA>       <NA>       <NA>       <NA>        <NA>
5        <NA>       <NA>       <NA>       <NA>        <NA>
6          V2       <NA>       <NA>       <NA>        <NA>
7        <NA>         V3       <NA>       <NA>        <NA>
8        <NA>       <NA>         V3       <NA>        <NA>
9        <NA>       <NA>       <NA>         V2        <NA>
10       <NA>       <NA>       <NA>       <NA>          V3

How can the names be collected neatly in a single column df$Location?

Chris Ruehlemann
  • 20,321
  • 4
  • 12
  • 34
  • 2
    Do you need `names(df)[max.col(df)]`? – markus Mar 30 '20 at 18:32
  • 1
    have you tried this : https://stackoverflow.com/questions/51613634/r-how-to-retrieve-a-column-name-of-a-data-frame https://stackoverflow.com/questions/17735859/for-each-row-return-the-column-name-of-the-largest-value – Yoan B. M.Sc Mar 30 '20 at 18:40

1 Answers1

1

We can use max.col

df$Location <- names(df)[1:3][max.col(df[1:3], 'first')]

If we want to use apply

 apply(df[1:3], 1, function(x) names(x)[which.max(x)])

Or if it needs to match the 'max' column, use the ==, and subset the names

apply(df, 1, function(x) names(x)[1:3][x[-length(x)] == x[length(x)]])

In the OPs' code, it is looping with rows only the first 3 columns, while the match is done on the entire 4th column for each row

  apply(df[, 1:3], 1, function(x) match(x, df[,4]))

and as there are 3 elements, it will result in 3 rows. In the OP's code, the arguments are reversed, so, the length would be equal to nrow for each row

akrun
  • 874,273
  • 37
  • 540
  • 662