CODEWITHSUNDEEP
rdplyrtidyr
user8248672
user8248672

Reputation:

Combining values in a row into one column in r

I would like to combine the different genres of each movie in each row into one column, named genre.

Ideally, I'd like to have as my final output:

enter image description here

Here is my sample dataset: 

structure(list(g1 = c("Action", "Action"), g2 = c("Adventure", 
"Adventure"), g3 = c("Fantasy", "Fantasy"), g4 = c("Sci-Fi", 
NA), g5 = c(NA_character_, NA_character_), g6 = c(NA_character_, 
NA_character_), g7 = c(NA_character_, NA_character_), g8 = c(NA_character_, 
NA_character_)), row.names = c(NA, -2L), class = c("tbl_df", 
"tbl", "data.frame"))

I've tried

test %>% gather(g1:g8, key = "genre", value = "value")

Upvotes: 1

Views: 49

Answers (2)

AntoniosK
AntoniosK

Reputation: 16121

Here's an alternative solution, that uses map to apply a function (that transposes data and adds row names as a column) to each row:

library(tidyverse)

df %>%
  group_by(id = row_number()) %>%
  nest() %>%
  mutate(d = map(data, ~{data.frame(genre = t(.x), stringsAsFactors = F) %>% 
                         rownames_to_column("g")})) %>%
  unnest(d)

# # A tibble: 16 x 3
#      id g     genre    
#   <int> <chr> <chr>    
# 1     1 g1    Action   
# 2     1 g2    Adventure
# 3     1 g3    Fantasy  
# 4     1 g4    Sci-Fi   
# 5     1 g5    NA       
# 6     1 g6    NA       
# 7     1 g7    NA       
# 8     1 g8    NA       
# 9     2 g1    Action   
#10     2 g2    Adventure
#11     2 g3    Fantasy  
#12     2 g4    NA       
#13     2 g5    NA       
#14     2 g6    NA       
#15     2 g7    NA       
#16     2 g8    NA

Upvotes: 1

austensen
austensen

Reputation: 3007

library(dplyr)
library(tidyr)

df <- structure(list(g1 = c("Action", "Action"), g2 = c("Adventure", 
"Adventure"), g3 = c("Fantasy", "Fantasy"), g4 = c("Sci-Fi", 
NA), g5 = c(NA_character_, NA_character_), g6 = c(NA_character_, 
NA_character_), g7 = c(NA_character_, NA_character_), g8 = c(NA_character_, 
NA_character_)), row.names = c(NA, -2L), class = c("tbl_df", 
"tbl", "data.frame"))

df %>% 
  mutate(id = row_number()) %>%
  gather("key", "genre", -id) %>% 
  arrange(id, genre)

#> # A tibble: 16 x 3
#>       id key   genre      
#>    <int> <chr> <chr>    
#>  1     1 g1    Action   
#>  2     1 g2    Adventure
#>  3     1 g3    Fantasy  
#>  4     1 g4    Sci-Fi   
#>  5     1 g5    <NA>     
#>  6     1 g6    <NA>     
#>  7     1 g7    <NA>     
#>  8     1 g8    <NA>     
#>  9     2 g1    Action   
#> 10     2 g2    Adventure
#> 11     2 g3    Fantasy  
#> 12     2 g4    <NA>     
#> 13     2 g5    <NA>     
#> 14     2 g6    <NA>     
#> 15     2 g7    <NA>     
#> 16     2 g8    <NA>

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

Upvotes: 1

Related Questions