Organzing/cleaning data frame

Question

I have the following data that I need to manage:

data <- data.frame(Name=c("11C","11C","11C","11C","11C","20D","20D"),
              PID=c("AD15E","AD15E","AD15E","AA05D","AA05D","Z48J","Z48J"),
              Type=c("Home","Auto","Auto","Home","Auto","Auto","Home"),
              Brand=c("A","B","C","H","I","P","D"),
              Model=c("A152","K235","W54","H2","A57","Z23","Y0878"))

By unique Name and PID, I want to convert the data from rows into columns. PID "AA05D" has two Type "Auto" so I'd like the second row to be converted into it's own columns.

I'm not sure what I can use to accomplish this.

I'm looking for the cleaned data to look like the following:

result <- data.frame(Name=c("11C","11C","20D"),
               PID=c("AD15E","AA05D","Z48J"),
               Home.Brand=c("A","H","D"),
               Home.Model=c("A152","H2","Y0878"),
               Auto1.Brand=c("B","I","P"),
               Auto1.Model=c("K235","A57","Z23"),
               Auto2.Brand=c("C","",""),
               Auto2.Model=c("W54","",""))

score 1 · Answer 1 · answered May 03 '17 at 18:53

How about using data.table's dcast?

library(data.table)

data$count <- ave(1:nrow(data), data$Name, data$PID, data$Type, FUN = function(x) 1:length(x))
dcast(setDT(data), Name + PID ~ Type + count, value.var = c("Brand", "Model"))
#   Name   PID Brand_Auto_1 Brand_Auto_2 Brand_Home_1 Model_Auto_1 Model_Auto_2 Model_Home_1
#1:  11C AA05D            I           NA            H          A57           NA           H2
#2:  11C AD15E            B            C            A         K235          W54         A152
#3:  20D  Z48J            P           NA            D          Z23           NA        Y0878

Organzing/cleaning data frame

1 Answers1