在R数据表中向后填充最后的NA值。

huangapple
go评论94阅读模式
英文:

Backwards fill last instances of NAs in R Data Table

问题

  1. library(data.table)
  3. dt <- data.table(V1 = c(58,NA,NA,NA,NA,13,NA,NA,NA,12,23,NA,12))
  4. dt[, rleid := rleid(V1)]
  5. dt[, num := seq_len(.N), by = rleid]
  6. dt[, max_num := max(num, na.rm = TRUE), by = rleid]
  8. # Fill NAs based on condition
  9. dt[, V2 := ifelse(is.na(V1) & num <= 2, V1, NA), by = rleid]
  11. # Fill NAs forward
  12. dt[, V2 := nafill(V2, type = "locf"), by = rleid]
  14. # Remove the temporary columns
  15. dt[, c("rleid", "num", "max_num") := NULL]
  17. # Result
  18. result <- dt$V2
  19. result

这个代码会更快地填充NA值，得到你期望的结果。

英文:

I have a data.table with a column like:

c(58,NA,NA,NA,NA,13,NA,NA,NA,12,23,NA,12)

I would like to fill only the two NAs before each non-NA value in the column by carrying backwards the next non-NA value. The result should be :

c(58,NA,NA,13,13,13,NA,12,12,12,23,12,12)

I have managed to do it with :

  1. dt = data.table(V1 = c(58,NA,NA,NA,NA,13,NA,NA,NA,12,23,NA,12))
  2. dt[, rleid:=rleid(dt$V1)]
  3. dt[, num := seq(.N), rleid]
  5. u=1
  6. arr = c()
  7. for (i in 1:(nrow(dt)-1)){
  8.   if(dt$rleid[i] == dt$rleid[i+1]){
  9.     u=u+1
  10.     next
  11.   }
  12.   else{
  13.     arr = append(arr,u)}
  14.   u=1
  15. }
  16. arr=append(arr,1)
  18. v=c()
  19. for (i in 1:(length(arr))){
  20.   for (j in 1:arr[i]){
  21.     v=append(v,arr[i])
  22.   }
  23. }
  25. dt[, len:=v]
  26. dt[, val:=len-num]
  27. dt[, V2 := fifelse(is.na(V1) &amp; val&lt;=1, nafill(V1, &quot;nocb&quot;), V1)]

This solution takes too long for a big data table. Any suggestions that are faster ?

答案1

得分: 4

以下是翻译好的部分：

A quick and dirty data.table solution:

快速且不太规范的 data.table 解决方案：

dt[, V1b := fcoalesce(c(list(V1), shift(V1, -(1:2))))]

Or simply (as suggested by B. Christian Kamgang)

或者简单地（如B. Christian Kamgang建议的）

dt[, V1b := fcoalesce(shift(V1, -(0:2)))]

  1.   V1   V1b
  2. &lt;num&gt; &lt;num&gt;

1: 58 58
2: NA NA
3: NA NA
4: NA 13
5: NA 13
6: 13 13
7: NA NA
8: NA 12
9: NA 12
10: 12 12
11: 23 23
12: NA 12
13: 12 12

英文:

A quick and dirty data.table solution:

  1. dt[, V1b := fcoalesce(c(list(V1), shift(V1, -(1:2))))]
  3. # Or simply (as suggested by B. Christian Kamgang)
  4. dt[, V1b := fcoalesce(shift(V1, -(0:2)))]
  7.       V1   V1b
  8.     &lt;num&gt; &lt;num&gt;
  9.  1:    58    58
  10.  2:    NA    NA
  11.  3:    NA    NA
  12.  4:    NA    13
  13.  5:    NA    13
  14.  6:    13    13
  15.  7:    NA    NA
  16.  8:    NA    12
  17.  9:    NA    12
  18. 10:    12    12
  19. 11:    23    23
  20. 12:    NA    12
  21. 13:    12    12

答案2

得分: 2

你可以修改提供给你的函数这里，通过两次反转向量来实现，即：

  1. na_locf_max_backwards <- function(x, nmax){
  2.   x <- rev(x)
  3.   s <- split(x, cumsum(!is.na(x)))
  4.   l <- mapply(\(x, y) {
  5.         x[1:nmax+1] <- x[1]
  6.         length(x) <- y
  7.         x
  8.       }, s, lengths(s))
  9.   x <- unlist(l, use.names = FALSE)
  10.   x <- rev(x)
  11.   x
  12. }
  14. na_locf_max_backwards(c(58,NA,NA,NA,NA,13,NA,NA,NA,12,23,NA,12), nmax = 2)
  15. # [1] 58 NA NA 13 13 13 NA 12 12 12 23 12 12
英文:

You can modify the function given to you here by reversing the vector twice, i.e.

  1. na_locf_max_backwards &lt;- function(x, nmax){
  2.   x &lt;- rev(x)
  3.   s &lt;- split(x, cumsum(!is.na(x)))
  4.   l &lt;- mapply(\(x, y) {
  5.         x[1:nmax+1] &lt;- x[1]
  6.         length(x) &lt;- y
  7.         x
  8.       }, s, lengths(s))
  9.   x &lt;- unlist(l, use.names = FALSE)
  10.   x &lt;- rev(x)
  11.   x
  12. }
  14. na_locf_max_backwards(c(58,NA,NA,NA,NA,13,NA,NA,NA,12,23,NA,12), nmax = 2)
  15. # [1] 58 NA NA 13 13 13 NA 12 12 12 23 12 12

答案3

得分: 1

另一个 data.table 解决方案：

  1. dt[, V2 := fifelse(rev(rowid(rev(rleid(V1)))) <= 2, nafill(V1, "nocb"), V1)]
  1. 	   V1    V2
  2.  1:    58    58
  3.  2:    NA    NA
  4.  3:    NA    NA
  5.  4:    NA    13
  6.  5:    NA    13
  7.  6:    13    13
  8.  7:    NA    NA
  9.  8:    NA    12
  10.  9:    NA    12
  11. 10:    12    12
  12. 11:    23    23
  13. 12:    NA    12
  14. 13:    12    12
英文:

Another data.table solution:

  1. dt[, V2 := fifelse(rev(rowid(rev(rleid(V1))))&lt;=2, nafill(V1, &quot;nocb&quot;), V1)]
  3. 	   V1    V2
  4.  1:    58    58
  5.  2:    NA    NA
  6.  3:    NA    NA
  7.  4:    NA    13
  8.  5:    NA    13
  9.  6:    13    13
  10.  7:    NA    NA
  11.  8:    NA    12
  12.  9:    NA    12
  13. 10:    12    12
  14. 11:    23    23
  15. 12:    NA    12
  16. 13:    12    12

huangapple
  • 本文由 发表于 2023年6月19日 21:54:29
  • 转载请务必保留本文链接：https://go.coder-hub.com/76507315.html
  • data.table
  • na
  • r
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定