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)
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.
