R - Pipeline to add column with If Formula-CodePudding

I have the following dataset

    A   B   C   D   E   F
    Ph  St  Per PM  On  IOI
1   1   959 270 3   6   NA
2   1   959 270 3   6   0.267
3   1   960 270 3   6   0.759
4   1   960 270 3   6   0.248
5   1   961 270 3   6   0.612
6   1   961 270 3   6   0.268
7   2   962 250 11  9   0.573
8   2   962 250 11  9   0.259
9   2   962 250 11  9   0.238
10  2   963 250 11  9   0.250
11  2   963 250 11  9   0.251
12  2   963 250 11  9   0.245
13  2   963 250 11  9   0.244
14  2   964 250 11  9   0.260
15  2   964 250 11  9   0.234
16  3   965 256 11  10  0.887
17  3   965 256 11  10  0.238
18  3   965 256 11  10  0.233
19  3   965 256 11  10  0.275

And I would like to add a column with the following formula:

IF(C2=C1,C2*1/F1*1000,0) - I'm using the equivalent formulas in Excel here. That is, is the second and the first row of column Per are equal, apply the formula 1/IOI*1000 and continue with this throughout the whole dataset.

I was creating a pipeline for it:

dt <- dt %>% 
  if (lag(Per) = Per) {
    mutate(Ratio = IOI * 1/Per*1000)  
  } else {
    mutate(Ratio = 0)
  }

But there is clearly something wrong with my code.

Any suggestions?

CodePudding user response：

Here is for your reference.

dt <- read.csv(
text="
    Ph , St,  Per, PM,  On , IOI
   1,   959, 270, 3  , 6  , NA
   1 ,  959, 270, 3  , 6 ,  0.267
   1  , 960, 270, 3  , 6,   0.759
   1  , 960, 250, 3  , 6,   0.7
",header=TRUE,stringsAsFactors = FALSE)

dt$Per=as.numeric(dt$Per)
dt$IOI=as.numeric(dt$IOI)


dt = dt %>% mutate( ratio= (ifelse(lag(Per) == Per ,  IOI * 1/Per*1000, 0)) )

result:

Ph  St  Per PM  On  IOI ratio
<dbl>   <int>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
1   959 270 3   6   NA  NA
1   959 270 3   6   0.267   0.9888889
1   960 270 3   6   0.759   2.8111111
1   960 250 3   6   0.700   0.0000000

CodePudding user response：

Is something like that what you need?

dt <- read.csv(
text="Ph, St, Per, PM, On, IOI,
1, 959, 270, 3, 6, ,
1, 959, 270, 3, 6, 0.267,
1, 960, 270, 3, 6, 0.759,
1, 960, 270, 3, 6, 0.248,
1, 961, 270, 3, 6, 0.612,
1, 961, 270, 3, 6, 0.268,
2, 962, 250, 11, 9, 0.573,
2, 962, 250, 11, 9, 0.259,
2, 962, 250, 11, 9, 0.238,
2, 963, 250, 11, 9, 0.250,
2, 963, 250, 11, 9, 0.251,
2, 963, 250, 11, 9, 0.245,
2, 963, 250, 11, 9, 0.244,
2, 964, 250, 11, 9, 0.260,
2, 964, 250, 11, 9, 0.234,
3, 965, 256, 11, 10, 0.887,
3, 965, 256, 11, 10, 0.238,
3, 965, 256, 11, 10, 0.233,
3, 965, 256, 11, 10, 0.275", header=TRUE, stringsAsFactors = FALSE)[,1:6]

dt$ratio<- ifelse(dt$Per - lag(dt$Per, n = 1) != 0, yes = 0, no = dt$IOI * 1/dt$Per*1000)