library(magrittr)
library(dplyr)
library(tibble)SN <- c(1,2,3,4,5)
test <- c(1,2,3)
group <- c('A', 'B')
col_SN <- rep(SN, times=length(test)*length(group))
col_test <- rep(test, each=length(SN), times=length(group))
col_group <- rep(group, each=length(SN)*length(test))
nrows <- length(col_SN)
sample_data <- tibble(SN = col_SN, TestNum = col_test, Group = col_group)
sample_data %<>% add_column(Val1 = round(abs(rnorm(nrows, mean = 0, sd = 1.05)), 3))
sample_data %<>% add_column(Val2 = sample.int(9, nrows, replace = TRUE))
sample_data %<>% add_column(Val3 = sample.int(100, nrows))
sample_data %>% print(n = Inf)## # A tibble: 30 x 6
## SN TestNum Group Val1 Val2 Val3
## <dbl> <dbl> <chr> <dbl> <int> <int>
## 1 1 1 A 1.53 3 10
## 2 2 1 A 0.513 9 30
## 3 3 1 A 0.159 3 4
## 4 4 1 A 1.10 4 9
## 5 5 1 A 0.048 5 53
## 6 1 2 A 0.567 1 26
## 7 2 2 A 0.814 1 91
## 8 3 2 A 0.125 8 93
## 9 4 2 A 0.461 7 33
## 10 5 2 A 3.73 6 42
## 11 1 3 A 0.466 8 38
## 12 2 3 A 1.19 2 75
## 13 3 3 A 2.21 6 54
## 14 4 3 A 0.476 1 15
## 15 5 3 A 0.901 6 85
## 16 1 1 B 1.76 1 47
## 17 2 1 B 0.791 7 43
## 18 3 1 B 0.053 3 18
## 19 4 1 B 0.152 3 8
## 20 5 1 B 0.763 7 48
## 21 1 2 B 0.919 1 24
## 22 2 2 B 0.695 9 50
## 23 3 2 B 0.351 5 58
## 24 4 2 B 0.911 4 92
## 25 5 2 B 0.152 6 71
## 26 1 3 B 0.49 6 41
## 27 2 3 B 0.63 7 72
## 28 3 3 B 0.529 6 82
## 29 4 3 B 1.02 8 64
## 30 5 3 B 0.862 4 31Try if we can correctly extract 1st test value of each SN per group using first():
sample_data %>%
group_by(Group, SN) %>%
mutate(Val1_delta = first(Val1)) %>%
print(n = Inf)## # A tibble: 30 x 7
## # Groups: Group, SN [10]
## SN TestNum Group Val1 Val2 Val3 Val1_delta
## <dbl> <dbl> <chr> <dbl> <int> <int> <dbl>
## 1 1 1 A 1.53 3 10 1.53
## 2 2 1 A 0.513 9 30 0.513
## 3 3 1 A 0.159 3 4 0.159
## 4 4 1 A 1.10 4 9 1.10
## 5 5 1 A 0.048 5 53 0.048
## 6 1 2 A 0.567 1 26 1.53
## 7 2 2 A 0.814 1 91 0.513
## 8 3 2 A 0.125 8 93 0.159
## 9 4 2 A 0.461 7 33 1.10
## 10 5 2 A 3.73 6 42 0.048
## 11 1 3 A 0.466 8 38 1.53
## 12 2 3 A 1.19 2 75 0.513
## 13 3 3 A 2.21 6 54 0.159
## 14 4 3 A 0.476 1 15 1.10
## 15 5 3 A 0.901 6 85 0.048
## 16 1 1 B 1.76 1 47 1.76
## 17 2 1 B 0.791 7 43 0.791
## 18 3 1 B 0.053 3 18 0.053
## 19 4 1 B 0.152 3 8 0.152
## 20 5 1 B 0.763 7 48 0.763
## 21 1 2 B 0.919 1 24 1.76
## 22 2 2 B 0.695 9 50 0.791
## 23 3 2 B 0.351 5 58 0.053
## 24 4 2 B 0.911 4 92 0.152
## 25 5 2 B 0.152 6 71 0.763
## 26 1 3 B 0.49 6 41 1.76
## 27 2 3 B 0.63 7 72 0.791
## 28 3 3 B 0.529 6 82 0.053
## 29 4 3 B 1.02 8 64 0.152
## 30 5 3 B 0.862 4 31 0.763Calculate % drift:
pct_diff <- function(reference, value) {
x <- abs(reference - value)/max(reference,value)
return(round(x,3))
}
result <- sample_data %>%
group_by(Group, SN) %>%
mutate(Val1_delta = pct_diff(first(Val1), Val1),
Val2_delta = pct_diff(first(Val2), Val2),
Val3_delta = pct_diff(first(Val3), Val3)) %>%
print(n = Inf, width = Inf)## # A tibble: 30 x 9
## # Groups: Group, SN [10]
## SN TestNum Group Val1 Val2 Val3 Val1_delta Val2_delta Val3_delta
## <dbl> <dbl> <chr> <dbl> <int> <int> <dbl> <dbl> <dbl>
## 1 1 1 A 1.53 3 10 0 0 0
## 2 2 1 A 0.513 9 30 0 0 0
## 3 3 1 A 0.159 3 4 0 0 0
## 4 4 1 A 1.10 4 9 0 0 0
## 5 5 1 A 0.048 5 53 0 0 0
## 6 1 2 A 0.567 1 26 0.629 0.25 0.421
## 7 2 2 A 0.814 1 91 0.252 0.889 0.67
## 8 3 2 A 0.125 8 93 0.015 0.625 0.957
## 9 4 2 A 0.461 7 33 0.579 0.429 0.727
## 10 5 2 A 3.73 6 42 0.987 0.167 0.129
## 11 1 3 A 0.466 8 38 0.695 0.625 0.737
## 12 2 3 A 1.19 2 75 0.57 0.778 0.495
## 13 3 3 A 2.21 6 54 0.928 0.375 0.538
## 14 4 3 A 0.476 1 15 0.566 0.429 0.182
## 15 5 3 A 0.901 6 85 0.229 0.167 0.376
## 16 1 1 B 1.76 1 47 0 0 0
## 17 2 1 B 0.791 7 43 0 0 0
## 18 3 1 B 0.053 3 18 0 0 0
## 19 4 1 B 0.152 3 8 0 0 0
## 20 5 1 B 0.763 7 48 0 0 0
## 21 1 2 B 0.919 1 24 0.479 0 0.489
## 22 2 2 B 0.695 9 50 0.121 0.222 0.097
## 23 3 2 B 0.351 5 58 0.563 0.333 0.488
## 24 4 2 B 0.911 4 92 0.741 0.125 0.913
## 25 5 2 B 0.152 6 71 0.709 0.143 0.324
## 26 1 3 B 0.49 6 41 0.722 0.833 0.128
## 27 2 3 B 0.63 7 72 0.204 0 0.403
## 28 3 3 B 0.529 6 82 0.9 0.5 0.78
## 29 4 3 B 1.02 8 64 0.852 0.625 0.609
## 30 5 3 B 0.862 4 31 0.115 0.429 0.239Filter relevant data and rearrange columns:
result %>%
filter(TestNum > 1) %>%
select(Group, SN, TestNum, Val1, Val1_delta, Val2, Val2_delta, Val3, Val3_delta) %>%
print(n = Inf, width = Inf) ## # A tibble: 20 x 9
## # Groups: Group, SN [10]
## Group SN TestNum Val1 Val1_delta Val2 Val2_delta Val3 Val3_delta
## <chr> <dbl> <dbl> <dbl> <dbl> <int> <dbl> <int> <dbl>
## 1 A 1 2 0.567 0.629 1 0.25 26 0.421
## 2 A 2 2 0.814 0.252 1 0.889 91 0.67
## 3 A 3 2 0.125 0.015 8 0.625 93 0.957
## 4 A 4 2 0.461 0.579 7 0.429 33 0.727
## 5 A 5 2 3.73 0.987 6 0.167 42 0.129
## 6 A 1 3 0.466 0.695 8 0.625 38 0.737
## 7 A 2 3 1.19 0.57 2 0.778 75 0.495
## 8 A 3 3 2.21 0.928 6 0.375 54 0.538
## 9 A 4 3 0.476 0.566 1 0.429 15 0.182
## 10 A 5 3 0.901 0.229 6 0.167 85 0.376
## 11 B 1 2 0.919 0.479 1 0 24 0.489
## 12 B 2 2 0.695 0.121 9 0.222 50 0.097
## 13 B 3 2 0.351 0.563 5 0.333 58 0.488
## 14 B 4 2 0.911 0.741 4 0.125 92 0.913
## 15 B 5 2 0.152 0.709 6 0.143 71 0.324
## 16 B 1 3 0.49 0.722 6 0.833 41 0.128
## 17 B 2 3 0.63 0.204 7 0 72 0.403
## 18 B 3 3 0.529 0.9 6 0.5 82 0.78
## 19 B 4 3 1.02 0.852 8 0.625 64 0.609
## 20 B 5 3 0.862 0.115 4 0.429 31 0.239