Can we make an application to process and fill data in 2 excel files using 1 formula file?

Question

(Excel)

1. so I have 1 formula file
2. This formula file, has been logically organized to process 2 types of data files
3. I want the process to be done automatically
4. So I just need to enter 2 data files into the app
5. and the output is 2 types of data files, already processed and filled in
6. So I can use the application for the same type of data but different values

could it happen? what kind of application is suitable? sorry if my English is bad :)

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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