# How to extract column from a matrix matching the another file (sample file)

I have two files master file.txt and sample.txt

master.txt

Name    GTEX.1117F.3226.SM.5N9CT    GTEX.111FC.3126.SM.5GZZ2    GTEX.111FC.3326.SM.5GZYV    GTEX.1128S.2726.SM.5H12C    GTEX.1128S.2826.SM.5N9DI    GTEX.117XS.3026.SM.5N9CA
ENSG00000223972 1   0   0   1   0   0
ENSG00000227232 298 168 197 106 221 184
ENSG00000278267 0   1   1   0   0   0
ENSG00000243485 0   2   0   1   0   1
ENSG00000237613 0   0   0   1   0   1
ENSG00000268020 1   0   0   1   0   1
ENSG00000240361 1   3   0   4   2   1
ENSG00000186092 3   1   1   1   1   2


Sample file.txt

GTEX.1117F
GTEX.111FC
GTEX.111XS


Desired output

Name    GTEX.1117F.3226.SM.5N9CT    GTEX.111FC.3126.SM.5GZZ2    GTEX.111FC.3326.SM.5GZYV    GTEX.117XS.3026.SM.5N9CA
ENSG00000223972 1   0   0   0
ENSG00000227232 298 168 197 184
ENSG00000278267 0   1   1   0
ENSG00000243485 0   2   0   1
ENSG00000237613 0   0   0   1
ENSG00000268020 1   0   0   1
ENSG00000240361 1   3   0   1
ENSG00000186092 3   1   1   2
ENSG00000238009 2   2   1   1
ENSG00000233750 0   3   3   0
ENSG00000268903 103 44  76  24


I tried using the R-code form this link Print specific columns in a matrix on the basis of sample id's in the header

However, I am not able to figure out how to extract columns matching only half part of ID (like for GTEX.111FC I want both GTEX.111FC.3126.SM.5GZZ2 and GTEX.111FC.3326.SM.5GZYV)

Thank you

Assuming your "master" files looks like this:

df <- data.frame(Name = LETTERS[1:5],
Col1 = sample(1:10,5),
Col2 = sample(1:10,5),
Col11 = sample(1:10,5),
Col3 = sample(1:10,5))

Name Col1 Col2 Col11 Col3
1    A    5    4     3   10
2    B    1    6     5    6
3    C    3    1     9    4
4    D    9    9     8    9
5    E    8   10     1    1


For a sequence of pattern you want to extract, you can use base r function such as grep:

match_pat = c("Col1","Col2")

m <- unlist(sapply(match_pat,function(x) grep(x,colnames(df))))

Col11 Col12  Col2
2     4     3


And then, extract your columns of interest by doing:

> df[,m]
Col1 Col11 Col2
1    5     3    4
2    1     5    6
3    3     9    1
4    9     8    9
5    8     1   10


In your example, it will be something like:

match_pat <- c("GTEX.1117F","GTEX.111FC","GTEX.111XS")
m <- unlist(sapply(match_pat,function(x) grep(x,colnames(df))))

master[,m]
$$$$

• Thank you for your answer. However, my actual sample list has more than 100 id's and matrix has 2000 rows. I want match_pat to read a list of id's? Is it possible? Mar 9 '21 at 23:59
• @Megha where does your list of ID's come from ? a file ? Mar 10 '21 at 12:02
• @Law: I have a GTEX brain sample file (which have id's in the header like GTEX.111FC.3126.SM.5GZZ2. I want to extract the age-related samples(20-60 yrs) from this file. The age-related data file is a list of files which says all id's starting with GTEX.111FC are 20-30 year old and so on. The issue is the id's in the list is the half part of ID in the main master file making it difficult for me to extract it Mar 10 '21 at 15:22
• And yes age id file is a separated file with the list of ID's Mar 10 '21 at 15:23
• Glad you succeed to solve your issue with Bash. To answer your question, you can define match_pat to be a vector of the column containing id files like match_pat <- as.vector(list_id_file\$NameOfTheColumWithID). Then, you can extract column form your GTEX brain sample file.
– dc37
Mar 10 '21 at 18:13