R gather function: 2 level grouped data

Question

I need to gather a dataframe, however the columns have further groupins. Example:

Data I have

I know how to use gather when I have just the columns. But I would also like to have the groups, like this:

The way I want it

In such way I would be able to group this data as I want later. How can I do this?

        group1  group1  group2  group2  group2
Week    Region  1   2   3   4   5
201601  somehing    0   0   0   0   0
201602  somehing    0   0   0   0   0
201603  somehing    0   0   0   0   0
201604  somehing    0   0   79.03055276 0   20675
201605  somehing    0   0   68.2845204  0   955157
201606  somehing    0   0   46.12847797 0   943991
201607  somehing    0   0   0   0   935029
201608  somehing    0   0   0   0   899158
201609  somehing    0   0   0   0   127633
201610  somehing    0   0   0   0   0
201611  somehing    0   0   0   0   0
201612  somehing    0   0   0   0   0
201613  somehing    0   0   0   0   0
201614  somehing    0   94.71177518 0   0   0

Could you please dput() your data? or create a reproducible example? — DJV, May 28 '18 at 11:27
http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — zx8754, May 28 '18 at 11:30
At least copy paste the tables in. This way of asking the question is just very annoying and makes people not want to help you — 5th, May 28 '18 at 11:56

score 0 · Answer 1 · answered May 28 '18 at 12:23

You can try

library(tidyverse)
df %>% 
  gather(k, v, -Week, -Region) %>% 
  mutate(groups=case_when(k %in% paste0("X",1:2) ~ "group1",
                          k %in% paste0("X",3:5) ~ "group2")) %>%    
head
    Week   Region  k v groups
1 201601 somehing X1 0 group1
2 201602 somehing X1 0 group1
3 201603 somehing X1 0 group1
4 201604 somehing X1 0 group1
5 201605 somehing X1 0 group1
6 201606 somehing X1 0 group1

Data. read in without the first row. The groups will be added using case_when

df <- read.table(text="V1 V2 group1 group1  group2  group2  group2
    Week    Region  1   2   3   4   5
    201601  somehing    0   0   0   0   0
    201602  somehing    0   0   0   0   0
    201603  somehing    0   0   0   0   0
    201604  somehing    0   0   79.03055276 0   20675
    201605  somehing    0   0   68.2845204  0   955157
    201606  somehing    0   0   46.12847797 0   943991
    201607  somehing    0   0   0   0   935029
    201608  somehing    0   0   0   0   899158
    201609  somehing    0   0   0   0   127633
    201610  somehing    0   0   0   0   0
    201611  somehing    0   0   0   0   0
    201612  somehing    0   0   0   0   0
    201613  somehing    0   0   0   0   0
    201614  somehing    0   94.71177518 0   0   0", header=T,  skip=1)

R gather function: 2 level grouped data

1 Answers1