r - R中的百分比分组与Excel不同

Question

我正在尝试从 R 中的总金额计算每个帐户的金额百分比。但是当我与 excel 进行比较时，我没有得到正确的值。我相信我的逻辑中有一些缺失。

> dput(test)
structure(list(Account_new = c("32001932", "31011217", "31011217", 
"22006428", "22005443", "22005443", "31011217", "32002608", "22001044", 
"22006428", "31004020", "32002608", "32002608", "32002692", "31001730", 
"31011597", "32002387", "32000717", "32002788", "32002789", "22001054", 
"31002915", "22002000", "31010427", "31007795", "23001237", "32002777", 
"22000284", "31004020", "32002062", "22000400", "31008884", "32002432", 
"31010427", "32002608", "32002608", "32002742", "22001061", "31005764", 
"31007410", "31011634", "31007335", "32000741", "32000741", "32000741", 
"32001742", "32002692", "32002692", "31001725", "31001730", "32001462", 
"32001462", "31011597", "32000756", "32000756", "32000756", "32000756", 
"32000756", "32000756", "32000756", "32000756", "32000756", "32000246", 
"32000256", "31008560", "32000706", "32000706", "32000706", "32000706", 
"32002777", "32000756", "22001054", "31002915", "31002915", "22006433", 
"22001061", "22006293", "32002707", "32002237", "32002608", "32002608", 
"22005443", "31010427", "22002000", "22001061", "31002915", "22006428", 
"22006428", "32002608", "32002097", "32002097", "32002097", "32002097", 
"32002097", "32002432", "22006293", "23000062", "32002782", "22005171", 
"22001061"), New_amt_loc_curr = c(393.82, 12558.49, 1738.87, 
22200.19, 101.19, 142.74, 68262.44, 5357.97, 382048.48, 56877.06, 
433.71, 43696.82, 14350.07, 369.97, 199723.81, 549.2, 52893.4, 
1617.03, 2093.81, 7607.58, 102152.99, 88716.88, 339.85, 51401.16, 
373.28, 14166.36, 77.22, 2478.74, 1110.22, 2520.91, 7736.87, 
501.7, 139.97, 16555.63, 25805.93, 10620.37, 34992.76, 6267.67, 
225.22, 228.62, 155580.49, 13749.64, 610.14, 238.31, 41.47, 1041.44, 
78.78, 994.85, 119598.63, 310084.4, 1263.4, 536.49, 3343.21, 
18.16, 913.63, 315.72, 5449.66, 2999.34, 121.54, 929, 403.73, 
44.71, 48085.22, 1911.95, 104957.41, 15732.01, 4356.91, 17642.22, 
3060.26, 6042.52, 22.69, 119372.69, 92081.72, 6213.82, 7458.01, 
6547.72, 10092.65, 560.75, 2402.59, 22804.72, 62681.66, 105.41, 
25664.79, 14953.4, 2145.61, 98682.25, 61021.51, 7.48, 822.44, 
67544.51, 24179.65, 23431.98, 46609.75, 67544.51, 612.36, 4528.94, 
25492.2, 6017.25, 15312.28, 1610.53)), row.names = c(NA, -100L
), class = c("tbl_df", "tbl", "data.frame"))

我用于百分比的代码是：

summary<-sqldf("select Account_new,New_amt_loc_curr/sum(New_amt_loc_curr) as amnt_avg from test group by Account_new")

输出：

excel中的输出是：

标签： rsqldf

Answer 1

现有的 SQL 代码正在平均每个Account_new : value/sum(values per group)，相反，我们需要的是sum(values per group)/sum(all values)

这是使用sqldf更正的 SQL 代码：

library(sqldf)

sqldf("SELECT Account_new,
              sum(New_amt_loc_curr) / (
                                      SELECT sum(New_amt_loc_curr)
                                      FROM test
                                      ) * 100 as amnt_avg
       FROM test
       GROUP BY Account_new")

#    Account_new     amnt_avg
# 1     22000284  0.086184065
# 2     22000400  0.269005588
# 3     22001044 13.283559869
# 4     22001054  7.702293784
# 5     22001061  0.576180570
# 6     22002000  0.531735663
# 7     22005171  0.532397323
# 8     22005443  0.012146309
# 9     22006293  0.508382512
# 10    22006428  4.871396497