基于另一个向量中的ID逐行求和列

Question

我有两个输入数据框，第一个被称为"Firms_Ind“，包含2列(”公司“，”行业“)，有多行。它给出了每个公司的行业ID。另一个叫做"ann_returns“，它的列数和"Firms_Ind”的行数一样多，而且是多行的。它包含每个公司(列)每年(行)的回报。

我想计算每个行业的年平均回报率。所以我想要一个维度为:列数=年数，行数=年数的输出矩阵。对于每个行业(列)，应计算每年的平均回报。

下面是一个小示例：

> Firms_Ind
  Firms Industry
1     A        1
2     B        2
3     C        3
4     D        1
5     E        2
6     F        1

> ann_returns
      A    B    C    D    E    F
y1 0.20 0.11 0.13 0.30 0.24 0.03
y2 0.23 0.08 0.03 0.23 0.17 0.01
y3 0.28 0.19 0.11 0.21 0.19 0.07

> Industry_mean
            1    2    3
y1_means 0.20 0.11 0.13
y2_means 0.23 0.08 0.03
y3_means 0.28 0.19 0.11

Answer 1

下面是一种使用sapply的方法

# get a list of firms by industry
inds <- split(Firms_Ind$Firms, Firms_Ind$Industry)
# loop through industries to calculate annual means
myMat <- sapply(inds,
              function(i) if(length(i) > 1) rowMeans(ann_returns[, i]) else ann_returns[, i])

在这里，sapply循环遍历各个行业。对于每个行业，检查是否有多个公司，如果有，则应用rowMeans，如果没有，则返回原始值。

这将返回

myMat
           1     2    3
y1 0.1766667 0.175 0.13
y2 0.1566667 0.125 0.03
y3 0.1866667 0.190 0.11

data

Firms_Ind <-
structure(list(Firms = structure(1:6, .Label = c("A", "B", "C", 
"D", "E", "F"), class = "factor"), Industry = c(1L, 2L, 3L, 1L, 
2L, 1L)), .Names = c("Firms", "Industry"), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6"))

ann_returns <- 
structure(c(0.2, 0.23, 0.28, 0.11, 0.08, 0.19, 0.13, 0.03, 0.11, 
0.3, 0.23, 0.21, 0.24, 0.17, 0.19, 0.03, 0.01, 0.07), .Dim = c(3L, 
6L), .Dimnames = list(c("y1", "y2", "y3"), c("A", "B", "C", "D", 
"E", "F")))

Answer 2

使用dplyr和tidyr

library(tidyr)
library(dplyr)

Industry_mean <- ann_returns %>% 
         gather(key=Firms,value=value,-Year) %>% #convert to long format
         left_join(Firms_Ind) %>% #merge with firms_ind
         group_by(Year,Industry) %>% #group as required
         summarise(mean=mean(value)) %>% #calculate means
         spread(key=Industry,value=mean) #convert back to wide format

Industry_mean

   Year       `1`   `2`   `3`
* <chr>     <dbl> <dbl> <dbl>
1    y1 0.1766667 0.175  0.13
2    y2 0.1566667 0.125  0.03
3    y3 0.1866667 0.190  0.11

Answer 3

我们可以按列拆分ann_returns，然后运行rowMeans

# if Firms in correct order
inds <- split.default(ann_returns, f = Firms_Ind$Industry)

# # if Firms not in correct order:
# inds <- split.default(
#     ann_returns,
#     f = Firms_Ind$Industry[match(colnames(ann_returns), Firms_Ind$Firms)])

do.call(cbind, lapply(inds,rowMeans))
#            1     2    3
# y1 0.1766667 0.175 0.13
# y2 0.1566667 0.125 0.03
# y3 0.1866667 0.190 0.11

两个输入data.frames是：

# > dput(ann_returns)
structure(list(A = c(0.2, 0.23, 0.28), B = c(0.11, 0.08, 0.19
), C = c(0.13, 0.03, 0.11), D = c(0.3, 0.23, 0.21), E = c(0.24, 
0.17, 0.19), F = c(0.03, 0.01, 0.07)), .Names = c("A", "B", "C", 
"D", "E", "F"), row.names = c("y1", "y2", "y3"), class = "data.frame")
# > dput(Firms_Ind)
structure(list(Firms = structure(1:6, .Label = c("A", "B", "C", 
"D", "E", "F"), class = "factor"), Industry = c(1L, 2L, 3L, 1L, 
2L, 1L)), .Names = c("Firms", "Industry"), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6"))