Pivot_long with binary variable

user_11010556 0
Viewed 374

I am trying to practice the "tidy" transformation of the data by using pivot_long. Using the movie dataset from the ggplot2movies package. I am curious to know how I can pivot the binary columns into a wide table by creating a new column "genre": something like enter image description here

Here is my code for the first step, I filter all Action and Romance movie out: 

movies %>%

select(title, rating,Action,Romance)%>% filter(Action==1|Romance==1)

if I do the following:

movies %>% 
select(title, rating,Action,Romance)%>%
filter(Action==1|Romance==1)%>% #select the Action and Romance movies
pivot_longer(names_to = "genre",
             values_to = "Yes/No",
             cols = -title)%>%
r-markdown r
1 Answers

We can select the columns of interest, filter where the columns 'Action' or 'Romance' are 1, pivot to 'long' format with pivot_longer and then filter the rows where Yes/No column is 1 and remove the Yes/No

library(ggplot2movies)
library(dplyr)
library(tidyr)
movies %>% 
 select(title, rating,Action,Romance)%>%
 filter(Action==1|Romance==1) %>%
 pivot_longer(cols = Action:Romance, names_to = 'genre', values_to = "Yes/No") %>%
 filter(`Yes/No` == 1) %>%
 select(-`Yes/No`)

Or instead of reshaping to 'long' format, after the filter, step, just transmute the columns of interest, and create the 'genre' based on the index of max value of the 'Action', 'Romance' columns per each row

nm1 <- c('Action', 'Romance')
movies %>%      
  filter(Action==1|Romance==1) %>% 
  transmute(title, rating, genre = nm1[max.col(.[nm1], "first")])

Or using base R

submov <- subset(movies, Action == 1|Romance ==1, 
       select = c(title, rating, Action, Romance))
cbind(submov[c('title', 'rating')], 
     genre = names(submov)[3:4][max.col(submov[3:4], "first")])
user_37322710
Related