根据其他列创建新的data.table列 [英] Create new data.table columns based on other columns

问题描述

我有一个 data.table ，其中包含一些州名的缩写和县名.我想得到大约.每行的 ggplot2 :: map_data('county')坐标.

I have a data.table containing some state name abbreviations and county names. I want to get approx. coordinates from ggplot2::map_data('county') for each row.

我可以使用:= 用多行代码顺序执行此操作，但是我只想调用一个函数.

I can do this sequentially with multiple lines of code using := but I would like to make only one function call.

以下是我尝试过的内容:

Below is what I've tried:

数据:

library(data.table)
library(ggplot2)

> dput(dt[1:20, .(state, county, prime_mover)])
structure(list(state = c("AZ", "AZ", "CA", "CA", "CA", "CT", 
"FL", "IN", "MA", "MA", "MA", "MN", "NJ", "NJ", "NJ", "NY", "NC", 
"SC", "TN", "TX"), county = c("Maricopa", "Maricopa", "Los Angeles", 
"Orange", "Los Angeles", "Fairfield", "Hillsborough", "Morgan", 
"Barnstable", "Nantucket", "Essex", "Dakota", "Cape May", "Salem", 
"Middlesex", "Kings", "Buncombe", "Anderson", "Shelby", "Tarrant"
), prime_mover = c("GT", "GT", "CT", "CT", "CT", "CT", "GT", 
"CT", "GT", "GT", "GT", "GT", "CT", "GT", "CT", "GT", "CT", "CT", 
"CT", "CT")), .Names = c("state", "county", "prime_mover"), row.names = c(NA, 
-20L), class = c("data.table", "data.frame"))

coord_data <- as.data.table(map_data('county'))

代码:

getCoords <- function(state, county){
  prov <- state.name[grep(state, state.abb)]
  ck <- coord_data[region == tolower(prov) & subregion == tolower(county), 
                   .(lon = mean(long), lat = mean(lat))]
  return(list(unname(unlist(ck))))
}

# Testing getCoords
> getCoords('AZ', 'Maricopa')
[[1]]
[1] -111.88668   33.58126

错误:

> dt[, c('lon', 'lat') := lapply(.SD, getCoords), .SDcols = c('state', 'county')]
Error in tolower(county) : argument "county" is missing, with no default
In addition: Warning message:
In grep(state, state.abb) :
  argument 'pattern' has length > 1 and only the first element will be used

我已经看到以下答案，但无法完全理解我做错了什么:

I've seen the following answers but am not able to quite get what I'm doing wrong:

1. 遍历data.table并创建新列基于某些条件
2. R data.table创建具有标准名称的新列
3. 向包含以下内容的data.table添加新列许多变量
4. 向R数据添加多列.一次调用一个表?
5. 在data.table中使用:=分配多列，按组
6. 在data.table中动态创建新列

1. Loop through data.table and create new columns basis some condition
2. R data.table create new columns with standard names
3. Add new columns to a data.table containing many variables
4. Add multiple columns to R data.table in one function call?
5. Assign multiple columns using := in data.table, by group
6. Dynamically create new columns in data.table

我可以通过其他方式(多行， dplyr 甚至是基数R)实现所需的功能，但是我更喜欢使用 data.table 方法.

I am able to achieve what I want by other means (multiple lines, dplyr or even base R) but I prefer to use the data.table approach for this.

推荐答案

我将使用两个 update joins :

library(data.table)
# aggregate coordinates
cols <- c("long", "lat")
agg_coord <- coord_data[, lapply(.SD, mean), .SDcols = cols, by = .(region, subregion)]
# coerce to data.table by reference
setDT(dt)[
  # 1st update join to append region/state.name
  .(state = state.abb, state.name = tolower(state.name)), 
  on = "state", region := state.name][
    # append subregion
    , subregion := tolower(county)][
      # 2nd update join to append coordinates
      agg_coord, on = .(region, subregion), (cols) := .(long, lat)][
        # remove helper columns
        , c("region", "subregion") := NULL]
# print updated dt
dt[]

    state       county prime_mover       long      lat
 1:    AZ     Maricopa          GT -111.88668 33.58126
 2:    AZ     Maricopa          GT -111.88668 33.58126
 3:    CA  Los Angeles          CT -118.29410 34.06683
 4:    CA       Orange          CT -117.73632 33.69611
 5:    CA  Los Angeles          CT -118.29410 34.06683
 6:    CT    Fairfield          CT  -73.35118 41.29633
 7:    FL Hillsborough          GT  -82.47527 27.87826
 8:    IN       Morgan          CT  -86.49791 39.52721
 9:    MA   Barnstable          GT  -70.21598 41.79520
10:    MA    Nantucket          GT  -70.05841 41.29880
11:    MA        Essex          GT  -70.98384 42.64042
12:    MN       Dakota          GT  -93.04962 44.70344
13:    NJ     Cape May          CT  -74.80790 39.15476
14:    NJ        Salem          GT  -75.36532 39.58720
15:    NJ    Middlesex          CT  -74.42345 40.45429
16:    NY        Kings          GT  -73.95052 40.64792
17:    NC     Buncombe          CT  -82.50883 35.62002
18:    SC     Anderson          CT  -82.61956 34.57094
19:    TN       Shelby          CT  -89.99297 35.22379
20:    TX      Tarrant          CT  -97.29396 32.79856

这篇关于根据其他列创建新的data.table列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！