# Splitting data table up based on separate file of gene names

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!

• What have you tried? You can use df1$col1 %in% df2$col1 and !(df1$col1 %in% df2$col1). Oct 26, 2020 at 16:48
• I would look up awk or forward your R code for loading a dataframe, which will give you access to the above code. Once you've a bit of code to share thats when help is more available.
– M__
Oct 26, 2020 at 17:03
• Come on, what happened to "friendly to New Contributors?" Oct 27, 2020 at 10:30
• @Megan As you are new please note that it is actually good practice to provide some reproducible examples. You can use dput() on your data to get a copy/paste ready version that you can share here. That makes it easier to understand how your data look and therefore easier to come up with solutions.
– user3051
Oct 27, 2020 at 12:00

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.