R：计算每列中的出现次数，并将该列的值替换为计数(SQL？

Question

以下是原始数据的示例：

ID        Test1    Test2       Test3      Test4
1          0         0          NA         1.2
1          0         NA         NA         3.0 
1          NA        NA         NA          0 
2          0         0          0           0
2          0         0          NA          NA

我想计算每个ID的非NA出现次数(因此包括0(，并将该列值替换为该数字。生产：

ID        Test1    Test2       Test3      Test4
1           2        1          NA          3
2           2        2          1           1

我不知道是否需要在R中使用sqldf包。我试图将数据帧强制转换为数据表并对其进行整形，但没有成功。

df<-x%>%熔体(idvars="ID"(

谢谢你的帮助。

Answer 1

我们可以在逻辑向量上通过sum进行分组

library(dplyr)
df1 %>% 
group_by(ID) %>% 
summarise_all(funs(na_if(sum(!is.na(.)), 0)))
# A tibble: 2 x 5
#     ID Test1 Test2 Test3 Test4
#  <int> <int> <int> <int> <int>
#1     1     2     1    NA     3
#2     2     2     2     1     1

---

或使用base R中的aggregate

aggregate(.~ ID, df1, FUN = function(x) sum(!is.na(x)), na.action = NULL)

或使用rowsum

rowsum(+(!is.na(df1[-1])), df1$ID)

数据

df1 <- structure(list(ID = c(1L, 1L, 1L, 2L, 2L), Test1 = c(0L, 0L, 
NA, 0L, 0L), Test2 = c(0L, NA, NA, 0L, 0L), Test3 = c(NA, NA, 
NA, 0L, NA), Test4 = c(1.2, 3, 0, 0, NA)), class = "data.frame", 
row.names = c(NA, -5L))

Answer 2

下面我们讨论使用问题中提到的两个包的解决方案。

1(sqldf使用问题中引用的sqldf包，使用末尾注释中可重复定义的输入：

library(sqldf)
sqldf("select ID, 
nullif(count(Test1), 0) Test1,
nullif(count(Test2), 0) Test2,
nullif(count(Test3), 0) Test3,
nullif(count(Test4), 0) Test4
from DF
group by ID")

给予：

ID Test1 Test2 Test3 Test4
1  1     2     1    NA     3
2  2     2     2     1     1

如果可以为所有NA的ID报告0，并且类似地为其他测试*列报告0，则nullif(count(test1), 0)可以缩短为仅count(test1)。

1a(如果实际上有很多列，而不仅仅是4列，或者你不喜欢重复select的一部分，我们可以构建字符串，然后像这样插入：

testNames <- names(DF)[-1]
select <- toString(sprintf("nullif(count(%s), 0) %s", testNames, testNames))
library(sqldf)
fn$sqldf("select ID, $select
from DF
group by ID")

将verbose = TRUE参数添加到sqldf调用中，以查看是否实际将相同的字符串发送到后端。

如果可以报告0而不是NA，那么我们可以将select <- ...简化为：

select <- toString(sprintf("count(%s) %s", testNames, testNames))

2(重新整形2如问题中的代码尝试中那样使用melt：

library(magrittr)
library(reshape2)
count <- function(x) if (all(is.na(x))) NA_integer_ else sum(!is.na(x))
DF %>% 
melt(id.vars = "ID") %>% 
dcast(ID ~ variable, count)

如果可以为所有NA的任何ID报告0，则计数可以简化为：

count <- function(x) sum(!is.na(x))

备注

Lines <- "ID        Test1    Test2       Test3      Test4
1          0         0          NA         1.2
1          0         NA         NA         3.0 
1          NA        NA         NA          0 
2          0         0          0           0
2          0         0          NA          NA"
DF <- read.table(text = Lines, header = TRUE)