I am new to R programming and data analytics. This is probably a trivial question but I could not find an answer. I'm also limited with my ontology of data science and data manipulation. So, I'm not sure if I conveyed my question right.

I have huge irregular time series data close to 1 Million records. They are all in the form of time stamps with different discrete attributes. I want to rearrange the data as shown below.

An Example of what I am trying to achieve FROM log entry of every car sold and the nature of the sale.

Date         Vehicle        Sale Type       Registration    Profit
1-1-2017    Toyota  Camry   Cash Sale          CA           2500
1-1-2017    Toyota Rav4     Lease              CA           8500
1-1-2017    Toyota Camry    Installment        AZ           250
2-1-2017    Toyota Corolla  Installment        NV           250
4-1-2017    Toyota Etios    Cash Sale          AZ           2500
4-1-2017    Toyota Camry    Lease              CA           8500
4-1-2017    Toyota Prius    Installment        CA           250
13-1-2017   Toyota Prius    Installment        VA           250

TO with rows having car names and columns containing cash sale, lease, installment

Vehicle Type    Cash Sale   Lease   Installment
Toyota Camry       1           1           1
Toyota Rav4                    1    
Toyota Corolla                             1
Toyota Etios       1        
Toyota Prius                               2
  • 1
    $\begingroup$ I'm voting to close this question as off-topic because this does not seem to belong to Data Science as science but more of a stackoverflow community question. $\endgroup$ Nov 21, 2017 at 16:37

2 Answers 2


This would do the job:

#install packages
#set working directory

#read csv
test <- read.csv("test.csv")

#create dummy variable
#test.new <- dummy.data.frame(test$Sale.Type, sep = ".")
test.new <- cbind(test, mtabulate(test$Sale.Type))

#rename column
names(test.new)[names(test.new) == 'Cash Sale'] <- 'cashsale'

#making new columns
test.new1 <- test.new %>%

test.new2 <-  test.new1 %>%
                  mutate(sum_Cashsale = sum(cashsale)) %>%
                  mutate(sum_Ins = sum(Installment)) %>%
                  mutate(sum_Lease = sum(Lease))

#select few necessary colums
test.new3 <- subset(test.new2, select=c("Vehicle", "sum_Cashsale", "sum_Ins","sum_Lease"))

#select the distict rows
test.new4 <- dplyr::distinct(test.new3)

Just copy paste the code. saved your sample data to test.csv Give your working directory details, do let me know if you have any issues.


You can do it also this way, using the tidyverse packages tidyr and dplyr:

# read in the data

dat <- structure(list(Date = c(
  "1-1-2017", "1-1-2017", "1-1-2017", "4-1-2017",
  "4-1-2017", "4-1-2017"
  ), Vehicle = c(
  "Toyota Camry", "Toyota Rav4",
  "Toyota Camry", "Toyota Etios", "Toyota Camry", "Toyota Prius"
  ), SaleType = c(
  "Cash Sale", "Lease", "Installment", "Cash Sale",
  "Lease", "Installment"
  ), Registration = c(
  "CA", "CA", "AZ", "AZ",
  "CA", "CA"
  ), Profit = c(2500L, 8500L, 250L, 2500L, 8500L, 250L)), .Names = c(
  "Date", "Vehicle", "SaleType", "Registration",
), class = "data.frame", row.names = c(NA, -6L))


# tidyr::gather() converts to "long" key-value format
dat %>% 
            gather(k,v, -Registration, -Vehicle, -Date, -Profit) %>%   # -minus: keep as categorical variables
            group_by(Vehicle, v) %>%                                   # to add drill down info, add one of the "-" variables from gather()
            summarise(cnt=n()) %>% 
            ungroup() %>%                                              # remove grouping metadata from data frame 
            spread(key=v, value = cnt)                                 # create crosstab


# A tibble: 4 x 4
       Vehicle `Cash Sale` Installment Lease
*        <chr>       <int>       <int> <int>
1 Toyota Camry           1           1     1
2 Toyota Etios           1          NA    NA
3 Toyota Prius          NA           1    NA
4  Toyota Rav4          NA          NA     1
  • $\begingroup$ got an error at Error in typeof(x) : object 'Registration' not found, have a look $\endgroup$
    – Toros91
    Nov 21, 2017 at 9:52
  • $\begingroup$ Works for me. A text conversion/encoding issue? Don't know, won't investigate. $\endgroup$
    – knb
    Nov 21, 2017 at 9:55
  • $\begingroup$ cool! if it works for you good! $\endgroup$
    – Toros91
    Nov 21, 2017 at 9:57
  • $\begingroup$ Thank you so much. i didn't work directly with the data but was able to hack my way through it. really appreciate the help :) $\endgroup$
    – user42173
    Nov 22, 2017 at 23:17
  • $\begingroup$ Now it went fine. @knb, Doing a little edit on your answer! $\endgroup$
    – Toros91
    Nov 23, 2017 at 8:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.