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$
  • 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.

| improve this answer | |
$\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
| improve this answer | |
$\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.