0

I have solved a problem using the dcast function from reshape library. However, I don't usually work with that library and generally I prefer working with dplyr.

Here's the data example:

segment<- c('seg1', 'seg1', 'seg2', 'seg2', 'seg1', 'seg2', 'seg3', 'seg3', 'seg2', 'seg3')
Company<- c('c1', 'c2', 'c3', 'c4', 'c5', 'c2', 'c1', 'c3', 'c4', 'c5' )
var.1<- c(100, 20, 30, 50, 40, 40, 20, 30, 50, 40)
var.2<- c(200, 30, 30, 70, 30, 140, 100, 30, 90, 10)
var.3<- c(50, 50, 40, 20, 30, 40, 50, 40, 20, 30)
var.4<- c(60, 50, 35, 53, 42, 20, 100, 20, 30, 50)
df<- data.frame(segment, Company, var.1, var.2, var.3, var.4)

We want to reshape that data frame so the results will be:

new.df<- df %>%
    melt(id.vars = c(1:2)) %>%
    dcast(`segment` + `variable` ~ `Company`)

I try to achieve this same results using group_by and pivot_longer function instead of melt & dcast but I can't make it work (The only way I could make it work was if I choose 1 of my 4 variables to store in the cells but I want all 4 of them to exist).

camille
  • 16,432
  • 18
  • 38
  • 60
lu-202
  • 121
  • 7

1 Answers1

0
segment<- c('seg1', 'seg1', 'seg2', 'seg2', 'seg1', 'seg2', 'seg3', 'seg3', 'seg2', 'seg3')
Company<- c('c1', 'c2', 'c3', 'c4', 'c5', 'c2', 'c1', 'c3', 'c4', 'c5' )
var.1<- c(100, 20, 30, 50, 40, 40, 20, 30, 50, 40)
var.2<- c(200, 30, 30, 70, 30, 140, 100, 30, 90, 10)
var.3<- c(50, 50, 40, 20, 30, 40, 50, 40, 20, 30)
var.4<- c(60, 50, 35, 53, 42, 20, 100, 20, 30, 50)
df<- data.frame(segment, Company, var.1, var.2, var.3, var.4)

library(tidyverse)
df %>%
 pivot_longer(-c(segment, Company)) %>%
 pivot_wider(
   id_cols = c(segment, name),
   names_from = Company,
   values_from = Company,
   values_fill = 0,
   values_fn = length
 )
#> # A tibble: 12 x 7
#>    segment name     c1    c2    c3    c4    c5
#>    <chr>   <chr> <int> <int> <int> <int> <int>
#>  1 seg1    var.1     1     1     0     0     1
#>  2 seg1    var.2     1     1     0     0     1
#>  3 seg1    var.3     1     1     0     0     1
#>  4 seg1    var.4     1     1     0     0     1
#>  5 seg2    var.1     0     1     1     2     0
#>  6 seg2    var.2     0     1     1     2     0
#>  7 seg2    var.3     0     1     1     2     0
#>  8 seg2    var.4     0     1     1     2     0
#>  9 seg3    var.1     1     0     1     0     1
#> 10 seg3    var.2     1     0     1     0     1
#> 11 seg3    var.3     1     0     1     0     1
#> 12 seg3    var.4     1     0     1     0     1

Created on 2022-01-27 by the reprex package (v2.0.1)

Yuriy Saraykin
  • 8,390
  • 1
  • 7
  • 14