Splitting data table up based on separate file of gene names

Question

I am very new so bare with me. I have a file called data (.csv that I turned into a data table) with 2 columns (geneid, normalized counts) and about 9000 rows. I have a second file called Xgenes of genes that I am interested in pulling out of the first table (1 column- geneid, about 50 rows).

Is there a way to make 2 new files- I want one with all the geneid/normalized counts minus the ones in the second file (so minus about 50 genes). Then I want to make another file that only has the geneid/normalized counts of the 50 genes in the second file.

Currently I am doing this manually which does indeed work but if there is a way to do it through R it would make my future analysis much easier.

Thanks!

Answer 1

There are plenty of efficient and smarter solutions, but if you have so small tables, you can just use the very basics of R to do what you need:

# you need to adjust the following two lines to fit to your file formating and file names
genes_filt_table <- read.table(..., col.names = c('geneid'), stringsAsFactors = F)
norm_counts <- read.table(..., col.names = c('geneid', 'norm_counts'), sep = ',', stringsAsFactors = F)

genes_to_filt <- norm_counts$geneid %in% genes_filt_table$geneid
filt_genes <- norm_counts[genes_to_filt, ]
non_filt_genes <- norm_counts[!genes_to_filt, ]

write.table(filt_genes, 'filtered_genes.csv', quote = F, row.names = F, sep = ',')
write.table(non_filt_genes, 'non-filtered_genes.csv', quote = F, row.names = F, sep = ',')

This being said, there are like milions of more efficient solutions out there. I provided one that is using as basic R functions as possible, as it seemed that you would like to see a simple R solution.