r - 如何生成具有可能出现在多个列中的分组值的汇总表？

Question

如果分组值可以出现在两列或多列中，是否存在生成汇总数据的最佳实践？

请考虑以下数据：

df<-data.frame(Age=c(1,1,1,1,1,2,2,2,2,2),
           Score=c(1,2,1,2,1,2,1,2,1,2),
           Major=c("Art","Art","Art","Math","Math","Science","Science","Science","Science","Science"),
           Major_2=c("Math","Knitting","Math","Science","Science","Knitting","Lava","Knitting","Lava","Knitting"))

我想为每个专业的人制作一个汇总统计表，但是每个人（由表中的一行表示）可能有多个专业，而对于两个主要列中的哪一个包含给定的没有任何要求价值。

如果专业存在于一个专栏中，我会接近dplyr并产生

df_summary<-df%>%
  group_by(Major)%>%
  summarize(Avg_Age=mean(Age),
        Total_Score=sum(Score))

我想要的结果是：

我可以使用下面的代码得到这个结果，但是有没有更容易阅读或者更有效的解决方案？

##gathering all the possible majors

majors<-unique(c(df$Major,df$Major_2))

##creating a list

answer<-list()

##looping through each major and summarizing

for (maj in majors) {
  answer[[maj]]<-df%>%
    filter(Major==maj |Major_2==maj)%>%
    summarize(Major=maj,
              Avg_Age=mean(Age),
              Total_Score=sum(Score))
  
}

##binding back into the desired table, with a row for each major

awkward_answer<-bind_rows(answer)

标签： rdplyr

Answer 1

您可以使用tidyr::pivot_longer

library(tidyr)
library(dplyr)
df %>% 
  pivot_longer(Major:Major_2, values_to = "Major") %>%
  group_by(Major) %>%
  summarise(Avg_Age = mean(Age),
            Total_Score = sum(Score))
# A tibble: 5 x 3
  Major    Avg_Age Total_Score
  <chr>      <dbl>       <dbl>
1 Art         1              4
2 Knitting    1.75           8
3 Lava        2              2
4 Math        1              5
5 Science     1.71          11