CODEWITHSUNDEEP
rdataframe
elfs
elfs

Reputation: 165

R: converting list with embedded identifiers into data frame

I am brand new to R and have been learning a lot from looking through other questions here on this fine website!

but now I am dealing with a data management issue that I can't figure out from other examples, so I'm hoping that you can help.

I have a set of survey responses that I've read in from a csv file and wrangled into a vector formatted as in the following example: 

test <- c(
  "[1234],Bob Smith,",
  "Q-0,Male",
  "Q-1,18-25",
  "Q-2,Computer Science",
  ",",
  "[5678],Julie Lewis",
  "Q-0,Female",
  "Q-1,18-25",
  ",",
  ","
)

Note that "," appears on its own line because I used fill=TRUE in read.csv to deal with the fact that not all of the lines were the same length. Also note that not all questions have been answered by all respondents.

I need to turn this into a data frame of the following structure:

     ID      name         gender   age    major
1    [1234]  Bob Smith    Male     18-25  Computer Science
2    [5678]  Julie Lewis  Female   18-25  NA
   ...

It seems that I can't read the vector into a matrix or data frame by rows because of the fact that not all questions have been answered by all respondents. Any advice on how to deal with this?

Upvotes: 1

Views: 184

Answers (2)

Andrie
Andrie

Reputation: 179438

You will probably save yourself a lot of trouble to read the csv file in the correct format in the first place. read.csv is a powerful function that should be able to cope with your data , and this munging shouldn't be necessary.

However, here goes:

x <- matrix(test, byrow=TRUE, ncol=5)
x <- x <- sub("Q-\\w+,", "", x)
x[x==","] <- NA
x <- cbind(matrix(unlist(strsplit(x[, 1], ",")), byrow=TRUE, ncol=2), x[, -1])
x <- as.data.frame(x, stringsAsFactors=FALSE)
names(x) <- c("ID", "Name", "Gender", "Age", "Major", "V1")

This results in:

x

      ID        Name Gender   Age            Major   V1
1 [1234]   Bob Smith   Male 18-25 Computer Science <NA>
2 [5678] Julie Lewis Female 18-25             <NA> <NA>

Upvotes: 2

thelatemail
thelatemail

Reputation: 93833

This is a bit clunky, but it works.

Here's the data:

test <- c(
"[1234],Bob Smith,",
"Q-0,Male",
"Q-1,18-25",
"Q-2,Computer Science",
",",
"[5678],Julie Lewis",
"Q-0,Female",
"Q-1,18-25",
",",
"[1234],Bob Smith,",
"Q-1,18-25",
"Q-2,Computer Science",
","
)

Here's the manipulation code:

#remove rows with just a comma
test <- test[test!=","]
#find id cases and remove the commas between the id and the name
#and add an id label
idcases <- grep("\\[.*\\]",test)
test[idcases] <- paste("id,",gsub(",","",test[idcases]),sep="")
#find id values positions and end position
idvals <- c(idcases,length(test)+1)
#generate a sequence identifier for each respondent
setid <- rep(1:(length(idvals)-1),diff(idvals))
#put the set id against each value
result1 <- paste(setid,test,sep=",")
#split the strings up and make them a data.frame
result2 <- data.frame(do.call(rbind,strsplit(result1,",")))
#get the final dataset with a reshape
final <- reshape(result2,idvar="X1",timevar="X2",direction="wide")[,-1]
#clean up the names etc
names(final) <- c("name","gender","age","major")
final$id <-  gsub("(\\[.*\\])(.*)","\\1",final$name)
final$name <- gsub("(\\[.*\\])(.*)","\\2",final$name)

Which gives:

> final
         name gender   age            major     id
1   Bob Smith   Male 18-25 Computer Science [1234]
5 Julie Lewis Female 18-25             <NA> [5678]
8   Bob Smith   <NA> 18-25 Computer Science [1234]

Upvotes: 0

Related Questions