R:在添加的汇总列中得到错误的结果

Question

R:如何绘制rpivotTable或dcast表，其中的汇总列与excel相同，用于报告。

检查数据集，尝试了不同的方法在dcast和rpivottable中添加摘要列，但没有得到它。

检查下面的代码，我在汇总栏中得到了不正确的总值。

示例数据集类似。

Buyer       year_month(order)       
A           2016-01         
B           2016-01         
C           2016-02         
A           2016-04                 
A           2016-01     
A           2017-01         
B           2017-01         
C           2017-02         
A           2017-04 
A           2017-05         
B           2017-05         
C           2017-06         
A           2017-08     
B           2018-01         
C           2018-02         
A           2018-04         
A           2018-03         
B           2018-03         
C           2018-05         
A           2018-06         
B           2018-07         
A           2018-11         
B           2018-11         
A           2019-01         
B           2019-01         
A           2019-01         
A           2019-02         

我使用了代码，但得到了不正确的数据。

library( data.table )
library( janitor )
#set data to data.table format
data.table::setDT(sub_data7)
sub_data7[, c("year", "month") := data.table::tstrsplit( year_month, "-" ) ][]
l <- lapply( unique(sub_data7$year),
function(x) {
temp <- sub_data7[ year == x, ]
ans <- data.table::dcast(sub_data7, Buyer ~ year_month, fill = 0 )
ans <- janitor::adorn_totals(ans, where = c("col"), name = paste0( x, "_Total"))
} )
#bind together
ans <- data.table::rbindlist( l, use.names = TRUE, fill = TRUE )
#melt to ling
ans <- data.table::melt( ans, id.vars = "Buyer" )
ans <- ans[, sum(value, na.rm = TRUE), by = .(Buyer, variable)]
#get the order right
colorder = c("Buyer", sort( names(final)[!names(final) == "Buyer"] ) )

datatable(janitor::adorn_totals( final[, ..colorder ], where = "row" ))

但是在total列中获取了不正确的值。

Answer 1

您需要使用以下代码。

library( data.table )
library( janitor )
#add new column as countdate, or elese we've got nothing to sum..
sub_data7 <-mutate(sub_data7,Countdate = ifelse(is.na(sub_data7$year_month),0,1))
#set data to data.table format
data.table::setDT(sub_data7)
#get yearly summarise, with totals
l <- lapply( unique(sub_data7$year),
function(x) {
temp <- sub_data7[ year == x, ]
ans <- data.table::dcast(temp[, .(sum(Countdate)), by = .(Buyer, year,Month) ],Buyer ~ year+Month, fill=0)
ans <- janitor::adorn_totals(ans, where = c("col"), name = paste0( x, "_Total"))
} )
.
.
. # code same it is.
.
#reorder, and get totals by column
datatable(janitor::adorn_totals( final[, ..colorder ], where = "row" ),options = list(pageLength = 25))