Extract and assign data information by matching columns of two data frames

Question

[Bio]

Hi there, I would like to extract and assign study information to the study column of my.data from info.data by matching their samples column.

I did the following:

my.data <- matrix(1:18, nrow = 6, dimnames = list(c("X","Y","Z","A","G","C"), c("A","B","C")))



info.data <- data.frame(samples = rep(rep(c("A","B","C")), times=1),
                        study = rep(rep(c("S1","S2","S3")), times=1))


as.data.frame(my.data)  %>%   
      rownames_to_column(var = "row") %>%  
      gather(key = 'samples', value = 'rep', -row) %>%   mutate(study =
      info.data$study[samples %in% info.data$samples])

But it fills out the first three rows of the study column and in an incorrect way.

Any help much appreciated!

Answer 1

[rpolicastro]

my.data |>
  as_tibble(rownames="row") |>
  pivot_longer(!row, names_to="samples", values_to="rep") |>
  left_join(info.data)

[Bio]

Thanks a lot for your help

[Bio]

Is there any other solution in the case when info.data contain many other columns?

[rpolicastro]

do you want to include or exclude those columns?

[Bio]

not really, I need only study column

[rpolicastro]

left_join(select(info.data, samples, study))