R: Assign or merge using two common values

Question

I have a dataset which has Latitude and Longitude for all US cities. I want to assign Latitude and Longitude to df. However, there are multiple cities with different State. How can I assign Lat and Lon to right city and state?

Cities <- data.frame(City = c("Sunnyvale", "Sunnyvale","Sunnyvale","Sunnyvale"), 
       State = c("CA","MO", "NC", "TX"),
       Lat = c(37.36889,    37.05083,   35.73889,   32.79639 ),
       Lon = c(-122.03528,  -94.495,    -82.13611,  -96.56056))

df <- data.frame(City2 = c("Sunnyvale", "Sunnyvale"),
             State2 = c("CA", "NC"))

This is [tag:r-faq] Duplicate of [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) — M--, Aug 30 '17 at 18:43

score 0 · Answer 1 · answered Aug 30 '17 at 18:37

0

We can use a join on 'State' and 'City' columns

library(data.table)
setDT(df)[Cities, c("Lat", "Lon") := .(Lat, Lon), on = .(City2 = City, State2 = State)]
df
#       City2 State2      Lat        Lon
#1: Sunnyvale     CA 37.36889 -122.03528
#2: Sunnyvale     NC 35.73889  -82.13611

answered Aug 30 '17 at 18:37

akrun

874,273
37
540
662

Would you post your answer to [this](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) r-faq question? I haven't seen one-liner data.table solution there. Thanks. – M-- Aug 30 '17 at 18:42
1

@Masoud I think jangorecki and bgoldst have some excellent posts there though i didn't find the exact use-case as here. You could perhaps edit one of their answers and add this case. I am not sure adding a new answer makes sense there as the answers there are very comprehensive – akrun Aug 30 '17 at 18:45

score 0 · Answer 2 · answered Aug 30 '17 at 18:42

0

Using dplyr's left_join:

> library(dplyr)
> left_join(df, Cities, by = c("City2" = "City", "State2" = "State"))
      City2 State2      Lat        Lon
1 Sunnyvale     CA 37.36889 -122.03528
2 Sunnyvale     NC 35.73889  -82.13611

answered Aug 30 '17 at 18:42

quartin

431
7
9

R: Assign or merge using two common values

2 Answers2