Converting fields of a column into separate columns

Question

The data I have is in the below format:-

I would like to group by name and have each of the keys as a column with values as fields in it.

After which it would look like

The code to create these dataframes :-

df <- data.frame(company_id = c(12,12,12, 12, 12), 
             name1 = c("John", "Jack", "Joey", "Jack", "Joey"), 
             key = c("Phone_no", "address", "id" , "Phone_no", "address"), 
             value = c("32453", "street a", "007" , "78632", "street x"))

    expecteddf <- data.frame(company_id = c(12, 12,12 ), 
                     name1 = c("John", "Jack", "Joey"), 
                     Phone_no = c("32453", "78632",  "-"), 
                     address = c("-", "street a", "street x"),
                     id = c("-", "-", "007"))

score 2 · Accepted Answer · answered Aug 11 '16 at 20:06

2

This is one line with tidyr:

 require(tidyr)
 df = spread(df, key = key, value = value)

"key" and "value" are arguments in spread.

answered Aug 11 '16 at 20:06

Willie D

145
1
10

score 1 · Answer 2 · answered Aug 11 '16 at 20:15

Reshape2 package in R should help you.

library(reshape2) #load the package
newdf <- dcast(df, company_id + name1  ~key) 

newdf[is.na(newdf)] <- '-' #replacing NAs with -

#sorting name1 and columns in the desired o/p format

newdf <- newdf[order(newdf$name1),c('company_id','name1','Phone_no','address','id')]

Converting fields of a column into separate columns

2 Answers2