CODEWITHSUNDEEP
rexcel
Chum
Chum

Reputation: 25

Excel pivot count to R code

I'm working on below data and would like to replicate the Pivot I did to R. I was able to do the sum and max but no idea how to do the count. Appreciate your help

Transaction Location    Invoice Paying_Party    Charge Delivery Paid    Points
123 UK  123_A   UK001   1   1   100
123 UK  123_B   UK001   1   0   100
123 UK  123_C   UK001   1   1   100
124 UK  124_A   UK001   1   1   100
124 UK  124_B   UK001   0   0   50
125 SE  125_A   SE005   0   0   50
126 HK  126_A   HK008   1   1   50
126 HK  126_B   HK008   1   1   50
127 DE  127_A   DE008   1   1   100
128 CN  128_A   CN002   0   1   150
128 CN  128_B   CN002   0   1   150
128 CN  128_C   CN002   0   0   150
128 CN  128_D   CN002   0   0   150
129 US  129_A   US007   1   1   100
129 US  129_B   US007   1   0   100
130 AU  130_A   AU111   1   0   150
130 AU  130_B   AU111   1   0   150
130 AU  130_C   AU111   1   1   150

Here is my code:

library(data.table)
Summary<-setDT(Report)[, list(Points=max(Points),Charge.Delivery=max(Charge.Delivery),Paid=sum (Paid)),by=list(Transaction,Paying_Party,Location=as.factor(Location))]

Thanks

Upvotes: 0

Views: 49

Answers (1)

Sun Bee
Sun Bee

Reputation: 1820

Use Count = .N like so:

Summary<-DT[, list(Points = max(Points), Charge = max(Charge), Paid = sum (Paid), Count = .N), by=list(Transaction,Paying_Party,Location=as.factor(Location))]

I have DT as the data.table and Charge in place of Charge.Delivery.

Upvotes: 2

Related Questions