logo标签列表

在R中使用data.table进行Vlookup

rdata.tablelookupvlookup
3

首先，让我们从DataTable 1 (DF1)开始：

          date id sales cost city  name
 1: 06/19/2016  1   149  101  MTL Bank1
 2: 06/20/2016  1   150  102  MTL Bank1
 3: 06/21/2016  1   151  104  MTL Bank1
 4: 06/22/2016  1   152  107  MTL Bank1
 5: 06/23/2016  1   155   99  MTL Bank1
 6: 06/19/2016  2    84   55   NY Bank2
 7: 06/20/2016  2    83   55   NY Bank2
 8: 06/21/2016  2    80   56   NY Bank2
 9: 06/22/2016  2    81   57   NY Bank2
10: 06/23/2016  2    97   58   NY Bank2

library(data.table)
DF1 <- data.table(c("06/19/2016", "06/20/2016", "06/21/2016", "06/22/2016", 
                    "06/23/2016", "06/19/2016", "06/20/2016", "06/21/2016",
                    "06/22/2016", "06/23/2016"),
                  c(1, 1, 1, 1, 1, 2, 2, 2, 2, 2),
                  c(149, 150, 151, 152, 155, 84, 83, 80, 81, 97),
                  c(101, 102, 104, 107, 99, 55, 55, 56, 57, 58),
                  c("MTL", "MTL", "MTL", "MTL", "MTL", "NY", "NY", 
                    "NY", "NY", "NY"))
colnames(DF1) <- c("date", "id", "sales", "cost", "city")

我希望您能够使用查找表添加上面显示的名称列，格式如下： 

   id  name start_date   end_date status
1:  1 Bank1 06/19/2016 06/25/2016      0
2:  2 Bank2 06/27/2016 06/27/2017      0
3:  3 Bank3 06/22/2016 06/24/2017      1
4:  4 Bank3 06/23/2016 12/23/2016      1

lookup <- data.table(c(1, 2, 3, 4),
                     c("Bank1", "Bank2", "Bank3", "Bank3"),
                     c("06/19/2016", "06/27/2016", "06/22/2016", "06/23/2016"),
                     c("06/25/2016", "06/27/2017", "06/24/2017", "12/23/2016"),
                     c("0", "0", "1", "1"))
colnames(lookup) <- c("id", "name", "start_date", "end_date", "status")

在这种情况下，我会使用id来查找名称。当我尝试使用merge时，在DF1中总是有包含NA的新行。
您的查询需要开始和结束日期以及状态，还是仅需要ID和名称这两列就足够了？ - lmo
id和name是必要的列，但在运行脚本之前我不知道所有的列，但我确定我们将有一个id列和一个日期列。 - Alexis
1
你应该使用 setnames 来通过引用添加名称。 - Frank
1
无论如何，我认为你可以/应该只需执行DF1[lookup, on="id", name := i.name]。 - Frank
@Frank，“i.name”中的“i”是如何工作的？ - Max Molina
1
@MaxMolina 当你在执行 x[i, on=, j] 时，这是一种特殊的语法，可以在键值参数 j 下找到相关文档。此外还有一个 x. 前缀。在这种情况下，它非常有用，可以确保我们从 i=lookup 而不是已经存在于 x=DF1 中的预先存在的列 name 中提取列。关于这种语法如何有用的更多信息，请参见：https://dev59.com/tlQJ5IYBdhLWcg3wNjK4 - Frank
2个回答
4
DF1<-merge(DF1, lookup[,.(id, name)], by='id', all.x=TRUE, all.y=FALSE)

我认为合并命令是您在这里寻找的，但是您缺少了all.y = FALSE部分。我们在这里所做的是将DF1与查找数据表合并，并告诉R包括x中的所有行，但只包括与x（即DF1）匹配的y中的行，其中y是查找表。lookup [,.（id，name）]表示我们只想要列id（与DF1匹配）和列'name'。如果DF1中有未能与lookup匹配的行，则它们会因为all.x = TRUE而显示为NA。
如果我设置 allow.cartesian = TRUE，那么当初始行数为83428时，最终将有145368行。 - Alexis
当我复制并粘贴了您的前两个代码段以及我的答案时，它对我起作用。您愿意在这里分享您的实际代码吗？您是否在运行library(data.table)后运行了我的合并命令？ - Austin
它对我也有效，但当我尝试在我的真实数据集上时，出现了这个错误。 - Alexis
是否存在两个银行名称共享一个ID的情况？ - Austin
如果您的查找表中有一行ID为2，名称为BANK2，另一行ID为2，名称为bank2，则会出现错误。如果是这个问题，您应该清理数据。我会将所有银行名称转换为小写字母x<-tolower(x)，并可能删除空格x<-gsub(" ", "", x)，然后运行DF1<-merge(DF1, unique(lookup[,.(id, name)]), by='id', all.x=TRUE, all.y=FALSE)。如果您的数据中有像bank1、bank1corp、bank1co、bank1corporation等内容，那么这将很快变得复杂，但您可以使用大量的gsub()语句来解决它。 - Austin
我不得不使用@lmo的答案的一部分。如果我使用unique(lookup[, .(id, name)])，它可以工作。 - Alexis
2

这里有一个略微不同的设置：我假设id对于两个数据集都是唯一的，并且查找数据包含在您的主数据集中存在的所有id。

我将查找子集缩小到仅包括id和名称。为确保查找中没有重复项（多个日期），我使用unique。

DF1[unique(lookup[, .(id, name)]), on="id"][!is.na(sales)]

    id       date sales cost city  name
 1:  1 06/19/2016   149  101  MTL Bank1
 2:  1 06/20/2016   150  102  MTL Bank1
 3:  1 06/21/2016   151  104  MTL Bank1
 4:  1 06/22/2016   152  107  MTL Bank1
 5:  1 06/23/2016   155   99  MTL Bank1
 6:  2 06/19/2016    84   55   NY Bank2
 7:  2 06/20/2016    83   55   NY Bank2
 8:  2 06/21/2016    80   56   NY Bank2
 9:  2 06/22/2016    81   57   NY Bank2
10:  2 06/23/2016    97   58   NY Bank2

这被称为左连接。我在末尾使用 [!is.na()] 来删除银行3和4，因为它们在主数据集中没有观测值。

谢谢。通过使用您的解决方案的一部分和@Austin的解决方案，我能够得到我的答案。 - Alexis
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接