How to get the filenames based on names in other file in R?

Question

I have a file directory data that have files like below:

data
  |___ UPA.csv
  |___ M_B.csv
  |___ M_C.csv
  |___ M_D.csv
  |___ M_E.csv

UPA.csv which have like below:

Genes
AC018653.3
AC022509.1
AC022509.2
AC055720.2
AC082651.1
AC084346.2
AC084824.4
AC092171.4
AC092803.2

M_B.csv have like below:

AC084346.2
AD097808.3
AC084824.4
ADFR3564.8
A1982983.4

M_C.csv have like below:

AC098789.3
AC022509.2
AC783546.3
AC055720.2

M_D.csv have like below:

AC018653.3
AS989473.9
AC022509.1
AE378467.1

I want to check which of the Genes in UPA.csv were also found in other files. And want to get the file name.

I want the output to be like below:

M_B.csv: AC084346.2, AC084824.4
M_C.csv: AC022509.2, AC055720.2
M_D.csv: AC018653.3, AC022509.1

For this I tried like below:

setwd("/data/")
library(tidyverse)
library(magrittr)

genes <- Sys.glob(file.path("M_*.csv"))
genes.read <- lapply(genes,function(x) read.delim(x, header = FALSE))
genes.read <- lapply(genes.read, function(x) set_colnames(x, "Genes"))
ref2 <- list.files(pattern = "UP")
ref2
ref.read <- read.delim(ref2[[1]])
intersect <- lapply(seq_along(genes.read), function(x) 
  intersect(genes.read[[x]], ref.read))
for(i in 1:length(genes.read)) { 
  cat(genes[[i]],":",intersect[[i]]$Genes, "\n")
}

And the above code gave just the file names, no genes:

M_B.csv:
M_C.csv
M_D.csv:

Answer 1

Try the following :

UPA <- read.csv('UPA.csv')
filenames <- list.files(pattern = 'M_.*\\.csv$')

do.call(rbind, lapply(filenames, function(x) {
  data <- read.delim(x, header = FALSE)
  names(data) <- 'Genes'
  cbind(file = x, subset(data, Genes %in% UPA$Genes))
})) -> result

With tidyverse you could do the same as :

library(tidyverse)

map_df(filenames, function(x) {
  read.delim(x, header = FALSE) %>%
    setNames('Genes') %>%
    filter(Genes %in% UPA$Genes) %>%
    mutate(file = x)
}) -> result

This should give you output something as :

result

#       Genes    file 
#1 AC084346.2 M_B.csv
#2 AC084824.4 M_B.csv
#3 AC022509.2 M_C.csv
#4 AC055720.2 M_C.csv
#...