r tidyverse - 计算具有相同名称的多个列的平均值

Question

我有一些每周收集的数据，其中的一个片段是这样的，通过 dput:

p <- structure(list(railroad = structure(c(2L, 2L, 2L, 3L, 3L, 3L), .Label = 
c("All Other Railroads", 
"BNSF Railway Company", "CN", "CSX Transportation", "Norfolk Southern", 
"The Kansas City Southern Railway and Kansas City Southern de Mexico, S.A. de 
C.V. Consolidated ", 
"Union Pacific Railroad"), class = "factor"), measure = structure(c(1L, 
4L, 3L, 1L, 4L, 3L), .Label = c("Cars On Line - By Car Owner", 
"Cars On Line - By Car Type", "Terminal Dwell (Hours)", "Train Speed (MPH)"
), class = "factor"), category = structure(c(76L, 35L, 4L, 76L, 
35L, 29L), .Label = c("All Trains", "Allentown, PA", "Baltimore, MD", 
"Barstow, CA", "Bellevue, OH", "Birmingham, AL", "Box", "Buffalo, NY", 
"Chattanooga, TN", "Chicago (Proviso), IL", "Chicago, IL", "Cincinnati, OH", 
"Coal Unit", "Columbus, OH", "Conway, PA", "Corbin, KY", "Covered Hopper", 
"Decatur, IL", "Denver, CO", "Elkhart, IN", "Entire Railroad", 
"Fond du Lac Yard, WI", "Foreign RR", "Fort Worth, TX", "Galesburg, IL", 
"Gondola", "Grain Unit", "Hamlet, NC", "Harrison Yard (Memphis), TN", 
"Hinkle, OR", "Houston (Englewood), TX", "Houston (Settegast), TX", 
"Houston, TX", "Indianapolis, IN", "Intermodal", "Jackson Yard, MS", 
"Jackson, MS", "Kansas City, KS", "Kansas City, MO", "Knoxville, TN", 
"Laredo, TX", "Lincoln, NE", "Linwood, NC", "Livonia, LA", "Louisville, KY", 
"MacMillan Yard (Toronto), ON", "Macon, GA", "Manifest", "Markham Yard, IL", 
"Memphis, TN", "Monterrey, NL", "Montgomery, AL", "Multilevel", 
"Nashville, TN", "New Orleans, LA", "North Little Rock, AR", 
"North Platte East, NE", "North Platte West, NE", "Northtown, MN", 
"Nuevo Laredo, TM", "Open Hopper", "Other", "Pasco, WA", "Pct. Private", 
"Pine Bluff, AR", "Private", "Roanoke, VA", "Roseville, CA", 
"Russell, KY", "San Luis Potosi, SL", "Sanchez, TM", "Selkirk, NY", 
"Sheffield, AL", "Shreveport, LA", "Symington Yard (Winnipeg), MB", 
"System", "Tank", "Tascherau Yard (Montreal), QC", "Thornton Yard (Vancouver), 
BC", 
"Toledo, OH", "Total", "Tulsa, OK", "Walker Yard (Edmonton), AB", 
"Waycross, GA", "West Colton, CA", "Willard, OH"), class = "factor"), 
`201510` = c(66923, 33.9, 39.3, 40227, 30.8, 17.5), `201510` = c(66637, 
32.6, 56.6, 40778, 30.9, 18.3), `201510` = c(66309, 33.4, 
44.9, 40407, 30.5, 17.3), `201511` = c(65980, 34.6, 37.5, 
40316, 30.6, 17.5), `201511` = c(67034, 34.6, 43.1, 40174, 
30.4, 18.7)), row.names = c(1L, 15L, 21L, 33L, 47L, 53L), class = 
"data.frame")

总共有 143 列，第 4 - 143 列是数字。我想计算具有相同列名的所有列的平均值。所以下面有列 201510 重复了 3 次，列 201511 重复了两次。所需的输出是每列重复的平均值。例如，201510 将具有以下值:

`201510`
[1] 66623.00000    33.30000    46.93333 40470.66667    30.73333    17.70000

我试过下面的代码:

library(tidyverse)

p = data.frame(p)

p %>%
  gather(time,value,railroad, measure, category) %>%                       
  mutate(time = gsub('X([^.]+)|.', '\\1', time)) %>%  
  group_by(time, value, railroad, measure, category) %>%                            
  summarise(MEAN = mean(value)) %>%                   
  ungroup() %>%                                       
  spread(time, MEAN)  

这会产生以下错误:

`Error in grouped_df_impl(data, unname(vars), drop) : 
Column `railroad` is unknown
In addition: Warning message:
attributes are not identical across measure variables;
they will be dropped `

有办法吗？

Answer 1

这里的主要问题是非唯一的列名。 tidyverse 主要采用唯一的列名，并且许多函数添加后缀以使其唯一(如果它们还没有)，许多基本函数也是如此，因此在下面的所有解决方案中，我们只是避免使用任何此类函数。我们仍然可以使用 magrittr、purrr 并且某些基本函数也仍然允许这样做。

(1)、(2) 和(4) 只使用magrittr。 (1a) 使用 purrr，在 (3) 中我们使用 tidyr 和 dplyr，但仅在转换为长格式之后。

所有解决方案都会为数字列中的每个唯一名称附加一个名称格式为 mean.* 的列。在问题的示例中，数字列中有两个唯一名称，因此对于该示例，它附加了两列，它们分别命名为 mean.201510 和 mean.201511，如图所示以下。我们只显示 (1) 中的输出，其余输出类似。

所有解决方案都使用两条管道。第一个由第一个 %>% 组成，第二个流水线作为 cbind 的参数出现，是创建新列的原因。

(1)、(1a) 和 (4) 并列最短。

1) magrittr magrittr 本身好像没有加后缀。 cbind 原始数据框 p 如下。首先将 p 转换为列列表，提取数字组件，将其拆分为列名，将每个组件转换为数据框并获取每个组件的 rowMeans，最后将名称设置为 mean。* .

library(magrittr)

p %>%
  cbind(as.list(.) %>%
    Filter(is.numeric, .) %>%
    split(names(.)) %>%
    lapply(as.data.frame) %>%
    lapply(rowMeans) %>%
    setNames(paste0("mean.", names(.)))
  )

给予:

               railroad                     measure                    category
1  BNSF Railway Company Cars On Line - By Car Owner                      System
15 BNSF Railway Company           Train Speed (MPH)                  Intermodal
21 BNSF Railway Company      Terminal Dwell (Hours)                 Barstow, CA
33                   CN Cars On Line - By Car Owner                      System
47                   CN           Train Speed (MPH)                  Intermodal
53                   CN      Terminal Dwell (Hours) Harrison Yard (Memphis), TN
    201510  201510  201510  201511  201511 mean.201510 mean.201511
1  66923.0 66637.0 66309.0 65980.0 67034.0 66623.00000     66507.0
15    33.9    32.6    33.4    34.6    34.6    33.30000        34.6
21    39.3    56.6    44.9    37.5    43.1    46.93333        40.3
33 40227.0 40778.0 40407.0 40316.0 40174.0 40470.66667     40245.0
47    30.8    30.9    30.5    30.6    30.4    30.73333        30.5
53    17.5    18.3    17.3    17.5    18.7    17.70000        18.1

1a) purrr 我们可以选择将一些基本函数替换为它们的 purrr 或 magrittr 等价物。我们也可以在其他解决方案中翻译成 purrr。

library(magrittr)
library(purrr)

p %>%
  cbind(as.list(.) %>%
    keep(is.numeric) %>%
    split(names(.)) %>%
    map(as.data.frame) %>%
    map(rowMeans) %>%
    set_names(paste0("mean.", names(.)))
  )

2) apply/tapply 另一种可能性是分别tapply每一行。 apply 行执行此操作。

library(magrittr)

p %>%
  cbind(as.list(.) %>%
    Filter(is.numeric, .) %>%
    do.call("cbind", .) %>%
    apply(1, tapply, colnames(.), mean) %>%
    t %>%
    as.data.frame %>%
    setNames(paste0("mean.", names(.)))
  )

3) as.data.frame.table 这种方法在大多数操作中使用 dplyr 和 tidyr，但使用来自 base 的 as.data.frame.table 而不是 gather 转换为长格式，以避免添加后缀的问题。

library(dplyr)
library(magrittr)
library(tidyr)

p %>%
  cbind(as.list(.) %>%
    keep(is.numeric) %>%
    do.call("cbind", .) %>%
    as.data.frame.table %>%
    group_by(Var2, Var1) %>%
    summarize(Mean = mean(Freq)) %>%
    ungroup %>%
    spread(Var2, Mean) %>%
    select(-Var1) %>%
    set_names(paste0("mean.", names(.)))
  )

4) lm 如果 X 是数字列，mean. 是列名，则 t(coef(lm( t(X) ~ mean. - 1))) 给出所需的平均列，因此:

library(magrittr)

p %>%
  cbind(as.list(.) %>%
    Filter(is.numeric, .) %>%
    do.call("cbind", .) %>%
    { lm(t(.) ~ mean. - 1, data.frame(mean. = colnames(.))) } %>%
    coef %>%
    t
  )