如何通过选定的列来估计每行的mean和st.dev？

Question

Table1:

ST crp pln 2000  2001  2002  2003  yr_count
AL 11  01  13.0   4.9   264   19         5 
AL 11  90  NA     NA     54   20        11 
AL 11  21  NA     NA     NA   NA         0 

我希望仅当yr_count >0时才按列2000、2001、2002、2003计算每行的平均值和SD。

sel_cols <- c("2000","2001","2002","2003")
Table1[Prm_yr_count > 0,`:=`(Avg_LR = round(rowMeans(.SD, na.rm = TRUE),0L), 
                                      Std_LR = round(sd(.SD, na.rm = TRUE),0L)),
                                      by=list(ST,crp,pln), .SDcols=sel_cols]

当我使用列名时，我得到下面的错误。

Error in `[.data.table`(Avg_CAT_CRP_PLN, Prm_yr_count > 0, `:=`(Avg_LR = round(rowMeans(.SD,  : 
  Some items of .SDcols are not column names (or are NA)

在data.table方法中修复这个问题的任何帮助都是非常感谢的。

Answer 1

我们可以使用rowMeans (来自base R)和rowSds (来自matrixStats)来做到这一点。“Std_LR”可以更改为

library(matrixStats)
library(data.table)
Table1[yr_count > 0, `:=`(Avg_LR = round(rowMeans(.SD, na.rm = TRUE),0L), 
       Std_LR = round(rowSds(as.matrix(.SD), na.rm = TRUE),0L)),
     by=list(ST,crp,pln), .SDcols=sel_cols]

Table1
#   ST crp pln 2000 2001 2002 2003 yr_count Avg_LR Std_LR
#1: AL  11   1   13  4.9  264   19        5     75    126
#2: AL  11  90   NA   NA   54   20       11     37     24
#3: AL  11  21   NA   NA   NA   NA        0     NA     NA

注意:假设'Table1‘是一个data.table

NOTE2：“Table1”的其中一列不是Prm_yr_count，而是yr_count

更新

在'Table2‘上使用相同的命令

Table2[yr_count > 0, `:=`(Avg_LR = round(rowMeans(.SD, na.rm = TRUE),0L), 
        Std_LR = round(rowSds(as.matrix(.SD), na.rm = TRUE),0L)),
      by=list(ST,crp,pln), .SDcols=sel_cols]




Table2
#   ST crp pln 2000 2001 2002  2003 2004 2005 2006  2007 2008  2009  2010  2011  2012  2013 2014 2018 yr_count Avg_LR
#1: AL  11  01   NA   NA   NA    NA   NA   NA   NA    NA   NA    NA    NA   0.0   0.0   1.5    0    0        5    NaN
#2: AL  11  90   13  4.9    0 267.7 10.0 49.7  0.0 159.7    0  19.1  16.2    NA    NA    NA   NA   NA       11     71
#3: AL  15  01   NA   NA   NA    NA   NA   NA   NA    NA   NA    NA    NA   NaN   NaN   NaN  NaN  NaN        0     NA
#4: AL  15  90   NA   NA   NA    NA   NA   NA   NA    NA   NA   NaN   NaN    NA    NA    NA   NA   NA        0     NA
#5: AL  16  90    0  0.0    0   0.0 38.5  0.0 52.9   0.0    0 297.8  46.3 183.5 714.8 487.9    0  NaN       15      0
#6: AL  20  41   NA   NA   NA    NA   NA   NA 20.0  11.2    0  49.0 117.2   0.0   0.0  39.7    0  NaN        9    NaN
#   Std_LR
#1:     NA
#2:    131
#3:     NA
#4:     NA
#5:      0
#6:     NA

数据

Table1 <- structure(list(ST = c("AL", "AL", "AL"), crp = c(11L, 11L, 11L
), pln = c(1L, 90L, 21L), `2000` = c(13, NA, NA), `2001` = c(4.9, 
NA, NA), `2002` = c(264L, 54L, NA), `2003` = c(19L, 20L, NA), 
    yr_count = c(5L, 11L, 0L)), class = c("data.table", "data.frame"
), row.names = c(NA, -3L))



Table2 <- structure(list(ST = c("AL", "AL", "AL", "AL", "AL", "AL"
), crp = c(11, 11, 15, 15, 16, 20), pln = c("01", "90", "01", 
"90", "90", "41"), `2000` = c(NA, 13, NA, NA, 0, NA), `2001` = c(NA, 
4.9, NA, NA, 0, NA), `2002` = c(NA, 0, NA, NA, 0, NA), `2003` = c(NA, 
267.7, NA, NA, 0, NA), `2004` = c(NA, 10, NA, NA, 38.5, NA), 
    `2005` = c(NA, 49.7, NA, NA, 0, NA), `2006` = c(NA, 0, NA, 
    NA, 52.9, 20), `2007` = c(NA, 159.7, NA, NA, 0, 11.2), `2008` = c(NA, 
    0, NA, NA, 0, 0), `2009` = c(NA, 19.1, NA, NaN, 297.8, 49
    ), `2010` = c(NA, 16.2, NA, NaN, 46.3, 117.2), `2011` = c(0, 
    NA, NaN, NA, 183.5, 0), `2012` = c(0, NA, NaN, NA, 714.8, 
    0), `2013` = c(1.5, NA, NaN, NA, 487.9, 39.7), `2014` = c(0, 
    NA, NaN, NA, 0, 0), `2018` = c(0, NA, NaN, NA, NaN, NaN), 
    yr_count = c(5, 11, 0, 0, 15, 9)), sorted = c("ST", 
"crp", "pln"), class = c("data.table", "data.frame"), row.names = c(NA, 
-6L))

Answer 2

这是一种dplyr方法。然后，您可以cbind，join回到旧的数据帧。这个解决方案可能会很慢：

      library(dplyr)
        df %>% 
          select(contains("X"),yr_count) %>% 
          mutate(Mean=ifelse(yr_count>0,
    rowMeans(.[,-ncol(.)],na.rm=T),
                             "No"),sd=ifelse(yr_count>0,
apply(.[,-ncol(.)],1,function(x) sd(x,na.rm = T)),
                                             "No"))


  X2000 X2001 X2002 X2003 yr_count   Mean               sd
1    13   4.9   264    19        5 75.225 125.982548394609
2    NA    NA    54    20       11     37 24.0416305603426
3    NA    NA    NA    NA        0     No               No

正如@akrun所建议的那样，我们可以通过使用purrr中的pmap_dbl来避免apply

    df %>% 
   select(contains("X"),yr_count) %>% 
    mutate(Mean = rowMeans(.[,-ncol(.)], na.rm = TRUE), 
  sd = purrr::pmap_dbl(.[,-ncol(.)], ~ sd(c(...), na.rm = TRUE)))

数据：

df<-read.table(text="ST crp pln 2000  2001  2002  2003  yr_count
AL 11  01  13.0   4.9   264   19         5 
               AL 11  90  NA     NA     54   20        11 
               AL 11  21  NA     NA     NA   NA         0 ",header=T)