如何从 R 中的 sqldf 输出中获取列的总和

Question

我想对 R 中 sqldf 函数输出的单列数据求和。

我有一个 csv. 文件，其中包含具有唯一 ID 的站点分组及其关联区域。例如：

occurrenceID                              sarea
{0255531B-904F-4E2D-B81D-797A21165A2F}  0.30626786
{0255531B-904F-4E2D-B81D-797A21165A2F}  0.49235953
{0255531B-904F-4E2D-B81D-797A21165A2F}  0.03490536
{0255531B-904F-4E2D-B81D-797A21165A2F}  0.00001389
{175A4B1C-CA8C-49F6-9CD6-CED9187579DC}  0.0302389
{175A4B1C-CA8C-49F6-9CD6-CED9187579DC}  0.01360811
{1EC60400-0AD0-4DB5-B815-221C4123AE7F}  0.08412911
{1EC60400-0AD0-4DB5-B815-221C4123AE7F}  0.01852466

我在 R 中使用下面的代码从每组唯一 ID 中提取出最大的区域。

> MyData <- read.csv(file="sacandaga2.csv", header=TRUE, sep=",")
> sqldf("select max(sarea),occurrenceID from MyData group by occurrenceID")

这将产生以下输出：

 max(sarea)                           occurrenceID
1  0.49235953 {0255531B-904F-4E2D-B81D-797A21165A2F}
2  0.03023890 {175A4B1C-CA8C-49F6-9CD6-CED9187579DC}
3  0.08412911 {1EC60400-0AD0-4DB5-B815-221C4123AE7F}
4  0.00548259 {2412E244-2E9A-4477-ACC6-1EB02503BE75}
5  0.00295924 {40450574-ABEB-48E3-9BE5-09B5AB65B465}
6  0.01403846 {473FB631-D398-46B7-8E85-E63540BDFF92}
7  0.00257519 {4BABDE22-E8E0-435E-B60D-0BB9A84E1489}
8  0.02158115 {5F616A33-B028-46B1-AD92-89EAC1660C41}
9  0.00191211 {70067496-25B6-4337-8C70-782143909EF9}
10 0.03049355 {7F858EBB-132E-483F-BA36-80CE889373F5}
11 0.03947298 {9A579565-57EC-4E46-95ED-79724FA6F2AB}
12 0.02464722 {A9010BA3-0FE1-40B1-96A7-21122261A003}
13 0.00136672 {AAD710BF-1539-4235-87F1-34B66CF90781}
14 0.01139146 {AB1286C3-DBE3-467B-99E1-AEEF88A1B5B2}
15 0.07954269 {BED0433A-7167-4184-A25F-B9DBD358AFFB}
16 0.08401067 {C4EF0F45-5BF7-4F7C-BED8-D6B2DB718CB2}
17 0.04289261 {C58AC2C6-BDBE-4FE5-BD51-D70BBDFB4DB5}
18 0.03151558 {D4230F9C-80E4-454A-9D5D-0E373C6DCD9A}
19 0.00403585 {DD76A03A-CFBF-41E9-A571-03DA707BEBDA}
20 0.00007336 {E20DE254-8A0F-40BE-90D2-D6B71880E2A8}
21 9.81847859 {F382D5A6-F385-426B-A543-F5DE13F94564}
22 0.00815881 {F9032905-074A-468F-B60E-26371CF480BB}
23 0.24717113 {F9E5DC3C-4602-4C80-B00B-2AF1D605A265}

现在我想对最大(面积(列中的所有值求和。实现这一目标的最佳方法是什么？

Answer 1

要么在 sqldf 或 R 中执行此操作，要么分配现有结果并在 R 中执行此操作：

# assign your original 
grouped_sum = sqldf("select max(sarea),occurrenceID from MyData group by occurrenceID")
# and sum in R
sum(grouped_sum$`max(sarea)`)
# you might prefer to use a standard column name so you don't need backticks
grouped_sum = sqldf(
  "select max(sarea) as max_sarea, occurrenceID
   from MyData 
   group by occurrenceID"
)
sum(grouped_sum$max_sarea)

Answer 2

如果打算在单个"sqldf"调用中执行此操作，请使用with

library(sqldf)
sqldf("with tmpdat AS (
    select max(sarea) as mxarea, occurrenceID 
     from MyData group by occurrenceID
    ) select sum(mxarea) 
         as smxarea from tmpdat")
#   smxarea
#1 0.6067275

数据

MyData <- 
structure(list(occurrenceID = c("{0255531B-904F-4E2D-B81D-797A21165A2F}", 
"{0255531B-904F-4E2D-B81D-797A21165A2F}", "{0255531B-904F-4E2D-B81D-797A21165A2F}", 
"{0255531B-904F-4E2D-B81D-797A21165A2F}", "{175A4B1C-CA8C-49F6-9CD6-CED9187579DC}", 
"{175A4B1C-CA8C-49F6-9CD6-CED9187579DC}", "{1EC60400-0AD0-4DB5-B815-221C4123AE7F}", 
"{1EC60400-0AD0-4DB5-B815-221C4123AE7F}"), sarea = c(0.30626786, 
0.49235953, 0.03490536, 1.389e-05, 0.0302389, 0.01360811, 0.08412911, 
0.01852466)), class = "data.frame", row.names = c(NA, -8L))

Answer 3

您可以通过获取最大值的总和来做到这一点：

sqldf("select sum(max_sarea) as sum_of_max_sarea 
          from (select max(sarea) as max_sarea,
          occurrenceID from Mydata group by occurrenceID)")

#   sum_of_max_sarea
# 1        0.6067275

数据：

Mydata <- structure(list(occurrenceID = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 3L, 3L), 
.Label = c("0255531B-904F-4E2D-B81D-797A21165A2F", "175A4B1C-CA8C-49F6-9CD6-CED9187579DC", 
           "1EC60400-0AD0-4DB5-B815-221C4123AE7F"), class = "factor"), 
sarea = c(0.30626786, 0.49235953, 0.03490536, 1.389e-05, 0.0302389, 
          0.01360811, 0.08412911, 0.01852466)), class = "data.frame", 
row.names = c(NA, -8L))

Answer 4

如果DF是问题中显示的最后一个数据框，则对数字列求和：

sqldf("select sum([max(sarea)]) as sum from DF")
##        sum
## 1 11.07853

注意

我们假设此数据框以可重现的形式显示：

Lines <- "max(sarea)                           occurrenceID
1  0.49235953 {0255531B-904F-4E2D-B81D-797A21165A2F}
2  0.03023890 {175A4B1C-CA8C-49F6-9CD6-CED9187579DC}
3  0.08412911 {1EC60400-0AD0-4DB5-B815-221C4123AE7F}
4  0.00548259 {2412E244-2E9A-4477-ACC6-1EB02503BE75}
5  0.00295924 {40450574-ABEB-48E3-9BE5-09B5AB65B465}
6  0.01403846 {473FB631-D398-46B7-8E85-E63540BDFF92}
7  0.00257519 {4BABDE22-E8E0-435E-B60D-0BB9A84E1489}
8  0.02158115 {5F616A33-B028-46B1-AD92-89EAC1660C41}
9  0.00191211 {70067496-25B6-4337-8C70-782143909EF9}
10 0.03049355 {7F858EBB-132E-483F-BA36-80CE889373F5}
11 0.03947298 {9A579565-57EC-4E46-95ED-79724FA6F2AB}
12 0.02464722 {A9010BA3-0FE1-40B1-96A7-21122261A003}
13 0.00136672 {AAD710BF-1539-4235-87F1-34B66CF90781}
14 0.01139146 {AB1286C3-DBE3-467B-99E1-AEEF88A1B5B2}
15 0.07954269 {BED0433A-7167-4184-A25F-B9DBD358AFFB}
16 0.08401067 {C4EF0F45-5BF7-4F7C-BED8-D6B2DB718CB2}
17 0.04289261 {C58AC2C6-BDBE-4FE5-BD51-D70BBDFB4DB5}
18 0.03151558 {D4230F9C-80E4-454A-9D5D-0E373C6DCD9A}
19 0.00403585 {DD76A03A-CFBF-41E9-A571-03DA707BEBDA}
20 0.00007336 {E20DE254-8A0F-40BE-90D2-D6B71880E2A8}
21 9.81847859 {F382D5A6-F385-426B-A543-F5DE13F94564}
22 0.00815881 {F9032905-074A-468F-B60E-26371CF480BB}
23 0.24717113 {F9E5DC3C-4602-4C80-B00B-2AF1D605A265}"
DF <- read.table(text = Lines, check.names = FALSE)