long to wide in R removing NA's

Question

I have a sample data which looks like

Dput:

structure(list(variable = structure(c(1L, 2L, 3L, 1L, 2L, 3L, 
1L, 2L, 3L), .Label = c("firstname", "lastname", "title"), class = "factor"), 
    value = structure(c(6L, 2L, 5L, 1L, 3L, 5L, 7L, 8L, 4L), .Label = c("adam", 
    "dingler", "jhon", "miss", "mr", "naji", "stephanie", "williams"
    ), class = "factor")), .Names = c("variable", "value"), class = "data.frame", row.names = c(NA, 
-9L))

I want to transform this to wide format so that it looks like :

I tried

library(tidyr) final_data <- spread(sample, key = variable, value = value) but i am getting output not in the desired format, i am getting output in this format:

I need help how can get rid of the NA'S and restructure the output in desired format.

Hey @rawr i never thought of this, thanks for this intuitive solution. — PSraj, Mar 13 '16 at 16:03

score 2 · Accepted Answer · answered Mar 13 '16 at 12:54

We need to create a sequence variable

library(dplyr)
library(tidyr)
sample %>%
     group_by(variable) %>% 
     mutate(n = row_number()) %>%
     spread(variable, value) %>%
     select(-n)
#    firstname lastname  title
#      (fctr)   (fctr) (fctr)
#1      naji  dingler     mr
#2      adam     jhon     mr
#3 stephanie williams   miss

score 0 · Answer 2 · answered Mar 13 '16 at 13:00

You could do the following:

data <- structure(list(variable = structure(c(1L, 2L, 3L, 1L, 2L, 3L, 
                                        1L, 2L, 3L), .Label = c("firstname", "lastname", "title"), class = "factor"), 
                 value = structure(c(6L, 2L, 5L, 1L, 3L, 5L, 7L, 8L, 4L), .Label = c("adam", 
                                                                                     "dingler", "jhon", "miss", "mr", "naji", "stephanie", "williams"
                 ), class = "factor")), .Names = c("variable", "value"), class = "data.frame", row.names = c(NA, 


firstname <- data$value[which(data$variable == "firstname")]
lastname <- data$value[which(data$variable == "lastname")]
title <- data$value[which(data$variable == "title")]

data_new <- data.frame(firstname, lastname, title)
data_new

long to wide in R removing NA's

2 Answers2