1

I have data that looks like the following:

a<-data_frame(Type=c(rep("A",6),rep("B",6),c(rep("A",6),rep("B",6))),Type2=c(rep("x1",3),rep("X2",3),rep("x1",3),rep("X2",3),rep("x1",3),rep("X2",3),rep("x1",3),rep("X2",3)),Color=rep(c("Red","Green","Yellow"),8),Metric=c(rep("N",12),rep("Percent",12)),Value=c(1:24))

Type Type2  Color  Metric Value
 A    x1    Red       N     1
 A    x1  Green       N     2
 A    x1 Yellow       N     3
 A    X2    Red       N     4
 A    X2  Green       N     5
 A    X2 Yellow       N     6
 B    x1    Red       N     7
 B    x1  Green       N     8
 B    x1 Yellow       N     9
 B    X2    Red       N    10
 B    X2  Green       N    11
 B    X2 Yellow       N    12
 A    x1    Red Percent    13
 A    x1  Green Percent    14
 A    x1 Yellow Percent    15
 A    X2    Red Percent    16
 A    X2  Green Percent    17
 A    X2 Yellow Percent    18
 B    x1    Red Percent    19
 B    x1  Green Percent    20
 B    x1 Yellow Percent    21
 B    X2    Red Percent    22
 B    X2  Green Percent    23
 B    X2 Yellow Percent    24

I am attempting to create two columns for each Type in Type where one column corresponds to N and the other to Percentwhere each row contains a unique combination of Color and Type2. In this case for example there are six combinations for Type2 and Color;thus, the result would have 6 rows and six columns and look like this:

b<-data_frame(Type2=c(rep("X1",3),rep("X2",3)),Color=rep(c("Red","Green","Yellow"),2),A_N=c(1:6),A_Percent=c(13:18),B_N=c(7:12),B_Percent=c(19:24))

# A tibble: 6 x 6
Type2  Color    A_N   A_Percen  B_N     B_Percent
<chr> <chr>     <int>    <int>  <int>     <int>
 X1    Red        1        13     7        19
 X1    Green      2        14     8        20
 X1    Yellow     3        15     9        21
 X2    Red        4        16    10        22
 X2    Green      5        17    11        23
 X2    Yellow     6        18    12        24

I have tried dplyr's gather and spread, but nothing seems to be working. Any help would be appreciated. Thanks.

Jaap
  • 81,064
  • 34
  • 182
  • 193
costebk08
  • 1,299
  • 4
  • 17
  • 42

1 Answers1

4

The trick is to use unite to concatenate the columns Type and Metric, then use that new column as the key for spread. I usually think about a task like this (had a few just like this at work this week) by figuring out where in my df each of those pieces of information are, such as where can I find "A" and where can I find "Percent", then how I can bring them together.

library(tidyverse)

a <- data_frame(
    Type = c(rep("A",6),rep("B",6),c(rep("A",6),rep("B",6))),
    Type2 = c(rep("x1",3),rep("X2",3),rep("x1",3),rep("X2",3),rep("x1",3),rep("X2",3),rep("x1",3),rep("X2",3)),
    Color = rep(c("Red","Green","Yellow"),8),
    Metric = c(rep("N",12),rep("Percent",12)),
    Value = c(1:24)
)

a %>%
    unite("type_metric", Type, Metric) %>%
    spread(key = type_metric, value = Value)
#> # A tibble: 6 x 6
#>   Type2 Color    A_N A_Percent   B_N B_Percent
#>   <chr> <chr>  <int>     <int> <int>     <int>
#> 1 x1    Green      2        14     8        20
#> 2 x1    Red        1        13     7        19
#> 3 x1    Yellow     3        15     9        21
#> 4 X2    Green      5        17    11        23
#> 5 X2    Red        4        16    10        22
#> 6 X2    Yellow     6        18    12        24

Created on 2018-05-10 by the reprex package (v0.2.0).

camille
  • 16,432
  • 18
  • 38
  • 60