Data classification in buckets

Question

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 1

You could do that by:

require(tidyverse) # dplyr would be enough...
dat %>% 
  mutate(Invoice.Date = as.Date(Invoice.Date, "%d/%m/%Y")) %>% 
  group_by(Model, Garage, Components) %>% 
  summarise(Unit.Price = first(Unit.Price, order_by = Invoice.Date)) %>% 
  spread(Garage, Unit.Price, sep = "")

Which gives you:

    Model Components GarageA GarageB
*   <chr>      <chr>   <int>   <int>
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

Now i am not sure how to interpret the broken into cars in your question. You could pipe (%>%) the above into

• split(.$Model) to get a list where each list-element represents one Model.
• nest(-Model) to get a nested tibble...