R:重塑数据框 - 添加列，同时保持行索引值一致

Question

我想使用dplyr::mutate()重塑以下数据框： 目标是创建一个新的列变量percent但要确保值对应于正确的subject。数据当前为"长"格式。详细：我想提取与模式"percentQ"相关的行，并根据每个subject创建一个名为percent的新列，以确保分数与相应的subject对齐。

df_long <- structure(list(id = c(NA,NA, "scoreQ1", NA, "scoreQ2", NA, NA,"percentQ1", "percentQ2", NA, "GPA"), 
subject = c(NA,NA, "Chris", NA, "Liz", NA, NA, "Chris","Liz", NA, NA),
grade = c(NA,NA, 45L, NA, 60L, NA, NA, 75L, 100L, NA)), row.names = c(NA,-11L), class = c("data.table", "data.frame"))
print(df_long)
#id       subject   grade
#<NA>      <NA>      NA
#<NA>      <NA>      NA
#scoreQ1   Chris     45
#<NA>      <NA>      NA
#scoreQ2   Liz       60
#<NA>      <NA>      NA
#<NA>      <NA>      NA
#percentQ1 Chris     75
#percentQ2 Liz       100
#<NA>      <NA>      NA
#GPA       <NA>      NA

请建议允许将数据框重塑为以下内容的 R 脚本： 可以看出，percent值对应于正确的subject，在这种情况下，克里斯75，100利兹。我一直遇到问题，无法将percent值分配给正确的subject。

df_wide <- structure(list(id = c(NA,NA, "scoreQ1", NA, "scoreQ2", NA, NA, NA, "GPA"), 
subject = c(NA,NA, "Chris", NA, "Liz", NA, NA, NA, NA),
grade = c(NA,NA, 45L, NA, 60L, NA, NA, NA, NA),
percent = c(NA,NA, 75L, NA, 100L, NA, NA, NA, NA)), row.names =  c(NA,-9L), class = c("data.table", "data.frame"))
print(df_wide)
#id       subject  grade   percent
#<NA>     <NA>     NA      NA
#<NA>     <NA>     NA      NA
#scoreQ1  Chris    45      75
#<NA>     <NA>     NA      NA
#scoreQ2  Liz      60     100
#<NA>     <NA>     NA      NA
#<NA>     <NA>     NA      NA
#<NA>     <NA>     NA      NA
#GPA     <NA>      NA      NA

Answer 1

我认为您的任务可以通过加入过程来完成。

在这里，我们可以过滤掉 id 列中带有百分比的值，然后 使用rename后将其left_join到data_frame。

library(dplyr)
percent <- grepl("percent", df_long$id)
df_long |> 
filter(!percent) |> 
left_join(
filter(df_long, percent) |> 
rename(percent = "grade") |> 
select(-id), by = "subject"
)

id subject grade percent
1:    <NA>    <NA>    NA      NA
2:    <NA>    <NA>    NA      NA
3: scoreQ1   Chris    45      75
4:    <NA>    <NA>    NA      NA
5: scoreQ2     Liz    60     100
6:    <NA>    <NA>    NA      NA
7:    <NA>    <NA>    NA      NA
8:    <NA>    <NA>    NA      NA
9:     GPA    <NA>    NA      NA

数据

df_long <- structure(list(id = c(NA, NA, "scoreQ1", NA, "scoreQ2", NA, NA, 
"percentQ1", "percentQ2", NA, "GPA"), subject = c(NA, NA, "Chris", 
NA, "Liz", NA, NA, "Chris", "Liz", NA, NA), grade = c(NA, NA, 
45L, NA, 60L, NA, NA, 75L, 100L, NA, NA)), row.names = c(NA, 
-11L), class = c("data.table", "data.frame"))

Answer 2

我可能遗漏了一些东西，但你并没有真正从长形式转向宽形式，你只是在添加一个额外的列。假设最高分是 60，那么：

library(tidyverse)
df2 <-
df_long |>
mutate(percent = (grade/60)*100)

应该做这个伎俩。