r - 如何将数据从宽格式排列到长格式，并指定关系

Question

目前我有一个文件需要从宽格式转换为长格式。数据的例子是:

Subject,Cat1_Weight,Cat2_Weight,Cat3_Weight,Cat1_Sick,Cat2_Sick,Cat3_Sick
1,10,11,12,1,0,0
2,7,8,9,1,0,0

但是，我需要如下的长格式

Subject,CatNumber,Weight,Sickness
1,1,10,1
1,2,11,0
1,3,12,0
2,1,7,1
2,2,8,0
2,3,9,0

到目前为止，我已经尝试在 R 中使用 melt 函数

datalong <- melt(exp2_simon_shortform, id ="Subject")

但它将每个单独的列名称视为一个唯一的变量，每个变量都有自己的值。有谁知道我如何根据列标题名称从宽到长？

干杯。

编辑:我意识到我犯了一个错误。我的最终输出需要如下所示。所以从 Cat1_ 部分，我实际上需要取出“Cat”和“1”

Subject Animal  CatNumber   Weight  Sickness
1   Cat 1   10  1
1   Cat 2   11  0
1   Cat 3   12  0
2   Cat 1   7   1
2   Cat 2   8   0
2   Cat 3   9   0

非常感谢任何更新的解决方案。

Answer 1

“dplyr”+“tidyr”方法可能是这样的:

library(dplyr)
library(tidyr)
mydf %>%
  gather(var, val, -Subject) %>%
  separate(var, into = c("CatNumber", "variable")) %>%
  spread(variable, val) 
#   Subject CatNumber Sick Weight
# 1       1      Cat1    1     10
# 2       1      Cat2    0     11
# 3       1      Cat3    0     12
# 4       2      Cat1    1      7
# 5       2      Cat2    0      8
# 6       2      Cat3    0      9

在其中添加 mutate 以及 gsub 以删除“CatNumber”列的“Cat”部分。

---

更新

基于 the discussions in chat ，您的数据实际上看起来更像是:

A = c("ATCint", "Blank", "None"); B = 1:5; C = c("ResumptionTime", "ResumptionMisses")

colNames <- expand.grid(A, B, C)
colNames <- sprintf("%s%d_%s", colNames[[1]], colNames[[2]], colNames[[3]])

subject = 1:60

set.seed(1)
M <- matrix(sample(10, length(subject) * length(colNames), TRUE), 
            nrow = length(subject), dimnames = list(NULL, colNames))

mydf <- data.frame(Subject = subject, M)

因此，您需要执行一些额外的步骤才能获得所需的输出。尝试:

library(dplyr)
library(tidyr)
mydf %>% 
  group_by(Subject) %>%                    ## Your ID variable
  gather(var, val, -Subject) %>%           ## Make long data. Everything except your IDs
  separate(var, into = c("partA", "partB")) %>%  ## Split new column into two parts
  mutate(partA = gsub("(.*)([0-9]+)", "\\1_\\2", partA)) %>% ## Make new col easy to split
  separate(partA, into = c("A1", "A2")) %>%                  ## Split this new column
  spread(partB, val)                                         ## Transform to wide form

产生:

Source: local data frame [900 x 5]

   Subject     A1    A2 ResumptionMisses ResumptionTime
     (int)  (chr) (chr)            (int)          (int)
1        1 ATCint     1                9              3
2        1 ATCint     2                4              3
3        1 ATCint     3                2              2
4        1 ATCint     4                7              4
5        1 ATCint     5                7              1
6        1  Blank     1                4             10
7        1  Blank     2                2              4
8        1  Blank     3                7              5
9        1  Blank     4                1              9
10       1  Blank     5               10             10
..     ...    ...   ...              ...            ...