-3

I have a long dataset that looks like this:

dat <- data.frame(enterprise = c("a","b"), rev01 = c(1, 10), rev02 = c(2, 9), rev03 = c(3, 8), rev04 = c(4,7), rev05 = c(5, 6),
         emp01 = c(6, 5), emp02 = c(7, 4), emp03 = c(8, 3), emp04 = c(9, 2), emp05 = c(10, 1))

enter image description here

Where "rev 1 to 5" is the revenue of the companies "a" and "b" in the years 1 to 5, and "emp 1 to 5" is the number of employees of these companies in the same period.

I wanted to transform this data from 'wide' to 'long' using the 'gather' function, but I don't know how to use this function to match the YEAR, the REVENUE, and the NUMBER OF EMPLOYEES.

What I wanted was something like this:

enter image description here

Thank you!

M.G.
  • 87
  • 6
  • Yes, I think you can. If you provide data in right format, you are more likely to get a quick answer. – Zhiqiang Wang Nov 08 '19 at 12:35
  • Sorry: dat <- data.frame(enterprise = c("a","b"), rev01 = c(1, 10), rev02 = c(2, 9), rev03 = c(3, 8), rev04 = c(4,7), rev05 = c(5, 6), emp01 = c(6, 5), emp02 = c(7, 4), emp03 = c(8, 3), emp04 = c(9, 2), emp05 = c(10, 1)) – M.G. Nov 08 '19 at 12:40
  • 2
    In general, this is a commonly asked question. `tidyr::gather()` has been retired in favour of `tidyr::pivot_longer()`. The latter can handle multiple value variables where the former could not. – Ritchie Sacramento Nov 08 '19 at 12:46

1 Answers1

2

you can try this:

df %>% 
  gather(key, value, -company) %>% 
  separate(key, c("key", "year")) %>% 
  spread(key, value)

output is:

# A tibble: 10 x 4
   company year    emp   rev
   <chr>   <chr> <dbl> <dbl>
 1 a       1         6     1
 2 a       2         7     2
 3 a       3         8     3
 4 a       4         9     4
 5 a       5        10     5
 6 b       1         5    10
 7 b       2         4     9
 8 b       3         3     8
 9 b       4         2     7
10 b       5         1     6

I used this data:

structure(list(company = c("a", "b"), `rev 1` = c(1, 10), `rev 2` = c(2, 
9), `rev 3` = c(3, 8), `rev 4` = c(4, 7), `rev 5` = c(5, 6), 
    `emp 1` = c(6, 5), `emp 2` = c(7, 4), `emp 3` = c(8, 3), 
    `emp 4` = c(9, 2), `emp 5` = c(10, 1)), class = c("spec_tbl_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -2L), spec = structure(list(
    cols = list(company = structure(list(), class = c("collector_character", 
    "collector")), `rev 1` = structure(list(), class = c("collector_double", 
    "collector")), `rev 2` = structure(list(), class = c("collector_double", 
    "collector")), `rev 3` = structure(list(), class = c("collector_double", 
    "collector")), `rev 4` = structure(list(), class = c("collector_double", 
    "collector")), `rev 5` = structure(list(), class = c("collector_double", 
    "collector")), `emp 1` = structure(list(), class = c("collector_double", 
    "collector")), `emp 2` = structure(list(), class = c("collector_double", 
    "collector")), `emp 3` = structure(list(), class = c("collector_double", 
    "collector")), `emp 4` = structure(list(), class = c("collector_double", 
    "collector")), `emp 5` = structure(list(), class = c("collector_double", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), skip = 1), class = "col_spec"))
Stephan
  • 2,056
  • 1
  • 9
  • 20