Merge dataframes or match columns to obtain new column

Question

I want to merge two data frames. So the column Type from Data appears in Values.

DATA

Name                Type            Code
gabapentine         Pharmaceutical  60142-96-3
Glyphosate          Pesticide       1071-83-6
Guanylurea          Pharmaceutical  141-83-3
hydrochlorthiazide  Pharmaceutical  58-93-5
EDTA                Industrial C.   NA

Values

Name                Value           Code
gabapentine         0,2             60142-96-3
Glyphosate          1,8             1071-83-6
Urea                1,2             141-83-3
hydrochlorthiazide  0,5             58-93-5
EDTA                2,3             NA

I want to get this

Name                Value           Code        Type
gabapentine         0,2             60142-96-3  Pharmaceutical
Glyphosate          1,8             1071-83-6   Pesticide
Guanylurea                1,2             141-83-3    Pharmaceutical
hydrochlorthiazide  0,5             58-93-5     Pharmaceutical
EDTA                2,3             NA          Industrial C.

I tried the options of this question with merge

But I get

Name                Value           Code        Type
gabapentine         0,2             60142-96-3  Pharmaceutical
Glyphosate          1,8             1071-83-6   Pesticide
Urea                1,2             141-83-3    NA
hydrochlorthiazide  0,5             58-93-5     Pharmaceutical
EDTA                2,3             NA          Industrial C.

Is it possible to use the OR | operator in the merge function? Or in the match function? Cause I want to match the Names,and if they don't (as the case of Guanylurea and Urea), then look in the Codes and finally add the Type column.

Is possible something like this

Values$type = Data$type[match((Values$Name, Data$Name) | (Values$Code, Data$Code))]

or like this

merge(Data, Values, by=c("Name" | "Code")))

Why don't you use only the code variable ? How is the name variable helpful ? — Haezer, May 02 '19 at 14:03
It's hard to know how this differs from the question you linked to (and many others on SO about joins) without seeing the code that you used to merge your data frames. — camille, May 02 '19 at 14:04
Cause the column Code does not always have a value. Just edited the question. — user195366, May 02 '19 at 14:07

Aaron Parrilla · Answer 1 · 2019-05-02T14:24:29.707

1

With dplyr you can try:

df <- full_join(DATA,Values,by=c("Name","Code"))

then you erase the value column, specifically, value.y

edited May 02 '19 at 14:24

answered May 02 '19 at 14:03

Aaron Parrilla

522
3
13

With this the Guanylurea rows don't appear. That also happened with the merge. – user195366 May 02 '19 at 14:15
@user195366 Sorry i don't see Guanylurea in your example. Could you show that case too? – Aaron Parrilla May 02 '19 at 14:20
@user195366 Is that what you where looking for? – Aaron Parrilla May 02 '19 at 14:24

score 0 · Answer 2 · answered May 02 '19 at 14:20

0

Consider an rbind with merge followed by unique to remove duplicates:

unique(rbind(merge(Data, Values, by="Name",
             merge(Data, Values, by="Code")
            )
      )

answered May 02 '19 at 14:20

Parfait

104,375
17
94
125

Merge dataframes or match columns to obtain new column

2 Answers2