填写dbplyr中缺少的值

Question

我在数据库中有如下数据：

ID    month_year   value
1     01/06/2014   10
1     01/07/2014   100
1     01/10/2014   25

我想填写已过的几个月：

ID    month_year   value
1     01/06/2014   10
1     01/07/2014   100
1     01/08/2014   NA
1     01/09/2014   NA
1     01/10/2014   25

我使用BigQuery包来使用dbplyr。我知道这是可能在BigQuery与UNNEST(GENERATE_DATE_ARRAY(.但我不能用dbplyr来解决这个问题。可能与这个github问题有关

Answer 1

您可以通过外部连接来完成此操作。

list_of_dates = data_with_missing_dates %>%
  select(month_year) %>%
  distinct()

data_with_filled_dates = data_with_missing_dates %>%
  right_join(list_of_dates, by = "month_year")

这些都是标准的dplyr命令，因此dbplyr可以将它们转换为bigquery。

以上假设您的现有数据包括最终输出中所需的所有日期(但分布在不同的ID值上)，因此可以从初始数据集构造list_of_dates。

如果您希望在最终数据中显示的初始数据中的任何ID没有出现日期，则需要以其他方式构造list_of_dates。在这种情况下，即使是complete()本身也是不够的。

编辑，因此每个ID都有自己的开始和结束

list_of_dates = data_with_missing_dates %>%
  select(month_year) %>%
  distinct() %>%
  mutate(placeholder = 1)

date_limits = data_with_missing_dates %>%
  group_by(ID) %>%
  summarise(min_date = min(month_year),
            max_date = max(month_year)) %>%
  mutate(placeholder = 1)

data_with_filled_dates = date_limits %>%
  outer_join(list_of_dates, by = "placeholder") %>%
  filter(min_date <= month_year,
         max_date >= month_year) %>%
  select(ID, month_year) %>%
  left_join(data_with_missing_dates, by = c("ID", "month_year"))