Transposing and rearranging data

[kuttan98]

Hi. I have a data frame (data1) with student classroom observation. So there are six students and there are three parameters (reading, demo and reprimand). Reading1 corresponds to student1, reading2 corresponds to student2 and so on.
I want to transpose this data and make it appear as in data2. How can I do this?

data1<-tibble::tribble(
  ~student1, ~student2, ~student3, ~student4, ~student5, ~student6, ~reading1, ~demo1, ~reprimand1, ~reading2, ~demo2, ~reprimand2, ~reading3, ~demo3, ~reprimand3, ~reading4, ~demo4, ~reprimand4, ~reading5, ~demo5, ~reprimand5, ~reading6, ~demo6, ~reprimand6,
      "abc",     "pqr",     "xyz",     "rst",     "def",     "ghi",        0L,     1L,          0L,        1L,     0L,          1L,        1L,     1L,          0L,        1L,     0L,          0L,        0L,     1L,          0L,        1L,     0L,          1L
  )

data2<-tibble::tribble(
         ~student, ~reading, ~demo, ~reprimand,
            "abc",       0L,    1L,         0L,
            "pqr",       1L,    0L,         1L,
            "xyz",       1L,    1L,         0L,
            "rst",       1L,    0L,         0L,
            "def",       0L,    1L,         0L,
            "ghi",       1L,    0L,         1L
         )
Created on 2022-06-11 by the reprex package (v2.0.1)

[xvalda]

Hi @kuttan98
I'm sure there's a shorter and more elegant way, but here's a solution:

library(tidyverse)
data1<-tibble::tribble(
  ~student1, ~student2, ~student3, ~student4, ~student5, ~student6, ~reading1, ~demo1, ~reprimand1, ~reading2, ~demo2, ~reprimand2, ~reading3, ~demo3, ~reprimand3, ~reading4, ~demo4, ~reprimand4, ~reading5, ~demo5, ~reprimand5, ~reading6, ~demo6, ~reprimand6,
  "abc",     "pqr",     "xyz",     "rst",     "def",     "ghi",        0L,     1L,          0L,        1L,     0L,          1L,        1L,     1L,          0L,        1L,     0L,          0L,        0L,     1L,          0L,        1L,     0L,          1L
)
(data1 %>% 
  mutate(across(everything(), ~as.character(.x))) %>% 
  pivot_longer(
    cols = everything(), 
    names_to = "name", 
    values_to = "value"
  ) %>% 
  mutate(name = str_replace(name, "(\\d+)", " \\1")) %>% 
  separate(name, into = c("name", "ID"), sep = " ") %>% 
  pivot_wider(names_from = name, values_from = value) %>% 
  mutate(across(-student, ~as.numeric(.x))) %>% 
  select(-ID))
#> # A tibble: 6 × 4
#>   student reading  demo reprimand
#>   <chr>     <dbl> <dbl>     <dbl>
#> 1 abc           0     1         0
#> 2 pqr           1     0         1
#> 3 xyz           1     1         0
#> 4 rst           1     0         0
#> 5 def           0     1         0
#> 6 ghi           1     0         1

[kuttan98]

Thank you for this.. But can you tell what the expression inside str_replace mean? I haven't played with strings much.

[xvalda]

Since I need to separate the name variable into two columns, I need to indicate a separator value.
For his I need to transform values of name from "student1", "reading1", ... to "student 1", "reading 1", ...
So I need to include a blank space in these values, between the characters and the digit(s).
Or better said, I need to replace a digit with a blank space and the same digit.

It's what str_replace does:

• it replaces all values of the name column based on a pattern and replacement string
• I look for the following pattern: any number (\d) one or more times (+) and put the whole pattern in brackets so that it can be referred to later with \1
• The replacement string (last argument of str_replace) is a space followed by the expression that was matched before (meaning a digit one of more times)