extracting common rows from two files

Question

[zizigolu]

hi,

I have two lists

GENE_SYMBOL ID
Mns1                      A_51_P100174
Srf                          A_51_P100289

and

gene_symbol substanceBXH
Slc25a27           MMT00000007
Mrpl45         MMT00000014

first column in both lists contains the name gene symbole although not sorted, how I can extract a list containing the second columns of both file, for example A_51_P100174 matches with which MMT00000007?

thank you

Answer 1

[ejm32]

I would start with merge.

merge(df1, df2, by.x="GENE_SYMBOL", by.y="gene_symbol", all=T)

[zizigolu]

sorry merged file is like so ID substanceBXH GENE_SYMBOL

A_55_P2280868 MMT00026679 0610005C13Rik

A_52_P7183  MMT00001605 0610007L01Rik

A_52_P194464 MMT00082066 0610007P08Rik

dim(merg) [1] 11012 3

I have another file contains expression profiles

substanceBXH F2_2 F2_3

A_30_P01033363 4.920370 5.128868

A_55_P1965358 6.673461 6.559542

A_66_P122433 3.925836 3.671287

 DarkCorner 3.447131 2.805281

dim(mycounts) [1] 2256 13

how I can extract 2256 MMT00026679 matched and the correspond expression profile from mycounts file?

[ejm32]

You can use merge again to match "MMT000..." to your expression data based on merg$ID and mycounts$substanceBXH, if you only want to keep entries that have data in both dataframes set all=FALSE. Then if you want to extract rows that contain "MMT000..." use grep and subseting...

merg2[grep("MMT000", merg2$substanceBXH), ]

[zizigolu]

sorry, I could not get you. too confusing :(

[ejm32]

Could you please clarify your question(s)? Then I can give you a more clear answer/solution.

[zizigolu]

actually your explanation was clear enough and thanks for that. the problem is with my weakness in R