Adding a column to a data frame based on certain criteria?

Question

Assuming the following data frame:

df <- (data.frame(ID = c("1", "2", "3"),
                               Drink = c("Cola", "Pepsi", "Fanta")))

Output:

     ID  Drink
1     1   Cola        
2     2  Pepsi      
3     3  Fanta

I then have another data frame that looks something like this:

df2 <- (data.frame(Drink = c("Cola", "Fanta", "Cola", "Pepsi", "Pepsi")))

     Drink
1     Cola        
2    Fanta
3     Cola
4    Pepsi
5    Pepsi

I would like to create an extra column in this data frame that contains the IDs corresponding to whatever drink it specified in the row (based on the IDs in the first data frame (df)). So that the outcome looks like this:

     ID  Drink
1     1   Cola        
2     3  Fanta
3     1   Cola
4     2  Pepsi
5     2  Pepsi

score 2 · Accepted Answer · answered Mar 31 '18 at 21:24

2

Using base R only you can do the following.
Note that in order to get the original order of df2, you will need to order df2$Drink and then invert the process.

inx <- order(df2$Drink)
merge(df, df2)[order(inx), 2:1]
#  ID Drink
#1  1  Cola
#3  3 Fanta
#2  1  Cola
#4  2 Pepsi
#5  2 Pepsi

answered Mar 31 '18 at 21:24

Rui Barradas

70,273
8
34
66

Thanks for the reply! Unfortunately, this merge function seems to ruin my later functions, so I'm not sure if I'm able to implement this. Isn't there a simple VLOOKUP feature that you know of instead? – WoeIs Mar 31 '18 at 21:50
Try `df$ID[match(df2$Drink, df$Drink)]`. – Rui Barradas Apr 01 '18 at 05:33

score 1 · Answer 2 · answered Mar 31 '18 at 22:39

1

You can try left_join or inner_join from dplyr.

library(dplyr)

df2 %>% left_join(df, by="Drink")

# Drink ID
# 1  Cola  1
# 2 Fanta  3
# 3  Cola  1
# 4 Pepsi  2
# 5 Pepsi  2

answered Mar 31 '18 at 22:39

MKR

19,739
4
23
33

Adding a column to a data frame based on certain criteria?

2 Answers2