Taking variable names out of column and creating new columns in R

Question

I am trying to take a dataframe like this

    name        response
 1   Phil        Exam
 2   Terry       Test
 3   Simmon      Exam
 4   Brad        Quiz

And turn it into this

    name        response    Exam    Test   Quiz
   1 Phil        Exam        Exam  
   2 Terry       Test                Test
   3 Simmon      Exam        Exam
   4 Brad        Quiz                       Quiz

I tried to use a for loop, extracting each row. Then I would check to see if the column already existed and if it did not it would create a new column. I couldnt get this close to working and am unsure how to do this.

Essentially a model matrix - cbind(dat, model.matrix(~ response + 0, data=dat)) — thelatemail
– thelatemail, Commented Sep 27, 2017 at 3:28

jdobres · Accepted Answer · 2017-09-27 01:36:01Z

2

This can be accomplished a few ways. Might be a good opportunity to get to know the tidyverse:

library(tidyverse)
new.df <- spread(old.df, response, response)

This is an unusual use of tidyr::spread(). In this case, it constructs new column names from the values in "response", and also fills those columns with the values in "response". The fill argument can be used to change what goes in the resulting blank cells.

answered Sep 27, 2017 at 1:36

jdobres

12k1 gold badge20 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Richard Lusch Over a year ago

I was on my way to posting the same. Glad I refreshed.

www Over a year ago

@rss1080 If this works, please accept this post as the answer.

rss1080 Over a year ago

Would you happen to have a method that can merge spread variables? For example if I run spread again on a different column, and that different column also contains Exam, Exam will then be sorted into the previously made Exam column?

www · Accepted Answer · 2017-09-27 01:49:10Z

A base R solution. We can create a function to replace words that do not match to the target word, and then create the new column to the data frame.

# Create example data frame
dt <- read.table(text = "    name        response
 1   Phil        Exam
 2   Terry       Test
 3   Simmon      Exam
 4   Brad        Quiz", 
                 header = TRUE, stringsAsFactors = FALSE)

# A function to create a new column based on the word in response
create_Col <- function(word, df, fill = NA){
  new <- df$response
  new[!new == word] <- fill
  return(new)
} 

# Apply this function
for (i in unique(dt$response)){
  dt[[i]] <- create_Col(word = i, df = dt)
}

dt
    name response Exam Test Quiz
1   Phil     Exam Exam <NA> <NA>
2  Terry     Test <NA> Test <NA>
3 Simmon     Exam Exam <NA> <NA>
4   Brad     Quiz <NA> <NA> Quiz

akrun · Accepted Answer · 2017-09-27 03:48:41Z

0

We can use dcast

library(data.table)
dcast(setDT(df1), name + response ~ response, value.var = 'response', fill = "")
#     name response Exam Quiz Test
#1:   Brad     Quiz      Quiz     
#2:   Phil     Exam Exam          
#3: Simmon     Exam Exam          
#4:  Terry     Test           Test

answered Sep 27, 2017 at 3:48

akrun

891k38 gold badges590 silver badges700 bronze badges

Collectives™ on Stack Overflow

Taking variable names out of column and creating new columns in R

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related