I have two tables and I want to merge them using R based on the SampleID

Question

Table 1:

SampleID	A	B	C
S101	1	23	24
S102	1	26	28
S103	2	22	40

Table 2:

SampleID	A	B	C	D
S101	1	23	24	99
S102	1	26	28	56
S103	2	22	40	78
S104	3	45	46	23

When I use merge for this based on SampleID this is what I am getting:

SampleID	A	B	C	D
S101	1	23	24	NA
S101	NA	NA	NA	99
S102	1	26	28	NA
S102	NA	NA	NA	56
S103	2	22	40	NA
S103	NA	NA	NA	78
S104	3	45	46	23
S104	NA	NA	NA	23

what I am expecting is:

SampleID	A	B	C	D
S101	1	23	24	99
S102	1	26	28	56
S103	2	22	40	78
S104	3	45	46	23

covariates_merge <- merge(x = hormonal, y = poa, by = "SampleID", all = TRUE) View(covariates_merge) — sai, May 26 '23 at 16:29
Does this answer your question? [How to join (merge) data frames (inner, outer, left, right)](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — benson23, May 26 '23 at 16:33
BTW your Table 2 and the expected output seems to be identical — benson23, May 26 '23 at 16:35
I am sorry about that, imagine we have more column in table two that needs to be added — sai, May 26 '23 at 16:44

stefan_aus_hannover · Answer 1 · 2023-05-26T17:52:25.413

0

Using a merge() like this would get you the two tables merged as you want

merge(Table1,Table2,all=TRUE)



Output:

  sampleID a  b  c  d
1     S101 1 23 24 99
2     S102 1 26 28 56
3     S103 2 22 40 78
4     S104 3 45 46 23

edited May 26 '23 at 17:52

answered May 26 '23 at 16:33

stefan_aus_hannover

1,777
12
13

When I use leftjoin() it is removing the duplicates but it is ging NA's for all the first table data – sai May 26 '23 at 16:36

I have two tables and I want to merge them using R based on the SampleID

1 Answers1