CODEWITHSUNDEEP
rdplyrreshape
versatile_programmer
versatile_programmer

Reputation: 235

How can I groupby a dataframe and then transpose and rbind groups in R?

library(dplyr)
sample.df <- data.frame(group.column = c("first_a","first_b","first_c","first_d",
                       "second_a","second_b","second_c","second_d",
                       "third_a","third_b","third_c","third_d"),
           value.column.first = 1:12,
           value.column.second = 13:24)

I created a buffer column and I tried the code below but it was no use.

sample.df %>% mutate(buffer = `group.column` %>% strsplit(split="_") %>% lapply(function(x) {x[1]}) %>% unlist) %>% group_by(buffer) %>% t %>% rbind

I want this table

A header a.1 a.2 b.1 b.2 c.1 c.2 d.1 d.2
first 1 13 2 14 3 15 4 16
second 5 17 6 18 7 19 8 20
third 9 21 10 22 11 23 12 24

What should I do ?

Upvotes: 2

Views: 80

Answers (1)

Ma&#235;l
Ma&#235;l

Reputation: 52349

separate your group column, and use pivot_wider:

library(tidyr)
library(dplyr)
sample.df %>% 
  separate(group.column, into = c("A header", "name")) %>% 
  pivot_wider(values_from = c(value.column.first, value.column.second),
              names_glue = "{name}.{match(.value, unique(.value))}",
              names_vary = "slowest")

# A tibble: 3 × 9
  `A header`   a.1   a.2   b.1   b.2   c.1   c.2   d.1   d.2
  <chr>      <int> <int> <int> <int> <int> <int> <int> <int>
1 first          1    13     2    14     3    15     4    16
2 second         5    17     6    18     7    19     8    20
3 third          9    21    10    22    11    23    12    24

Explanation:

  • separate divides the group.column string into two columns, one called A header with the first part (first, second, third), and one called name with a, b, c.... I call it name because that's the default value for the names_from parameter in pivot_wider.
  • in pivot_wider, names are taken from name, and values from the value columns. The names of the column are composed of the name value (a, b, c) and match(.value, unique(.value)) is a way to get the position of each of the 2 .value columns. names_vary = "slowest" is used to get the column ordering correct.

Upvotes: 2

Related Questions