Loop through two columns and print third column in R or linux

Question

My file1.txt looks like this

ENST00000456328.2_1
ENST00000002596.6_3
ENST00000488147.1_2
ENST00000003302.8_2

and my file2.tsv looks like this

ENST00000456328.2_1     ENSG00000223972
ENST00000450305.2_1     ENSG00000223972
ENST00000488147.1_2     ENSG00000227232
ENST00000473358.1_1     ENSG00000243485

I would like to loop through the whole column and:

if column 1 in file1 == column 1 in file 2
print column 2 in file2 into a new txt file.

*file2 contains all the values of file1

to give the output.txt

ENSG00000223972
ENSG00000227232

May I know how to do this in R or linux? Thank you.

Have you tried something? In R you can `merge` the two files. See https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right — Ronak Shah, Mar 05 '21 at 05:35
The expected output from your example is empty? If so, can you post an example with a non empty output and its expected output? — Rui Barradas, Mar 05 '21 at 05:53
ok row 1 and 3 should match now, thanks for pointing that out! — beepboopbeep, Mar 05 '21 at 05:58

score 0 · Answer 1 · answered Mar 05 '21 at 06:04

0

Say your first file is F1.tsv and file 2 is F2.tsv. In R:

file1 = read.table("F1.tsv")
file2 = read.table("F2.tsv")
# Since these files don't have a header row (i.e. the columns are not named), 
# variables (i.e. columns) will be named as {V1} and {V1,V2} by default, respectively
file2$V2[file1$V1 == file2$V1]

Should give you the answer...

answered Mar 05 '21 at 06:04

Sudaraka

125
7

thank you! it gives ``` Warning message: In isoforms$pid == map$Tx : longer object length is not a multiple of shorter object length ``` – beepboopbeep Mar 05 '21 at 07:32
I think you might have a larger number of entries (rows) in one of the two files. You can use %in% to do the matching. If you can attach the two files, I can help further... – Sudaraka Mar 05 '21 at 08:58
sorry for the late reply and yes that works! thank you! – beepboopbeep Mar 05 '21 at 22:44

Loop through two columns and print third column in R or linux

1 Answers1