聚合多个重复项并计算其平均值

Question

假设我们有一个DF，其受尊重的用户ID中有重复项，但名称不同，当然也可以是重复项。

DF <- data.frame(ID=c(101,101,101,101,101,102,102,102,102),
Name=c("Ed","Ed","Hank","Hank","Hank","Sandy","Sandy","Jessica","Jessica"),
Class=c("Junior","Junior","Junior","Junior", "Junior","High","High","Mid","Mid"),
Scoring=c(11,15,18,18,12,20,22,25,26), Other_Scores=c(15,9,34,23,43,23,34,23,23))

目的是分别聚合和计算用户ID及其名称的均值和均方差。一个所需的输出示例：

UserID  Name     Class    Scoring_mean  Scoring_std
101     Ed       Junior   12.5          3
101     Hank     Junior   24.67         11.62
102     Sandy    High     24.75         6.29
102     Jessica  High     24.25         1.5

因此我的问题是:

• 有哪些选项可以根据UserID聚合Names，而不会丢失信息（Hank被强制插入Ed等，就像sumise（）或mutate（）一样）

按照我的想法，R必须检查哪个名称对应于用户ID，以及是否匹配；汇总并计算平均值

与此同时，我找不到任何其他与这个问题相关的帖子，比如：

1. 如何计算R中特定行的平均值
2. 根据匹配列减去列对。
3. 计算R中需要满足2个条件时的平均值
4. R中重复行之间的平均值

Answer 1

这里有一个＜code＞tidyverse＜/code＞选项，它使用一些整形来创建一列分数，然后进行一些分组以获得汇总统计数据：

DF <- data.frame(
ID=c(101,101,101,101,101,102,102,102,102),
Name=c("Ed","Ed","Hank","Hank","Hank","Sandy","Sandy","Jessica","Jessica"),
Class=c("Junior","Junior","Junior","Junior", "Junior","High","High","Mid","Mid"),
Scoring=c(11,15,18,18,12,20,22,25,26), 
Other_Scores=c(15,9,34,23,43,23,34,23,23)
)

library(tidyverse)

DF %>%
  gather(score_type, score, Scoring, Other_Scores) %>%  # reshape score columns
  group_by(ID, Name, Class) %>%                         # group by combinations
  summarise(scoring_mean = mean(score),                 # get summary stats
            scoring_sd = sd(score)) %>%
  ungroup()                                             # forget the grouping

# # A tibble: 4 x 5
#       ID Name    Class  scoring_mean scoring_sd
#    <dbl> <fct>   <fct>         <dbl>      <dbl>
# 1  101. Ed      Junior         12.5       3.00
# 2  101. Hank    Junior         24.7      11.6 
# 3  102. Jessica Mid            24.2       1.50
# 4  102. Sandy   High           24.8       6.29

Answer 2

如何计算你的汇总统计数据，然后将结果加入到你的初始数据框架中。像这样:

DF <- data.frame(ID=c(101,101,101,101,101,102,102,102,102),
                 Name=c("Ed","Ed","Hank","Hank","Hank","Sandy","Sandy","Jessica","Jessica"),
                 Class=c("Junior","Junior","Junior","Junior", "Junior","High","High","Mid","Mid"),
                 Scoring=c(11,15,18,18,12,20,22,25,26), Other_Scores=c(15,9,34,23,43,23,34,23,23))


DF2 <- DF %>% group_by(Name) %>%
  summarise(scoring_mean=mean(Scoring), scoring_sd = sd(Scoring)) %>%
  left_join(DF[,c(1,2,3)], by="Name")

给：

# A tibble: 9 x 5
  Name    scoring_mean scoring_sd    ID Class 
  <fct>          <dbl>      <dbl> <dbl> <fct> 
1 Ed              13.0      2.83   101. Junior
2 Ed              13.0      2.83   101. Junior
3 Hank            16.0      3.46   101. Junior
4 Hank            16.0      3.46   101. Junior
5 Hank            16.0      3.46   101. Junior
6 Jessica         25.5      0.707  102. Mid   
7 Jessica         25.5      0.707  102. Mid   
8 Sandy           21.0      1.41   102. High  
9 Sandy           21.0      1.41   102. High