CODEWITHSUNDEEP
rpivot
Code111
Code111

Reputation: 13

Reshaping table with R

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"))

Upvotes: 0

Views: 65

Answers (1)

TarJae
TarJae

Reputation: 78927

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

Upvotes: 1

Related Questions