Merging between two tables in R

Question

I have a first table "results" of size 5313 rows and a second table "Lpp_sans_rss" size 3889 rows. I would like to create a merge between the tables by adding only the second column "lpp_libelle" from the second table in the last column of the first table. My problem is : when i make merge between these two tables, i get "124556" rows. I want to keep the same size of the first table by adding the "libell" of each "lpp_code"

Example of table 1 :

code_acte;lpp_code;count;expected_count;p-value;ROR;drug_margin;event_margin;FDR
 NGQK001 ;985675;3;0.00894810101411811;9.99200722162641e-16;689.196078431372;105;6;2.11945533050936e-16
 DASA006 ;985536;3;0.0159787518109252;9.99200722162641e-16;288.012295081967;9;125;2.11778383576921e-16

Example of table 2 :

lpp_code;lpp_libelle
953082;IMP RES SEPRAFILM 13X15CM 430103 (A56D)
3115092;ENDOPROT CORONAIRE, STENT LIB. ZOTAROLIMUS, MEDTRONIC,RESOLUTE INTEGRITY,3MM.

Xizam · Answer 1 · 2016-09-15T15:02:28.133

1

First, look for every lppcode in table1

see what the lpp_libelle is in table2 for that lppcode

put that value in table1 in column 10 in table1

for (i in length(table1[,2])){
 table1[i,10]=as.character(table2[which(table2[,1]==table1[i,2]),2])
}

edit: Ferdi's answer is more elegant.

edited Sep 15 '16 at 15:02

answered Sep 15 '16 at 14:24

Xizam

699
7
21

Thank you but i don't used c++. I do this with r like this : > file_result<-merge(resultat,Lpp_sans_rss, by.lpp_cde = "lpp_code") – user41096 Sep 15 '16 at 14:26
This isn't C++, this is S, the language of R. – Xizam Sep 15 '16 at 14:27
ok sorry but i'am new user of R – user41096 Sep 15 '16 at 14:34
That's quite alright. Have you tried this solution? Don't forget to change the table1 and table2 to the real names of the data structures. – Xizam Sep 15 '16 at 14:47
No. i don't do it because i don't how to used this solution in R. – user41096 Sep 15 '16 at 14:49
Exactly the same as you would the solution suggested by Ferdi. Just type it into your R window or copy/paste it. – Xizam Sep 15 '16 at 14:50
i get this > for (i in length(resultat[,2])){ + resultat[i,10]=Lpp_sans_rss[which(Lpp_sans_rss[,1]==resultat[i,2]),2] + } Error in `[<-.data.frame`(`*tmp*`, i, 10, value = c(167L, 167L, 167L, : replacement has 141 rows, data has 1 – user41096 Sep 15 '16 at 14:58
This is because your data is loaded as factors rather than characters. I will adjust my answer. – Xizam Sep 15 '16 at 15:01

score 1 · Accepted Answer · edited May 23 '17 at 12:32

1

The left outer join (or simply left join) described in the following question should solve your problem.

merge(x = results, y = Lpp_sans_rss, by = "ENTERHEREYOURCRITERIA", all.x = TRUE)

How to join (merge) data frames (inner, outer, left, right)?

edited May 23 '17 at 12:32

Community

1
1

answered Sep 15 '16 at 14:26

Ferdi

540
3
12
23

Thank you. I do this : > file_result<-merge(x = resultat, y = Lpp_sans_rss, by = "lpp_code", all.x = TRUE) but it did not work. – user41096 Sep 15 '16 at 14:34
Do the columns in both data.frames have the name "lpp_code"? – Ferdi Sep 15 '16 at 14:39
Yes. exact. they have the same name. I get 124556 rows ! – user41096 Sep 15 '16 at 14:43
Presumable your algorithm thinks you want to do an inner or an outer join and not a left join. If you use RStudio try to take "all.x" as an autocompletion option – Ferdi Sep 15 '16 at 14:48
I tried to apply a left outer join. The same problem ! – user41096 Sep 15 '16 at 14:52

Merging between two tables in R

2 Answers2