英文:
Transpose Data Frame with Column and Row names intact in R
问题
我正在尝试创建一个简单的转置函数，将列名保留在第一行，将第一行的数据作为列名，类似于Excel。
我编写的代码如下：
Book1 <- read_excel("C:/X/X/X- X/X/Book1.xlsx")
dput(Book1)
X1 <- as.data.frame(t(Book1))
X2 <- transpose(Book1)
这两个输出中，要么列名要么行名在输出中被排除了。
以下是我正在使用的数据框"Book1"的结构：
structure(list(`Row Labels` = structure(c(1667260800, 1669852800,
1672531200, 1675209600, 1677628800, 1680307200, 1682899200, 1685577600,
1688169600), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
X1 = c(1, 2, 2, 3, 3, 4, 4, 5, 5), X2 = c(2, 3, 2, 3, 2,
3, 2, 3, 2), X3 = c(3, 4, 3, 4, 3, 4, 3, 4, 3), X6 = c(1,
1, 1, 1, 1, NA, NA, NA, NA), X7 = c(1, 1, 1, 1, 1, NA, NA,
NA, NA), X8 = c(1, 1, 1, 1, 1, NA, NA, NA, NA), X9 = c(1,
1, 1, 1, 1, NA, NA, NA, NA)), class = c("tbl_df", "tbl",
"data.frame"), row.names = c(NA, -9L))
预期输出如下：
请问有人能告诉我我做错了什么吗？
英文:
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:
Book1 <- read_excel("C:/X/X/X- X/X/Book1.xlsx")
dput(Book1)
X1 <- as.data.frame(t(Book1))
X2 <- 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:
structure(list(`Row Labels` = structure(c(1667260800, 1669852800,
1672531200, 1675209600, 1677628800, 1680307200, 1682899200, 1685577600,
1688169600), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
X1 = c(1, 2, 2, 3, 3, 4, 4, 5, 5), X2 = c(2, 3, 2, 3, 2,
3, 2, 3, 2), X3 = c(3, 4, 3, 4, 3, 4, 3, 4, 3), X6 = c(1,
1, 1, 1, 1, NA, NA, NA, NA), X7 = c(1, 1, 1, 1, 1, NA, NA,
NA, NA), X8 = c(1, 1, 1, 1, 1, NA, NA, NA, NA), X9 = c(1,
1, 1, 1, 1, NA, NA, NA, NA)), class = c("tbl_df", "tbl",
"data.frame"), row.names = c(NA, -9L))
The expected output is:
Can someone let me know what is it that I am doing wrong.
答案1
得分: 4
你可以执行以下操作：
setNames(as.data.frame(t(Book1[-1])), Book1[[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
X1 1 2 2 3 3 4 4 5 5
X2 2 3 2 3 2 3 2 3 2
X3 3 4 3 4 3 4 3 4 3
X6 1 1 1 1 1 NA NA NA NA
X7 1 1 1 1 1 NA NA NA NA
X8 1 1 1 1 1 NA NA NA NA
X9 1 1 1 1 1 NA NA NA NA
如果要将行名添加为列，请添加 tibble::rownames_to_column(var = "Row Labels")。
英文:
You can do:
setNames(as.data.frame(t(Book1[-1])), Book1[[1]])
output
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
X1 1 2 2 3 3 4 4 5 5
X2 2 3 2 3 2 3 2 3 2
X3 3 4 3 4 3 4 3 4 3
X6 1 1 1 1 1 NA NA NA NA
X7 1 1 1 1 1 NA NA NA NA
X8 1 1 1 1 1 NA NA NA NA
X9 1 1 1 1 1 NA NA NA NA
Add tibble::rownames_to_column(var = "Row Labels") to it if you want rownames to be a column.
答案2
得分: 2
使用data.frame(t(Book1))转置数据框后，您可以使用tibble包的rownames_to_column函数和janitor包的row_to_names函数。
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.
data.frame(t(Book1)) %>% tibble::rownames_to_column() %>% janitor::row_to_names(row_number = 1)
答案3
得分: 1
我正在使用一个自定义函数来转置 data.frame。它肯定可以改进，目前它只能使用数字选择器的 id 列（默认为第一列）（将来我想添加 tidy select 语法）：
library(dplyr)
library(tibble)
tdf <- function(df, id = 1, transform_nms = NULL, idcol_nm = NULL) {
stopifnot(is.numeric(id))
tmp <- df[, -id]
if(is.null(idcol_nm)) idcol_nm <- names(df[,id])
if(!is.null(transform_nms)) {
col_nms <- transform_nms(df[[id]])
} else {
col_nms <- df[[id]]
}
new_col_nms <- c(idcol_nm, col_nms)
out <- as.data.frame(t(tmp)) %>% rownames_to_column()
names(out) <- new_col_nms
out
}
tdf(Book1, transform_nms = \(x) as.character(x))
#> 行标签 2022-11-01 2022-12-01 2023-01-01 2023-02-01 2023-03-01 2023-04-01
#> 1 X1 1 2 2 3 3 4
#> 2 X2 2 3 2 3 2 3
#> 3 X3 3 4 3 4 3 4
#> 4 X6 1 1 1 1 1 NA
#> 5 X7 1 1 1 1 1 NA
#> 6 X8 1 1 1 1 1 NA
#> 7 X9 1 1 1 1 1 NA
#> 2023-05-01 2023-06-01 2023-07-01
#> 1 4 5 5
#> 2 2 3 2
#> 3 3 4 3
#> 4 NA NA NA
#> 5 NA NA NA
#> 6 NA NA NA
#> 7 NA NA NA
OP 提供的数据
Book1 <- structure(list(`行标签` = structure(c(1667260800, 1669852800,
1672531200, 1675209600, 1677628800, 1680307200, 1682899200, 1685577600,
1688169600), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
X1 = c(1, 2, 2, 3, 3, 4, 4, 5, 5), X2 = c(2, 3, 2, 3, 2,
3, 2, 3, 2), X3 = c(3, 4, 3, 4, 3, 4, 3, 4, 3), X6 = c(1,
1, 1, 1, 1, NA, NA, NA, NA), X7 = c(1, 1, 1, 1, 1, NA, NA,
NA, NA), X8 = c(1, 1, 1, 1, 1, NA, NA, NA, NA), X9 = c(1,
1, 1, 1, 1, NA, NA, NA, NA)), class = c("tbl_df", "tbl",
"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):
library(dplyr)
library(tibble)
tdf <- function(df, id = 1, transform_nms = NULL, idcol_nm = NULL) {
stopifnot(is.numeric(id))
tmp <- df[, -id]
if(is.null(idcol_nm)) idcol_nm <- names(df[,id])
if(!is.null(transform_nms)) {
col_nms <- transform_nms(df[[id]])
} else {
col_nms <- df[[id]]
}
new_col_nms <- c(idcol_nm, col_nms)
out <- as.data.frame(t(tmp)) %>% rownames_to_column()
names(out) <- new_col_nms
out
}
tdf(Book1, transform_nms = \(x) as.character(x))
#> Row Labels 2022-11-01 2022-12-01 2023-01-01 2023-02-01 2023-03-01 2023-04-01
#> 1 X1 1 2 2 3 3 4
#> 2 X2 2 3 2 3 2 3
#> 3 X3 3 4 3 4 3 4
#> 4 X6 1 1 1 1 1 NA
#> 5 X7 1 1 1 1 1 NA
#> 6 X8 1 1 1 1 1 NA
#> 7 X9 1 1 1 1 1 NA
#> 2023-05-01 2023-06-01 2023-07-01
#> 1 4 5 5
#> 2 2 3 2
#> 3 3 4 3
#> 4 NA NA NA
#> 5 NA NA NA
#> 6 NA NA NA
#> 7 NA NA NA
Data from OP
Book1 <- structure(list(`Row Labels` = structure(c(1667260800, 1669852800,
1672531200, 1675209600, 1677628800, 1680307200, 1682899200, 1685577600,
1688169600), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
X1 = c(1, 2, 2, 3, 3, 4, 4, 5, 5), X2 = c(2, 3, 2, 3, 2,
3, 2, 3, 2), X3 = c(3, 4, 3, 4, 3, 4, 3, 4, 3), X6 = c(1,
1, 1, 1, 1, NA, NA, NA, NA), X7 = c(1, 1, 1, 1, 1, NA, NA,
NA, NA), X8 = c(1, 1, 1, 1, 1, NA, NA, NA, NA), X9 = c(1,
1, 1, 1, 1, NA, NA, NA, NA)), class = c("tbl_df", "tbl",
"data.frame"), row.names = c(NA, -9L))
<sup>Created on 2023-03-07 by the reprex package (v2.0.1)</sup>
答案4
得分: 1
使用data.table库：
library(data.table)
setDT(Book1)
使用data.table::transpose()方法：
X2 <- data.table::transpose(Book1, keep.names = "Row Labels", make.names = "Row Labels")
使用melt和dcast的方法：
X2 <- setnames(dcast(melt(Book1, id.vars = "Row Labels"), variable ~ `Row Labels`), "variable", "Row Labels")
结果：
X2
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
1: X1 1 2 2 3 3 4 4 5 5
2: X2 2 3 2 3 2 3 2 3 2
3: X3 3 4 3 4 3 4 3 4 3
4: X6 1 1 1 1 1 NA NA NA NA
5: X7 1 1 1 1 1 NA NA NA NA
6: X8 1 1 1 1 1 NA NA NA NA
7: X9 1 1 1 1 1 NA NA NA NA
以上是代码的翻译部分，不包括代码中的注释或其他说明。
英文:
using data.table
library(data.table)
setDT(Book1)
method 1 using data.table::transpose()
X2 <- data.table::transpose(Book1, keep.names = "Row Labels", make.names = "Row Labels")
method 2 using melt and dcast
X2 <- setnames(dcast(melt(Book1, id.vars = "Row Labels"), variable ~ `Row Labels`), "variable", "Row Labels")
results
X2
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
1: X1 1 2 2 3 3 4 4 5 5
2: X2 2 3 2 3 2 3 2 3 2
3: X3 3 4 3 4 3 4 3 4 3
4: X6 1 1 1 1 1 NA NA NA NA
5: X7 1 1 1 1 1 NA NA NA NA
6: X8 1 1 1 1 1 NA NA NA NA
7: X9 1 1 1 1 1 NA NA NA NA
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论