按组填补缺失的日期

Question

按组填补缺失的日期

4

我有一个数据集，看起来像这样：

shop_id,item_id,time,value
150,1,2015-07-10,3
150,1,2015-07-11,5
150,1,2015-07-13,2
150,2,2015-07-10,15
150,2,2015-07-12,12

每个由“shop_id”和“item_id”定义的组中都存在缺失的日期。

我希望在每个组内将这个不规则的时间序列扩展为一个连续的时间序列，使其包含连续的日期：

shop_id,item_id,time,value
150,1,2015-07-10,3
150,1,2015-07-11,5
150,1,2015-07-12,0 # <~~ added
150,1,2015-07-13,2
150,2,2015-07-10,15
150,2,2015-07-11,0 # <~~ added
150,2,2015-07-12,12

对于添加的日期，相应的值应该为零。我读过非常类似的问题（使用R或SQL合并），但我看到的大多数解决方案都不涉及GROUP BY。

基本上，我可以访问SQL数据库/我可以将其导出为CSV进行操作，最好是在C#中。希望找到可以进行此类数据操作的C#库，但找不到任何内容。

任何建议或帮助都将不胜感激！

- Jia Jian Goi

1

所以，您想要基于 [tag:R] 的解决方案还是基于 [tag:SQL] 的解决方案，或者只是单纯的 C#？如果您不需要 R，那么请删除标签，因为这个问题就完全没有涉及到 R。此外，您尝试过使用 C# 做过任何事情吗？即使没有库（可以查看 .NET DataSet），也可以手动打开和解析文件。 - LinkBerest

为了在R中模拟GROUP BY，您可以使用plyr包来聚合数据。尝试使用函数daply（plyr手册，第17页）。 - noumenal

抱歉没有表达清楚 - 实际上任何解决方案都可以！ - Jia Jian Goi

3个回答

3

这是一个基于SQL的解决方案。

首先需要创建一个名为“dates”的表。

日期表查询。请注意，这将在您的数据库中创建一个物理表。

;with cte as
(
select cast('2000-01-01' as datetime) as Dates -- Start date 
union all
select dateadd(MM,1,Dates) 
from cte 
where Dates < '2099-12-01' -- End date
)
select * 
INTO Date_table 
from CTE

接下来，您需要使用左外连接将表格与Date_table进行连接，以获取缺失的日期。

SELECT A.shop_id, 
       A.item_id, 
       DT.dates, 
       Isnull(Y.value, 0) 
FROM   date_table DT 
       CROSS JOIN(SELECT DISTINCT shop_id, 
                                  item_id 
                  FROM   yourtable) A 
       LEFT OUTER JOIN yourtable Y 
                    ON t.[time] = DT.dates 
                       AND A.shop_id = Y.shop_id 
                       AND A.item_id = Y.item_id

- Pரதீப்

1

这是一个涉及编程的解决方案，使用了来自 padr 的 fill_by_value 方法：

library(dplyr)
library(tidyr)
library(padr)

df %>%
  mutate(time = as.Date(time)) %>%
  group_by(item_id) %>%
  pad() %>%              # from padr
  fill(shop_id) %>%      # from tidyr
  fill_by_value(value)   # from padr

Result:

# A tibble: 7 x 4
# Groups:   item_id [2]
  shop_id item_id       time value
    <int>   <int>     <date> <dbl>
1     150       1 2015-07-10     3
2     150       1 2015-07-11     5
3     150       1 2015-07-12     0
4     150       1 2015-07-13     2
5     150       2 2015-07-10    15
6     150       2 2015-07-11     0
7     150       2 2015-07-12    12

数据：

df = read.table(text = "shop_id,item_id,time,value
150,1,2015-07-10,3
150,1,2015-07-11,5
150,1,2015-07-13,2
150,2,2015-07-10,15
150,2,2015-07-12,12", header = TRUE, sep = ",")

- acylam

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- akrun · Accepted Answer

你可以使用 R 中的 data.table。假设 'time' 列是 'Date' 类型，

library(data.table)#v1.9.5+
DT1 <- setDT(df1)[, list(time=seq(min(time), max(time), by ='day')),
                    by =.(shop_id, item_id)]
setkeyv(df1, names(df1)[1:3])[DT1][is.na(value), value:=0]
#   shop_id item_id       time value
#1:     150       1 2015-07-10     3
#2:     150       1 2015-07-11     5
#3:     150       1 2015-07-12     0
#4:     150       1 2015-07-13     2
#5:     150       2 2015-07-10    15
#6:     150       2 2015-07-11     0
#7:     150       2 2015-07-12    12

在开发版本中，你也可以不设置“key”就这样做。安装开发版本的说明在这里

 df1[DT1, on =c('shop_id', 'item_id', 'time')][is.na(value), value:=0]
 #   shop_id item_id       time value
 #1:     150       1 2015-07-10     3
 #2:     150       1 2015-07-11     5
 #3:     150       1 2015-07-12     0
 #4:     150       1 2015-07-13     2
 #5:     150       2 2015-07-10    15
 #6:     150       2 2015-07-11     0
 #7:     150       2 2015-07-12    12

如@Arun所建议的那样，更高效的选择是

 DT1[, value := 0L][df1, value := i.value, on = c('shop_id', 'item_id', 'time')]
 DT1