Extract all row.names in a data.frame that match a value in another data.frame

I have a data.frame with 150 column names. For each column, I want to extract the maximum and minimum values (the rows repeat) and the row names of each maximum value. I have extracted the min and max values in another data.frame but don't know how to match them.

I have found functions that are very close for this, like for minimum values:

head(cars)
  speed dist
1     4    2
2     4   10
3     7    4
4     7   22
5     8   16
6     9   10

sapply(cars,which.min)

speed  dist 
    1     1

Here, it only gives the first index for minimum speed.

And I've tried with loops like:

for (i in (colnames(cars))){
  print(min(cars[[i]]))
}


[1] 4
[1] 2

But that just gives me the minimum values, and not if they are repeated and the rowname of each repeated value.

I want something like:

min.value  column  rowname   freq.times
4          speed      1,2        2
2          dist       1          1

Thanks and sorry if I have orthography mistakes. No native speaker

Solution

One option is to use tidyverse. I was a little unclear if you want min and max in the same dataframe, so I included both. First, I create an index column with row numbers. Then, I pivot to long format to determine which values are minimum and maximum (using case_when). Then, I drop the rows that are not min or max (i.e., NA in category). Then, I use summarise to turn the row names into a single character string and get the frequency of a given minimum or maximum value.

library(tidyverse)

cars %>%
  mutate(rowname = row_number()) %>%
  pivot_longer(-rowname, names_to = "column", values_to = "value") %>%
  group_by(column) %>%
  mutate(category = case_when((value == min(value)) == TRUE ~ "min",
                              (value == max(value)) == TRUE ~ "max")) %>%
  drop_na(category) %>%
  group_by(column, value, category) %>%
  summarise(rowname = toString(rowname), freq.times = n()) %>% 
  select(2:3, 1, 4, 5)

Output

# A tibble: 4 × 5
# Groups:   column, value [4]
  value category column rowname freq.times
  <dbl> <chr>    <chr>  <chr>        <int>
1     2 min      dist   1                1
2   120 max      dist   49               1
3     4 min      speed  1, 2             2
4    25 max      speed  50               1

However, if you want to produce the dataframes separately. Then, you could adjust something like this. Here, I don't use category and instead use filter to drop all rows that are not the minimum for a group/column. Then, we can summarise as we did above. You can do the samething for max as well.

cars %>%
  mutate(rowname = row_number()) %>%
  pivot_longer(-rowname, names_to = "column", values_to = "min.value") %>%
  group_by(column) %>%
  filter(min.value == min(min.value)) %>%
  group_by(column, min.value) %>%
  summarise(rowname = toString(rowname), freq.times = n()) %>% 
  select(2, 1, 3, 4)

Output

# A tibble: 2 × 4
# Groups:   column [2]
  min.value column rowname freq.times
      <dbl> <chr>  <chr>        <int>
1         2 dist   1                1
2         4 speed  1, 2             2