R ReConstruct DataFrame

1
votes
data1=data.frame("grade"=c(rep(1:3,6)),
                "class" = c(rep(c(rep('a',3),rep('b',3)),3)),
"score"=c(rep(c('p','p','p','s','s','s','q','q','q'),2)),
"p"=c(-9:8),
"s"=c(1:18),
"q"=c(21:38))


data2=data.frame("grade"=c(rep(1:3,6)),
                  "class" = c(rep(rep('a',3),rep('b',3),3)),
                "pp"=c(-9,-8,-7,0,1,2),
                "ps"=c(1,2,3,10,11,12),
                "pq"=c(21,22,23,30,31,32),
                "sp"=c(-6,-5,-4,3,4,5),
                "ss"=c(4,5,6,13,14,15),
                "sq"=c(24,25,26,33,34,35),
                "qp"=c(-3,-2,-1,6,7,8),
                "qs"=c(7,8,9,16,17,18),
                "qq"=c(27,28,29,36,37,38))

What I have is data1 and I want to make data2. My english is not so good and so I made these two example data frames to show what I wish for. Basically to combine 'score' from 'data1' with column names 'p' and 'q' and 'q' from data1 to create 'data2'.

I have big student district data so wish for a fast data.table solution if it is possible. Also am interested to see dplyr or other simple solution!

Then I wonder how to ggplot all columns of 'data2' [pp-qq] by grade AND 'class'

dcast(setDT(data1), grade + class + rowid(score) ~ score, value.var = c('p', 's', 'q'), sep="")[, score := NULL][]

enter image description here

Hopeful output in two steps, ultimate wish is one on bottom, one on top is intermediate-

enter image description here

_____________________________________________________________________________ A member gave me a very great answer on recasting the dataframe:

data1=data.frame("grade"=c(rep(1:3,6)),
                 "class" = c(rep(c(rep('a',3),rep('b',3)),3)),
                 "score"=c(rep(c('p','p','p','s','s','s','q','q','q'),2)),
                 "p"=c(-9:8),
                 "s"=c(1:18),
                 "q"=c(21:38))


d2=dcast(melt(setDT(data1), id.var = 1:3)[, c('score', 'variable') := 
                                         lapply(.SD, function(x) setNames(c(3, 5, 9), c('p', 's', 'q'))[x]),
                                       .SDcols = c('score', 'variable')],grade + class ~ 
        paste0('x', score, variable), value.var = 'value')

It works perfect. In my application however I have many more variables. When I go to even add just 1 I break it:

data1=data.frame("col1"=c(1),
  "grade"=c(rep(1:3,6)),
                 "class" = c(rep(c(rep('a',3),rep('b',3)),3)),
                 "score"=c(rep(c('p','p','p','s','s','s','q','q','q'),2)),
                 "p"=c(-9:8),
                 "s"=c(1:18),
                 "q"=c(21:38))


d2=dcast(melt(setDT(data1), id.var = 1:3)[, c('score', 'variable') := 
                                         lapply(.SD, function(x) setNames(c(3, 5, 9), c('p', 's', 'q'))[x]),
                                       .SDcols = c('score', 'variable')], col1 + grade + class ~ 
        paste0('x', score, variable), value.var = 'value')

Error in [.data.table(melt(setDT(data1), id.var = 1:3), , :=(c("score", : Some items of .SDcols are not column names: [score] In addition: Warning message: In melt.data.table(setDT(data1), id.var = 1:3) : 'measure.vars' [score, p, s, q] are not all of the same type. By order of hierarchy, the molten data value column will be of type 'character'. All measure variables not of type 'character' will be coerced too. Check DETAILS in ?melt.data.table for more on coercion.

Maybe there is a way to do it less likely to break? Your aid is much appreciated!

2
rdplyrdata.table
@akrun any thoughts? - bvowe
@Ronak Shah any thoughts? - bvowe
Are those values x53, x35 interchanged - akrun
I see some values not correct. Can you please check in the expected - akrun
@akrun I AM INDEBTED TO YOU! - bvowe

2 Answers

1
votes

You can use base function reshape for this.

I needed to add an extra variable (unique) to discriminate the first 9 from the second 9 rows in data1. You could delete this afterwards if you don't need it.

data1$unique <- rep(1:2, each=9)

reshape(data=data1, 
        direction="wide",
        v.names=c("p","s","q"),
        timevar="score",
        idvar=c("grade","class","unique"),
        sep="")

   grade class unique pp sp qp ps ss qs pq sq qq
1      1     a      1 -9  1 21 -6  4 24 -3  7 27
2      2     a      1 -8  2 22 -5  5 25 -2  8 28
3      3     a      1 -7  3 23 -4  6 26 -1  9 29
10     1     a      2  0 10 30  3 13 33  6 16 36
11     2     a      2  1 11 31  4 14 34  7 17 37
12     3     a      2  2 12 32  5 15 35  8 18 38
1
votes

With tidyverse, we can do

library(dplyr)
library(tidyr)
library(data.table)
data1 %>%
   mutate(rn = rowid(score)) %>% 
   pivot_wider(names_from = score, values_from = c('p', 's', 'q'), 
         names_sep="") %>%
    select(-rn)
# A tibble: 6 x 11
#  grade class    pp    ps    pq    sp    ss    sq    qp    qs    qq
#  <int> <fct> <int> <int> <int> <int> <int> <int> <int> <int> <int>
#1     1 a        -9    -6    -3     1     4     7    21    24    27
#2     2 a        -8    -5    -2     2     5     8    22    25    28
#3     3 a        -7    -4    -1     3     6     9    23    26    29
#4     1 a         0     3     6    10    13    16    30    33    36
#5     2 a         1     4     7    11    14    17    31    34    37
#6     3 a         2     5     8    12    15    18    32    35    38

Or using data.table

library(data.table)
dcast(setDT(data1), grade + class + rowid(score) ~ score,
     value.var = c('p', 's', 'q'), sep="")[, score := NULL][]
#   grade class pp pq ps sp sq ss qp qq qs
#1:     1     a -9 -3 -6  1  7  4 21 27 24
#2:     1     a  0  6  3 10 16 13 30 36 33
#3:     2     a -8 -2 -5  2  8  5 22 28 25
#4:     2     a  1  7  4 11 17 14 31 37 34
#5:     3     a -7 -1 -4  3  9  6 23 29 26
#6:     3     a  2  8  5 12 18 15 32 38 35

If we want to replace the column names, use a named vector to match and replace

out <- dcast(setDT(data1), grade + class + rowid(score) ~ 
      setNames(c(33, 55, 99), c('p', 's', 'q'))[score],
     value.var = c('p', 's', 'q'), sep="")[, score := NULL][]
setnames(out, 3:ncol(out), sub("^.", "x", names(out)[3:ncol(out)]))
out
#   grade class x33 x55 x99 x33 x55 x99 x33 x55 x99
#1:     1     a  -9  -3  -6   1   7   4  21  27  24
#2:     1     a   0   6   3  10  16  13  30  36  33
#3:     2     a  -8  -2  -5   2   8   5  22  28  25
#4:     2     a   1   7   4  11  17  14  31  37  34
#5:     3     a  -7  -1  -4   3   9   6  23  29  26
#6:     3     a   2   8   5  12  18  15  32  38  35

For the updated example, we need to get the sequence by 'class' as well

out <- dcast(setDT(data1), grade + class + rowid(class, score) ~ 
       setNames(c(33, 55, 99), c('p', 's', 'q'))[score],
      value.var = c('p', 's', 'q'), sep="")[, class1 := NULL][]
setnames(out, 3:ncol(out), sub("^.", "x", names(out)[3:ncol(out)]))
out
#   grade class x33 x55 x99 x33 x55 x99 x33 x55 x99
#1:     1     a  -9  -3   3   1   7  13  21  27  33
#2:     1     b   0   6  -6  10  16   4  30  36  24
#3:     2     a  -8  -2   4   2   8  14  22  28  34
#4:     2     b   1   7  -5  11  17   5  31  37  25
#5:     3     a  -7  -1   5   3   9  15  23  29  35
#6:     3     b   2   8  -4  12  18   6  32  38  26

Update

setDT(data1)[, score := setNames(c(3, 5, 9), c('p', 's', 'q'))[score]]
setnames(data1, c('p', 's', 'q'), as.character(c(3, 5, 9)))
out <-  dcast(setDT(data1), grade + class + rowid(class, score) ~ score,
     value.var = c('3', '5', '9'), sep="")[, class1 := NULL][]
setnames(out, 3:ncol(out), paste0("x", names(out)[3:ncol(out)]))

Or another option is melt/dcast 

dcast(melt(setDT(data1), id.var = 1:3)[, c('score', 'variable') := 
 lapply(.SD, function(x) setNames(c(3, 5, 9), c('p', 's', 'q'))[x]),
 .SDcols = c('score', 'variable')], grade + class ~ 
     paste0('x', score, variable), value.var = 'value')
#   grade class x33 x35 x39 x53 x55 x59 x93 x95 x99
#1:     1     a  -9   1  21  -3   7  27   3  13  33
#2:     1     b   0  10  30   6  16  36  -6   4  24
#3:     2     a  -8   2  22  -2   8  28   4  14  34
#4:     2     b   1  11  31   7  17  37  -5   5  25
#5:     3     a  -7   3  23  -1   9  29   5  15  35
#6:     3     b   2  12  32   8  18  38  -4   6  26