How to gather and fill in values over a range

Question

I'm trying to convert from wide to long data, but having trouble with a specific problem. Each person has a row of rooms in a hotel, but I only know their first and last room. I'd like to fill in the rooms that are between first and last room.

data = read.table(text="
   name  first_room  last_room
   A   2  5
   B   4   7", header=TRUE)

and I'm trying to get every room each person has, including the ones in between.

I've tried using gather in tidyr, but that only gives me the first and last room.

data %>% gather(type, room, -1) %>% arrange(name)

current output:

     name    type room
 1    A first_room    2
 2    A  last_room    5
 3    B first_room    4
 4    B  last_room    7

wanted output:

   Name       type room
 1    A first_room    2
 2    A  last_room    3
 3    A  last_room    4
 4    A  last_room    5
 5    B first_room    4
 6    B first_room    5
 7    B first_room    6
 8    B first_room    7

you could do `data %>% gather(type, room, -name) %>% group_by(name) %>% complete(room = seq(min(room), max(room)), fill = list(type = "last room"))` — Ronak Shah, May 10 '19 at 04:29

score 3 · Accepted Answer · answered May 10 '19 at 04:29

One option would be after grouping by 'name', get a complete sequence from the first to the last element of 'room' and then fill

library(tidyverse)
data %>% 
   gather(type, room, -1) %>%
   arrange(name) %>% 
   group_by(name) %>%
   complete(room = seq(room[1], room[2]))  %>% 
   fill(type, .direction = "up")
# A tibble: 8 x 3
# Groups:   name [2]
#  name   room type      
#  <fct> <int> <chr>     
#1 A         2 first_room
#2 A         3 last_room 
#3 A         4 last_room 
#4 A         5 last_room 
#5 B         4 first_room
#6 B         5 last_room 
#7 B         6 last_room 
#8 B         7 last_room

score 1 · Answer 2 · answered May 10 '19 at 04:35

Here's a more manual approach. I'd probably use akrun's solution with complete, but this is another option. This is more general in some ways... you could do stranger things with it if you wanted something similar to but not quite the complete sequence.

data %>% mutate(room = Map(seq, from = data$first_room, to = data$last_room)) %>%
  tidyr::unnest() %>%
  select(name, room) %>%
  group_by(name) %>%
  mutate(type = if_else(row_number() == 1, "first room", "last_room"))
# # A tibble: 8 x 3
# # Groups:   name [2]
#   name   room type      
#   <fct> <int> <chr>     
# 1 A         2 first room
# 2 A         3 last_room 
# 3 A         4 last_room 
# 4 A         5 last_room 
# 5 B         4 first room
# 6 B         5 last_room 
# 7 B         6 last_room 
# 8 B         7 last_room

How to gather and fill in values over a range

2 Answers2