Tidyverse approach to selecting values from different columns in a dataframe with row-column index pairs

user_13993139 0
Viewed 195

In R, I have a character vector v, whose values are all elements of colnames(rain) for a dataframe rain. I want to use v to create a new vector chosen whose values satisfy chosen[i] == rain[i, v[i]] without resorting to a loop and preferably using a tidyverse approach.

For example, if I have:

library(tidyverse)
rain <- tibble(ceres = c(0, 1, 0, 1.5, 3),
               mond = c(0, 0, 0.5, 0, 0),
               els = c(1, 2, 1, 0, 1))

v <- c("els", "ceres", "els", "mond", "ceres")

I would want to have returned in chosen:

> chosen
# els ceres   els  mond ceres 
#    1     1     1     0     3
tibble tidyverse dataframe r
5 Answers

You can also use the map2 function from purrr

map2_dbl(set_names(v), seq_along(v), ~rain[[.y, .x]])

#>   els ceres   els  mond ceres 
#>     1     1     1     0     3
user_77787870

You could do

rain %>%
  mutate(id = row_number()) %>%
  pivot_longer(-id) %>%
  inner_join(tibble(id = seq_along(v), name = v))

which returns

# A tibble: 5 x 3
     id name  value
  <int> <chr> <dbl>
1     1 els       1
2     2 ceres     1
3     3 els       1
4     4 mond      0
5     5 ceres     3

Adding

pull(value, name)

returns the named vector

#>  els ceres   els  mond ceres 
#>    1     1     1     0     3
user_125052510

Do you mean something like this?

> mapply(`[`, rain[v], seq_along(v))
  els ceres   els  mond ceres
    1     1     1     0     3

or

> mapply(function(p, q) rain[[p]][q], v, seq_along(v))
  els ceres   els  mond ceres
    1     1     1     0     3

or

> setNames(as.matrix(rain)[cbind(seq_along(v), match(v, names(rain)))], v)
  els ceres   els  mond ceres
    1     1     1     0     3
user_121587570

lots of answers already, but just to show that this is very simple with matrix indexing in base R

m.rain = as.matrix(rain, rownames.force =T)
m.rain[cbind(seq_along(v), v)]

Or, similary:

as.matrix(rain)[cbind(seq_along(v), match(v, names(rain)))]
user_27615750

I think this might be an XY problem, I have at least never encountered the need to index a dataframe like that. I'd suspect that you could encode your data differently, but if it's not the case you can do the indexing like this

enframe(v, name = "i", value = "j") %>% 
  rowwise() %>% 
  summarise(
    name = j, value = rain[[i, j]]
  ) %>% 
  deframe()
#>   els ceres   els  mond ceres 
#>     1     1     1     0     3

Created on 2021-08-09 by the reprex package (v1.0.0)

user_77787870
Related