Reshaping table with R

Question

I want to reshape a table using R. I tried the pivot_wider function but I am not achieving the desired result.

This is my table:

table1 <- structure(list(subjects = c("Group A_subject 1", "Group A_subject 2", 
"Group A_subject 3", "Group B_subject 1", "Group B_subject 2"
), age = c(6, 8, 4, 9, 7), whatever = c(10, 12, 15, 16, 19)), class = c("spec_tbl_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -5L), spec = structure(list(
    cols = list(subjects = structure(list(), class = c("collector_character", 
    "collector")), age = structure(list(), class = c("collector_double", 
    "collector")), whatever = structure(list(), class = c("collector_double", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), skip = 1L), class = "col_spec"))

I am trying something like the following, but I can't set up a proper pattern for names_sep.

    tableout <- table1 %>%
    pivot_wider(names_from = subjects, names_sep = Group *, values_from = age, values_fill = 1)

I am new to R so another solution may be better than using pivot. Basically assigning the original values to Group A and Group B in a wide format.

Desired Output from dput() - the table was created manually

   output <- structure(list(subjects = c("subject 1", "subject 2", "subject 3"
    ), `Group A` = c(6, 8, 4), `Group B` = c(9, 7, NA)), class = c("spec_tbl_df", 
    "tbl_df", "tbl", "data.frame"), row.names = c(NA, -3L), spec = structure(list(
        cols = list(subjects = structure(list(), class = c("collector_character", 
        "collector")), `Group A` = structure(list(), class = c("collector_double", 
        "collector")), `Group B` = structure(list(), class = c("collector_double", 
        "collector"))), default = structure(list(), class = c("collector_guess", 
        "collector")), skip = 1L), class = "col_spec"))

Welcome to SO! Please post your input data and desired output using `dput()`: we can't easily recreate your data from images. — Limey, Mar 23 '21 at 14:53
Your picture does not show a table. It shows two vectors. In R, a table is a matrix with labels. Your row dimension is not the same for A and B. — dcarlson, Mar 24 '21 at 03:21
If I copy/paste your code `names_sep = Group *` does not work, how do you get that asterisk in? — Emy, Mar 26 '21 at 13:10
sorry I was not perfectly clear. That part is exactly the problem as I don't know what I should put there in order to make it work. The "Group * " was my attempt but it doesn't work. — Code111, Mar 26 '21 at 13:14
No problem, I think it would be better to try something and show that is not working, rather than writing "what does it go here?". Also try building a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — Emy, Mar 26 '21 at 13:39

score 1 · Accepted Answer · answered Mar 27 '21 at 19:31

We can use separate then pivot_wider Using tidyr and dplyr packages. Both in tidyverse

library(tidyverse)
df1 <- table1 %>% 
  select(-whatever) %>% 
  separate(subjects, c("Group", "Subject"), "_") %>% 
  pivot_wider(names_from = "Group", values_from= "age")

# Output
# A tibble: 3 x 3
# Groups:   Subject [3]
  Subject   `Group A` `Group B`
  <chr>         <dbl>     <dbl>
1 subject 1         6         9
2 subject 2         8         7
3 subject 3         4        NA

Reshaping table with R

1 Answers1