Merge by a condition

Question

I have this two db:

dfgenus<- c("Coragyps" ,"Elanus", "Elanus", "Patagioenas", "Crotophaga")

so

dfgenus
       Genus
1     Coragyps
2       Elanus
3       Elanus
4  Patagioenas
5   Crotophaga

and

family <-c("Cathartidae", "Accipitridae","Cuculidae", "Columbidae","Psittacidae")
Genus <- c("Coragyps" ,"Elanus", "Crotophaga", "Patagioenas", "Pyrrhura")

sacc<- data.frame(family, genus)
##Sacc db rows are in the right order (the genus belongs to its taxonomic family)

sacc
  family       Genus
1  Cathartidae    Coragyps
2 Accipitridae      Elanus
3    Cuculidae  Crotophaga
4   Columbidae Patagioenas
5  Psittacidae    Pyrrhura

following the info on "sacc", how can I add the correct family for each genus in "dbgenus"?

I've been unsuccesfully trying:

for (i in length(dfgenus)){
if (identical(sacc[i], dfgenus[i])) {
    df$family[i] <- sacc$family[i] 
}   else {
        i-1 
} 
print(df$family) 
}

the output should be:

df
        family       Genus
1  Cathartidae    Coragyps
2 Accipitridae      Elanus
3 Accipitridae      Elanus
4   Columbidae Patagioenas
5    Cuculidae  Crotophaga

score 1 · Answer 1 · answered Jul 12 '18 at 00:40

1

Solution using dplyr:

library(dplyr)

dbgenus<- data.frame(genus = c("Coragyps" ,"Elanus", "Elanus", "Patagioenas", "Crotophaga"))
family <-c("Cathartidae", "Accipitridae","Cuculidae", "Columbidae","Psittacidae")
genus <- c("Coragyps" ,"Elanus", "Crotophaga", "Patagioenas", "Pyrrhura")

sacc<- data.frame(family, genus)

dbgenus %>% left_join(sacc)

answered Jul 12 '18 at 00:40

UpsideDownRide

528
1
4
12

This works but consider `stringsAsFactors = FALSE` when creating data frames (or use tibbles) to avoid the warning about factor levels. – neilfws Jul 12 '18 at 00:44
I recommend setting this to FALSE as default using options. – UpsideDownRide Jul 12 '18 at 00:47
Thanks a lot for this, i've had been stuck on that for a long time :) – JUAN PABLO MONGUI Jul 12 '18 at 02:45

score 1 · Answer 2 · answered Jul 12 '18 at 00:40

There are several ways to achieve your result. None of them should involve for-loops :)

If you made dfgenus a data frame (with one column), you could investigate the merge() function, or a join function from the dplyr package.

But using your data as it stands, you can use match():

newdf <- data.frame(Genus  = dfgenus, 
                    Family = sacc[match(dfgenus, sacc$Genus), "family"])

        Genus       Family
1    Coragyps  Cathartidae
2      Elanus Accipitridae
3      Elanus Accipitridae
4 Patagioenas   Columbidae
5  Crotophaga    Cuculidae

match returns the matching row numbers from sacc, which are then used to return the subset from the family column.

Merge by a condition

2 Answers2