r - 长转宽格式: keep row orders and use only part of row values for newly created column names

Question

我的数据:

> print(DT)
          scenario     hyear          P
 1:  flux_0_P1.0_1 2013-2014 0.14044214
 2:  flux_0_P1.0_1 2014-2015 0.09141671
 3:  flux_0_P1.0_2 2013-2014 0.69610343
 4:  flux_0_P1.0_2 2014-2015 0.52359157
 5:  flux_0_P1.0_3 2013-2014 0.89724457
 6:  flux_0_P1.0_3 2014-2015 0.78003786
 7: flux_0_P1.0_10 2013-2014 0.73752843
 8: flux_0_P1.0_10 2014-2015 0.62216371
 9: flux_0_P1.0_11 2013-2014 0.14259943
10: flux_0_P1.0_11 2014-2015 0.15309200
11: flux_0_P1.0_12 2013-2014 0.81472886
12: flux_0_P1.0_12 2014-2015 0.66015071

我想从长格式更改为宽格式:

• 将行顺序保留在新创建的宽数据框 (data.table) 列的 scenario 列中，例如1, 2, 3, 10, 11, 12 不是 1, 10, 11, 12, 2, 3

• 仅使用scenario列中行值的一部分(匹配和替换模式)作为宽数据框(data.table)中的列名称，例如从 flux_0_P1.0_1 到 P_0_P1.0_1(P 是原始数据框中值列的名称)

    hyear     P_0_P1.0_1 P_0_P1.0_2 P_0_P1.0_3 P_0_P1.0_10 P_0_P1.0_11 P_0_P1.0_12
  1 2013-2014     0.140       0.696      0.897       0.738       0.143       0.815
  2 2014-2015     0.0914      0.524      0.780       0.622       0.153       0.660
  

到目前为止我的尝试:spread 和 dcast 都更改了 key 列的顺序

### tidyverse
DT_wide_tidyr <- tidyr::spread(DT, scenario, P)
DT_wide_tidyr

> DT_wide_tidyr
# A tibble: 2 x 7
  hyear     flux_0_P1.0_1 flux_0_P1.0_10 flux_0_P1.0_11 flux_0_P1.0_12 flux_0_P1.0_2 flux_0_P1.0_3
  <chr>             <dbl>          <dbl>          <dbl>          <dbl>         <dbl>         <dbl>
1 2013-2014        0.140           0.738          0.143          0.815         0.696         0.897
2 2014-2015        0.0914          0.622          0.153          0.660         0.524         0.780

### data.table
DT_wide_dcast <- data.table::dcast(DT, hyear ~ scenario, value.var = "P")
DT_wide_dcast

> DT_wide_dcast
       hyear flux_0_P1.0_1 flux_0_P1.0_10 flux_0_P1.0_11 flux_0_P1.0_12 flux_0_P1.0_2 flux_0_P1.0_3
1: 2013-2014    0.14044214      0.7375284      0.1425994      0.8147289     0.6961034     0.8972446
2: 2014-2015    0.09141671      0.6221637      0.1530920      0.6601507     0.5235916     0.7800379

使用的数据

> dput(as.data.frame(DT))
structure(list(scenario = c("flux_0_P1.0_1", "flux_0_P1.0_1", 
"flux_0_P1.0_2", "flux_0_P1.0_2", "flux_0_P1.0_3", "flux_0_P1.0_3", 
"flux_0_P1.0_10", "flux_0_P1.0_10", "flux_0_P1.0_11", "flux_0_P1.0_11", 
"flux_0_P1.0_12", "flux_0_P1.0_12"), hyear = c("2013-2014", "2014-2015", 
"2013-2014", "2014-2015", "2013-2014", "2014-2015", "2013-2014", 
"2014-2015", "2013-2014", "2014-2015", "2013-2014", "2014-2015"
), P = structure(c(0.140442142857143, 0.0914167142857143, 0.696103428571428, 
0.523591571428571, 0.897244571428571, 0.780037857142857, 0.737528428571428, 
0.622163714285714, 0.142599428571429, 0.153092, 0.814728857142857, 
0.660150714285714))), .Names = c("scenario", 
"hyear", "P"), class = "data.frame", row.names = c(NA, -12L))

如有任何建议，我们将不胜感激!谢谢您，新年快乐!

编辑

基于@G提供的解决方案。格洛腾迪克，这就是我最终使用的:

# Set row order in scenario column
DT[, scenario := factor(scenario, levels = unique(scenario))]

# tidyr
DT_wide_tidyr <- tidyr::spread(DT, scenario, P) %>% 
  dplyr::rename_at(vars(contains("flux")), funs(sub("flux", names(DT)[3], .)))
DT_wide_tidyr

# data.table
DT_wide_dcast <- data.table::dcast(DT, hyear ~ scenario, value.var = names(DT)[3])
names(DT_wide_dcast) <- gsub("flux", names(DT)[3], names(DT_wide_dcast))
DT_wide_dcast

Answer 1

如果将scenario 列更改为具有所需顺序的给定级别的因子，则您的两种解决方案都将有效。

如果 DF 是问题末尾显示的输入，则使用此处显示的 DF2 代码:

DF2 <- transform(DF, scenario = factor(scenario, levels = unique(scenario)))

如果 wide 是代码的结果，那么这会将列名称中的 flux 更改为 P:

names(wide) <- sub("flux", "P", names(wide))