您所在的位置： 首页 > 最新热文 > 计算机探索

    通过填充丢失的日期和对称地上下迭代日期以找到r中最接近的值来表示补偿日期、对称、最接近、上下

    2023-09-04 02:24:10 作者：忍住孤独

    我需要计算每个id的可用日期之间的所有丢失日期，然后对称地上下移动以计算丢失。此外，我并不总是需要两个日期之间的平均值，例如：当我上下移动两个日期时，我只看到一个值，那么我就会计算那个值。

    df1 <- data.frame(id = c(11,11,11,11,11,11,11,11),
                  Date = c("2021-06-01", "2021-06-05", "2021-06-08", "2021-06-09", "2021-06-14", "2021-06-16", "2021-06-20", "2021-06-21"),
                  price = c(NA, NA,100, NA, 50, NA, 200, NA)
)
    @lovalery在对称迭代上有一个很好的解决遗漏归罪的方法 how to groupby and take mean of value by symetrically looping forward and backward on the date value in r

    在上面的解决方案中，使用了存在的日期，但当两者之间缺少大量日期时，这可能是一个问题。 因此，我希望在两者之间插入所有缺少的日期，然后在两个方向上对称移动，直到我在两个方向上至少得到一个值，我需要保留它，如果有两个值，我需要平均值。

    3种自动记录数据录入时间的方法,学会它们,快速提高工作效率

    更新：我们还需要考虑价格只出现在第一个日期或最后一个日期的情况。此外，如果在多个日期中存在相同的价格

    df1 <- data.frame(id = c(11,11,11,11,11,11,11,11,
                     12,12,12,
                     13,13,13),
              Date = c("2021-06-01", "2021-06-05", "2021-06-08", "2021-06-09", "2021-06-14", "2021-06-16", "2021-06-20", "2021-06-21",
                       "2021-07-01","2021-07-03","2021-07-05",
                       "2021-08-01","2021-08-03","2021-08-05"),
              price = c(200, NA,100, NA, 50, NA, 200, NA,
                        10,NA,NA,
                        NA,NA,20)

    )

    我使用了函数na_imputation_date_v2 by@lovalery

    df1 <- setDT(df1)
df2 <- NA_imputations_dates_v2(df1)
df3 <- merge(df1,df2,by = c("id","Date"),all.x = T)

    推荐答案

    请在下面使用data.table和padr库找到一个可能的解决方案。

    我构建了一个函数以使其更易于使用。

    Reprex

    您的数据集#1 
    df1 <- data.frame(id = c(11,11,11,11,11,11,11,11),
                  Date = c("2021-06-01", "2021-06-05", "2021-06-08", "2021-06-09", "2021-06-14", "2021-06-16", "2021-06-20", "2021-06-21"),
                  price = c(NA, NA,100, NA, 50, NA, 200, NA))
    NA_imputations_dates()函数代码 
    library(data.table)
library(padr)

NA_imputations_dates <- function(x) {
  
  setDT(x)[, Date := as.Date(Date)]
  
  x <- pad(x, interval = "day", group = "id")
  
  setDT(x)[, rows := .I]
  
  z <- x[, .I[!is.na(price)]]
  
  id_1 <- z[-length(z)]
  id_2 <- z[-1]
  
  values <- x[z, .(price = price, id = id)]
  values_1 <- values[-nrow(values)]
  names(values_1) <- c("price_1", "id_o1")
  values_2 <- values[-1]
  names(values_2) <- c("price_2", "id_o2")
  
  subtract <- z[-1] - z[-length(z)]
  
  r <- data.table(id_1, values_1, id_2, values_2, subtract)
  
  r <- r[, `:=` (id_mean = fifelse(subtract > 2 & subtract %% 2 == 0, id_1+(subtract/2), (id_1+id_2)/2),
                 mean = fifelse(subtract >= 2 & subtract %% 2 == 0 & id_o1 == id_o2, (price_1+price_2)/2, NA_real_))
         ][, `:=` (price_1 = NULL, id_1 = NULL, id_o1 = NULL, id_2 = NULL, price_2 = NULL, id_o2 = NULL, subtract = NULL)
           ][x, on = .(id_mean = rows)][, dummy := cumsum(!is.na(mean)), by = .(id)]
  
  h <-  r[, .(price = na.omit(price)), by = .(dummy)]
  
  Results <- r[, price := NULL
               ][h, on = .(dummy)
                 ][, price := fifelse(!is.na(mean), mean, price)
                   ][, `:=` (id_mean = NULL, mean = NULL, dummy = NULL)][]
  
  return(Results)
}
    NA_imputations_dates()函数的输出 
    NA_imputations_dates(df1)
#>     id       Date price
#>  1: 11 2021-06-01   100
#>  2: 11 2021-06-02   100
#>  3: 11 2021-06-03   100
#>  4: 11 2021-06-04   100
#>  5: 11 2021-06-05   100
#>  6: 11 2021-06-06   100
#>  7: 11 2021-06-07   100
#>  8: 11 2021-06-08   100
#>  9: 11 2021-06-09   100
#> 10: 11 2021-06-10   100
#> 11: 11 2021-06-11    75
#> 12: 11 2021-06-12    50
#> 13: 11 2021-06-13    50
#> 14: 11 2021-06-14    50
#> 15: 11 2021-06-15    50
#> 16: 11 2021-06-16    50
#> 17: 11 2021-06-17   125
#> 18: 11 2021-06-18   200
#> 19: 11 2021-06-19   200
#> 20: 11 2021-06-20   200
#> 21: 11 2021-06-21   200
#>     id       Date price

    由reprex package(v2.0.1)在2021-12-12创建

    编辑函数以处理更通用的数据集#2

    作为您的评论的后续内容，请在下面找到该函数的修改版本(即NA_imputations_dates_v2())，以处理您的新数据集提供的更一般情况(即dataset #2)。

    Reprex

    您的数据集#2 
    df1 <- data.frame(id = c(11,11,11,11,11,11,11,11,
                         12,12,12,
                         13,13,13),
                  Date = c("2021-06-01", "2021-06-05", "2021-06-08", "2021-06-09", "2021-06-14", "2021-06-16", "2021-06-20", "2021-06-21",
                           "2021-07-01","2021-07-03","2021-07-05",
                           "2021-08-01","2021-08-03","2021-08-05"),
                  price = c(NA, NA,100, NA, 50, NA, 200, NA,
                            10,NA,NA,
                            NA,NA,20))
    NA_imputations_dates_v2()函数代码 
    library(data.table)
library(padr)  
  
NA_imputations_dates_v2 <- function(x) {
  
  setDT(x)[, Date := as.Date(Date)]
  
  x <- pad(x, interval = "day", group = "id")

  setDT(x)[, rows := .I]
  
  z <- x[, .I[!is.na(price)]]
  
  id_1 <- z[-length(z)]
  id_2 <- z[-1]
  
  values <- x[z, .(price = price, id = id)]
  values_1 <- values[-nrow(values)]
  names(values_1) <- c("price_1", "id_o1")
  values_2 <- values[-1]
  names(values_2) <- c("price_2", "id_o2")
  
  subtract <- z[-1] - z[-length(z)]
  
  r <- data.table(id_1, values_1, id_2, values_2, subtract)

  r <- r[, `:=` (id_mean = fifelse(subtract > 2 & subtract %% 2 == 0 & id_o1 == id_o2, id_1+(subtract/2), NA_real_),
                 mean = fifelse(subtract >= 2 & subtract %% 2 == 0 & id_o1 == id_o2, (price_1+price_2)/2, NA_real_))
         ][, `:=` (price_1 = NULL, id_1 = NULL, id_o1 = NULL, id_2 = NULL, price_2 = NULL, id_o2 = NULL, subtract = NULL)
           ][x, on = .(id_mean = rows)][, dummy := cumsum(!is.na(mean)), by = .(id)]
  
  h <-  r[, .(price = na.omit(price)), by = .(dummy, id)]
  
  Results <- r[, price := NULL
               ][h, on = .(dummy, id)
                 ][, price := fifelse(!is.na(mean), mean, price)
                   ][, `:=` (id_mean = NULL, mean = NULL, dummy = NULL)][]
  
  return(Results)
}
    NA_imputations_dates_v2()函数的输出 
    NA_imputations_dates_v2(df1)
#>     id       Date price
#>  1: 11 2021-06-01   100
#>  2: 11 2021-06-02   100
#>  3: 11 2021-06-03   100
#>  4: 11 2021-06-04   100
#>  5: 11 2021-06-05   100
#>  6: 11 2021-06-06   100
#>  7: 11 2021-06-07   100
#>  8: 11 2021-06-08   100
#>  9: 11 2021-06-09   100
#> 10: 11 2021-06-10   100
#> 11: 11 2021-06-11    75
#> 12: 11 2021-06-12    50
#> 13: 11 2021-06-13    50
#> 14: 11 2021-06-14    50
#> 15: 11 2021-06-15    50
#> 16: 11 2021-06-16    50
#> 17: 11 2021-06-17   125
#> 18: 11 2021-06-18   200
#> 19: 11 2021-06-19   200
#> 20: 11 2021-06-20   200
#> 21: 11 2021-06-21   200
#> 22: 12 2021-07-01    10
#> 23: 12 2021-07-02    10
#> 24: 12 2021-07-03    10
#> 25: 12 2021-07-04    10
#> 26: 12 2021-07-05    10
#> 27: 13 2021-08-01    20
#> 28: 13 2021-08-02    20
#> 29: 13 2021-08-03    20
#> 30: 13 2021-08-04    20
#> 31: 13 2021-08-05    20
#>     id       Date price

    由reprex package(v2.0.1)于2021-12-14创建

    第二次编辑函数以处理更通用的数据集#3

    作为您第二条评论的后续内容，请在下面找到该函数的修改版本(即NA_imputations_dates_v3())，以处理您的新数据集提供的更一般情况(即dataset #3)。

    Reprex

    您的数据集#3 
    df1 <- data.frame(id = c(11,11,11,11,11,11,11,11,
                         12,12,12,
                         13,13,13),
                  Date = c("2021-06-01", "2021-06-05", "2021-06-08", "2021-06-09", "2021-06-14", "2021-06-16", "2021-06-20", "2021-06-21",
                           "2021-07-01","2021-07-03","2021-07-05",
                           "2021-08-01","2021-08-03","2021-08-05"),
                  price = c(NA, NA,100, NA, 50, NA, 200, 200,
                            10,NA,NA,
                            NA,NA,20))
    NA_imputations_dates_v3()函数代码 
    library(data.table)
library(padr)  
  
NA_imputations_dates_v3 <- function(x) {
  
  setDT(x)[, Date := as.Date(Date)]
  
  x <- pad(x, interval = "day", group = "id")
  
  setDT(x)[, rows := .I]
  
  z <- x[, .I[!is.na(price)]]
  
  id_1 <- z[-length(z)]
  id_2 <- z[-1]
  
  values <- x[z, .(price = price, id = id)]
  values_1 <- values[-nrow(values)]
  names(values_1) <- c("price_1", "id_o1")
  values_2 <- values[-1]
  names(values_2) <- c("price_2", "id_o2")
  
  subtract <- z[-1] - z[-length(z)]
  
  r <- data.table(id_1, values_1, id_2, values_2, subtract)
  
  r <- r[, `:=` (id_mean = fifelse(subtract > 2 & subtract %% 2 == 0 & id_o1 == id_o2, id_1+(subtract/2), NA_real_),
                 mean = fifelse(subtract >= 2 & subtract %% 2 == 0 & id_o1 == id_o2, (price_1+price_2)/2, NA_real_))
         ][, `:=` (price_1 = NULL, id_1 = NULL, id_o1 = NULL, id_2 = NULL, price_2 = NULL, id_o2 = NULL, subtract = NULL)
           ][x, on = .(id_mean = rows)][, dummy := cumsum(!is.na(mean)), by = .(id)]
  
  r <- r[, price_lag := shift(price, 1), by = .(dummy, id)]
  
  h <-  r[, .(price = na.omit(price)), by = .(dummy, id, price_lag)]
  
  h <- h[h[,.I[is.na(price_lag)]]][, price_lag := NULL]
  
  Results <- r[, `:=` (price = NULL, price_lag = NULL)
               ][h, on = .(dummy, id)
                 ][, price := fifelse(!is.na(mean), mean, price)
                   ][, `:=` (id_mean = NULL, mean = NULL, dummy = NULL)][]
  
  return(Results)
}
    NA_imputations_dates_v3()函数的输出 
    NA_imputations_dates_v3(df1)  
#>     id       Date price
#>  1: 11 2021-06-01   100
#>  2: 11 2021-06-02   100
#>  3: 11 2021-06-03   100
#>  4: 11 2021-06-04   100
#>  5: 11 2021-06-05   100
#>  6: 11 2021-06-06   100
#>  7: 11 2021-06-07   100
#>  8: 11 2021-06-08   100
#>  9: 11 2021-06-09   100
#> 10: 11 2021-06-10   100
#> 11: 11 2021-06-11    75
#> 12: 11 2021-06-12    50
#> 13: 11 2021-06-13    50
#> 14: 11 2021-06-14    50
#> 15: 11 2021-06-15    50
#> 16: 11 2021-06-16    50
#> 17: 11 2021-06-17   125
#> 18: 11 2021-06-18   200
#> 19: 11 2021-06-19   200
#> 20: 11 2021-06-20   200
#> 21: 11 2021-06-21   200
#> 22: 12 2021-07-01    10
#> 23: 12 2021-07-02    10
#> 24: 12 2021-07-03    10
#> 25: 12 2021-07-04    10
#> 26: 12 2021-07-05    10
#> 27: 13 2021-08-01    20
#> 28: 13 2021-08-02    20
#> 29: 13 2021-08-03    20
#> 30: 13 2021-08-04    20
#> 31: 13 2021-08-05    20
#>     id       Date price

    由reprex package(v2.0.1)于2021-12-14创建

    上一篇：使用skLearning IterativeImputer类为鼠标输入遗漏的值鼠标、skLearning、IterativeImputer

    下一篇：连接两个具有两个ID协议的数据集，但R中缺少数据两个、数据、协议、ID

    相关推荐
     
    猜您喜欢
    《误杀》素察尸身躲在何处？误杀结论领会,你大概没瞅懂！

    《误杀》素察尸身躲在何处？误杀结论领会,

    精彩图集
    精彩推荐
    图片推荐

    探秘家（tanmijia.com），深层探秘世界自然奇观未解之谜，揭秘宇宙空间、人类、生物未解之谜,世界传闻、奇闻异事ufo未解之谜。有效真实科学探索天下奇观猎奇世界。

    © 2016-2021 探秘家. All Rights Reserved 浙ICP备20029812号-3