2

I have 150 stops (Cod) and each one of this have a number of service that used.

|        Cod |  SERVICE1 | SERVICE2 | SERVICE3 | Position
------------------------------------------------------
| P05        |     XRS10 |     XRS07|     XRS05| 12455
| R07        |      FR05 |          |          | 4521
| X05        |     XRS07 |     XRS10|          | 57541

I need to put all the services (SERVICE1,SERVICE2,SERVICE3) in one column. That means that I need the following result.

|        Cod |   SERVICE |  Position
------------------------------------------------------
| P05        |     XRS10 |   12455
| P05        |     XRS07 |   12455
| P05        |     XRS05 |   12455
| R07        |      FR05 |    4521
| X05        |     XRS07 |   57541
| X05        |     XRS10 |   57541

There is any way to do this using the sqldf package of R. Or any kind of way to do it?

Matias.10
  • 53
  • 6
  • Could you please include a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) of the data, perhaps using `dput()`? – BarkleyBG Jul 18 '16 at 19:29

1 Answers1

1

try this:

library(magrittr) ##used for the pipe, %>%
library(dplyr) ##for filtering observations and selecting columns
library(tidyr) ##for making your dataset long/tidy

new_data <- original_data %>%
tidyr::gather(key = service_type, value = SERVICE) %>%
dplyr::filter(!is.na(SERVICE)) %>%
dplyr::select(-service_type)

Unfortunately I am not familiar with sqldf

Note that if you want to keep the information on whether the service comes from SERVICE1, SERVICE2, or SERVICE3, you'll omit the last line (dplyr::select) entirely.

BarkleyBG
  • 664
  • 5
  • 16