Rearrange Data in R, Breaking Apart Column Names

Question

I have been given a table which I need to transform into two other forms. Can anyone help me find a systematic R-way to convert it without a bunch of nested for loops?

Here's a simplified version of the table:

Status <- rep(c(paste0("B",seq(1,4)),"Total"),3)
FID <- c(rep("N123",10),rep("K541",5))
IID <- c(rep(123,5),rep(456,5),rep(789,5))

Value1.G1 <- c(rep(c(888,345,765,875,875,323),2),8039,830,849)
Value2.G1 <- c(rep(c(443,325,761),4),649,975,323)
Value1.G2 <- rep(c(446,345,765,875,323),3)
Value2.G2 <- c(rep(c(540,345,765),4),169,875,431)

dat <-data.frame(FID,IID,Status,Value1.G1,Value2.G1,Value1.G2,Value2.G2)
print(dat)
    FID IID Status Value1.G1 Value2.G1 Value1.G2 Value2.G2
1  N123 123     B1       888       443       446       540
2  N123 123     B2       345       325       345       345
3  N123 123     B3       765       761       765       765
4  N123 123     B4       875       443       875       540
5  N123 123  Total       875       325       323       345
6  N123 456     B1       323       761       446       765
7  N123 456     B2       888       443       345       540
8  N123 456     B3       345       325       765       345
9  N123 456     B4       765       761       875       765
10 N123 456  Total       875       443       323       540
11 K541 789     B1       875       325       446       345
12 K541 789     B2       323       761       345       765
13 K541 789     B3      8039       649       765       169
14 K541 789     B4       830       975       875       875
15 K541 789  Total       849       323       323       431

In brief, excluding the first three columns, the number in each cell is a specific value (in this example Value1 or Value2) for a specific sample (123,456, and 789), on a specific gel (1 and 2), at a specific status (B1,B2,B3,B4,and Total). The first three columns (FID,IID,Status) list information about the sample (FID,IID) and the status (Status).

First Form

The first form I need to create breaks down the titles into their component parts. (Point of clarification: the "Gel" column refers to the previous numeric value after "G" in the column name.)

    FID IID Status Value1 Value2 Gel
1  N123 123     B1    888    443   1
2  N123 456     B1    323    761   1
3  K541 789     B1    875    325   1
4  N123 123     B1    446    540   2
5  N123 456     B1    446    765   2
6  K541 789     B1    446    345   2
7  N123 123     B2    345    325   1
8  N123 456     B2    888    443   1
9  K541 789     B2    323    761   1
10 N123 123     B2    345    345   2
11 N123 456     B2    345    540   2
12 K541 789     B2    345    765   2
13 N123 123     B3    765    761   1
14 N123 456     B3    345    325   1
15 K541 789     B3   8039    649   1
16 N123 123     B3    765    765   2
17 N123 456     B3    765    345   2
18 K541 789     B3    765    169   2
19 N123 123     B4    875    443   1
20 N123 456     B4    765    761   1
21 K541 789     B4    830    975   1
22 N123 123     B4    875    540   2
23 N123 456     B4    875    765   2
24 K541 789     B4    875    875   2
25 N123 123  Total    875    325   1
26 N123 456  Total    875    443   1
27 K541 789  Total    849    323   1
28 N123 123  Total    323    345   2
29 N123 456  Total    323    540   2
30 K541 789  Total    323    431   2

Second Form

The second form I need to create requires that there be a single row for each unique individual id (IID) containing all of it's value information. In this case, the column names indicate all of the information about the the value and it's specific conditions (i.e. which value, which gel and which status). With this example data, that means that there are 3 rows and 22 columns.

   FID IID Value1.G1.B1 Value2.G1.B1 Value1.G2.B1 Value2.G2.B1 Value1.G1.B2 Value2.G1.B2 Value1.G2.B2
1 N123 123          888          443          446          540          345          325          345
2 N123 456          323          761          446          765          888          443          345
3 K541 789          875          325          446          345          323          761          345
  Value2.G2.B2 Value1.G1.B3 Value2.G1.B3 Value1.G2.B3 Value2.G2.B3 Value1.G1.B4 Value2.G1.B4 Value1.G2.B4
1          345          765          761          765          765          875          443          875
2          540          345          325          765          345          765          761          875
3          765         8039          649          765          169          830          975          875
  Value2.G2.B4 Value1.G1.Total Value2.G1.Total Value1.G2.Total Value2.G2.Total
1          540             875             325             323             345
2          765             875             443             323             540
3          875             849             323             323             431

The process is called "reshaping". Look at the `reshape` function, as well as packages like "tidyr" and "reshape2". — A5C1D2H2I1M1N2O1R2T1, Apr 03 '15 at 17:57

rawr · Accepted Answer · 2015-04-03T18:32:21.487

using ?reshape

Status <- rep(c(paste0("B",seq(1,4)),"Total"),3)
FID <- c(rep("N123",10),rep("K541",5))
IID <- c(rep(123,5),rep(456,5),rep(789,5))

Value1.G1 <- c(rep(c(888,345,765,875,875,323),2),8039,830,849)
Value2.G1 <- c(rep(c(443,325,761),4),649,975,323)
Value1.G2 <- rep(c(446,345,765,875,323),3)
Value2.G2 <- c(rep(c(540,345,765),4),169,875,431)

dat <-data.frame(FID,IID,Status,Value1.G1,Value2.G1,Value1.G2,Value2.G2)
dat

for long format

l <- reshape(dat, direction = 'long', varying = list(c(4,6), c(5,7)),
             v.names = c('Value1', 'Value2'), timevar = 'Gel')
l[order(l$Status), ]

#       FID IID Status Gel Value1 Value2 id
# 1.1  N123 123     B1   1    888    443  1
# 6.1  N123 456     B1   1    323    761  6
# 11.1 K541 789     B1   1    875    325 11
# 1.2  N123 123     B1   2    446    540  1
# 6.2  N123 456     B1   2    446    765  6
# 11.2 K541 789     B1   2    446    345 11
# 2.1  N123 123     B2   1    345    325  2
# 7.1  N123 456     B2   1    888    443  7
# 12.1 K541 789     B2   1    323    761 12
# 2.2  N123 123     B2   2    345    345  2
# 7.2  N123 456     B2   2    345    540  7
# 12.2 K541 789     B2   2    345    765 12
# 3.1  N123 123     B3   1    765    761  3
# 8.1  N123 456     B3   1    345    325  8
# 13.1 K541 789     B3   1   8039    649 13
# 3.2  N123 123     B3   2    765    765  3
# 8.2  N123 456     B3   2    765    345  8
# 13.2 K541 789     B3   2    765    169 13
# 4.1  N123 123     B4   1    875    443  4
# 9.1  N123 456     B4   1    765    761  9
# 14.1 K541 789     B4   1    830    975 14
# 4.2  N123 123     B4   2    875    540  4
# 9.2  N123 456     B4   2    875    765  9
# 14.2 K541 789     B4   2    875    875 14
# 5.1  N123 123  Total   1    875    325  5
# 10.1 N123 456  Total   1    875    443 10
# 15.1 K541 789  Total   1    849    323 15
# 5.2  N123 123  Total   2    323    345  5
# 10.2 N123 456  Total   2    323    540 10
# 15.2 K541 789  Total   2    323    431 15

and for wide

reshape(dat, direction = 'wide', timevar = 'Status', idvar = names(dat)[1:2])

#     FID IID Value1.G1.B1 Value2.G1.B1 Value1.G2.B1 Value2.G2.B1 Value1.G1.B2
# 1  N123 123          888          443          446          540          345
# 6  N123 456          323          761          446          765          888
# 11 K541 789          875          325          446          345          323
#    Value2.G1.B2 Value1.G2.B2 Value2.G2.B2 Value1.G1.B3 Value2.G1.B3 Value1.G2.B3
# 1           325          345          345          765          761          765
# 6           443          345          540          345          325          765
# 11          761          345          765         8039          649          765
#    Value2.G2.B3 Value1.G1.B4 Value2.G1.B4 Value1.G2.B4 Value2.G2.B4 Value1.G1.Total
# 1           765          875          443          875          540             875
# 6           345          765          761          875          765             875
# 11          169          830          975          875          875             849
#    Value2.G1.Total Value1.G2.Total Value2.G2.Total
# 1              325             323             345
# 6              443             323             540
# 11             323             323             431

akrun · Answer 2 · 2015-04-03T20:29:12.553

You could try melt, dcast from the devel version of data.table i.e. v1.9.5 which can take multiple value columns. It can be installed from here

For converting from 'wide' to 'long', use melt on the 'data.table' (setDT(dat) - converts 'data.frame' to 'data.table') and specify the index of 'Value1', and 'Value2' as a 'list' in the measure.vars

library(data.table)#v1.9.5+
dM <- melt(setDT(dat), measure.vars=list(c(4,6), c(5,7)), variable.name='Gel')

dM[order(Status)]
#     FID IID Status Gel value1 value2
# 1: N123 123     B1   1    888    443
# 2: N123 456     B1   1    323    761
# 3: K541 789     B1   1    875    325
# 4: N123 123     B1   2    446    540
# 5: N123 456     B1   2    446    765
# 6: K541 789     B1   2    446    345
# 7: N123 123     B2   1    345    325
# 8: N123 456     B2   1    888    443
# 9: K541 789     B2   1    323    761
#10: N123 123     B2   2    345    345
#11: N123 456     B2   2    345    540
#12: K541 789     B2   2    345    765
#13: N123 123     B3   1    765    761
#14: N123 456     B3   1    345    325
#15: K541 789     B3   1   8039    649
#16: N123 123     B3   2    765    765
#17: N123 456     B3   2    765    345
#18: K541 789     B3   2    765    169
#19: N123 123     B4   1    875    443
#20: N123 456     B4   1    765    761
#21: K541 789     B4   1    830    975
#22: N123 123     B4   2    875    540
#23: N123 456     B4   2    875    765
#24: K541 789     B4   2    875    875
#25: N123 123  Total   1    875    325
#26: N123 456  Total   1    875    443
#27: K541 789  Total   1    849    323
#28: N123 123  Total   2    323    345
#29: N123 456  Total   2    323    540
#30: K541 789  Total   2    323    431

We can convert the 'long' format to 'wide' with dcast. Here, we specify the multiple value columns in value.var

dC <- dcast(dM, FID+IID~Gel+Status, value.var=c('value1', 'value2'))
dC
#   FID IID 1_B1_value1 1_B2_value1 1_B3_value1 1_B4_value1 1_Total_value1
#1: K541 789         875         323        8039         830            849
#2: N123 123         888         345         765         875            875
#3: N123 456         323         888         345         765            875
#   2_B1_value1 2_B2_value1 2_B3_value1 2_B4_value1 2_Total_value1 1_B1_value2
#1:         446         345         765         875            323         325
#2:         446         345         765         875            323         443
#3:         446         345         765         875            323         761
#   1_B2_value2 1_B3_value2 1_B4_value2 1_Total_value2 2_B1_value2 2_B2_value2
#1:         761         649         975            323         345         765
#2:         325         761         443            325         540         345
#3:         443         325         761            443         765         540
#   2_B3_value2 2_B4_value2 2_Total_value2
#1:         169         875            431
#2:         765         540            345
#3:         345         765            540

We can also get the wide format from the original dataset

dcast(setDT(dat), FID+IID~Status, value.var=names(dat)[4:7])
#   FID IID B1_Value1.G1 B2_Value1.G1 B3_Value1.G1 B4_Value1.G1 Total_Value1.G1
#1: K541 789          875          323         8039        830          849
#2: N123 123          888          345          765          875          875
#3: N123 456          323          888          345          765          875
#   B1_Value2.G1 B2_Value2.G1 B3_Value2.G1 B4_Value2.G1 Total_Value2.G1
#1:          325          761          649          975             323
#2:          443          325          761          443             325
#3:          761          443          325          761             443
#    B1_Value1.G2 B2_Value1.G2 B3_Value1.G2 B4_Value1.G2 Total_Value1.G2
#1:          446          345          765          875             323
#2:          446          345          765          875             323
#3:          446          345          765          875             323
#   B1_Value2.G2 B2_Value2.G2 B3_Value2.G2 B4_Value2.G2 Total_Value2.G2
#1:          345          765          169          875             431
#2:          540          345          765          540             345
#3:          765          540          345          765             540

Update

Just double checking with the OP's expected output ('form1')

   merge(dM,form1,by=c("FID","IID","Gel","Status"),suffixes=c("akrun","real"))
#      FID IID Status Gel value1 value2 Value1 Value2
#  1: K541 789     B1   1    875    325    875    325
#  2: K541 789     B2   1    323    761    323    761
#  3: K541 789     B3   1   8039    649   8039    649
#  4: K541 789     B4   1    830    975    830    975   
#  5: K541 789  Total   1    849    323    849    323
#  6: K541 789     B1   2    446    345    446    345
#  7: K541 789     B2   2    345    765    345    765
#  8: K541 789     B3   2    765    169    765    169
#  9: K541 789     B4   2    875    875    875    875
# 10: K541 789  Total   2    323    431    323    431
# 11: N123 123     B1   1    888    443    888    443
# 12: N123 123     B2   1    345    325    345    325
# 13: N123 123     B3   1    765    761    765    761
# 14: N123 123     B4   1    875    443    875    443
# 15: N123 123  Total   1    875    325    875    325
# 16: N123 123     B1   2    446    540    446    540
# 17: N123 123     B2   2    345    345    345    345
# 18: N123 123     B3   2    765    765    765    765
# 19: N123 123     B4   2    875    540    875    540
# 20: N123 123  Total   2    323    345    323    345
# 21: N123 456     B1   1    323    761    323    761
# 22: N123 456     B2   1    888    443    888    443
# 23: N123 456     B3   1    345    325    345    325
# 24: N123 456     B4   1    765    761    765    761
# 25: N123 456  Total   1    875    443    875    443
# 26: N123 456     B1   2    446    765    446    765
# 27: N123 456     B2   2    345    540    345    540
# 28: N123 456     B3   2    765    345    765    345
# 29: N123 456     B4   2    875    765    875    765
# 30: N123 456  Total   2    323    540    323    540

@akron You are right. I just realized that I didn't install the devel package correctly. My version of your output was flawed. My apologies. — RealViaCauchy, Apr 03 '15 at 20:28
@RealViaCauchy It's okay. I just compared the `merge` code that you posted earlier. It is the same results — akrun, Apr 03 '15 at 20:30
akron, I appreciate the brevity of this answer, but I think I prefer rawr 's approach since there is no package install required to implement it (less chance for me to screw it up). — RealViaCauchy, Apr 03 '15 at 20:35
@RealViaCauchy It's okay. This is another method to do the same thing. One advantage with data.table is it is highly efficient. — akrun, Apr 03 '15 at 20:40
@RealViaCauchy I have screwed up `reshape` many, many, *many* times. It's not always the easiest to figure out and the docs can be not-so-helpful. I finally just wrote my own wrapper functions for `long` and `wide` variations (similar to `melt` and `dcast` from `reshape2`/`data.table`) just so I can remember which arguments I need to give to get the output I want. `reshape2`/`data.table` alleviate some of that headache by having separate functions and reducing the number of arguments for each — rawr, Apr 04 '15 at 01:21

Rearrange Data in R, Breaking Apart Column Names

First Form

Second Form

2 Answers2

Update