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
4 answers

min.value < sapply(cars, min) columns < names(min.value) row.values < sapply(columns, \(x) which(cars[[x]] == min.value[which(names(min.value) == x)])) freq.times < sapply(row.values, length) row.values < sapply(row.values, \(x) paste(x, collapse = ",")) names(min.value) < names(row.values) < names(freq.times) < NULL data.frame(min.value = min.value, columns = columns, row.values = row.values, freq.times = freq.times) min.value columns row.values freq.times 1 4 speed 1,2 2 2 2 dist 1 1
Here it is wrapped in function, so that you can use it across whatever data frame and function you need:
create_table < function(df, FUN) { values < sapply(df, FUN) columns < names(values) row.values < sapply(columns, \(x) which(df[[x]] == values[which(names(values) == x)])) freq.times < sapply(row.values, length) row.values < sapply(row.values, \(x) paste(x, collapse = ",")) names(values) < names(row.values) < names(freq.times) < NULL data.frame(values = values, columns = columns, row.values = row.values, freq.times = freq.times) } create_table(cars, min) values columns row.values freq.times 1 4 speed 1,2 2 2 2 dist 1 1 create_table(cars, max) values columns row.values freq.times 1 25 speed 50 1 2 120 dist 49 1

One option is to use
tidyverse
. I was a little unclear if you wantmin
andmax
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 (usingcase_when
). Then, I drop the rows that are not min or max (i.e.,NA
in category). Then, I usesummarise
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 usefilter
to drop all rows that are not the minimum for a group/column. Then, we cansummarise
as we did above. You can do the samething formax
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

You can use
which
to obtain the positions.sapply
should work. Since you need multiple summary statistics for each column, you just have to wrap up them in a list. Something like thisas.data.frame(sapply(cars, \(x) { extrema < range(x) min.row < which(x == extrema[[1L]]) max.row < which(x == extrema[[2L]]) list( min.value = extrema[[1L]], max.value = extrema[[2L]], min.row = min.row, max.row = max.row, freq.min = length(min.row), freq.max = length(max.row) ) }))
Output
speed dist min.value 4 2 max.value 25 120 min.row 1, 2 1 max.row 50 49 freq.min 2 1 freq.max 1 1

Here is another
tidyverse
approach:which.min(.)
gives the first index, whereaswhich(. == min(.))
will give all indices that are true for the condition!Analogues to get the frequence we could use:
length(which(.==min(.)))
summarise
across all columnsmin.value
,rowname
andfreq.time
 The part after is pivoting to bring the column name in position.
library(tidyverse) cars %>% summarise(across(dplyr::everything(), list(min.value = min, rowname = ~list(which(. == min(.))), freq.times = ~length(which(.==min(.)))))) %>% pivot_longer( cols = contains("_"), names_to = "key", values_to = "val", values_transform = list(val = as.character) ) %>% separate(key, c("column", "name"), sep="_") %>% pivot_wider( names_from = name, values_from = val ) %>% mutate(rowname = str_replace(rowname, '\\:', '\\,'))
column min.value rowname freq.times <chr> <chr> <chr> <chr> 1 speed 4 1,2 2 2 dist 2 1 1
do you know?
how many words do you know