英文:
How to update variable value to current value (current value * 100/ colsums())
问题
我有一个如下的数据框（df）：
我想要执行以下两个步骤：
- 如果列名的第一个字母与
Grade中的值匹配，并且当前值为NA，则将其替换为0。 - 将A1:B2范围的值更新为当前值（
当前值 * 100 / colsums()）。
如何实现这个目标？
df <- structure(list(Grade = c("A", "A", "B", "B"), Pass = c("Y", "N","Y", "N"), A1 = c(7, 8, NA, NA), A2 = c(4, 5, NA, NA), A3 = c(9,NA, NA, NA), B1 = c(NA, NA, 8, NA), B2 = c(NA, NA, 3, 4)), row.names = c(NA,-4L), class = c("tbl_df", "tbl", "data.frame"))
英文:
I have a df like below.:
I would like to do following two steps:
- if first letter of col name matched the value in
Gradeand current value is NA, then replace it as 0. - update the value from A1:B2 as current value, (
current value * 100/ colsums()).
How can I make this happen?
df <- structure(list(Grade = c("A", "A", "B", "B"), Pass = c("Y", "N","Y", "N"), A1 = c(7, 8, NA, NA), A2 = c(4, 5, NA, NA), A3 = c(9,NA, NA, NA), B1 = c(NA, NA, 8, NA), B2 = c(NA, NA, 3, 4)), row.names = c(NA,-4L), class = c("tbl_df", "tbl", "data.frame"))
答案1
得分: 3
使用dplyr包，我们可以在case_when中使用across（如果您想要使用两个单独的ifelse，请查看我的旧编辑）。
library(dplyr)df %>%mutate(across(A1:B2, ~case_when(str_extract(cur_column(), "^.") == Grade & is.na(.x) ~ "0 (0%)",is.na(.x) ~ NA,.default = paste0(.x, " (", round(.x * 100/(sum(.x, na.rm = T)), digits = 2), "%)"))))
A tibble: 4 × 7
Grade Pass A1 A2 A3 B1 B2
1 A Y 7 (46.67%) 4 (44.44%) 9 (100%) NA NA
2 A N 8 (53.33%) 5 (55.56%) 0 (0%) NA NA
3 B Y NA NA NA 8 (100%) 3 (42.86%)
4 B N NA NA NA 0 (0%) 4 (57.14%)
<hr>### 更新如果要使用`colnames`，您需要首先`pick`列。```rdf %>%mutate(across(A1:B2, ~ifelse(str_extract(colnames(pick(.x)), "^.") == Grade & is.na(.x), 0, .x)),across(A1:B2, ~ifelse(is.na(.x), NA, paste0(.x, " (", round(.x * 100/(sum(.x, na.rm = T)), digits = 2), "%)"))))
英文:
With the dplyr package, we can use across with case_when (check my old edits if you want to use two separate ifelse).
library(dplyr)df |>mutate(across(A1:B2, ~case_when(str_extract(cur_column(), "^.") == Grade & is.na(.x) ~ "0 (0%)",is.na(.x) ~ NA,.default = paste0(.x, " (", round(.x * 100/(sum(.x, na.rm = T)), digits = 2), "%)"))))# A tibble: 4 × 7Grade Pass A1 A2 A3 B1 B2<chr> <chr> <chr> <chr> <chr> <chr> <chr>1 A Y 7 (46.67%) 4 (44.44%) 9 (100%) NA NA2 A N 8 (53.33%) 5 (55.56%) 0 (0%) NA NA3 B Y NA NA NA 8 (100%) 3 (42.86%)4 B N NA NA NA 0 (0%) 4 (57.14%)
<hr>
Update
You need to first pick the columns if you want to use colnames.
df %>%mutate(across(A1:B2, ~ifelse(str_extract(colnames(pick(.x)), "^.") == Grade & is.na(.x), 0, .x)),across(A1:B2, ~ifelse(is.na(.x), NA, paste0(.x, " (", round(.x * 100/(sum(.x, na.rm = T)), digits = 2), "%)"))))
答案2
得分: 3
以下是代码的中文翻译：
# 使用两个枢轴的替代方法：library(dplyr)library(tidyr) # pivot_*# 将数据框进行长格式化，排除 Grade 和 Pass 列df %>%pivot_longer(-c(Grade, Pass)) %>%# 如果 Grade 与 name 的首字母相匹配，则将值设为 0mutate(value = if_else(Grade == substring(name, 1, 1), coalesce(value, 0), value)) %>%# 按 name 列分组group_by(name) %>%# 如果值为 NA，则将其替换为空字符串，否则将其格式化为带百分比的字符串mutate(value = if_else(is.na(value), "", sprintf("%i (%0.02f%%)", value, 100 * value / sum(value, na.rm = TRUE)))) %>%# 将数据框重新转换为宽格式pivot_wider()# # 一个数据框: 4 × 7# Grade Pass A1 A2 A3 B1 B2# <chr> <chr> <chr> <chr> <chr> <chr> <chr># 1 A Y "7 (46.67%)" "4 (44.44%)" "9 (100.00%)" "" ""# 2 A N "8 (53.33%)" "5 (55.56%)" "0 (0.00%)" "" ""# 3 B Y "" "" "" "8 (100.00%)" "3 (42.86%)"# 4 B N "" "" "" "0 (0.00%)" "4 (57.14%)"
这是你要求的代码部分的中文翻译。如果还有其他需要，请随时告诉我。
英文:
An alternative with two pivots:
library(dplyr)library(tidyr) # pivot_*df %>%pivot_longer(-c(Grade, Pass)) %>%mutate(value = if_else(Grade == substring(name, 1, 1), coalesce(value, 0), value)) %>%group_by(name) %>%mutate(value = if_else(is.na(value), "", sprintf("%i (%0.02f%%)", value, 100 * value / sum(value, na.rm = TRUE)))) %>%pivot_wider()# # A tibble: 4 × 7# Grade Pass A1 A2 A3 B1 B2# <chr> <chr> <chr> <chr> <chr> <chr> <chr># 1 A Y "7 (46.67%)" "4 (44.44%)" "9 (100.00%)" "" ""# 2 A N "8 (53.33%)" "5 (55.56%)" "0 (0.00%)" "" ""# 3 B Y "" "" "" "8 (100.00%)" "3 (42.86%)"# 4 B N "" "" "" "0 (0.00%)" "4 (57.14%)"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论