时间戳

首页 > 解决方案 > 合并和左连接不断在 R 中重复行项目

r - 合并和左连接不断在 R 中重复行项目

问题描述

我正在努力解决这个我无法解决的问题！我必须数据框（“Master”和“Hours”）。'Master' df 有很多列，但具体如下：

掌握

StoreNumber ... MON TUE WED THU FRI SAT SUN 
     1           0   0   0   0   0   0   0
     2           0   0   0   0   0   0   0
     3           0   0   0   0   0   0   0
     ...

注意：Master df 在 StoreNumber 之间有很多列作为星期几，并且保存了大量数据（大约 3000 个商店）

小时

BranchNumber   Day   TimeDiff
     1         MON   7.50
     1         TUE   6.00
     1         WED   8.50
     1         THU   2.00
     1         FRI   1.00
     1         SAT   2.50
     3         MON   7.50
     3         TUE   6.00
     3         WED   8.50
     3         THU   2.00
     3         FRI   1.00
     3         SAT   2.50
     3         SUN   5.00
    ...

所以我的想法是我试图将“小时”品牌编号与“主”商店编号相匹配。一旦有匹配项，它就会将“小时”表中的“日”列与“大师”表中的星期几匹配......它将为每一行执行此操作，然后用相应的值填充星期几在“TimeDiff”列中...如果商店和分支编号不匹配（如 StoreNumber 2），则应跳过该行并移至下一行。另一种情况，如 BranchNumber '1'，没有 SUNDAY 的数据，因此在“Master”表中，SUNDAY 单元格应保留为 0...这应该适用于一周中的任何一天。

输出应该是“主”表，但包含“小时”表中的所有星期数据。在此示例中，它应如下所示：

    StoreNumber ... MON    TUE   WED   THU   FRI   SAT   SUN 
         1          7.50  6.00  8.50  2.00  1.00  2.50    0
         2           0      0     0     0     0     0     0
         3          7.50  6.00  8.50  2.00  1.00  2.50  5.00
     ...

我尝试过的代码是半工作的，但我不确定它是否是正确的方法。我遇到的最大问题是它复制了第一行所期望的行。例如，输出看起来更像这样。

StoreNumber
    1
    2
    2
    3
    3
    4
    4
    5
    5 
    5

所有都是重复的，有些是三倍的，每 87 列都是相同的......但是重复行的星期几都是 0。

 merged <- Master %>% select(-c("MON","TUE","WED","THU","FRI","SAT","SUN")) %>%
 left_join(
 Hours %>% pivot_wider(names_from = Day, values_from = TimeDiff),
 by = c('StoreNumber' = 'BranchNumber'))

 merged <- merged %>% replace(is.na(.),0)

对不起，这个问题很长，这个问题一直困扰着我一段时间，所以任何帮助/建议将不胜感激

标签： rdataframedata.tablematchunique

解决方案

如果我理解正确，该Master表有很多列，只有需要更新的MON列SUN。

这里有两种使用'在连接中更新data.table的能力的方法。仅通过引用修改相关列，即不复制整个数据对象。它避免来回重塑（或旋转）表格。Master

变体 1

library(data.table)
days <- names(Master)[which(names(Master) == "MON") + (0:6)]
setDT(Master)[, (days) := lapply(.SD, as.double), .SDcols = days]
for (d in days) {
  Master[Hours, on =.(StoreNumber = BranchNumber), (d) := TimeDiff[d == Day], by = .EACHI]
}
Master[]

   StoreNumber OtherCol MON TUE WED THU FRI SAT SUN
1:           1        a 7.5   6 8.5   2   1 2.5   0
2:           2        b 0.0   0 0.0   0   0 0.0   0
3:           3        c 7.5   6 8.5   2   1 2.5   5

解释

  • days包含列的名称。
    days <- names(Master)[which(names(Master) == "MON") + (0:6)]相当于
    days <- c("MON", "TUE", "WED", "THU", "FRI", "SAT", "SUN")
  • data.table更新部分列时需要一致的数据类型。中的天列Master初始化为整数零，但TimeDiffinHours是数字。因此，日期列在Master更新之前被强制加倍。
  • 该for循环遍历每一天的列并为此列执行更新连接。对于每个匹配项 ( by = .EACHI)，Timediff选择相关日期的 。

为了验证Master没有被复制，我们可以调用

data.table::address(Master)

操作前后：地址Master没有变化。

变体 2

这种方法有点精简。它还使用更新连接，但它与变体 1 不同，因为它Hours从长格式重塑（或旋转）到宽格式，并从中删除天数列，Master而不是强制一堆整数零来键入数字：

    library(data.table)
    days <- c("MON", "TUE", "WED", "THU", "FRI", "SAT", "SUN")
    Hours_wide <- dcast(setDT(Hours)[, Day := ordered(Day, levels = days)], BranchNumber ~ Day)
    setDT(Master)[, (days) := NULL][
      Hours_wide, on = .(StoreNumber = BranchNumber), (days) := mget(paste0("i.", days))]
    Master[]

   StoreNumber OtherCol MON TUE WED THU FRI SAT SUN
1:           1        a 7.5   6 8.5   2   1 2.5  NA
2:           2        b  NA  NA  NA  NA  NA  NA  NA
3:           3        c 7.5   6 8.5   2   1 2.5   5

请注意，缺少的元素现在被初始化为/指示，NA这更容易检测到，恕我直言。如果需要，NA可以将 s 转换为另一个数值

Master[, (days) := lapply(.SD, nafill, fill = 0), .SDcols = days][] 

   StoreNumber OtherCol MON TUE WED THU FRI SAT SUN
1:           1        a 7.5   6 8.5   2   1 2.5   0
2:           2        b 0.0   0 0.0   0   0 0.0   0
3:           3        c 7.5   6 8.5   2   1 2.5   5

此方法用于mget(paste0("i.", days))从 中选择天数列Hours。如果连接中的两个 data.tables 中有同名的列，我们可以通过在列名前分别加上 ax.和来区分这些列i.。因此，在这种情况下，x.MON指的MON是第一个 data.table 中的列，并指的是第二个 data.table 中的列。将列名作为字符串并返回各个列的值列表。Masteri.MONMONHours_widemget()

变体 2 - 编辑 1

上面的代码可以简化为

setDT(Master)[, (days) := NULL][
  Hours_wide, on = .(StoreNumber = BranchNumber), (days) := mget(days)][]

   StoreNumber OtherCol MON TUE WED THU FRI SAT SUN
1:           1        a 7.5   6 8.5   2   1 2.5  NA
2:           2        b  NA  NA  NA  NA  NA  NA  NA
3:           3        c 7.5   6 8.5   2   1 2.5   5

因为setDT(Master)[, (days) := NULL]已经删除了列MON，所以列名没有歧义SUN。Master因此，列名MONtoSUN可以在不添加 byi.的情况下使用，因为只有名为MONtoSUN的列位于Hours_wide.

变体 2 - 编辑 2

在 2021-05-10 的开发版本 1.14.1 中，添加了用于对 data.table 进行编程的新接口（参见NEWS 中的第 10 项和对 data.table 的新小插曲编程）。代替get()/建议mget()使用新env参数：

library(data.table) # development version 1.14.1 used
days <- c("MON", "TUE", "WED", "THU", "FRI", "SAT", "SUN")
Hours_wide <- dcast(setDT(Hours)[, Day := ordered(Day, levels = days)], BranchNumber ~ Day)
setDT(Master)[, (days) := NULL][
  Hours_wide, on = .(StoreNumber = BranchNumber), (days) := s,
  env = list(s = as.list(days))][]

   StoreNumber OtherCol MON TUE WED THU FRI SAT SUN
1:           1        a 7.5   6 8.5   2   1 2.5  NA
2:           2        b  NA  NA  NA  NA  NA  NA  NA
3:           3        c 7.5   6 8.5   2   1 2.5   5

变体 3：env参数和fcoalesce()

OP 的预期结果显示0而不是NA. 对于上面的变体 2，这是通过使用nafill().

可以通过使用更新连接fcoalesce()中的函数来避免这个单独的更新步骤：

library(data.table) # development version 1.14.1 used
days <- c("MON", "TUE", "WED", "THU", "FRI", "SAT", "SUN")
Hours_wide <- dcast(setDT(Hours)[, Day := ordered(Day, levels = days)], BranchNumber ~ Day)
setDT(Master)[, (days) := lapply(.SD, as.double), .SDcols = days][
  Hours_wide, on = .(StoreNumber = BranchNumber), (days) := lapply(s, fcoalesce, 0), 
  env = list(s = as.list(paste0("i.", days)))][]

   StoreNumber OtherCol MON TUE WED THU FRI SAT SUN
1:           1        a 7.5   6 8.5   2   1 2.5   0
2:           2        b 0.0   0 0.0   0   0 0.0   0
3:           3        c 7.5   6 8.5   2   1 2.5   5

数据

library(data.table)

Master <- fread("
StoreNumber OtherCol MON TUE WED THU FRI SAT SUN 
     1         a      0   0   0   0   0   0   0
     2         b      0   0   0   0   0   0   0
     3         c      0   0   0   0   0   0   0
", data.table = FALSE)
 
Hours <- fread("
BranchNumber   Day   TimeDiff
     1         MON   7.50
     1         TUE   6.00
     1         WED   8.50
     1         THU   2.00
     1         FRI   1.00
     1         SAT   2.50
     3         MON   7.50
     3         TUE   6.00
     3         WED   8.50
     3         THU   2.00
     3         FRI   1.00
     3         SAT   2.50
     3         SUN   5.00
", data.table = FALSE)

推荐阅读