1

Still learning R and I've had a look at Combine Multiple Columns Into Tidy Data and Gather multiple sets of columns but can't quite answer my question.

I have this dataset where I need to gather wide dataframe into a long dataframe.

year    Bn_elec Bn_water    Hn_elec Hn_water    SF_elec SF_water
2000    148.58  150.8       111.73  159.11      170.74  165.74
2001    189.41  170.34      132.86  195.5       205.14  199.14
2002    234.68  181.89      164.54  223.02      223.33  123.33
2003    272.14  186.43      219.74  305.88      311.17  411.17
2004    253.33  158.29      234.13  291.35      293.01  213

and I want to end up with this:

year    City       electricity  water
2000    Boston          148.58  150.8
2001    Boston          189.41  170.34
2002    Boston          234.68  181.89
2003    Boston          272.14  186.43
2004    Boston          253.33  158.29
2000    Houston         111.73  159.11
2001    Houston         132.86  195.5
2002    Houston         164.54  223.02
2003    Houston         219.74  305.88
2004    Houston         234.13  291.35
2000    San Fransicso   170.74  165.74
2001    San Fransicso   205.14  199.14
2002    San Fransicso   223.33  123.33
2003    San Fransicso   311.17  411.17
2004    San Fransicso   293.01  213.01

Code to replicate dataset:

usage_data <- data.frame(stringsAsFactors=FALSE,
year = c("2000", "2001", "2002", "2003", "2004"),
Bn_elec = c(148.58, 189.41, 234.68, 272.14, 253.33),
Bn_water = c(150.8, 170.34, 181.89, 186.43, 158.29),
Hn_elec = c(111.73, 132.86, 164.54, 219.74, 234.13),
Hn_water = c(159.11, 195.5, 223.02, 305.88, 291.35),
SF_elec = c(170.74, 205.14, 223.33, 311.17, 293.01),
SF_water = c(165.74, 199.14, 123.33, 411.17, 213.01))
divibisan
  • 11,659
  • 11
  • 40
  • 58
xefere
  • 19
  • 3

1 Answers1

1

We gather into 'long', separate the 'key' column into 'two' and then spread

library(tidyverse)
gather(usage_data, key, val, -year) %>% 
  separate(key, into = c("City", "categ")) %>%
  spread(categ, val)
akrun
  • 874,273
  • 37
  • 540
  • 662