在R中转置数据框，保持列名和行名不变。

huangapple
go评论134阅读模式
英文:

Transpose Data Frame with Column and Row names intact in R

问题

我正在尝试创建一个简单的转置函数，将列名保留在第一行，将第一行的数据作为列名，类似于Excel。

我编写的代码如下：

  1. Book1 <- read_excel("C:/X/X/X- X/X/Book1.xlsx")
  2. dput(Book1)
  3. X1 <- as.data.frame(t(Book1))
  4. X2 <- transpose(Book1)

这两个输出中，要么列名要么行名在输出中被排除了。

以下是我正在使用的数据框"Book1"的结构：

  1. structure(list(`Row Labels` = structure(c(1667260800, 1669852800, 
  2. 1672531200, 1675209600, 1677628800, 1680307200, 1682899200, 1685577600, 
  3. 1688169600), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
  4. X1 = c(1, 2, 2, 3, 3, 4, 4, 5, 5), X2 = c(2, 3, 2, 3, 2, 
  5. 3, 2, 3, 2), X3 = c(3, 4, 3, 4, 3, 4, 3, 4, 3), X6 = c(1, 
  6. 1, 1, 1, 1, NA, NA, NA, NA), X7 = c(1, 1, 1, 1, 1, NA, NA, 
  7. NA, NA), X8 = c(1, 1, 1, 1, 1, NA, NA, NA, NA), X9 = c(1, 
  8. 1, 1, 1, 1, NA, NA, NA, NA)), class = c("tbl_df", "tbl", 
  9. "data.frame"), row.names = c(NA, -9L))

预期输出如下：

在R中转置数据框，保持列名和行名不变。

请问有人能告诉我我做错了什么吗？

英文:

I am trying to do a simple transpose function by keeping the column names in the first row and the first rows in the column name. Similar to Excel.

The code I wrote is:

  1. Book1 &lt;- read_excel(&quot;C:/X/X/X- X/X/Book1.xlsx&quot;)
  2. dput(Book1)
  3. X1 &lt;- as.data.frame(t(Book1)) 
  4. X2 &lt;- transpose(Book1)

Both of these outputs are in such a manner that the either the column names or row names are excluded in the output.

Attached is the dataframe I am working with which is "Book1" is shown below:

  1. structure(list(`Row Labels` = structure(c(1667260800, 1669852800, 
  2. 1672531200, 1675209600, 1677628800, 1680307200, 1682899200, 1685577600, 
  3. 1688169600), class = c(&quot;POSIXct&quot;, &quot;POSIXt&quot;), tzone = &quot;UTC&quot;), 
  4.     X1 = c(1, 2, 2, 3, 3, 4, 4, 5, 5), X2 = c(2, 3, 2, 3, 2, 
  5.     3, 2, 3, 2), X3 = c(3, 4, 3, 4, 3, 4, 3, 4, 3), X6 = c(1, 
  6.     1, 1, 1, 1, NA, NA, NA, NA), X7 = c(1, 1, 1, 1, 1, NA, NA, 
  7.     NA, NA), X8 = c(1, 1, 1, 1, 1, NA, NA, NA, NA), X9 = c(1, 
  8.     1, 1, 1, 1, NA, NA, NA, NA)), class = c(&quot;tbl_df&quot;, &quot;tbl&quot;, 
  9. &quot;data.frame&quot;), row.names = c(NA, -9L))

The expected output is:

在R中转置数据框，保持列名和行名不变。

Can someone let me know what is it that I am doing wrong.

答案1

得分: 4

你可以执行以下操作：

  1. setNames(as.data.frame(t(Book1[-1])), Book1[[1]])

输出

  1.    2022-11-01 2022-12-01 2023-01-01 2023-02-01 2023-03-01 2023-04-01 2023-05-01 2023-06-01 2023-07-01
  2. X1          1          2          2          3          3          4          4          5          5
  3. X2          2          3          2          3          2          3          2          3          2
  4. X3          3          4          3          4          3          4          3          4          3
  5. X6          1          1          1          1          1         NA         NA         NA         NA
  6. X7          1          1          1          1          1         NA         NA         NA         NA
  7. X8          1          1          1          1          1         NA         NA         NA         NA
  8. X9          1          1          1          1          1         NA         NA         NA         NA

如果要将行名添加为列，请添加 tibble::rownames_to_column(var = "Row Labels")。

英文:

You can do:

  1. setNames(as.data.frame(t(Book1[-1])), Book1[[1]])

output

  1.    2022-11-01 2022-12-01 2023-01-01 2023-02-01 2023-03-01 2023-04-01 2023-05-01 2023-06-01 2023-07-01
  2. X1          1          2          2          3          3          4          4          5          5
  3. X2          2          3          2          3          2          3          2          3          2
  4. X3          3          4          3          4          3          4          3          4          3
  5. X6          1          1          1          1          1         NA         NA         NA         NA
  6. X7          1          1          1          1          1         NA         NA         NA         NA
  7. X8          1          1          1          1          1         NA         NA         NA         NA
  8. X9          1          1          1          1          1         NA         NA         NA         NA

Add tibble::rownames_to_column(var = &quot;Row Labels&quot;) to it if you want rownames to be a column.

答案2

得分: 2

使用data.frame(t(Book1))转置数据框后，您可以使用tibble包的rownames_to_column函数和janitor包的row_to_names函数。

  1. data.frame(t(Book1)) %>% tibble::rownames_to_column() %>% janitor::row_to_names(row_number = 1)
英文:

After transposing a dataframe with data.frame(t(Book1)), you can use the rownames_to_column function from the tibble package and the row_to_names function from the janitor package.

  1. data.frame(t(Book1)) %&gt;% tibble::rownames_to_column() %&gt;% janitor::row_to_names(row_number = 1)

答案3

得分: 1

我正在使用一个自定义函数来转置 data.frame。它肯定可以改进，目前它只能使用数字选择器的 id 列（默认为第一列）（将来我想添加 tidy select 语法）：

  1. library(dplyr)
  2. library(tibble)
  4. tdf <- function(df, id = 1, transform_nms = NULL, idcol_nm = NULL) {
  6.   stopifnot(is.numeric(id))
  7.   
  8.   tmp <- df[, -id]
  9.   
  10.   if(is.null(idcol_nm)) idcol_nm <- names(df[,id])
  11.   
  12.   if(!is.null(transform_nms)) {
  13.     col_nms <- transform_nms(df[[id]])
  14.   } else {
  15.     col_nms <- df[[id]]
  16.   }
  17.   new_col_nms <- c(idcol_nm, col_nms)
  18.   
  19.   out <- as.data.frame(t(tmp)) %>% rownames_to_column()
  20.   names(out) <- new_col_nms
  21.   
  22.   out
  23. }
  26. tdf(Book1, transform_nms = \(x) as.character(x))
  27. #>   行标签 2022-11-01 2022-12-01 2023-01-01 2023-02-01 2023-03-01 2023-04-01
  28. #> 1     X1          1          2          2          3          3          4
  29. #> 2     X2          2          3          2          3          2          3
  30. #> 3     X3          3          4          3          4          3          4
  31. #> 4     X6          1          1          1          1          1         NA
  32. #> 5     X7          1          1          1          1          1         NA
  33. #> 6     X8          1          1          1          1          1         NA
  34. #> 7     X9          1          1          1          1          1         NA
  35. #>   2023-05-01 2023-06-01 2023-07-01
  36. #> 1          4          5          5
  37. #> 2          2          3          2
  38. #> 3          3          4          3
  39. #> 4         NA         NA         NA
  40. #> 5         NA         NA         NA
  41. #> 6         NA         NA         NA
  42. #> 7         NA         NA         NA

OP 提供的数据

  1. Book1 <- structure(list(`行标签` = structure(c(1667260800, 1669852800, 
  2.                                           1672531200, 1675209600, 1677628800, 1680307200, 1682899200, 1685577600, 
  3.                                           1688169600), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
  4.                X1 = c(1, 2, 2, 3, 3, 4, 4, 5, 5), X2 = c(2, 3, 2, 3, 2, 
  5.                                                          3, 2, 3, 2), X3 = c(3, 4, 3, 4, 3, 4, 3, 4, 3), X6 = c(1, 
  6.                                                                                                                 1, 1, 1, 1, NA, NA, NA, NA), X7 = c(1, 1, 1, 1, 1, NA, NA, 
  7.                                                                                                                                                     NA, NA), X8 = c(1, 1, 1, 1, 1, NA, NA, NA, NA), X9 = c(1, 
  8.                                                                                                                                                                                                            1, 1, 1, 1, NA, NA, NA, NA)), class = c("tbl_df", "tbl", 
  9.                                                                                                                                                                                                                                                    "data.frame"), row.names = c(NA, -9L))

创建于 2023-03-07，使用 reprex package (v2.0.1)

英文:

I'm using a custom function to transpose data.frames. It can definitely be improved, at the moment it just works with a numeric selector of id columns default is always the first column (in the future I want to add tidy select syntax):

  1. library(dplyr)
  2. library(tibble)
  4. tdf &lt;- function(df, id = 1, transform_nms = NULL, idcol_nm = NULL) {
  6.   stopifnot(is.numeric(id))
  7.   
  8.   tmp &lt;- df[, -id]
  9.   
  10.   if(is.null(idcol_nm)) idcol_nm &lt;- names(df[,id])
  11.   
  12.   if(!is.null(transform_nms)) {
  13.     col_nms &lt;- transform_nms(df[[id]])
  14.   } else {
  15.     col_nms &lt;- df[[id]]
  16.   }
  17.   new_col_nms &lt;- c(idcol_nm, col_nms)
  18.   
  19.   out &lt;- as.data.frame(t(tmp)) %&gt;% rownames_to_column()
  20.   names(out) &lt;- new_col_nms
  21.   
  22.   out
  23. }
  26. tdf(Book1, transform_nms = \(x) as.character(x))
  27. #&gt;   Row Labels 2022-11-01 2022-12-01 2023-01-01 2023-02-01 2023-03-01 2023-04-01
  28. #&gt; 1         X1          1          2          2          3          3          4
  29. #&gt; 2         X2          2          3          2          3          2          3
  30. #&gt; 3         X3          3          4          3          4          3          4
  31. #&gt; 4         X6          1          1          1          1          1         NA
  32. #&gt; 5         X7          1          1          1          1          1         NA
  33. #&gt; 6         X8          1          1          1          1          1         NA
  34. #&gt; 7         X9          1          1          1          1          1         NA
  35. #&gt;   2023-05-01 2023-06-01 2023-07-01
  36. #&gt; 1          4          5          5
  37. #&gt; 2          2          3          2
  38. #&gt; 3          3          4          3
  39. #&gt; 4         NA         NA         NA
  40. #&gt; 5         NA         NA         NA
  41. #&gt; 6         NA         NA         NA
  42. #&gt; 7         NA         NA         NA

Data from OP

  1. Book1 &lt;- structure(list(`Row Labels` = structure(c(1667260800, 1669852800, 
  2.                                           1672531200, 1675209600, 1677628800, 1680307200, 1682899200, 1685577600, 
  3.                                           1688169600), class = c(&quot;POSIXct&quot;, &quot;POSIXt&quot;), tzone = &quot;UTC&quot;), 
  4.                X1 = c(1, 2, 2, 3, 3, 4, 4, 5, 5), X2 = c(2, 3, 2, 3, 2, 
  5.                                                          3, 2, 3, 2), X3 = c(3, 4, 3, 4, 3, 4, 3, 4, 3), X6 = c(1, 
  6.                                                                                                                 1, 1, 1, 1, NA, NA, NA, NA), X7 = c(1, 1, 1, 1, 1, NA, NA, 
  7.                                                                                                                                                     NA, NA), X8 = c(1, 1, 1, 1, 1, NA, NA, NA, NA), X9 = c(1, 
  8.                                                                                                                                                                                                            1, 1, 1, 1, NA, NA, NA, NA)), class = c(&quot;tbl_df&quot;, &quot;tbl&quot;, 
  9.                                                                                                                                                                                                                                                    &quot;data.frame&quot;), row.names = c(NA, -9L))

<sup>Created on 2023-03-07 by the reprex package (v2.0.1)</sup>

答案4

得分: 1

使用data.table库：

  1. library(data.table)
  2. setDT(Book1)

使用data.table::transpose()方法：

  1. X2 <- data.table::transpose(Book1, keep.names = "Row Labels", make.names = "Row Labels")

使用melt和dcast的方法：

  1. X2 <- setnames(dcast(melt(Book1, id.vars = "Row Labels"), variable ~ `Row Labels`), "variable", "Row Labels")

结果：

  1. X2
  3.    Row Labels 2022-11-01 2022-12-01 2023-01-01 2023-02-01 2023-03-01 2023-04-01 2023-05-01 2023-06-01 2023-07-01
  4. 1:         X1          1          2          2          3          3          4          4          5          5
  5. 2:         X2          2          3          2          3          2          3          2          3          2
  6. 3:         X3          3          4          3          4          3          4          3          4          3
  7. 4:         X6          1          1          1          1          1         NA         NA         NA         NA
  8. 5:         X7          1          1          1          1          1         NA         NA         NA         NA
  9. 6:         X8          1          1          1          1          1         NA         NA         NA         NA
  10. 7:         X9          1          1          1          1          1         NA         NA         NA         NA

以上是代码的翻译部分，不包括代码中的注释或其他说明。

英文:

using data.table

  1. library(data.table)
  3. setDT(Book1)

method 1 using data.table::transpose()

  1. X2 &lt;- data.table::transpose(Book1, keep.names = &quot;Row Labels&quot;, make.names = &quot;Row Labels&quot;)

method 2 using melt and dcast

  1. X2 &lt;- setnames(dcast(melt(Book1, id.vars = &quot;Row Labels&quot;), variable ~ `Row Labels`), &quot;variable&quot;, &quot;Row Labels&quot;)

results

  1. X2
  3.    Row Labels 2022-11-01 2022-12-01 2023-01-01 2023-02-01 2023-03-01 2023-04-01 2023-05-01 2023-06-01 2023-07-01
  4. 1:         X1          1          2          2          3          3          4          4          5          5
  5. 2:         X2          2          3          2          3          2          3          2          3          2
  6. 3:         X3          3          4          3          4          3          4          3          4          3
  7. 4:         X6          1          1          1          1          1         NA         NA         NA         NA
  8. 5:         X7          1          1          1          1          1         NA         NA         NA         NA
  9. 6:         X8          1          1          1          1          1         NA         NA         NA         NA
  10. 7:         X9          1          1          1          1          1         NA         NA         NA         NA

huangapple
  • 本文由 发表于 2023年3月7日 18:47:59
  • 转载请务必保留本文链接：https://go.coder-hub.com/75661006.html
  • dataframe
  • r
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定