excel 如何将多个列堆叠在一起并并排放置？

1rhkuytd  于 2023-02-10  发布在  其他
关注(0)|答案(1)|浏览(161)

我的数据集如下所示

structure(list(treatment = c("T5", "T5", "T5", "T5", "T5", "T5", 
"T4", "T4", "T4", "T4", "T4", "T4", "T3", "T3", "T3", "T3", "T3", 
"T3", "T2", "T2", "T2", "T2", "T2", "T2", "T1", "T1", "T1", "T1", 
"T1", "T1", "T5", "T4", "T3", "T2", "T1", "T5", "T4", "T3", "T2", 
"T1", "T5", "T4", "T3", "T2", "T1", "T5", "T4", "T3", "T2", "T1", 
"T5", "T4", "T3", "T2", "T1"), block = c("B3", "B2", "B1", "B3", 
"B2", "B1", "B3", "B2", "B1", "B3", "B2", "B1", "B3", "B2", "B1", 
"B3", "B2", "B1", "B3", "B2", "B1", "B3", "B2", "B1", "B3", "B2", 
"B1", "B3", "B2", "B1", "B1", "B1", "B1", "B1", "B1", "B1", "B1", 
"B1", "B1", "B1", "B1", "B1", "B1", "B1", "B1", "B1", "B1", "B1", 
"B1", "B1", "B1", "B1", "B1", "B1", "B1"), position = c("B", 
"B", "B", "F", "F", "F", "B", "B", "B", "F", "F", "F", "B", "B", 
"B", "F", "F", "F", "B", "B", "B", "F", "F", "F", "B", "B", "B", 
"F", "F", "F", "F", "F", "F", "F", "F", "F", "F", "F", "F", "F", 
"F", "F", "F", "F", "F", "F", "F", "F", "F", "F", "F", "F", "F", 
"F", "F"), year = c(2022, 2022, 2022, 2022, 2022, 2022, 2022, 
2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 
2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 
2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 
2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 
2022, 2022, 2022, 2022), leaf_quality = c(5, 6, 7, 5, 7, 6, 7, 
6, 6, 6, 5, 7, 7, 7, 7, 8, 8, 8, 8, 8, 8, 5, 8, 9, 7, 7, 7, 7, 
7, 7, 4, 6, 8, 8, 6, 6, 3, 4, 7, 5, 8, 7, 8, 7, 6, 7, 7, 7, 8, 
7, 7, 5, 6, 6, 8), `1branching` = c(5, 5, 5, 3, 5, 5, 5, 4, 5, 
5, 5, 5, 5, 5, NA, 5, 5, 5, 5, 5, 5, 5, 5, 5, 4, 5, 5, 5, 5, 
5, 5, 4, 5, 5, 4, 3, 3, 3, 4, 5, 5, 5, 5, 5, 4, 4, 4, 4, 2, 2, 
4, 3, 3, 3, 3), `2branching` = c(5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 
5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, NA, 5, 5, 5, 5, 
4, 5, 5, 5, 5, 3, 4, 3, 4, 4, 4, 5, 3, 4, 3, 4, 4, 3, 3, 4, 4, 
3, 4, 4, 3), `3branching` = c(3, 5, 5, 3, 4, 5, 5, 5, 5, 5, 5, 
5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, NA, 5, 5, 5, 5, 5, 
5, 5, 5, 5, 4, 4, 4, 5, 4, 4, 5, 3, 5, 3, 4, 3, 4, 4, 5, 4, 4, 
4, 2, 4), `4branching` = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, 5, 5, 5, 4, 5, 4, 3, 5, 4, 4, 5, 3, 5, 5, 
4, 5, 5, 3, 3, 3, 3, 4, 4, 4, 3), `5branching` = c(NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 5, 5, 5, 5, 5, 4, 
3, 5, 5, 4, 5, 5, 4, 4, 5, 4, 5, 4, 4, 5, 5, 5, 4, 4, 3), `1height` = c(17, 
21, 21, 20, 20, 20, 20, 22, 18, 25, 22, 20, 19, 20, NA, 19, 20, 
21, 19, 16, 18, 17, 18, 17, 18, 18, 19, 17, 17, 16, 21, 19, 23, 
22, 25, 23, 22, 20, 22, 22, 19, 20, 19, 23, 20, 23, 25, 26, 22, 
25, 26, 23, 24, 24, 26), `2height` = c(20, 21, 21, 22, 21, 23, 
20, 23, 17, 23, 20, 19, 18, 19, 21, 19, 21, 20, 19, 17, 19, 19, 
18, 17, 19, NA, 19, 18, 17, 18, 19, 22, 23, 21, 19, 19, 17, 19, 
21, 18, 22, 23, 22, 18, 23, 24, 24, 23, 23, 21, 27, 26, 24, 26, 
24), `3height` = c(18, 18, 17, 21, 21, 21, 21, 21, 23, 22, 20, 
21, 19, 16, 20, 17, 18, 19, 20, 17, 19, 19, 18, 18, 17, NA, NA, 
18, 18, 16, 22, 22, 22, 20, 19, 21, 24, 20, 22, 23, 21, 24, 20, 
18, 23, 23, 19, 27, 25, 23, 26, 27, 24, 26, 27), `4height` = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 19, 25, 22, 
23, 20, 23, 21, 21, 21, 21, 20, 21, 19, 19, 19, 24, 25, 25, 24, 
24, 23, 27, 23, 27, 25), `5height` = c(NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, 22, 23, 23, 23, 21, 21, 22, 22, 
22, 18, 21, 20, 20, 19, 23, 23, 25, 25, 22, 25, 26, 25, 26, 28, 
23), `1_rooting` = c(9, 8, 7, 8, 8, 7, 8, 9, 8, 9, 9, 9, 8, 9, 
NA, 9, 9, 8, 8, 9, 8, 8, 9, 9, 9, 9, 9, 9, 9, 9, 8, 8, 8, 8, 
8, 8, 8, 9, 9, 8, 8, 9, 9, 9, 9, 8, 9, 9, 9, 9, 8, 8, 8, 8, 8
), `2_rooting` = c(8, 8, 7, 8, 8, 8, 9, 9, 7, 9, 8, 9, 9, 9, 
9, 9, 9, 8, 9, 9, 8, 8, 9, 9, 9, NA, 9, 9, 9, 9, 8, 8, 8, 8, 
8, 8, 3, 9, 7, 7, 9, 8, 8, 8, 9, 8, 8, 9, 9, 9, 8, 8, 8, 8, 8
), `3_rooting` = c(9, 7, 8, 9, 9, 8, 8, 9, 8, 9, 9, 9, 9, 9, 
9, 9, 9, 9, 9, 9, 9, 8, 9, 9, 9, NA, NA, 9, 9, 9, 7, 8, 8, 8, 
8, 8, 8, 6, 8, 8, 8, 9, 8, 8, 9, 8, 9, 9, 9, 8, 8, 8, 8, 8, 8
), `4_rooting` = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, 8, 8, 8, 8, 8, 8, 9, 7, 7, 7, 9, 8, 8, 8, 8, 9, 8, 
8, 9, 8, 8, 8, 8, 8, 8), `5_rooting` = c(NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, 7, 8, 8, 8, 8, 9, 7, 8, 8, 
8, 9, 9, 9, 8, 9, 9, 8, 9, 9, 9, 9, 8, 8, 8, 8), `1_weight` = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, 24, 24, 28, 31, 26, 26, 29, 27, 24, 
28, 31, 22, 24, 26, 27), `2_weight` = c(NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 30, 31, 21, 21, 22, 31, 32, 17, 25, 24, 23, 22, 32, 34, 
31), `3_weight` = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 27, 29, 23, 
22, 29, 38, 16, 25, 32, 27, 23, 23, 28, 27, 26), `4_weight` = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, 32, 20, 22, 26, 22, 30, 31, 28, 30, 
28, 22, 33, 26, 32, 20), `5_weight` = c(NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 32, 26, 19, 20, 32, 25, 30, 34, 23, 30, 35, 28, 25, 38, 
25)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-55L))

我有一些列1_rooting，2_rooting，3_rooting等。我需要把所有的根列，分支列，高度列和重量列分别堆叠起来，并把它们并排放在一起。最后的结果应该看起来像这样

我正在使用下面的代码，但它只需要1组性状的时间。我需要所有4个性状（生根，分枝，身高和体重）一起在一个excel文件并排。

branchingdata<-gather(trial,"pot_number","branching",6:10)

有什么帮助吗？谢谢
我试过合并，再转一转，但到目前为止运气不好

excel

1条答案

按热度按时间
watbbzwu

watbbzwu1#

老实说，我并不完全确定你到底想要什么，在你的预期输出中，我不清楚你是如何在“根”和“分支”列中得到1-8的，我只在你的样本dt的rooting_x列中看到6：9的值。
如果您试图融化所有的trait列并保持与每列关联的值，可以在下面找到一个选项。
你可以使用data.table::melt在列的子集上进行宽到长的转换。首先，我将创建一个列表来定义变量和值列，以及选择要融化的每个特征组的模式。我将每个参数传递给融化，子集传递给新融化的列，cbind传递结果。

library(data.table)

setDT(dta) # your sample data

melt_names = list(
  list(val = "rooting", var = "rooting_trait", pat = "^\\d_r"),
  list(val = "branching", var = "branching_trait", pat = "^\\db"),
  list(val = "height", var = "height_trait", pat = "^\\dh"),
  list(val = "weight", var = "weight_trait", pat = "^\\d_w")
)

# use do.call to cbind each list into a data.table
traits = do.call(cbind, lapply(melt_names, function(nms) {
  # melt only the columns in each trait group
  melt_d = melt(dta, measure.vars = patterns(nms$pat), variable.name = nms$var, value.name = nms$val)
  long_cols = c(nms$var, nms$val)
  # return the columns of interest
  return(melt_d[,..long_cols])
})

head(traits, 10)
rooting_trait rooting branching_trait branching height_trait height weight_trait weight
 1:     1_rooting       9      1branching         5      1height     17     1_weight     NA
 2:     1_rooting       8      1branching         5      1height     21     1_weight     NA
 3:     1_rooting       7      1branching         5      1height     21     1_weight     NA
 4:     1_rooting       8      1branching         3      1height     20     1_weight     NA
 5:     1_rooting       8      1branching         5      1height     20     1_weight     NA
 6:     1_rooting       7      1branching         5      1height     20     1_weight     NA
 7:     1_rooting       8      1branching         5      1height     20     1_weight     NA
 8:     1_rooting       9      1branching         4      1height     22     1_weight     NA
 9:     1_rooting       8      1branching         5      1height     18     1_weight     NA
10:     1_rooting       9      1branching         5      1height     25     1_weight     NA
赞(0）分享  回复(0）举报  2023-02-10
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com