r - Match column names with another dataframe and split into separate dataframes

Question

I've got a dataframe (df) which looks like this:

tex21.222   chic56.345  wa34.907
0.5         0.6         1.12
0.8         1.2         0.9

I want to partition this dataframe into separate dataframes, based on another dataframe called keys, which looks like this:

site_name   sample_name
tex_1       tex21.222
tex_1       tex23.234
chic_1      chic56.345 
wa_1        wa34.907
wa_2        wa24.277

For every column of df, I want to (1) match the column name with sample_name in keys to get site_name, (2) move all columns that have the same site_name from df into separate dataframes.

So, in the end, I want to have separate dataframes with all columns that fall within tex_1, all columns that fall within chic_1 etc.

How can I do this?

question from:https://stackoverflow.com/questions/65847609/match-column-names-with-another-dataframe-and-split-into-separate-dataframes

Answer 1

Try next code:

library(dplyr)
library(tidyr)
#Code
data <- df %>% pivot_longer(everything()) %>%
  left_join(keys,by = c('name'='sample_name'))
#Split
List <- split(data,data$site_name)
List <- lapply(List,function(x) {x$site_name<-NULL;x})
list2env(List,envir = .GlobalEnv)

Outputs:

List
$chic_1
# A tibble: 2 x 2
  name       value
  <chr>      <dbl>
1 chic56.345   0.6
2 chic56.345   1.2

$tex_1
# A tibble: 2 x 2
  name      value
  <chr>     <dbl>
1 tex21.222   0.5
2 tex21.222   0.8

$wa_1
# A tibble: 2 x 2
  name     value
  <chr>    <dbl>
1 wa34.907  1.12
2 wa34.907  0.9