R: make a data matrix of grouped variables and frequencies

Question

I have a data frame with three categories, A student grade (grade), a 1-5 value reported by the student how easy the assignment was (level), the percentage of students who reported each 1-5 value (percentage), example,

grade <- c('A','A','A','A','B','B','B','B','C+','C+','C+','C-','C-','C-','D','D','N','N')
level <- c(2,3,4,5,2,3,4,5,2,3,4,2,3,4,2,3,1,3)
percentage <- c(0.8403361,12.605042,12.605042,0.8403361,2.5210084,
                23.5294118,10.9243697, 2.5210084, 4.2016807, 11.7647059, 
                5.8823529, 0.8403361, 4.2016807, 1.6806723, 0.8403361, 
                2.5210084, 0.8403361, 0.8403361)

df <- data.frame(grade, level, percentage)

grade level percentage
A      2    0.8403361
A      3    12.605042
A      4    12.605042
A      5    0.8403361
B      2    2.5210084
B      3    23.5294118
B      4    10.9243697
B      5    2.5210084
C+     2    4.2016807
C+     3    11.7647059
C+     4    5.8823529
C-     2    0.8403361
C-     3    4.2016807
C-     4    1.6806723
D      2    0.8403361
D      3    2.5210084
N      1    0.8403361
N      3    0.8403361

Note that the 'easiness' (level) 1 has been missing from all the grades except "N".

Now I need to put this data in a matrix-like way, which should look like,

            A           B           C+          C-          D           N
1           0           0           0           0           0   0.8403361
2   0.8403361   2.5210084   4.2016807   0.8403361   0.8403361           0
3   12.605042   23.5294118  11.7647059  4.2016807   2.5210084   0.8403361
4   12.605042   10.9243697  5.8823529   1.6806723          0            0
5   0.8403361   2.5210084           0           0          0            0

Can you help me with the R code to transform the data frame like this? Here I want the R code to look for "1" level even though they could be missing from the frequency table. Should the "1" level and the 0 percentages be introduced to the table beforehand or can I automate it somehow? (By ifelse checking)

Possible duplicate of [How to reshape data from long to wide format?](https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format) or https://stackoverflow.com/questions/9617348/reshape-three-column-data-frame-to-matrix-long-to-wide-format — jogo, Dec 21 '17 at 12:14

score 0 · Accepted Answer · answered Dec 21 '17 at 11:58

0

You can use dcast from the reshape2 package:

library(reshape2)
dcast(df, formula="level~grade", fill=0, value.var="percentage")

answered Dec 21 '17 at 11:58

ags29

2,621
1
8
14

score 0 · Answer 2 · answered Dec 21 '17 at 12:12

With tidyr you can solve this using spread:

library(tidyr)
spread(data = df, key = grade, value = percentage, fill = 0)

#   level          A         B        C-        C+         D         N
# 1     1  0.0000000  0.000000 0.0000000  0.000000 0.0000000 0.8403361
# 2     2  0.8403361  2.521008 0.8403361  4.201681 0.8403361 0.0000000
# 3     3 12.6050420 23.529412 4.2016807 11.764706 2.5210084 0.8403361
# 4     4 12.6050420 10.924370 1.6806723  5.882353 0.0000000 0.0000000
# 5     5  0.8403361  2.521008 0.0000000  0.000000 0.0000000 0.0000000

fill = 0 makes sure that missing values are filled with 0 instead of the default NA

R: make a data matrix of grouped variables and frequencies

2 Answers2