0
$\begingroup$

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
$\endgroup$

closed as off-topic by Stephen Rauch, Kiritee Gak, Sean Owen Nov 21 '17 at 20:40

  • This question does not appear to be about data science, within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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$ – Kiritee Gak Nov 21 '17 at 16:37
1
$\begingroup$

This would do the job:

#install packages
library(qdapTools)
library(dplyr)
#set working directory
setwd("C:/Users")

#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 %>%
                group_by(Vehicle) 

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.

$\endgroup$
1
$\begingroup$

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",
  "Profit"
), class = "data.frame", row.names = c(NA, -6L))


library(tidyr)
library(dplyr)

# 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

Result:

# 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
$\endgroup$
  • $\begingroup$ got an error at Error in typeof(x) : object 'Registration' not found, have a look $\endgroup$ – Toros91 Nov 21 '17 at 9:52
  • $\begingroup$ Works for me. A text conversion/encoding issue? Don't know, won't investigate. $\endgroup$ – knb Nov 21 '17 at 9:55
  • $\begingroup$ cool! if it works for you good! $\endgroup$ – Toros91 Nov 21 '17 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 '17 at 23:17
  • $\begingroup$ Now it went fine. @knb, Doing a little edit on your answer! $\endgroup$ – Toros91 Nov 23 '17 at 8:40

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