How to change data frame in R or Excel?

Question

Here is the code to get a sample dataset:

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 is as follows:

    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

I want to:

    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 goals, so it will be tiring to simply copy and paste the attempts and scores, so I wonder if there is a better way to clear the data.

R: I tried to use a function meltin R to get new data, but it does not work. For some students there are no grades, and the name of the student is not indicated, for example, student3in this case, so I can’t just cbindrate.

Excel: There are 70 learning goals, and due to the lack of names, I have to check all the relevant lines for all these 70 goals 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

devel data.table i.e. v1.9.5, value.var "long" "wide". : 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

reshape 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