r - 使用 case_when() 分配两个新列，而不是一个

Question

我有这个示例数据:

df <- tibble(
  "City1" = c("New York", "Boston", "Chicago"),
  "City2" = c("Chicago", "Cleveland", "Atlanta"))

假设 City1 是起点，City2 是目的地。即，一个人从纽约前往芝加哥。

我想添加一列表示起始纬度和一列表示起始经度，并对目的地城市执行相同的操作。总之，我想要四个新专栏。我已经有了坐标。

如何分配坐标？我尝试过使用 case_when，但我不确定如何将坐标传递到多个列。做一栏很容易:

library(tidyverse)

# The numbers after the cities are the latitudes
df <- df %>% 
  mutate(
   City1_lat = case_when(
    City1 == 'New York' ~ 40.7128,
    City1 == 'Boston' ~ 42.3601,
    City1 == 'Chicago' ~ 41.8781
  )
 )

如何扩展它以添加到 City1_lon 列中？尝试尽可能简化这一过程，因为我有几千行出发地/目的地。 dplyr 或 base 解决方案均有效。我会将其扩展到目的地城市 City2。供引用:

New York: 40.7128, 74.0060
Boston: 42.3601, 71.0589
Chicago: 41.8781, 87.6298
Cleveland: 41.4993, 81.6944
Atlanta: 33.7490, 84.3880

Answer 1

将您的城市数据放在这样的数据框中:

> city
       City     lat    long
1  New York 40.7128 74.0060
2    Boston 42.3601 71.0589
3   Chicago 41.8781 87.6298
4 Cleveland 41.4993 81.6944
5   Atlanta 33.7490 84.3880

使用match在表格中查找城市名称，提取经纬度，重命名后得到:

> setNames(city[match(df$City1, city$City), c("lat","long")],c("City1lat","City1long"))
  City1lat City1long
1  40.7128   74.0060
2  42.3601   71.0589
3  41.8781   87.6298

> setNames(city[match(df$City2, city$City), c("lat","long")],c("City2lat","City2long"))
  City2lat City2long
3  41.8781   87.6298
4  41.4993   81.6944
5  33.7490   84.3880

您可以将其cbind到您的原始数据上:

> df = cbind(df, setNames(city[match(df$City1, city$City), c("lat","long")],c("City1lat","City1long")), setNames(city[match(df$City2, city$City), c("lat","long")],c("City2lat","City2long")))
> df
     City1     City2 City1lat City1long City2lat City2long
1 New York   Chicago  40.7128   74.0060  41.8781   87.6298
2   Boston Cleveland  42.3601   71.0589  41.4993   81.6944
3  Chicago   Atlanta  41.8781   87.6298  33.7490   84.3880