Can I make R work as excel in terms of dynamic formulas

[haythamomar]

hELLO aLL,

I AM TRYING TO MIMIC AN EXCEL SHEET FOR INVENTORY MODELLING WHERE VALUES OF CELLS CHANGES DYNAMICALLY BASED ON THE VALUES OF OTHER CELLS.
However this is not happening in R .
for example if I have a data Frame and I say x<- data.frame(a=5,b=6)
and then x$c <- a+b so c should be 11.
however when I change a to say 1 instead of 5, c is not changed to 7. the problem is I have many column that needs to by dynamic to each other , is there a package that mimics this like excel. Thanks in advance .

Haytham

[FvD]

Once you grasp what how a data frame behaves you will be able to mimic the excel sheet in full. In this particular case what is happening is the following.

First you create a data.frame:

> x<- data.frame(a=5,b=6)
> x
  a b
1 5 6

And so you now have one data frame x with two columns a and b.

But in your next step your are trying to get at a and b as separte objects (not columns) so this will not work. It will in fact give an error (unless you have assigned a value to a and b in a separate step.

> x$c <- a+b 
Error: object 'a' not found

But here comes dplyr to the rescue to give you a readable way to do what you want. Consider:

library(dplyr)
x %>% 
  mutate(c = a + b)

The result will show a new column c with 11 as its value in the first row (the sum of a and b).

  a b  c
1 5 6 11
> 

If you extend your dataframe with as many additional rows as you need, the mutate() approach will still work.

[haythamomar]

Hi Fvd,

Thank you for your reply , yes I did have that error. and the workaround is your proposal.

now suppose A is changed to 1 instead of 5 , is there a dynamic was to make c 7, not 11 ? this is a simple example but my spread sheets every cell has a dependability on many other cells in the same row and in previous rows.
is there for example a for loop I can do that iterates on the row first to finish it and then go to the next row .
I mean dosent's iterate on the values inside the column separately. because when it does that , the values are not correct.

let me give an example

suppose when my inventory level is 5 , I order 10

demand inventory level order
5 5 10
8 7 0
10 -3 10

I have other variables such as expected, received and backlogs and commutative backlogs in columns .
I tried for loops with if functions and it works but not simaltnously as it should be , it iterates over the columns , I need the loop to iterate over all the values in one row first and go to the next row.

Thank you in advance,
Haytham

[andresrcs]

You need to change your mind set when working with R, R is a programming language not a spreadsheet editor, R only do stuff when you execute the code, if you change your inputs and run the code again then you get new results based on the new input, see this example.

library(dplyr)

input_data <- data.frame(stringsAsFactors = FALSE,
                         demand = c(5, 8),
                         inventory = c(5, 7))
order_calc <- function(df) {
    df %>% 
        mutate(level_order = demand + inventory)
}

order_calc(input_data)
#>   demand inventory level_order
#> 1      5         5          10
#> 2      8         7          15

new_input_data <- data.frame(stringsAsFactors = FALSE,
                             demand = c(2, 9),
                             inventory = c(4, 5))

order_calc(new_input_data)
#>   demand inventory level_order
#> 1      2         4           6
#> 2      9         5          14

^{Created on 2019-03-15 by the reprex package (v0.2.1)}

Depending on your specific application, you can get reactivity with R through a shiny app, but that is nothing like excel behaviour.