Dataset transformation from gather() to pivot_longer()

Question

I have used gather/separate/spread in the past, but I guess they have been superseded and may be interfering with some of my plots, so I have been recommended to change to pivot_longer(). However, I cannot seem to figure out how to get the same data frame.

Here is my original dataframe:

df <- structure(list(Station = c("Stn01", "Stn11", "Stn20", "Stn01", 
"Stn11", "Stn20", "Stn01", "Stn11", "Stn20", "Stn01", "Stn11", 
"Stn20", "Stn01", "Stn11", "Stn20", "Stn01", "Stn11", "Stn20", 
"Stn01", "Stn11", "Stn20", "Stn01", "Stn11", "Stn20", "Stn01", 
"Stn11", "Stn20"), HC = c("Alkanes", "Alkanes", "Alkanes", "Alkylated PAHs", 
"Alkylated PAHs", "Alkylated PAHs", "PAHs", "PAHs", "PAHs", "Alkanes", 
"Alkanes", "Alkanes", "Alkylated PAHs", "Alkylated PAHs", "Alkylated PAHs", 
"PAHs", "PAHs", "PAHs", "Alkanes", "Alkanes", "Alkanes", "Alkylated PAHs", 
"Alkylated PAHs", "Alkylated PAHs", "PAHs", "PAHs", "PAHs"), 
    Treatment = c("Control", "Control", "Control", "Control", 
    "Control", "Control", "Control", "Control", "Control", "Diesel", 
    "Diesel", "Diesel", "Diesel", "Diesel", "Diesel", "Diesel", 
    "Diesel", "Diesel", "Heidrun", "Heidrun", "Heidrun", "Heidrun", 
    "Heidrun", "Heidrun", "Heidrun", "Heidrun", "Heidrun"), Avg_0 = c(6.62, 
    6.82, 0, 0, 0, 0, 0, 0, 0, 11.73, 10.92, 10.98, 7.96, 6.71, 
    6.9, 3.97, 2.95, 0, 9.58, 10.73, 10.79, 9.37, 10.78, 10.89, 
    7, 8.49, 8.49), Avg_12 = c(6.47, 7.33, 0, 0, 2.61, 0, 0, 
    1.86, 0, 11.8, 11.94, 9.8, 8.66, 9.32, 8.39, 6.26, 7.22, 
    6.44, 8.93, 8.49, 0, 10.08, 10.42, 9.7, 8.58, 9.1, 8.47), 
    Avg_24 = c(6.39, 8.1, 0, 0, 0, 0, 0, 3.51, 0, 10.55, 10.92, 
    9.35, 7.78, 8.27, 7.76, 5.64, 6.69, 5.64, 8.28, 8.84, 1.68, 
    9.26, 9.99, 9.2, 7.93, 8.66, 8.04), Avg_36 = c(6.38, 8.06, 
    3.82, 0, 2.25, 0, 0, 4.86, 0, 10.04, 9.13, 9.4, 7.38, 7.15, 
    7.48, 4.77, 6.24, 4.83, 8.48, 8.65, 3.56, 8.91, 9.52, 8.34, 
    7.32, 8.31, 7.58), Avg_48 = c(6.49, 7.9, 0, 0, 0, 0, 0, 4.89, 
    0, 10.24, 9.47, 8.28, 7.23, 7.27, 6.74, 4.55, 6.21, 3, 8.92, 
    8.97, 3.44, 8.79, 9.28, 7.85, 7.07, 8.04, 7.17), Avg_60 = c(6.22, 
    7.96, 6.56, 0, 0, 0, 0, 5.45, 0, 10.15, 9.46, 8.05, 7, 5.86, 
    1.95, 1.48, 5.93, 0, 8.78, 8.56, 7.31, 8.63, 8.49, 7.1, 7.23, 
    7.77, 6.62), Avg_72 = c(6.84, 7.15, 7.49, 0, 0, 0, 0, 4.72, 
    0, 10.24, 8.46, 9.89, 6.78, 4.01, 4.03, 0, 3.56, 0, 8.42, 
    7.55, 8.21, 5.38, 8.02, 7.46, 4.57, 7.42, 6.6), Std_0 = c(0.12, 
    0.98, 0, 0, 0, 0, 0, 0, 0, 1.35, 0.42, 0.29, 1.04, 0.86, 
    0.45, 3.44, 2.56, 0, 0.88, 0.23, 0.09, 1.72, 0.23, 0.1, 1.79, 
    0.23, 0.09), Std_12 = c(0.1, 0.39, 0, 0, 4.52, 0, 0, 3.23, 
    0, 1.18, 0.98, 0.26, 0.18, 0.49, 0.13, 0.37, 0.16, 0.06, 
    0.03, 0.4, 0, 0.29, 0.15, 0.11, 0.08, 0.12, 0.08), Std_24 = c(0.25, 
    0.58, 0, 0, 0, 0, 0, 3.08, 0, 1.16, 0.41, 0.43, 0.19, 0.39, 
    0.09, 0.24, 0.15, 0.07, 0.27, 0.06, 2.91, 0.12, 0.24, 0.05, 
    0.15, 0.3, 0.07), Std_36 = c(0.47, 0.63, 3.35, 0, 3.9, 0, 
    0, 0.32, 0, 1.33, 0.28, 1.63, 0.16, 0.31, 0.47, 0.1, 0.13, 
    0.12, 0.64, 0.1, 3.08, 0.35, 0.16, 0.09, 0.18, 0.14, 0.02
    ), Std_48 = c(0.6, 0.31, 0, 0, 0, 0, 0, 0.36, 0, 0.14, 0.88, 
    0.29, 0.5, 0.49, 0.35, 0.15, 0.18, 2.59, 1, 0.08, 2.98, 0.2, 
    0.15, 0.18, 0.24, 0.14, 0.13), Std_60 = c(0.82, 0.61, 0.22, 
    0, 0, 0, 0, 0.66, 0, 0.57, 1.03, 0.31, 0.16, 1.3, 3.38, 2.56, 
    0.27, 0, 0.43, 0.07, 0.23, 0.48, 0.27, 0.39, 0.25, 0.15, 
    0.2), Std_72 = c(0.02, 0.4, 0.2, 0, 0, 0, 0, 0.32, 0, 0.56, 
    0.89, 0.49, 0.61, 3.48, 3.51, 0, 3.09, 0, 0.82, 1.5, 0.06, 
    4.69, 0.14, 0.3, 3.96, 0.19, 0.06)), class = "data.frame", row.names = c(NA, 
27L))

And here is the code I previously used:

    df2 = df %>% 
      gather(time, value, -Station, -Treatment, -HC) %>%
      separate(time, c("Stat", "Var")) %>% 
      spread(Stat, value)

And here is df2

    > data.frame(df2)
    Station             HC Treatment Var       Avg        Std
1     Stn01        Alkanes   Control   0  6.621491 0.11631567
2     Stn01        Alkanes   Control  12  6.473529 0.09578875
3     Stn01        Alkanes   Control  24  6.393808 0.24823024
4     Stn01        Alkanes   Control  36  6.375814 0.46547541
5     Stn01        Alkanes   Control  48  6.494237 0.60114917
6     Stn01        Alkanes   Control  60  6.222063 0.82456790
7     Stn01        Alkanes   Control  72  6.844415 0.02202778
8     Stn01        Alkanes    Diesel   0 11.729584 1.35398125
9     Stn01        Alkanes    Diesel  12 11.802093 1.17725691
10    Stn01        Alkanes    Diesel  24 10.548623 1.16186598
11    Stn01        Alkanes    Diesel  36 10.035586 1.32654703
12    Stn01        Alkanes    Diesel  48 10.244188 0.13859883
13    Stn01        Alkanes    Diesel  60 10.146536 0.56706695
14    Stn01        Alkanes    Diesel  72 10.244465 0.55938128
15    Stn01        Alkanes   Heidrun   0  9.578874 0.88330482
16    Stn01        Alkanes   Heidrun  12  8.929797 0.03025275
17    Stn01        Alkanes   Heidrun  24  8.284761 0.26987942
18    Stn01        Alkanes   Heidrun  36  8.482976 0.63811537
19    Stn01        Alkanes   Heidrun  48  8.918134 0.99887085
20    Stn01        Alkanes   Heidrun  60  8.775376 0.42741441
21    Stn01        Alkanes   Heidrun  72  8.417767 0.82238414
22    Stn01 Alkylated PAHs   Control   0  0.000000 0.00000000
23    Stn01 Alkylated PAHs   Control  12  0.000000 0.00000000
24    Stn01 Alkylated PAHs   Control  24  0.000000 0.00000000
25    Stn01 Alkylated PAHs   Control  36  0.000000 0.00000000
26    Stn01 Alkylated PAHs   Control  48  0.000000 0.00000000
27    Stn01 Alkylated PAHs   Control  60  0.000000 0.00000000
28    Stn01 Alkylated PAHs   Control  72  0.000000 0.00000000
29    Stn01 Alkylated PAHs    Diesel   0  7.960627 1.03913361
30    Stn01 Alkylated PAHs    Diesel  12  8.662339 0.18208836
31    Stn01 Alkylated PAHs    Diesel  24  7.784496 0.18994927
32    Stn01 Alkylated PAHs    Diesel  36  7.376753 0.15882542
33    Stn01 Alkylated PAHs    Diesel  48  7.230484 0.50120409
34    Stn01 Alkylated PAHs    Diesel  60  7.002890 0.15646328
35    Stn01 Alkylated PAHs    Diesel  72  6.779103 0.61029703
36    Stn01 Alkylated PAHs   Heidrun   0  9.367344 1.72094653
37    Stn01 Alkylated PAHs   Heidrun  12 10.082869 0.29415459
38    Stn01 Alkylated PAHs   Heidrun  24  9.259678 0.12171481
39    Stn01 Alkylated PAHs   Heidrun  36  8.907677 0.34686340
40    Stn01 Alkylated PAHs   Heidrun  48  8.794576 0.20443661
41    Stn01 Alkylated PAHs   Heidrun  60  8.633095 0.48155427
42    Stn01 Alkylated PAHs   Heidrun  72  5.376805 4.68690530
43    Stn01           PAHs   Control   0  0.000000 0.00000000
44    Stn01           PAHs   Control  12  0.000000 0.00000000
45    Stn01           PAHs   Control  24  0.000000 0.00000000
46    Stn01           PAHs   Control  36  0.000000 0.00000000
47    Stn01           PAHs   Control  48  0.000000 0.00000000
48    Stn01           PAHs   Control  60  0.000000 0.00000000
49    Stn01           PAHs   Control  72  0.000000 0.00000000
50    Stn01           PAHs    Diesel   0  3.968547 3.44357627
51    Stn01           PAHs    Diesel  12  6.263849 0.36819129
52    Stn01           PAHs    Diesel  24  5.636045 0.24033814
53    Stn01           PAHs    Diesel  36  4.768062 0.10433426
54    Stn01           PAHs    Diesel  48  4.551265 0.15052789
55    Stn01           PAHs    Diesel  60  1.479981 2.56340154
56    Stn01           PAHs    Diesel  72  0.000000 0.00000000
57    Stn01           PAHs   Heidrun   0  6.996183 1.79284347
58    Stn01           PAHs   Heidrun  12  8.582956 0.07869598
59    Stn01           PAHs   Heidrun  24  7.931676 0.15300706
60    Stn01           PAHs   Heidrun  36  7.319881 0.17798018
61    Stn01           PAHs   Heidrun  48  7.074597 0.24238709
62    Stn01           PAHs   Heidrun  60  7.225184 0.25387164
63    Stn01           PAHs   Heidrun  72  4.565478 3.96113845
64    Stn11        Alkanes   Control   0  6.819945 0.97902331
65    Stn11        Alkanes   Control  12  7.333212 0.38905367
66    Stn11        Alkanes   Control  24  8.104873 0.57856444
67    Stn11        Alkanes   Control  36  8.062125 0.62644411
68    Stn11        Alkanes   Control  48  7.904382 0.31320345
69    Stn11        Alkanes   Control  60  7.956617 0.61191206
70    Stn11        Alkanes   Control  72  7.148201 0.40366363
71    Stn11        Alkanes    Diesel   0 10.920603 0.42274728
72    Stn11        Alkanes    Diesel  12 11.942563 0.98408961
73    Stn11        Alkanes    Diesel  24 10.923680 0.40809327
74    Stn11        Alkanes    Diesel  36  9.126043 0.27721105
75    Stn11        Alkanes    Diesel  48  9.469791 0.88096328
76    Stn11        Alkanes    Diesel  60  9.463811 1.03361345
77    Stn11        Alkanes    Diesel  72  8.456687 0.89304467
78    Stn11        Alkanes   Heidrun   0 10.733623 0.23269887
79    Stn11        Alkanes   Heidrun  12  8.490746 0.40459069
80    Stn11        Alkanes   Heidrun  24  8.838450 0.05919360
81    Stn11        Alkanes   Heidrun  36  8.646687 0.10360369
82    Stn11        Alkanes   Heidrun  48  8.972611 0.08290277
83    Stn11        Alkanes   Heidrun  60  8.562786 0.07100640
84    Stn11        Alkanes   Heidrun  72  7.550729 1.49682901
85    Stn11 Alkylated PAHs   Control   0  0.000000 0.00000000
86    Stn11 Alkylated PAHs   Control  12  2.609902 4.52048285
87    Stn11 Alkylated PAHs   Control  24  0.000000 0.00000000
88    Stn11 Alkylated PAHs   Control  36  2.252772 3.90191507
89    Stn11 Alkylated PAHs   Control  48  0.000000 0.00000000
90    Stn11 Alkylated PAHs   Control  60  0.000000 0.00000000
91    Stn11 Alkylated PAHs   Control  72  0.000000 0.00000000
92    Stn11 Alkylated PAHs    Diesel   0  6.705842 0.85510317
93    Stn11 Alkylated PAHs    Diesel  12  9.319237 0.49239843
94    Stn11 Alkylated PAHs    Diesel  24  8.267307 0.38986263
95    Stn11 Alkylated PAHs    Diesel  36  7.147588 0.30608563
96    Stn11 Alkylated PAHs    Diesel  48  7.268045 0.49381914
97    Stn11 Alkylated PAHs    Diesel  60  5.857923 1.29590884
98    Stn11 Alkylated PAHs    Diesel  72  4.011634 3.47682181
99    Stn11 Alkylated PAHs   Heidrun   0 10.784315 0.22800645
100   Stn11 Alkylated PAHs   Heidrun  12 10.417527 0.15014292
101   Stn11 Alkylated PAHs   Heidrun  24  9.993212 0.23598037
102   Stn11 Alkylated PAHs   Heidrun  36  9.518269 0.15532675
103   Stn11 Alkylated PAHs   Heidrun  48  9.277790 0.14585692
104   Stn11 Alkylated PAHs   Heidrun  60  8.492607 0.26669409
105   Stn11 Alkylated PAHs   Heidrun  72  8.015163 0.14303819
106   Stn11           PAHs   Control   0  0.000000 0.00000000
107   Stn11           PAHs   Control  12  1.863725 3.22806554
108   Stn11           PAHs   Control  24  3.505419 3.07915940
109   Stn11           PAHs   Control  36  4.863693 0.32386366
110   Stn11           PAHs   Control  48  4.886971 0.35810888
111   Stn11           PAHs   Control  60  5.445887 0.66034850
112   Stn11           PAHs   Control  72  4.717201 0.31915071
113   Stn11           PAHs    Diesel   0  2.952711 2.55827327
114   Stn11           PAHs    Diesel  12  7.215409 0.15640465
115   Stn11           PAHs    Diesel  24  6.686688 0.14949982
116   Stn11           PAHs    Diesel  36  6.237002 0.13335497
117   Stn11           PAHs    Diesel  48  6.208333 0.18293420
118   Stn11           PAHs    Diesel  60  5.928555 0.26737710
119   Stn11           PAHs    Diesel  72  3.557723 3.08687868
120   Stn11           PAHs   Heidrun   0  8.488757 0.23205968
121   Stn11           PAHs   Heidrun  12  9.102304 0.12085427
122   Stn11           PAHs   Heidrun  24  8.664061 0.29644743
123   Stn11           PAHs   Heidrun  36  8.309851 0.14125593
124   Stn11           PAHs   Heidrun  48  8.038313 0.14097116
125   Stn11           PAHs   Heidrun  60  7.773979 0.14573561
126   Stn11           PAHs   Heidrun  72  7.416157 0.19134229
127   Stn20        Alkanes   Control   0  0.000000 0.00000000
128   Stn20        Alkanes   Control  12  0.000000 0.00000000
129   Stn20        Alkanes   Control  24  0.000000 0.00000000
130   Stn20        Alkanes   Control  36  3.821349 3.34649990
131   Stn20        Alkanes   Control  48  0.000000 0.00000000
132   Stn20        Alkanes   Control  60  6.564225 0.21539148
133   Stn20        Alkanes   Control  72  7.486341 0.20436615
134   Stn20        Alkanes    Diesel   0 10.984364 0.29144447
135   Stn20        Alkanes    Diesel  12  9.799222 0.26435254
136   Stn20        Alkanes    Diesel  24  9.351346 0.42947220
137   Stn20        Alkanes    Diesel  36  9.397014 1.62881745
138   Stn20        Alkanes    Diesel  48  8.276159 0.28532815
139   Stn20        Alkanes    Diesel  60  8.049423 0.30844008
140   Stn20        Alkanes    Diesel  72  9.892089 0.49382239
141   Stn20        Alkanes   Heidrun   0 10.791625 0.09164511
142   Stn20        Alkanes   Heidrun  12  0.000000 0.00000000
143   Stn20        Alkanes   Heidrun  24  1.677661 2.90579449
144   Stn20        Alkanes   Heidrun  36  3.557033 3.08412839
145   Stn20        Alkanes   Heidrun  48  3.438805 2.98001652
146   Stn20        Alkanes   Heidrun  60  7.312609 0.22919464
147   Stn20        Alkanes   Heidrun  72  8.214011 0.05606546
148   Stn20 Alkylated PAHs   Control   0  0.000000 0.00000000
149   Stn20 Alkylated PAHs   Control  12  0.000000 0.00000000
150   Stn20 Alkylated PAHs   Control  24  0.000000 0.00000000
151   Stn20 Alkylated PAHs   Control  36  0.000000 0.00000000
152   Stn20 Alkylated PAHs   Control  48  0.000000 0.00000000
153   Stn20 Alkylated PAHs   Control  60  0.000000 0.00000000
154   Stn20 Alkylated PAHs   Control  72  0.000000 0.00000000
155   Stn20 Alkylated PAHs    Diesel   0  6.895491 0.45383586
156   Stn20 Alkylated PAHs    Diesel  12  8.388744 0.12780268
157   Stn20 Alkylated PAHs    Diesel  24  7.764613 0.08532223
158   Stn20 Alkylated PAHs    Diesel  36  7.476990 0.47485073
159   Stn20 Alkylated PAHs    Diesel  48  6.737704 0.35296976
160   Stn20 Alkylated PAHs    Diesel  60  1.951222 3.37961617
161   Stn20 Alkylated PAHs    Diesel  72  4.034777 3.51395767
162   Stn20 Alkylated PAHs   Heidrun   0 10.889993 0.09649378
163   Stn20 Alkylated PAHs   Heidrun  12  9.700617 0.11036149
164   Stn20 Alkylated PAHs   Heidrun  24  9.196964 0.04600598
165   Stn20 Alkylated PAHs   Heidrun  36  8.342139 0.08658614
166   Stn20 Alkylated PAHs   Heidrun  48  7.854261 0.17562572
 [ reached 'max' / getOption("max.print") -- omitted 23 rows ]

The furthest I can get to is

df3 = df %>%
    pivot_longer(!c(Station, HC, Treatment), names_to = c("Avg", "Std"), 
                 names_pattern = "(.+)_(.+)")

which looks like:

    > data.frame(df3)
   Station             HC Treatment   T          X0         X12         X24
1    Stn01        Alkanes   Control Avg  6.62149136  6.47352880  6.39380797
2    Stn01        Alkanes   Control Std  0.11631567  0.09578875  0.24823024
3    Stn11        Alkanes   Control Avg  6.81994541  7.33321187  8.10487346
4    Stn11        Alkanes   Control Std  0.97902331  0.38905367  0.57856444
5    Stn20        Alkanes   Control Avg  0.00000000  0.00000000  0.00000000
6    Stn20        Alkanes   Control Std  0.00000000  0.00000000  0.00000000
7    Stn01 Alkylated PAHs   Control Avg  0.00000000  0.00000000  0.00000000
8    Stn01 Alkylated PAHs   Control Std  0.00000000  0.00000000  0.00000000
9    Stn11 Alkylated PAHs   Control Avg  0.00000000  2.60990199  0.00000000
10   Stn11 Alkylated PAHs   Control Std  0.00000000  4.52048285  0.00000000
11   Stn20 Alkylated PAHs   Control Avg  0.00000000  0.00000000  0.00000000
12   Stn20 Alkylated PAHs   Control Std  0.00000000  0.00000000  0.00000000
13   Stn01           PAHs   Control Avg  0.00000000  0.00000000  0.00000000
14   Stn01           PAHs   Control Std  0.00000000  0.00000000  0.00000000
15   Stn11           PAHs   Control Avg  0.00000000  1.86372451  3.50541914
16   Stn11           PAHs   Control Std  0.00000000  3.22806554  3.07915940
17   Stn20           PAHs   Control Avg  0.00000000  0.00000000  0.00000000
18   Stn20           PAHs   Control Std  0.00000000  0.00000000  0.00000000
19   Stn01        Alkanes    Diesel Avg 11.72958402 11.80209328 10.54862335
20   Stn01        Alkanes    Diesel Std  1.35398125  1.17725691  1.16186598
21   Stn11        Alkanes    Diesel Avg 10.92060319 11.94256329 10.92367974
22   Stn11        Alkanes    Diesel Std  0.42274728  0.98408961  0.40809327
23   Stn20        Alkanes    Diesel Avg 10.98436446  9.79922160  9.35134631
24   Stn20        Alkanes    Diesel Std  0.29144447  0.26435254  0.42947220
25   Stn01 Alkylated PAHs    Diesel Avg  7.96062743  8.66233907  7.78449583
26   Stn01 Alkylated PAHs    Diesel Std  1.03913361  0.18208836  0.18994927
27   Stn11 Alkylated PAHs    Diesel Avg  6.70584215  9.31923747  8.26730687
28   Stn11 Alkylated PAHs    Diesel Std  0.85510317  0.49239843  0.38986263
29   Stn20 Alkylated PAHs    Diesel Avg  6.89549063  8.38874445  7.76461313
30   Stn20 Alkylated PAHs    Diesel Std  0.45383586  0.12780268  0.08532223
31   Stn01           PAHs    Diesel Avg  3.96854703  6.26384860  5.63604479
32   Stn01           PAHs    Diesel Std  3.44357627  0.36819129  0.24033814
33   Stn11           PAHs    Diesel Avg  2.95271069  7.21540928  6.68668797
34   Stn11           PAHs    Diesel Std  2.55827327  0.15640465  0.14949982
35   Stn20           PAHs    Diesel Avg  0.00000000  6.44436715  5.63778692
36   Stn20           PAHs    Diesel Std  0.00000000  0.06357132  0.06769244
37   Stn01        Alkanes   Heidrun Avg  9.57887411  8.92979662  8.28476115
38   Stn01        Alkanes   Heidrun Std  0.88330482  0.03025275  0.26987942
39   Stn11        Alkanes   Heidrun Avg 10.73362302  8.49074639  8.83844980
40   Stn11        Alkanes   Heidrun Std  0.23269887  0.40459069  0.05919360
41   Stn20        Alkanes   Heidrun Avg 10.79162539  0.00000000  1.67766123
42   Stn20        Alkanes   Heidrun Std  0.09164511  0.00000000  2.90579449
43   Stn01 Alkylated PAHs   Heidrun Avg  9.36734404 10.08286909  9.25967823
44   Stn01 Alkylated PAHs   Heidrun Std  1.72094653  0.29415459  0.12171481
45   Stn11 Alkylated PAHs   Heidrun Avg 10.78431489 10.41752705  9.99321187
46   Stn11 Alkylated PAHs   Heidrun Std  0.22800645  0.15014292  0.23598037
47   Stn20 Alkylated PAHs   Heidrun Avg 10.88999289  9.70061710  9.19696402
48   Stn20 Alkylated PAHs   Heidrun Std  0.09649378  0.11036149  0.04600598
49   Stn01           PAHs   Heidrun Avg  6.99618256  8.58295623  7.93167597
50   Stn01           PAHs   Heidrun Std  1.79284347  0.07869598  0.15300706
51   Stn11           PAHs   Heidrun Avg  8.48875658  9.10230450  8.66406137
52   Stn11           PAHs   Heidrun Std  0.23205968  0.12085427  0.29644743
53   Stn20           PAHs   Heidrun Avg  8.48773598  8.47361792  8.04441609
54   Stn20           PAHs   Heidrun Std  0.08710327  0.07944757  0.06751443
           X36         X48        X60         X72
1   6.37581409  6.49423746  6.2220630  6.84441527
2   0.46547541  0.60114917  0.8245679  0.02202778
3   8.06212529  7.90438240  7.9566169  7.14820084
4   0.62644411  0.31320345  0.6119121  0.40366363
5   3.82134939  0.00000000  6.5642254  7.48634072
6   3.34649990  0.00000000  0.2153915  0.20436615
7   0.00000000  0.00000000  0.0000000  0.00000000
8   0.00000000  0.00000000  0.0000000  0.00000000
9   2.25277172  0.00000000  0.0000000  0.00000000
10  3.90191507  0.00000000  0.0000000  0.00000000
11  0.00000000  0.00000000  0.0000000  0.00000000
12  0.00000000  0.00000000  0.0000000  0.00000000
13  0.00000000  0.00000000  0.0000000  0.00000000
14  0.00000000  0.00000000  0.0000000  0.00000000
15  4.86369268  4.88697124  5.4458867  4.71720123
16  0.32386366  0.35810888  0.6603485  0.31915071
17  0.00000000  0.00000000  0.0000000  0.00000000
18  0.00000000  0.00000000  0.0000000  0.00000000
19 10.03558621 10.24418752 10.1465365 10.24446485
20  1.32654703  0.13859883  0.5670670  0.55938128
21  9.12604286  9.46979080  9.4638110  8.45668687
22  0.27721105  0.88096328  1.0336134  0.89304467
23  9.39701364  8.27615927  8.0494232  9.89208899
24  1.62881745  0.28532815  0.3084401  0.49382239
25  7.37675286  7.23048413  7.0028904  6.77910269
26  0.15882542  0.50120409  0.1564633  0.61029703
27  7.14758793  7.26804504  5.8579229  4.01163446
28  0.30608563  0.49381914  1.2959088  3.47682181
29  7.47699006  6.73770441  1.9512223  4.03477721
30  0.47485073  0.35296976  3.3796162  3.51395767
31  4.76806239  4.55126464  1.4799806  0.00000000
32  0.10433426  0.15052789  2.5634015  0.00000000
33  6.23700192  6.20833318  5.9285554  3.55772271
34  0.13335497  0.18293420  0.2673771  3.08687868
35  4.83348946  2.99566227  0.0000000  0.00000000

You may get better, faster help if you provide your data in a format this is reproducible (and minimal). For tips on how to do that, see [here](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — jpsmith, Jul 11 '23 at 21:50
Please use `dput` to provide usable samples. It's a bit more difficult using the rendered output from R's console since it has embedded spaces, so `read.table` doesn't work. The links I provided in my previous comments to you (other question) have good examples. — r2evans, Jul 11 '23 at 21:50

r2evans · Answer 1 · 2023-07-11T22:15:41.917

Using this rendition of df:

df <- structure(list(Station = c("Stn01", "Stn11", "Stn20", "Stn01", "Stn11", "Stn20"), HC = c("Alkanes", "Alkanes", "Alkanes", "Alkylated PAHs", "Alkylated PAHs", "Alkylated PAHs"), Treatment = c("Control", "Control", "Control", "Control", "Control", "Control"), Avg_0 = c(6.621491, 6.819945, 0, 0, 0, 0), Avg_12 = c(6.473529, 7.333212, 0, 0, 2.609902, 0), Avg_24 = c(6.393808, 8.104873, 0, 0, 0, 0), Avg_36 = c(6.375814, 8.062125, 3.821349, 0, 2.252772, 0), Avg_48 = c(6.494237, 7.904382, 0, 0, 0, 0), Avg_60 = c(6.222063,  7.956617, 6.564225, 0, 0, 0), Avg_72 = c(6.844415, 7.148201, 7.486341, 0, 0, 0), Std_0 = c(0.11631567, 0.97902331, 0, 0, 0, 0), Std_12 = c(0.09578875, 0.38905367, 0, 0, 4.52048285, 0), Std_24 = c(0.24823024, 0.57856444, 0, 0, 0, 0), Std_36 = c(0.46547541, 0.62644411, 3.3464999, 0, 3.90191507, 0)), class = "data.frame", row.names = c("1", "2", "3", "4", "5", "6"))

We can use the special ".value" for names_to= to do your multiple steps in one:

library(tidyr)
pivot_longer(df, -c(Station, HC, Treatment),
             names_pattern = "(.*)_(.*)",
             names_to = c(".value", "Var"))
# # A tibble: 42 × 6
#    Station HC      Treatment Var     Avg     Std
#    <chr>   <chr>   <chr>     <chr> <dbl>   <dbl>
#  1 Stn01   Alkanes Control   0      6.62  0.116 
#  2 Stn01   Alkanes Control   12     6.47  0.0958
#  3 Stn01   Alkanes Control   24     6.39  0.248 
#  4 Stn01   Alkanes Control   36     6.38  0.465 
#  5 Stn01   Alkanes Control   48     6.49 NA     
#  6 Stn01   Alkanes Control   60     6.22 NA     
#  7 Stn01   Alkanes Control   72     6.84 NA     
#  8 Stn11   Alkanes Control   0      6.82  0.979 
#  9 Stn11   Alkanes Control   12     7.33  0.389 
# 10 Stn11   Alkanes Control   24     8.10  0.579 
# # ℹ 32 more rows
# # ℹ Use `print(n = ...)` to see more rows

In the pattern "(.*)_(.*)", the first is what we want to keep as individual columns without reshaping them into rows, so the Avg and Std labels remain as column names. For this to happen, we use the special ".value" within names_to=. The second portion (all after _) will go to the "Var" column as expected.

score 0 · Answer 2 · edited Jul 12 '23 at 04:19

Here is a certainly less elegant but more direct translation of your original code from gather/spread to pivot_longer/pivot_wider - you should go with r2evans's more elegant approach, but this may lend some insight the transition to the new terms:

library(dplyr)
library(tidyr)

df %>%
  pivot_longer(cols = -c(Station, Treatment, HC), names_to = "time", values_to = "value") %>%
  separate(col = "time", into = c("Stat", "Var")) %>%
  pivot_wider(names_from = Stat, values_from = value)

Output:

  Station HC      Treatment Var     Avg   Std
   <chr>   <chr>   <chr>     <chr> <dbl> <dbl>
 1 Stn01   Alkanes Control   0      6.62  0.12
 2 Stn01   Alkanes Control   12     6.47  0.1 
 3 Stn01   Alkanes Control   24     6.39  0.25
 4 Stn01   Alkanes Control   36     6.38  0.47
 5 Stn01   Alkanes Control   48     6.49  0.6 
 6 Stn01   Alkanes Control   60     6.22  0.82
 7 Stn01   Alkanes Control   72     6.84  0.02
 8 Stn11   Alkanes Control   0      6.82  0.98
 9 Stn11   Alkanes Control   12     7.33  0.39
10 Stn11   Alkanes Control   24     8.1   0.58

`separate` has also been superseded in favour of `separate_wider_position()` and `separate_wider_delim()`. — UseR10085, Jul 12 '23 at 04:20

Dataset transformation from gather() to pivot_longer()

2 Answers2