Merge 2 files by first column

Slack CH Sept 22nd 2021

Stored on server: /space/chen-syn01/1/data/cinliu/data/merge_by_col1

Original instructions

Hi Nini, could you help me merge these two files by the first column (ie, sample ID)?

Please merge the big file into the small one so resulting in 33 rows. Thanks!

The subjects absent in the small file can be omitted.

Files given

Excel file (big file): 41588_2012_BFng2335_MOESM53_ESM.xls

2700 rows x 14 columns

Small file: igsr_samples (1).tsv

33 rows x 9 columns

Step 1: basic set up

Clear work space, set working directory. Input files.

rm(list = ls())

setwd('/Users/nini/Desktop/2021lab/CH/merge_by_1st_col/')

big_df <- read_excel("41588_2012_BFng2335_MOESM53_ESM.xls")

small_df <- read.delim2("~/Desktop/2021lab/CH/merge_by_1st_col/igsr_samples (1).tsv")

Step 2: Data manipulation

Make ID the common identifier. Then use the merge function to merge and indicate ID as the common factor.

My personal favorite video tutorial of how to use merge function:

Merge Data Frames by Column Names in R (Example) | Combine with merge Function : https://www.youtube.com/watch?v=rlvWJdjYo1g
#merge data small_df$ID <- small_df$Sample.name #make ID the common name to refer to. Not removing Sample.name because in the end that is the name we want to keep when saving tsv
new_df<- merge(small_df, big_df, by='ID')

Step 3: Output

Save as tab separated file

#merge data small_df$ID <- small_df$Sample.name #make ID the common name to refer to. Not removing Sample.name because in the end that is the name we want to keep when saving tsv
new_df<- merge(small_df,big_df,by='ID')

Full code:

merge_by_1st_col.R

rm(list = ls())setwd('/Users/nini/Desktop/2021lab/CH/merge_by_1st_col/data/')
big_df <- read_excel("41588_2012_BFng2335_MOESM53_ESM.xls")small_df <- read.delim2("~/Desktop/2021lab/CH/merge_by_1st_col/data/igsr_samples (1).tsv")#goal: merge these two files by the first column (ie, sample ID)#Please merge the big file into the small one so resulting in 33 rows#the subjects absent in the small file can be omitted
summary(match(small_df$Sample.name,big_df$ID)) #20 IDs from the small life could not be found in the big list small_df[1,]big_df[2278,]
#save a list of the NAs for record keeping nas <- which(is.na(match(small_df$Sample.name,big_df$ID)), arr.ind=TRUE) #no match in the subset naIDs <- small_df$Sample.name[nas]write.table(naIDs, 'Sample_name(ID)_not_found_in_big_dat.txt', col.names = F,row.names = F, quote = F)## double check ##for (i in 1:length(naIDs)) { print(grep(naIDs[i],big_df$ID))}#should all return integer(0)#double check end ###
#merge data small_df$ID <- small_df$Sample.name #make ID the common name to refer to. Not removing Sample.name because in the end that is the name we want to keep when saving tsv
new_df<- merge(small_df,big_df,by='ID')
write.table(new_df,'merged_by_ID(sample_name).tsv',quote = F, row.names = F, sep='\t')
#if you would like to save a copy with all 33 rows#all33 <- merge(small_df,big_df,by='ID', all.x = T)