我有以下提到的数据框:
ID Date Status Category
TR-1 2018-01-10 Passed A
TR-2 2018-01-09 Passed B
TR-3 2018-01-09 Failed C
TR-3 2018-01-09 Failed A
TR-4 2018-01-08 Failed B
TR-5 2018-01-08 Passed C
TR-5 2018-01-08 Failed A
TR-6 2018-01-07 Passed A
利用上述数据框,我想要以下格式的输出结果:
日期应按降序排列,类别顺序应为 C、A 和 B。
Date count distinct_count Passed Failed
2018-01-10 1 1 1 0
A 1 1 1 0
B 0 0 0 0
C 0 0 0 0
2018-01-09 3 2 1 2
A 1 1 1 0
B 1 1 1 0
C 1 1 1 0
为了得到上述输出,我尝试了以下代码,但它不能正常工作,无法得到期望的输出。
Output<-DF %>%
group_by(Date=Date,A,B,C) %>%
summarise(`Count` = n(),
`Distinct_count` = n_distinct(ID),
Passed=sum(Status=='Passed'),
A=count(category='A'),
B=count(category='B'),
C=count(category='C'),
Failed=sum(Status=='Failed'))
Dput:
structure(list(ID = structure(c(1L, 2L, 3L, 3L, 4L, 5L, 5L, 6L
), .Label = c("TR-1", "TR-2", "TR-3", "TR-4", "TR-5", "TR-6"), class = "factor"),
Date = structure(c(4L, 3L, 3L, 3L, 2L, 2L, 2L, 1L), .Label = c("07/01/2018",
"08/01/2018", "09/01/2018", "10/01/2018"), class = "factor"),
Status = structure(c(2L, 2L, 1L, 1L, 1L, 2L, 1L, 2L), .Label = c("Failed",
"Passed"), class = "factor"), Category = structure(c(1L,
2L, 3L, 1L, 2L, 3L, 1L, 1L), .Label = c("A", "B", "C"), class = "factor")), .Names = c("ID",
"Date", "Status", "Category"), class = "data.frame", row.names = c(NA,
-8L))
group_by(Date, Category) %>%
来替换你的第二行代码生成输出结果吗?然后你可以再对第二个表按Date
进行分组汇总,以获取日期计数,并使用left_join
将其与第一个表连接,以获得一个额外的列来指示日期计数... - NRLPsummarise
,我认为它不会按照你的建议工作。请按 Nutle 的建议添加你初始数据的输出。 - NRLP