R: Getting incorrect result in added summarised column

Question

R: How to plot rpivotTable or dcast table with summarised column in between same as excel for reporting.

Check data set, tried different way to add summarised column in dcast and rpivottable but not getting it.

Check below code where i getting incorrect total value in summarised column.

Sample Data set be like.

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

Code i used but getting incorrect data.

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" ))

But Getting incorrect value in total column.

I'd like to help you, but don't quite get your problem. Can you explain in detail what you want to achieve and what's the current problem? It would help, if you added the desired output, i.e. a snippet of the resulting data table with correct numbers and totals. — HeiN3r, Mar 17 '20 at 08:06

score 0 · Accepted Answer · answered Mar 17 '20 at 08:03

you need to use below code.

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))

R: Getting incorrect result in added summarised column

1 Answers1