R:使用另一个数据表的值更新数据表中的NA [英] R: Updating NAs in a data table with values of another data table

问题描述

有两个具有以下结构的数据表:

There are two data tables of the following structure:

 DT1 <- data.table(ID=c("A","B","C"), P0=c(1,10,100), key="ID")
 DT2 <- data.table(ID=c("B","B","B","A","A","A","C","C","C"), t=rep(seq(0:2),3), P=c(NA,30,50,NA,4,6,NA,200,700)) 

在数据表DT2中，列P中的所有NA都应由数据表DT1中的值P0更新.

In data tableDT2all NAs in column P shall be updated by values P0 out of data table DT1.

如果DT2由ID像DT1排序，则可以这样解决问题:

If DT2 is ordered by ID like DT1, the problem can be solved like this:

 setorder(DT2,ID)
 idxr <- which(DT2[["t"]]==1)
 set(DT2, i=idxr, j="P", value=DT1[["P0"]])

但是如何在不先订购DT2的情况下将数据表合并"?

But how can the data tables be "merged" without ordering DT2 before?

推荐答案

我们可以加入两个数据集on'ID'，对于'P'中的NA值，我们将'P'分配为'P0'，然后通过将其分配为"NULL"来删除"P0".

We can join the two datasets on 'ID', for NA values in 'P', we assign 'P' as 'P0', and then remove the 'P0' by assigning it to 'NULL'.

library(data.table)#v1.9.6+
DT2[DT1, on='ID'][is.na(P), P:= P0][, P0:= NULL][]

或者就像@DavidArenburg提到的，我们可以在加入"ID"后使用ifelse条件来替换"P"中的NA元素.

Or as @DavidArenburg mentioned, we can use ifelse condition after joining on 'ID' to replace the NA elements in 'P'.

DT2[DT1, P := ifelse(is.na(P), i.P0, P), on = 'ID']

这篇关于R:使用另一个数据表的值更新数据表中的NA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！