emehex emehex - 3 months ago 21
R Question

Expand range (low, high) to values with index

My data looks like this:

df <- data.frame(
index = c(1, 2, 3),
lo = c(1, 3, 6),
hi = c(2, 5, 10)
)

# index lo hi
# 1 1 1 2
# 2 2 3 5
# 3 3 6 10


I want to tidy the low - high range to expanded values:

df_new <- data.frame(
index = c(1, 1, 2, 2, 2, 3, 3, 3, 3, 3),
value = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
)

# index value
# 1 1 1
# 2 1 2
# 3 2 3
# 4 2 4
# 5 2 5
# 6 3 6
# 7 3 7
# 8 3 8
# 9 3 9
# 10 3 10

Answer

Two options with dplyr and tidyr, the second approach works when there are no duplicated indexes in the data frame:

library(dplyr); library(tidyr)
df %>% rowwise() %>% summarise(index = index, value = list(lo:hi)) %>% unnest()

# Source: local data frame [10 x 2]

#    index value
#    <dbl> <int>
# 1      1     1
# 2      1     2
# 3      2     3
# 4      2     4
# 5      2     5
# 6      3     6
# 7      3     7
# 8      3     8
# 9      3     9
# 10     3    10


df %>% group_by(index) %>% summarise(value = list(lo:hi)) %>% unnest()

# Source: local data frame [10 x 2]

#    index value
#    <dbl> <int>
# 1      1     1
# 2      1     2
# 3      2     3
# 4      2     4
# 5      2     5
# 6      3     6
# 7      3     7
# 8      3     8
# 9      3     9
# 10     3    10