How do I transform a data.frame from vertical to horizontally grouped columns?

Question

Suppose I have the following data.frame:

a <- data.frame(group=c(3, 3, 3, 5, 5, 5), code=c(1, 2, 3, 1, 2, 3), val=c(10, 20, 30, 40, 50, 60), val2=c(100, 200, 300, 400, 500, 600))
a
    group code val val2
1     3    1  10  100
2     3    2  20  200
3     3    3  30  300
4     5    1  40  400
5     5    2  50  500
6     5    3  60  600

I would like to get the following data.frame:

  group  "1.val" "2.val" "3.val" "1.val2" "2.val2" "3.val2"
1     3     10     20       30      100      200      300
2     5     40     50       60      400      500      600

The column names are not correct in the duplicated post.

NOTE: I would prefer a base-R solution but other ideas would be interesting to see

score 3 · Accepted Answer · answered Jul 29 '19 at 17:03

An option with pivot_wider from the devel version of tidyr

library(tidyr)#  ‘0.8.3.9000’
pivot_wider(a, names_from = code, values_from = c("val", "val2"))
# A tibble: 2 x 7
#  group val_1 val_2 val_3 val2_1 val2_2 val2_3
#  <dbl> <dbl> <dbl> <dbl>  <dbl>  <dbl>  <dbl>
#1     3    10    20    30    100    200    300
#2     5    40    50    60    400    500    600

If we need the sep in column name to be ., use names_sep

pivot_wider(a, names_from = code, values_from = c("val", "val2"), names_sep=".")

NOTE: Generally, it is better to have column names start with standard format characters instead of numbers

score 1 · Answer 2 · answered Jul 29 '19 at 17:33

do.call(rbind, lapply(split(a, a$group), function(x) {
    cbind(data.frame(group = x$group[1]),
          setNames(data.frame(lapply(lapply(x[!names(x) %in% c("group", "code")], c),
                                     function(y) {
                                         lapply(y, list)
                                     })),
                   paste(rep(
                       names(x)[!names(x) %in% c("group", "code")], each = NROW(x)
                   ), x$code, sep = ".")))
}))
#  group val.1 val.2 val.3 val2.1 val2.2 val2.3
#3     3    10    20    30    100    200    300
#5     5    40    50    60    400    500    600

How do I transform a data.frame from vertical to horizontally grouped columns?

2 Answers2