1

I have a dataframe (data) that looks like this:

ID     Peak     RT     Area
P1A1   1        0.7    100
P1A2   1        0.5    150
P1A1   2        1.2    100
P1A2   2        1.4    200

I have another dataframe (AllPlates) that looks like this:

ID     Sample
P1A1   1
P1A2   2

I would like to merge them horizontally to create a dataframe that looks like this:

ID     Peak     RT     Area     Sample
P1A1   1        0.7    100      1
P1A2   1        0.5    150      2
P1A1   2        1.2    100      1
P1A2   2        1.4    200      2

I tried using the merge function like so:

mergeddata <- merge(AllPlates, data[, c("ID", "Peak", "RT", "Area")], by="ID", all=TRUE)

However, the result is this:

ID     Peak     RT     Area     Sample
P1A1   NA       NA     NA       1
P1A1   1        0.7    100      NA
P1A1   2        1.2    100      NA
P1A2   NA       NA     NA       2
P1A2   1        0.5    150      NA
P1A2   2        1.4    200      NA

As you can see, instead of merging horizontally, it's simply inserting a row from AllPlates on top of each group of identical data$ID rows. Any idea why this is the case?

Alexander Kim
  • 11
  • 2
  • When I run your code, I do not get those results. Perhaps there's something else with your data. Can you [edit] your question and add the output from `dput(data)` and `dput(AllPlates)`? – r2evans Aug 18 '21 at 20:33

1 Answers1

1

We could use left_join from dplyr package:

library(dplyr)
left_join(data, AllPlates, by="ID")

    ID Peak  RT Area Sample
1 P1A1    1 0.7  100      1
2 P1A2    1 0.5  150      2
3 P1A1    2 1.2  100      1
4 P1A2    2 1.4  200      2
TarJae
  • 72,363
  • 6
  • 19
  • 66