Question

Extract the match content and merge them

0

Entering edit mode

5.6 years ago

xxxxxxxx ▴ 20

I have 2 files-

file1-

MU_Id
MU1045462
MU104752
MU10563
MU105995
MU106482
MU11094
MU111277
MU1120685
MU1120685
MU1120685
MU1120685
MU1120685
MU1120685
MU1120685
MU1120685
MU11229
MU11245484
MU11246134
MU112589917

file 2-

Ens_Id              Symbol   MU_Id
ENSG00000151779 NBAS    MU101188
ENSG00000007174 DNAH9   MU101310
ENSG00000213281 NRAS    MU101326
ENSG00000221866 PLXNA4  MU10251
ENSG00000141510 TP53    MU1045462
ENSG00000158477 CD1A    MU104752
ENSG00000141510 TP53    MU10563
ENSG00000136021 SCYL2   MU105995
ENSG00000034677 RNF19A  MU106482
ENSG00000141510 TP53    MU11094
ENSG00000152592 DMP1    MU111277
ENSG00000147889 CDKN2A  MU1120685
ENSG00000147889 CDKN2A  MU1120685
ENSG00000147889 CDKN2A  MU1120685
ENSG00000147889 CDKN2A  MU1120685
ENSG00000147889 CDKN2A  MU1120685
ENSG00000147889 CDKN2A  MU1120685
ENSG00000147889 CDKN2A  MU1120685
ENSG00000147889 CDKN2A  MU1120685
ENSG00000141510 TP53    MU11229
ENSG00000086717 PPEF1   MU11245484
ENSG00000165168 CYBB    MU11246134
ENSG00000182752 PAPPA   MU112589917
ENSG00000155761 SPAG17  MU112692097
ENSG00000171307 ZDHHC16 MU112734
ENSG00000106665 CLIP2   MU112756738

I want to search MU Ids of file 1 in file 2 and then add the corresponding 1stcolumn and 2nd column.

expected output-

MU_id              Ens_Id        Symbol
MU1045462   ENSG00000141510 TP53
MU104752    ENSG00000158477 CD1A
MU10563 ENSG00000141510 TP53
MU105995    ENSG00000136021 SCYL2
MU106482    ENSG00000034677 RNF19A
MU11094 ENSG00000141510 TP53
MU111277    ENSG00000152592 DMP1
MU1120685   ENSG00000147889 CDKN2A
MU1120685   ENSG00000147889 CDKN2A
MU1120685   ENSG00000147889 CDKN2A
MU1120685   ENSG00000147889 CDKN2A
MU1120685   ENSG00000147889 CDKN2A
MU1120685   ENSG00000147889 CDKN2A
MU1120685   ENSG00000147889 CDKN2A
MU1120685   ENSG00000147889 CDKN2A
MU11229 ENSG00000141510 TP53
MU11245484  ENSG00000086717 PPEF1
MU11246134  ENSG00000165168 CYBB
MU112589917 ENSG00000182752 PAPPA

It is a sample file the original file is very large so I cant do it manually. I have tried Rscript-

df <- merge(df1, df2, by.x = "MU_Id", by.y = "MU_Id")

But I got output like this-

MU_Id   Ens_Id  Symbol
1   MU1045462   ENSG00000141510 TP53
2   MU104752    ENSG00000158477 CD1A
3   MU10563 ENSG00000141510 TP53
4   MU105995    ENSG00000136021 SCYL2
5   MU106482    ENSG00000034677 RNF19A
6   MU11094 ENSG00000141510 TP53
7   MU111277    ENSG00000152592 DMP1
8   MU1120685   ENSG00000147889 CDKN2A
9   MU1120685   ENSG00000147889 CDKN2A
10  MU1120685   ENSG00000147889 CDKN2A
11  MU1120685   ENSG00000147889 CDKN2A
12  MU1120685   ENSG00000147889 CDKN2A
13  MU1120685   ENSG00000147889 CDKN2A
14  MU1120685   ENSG00000147889 CDKN2A
15  MU1120685   ENSG00000147889 CDKN2A
16  MU1120685   ENSG00000147889 CDKN2A
17  MU1120685   ENSG00000147889 CDKN2A
18  MU1120685   ENSG00000147889 CDKN2A
19  MU1120685   ENSG00000147889 CDKN2A
20  MU1120685   ENSG00000147889 CDKN2A
21  MU1120685   ENSG00000147889 CDKN2A
22  MU1120685   ENSG00000147889 CDKN2A
23  MU1120685   ENSG00000147889 CDKN2A
24  MU1120685   ENSG00000147889 CDKN2A
25  MU1120685   ENSG00000147889 CDKN2A
26  MU1120685   ENSG00000147889 CDKN2A
27  MU1120685   ENSG00000147889 CDKN2A
28  MU1120685   ENSG00000147889 CDKN2A
29  MU1120685   ENSG00000147889 CDKN2A
30  MU1120685   ENSG00000147889 CDKN2A
31  MU1120685   ENSG00000147889 CDKN2A
32  MU1120685   ENSG00000147889 CDKN2A
33  MU1120685   ENSG00000147889 CDKN2A
34  MU1120685   ENSG00000147889 CDKN2A
35  MU1120685   ENSG00000147889 CDKN2A
36  MU1120685   ENSG00000147889 CDKN2A
37  MU1120685   ENSG00000147889 CDKN2A
38  MU1120685   ENSG00000147889 CDKN2A
39  MU1120685   ENSG00000147889 CDKN2A
40  MU1120685   ENSG00000147889 CDKN2A
41  MU1120685   ENSG00000147889 CDKN2A
42  MU1120685   ENSG00000147889 CDKN2A
43  MU1120685   ENSG00000147889 CDKN2A
44  MU1120685   ENSG00000147889 CDKN2A
45  MU1120685   ENSG00000147889 CDKN2A
46  MU1120685   ENSG00000147889 CDKN2A
47  MU1120685   ENSG00000147889 CDKN2A
48  MU1120685   ENSG00000147889 CDKN2A
49  MU1120685   ENSG00000147889 CDKN2A
50  MU1120685   ENSG00000147889 CDKN2A
51  MU1120685   ENSG00000147889 CDKN2A
52  MU1120685   ENSG00000147889 CDKN2A
53  MU1120685   ENSG00000147889 CDKN2A
54  MU1120685   ENSG00000147889 CDKN2A
55  MU1120685   ENSG00000147889 CDKN2A
56  MU1120685   ENSG00000147889 CDKN2A
57  MU1120685   ENSG00000147889 CDKN2A
58  MU1120685   ENSG00000147889 CDKN2A
59  MU1120685   ENSG00000147889 CDKN2A
60  MU1120685   ENSG00000147889 CDKN2A
61  MU1120685   ENSG00000147889 CDKN2A
62  MU1120685   ENSG00000147889 CDKN2A
63  MU1120685   ENSG00000147889 CDKN2A
64  MU1120685   ENSG00000147889 CDKN2A
65  MU1120685   ENSG00000147889 CDKN2A
66  MU1120685   ENSG00000147889 CDKN2A
67  MU1120685   ENSG00000147889 CDKN2A
68  MU1120685   ENSG00000147889 CDKN2A
69  MU1120685   ENSG00000147889 CDKN2A
70  MU1120685   ENSG00000147889 CDKN2A
71  MU1120685   ENSG00000147889 CDKN2A
72  MU11229 ENSG00000141510 TP53
73  MU11245484  ENSG00000086717 PPEF1
74  MU11246134  ENSG00000165168 CYBB
75  MU112589917 ENSG00000182752 PAPPA

Or suggest me some command in terminal

bash R • 1.6k views

ADD COMMENT • link updated 20 months ago by Ram 44k • written 5.6 years ago by xxxxxxxx ▴ 20

2

Entering edit mode

With grep you can achieve the same.

ADD REPLY • link 5.6 years ago by Arup Ghosh 3.2k

2

Entering edit mode

grep -Fxf is the way to go, as long as OP knows what they're doing and the edge cases where it could fail.

ADD REPLY • link 5.6 years ago by Ram 44k

0

Entering edit mode

I have tried Rscript-

This is most uninformative, please show some effort.

What is the output you get?

ADD REPLY • link 5.6 years ago by ATpoint 85k

0

Entering edit mode

Got a very long output as compared to file1

ADD REPLY • link 5.6 years ago by xxxxxxxx ▴ 20

0

Entering edit mode

We cannot read your screen or your mind. You'll have to explain more. Don't make this too hard for us or you won't get any help.

ADD REPLY • link 5.6 years ago by WouterDeCoster 47k

0

Entering edit mode

Modified the Question please check

ADD REPLY • link 5.6 years ago by xxxxxxxx ▴ 20

0

Entering edit mode

That is not possible - your output does not have V1-style headers, but has proper headers. Either your command is wrong or your output is.

ADD REPLY • link 5.6 years ago by Ram 44k

0

Entering edit mode

Did you read the help page? ?merge

Play around with the optional arguments.

ADD REPLY • link 5.6 years ago by Benn 8.3k

score 3 · Answer 1 · 2019-04-24

3

Entering edit mode

5.6 years ago

b2tremblay ▴ 30

Perhaps this could work for you:

merge(df1[!duplicated(df1$MU_Id), , drop=FALSE], df2, by = "MU_Id", all.x = TRUE, all.y = FALSE)

If you actually want the duplicates in df1, then:

merge(df1, df2, by = "MU_Id", all.x = TRUE, all.y = FALSE)

ADD COMMENT • link 5.6 years ago by b2tremblay ▴ 30

0

Entering edit mode

You don't need to add all.y=FALSE, it's programmed to be set to FALSE unless you explicitly set it to TRUE

> ?merge  
...
...
...
## S3 method for class 'data.frame'
merge(x, y, by = intersect(names(x), names(y)),
      by.x = by, by.y = by, all = FALSE, all.x = all, all.y = all,
      sort = TRUE, suffixes = c(".x",".y"), no.dups = TRUE,
      incomparables = NULL, ...)
...
...
...

ADD REPLY • link 5.6 years ago by Ram 44k