15

A have the following tibble:

structure(list(age = c("21", "17", "32", "29", "15"), 
               gender = structure(c(2L, 1L, 1L, 2L, 2L), .Label = c("Female", "Male"), class = "factor")), 
          row.names = c(NA, -5L), class = c("tbl_df", "tbl", "data.frame"), .Names = c("age", "gender"))

    age gender
  <chr> <fctr>
1    21   Male
2    17 Female
3    32 Female
4    29   Male
5    15   Male

And I am trying to use tidyr::spread to achieve this:

  Female Male
1    NA     21
2    17     NA
3    32     NA
4    NA     29
5    NA     15

I thought spread(gender, age) would work, but I get an error message saying:

Error: Duplicate identifiers for rows (2, 3), (1, 4, 5)
alistaire
  • 42,459
  • 4
  • 77
  • 117
Dambo
  • 3,318
  • 5
  • 30
  • 79
  • Why do you want to untidy your data? – F. Privé Aug 26 '17 at 18:50
  • Spreading only with gender won't work? – F. Privé Aug 26 '17 at 18:51
  • 1
    @F.Privé just practicing, I simply want to understand why it is not working – Dambo Aug 26 '17 at 18:55
  • 1
    Add a unique index like `df %>% mutate(i = row_number()) %>% spread(gender, age)`. It's not very useful in this case, but is sometimes in more complicated contexts. – alistaire Aug 26 '17 at 19:14
  • @alistaire thanks, could you briefly explain why my solution is ambiguous? I don't get why tidyr needs those row numbers to spread. – Dambo Aug 26 '17 at 19:19
  • I added a somewhat fuller explanation in an answer. `vignette('tidy-data', 'tidyr')` may also be helpful. – alistaire Aug 26 '17 at 19:26
  • Related / possible duplicate: [*Transpose / reshape dataframe without “timevar” from long to wide format*](https://stackoverflow.com/q/11322801/2204410) – Jaap Jul 04 '19 at 16:31

1 Answers1

21

Right now you have two age values for Female and three for Male, and no other variables keeping them from being collapsed into a single row, as spread tries to do with values with similar/no index values:

library(tidyverse)

df <- data_frame(x = c('a', 'b'), y = 1:2)

df    # 2 rows...
#> # A tibble: 2 x 2
#>       x     y
#>   <chr> <int>
#> 1     a     1
#> 2     b     2

df %>% spread(x, y)    # ...become one if there's only one value for each.
#> # A tibble: 1 x 2
#>       a     b
#> * <int> <int>
#> 1     1     2

spread doesn't apply a function to combine multiple values (à la dcast), so rows must be indexed so there's one or zero values for a location, e.g.

df <- data_frame(i = c(1, 1, 2, 2, 3, 3), 
                 x = c('a', 'b', 'a', 'b', 'a', 'b'), 
                 y = 1:6)

df    # the two rows with each `i` value here...
#> # A tibble: 6 x 3
#>       i     x     y
#>   <dbl> <chr> <int>
#> 1     1     a     1
#> 2     1     b     2
#> 3     2     a     3
#> 4     2     b     4
#> 5     3     a     5
#> 6     3     b     6

df %>% spread(x, y)    # ...become one row here.
#> # A tibble: 3 x 3
#>       i     a     b
#> * <dbl> <int> <int>
#> 1     1     1     2
#> 2     2     3     4
#> 3     3     5     6

If you your values aren't indexed naturally by the other columns you can add a unique index column (e.g. by adding the row numbers as a column) which will stop spread from trying to collapse the rows:

df <- structure(list(age = c("21", "17", "32", "29", "15"), 
                     gender = structure(c(2L, 1L, 1L, 2L, 2L), 
                                        .Label = c("Female", "Male"), class = "factor")), 
                row.names = c(NA, -5L), 
                class = c("tbl_df", "tbl", "data.frame"), 
                .Names = c("age", "gender"))

df %>% mutate(i = row_number()) %>% spread(gender, age)
#> # A tibble: 5 x 3
#>       i Female  Male
#> * <int>  <chr> <chr>
#> 1     1   <NA>    21
#> 2     2     17  <NA>
#> 3     3     32  <NA>
#> 4     4   <NA>    29
#> 5     5   <NA>    15

If you want to remove it afterwards, add on select(-i). This doesn't produce a terribly useful data.frame in this case, but can be very useful in the midst of more complicated reshaping.

alistaire
  • 42,459
  • 4
  • 77
  • 117
  • Why cant `tidyr` do this for me? df %>% group_by_at(vars(-age)) %>% mutate(row_id=1:n()) %>% ungroup() %>% spread(key=gender, value=age) %>% select(-row_id) – dmi3kno Feb 21 '18 at 23:19
  • @dmi3kno It can and does? Or do you mean why can't `spread` do that by itself? If so, it's because row order is not a variable, so there's no inherent connection between 17 and 21 in the data until you add one via an index. – alistaire Feb 21 '18 at 23:50