Convert some rows to columns in r

Question

I have a dataset like the one below:

test <- structure(list(SR = c(1L, 1L, 15L, 20L, 20L, 96L, 110L, 110L, 
121L, 121L, 130L, 130L, 143L, 143L), Area = structure(c(3L, 3L, 
1L, 1L, 1L, 1L, 2L, 2L, 1L, 1L, 4L, 4L, 2L, 2L), .Label = c("FH", 
"MO", "TSC", "WMB"), class = "factor"), Period = structure(c(1L, 
2L, 1L, 1L, 2L, 1L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L), .Label = c("First", 
"Second"), class = "factor"), count = c(4L, 6L, 3L, 6L, 6L, 3L, 
6L, 6L, 6L, 6L, 6L, 6L, 5L, 6L), countTotal = c(10L, 10L, 3L, 
12L, 12L, 3L, 12L, 12L, 12L, 12L, 12L, 12L, 11L, 11L), SumTotal = c(1520, 
5769.02, 29346.78, 13316.89, 11932.68, 10173.05, 13243.5, 17131.94, 
111189.07, 84123.52, 79463.1, 120010.57, 7035.88, 11520)), .Names = c("SR", 
"Area", "Period", "count", "countTotal", "SumTotal"), class = "data.frame", row.names = c(NA, 
-14L))

 SR Area Period count countTotal  SumTotal
  1  TSC  First     4         10   1520.00
  1  TSC Second     6         10   5769.02
 15   FH  First     3          3  29346.78
 20   FH  First     6         12  13316.89
 20   FH Second     6         12  11932.68
 96   FH  First     3          3  10173.05
110   MO  First     6         12  13243.50
110   MO Second     6         12  17131.94
121   FH  First     6         12 111189.07
121   FH Second     6         12  84123.52
130  WMB  First     6         12  79463.10
130  WMB Second     6         12 120010.57
143   MO  First     5         11   7035.88
143   MO Second     6         11  11520.00

I want to convert some of the rows to columns to make the dataset look like this:

 SR Area countTotal First.Count Second.Count First.SumTotal Second.SumTotal
  1  TSC         10           4            6        1520.00         5769.02
 15   FH          3           3           NA       29346.78              NA
 20   FH         12           6            6       13316.89        11932.68
 96   FH          3           3           NA       10173.05              NA
110   MO         12           6            6       13243.50        17131.94
121   FH         12           6            6      111189.07        84123.52
130  WMB         12           6            6       79463.10       120010.57
143   MO         11           5            6        7035.88        11520.00

I was trying to use spread from tidyr with this code test %>% spread(Period, SumTotal) but I still get two lines for each SR and Area. Can someone help?

@Imo, This question is a bit more complicated than the linked duplicate as it involves an extra `gather` step. — acylam, Nov 13 '17 at 16:53
@Imo Hack-R's link should be the correct duplicate. None of the mentioned duplicates exactly fit this question. — acylam, Nov 13 '17 at 17:21

score 2 · Accepted Answer · answered Nov 13 '17 at 16:47

You need to first gather by the columns you want to spread, and combine the Period column with the variable column, then spread the resulting variable column:

library(dplyr)
library(tidyr)

test %>%
  gather(variable, value, count:SumTotal) %>%
  unite("variable", Period, variable, sep = ".") %>%
  spread(variable, value)

Result:

   SR Area First.count First.countTotal First.SumTotal Second.count Second.countTotal
1   1  TSC           4               10        1520.00            6                10
2  15   FH           3                3       29346.78           NA                NA
3  20   FH           6               12       13316.89            6                12
4  96   FH           3                3       10173.05           NA                NA
5 110   MO           6               12       13243.50            6                12
6 121   FH           6               12      111189.07            6                12
7 130  WMB           6               12       79463.10            6                12
8 143   MO           5               11        7035.88            6                11
  Second.SumTotal
1         5769.02
2              NA
3        11932.68
4              NA
5        17131.94
6        84123.52
7       120010.57
8        11520.00

Convert some rows to columns in r

1 Answers1