String manipulation - replace text by variable and parse

Question

Task:

I have a Task to apply some given calculation rules at csv-input data. In the first step I Need to "translate" given rules (examples below) to R language. In the second step I load some csv files (Sheets) into R and apply the rules to the data to check whether the conditions given in the rules can be comfirmed.

C 01.00, F 08.01.b etc. are the different Sheets. Every sheet has many rows and columns.

Idea:

My idea is to define a Matrix for every sheet. For example F0801b is a Matrix from the sheet "F 08.01.b" with 99 rows and 99 columns and many values in it. The variable F0801bR450 would be the 45th row from the F0801b-Matrix.

Rules can look like this:

{r390, c010} == {r400, c010} + {r410, c010} + {r420, c010}

Every calculation rule is connected to a particular "sheet". If the rule is like above, the cell of 39th row and the 1st column of the same sheet has to be equal to the term on the right side. The task ist to grab the value from the cells given in the calculation rule, so replace the string within {} by the value from the Matrix and parse the equation in the end to check whether the condition is true.

{F 19.00.a, c100} <= {F 18.00.a, c120}

Let's say this rule is belonging to the sheet F 17.00.a. Here, the (sums of)columns of other sheets are called and shall be compared.

{C 02.00, r570 , c010} == {C 23.00, r010 , c070}

Here, specific cells of other forms shall be compared.

{C 01.00, r480} * ({C 04.00, r230} + {C 04.00, r300} + {C 04.00, r370}) == -(max({C 04.00, r230} + {C 04.00, r300} + {C 04.00, r370} - {C 04.00, r190}, 0)) * {C 04.00, r230}

In this example, rows and columns of different Sheets shall be calculated with to check whether the condition is true.

{F 08.01.b, r450} == sum({F 08.01.a, ({r010}, {r020}, {r050}, {r360}, {r440})})

A further example of how the rules can look.

Challenge:

Again: the challenge here is to translate These strings into R language and apply the rules to the data. The Goal is to have an Automation which has Logical values as an Output.

What I've tried so far:

to replace the cell coordinates by the specific value:

string<-"{r390, c010} == {r400, c010} + {r410, c010} + {r420, c010}"
# the string is actually a row of a data.frame-column like vrList$Formula[i]   
pat  <- "\\d+(?>\\d)\\B"
pat2 <- "\\{r..., c...\\}"

getCell=function(data,string){
  pos=regmatches(string,gregexpr(pat,string,perl = T))
  data[do.call(rbind,lapply(pos,as.numeric))]
}

pos<- regmatches(string,gregexpr(pat2,string,perl = T))
getCell(table,unlist(pos))

The code above can grab the values from the cells of my Matrix which contains randomly generated numbers just to proof the mechanics of the code. The idea of storing several variables for every sheet and grab the values from the different matrices is new and has not been implemented yet.

b <- gsubfn(pat2, getCell, string); b

Here I wanted to replace the original strings with the value, but gsubfn doesn't really work here. In the end the parse below should do ist part:

eval(parse(text=b))

I hope the examples are adequate to Show the complexity of the Task.

Thanks for your help.

What have you tried so far? On top of that, show us reproducible data as well. — Shique, Mar 28 '18 at 09:11
http://stackoverflow.com/help/how-to-askhttp://stackoverflow.com/questions/5963269/how-to-make-a-great-r- — timat, Mar 28 '18 at 10:15

G. Grothendieck · Answer 1 · 2018-03-28T16:51:43.893

Do you really need the custom formulas? is there any reason not to just write an R expression such as:

M <- matrix(1:16, 4) # test matrix

M[1, 2] == M[4, 1] + M[3, 3] + M[2, 1]
## [1] FALSE

If you really do need a custom formula notation, then the formulas in the question don't seem to be consistent so it is not clear which to use but if the formulas are of the following form a gsub/parse/eval would do it:

 # test input
 matrix_name <- "M"
 string <- "{r001, c002} == {r004, c001} + {r003, c003} + {r002, c002}"

 txt <- gsub("\\{r(\\d+), c(\\d+)\\}", paste0(matrix_name, "[\\1, \\2]"), string)
 eval(parse(text = txt))
 ## [1] FALSE

or if the formulas are of the form

 string2 <- "{M r001, c002} == {M r004, c001} + {M r003, c003} + {M r002, c002}" # input

 txt2 <- gsub("\\{(\\w+) r(\\d+), c(\\d+)\\}", "\\1[\\2, \\3]", string2)
 eval(parse(text = txt2))
 ## [1] FALSE

This prior question of yours with yet another variation was already answered: R: Define ranges from text using regex

String manipulation - replace text by variable and parse

1 Answers1