时间戳

首页 > 解决方案 > R data.table 如何根据另一列的值从许多列中的一个（按列名称）获取值

r - R data.table 如何根据另一列的值从许多列中的一个（按列名称）获取值

问题描述

我正在尝试通过引用列名从 R 中的 data.table 中提取特定值

require(data.table)

# Create data.frame

cohort = c("cohort1", "cohort2", "cohort3")
year = c(2019, 2018, 2020)
item_2018 = c("alpha", "beta", "gamma")
item_2019 = c("banana", "apples", "oranges")
item_2020 = c("Tim", "Daniel","Simon")
desired_result = c("banana", "beta", "Simon")  # the values in this column I want to programatically grab from the relevant column before

cohorts <- data.frame(cohort,year, item_2018, item_2019, item_2020, desired_result)



setDT(cohorts) # turn the data.frame into a data.table

setkey(cohorts, year)  # setting the key for the data.table (not sure if this is necessary)


# CALCULATE NEW FIELD (attempts - not working)

# trying to populate new column "result_attempt_1" with : c("banana", "beta", "Simon")

cohorts[, result_attempt_1 := get(paste0("item_", year)), by = year] # this returns c("Simon", "Simon", "Simon") rather than  c("banana", "beta", "Simon") 

cohorts[, result_attempt_2 := .SD[, get(paste0("item_", year)), by = year]] # very wrong

cohorts[, result_attempt_3 := .SD[, get(paste0("item_", year)), by = get(paste0("item_", year))]] # very wrong

我希望“desired_results”列中的值最终出现在“result_attempt”列中。我得到的最接近的是为每条记录/行重复的最后一个正确结果。

任何想法如何实现这一目标？非常感谢。

标签： rdata.tablelookup

解决方案

使用的另一个选项data.table::melt，匹配 item_year 然后通过引用查找和更新：

cohorts[
    melt(cohorts, measure.vars=patterns("^item"), variable.factor=FALSE)[, 
        value[variable==paste0("item_", year)], by=.(cohort)],
    on=.(cohort), desired_result := V1]

推荐阅读