Ashish Ashish - 4 years ago 152
R Question

Data classification in buckets

I have a data frame named as Data which has the following elements:

Model Garage City Unit.Price Invoice.Date Components
Hyundai A NY 500 31/12/2016 HL
Honda B NJ 700 31/12/2016 TL
Porsche A NY 800 30/12/2016 TL
BMW B NJ 800 30/12/2016 HL
BMW A NJ 700 31/12/2016 HL
Porsche B NY 800 30/12/2016 TL
Honda A NY 400 30/12/2016 TL
Honda A NY 500 30/12/2016 HL
Honda B NY 600 30/12/2016 HL
Honda A NY 200 29/12/2016 TL
Honda A NY 300 29/12/2016 HL


I want the output of Data broken into cars sorted with
Invoice.Date
so that the current cost is captured first.

Ex:Honda

Components GarageA GarageB
HL 500 600
TL 400 700


This is how I started:

Category <- as.data.frame(c("BMW","Honda","Porsche","Hyundai"))

for(i in 1:nrow(Category))
{
m <- Category[i,1]
X <- subset(Data,Model==m)
X <- Data[order(Data$Invoice.Date,decreasing = T),]
Pivot_A<-dcast(X,Name~Garage,value.var = "Unit.Price",function(x) length((x)))
write.csv(Pivot,file = paste(X,"Cars.csv",sep = "_"))
}


The only issue I am getting is to map the correct unit price. Is there any code or function to do this with
dcast
?
dcast
has options of
sum
,
count
. What If I want the exact amount and not
sum
,
average
.

Answer Source

We can do this with dcast from data.table. Convert the 'data.frame' to 'data.table' (setDT(df1)), order the rows by the 'Invoice.Date' and dcast from 'long' to 'wide' with dcast while specifying the fun.aggregate to select only the first observation

library(data.table)
library(lubridate)
dcast(setDT(df1)[order(dmy(Invoice.Date))] , Model + Components ~ 
  paste0("Garage", Garage), value.var = "Unit.Price", function(x) x[1])
#     Model Components GarageA GarageB
#1:     BMW         HL     700     800
#2:   Honda         HL     300     600
#3:   Honda         TL     200     700
#4: Hyundai         HL     500      NA
#5: Porsche         TL     800     800
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download