CODEWITHSUNDEEP
rdataframerange
sw1t3
sw1t3

Reputation: 11

Create table from values assigned to ranges

I have a table of 3 columns:

  1. Start of range
  2. End of range
  3. Number assigned to all values within the range.

I want to create a table with the first column having values 1-x (x being the total of all ranges) and the second column with the assigned number for each value. Any unassigned values need to be set to 0.

E.g. original table:

start end value
1 4 -1
6 8 4

So the final table would be:

Number Value
1 -1
2 -1
3 -1
4 -1
5 0
6 4
7 4
8 4

But I have no idea where to start - any suggestions?

Thanks.

Upvotes: 1

Views: 642

Answers (4)

ekoam
ekoam

Reputation: 8844

If you are looking for a generic solution, you can try this function

expand_integers <- function(start, end, value) {
  n <- end - start + 1L
  rng <- range(c(start, end))
  pos <- sequence(n, start - rng[[1L]] + 1L)
  val <- rep.int(value, n)
  data.frame(
    number = seq.int(rng[[1L]], rng[[2L]]), 
    value = `[<-`(integer(rng[[2L]] - rng[[1L]] + 1L), pos, value = val)
  )
}

It works for any start and end values and is very efficient. Here is a simple test:

df <- data.frame(start = c(4L, 10L), end = c(7L, 19L), value = c(-1L, 4L))
df
expand_integers(df$start, df$end, df$value)

Output

> df
  start end value
1     4   7    -1
2    10  19     4
> expand_integers(df$start, df$end, df$value)
   number value
1       4    -1
2       5    -1
3       6    -1
4       7    -1
5       8     0
6       9     0
7      10     4
8      11     4
9      12     4
10     13     4
11     14     4
12     15     4
13     16     4
14     17     4
15     18     4
16     19     4

Upvotes: 1

TarJae
TarJae

Reputation: 78947

Here is a tidyverse solution:

library(dplyr)
library(tidyr)
df %>% 
  group_by(start) %>% 
  mutate(index = list(start:end)) %>% 
  unnest(cols = c(index)) %>% 
  ungroup() %>% 
  complete(index = 1:max(index), fill = list(value = 0)) %>% 
  select(Number=index, Value=value)

  Number Value
   <int> <dbl>
1      1    -1
2      2    -1
3      3    -1
4      4    -1
5      5     0
6      6     4
7      7     4
8      8     4

Upvotes: 1

Jost
Jost

Reputation: 436

The obligatory "data.table" solution ;), a general solution can be obtained using "foverlaps"

library(data.table)

data <- data.frame(start = c(1, 6), end= c(4, 8), value = c(-1, 4))
number <- data.frame(start = c(1:8), end = c(1:8))

setDT(data)
setDT(number)

setkey(data, start, end) 
df<-foverlaps(number, data)[, c("i.start", "value"),
                         with = FALSE] 
df[is.na(df$value), ]$value <- 0

Upvotes: 1

elielink
elielink

Reputation: 1202

Does this do the trick? starting from your data example

library(dplyr)
a = data.frame(start= c(1,6),end=c(4,8),value=c(-1,4))

c= apply(a, 1,function(i){
  b = i[1]:i[2]
  return(as.data.frame(cbind(b, rep(i[3], length(b)))))
})

c = bind_rows(c, .id = "column_label")[,-1]
d= (c[1,1]:c[nrow(c),1])[!c[1,1]:c[nrow(c),1]%in%c$b]
d= cbind(d, rep(0, length(d)))
colnames(d)=colnames(c)
res = rbind(c,d)[order(rbind(c,d)[,1]),]
rownames(res)= 1:nrow(res)
colnames(res)=c('Number', 'Value')

res

output:

> res
  Number Value
1      1    -1
2      2    -1
3      3    -1
4      4    -1
5      5     0
6      6     4
7      7     4
8      8     4

Upvotes: 1

Related Questions