我正试图根据值的矩阵和分析物，附加一个表，并对另一个表的值进行限制。
表一的列包括矩阵(Fish或Floc)、分析物。
表二表一中每个分析物都有行，3列，一列用于Fish矩阵，两列用于Floc矩阵。
我正试图找出如何将表二绑定到表格一，以便分析物和矩阵的每个特定组合都将表二的相应极限值附加为一个新列，其中不符合读取NA条件的行。
limit_table看起来像这样：
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
Results表基本上如下所示：
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
我希望新的表格看起来像这样，3个新列的值对应于矩阵和分析物，否则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
编辑：
我重新格式化了limit_table，使行名成为一个名为Analyte的新列，并将该表合并到第一个表中，Analyte作为by=运算符：
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)
但是，我不可能只将Fish_Limit列添加到包含Fish矩阵值的行中，并将Floc_PEC和Floc_TEC列添加到具有FlocMatrix的行中。有没有办法包括额外的步骤？
以下是到目前为止新表格的一部分：
> 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
如果您的limits对象是data.frame(请参阅下面的输入)，则可以使用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)
)
)
输出：
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
输入：
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")