How to reshape data frame in R or Excel?

Question

Here's the code to get a sample data set:

set.seed(0)
practice <- matrix(sample(1:100, 20), ncol = 2)
data <- as.data.frame(practice)
data <- cbind( lob = sprintf("objective%d", rep(1:2,each=5)), data)
data <- cbind( student = sprintf("student%d", rep(1:5,2)), data)
names(data) <- c("student", "learning objective","attempt", "score")
data[-8,]

The data looks like this:

    student learning objective attempt score
1  student1         objective1      90     6
2  student2         objective1      27    19
3  student3         objective1      37    16
4  student4         objective1      56    60
5  student5         objective1      88    34
6  student1         objective2      20    66
7  student2         objective2      85    42
9  student4         objective2      61    82
10 student5         objective2      58    31

What I want is:

    student       objective1         objective2 
                 attempt  score     attempt score
1  student1         90     6          20      66
2  student2         27    19          85      42
3  student3         ...                0       0
4  student4         ...                  ...
5  student5         ...                  ...

There are 70 learning objectives, so it's going to be tedious to just copy and paste the attempts and scores, so I wonder whether there's a better way to clean the data.

R: I was trying to use the melt function in R to get the new data, but it doesn't work well. There are missing scores for some of the students and the student name is not listed, for example student3 in this case, so I cannot just cbind the score.

Excel: There are 70 learning objectives, and because of missing names, I have to check all corresponding rows for all those 70 objectives for VLOOKUP:

(=VLOOKUP($C7,'0learning.csv'!$B$372:$G$395,5,0)
(=VLOOKUP($C7,'0learning.csv'!$B$372:$G$395,6,0)

Is there a better way?

Answer 1

We could use the devel version of data.table i.e. v1.9.5 which can take take multiple value.var columns and reshape the 'long' form to 'wide'. Instructions to install are here.

 library(data.table)#v1.9.5+
 names(data)[2] <- 'objective'
 dcast(setDT(data), student~objective, value.var=c('attempt', 'score'))
 #    student attempt_objective1 attempt_objective2 score_objective1
 #1: student1                 90                 20                6
 #2: student2                 27                 85               19
 #3: student3                 37                 96               16
 #4: student4                 56                 61               60
 #5: student5                 88                 58               34
 #    score_objective2
 #1:               66
 #2:               42
 #3:               87
 #4:               82
 #5:               31

Or using reshape from base R

 reshape(data, idvar='student', timevar='objective', direction='wide')
 #  student attempt.objective1 score.objective1 attempt.objective2
 #  1 student1                 90                6                 20
 #  2 student2                 27               19                 85
 #  3 student3                 37               16                 96
 #  4 student4                 56               60                 61
 #  5 student5                 88               34                 58
 #    score.objective2
 #  1               66
 #  2               42
 #  3               87
 #  4               82
 #  5               31