Add to dataframe using mathematical equation with uneven number of variables from the existing colum-CodePudding

I am trying to make new column by performing an equation that puts each Port's Value within a given day inputted along with its Port_max for that day. The Port_max is associated with its Timestamp of when it occurs. and the equation is *119((max value/Value)-1)^1.231

I initially tried df1 <- df %>% mutate(x = 119*((df$Port_max/df$Value)-1)^1.231) didn't work because there is only 1 value per day per port for the Port_max

Sample of the dataframe that covers 6 weeks 24hrs/day:

Timestamp              Port Value  Port_max
2021-05-19 00:00:00    1    0.66   0.66
2021-05-19 00:00:00    2    0.57   0.57
2021-05-19 00:00:00    3    0.47   NA
2021-05-19 00:00:00    4    0.83   0.83
2021-05-19 00:00:00    5    0.70   NA
2021-05-19 00:00:00    6    0.81   NA
2021-05-19 00:15:00    1    0.55   NA
2021-05-19 00:15:00    2    0.62   NA
2021-05-19 00:15:00    3    0.76   0.76

CodePudding user response：

Are you looking for this?

library(dplyr)

df %>% 
  mutate( x =  119*((max(Port)/Value)-1)^1.231)

            Timestamp Port Value Port_max        x
1 2021-05-19 00:00:00    1  0.66     0.66 1560.600
2 2021-05-19 00:00:00    2  0.57     0.67 1908.111
3 2021-05-19 00:00:00    3  0.47       NA 2474.510
4 2021-05-19 00:00:00    4  0.83     0.83 1131.016
5 2021-05-19 00:00:00    5  0.70       NA 1438.185
6 2021-05-19 00:00:00    6  0.81       NA 1171.043
7 2021-05-19 00:15:00    1  0.55       NA 2002.925
8 2021-05-19 00:15:00    2  0.62       NA 1701.006
9 2021-05-19 00:15:00    3  0.76     0.76 1281.629

data:

df <- structure(list(Timestamp = c("2021-05-19 00:00:00", "2021-05-19 00:00:00", 
"2021-05-19 00:00:00", "2021-05-19 00:00:00", "2021-05-19 00:00:00", 
"2021-05-19 00:00:00", "2021-05-19 00:15:00", "2021-05-19 00:15:00", 
"2021-05-19 00:15:00"), Port = c(1L, 2L, 3L, 4L, 5L, 6L, 1L, 
2L, 3L), Value = c(0.66, 0.57, 0.47, 0.83, 0.7, 0.81, 0.55, 0.62, 
0.76), Port_max = c(0.66, 0.67, NA, 0.83, NA, NA, NA, NA, 0.76
)), class = "data.frame", row.names = c(NA, -9L))

CodePudding user response：

Is this would help ?

df$x <- 119 * (df$Port_max / df$Value - 1)^1.231

Output

            Timestamp Port Value Port_max        x
1 2021-05-19 00:00:00    1  0.66     0.66  0.00000
2 2021-05-19 00:00:00    2  0.57     0.67 13.96577
3 2021-05-19 00:00:00    3  0.47       NA       NA
4 2021-05-19 00:00:00    4  0.83     0.83  0.00000
5 2021-05-19 00:00:00    5  0.70       NA       NA
6 2021-05-19 00:00:00    6  0.81       NA       NA
7 2021-05-19 00:15:00    1  0.55       NA       NA
8 2021-05-19 00:15:00    2  0.62       NA       NA
9 2021-05-19 00:15:00    3  0.76     0.76  0.00000