对行和列进行透视，但在R中的PASSFAIL列上有一个条件

Question

我有一个像这样的数据帧

NUM <- c(1,2,3,1,2,3,1,2,3,1)
ID <- c("DJ45","DJ45","DJ45","DJ46","DJ46","DJ46","DJ47","DJ47","DJ47","DJ48")
Type <- c("A", "F", "C", "B", "D", "A", "E", "C", "F", "D")
Points1 <- c(9.2,60.8,22.9,1012.7,18.7,11.1,67.2,63.1,16.7,58.4)
Points2 <- c(19.2,0.8,2.9,12.7,188.7,114.1,7.2,66.1,46.7,508.4)
PASSFAIL <- c("PASS", "PASS", "FAIL", "PASS", "FAIL", "PASS", "PASS", "FAIL", "FAIL", "FAIL")
df1 <- data.frame(ID,NUM,Type,Points1,Points2,PASSFAIL)

df1:

    ID NUM Type Points1 Points2 PASSFAIL
1  DJ45   1    A     9.2    19.2     PASS
2  DJ45   2    F    60.8     0.8     PASS
3  DJ45   3    C    22.9     2.9     FAIL
4  DJ46   1    B    12.7    12.7     PASS
5  DJ46   2    D    18.7   188.7     FAIL
6  DJ46   3    A    11.1   114.1     PASS
7  DJ47   1    E    67.2     7.2     PASS
8  DJ47   2    C    63.1    66.1     FAIL
9  DJ47   3    F    16.7    46.7     FAIL
10 DJ48   1    D    58.4   508.4     FAIL

我试图获得一个输出，将"类型"列中的行转换为使用PASSFAIL列上的条件填充值的单个列，即(如果PASSFAIL = PASS，使用Points1列的值，如果PASSFAIL = FAIL，使用Points2列的值)

My Desired Output is

    ID   NUM   A    B    C    D    E    F    PASSFAIL 
1  DJ45   1    9.2  NA   NA   NA   NA   NA     PASS
2  DJ45   2    NA   NA   NA   NA   NA   60.8   PASS
3  DJ45   3    NA   NA   2.9  NA   NA   NA     FAIL
4  DJ46   1    NA 1012.7 NA   NA   NA   NA     PASS
5  DJ46   2    NA   NA   NA  188.7 NA   NA     FAIL
6  DJ46   3    11.1 NA   NA   NA   NA   NA     PASS
7  DJ47   1    NA   NA   NA   NA   67.2 NA     PASS
8  DJ47   2    NA   NA   66.1 NA   NA   NA     FAIL
9  DJ47   3    NA   NA   NA   NA   NA   46.7   FAIL
10 DJ48   1    NA   NA   NA  508.4 NA   NA     FAIL

我试图这样做，但我只是不知道如何在这段代码中使用我的条件。

df1 %>%
  group_by(ID, NUM) %>%
  mutate(id2 = sequence(n())) %>%
  spread(Type, Points1)

有人能帮我一下吗?

Answer 1

One way with reshape2:

df1$pick <- ifelse(df1$PASSFAIL == "PASS", df1$Points1, df1$Points2)
newdf <- dcast(df1, ID+NUM~Type, value.var="pick")
data.frame(newdf, PASSFAIL=df1[,"PASSFAIL"])
#      ID NUM    A      B    C     D    E    F PASSFAIL
# 1  DJ45   1  9.2     NA   NA    NA   NA   NA     PASS
# 2  DJ45   2   NA     NA   NA    NA   NA 60.8     PASS
# 3  DJ45   3   NA     NA  2.9    NA   NA   NA     FAIL
# 4  DJ46   1   NA 1012.7   NA    NA   NA   NA     PASS
# 5  DJ46   2   NA     NA   NA 188.7   NA   NA     FAIL
# 6  DJ46   3 11.1     NA   NA    NA   NA   NA     PASS
# 7  DJ47   1   NA     NA   NA    NA 67.2   NA     PASS
# 8  DJ47   2   NA     NA 66.1    NA   NA   NA     FAIL
# 9  DJ47   3   NA     NA   NA    NA   NA 46.7     FAIL
# 10 DJ48   1   NA     NA   NA 508.4   NA   NA     FAIL

它有助于创建一个新列，其中的数据将被制作成"wide"格式。我们通过使用ifelse选择Points1代替"PASS", Points2代替"PASS"来做到这一点。在ID和NUM列上调用dcast，以便与"Type"列一起扩展。PASSFAIL列附加到重塑输出以完成。