Find a maximum value for multiple entries in a column

Question

I have this dataset

##   X Sector Octant Distance_m Yr1951    Yr1952   Yr1953    Yr1954    Yr1955
## 1 1    B01     NW       1600      0  4.167226 15.79016  23.24586  23.24586
## 2 2    B02      N       1600      0  8.070090 30.57861  45.01705  45.01705
## 3 3    B03     NE       1600      0 19.917085 75.46840 111.10264 111.10264
## 4 4    B04     NW       3200      0  3.140495 11.89974  17.51849  17.51849
## 5 5    B05     NW       3200      0  3.140495 11.89974  17.51849  17.51849
## 6 6    B06      N       3200      0  6.081763 23.04458  33.92564  33.92564
##      Yr1956    Yr1957    Yr1958    Yr1959    Yr1960    Yr1961    Yr1962
## 1  23.24586  23.24586  24.64390  28.83804  28.83804  28.83804  28.83804
## 2  45.01705  45.01705  47.72445  55.84665  55.84665  55.84665  55.84665
## 3 111.10264 111.10264 117.78453 137.83023 137.83023 137.83023 137.83023
## 4  17.51849  17.51849  18.57208  21.73285  21.73285  21.73285  21.73285
## 5  17.51849  17.51849  18.57208  21.73285  21.73285  21.73285  21.73285
## 6  33.92564  33.92564  35.96599  42.08702  42.08702  42.08702  42.08702
##      Yr1963    Yr1964    Yr1965    Yr1966    Yr1967    Yr1968    Yr1969
## 1  28.83804  28.83804  28.83804  28.83804  28.83804  28.83804  28.83804
## 2  55.84665  55.84665  55.84665  55.84665  55.84665  55.84665  55.84665
## 3 137.83023 137.83023 137.83023 137.83023 137.83023 137.83023 137.83023
## 4  21.73285  21.73285  21.73285  21.73285  21.73285  21.73285  21.73285
## 5  21.73285  21.73285  21.73285  21.73285  21.73285  21.73285  21.73285
## 6  42.08702  42.08702  42.08702  42.08702  42.08702  42.08702  42.08702
##      Yr1970    Yr1971    Yr1972    Yr1973    Yr1974    Yr1975    Yr1976
## 1  28.83804  28.83804  28.83804  28.83804  28.83804  28.83804  28.83804
## 2  55.84665  55.84665  55.84665  55.84665  55.84665  55.84665  55.84665
## 3 137.83023 137.83023 137.83023 137.83023 137.83023 137.83023 137.83023
## 4  21.73285  21.73285  21.73285  21.73285  21.73285  21.73285  21.73285
## 5  21.73285  21.73285  21.73285  21.73285  21.73285  21.73285  21.73285
## 6  42.08702  42.08702  42.08702  42.08702  42.08702  42.08702  42.08702
##      Yr1977    Yr1978   Yr1979    Yr1980    Yr1981    Yr1982    Yr1983
## 1  28.83804  28.83804 16.64263  4.447229  4.447229  4.447229  4.447229
## 2  55.84665  55.84665 32.22949  8.612334  8.612334  8.612334  8.612334
## 3 137.83023 137.83023 79.54279 21.255349 21.255349 21.255349 21.255349
## 4  21.73285  21.73285 12.54218  3.351510  3.351510  3.351510  3.351510
## 5  21.73285  21.73285 12.54218  3.351510  3.351510  3.351510  3.351510
## 6  42.08702  42.08702 24.28871  6.490407  6.490407  6.490407  6.490407
##      Yr1984    Yr1985    Yr1986    Yr1987    Yr1988
## 1  4.447229  4.447229  4.447229  4.447229 1.1104088
## 2  8.612334  8.612334  8.612334  8.612334 2.1503755
## 3 21.255349 21.255349 21.255349 21.255349 5.3071537
## 4  3.351510  3.351510  3.351510  3.351510 0.8368237
## 5  3.351510  3.351510  3.351510  3.351510 0.8368237
## 6  6.490407  6.490407  6.490407  6.490407 1.6205609

I am wanting to separate the data out per sector and find which year for every sector had the maximum/highest exposure and have that maximum exposure displayed. How would I go about doing this? My desired output would (ideally) include the sector, the year of the highest exposure was recorded, and what that value is. Reproducible data below:

structure(list(X = 1:10, Sector = c("B01", "B02", "B03", "B04", 
"B05", "B06", "B07", "B08", "B09", "B10"), Octant = c("NW", "N", 
"NE", "NW", "NW", "N", "N", "NE", "NE", "W"), Distance_m = c(1600L, 
1600L, 1600L, 3200L, 3200L, 3200L, 3200L, 3200L, 3200L, 4800L
), Yr1951 = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), Yr1952 = c(4.16722556361885, 
8.07009038922937, 19.9170847727104, 3.14049476463692, 3.14049476463692, 
6.08176260934446, 6.08176260934446, 15.0098667568185, 15.0098667568185, 
4.65320683317187), Yr1953 = c(15.7901552659006, 30.578613590843, 
75.4684036666576, 11.8997398120925, 11.8997398120925, 23.0445831227103, 
23.0445831227103, 56.8743215341634, 56.8743215341634, 17.6316009916985
), Yr1954 = c(23.2458594045635, 45.0170464032273, 111.102637787894, 
17.5184900949112, 17.5184900949112, 33.9256410267316, 33.9256410267316, 
83.7289095546898, 83.7289095546898, 25.9567883170532), Yr1955 = c(23.2458594045635, 
45.0170464032273, 111.102637787894, 17.5184900949112, 17.5184900949112, 
33.9256410267316, 33.9256410267316, 83.7289095546898, 83.7289095546898, 
25.9567883170532), Yr1956 = c(23.2458594045635, 45.0170464032273, 
111.102637787894, 17.5184900949112, 17.5184900949112, 33.9256410267316, 
33.9256410267316, 83.7289095546898, 83.7289095546898, 25.9567883170532
), Yr1957 = c(23.2458594045635, 45.0170464032273, 111.102637787894, 
17.5184900949112, 17.5184900949112, 33.9256410267316, 33.9256410267316, 
83.7289095546898, 83.7289095546898, 25.9567883170532), Yr1958 = c(24.6439039639345, 
47.724446276369, 117.78453479105, 18.5720811598549, 18.5720811598549, 
35.9659853751658, 35.9659853751658, 88.7645051172278, 88.7645051172278, 
27.517872639807), Yr1959 = c(28.8380376420477, 55.8466458957943, 
137.830225800518, 21.7328543546861, 21.7328543546861, 42.0870184204684, 
42.0870184204684, 103.871291804842, 103.871291804842, 32.2011256080683
), Yr1960 = c(28.8380376420477, 55.8466458957943, 137.830225800518, 
21.7328543546861, 21.7328543546861, 42.0870184204684, 42.0870184204684, 
103.871291804842, 103.871291804842, 32.2011256080683), Yr1961 = c(28.8380376420477, 
55.8466458957943, 137.830225800518, 21.7328543546861, 21.7328543546861, 
42.0870184204684, 42.0870184204684, 103.871291804842, 103.871291804842, 
32.2011256080683), Yr1962 = c(28.8380376420477, 55.8466458957943, 
137.830225800518, 21.7328543546861, 21.7328543546861, 42.0870184204684, 
42.0870184204684, 103.871291804842, 103.871291804842, 32.2011256080683
), Yr1963 = c(28.8380376420477, 55.8466458957943, 137.830225800518, 
21.7328543546861, 21.7328543546861, 42.0870184204684, 42.0870184204684, 
103.871291804842, 103.871291804842, 32.2011256080683), Yr1964 = c(28.8380376420477, 
55.8466458957943, 137.830225800518, 21.7328543546861, 21.7328543546861, 
42.0870184204684, 42.0870184204684, 103.871291804842, 103.871291804842, 
32.2011256080683), Yr1965 = c(28.8380376420477, 55.8466458957943, 
137.830225800518, 21.7328543546861, 21.7328543546861, 42.0870184204684, 
42.0870184204684, 103.871291804842, 103.871291804842, 32.2011256080683
), Yr1966 = c(28.8380376420477, 55.8466458957943, 137.830225800518, 
21.7328543546861, 21.7328543546861, 42.0870184204684, 42.0870184204684, 
103.871291804842, 103.871291804842, 32.2011256080683), Yr1967 = c(28.8380376420477, 
55.8466458957943, 137.830225800518, 21.7328543546861, 21.7328543546861, 
42.0870184204684, 42.0870184204684, 103.871291804842, 103.871291804842, 
32.2011256080683), Yr1968 = c(28.8380376420477, 55.8466458957943, 
137.830225800518, 21.7328543546861, 21.7328543546861, 42.0870184204684, 
42.0870184204684, 103.871291804842, 103.871291804842, 32.2011256080683
), Yr1969 = c(28.8380376420477, 55.8466458957943, 137.830225800518, 
21.7328543546861, 21.7328543546861, 42.0870184204684, 42.0870184204684, 
103.871291804842, 103.871291804842, 32.2011256080683), Yr1970 = c(28.8380376420477, 
55.8466458957943, 137.830225800518, 21.7328543546861, 21.7328543546861, 
42.0870184204684, 42.0870184204684, 103.871291804842, 103.871291804842, 
32.2011256080683), Yr1971 = c(28.8380376420477, 55.8466458957943, 
137.830225800518, 21.7328543546861, 21.7328543546861, 42.0870184204684, 
42.0870184204684, 103.871291804842, 103.871291804842, 32.2011256080683
), Yr1972 = c(28.8380376420477, 55.8466458957943, 137.830225800518, 
21.7328543546861, 21.7328543546861, 42.0870184204684, 42.0870184204684, 
103.871291804842, 103.871291804842, 32.2011256080683), Yr1973 = c(28.8380376420477, 
55.8466458957943, 137.830225800518, 21.7328543546861, 21.7328543546861, 
42.0870184204684, 42.0870184204684, 103.871291804842, 103.871291804842, 
32.2011256080683), Yr1974 = c(28.8380376420477, 55.8466458957943, 
137.830225800518, 21.7328543546861, 21.7328543546861, 42.0870184204684, 
42.0870184204684, 103.871291804842, 103.871291804842, 32.2011256080683
), Yr1975 = c(28.8380376420477, 55.8466458957943, 137.830225800518, 
21.7328543546861, 21.7328543546861, 42.0870184204684, 42.0870184204684, 
103.871291804842, 103.871291804842, 32.2011256080683), Yr1976 = c(28.8380376420477, 
55.8466458957943, 137.830225800518, 21.7328543546861, 21.7328543546861, 
42.0870184204684, 42.0870184204684, 103.871291804842, 103.871291804842, 
32.2011256080683), Yr1977 = c(28.8380376420477, 55.8466458957943, 
137.830225800518, 21.7328543546861, 21.7328543546861, 42.0870184204684, 
42.0870184204684, 103.871291804842, 103.871291804842, 32.2011256080683
), Yr1978 = c(28.8380376420477, 55.8466458957943, 137.830225800518, 
21.7328543546861, 21.7328543546861, 42.0870184204684, 42.0870184204684, 
103.871291804842, 103.871291804842, 32.2011256080683), Yr1979 = c(16.6426332897682, 
32.2294900798683, 79.5427876444474, 12.5421823029183, 12.5421823029183, 
24.2887127940458, 24.2887127940458, 59.9448492404336, 59.9448492404336, 
18.5834948849451), Yr1980 = c(4.44722893748883, 8.61233426394242, 
21.2553494883767, 3.35151025115061, 3.35151025115061, 6.49040716762317, 
6.49040716762317, 16.0184066760251, 16.0184066760251, 4.96586416182185
), Yr1981 = c(4.44722893748883, 8.61233426394242, 21.2553494883767, 
3.35151025115061, 3.35151025115061, 6.49040716762317, 6.49040716762317, 
16.0184066760251, 16.0184066760251, 4.96586416182185), Yr1982 = c(4.44722893748883, 
8.61233426394242, 21.2553494883767, 3.35151025115061, 3.35151025115061, 
6.49040716762317, 6.49040716762317, 16.0184066760251, 16.0184066760251, 
4.96586416182185), Yr1983 = c(4.44722893748883, 8.61233426394242, 
21.2553494883767, 3.35151025115061, 3.35151025115061, 6.49040716762317, 
6.49040716762317, 16.0184066760251, 16.0184066760251, 4.96586416182185
), Yr1984 = c(4.44722893748883, 8.61233426394242, 21.2553494883767, 
3.35151025115061, 3.35151025115061, 6.49040716762317, 6.49040716762317, 
16.0184066760251, 16.0184066760251, 4.96586416182185), Yr1985 = c(4.44722893748883, 
8.61233426394242, 21.2553494883767, 3.35151025115061, 3.35151025115061, 
6.49040716762317, 6.49040716762317, 16.0184066760251, 16.0184066760251, 
4.96586416182185), Yr1986 = c(4.44722893748883, 8.61233426394242, 
21.2553494883767, 3.35151025115061, 3.35151025115061, 6.49040716762317, 
6.49040716762317, 16.0184066760251, 16.0184066760251, 4.96586416182185
), Yr1987 = c(4.44722893748883, 8.61233426394242, 21.2553494883767, 
3.35151025115061, 3.35151025115061, 6.49040716762317, 6.49040716762317, 
16.0184066760251, 16.0184066760251, 4.96586416182185), Yr1988 = c(1.1104088237638, 
2.15037545723583, 5.30715372557456, 0.836823695861693, 0.836823695861693, 
1.62056091333541, 1.62056091333541, 3.99956475497726, 3.99956475497726, 
1.23990454739508)), class = "data.frame", row.names = c(NA, -10L
))

user_2725773 · Answer

You can do:

library(tidyverse)
df %>%
  pivot_longer(cols = -c(X, Sector, Octant, Distance_m)) %>%
  group_by(Sector) %>%
  summarize(max_years = list(name[which(value == max(value))])) %>%
  unnest_wider(max_years)

Alternative:

df %>%
  rowwise() %>%
  summarize(max_years = list(names(df[4 + (which(c_across(starts_with("Yr")) == max(c_across(starts_with("Yr")))))]))) %>%
  unnest_wider(max_years)

which gives:

# A tibble: 10 x 21
   Sector ...1   ...2   ...3   ...4   ...5   ...6   ...7   ...8   ...9   ...10  ...11  ...12  ...13  ...14  ...15  ...16  ...17  ...18  ...19  ...20 
                                            
 1 B01    Yr1959 Yr1960 Yr1961 Yr1962 Yr1963 Yr1964 Yr1965 Yr1966 Yr1967 Yr1968 Yr1969 Yr1970 Yr1971 Yr1972 Yr1973 Yr1974 Yr1975 Yr1976 Yr1977 Yr1978
 2 B02    Yr1959 Yr1960 Yr1961 Yr1962 Yr1963 Yr1964 Yr1965 Yr1966 Yr1967 Yr1968 Yr1969 Yr1970 Yr1971 Yr1972 Yr1973 Yr1974 Yr1975 Yr1976 Yr1977 Yr1978
 3 B03    Yr1959 Yr1960 Yr1961 Yr1962 Yr1963 Yr1964 Yr1965 Yr1966 Yr1967 Yr1968 Yr1969 Yr1970 Yr1971 Yr1972 Yr1973 Yr1974 Yr1975 Yr1976 Yr1977 Yr1978
 4 B04    Yr1959 Yr1960 Yr1961 Yr1962 Yr1963 Yr1964 Yr1965 Yr1966 Yr1967 Yr1968 Yr1969 Yr1970 Yr1971 Yr1972 Yr1973 Yr1974 Yr1975 Yr1976 Yr1977 Yr1978
 5 B05    Yr1959 Yr1960 Yr1961 Yr1962 Yr1963 Yr1964 Yr1965 Yr1966 Yr1967 Yr1968 Yr1969 Yr1970 Yr1971 Yr1972 Yr1973 Yr1974 Yr1975 Yr1976 Yr1977 Yr1978
 6 B06    Yr1959 Yr1960 Yr1961 Yr1962 Yr1963 Yr1964 Yr1965 Yr1966 Yr1967 Yr1968 Yr1969 Yr1970 Yr1971 Yr1972 Yr1973 Yr1974 Yr1975 Yr1976 Yr1977 Yr1978
 7 B07    Yr1959 Yr1960 Yr1961 Yr1962 Yr1963 Yr1964 Yr1965 Yr1966 Yr1967 Yr1968 Yr1969 Yr1970 Yr1971 Yr1972 Yr1973 Yr1974 Yr1975 Yr1976 Yr1977 Yr1978
 8 B08    Yr1959 Yr1960 Yr1961 Yr1962 Yr1963 Yr1964 Yr1965 Yr1966 Yr1967 Yr1968 Yr1969 Yr1970 Yr1971 Yr1972 Yr1973 Yr1974 Yr1975 Yr1976 Yr1977 Yr1978
 9 B09    Yr1959 Yr1960 Yr1961 Yr1962 Yr1963 Yr1964 Yr1965 Yr1966 Yr1967 Yr1968 Yr1969 Yr1970 Yr1971 Yr1972 Yr1973 Yr1974 Yr1975 Yr1976 Yr1977 Yr1978
10 B10    Yr1959 Yr1960 Yr1961 Yr1962 Yr1963 Yr1964 Yr1965 Yr1966 Yr1967 Yr1968 Yr1969 Yr1970 Yr1971 Yr1972 Yr1973 Yr1974 Yr1975 Yr1976 Yr1977 Yr1978

Seems that your example data has the max per sector always in Yr1959 - Yr1978.

user_3732271 · Answer

We may use max.col to get the index of the year column and use that to subset the value

i1 <- grep("^Yr\d+", names(df))
i2 <- max.col(df[i1], "first")
yr_name <- names(df)[i1][i2]
value <- df[i1][cbind(seq_len(nrow(df)), i2)]
out <- data.frame(df['Sector'], yr_name, value)

Find a maximum value for multiple entries in a column

2 Answers