将3列一组的多列表格重塑为3列表格[重复]

你已经注意到了 unlist 按列显示值:

unlist(df[1:3], use.names = FALSE)
##  [1]  1  4  7 10  2  5  8 11  3  6  9 12

要按行获取值,可以使用 c(t(...)) 习语:

c(t(df[1:3]))
##  [1]  1  2  3  4  5  6  7  8  9 10 11 12

这将允许您使用以下方法解决base R中的问题:

as.data.frame(matrix(c(t(df[1:3]), t(df[4:6])), ncol = 3, byrow = TRUE))
##   V1 V2 V3
## 1  1  2  3
## 2  4  5  6
## 3  7  8  9
## 4 10 11 12
## 5 13 14 15
## 6 16 NA NA
## 7 19 20 21
## 8 22 23 24

作为一个函数,您可以尝试以下操作:

splitter <- function(indf, ncols) {
  if (ncol(indf) %% ncols != 0) stop("Not the right number of columns to split")
  inds <- split(sequence(ncol(indf)), c(0, sequence(ncol(indf)-1) %/% ncols))
  temp <- unlist(lapply(inds, function(x) c(t(indf[x]))), use.names = FALSE)
  as.data.frame(matrix(temp, ncol = ncols, byrow = TRUE))
}
splitter(df, 3)

更灵活的“数据表”方法如下所示:

library(data.table)
rbindlist(split.default(as.data.table(df), 
                        c(0, sequence(ncol(df)-1) %/% 3)), 
          use.names = FALSE)
##    V1 V2 V3
## 1:  1  2  3
## 2:  4  5  6
## 3:  7  8  9
## 4: 10 11 12
## 5: 13 14 15
## 6: 16 NA NA
## 7: 19 20 21
## 8: 22 23 24

我很惊讶没有人提到 split.default ,它还可以处理具有更多列的数据:

x <- split.default(df, ceiling(seq_along(df) / 3 ))
do.call(rbind, lapply(x, setNames, names(x[[1]])))

#     V1 V2 V3
# 1.1  1  2  3
# 1.2  4  5  6
# 1.3  7  8  9
# 1.4 10 11 12
# 2.1 13 14 15
# 2.2 16 NA NA
# 2.3 19 20 21
# 2.4 22 23 24

添加 make.row.names = FALSE 要消除奇数行名称,请执行以下操作:

do.call(rbind, c(lapply(x, setNames, names(x[[1]])), list(make.row.names = FALSE)))
#   V1 V2 V3
# 1  1  2  3
# 2  4  5  6
# 3  7  8  9
# 4 10 11 12
# 5 13 14 15
# 6 16 NA NA
# 7 19 20 21
# 8 22 23 24

data.table :-

df <- data.frame(V1 = c(1, 4, 7, 10), V2 = c(2, 5, 8, 11), V3 = c(3, 6, 9, 12), V4 = c(13, 16, 19, 22), V5 = c(14, NA, 20, 23), V6 = c(15, NA, 21, 24))


library(data.table)
setDT(df)
df1 <- df[, c("V4", "V5", "V6")]
setnames(df1, "V4", "V1")
setnames(df1, "V5", "V2")
setnames(df1, "V6", "V3")
df <- df[, c("V1", "V2", "V3")]
df <- rbind(df, df1)

输出将为:-

   V1 V2 V3
1:  1  2  3
2:  4  5  6
3:  7  8  9
4: 10 11 12
5: 13 14 15
6: 16 NA NA
7: 19 20 21
8: 22 23 24

解决方案使用 dplyr 和 tidyr .

library(dplyr)
library(tidyr)

dt2 <- dt %>%
  gather(Column, Value) %>%
  extract(Column, into = c("Group", "Index"), regex = "([A-Z+])([\\d].*$)",
          convert = TRUE) %>%
  mutate(Index = Index %% 3) %>%
  mutate(Index = ifelse(Index == 0, 3, Index)) %>%
  unite(Column, c("Group", "Index"), sep = "") %>%
  group_by(Column) %>%
  mutate(ID = 1:n()) %>%
  spread(Column, Value) %>%
  select(-ID)
dt2
# # A tibble: 8 x 3
#      V1    V2    V3
# * <int> <int> <int>
# 1     1     2     3
# 2     4     5     6
# 3     7     8     9
# 4    10    11    12
# 5    13    14    15
# 6    16    NA    NA
# 7    19    20    21
# 8    22    23    24

数据

dt <- read.table(text = "              V1       V2        V3         V4      V5       V6
  1             1         2         3         13      14       15
                 2             4         5         6         16      NA       NA 
                 3             7         8         9         19      20       21 
                 4             10        11        12        22      23       24",
                 header = TRUE)

使现代化

下面是一个示例,显示该代码也可以在更大的数据帧上工作。

library(dplyr)
library(tidyr)

# Create example data frame
dt <- as_data_frame(matrix(1:60, ncol = 12, byrow = TRUE))

dt2 <- dt %>%
  gather(Column, Value) %>%
  extract(Column, into = c("Group", "Index"), regex = "([A-Z+])([\\d].*$)",
          convert = TRUE) %>%
  mutate(Index = Index %% 3) %>%
  mutate(Index = ifelse(Index == 0, 3, Index)) %>%
  unite(Column, c("Group", "Index"), sep = "") %>%
  group_by(Column) %>%
  mutate(ID = 1:n()) %>%
  spread(Column, Value) %>%
  select(-ID)
dt2
# # A tibble: 20 x 3
#      V1    V2    V3
# * <int> <int> <int>
#  1     1     2     3
#  2    13    14    15
#  3    25    26    27
#  4    37    38    39
#  5    49    50    51
#  6     4     5     6
#  7    16    17    18
#  8    28    29    30
#  9    40    41    42
# 10    52    53    54
# 11     7     8     9
# 12    19    20    21
# 13    31    32    33
# 14    43    44    45
# 15    55    56    57
# 16    10    11    12
# 17    22    23    24
# 18    34    35    36
# 19    46    47    48
# 20    58    59    60

以下是任意数量列的通用解决方案,使用 dplyr .

测试数据 data :

# A tibble: 5 x 9
     V1    V2    V3    V4    V5    V6    V7    V8    V9
  <int> <int> <int> <int> <int> <int> <int> <int> <int>
1     1     2     3     4     5     6     7     8     9
2    10    11    12    13    14    15    16    17    18
3    19    20    21    22    23    24    25    26    27
4    28    29    30    31    32    33    34    35    36
5    37    38    39    40    41    42    43    44    45

代码:

for (i in seq(1, ncol(data), by = 3)) {
  if (i == 1) {
    out <- select(data, 1:3)
  } else {
    out <- select(data, i:(i+2)) %>% setNames(names(out)) %>% bind_rows(out, .)
  }
}

输出 out :

# A tibble: 15 x 3
      V1    V2    V3
   <int> <int> <int>
 1     1     2     3
 2    10    11    12
 3    19    20    21
 4    28    29    30
 5    37    38    39
 6     4     5     6
 7    13    14    15
 8    22    23    24
 9    31    32    33
10    40    41    42
11     7     8     9
12    16    17    18
13    25    26    27
14    34    35    36
15    43    44    45