I am trying to append a table with limits for the values of another table according to the matrix and analyte of the value.
Table One has columns including Matrix (Fish or Floc), Analyte (As, Cd, Cr, Cu, Pb, Hg), and ResultWet (numeric values).
Table Two has rows for each Analyte in Table One and 3 columns, one for the Fish matrix and two for the Floc matrix.
I am trying to figure out how to bind Table Two to Table One so that each specific combination of Analyte and Matrix will have the corresponding limit value from Table Two appended as a new column(s), with rows not meeting the conditions reading NA.
The limit_table looks like this:
limits= matrix(c(30,33,9.79,
0.5,4.98,0.99,
0.88,111,43.4,
300,149,31.6,
0.1,1.06,0.18,
50,128,35.8), nrow=6, ncol=3, byrow=TRUE)
colnames(limits) = c("fish","floc_PEC","floc_TEC")
rownames(limits) = c("As","Cd","Cr","Cu","Hg","Pb")
limit_table=as.table(limits)
limit_table
fish floc_PEC floc_TEC
As 30.00 33.00 9.79
Cd 0.50 4.98 0.99
Cr 0.88 111.00 43.40
Cu 300.00 149.00 31.60
Hg 0.10 1.06 0.18
Pb 50.00 128.00 35.80
The Results table essentially looks like this:
setwd("Y:/Luke/HWTT")
Lab_Results <- read.csv(
file = "Grassy_4.22_Data.csv",
fileEncoding = 'UTF-8-BOM',
header =TRUE,
sep = ",",
dec = "."
)
Lab_Results= Lab_Results[c("Sample_Date","Matrix","Analyte","ResultDry","ResultWet","MDLDry","MRLDry","MDLWet","MRLWet","Units")]
Results= Lab_Results[which(Lab_Results$Matrix=='Fish' | Lab_Results$Matrix=="Floc"),]
Sample_Date Matrix Analyte ResultDry ResultWet MDLDry MRLDry MDLWet MRLWet Units
1 04/27/2022 Floc %TS 5.6800 5.68000 0.13000 0.42000 0.130000 0.420000 %
2 04/27/2022 Floc As 1.9000 0.10800 0.71300 1.66000 0.040000 0.094000 mg/kg
3 04/27/2022 Floc Cd 0.3170 0.01800 0.31700 0.71300 0.018000 0.040000 mg/kg
4 04/27/2022 Floc Cr 10.5000 0.59500 7.13000 14.30000 0.405000 0.809000 mg/kg
5 04/27/2022 Floc Cu 4.2000 0.24000 4.20000 8.40000 0.240000 0.480000 mg/kg
6 04/27/2022 Floc Pb 3.5300 0.20000 0.71300 2.14000 0.040000 0.121000 mg/kg
7 04/27/2022 Floc Hg 0.0269 0.00153 0.00563 0.01410 0.000319 0.000799 mg/kg
8 04/29/2022 Fish %TS 20.5700 20.57000 0.33000 1.09000 0.330000 1.090000 %
9 04/29/2022 Fish As 0.1620 0.03300 0.03900 0.09200 0.008000 0.019000 mg/kg
10 04/29/2022 Fish Cd 0.0170 0.00400 0.01700 0.03900 0.004000 0.008000 mg/kg
11 04/29/2022 Fish Cr 0.8950 0.18400 0.39200 0.78400 0.081000 0.161000 mg/kg
12 04/29/2022 Fish Cu 7.9700 1.64000 0.23100 0.46200 0.048000 0.095000 mg/kg
13 04/29/2022 Fish Hg 0.1900 0.03900 0.00308 0.00771 0.000634 0.001590 mg/kg
14 04/29/2022 Fish Pb 1.3900 0.28700 0.03900 0.11800 0.008000 0.024000 mg/kg
I would like the new table to look something like this, with the values for the 3 new columns corresponding to the matrix and analyte or otherwise NA:
Sample_Date Matrix Analyte ResultDry ResultWet MDLDry MRLDry MDLWet MRLWet Units Fish_Limit Floc_PEC Floc_TEC
1 04/27/2022 Floc %TS 5.6800 5.68000 0.13000 0.42000 0.130000 0.420000 %
2 04/27/2022 Floc As 1.9000 0.10800 0.71300 1.66000 0.040000 0.094000 mg/kg
3 04/27/2022 Floc Cd 0.3170 0.01800 0.31700 0.71300 0.018000 0.040000 mg/kg
4 04/27/2022 Floc Cr 10.5000 0.59500 7.13000 14.30000 0.405000 0.809000 mg/kg
5 04/27/2022 Floc Cu 4.2000 0.24000 4.20000 8.40000 0.240000 0.480000 mg/kg
6 04/27/2022 Floc Pb 3.5300 0.20000 0.71300 2.14000 0.040000 0.121000 mg/kg
7 04/27/2022 Floc Hg 0.0269 0.00153 0.00563 0.01410 0.000319 0.000799 mg/kg
8 04/29/2022 Fish %TS 20.5700 20.57000 0.33000 1.09000 0.330000 1.090000 %
9 04/29/2022 Fish As 0.1620 0.03300 0.03900 0.09200 0.008000 0.019000 mg/kg
10 04/29/2022 Fish Cd 0.0170 0.00400 0.01700 0.03900 0.004000 0.008000 mg/kg
11 04/29/2022 Fish Cr 0.8950 0.18400 0.39200 0.78400 0.081000 0.161000 mg/kg
12 04/29/2022 Fish Cu 7.9700 1.64000 0.23100 0.46200 0.048000 0.095000 mg/kg
13 04/29/2022 Fish Hg 0.1900 0.03900 0.00308 0.00771 0.000634 0.001590 mg/kg
14 04/29/2022 Fish Pb 1.3900 0.28700 0.03900 0.11800 0.008000 0.024000 mg/kg
Edit:
I reformatted the limit_table to make the row names into a new column called Analyte and merged the table to the first table with Analyte as the by= operator:
limits= matrix(c("As",30,33,9.79,
"Cd",0.5,4.98,0.99,
"Cr",0.88,111,43.4,
"Cu",300,149,31.6,
"Hg",0.1,1.06,0.18,
"Pb",50,128,35.8), nrow=6, ncol=4, byrow=TRUE)
colnames(limits) = c("Analyte","Fish_Limit","Floc_PEC","Floc_TEC")
limits
Results= merge(Results,limits,by='Analyte', all.x=TRUE)
However, there is no way I can see to only add the Fish_Limit column to rows that include the Fish Matrix value and the Floc_PEC and Floc_TEC columns to the rows that have the Floc Matrix. Is there any way to include that extra step?
Here is a part of the new table so far:
> Results
Analyte Sample_Date Matrix ResultDry ResultWet MDLDry MRLDry MDLWet MRLWet Units Fish_Limit Floc_PEC Floc_TEC
1 %TS 04/27/2022 Floc 5.6800 5.68000 0.13000 0.42000 0.130000 0.420000 % <NA> <NA> <NA>
2 %TS 04/27/2022 Floc 5.2100 5.21000 0.14000 0.45000 0.140000 0.450000 % <NA> <NA> <NA>
3 %TS 04/27/2022 Floc 5.2700 5.27000 0.13000 0.45000 0.130000 0.450000 % <NA> <NA> <NA>
4 %TS 04/29/2022 Fish 21.8700 21.87000 0.28000 0.94000 0.280000 0.940000 % <NA> <NA> <NA>
5 %TS 04/29/2022 Fish 21.9800 21.98000 0.28000 0.92000 0.280000 0.920000 % <NA> <NA> <NA>
6 %TS 04/29/2022 Fish 21.9200 21.92000 0.30000 1.00000 0.300000 1.000000 % <NA> <NA> <NA>
7 %TS 04/29/2022 Fish 20.2800 20.28000 0.31000 1.02000 0.310000 1.020000 % <NA> <NA> <NA>
8 %TS 04/29/2022 Fish 20.5700 20.57000 0.33000 1.09000 0.330000 1.090000 % <NA> <NA> <NA>
9 %TS 04/29/2022 Fish 20.9800 20.98000 0.33000 1.10000 0.330000 1.100000 % <NA> <NA> <NA>
10 %TS 04/29/2022 Fish 20.8200 20.82000 0.34000 1.12000 0.340000 1.120000 % <NA> <NA> <NA>
11 %TS 04/29/2022 Fish 21.2000 21.20000 0.29000 0.96000 0.290000 0.960000 % <NA> <NA> <NA>
12 %TS 04/29/2022 Fish 21.5300 21.53000 0.28000 0.95000 0.280000 0.950000 % <NA> <NA> <NA>
13 As 04/29/2022 Fish 0.1240 0.02700 0.04100 0.09500 0.009000 0.021000 mg/kg 30 33 9.79
14 As 04/29/2022 Fish 0.1560 0.03400 0.04000 0.09300 0.009000 0.020000 mg/kg 30 33 9.79
15 As 04/27/2022 Floc 1.9000 0.10800 0.71300 1.66000 0.040000 0.094000 mg/kg 30 33 9.79
16 As 04/27/2022 Floc 2.3200 0.12100 0.75800 1.77000 0.040000 0.092000 mg/kg 30 33 9.79
17 As 04/27/2022 Floc 2.3900 0.12600 0.79700 1.86000 0.042000 0.098000 mg/kg 30 33 9.79
18 As 04/29/2022 Fish 0.1640 0.03600 0.03800 0.09000 0.008000 0.020000 mg/kg 30 33 9.79
19 As 04/29/2022 Fish 0.1550 0.03400 0.04000 0.09400 0.009000 0.021000 mg/kg 30 33 9.79
20 As 04/29/2022 Fish 0.1780 0.03700 0.04000 0.09200 0.008000 0.019000 mg/kg 30 33 9.79
21 As 04/29/2022 Fish 0.1600 0.03200 0.03900 0.09200 0.008000 0.019000 mg/kg 30 33 9.79
22 As 04/29/2022 Fish 0.1620 0.03300 0.03900 0.09200 0.008000 0.019000 mg/kg 30 33 9.79
23 As 04/29/2022 Fish 0.1700 0.03600 0.04000 0.09400 0.008000 0.020000 mg/kg 30 33 9.79
24 As 04/29/2022 Fish 0.1330 0.02800 0.04200 0.09700 0.009000 0.021000 mg/kg 30 33 9.79
25 Cd 04/29/2022 Fish 0.0180 0.00400 0.01800 0.04000 0.004000 0.009000 mg/kg 0.5 4.98 0.99
26 Cd 04/29/2022 Fish 0.0180 0.00400 0.01800 0.04100 0.004000 0.009000 mg/kg 0.5 4.98 0.99
27 Cd 04/29/2022 Fish 0.0180 0.00400 0.01800 0.04200 0.004000 0.009000 mg/kg 0.5 4.98 0.99
28 Cd 04/29/2022 Fish 0.0180 0.00400 0.01800 0.04000 0.004000 0.009000 mg/kg 0.5 4.98 0.99
29 Cd 04/27/2022 Floc 0.3170 0.01800 0.31700 0.71300 0.018000 0.040000 mg/kg 0.5 4.98 0.99
30 Cd 04/27/2022 Floc 0.3370 0.01800 0.33700 0.75800 0.018000 0.040000 mg/kg 0.5 4.98 0.99
31 Cd 04/27/2022 Floc 0.3540 0.01900 0.35400 0.79700 0.019000 0.042000 mg/kg 0.5 4.98 0.99
32 Cd 04/29/2022 Fish 0.0170 0.00400 0.01700 0.03800 0.004000 0.008000 mg/kg 0.5 4.98 0.99
33 Cd 04/29/2022 Fish 0.0180 0.00400 0.01800 0.04000 0.004000 0.008000 mg/kg 0.5 4.98 0.99
34 Cd 04/29/2022 Fish 0.0180 0.00400 0.01800 0.04000 0.004000 0.008000 mg/kg 0.5 4.98 0.99
35 Cd 04/29/2022 Fish 0.0170 0.00400 0.01700 0.03900 0.004000 0.008000 mg/kg 0.5 4.98 0.99
36 Cd 04/29/2022 Fish 0.0170 0.00400 0.01700 0.03900 0.004000 0.008000 mg/kg 0.5 4.98 0.99
CodePudding user response：
If your limits object is a data.frame (see input below), you can do this, using dplyr
library(dplyr)
Results %>%
left_join(
bind_rows(
limits %>% mutate(Matrix = "Fish") %>% mutate(Floc_PEC = NA, Floc_TEC=NA),
limits %>% mutate(Matrix = "Floc") %>% mutate(Fish_Limit = NA)
)
)
Output:
Sample_Date Matrix Analyte ResultDry ResultWet MDLDry MRLDry MDLWet MRLWet Units Fish_Limit Floc_PEC Floc_TEC
1 04/27/2022 Floc %TS 5.6800 5.68000 0.13000 0.42000 0.130000 0.420000 % NA NA NA
2 04/27/2022 Floc As 1.9000 0.10800 0.71300 1.66000 0.040000 0.094000 mg/kg NA 33.00 9.79
3 04/27/2022 Floc Cd 0.3170 0.01800 0.31700 0.71300 0.018000 0.040000 mg/kg NA 4.98 0.99
4 04/27/2022 Floc Cr 10.5000 0.59500 7.13000 14.30000 0.405000 0.809000 mg/kg NA 111.00 43.40
5 04/27/2022 Floc Cu 4.2000 0.24000 4.20000 8.40000 0.240000 0.480000 mg/kg NA 149.00 31.60
6 04/27/2022 Floc Pb 3.5300 0.20000 0.71300 2.14000 0.040000 0.121000 mg/kg NA 128.00 35.80
7 04/27/2022 Floc Hg 0.0269 0.00153 0.00563 0.01410 0.000319 0.000799 mg/kg NA 1.06 0.18
8 04/29/2022 Fish %TS 20.5700 20.57000 0.33000 1.09000 0.330000 1.090000 % NA NA NA
9 04/29/2022 Fish As 0.1620 0.03300 0.03900 0.09200 0.008000 0.019000 mg/kg 30.00 NA NA
10 04/29/2022 Fish Cd 0.0170 0.00400 0.01700 0.03900 0.004000 0.008000 mg/kg 0.50 NA NA
11 04/29/2022 Fish Cr 0.8950 0.18400 0.39200 0.78400 0.081000 0.161000 mg/kg 0.88 NA NA
12 04/29/2022 Fish Cu 7.9700 1.64000 0.23100 0.46200 0.048000 0.095000 mg/kg 300.00 NA NA
13 04/29/2022 Fish Hg 0.1900 0.03900 0.00308 0.00771 0.000634 0.001590 mg/kg 0.10 NA NA
14 04/29/2022 Fish Pb 1.3900 0.28700 0.03900 0.11800 0.008000 0.024000 mg/kg 50.00 NA NA
Inputs:
limits = structure(list(Analyte = c("As", "Cd", "Cr", "Cu", "Hg", "Pb"
), Fish_Limit = c(30, 0.5, 0.88, 300, 0.1, 50), Floc_PEC = c(33,
4.98, 111, 149, 1.06, 128), Floc_TEC = c(9.79, 0.99, 43.4, 31.6,
0.18, 35.8)), class = "data.frame", row.names = c(NA, -6L))
Results = structure(list(Sample_Date = c("04/27/2022", "04/27/2022", "04/27/2022",
"04/27/2022", "04/27/2022", "04/27/2022", "04/27/2022", "04/29/2022",
"04/29/2022", "04/29/2022", "04/29/2022", "04/29/2022", "04/29/2022",
"04/29/2022"), Matrix = c("Floc", "Floc", "Floc", "Floc", "Floc",
"Floc", "Floc", "Fish", "Fish", "Fish", "Fish", "Fish", "Fish",
"Fish"), Analyte = c("%TS", "As", "Cd", "Cr", "Cu", "Pb", "Hg",
"%TS", "As", "Cd", "Cr", "Cu", "Hg", "Pb"), ResultDry = c(5.68,
1.9, 0.317, 10.5, 4.2, 3.53, 0.0269, 20.57, 0.162, 0.017, 0.895,
7.97, 0.19, 1.39), ResultWet = c(5.68, 0.108, 0.018, 0.595, 0.24,
0.2, 0.00153, 20.57, 0.033, 0.004, 0.184, 1.64, 0.039, 0.287),
MDLDry = c(0.13, 0.713, 0.317, 7.13, 4.2, 0.713, 0.00563,
0.33, 0.039, 0.017, 0.392, 0.231, 0.00308, 0.039), MRLDry = c(0.42,
1.66, 0.713, 14.3, 8.4, 2.14, 0.0141, 1.09, 0.092, 0.039,
0.784, 0.462, 0.00771, 0.118), MDLWet = c(0.13, 0.04, 0.018,
0.405, 0.24, 0.04, 0.000319, 0.33, 0.008, 0.004, 0.081, 0.048,
0.000634, 0.008), MRLWet = c(0.42, 0.094, 0.04, 0.809, 0.48,
0.121, 0.000799, 1.09, 0.019, 0.008, 0.161, 0.095, 0.00159,
0.024), Units = c("%", "mg/kg", "mg/kg", "mg/kg", "mg/kg",
"mg/kg", "mg/kg", "%", "mg/kg", "mg/kg", "mg/kg", "mg/kg",
"mg/kg", "mg/kg")), row.names = c(NA, -14L), class = "data.frame")