rreshapenumeric

R issue with reshape, reshape is not ordering columns correctly


I am trying to use reshape function in R to arrange my data timepoints in a table. The variable that I am using to split it is numerical, but when reshape arranges the table it incorrectly puts 2,0.5,4,6,8,0,24...168, instead of 0.0 , 0.5 , 2.0 , 4.0 , 6.0 , 8.0 , 24.0 , 48.0... 168.0

Any help is greatly appreciated. This is my data:


>G2
   Animal Sex  Time  Sugar
1    2001   M   2.0   5880
2    2001   M   0.5     95
3    2001   M   4.0  25100
4    2001   M   6.0  68500
5    2001   M   8.0 110000
6    2001   M   0.0     NA
7    2001   M  24.0 336000
8    2001   M  48.0 476000
9    2001   M  72.0 413000
10   2001   M  96.0 368000
11   2001   M 120.0 332000
12   2001   M 144.0 282000
13   2001   M 168.0 271000
14   2002   M   2.0  13200
15   2002   M   0.5    356
16   2002   M   4.0  55700
17   2002   M   6.0 121000
18   2002   M   8.0 216000
19   2002   M   0.0     NA
20   2002   M  24.0 529000
21   2002   M  48.0 554000
22   2002   M  72.0 468000
23   2002   M  96.0 408000
24   2002   M 120.0 354000
25   2002   M 144.0 348000
26   2002   M 168.0 290000
27   2003   M   2.0  20500
28   2003   M   0.5   1190
29   2003   M   4.0  43800
30   2003   M   6.0  90500
31   2003   M   8.0 142000
32   2003   M   0.0     NA
33   2003   M  24.0 401000
34   2003   M  48.0 485000
35   2003   M  72.0 393000
36   2003   M  96.0 386000
37   2003   M 120.0 335000
38   2003   M 144.0 289000
39   2003   M 168.0 247000
40   2501   F   2.0   6630
41   2501   F   0.5    305
42   2501   F   4.0  28900
43   2501   F   6.0  67300
44   2501   F   8.0 138000
45   2501   F   0.0     NA
46   2501   F  24.0 395000
47   2501   F  48.0 474000
48   2501   F  72.0 390000
49   2501   F  96.0 330000
50   2501   F 120.0 318000
51   2501   F 144.0 258000
52   2501   F 168.0 238000
53   2502   F   2.0   2140
54   2502   F   0.5     NA
55   2502   F   4.0  15700
56   2502   F   6.0  69200
57   2502   F   8.0 125000
58   2502   F   0.0     NA
59   2502   F  24.0 478000
60   2502   F  48.0 500000
61   2502   F  72.0 471000
62   2502   F  96.0 407000
63   2502   F 120.0 414000
64   2502   F 144.0 352000
65   2502   F 168.0 462000
66   2503   F   2.0  32400
67   2503   F   0.5   6290
68   2503   F   4.0  78800
69   2503   F   6.0 119000
70   2503   F   8.0 160000
71   2503   F   0.0     NA
72   2503   F  24.0 328000
73   2503   F  48.0 463000
74   2503   F  72.0 429000
75   2503   F  96.0 381000
76   2503   F 120.0 389000
77   2503   F 144.0 339000
78   2503   F 168.0 327000

I am doing this:

    G2r = reshape(G2, direction = "wide",
             idvar = c("Animal","Sex"), 
             times = Time,
             timevar = "Time"
             )

I am getting this:

> head(G2r)
   Animal Sex Sugar.2 Sugar.0.5 Sugar.4 Sugar.6 Sugar.8 Sugar.24 Sugar.48 Sugar.72 Sugar.96 Sugar.120 Sugar.144 Sugar.168
1    2001   M   5880       95  25100  68500 110000  336000  476000  413000  368000   332000   282000   271000
13   2002   M  13200      356  55700 121000 216000  529000  554000  468000  408000   354000   348000   290000
25   2003   M  20500     1190  43800  90500 142000  401000  485000  393000  386000   335000   289000   247000
37   2501   F   6630      305  28900  67300 138000  395000  474000  390000  330000   318000   258000   238000
49   2502   F   2140       NA  15700  69200 125000  478000  500000  471000  407000   414000   352000   462000
60   2503   F  32400     6290  78800 119000 160000  328000  463000  429000  381000   389000   339000   327000

I tried to sort the time and put it as times = sort(unique(G2$Time)), but it doesn't work. I also tried na.omit(Time) and na.omit(Sugar) in case the NAs were producing the issue. But it did not work either.


Solution

  • If you sort the data going into reshape() it should get the result you need.

    G2[order(G2$Animal, G2$Sex, G2$Time), ] is your data frame in the required order.

    G2r = reshape(G2[order(G2$Animal, G2$Sex, G2$Time), ], direction = "wide",
             idvar = c("Animal","Sex"), 
             times = Time,
             timevar = "Time"
             )
    
    G2r
    #    Animal Sex Sugar.0 Sugar.0.5 Sugar.2 Sugar.4 Sugar.6 Sugar.8 Sugar.24
    # 6    2001   M      NA        95    5880   25100   68500  110000   336000
    # 19   2002   M      NA       356   13200   55700  121000  216000   529000
    # 32   2003   M      NA      1190   20500   43800   90500  142000   401000
    # 45   2501   F      NA       305    6630   28900   67300  138000   395000
    # 58   2502   F      NA        NA    2140   15700   69200  125000   478000
    # 71   2503   F      NA      6290   32400   78800  119000  160000   328000
    #    Sugar.48 Sugar.72 Sugar.96 Sugar.120 Sugar.144 Sugar.168
    # 6    476000   413000   368000    332000    282000    271000
    # 19   554000   468000   408000    354000    348000    290000
    # 32   485000   393000   386000    335000    289000    247000
    # 45   474000   390000   330000    318000    258000    238000
    # 58   500000   471000   407000    414000    352000    462000
    # 71   463000   429000   381000    389000    339000    327000