Pivot wider and summarise whether level exists

user_9144036 0
Viewed 54

I have a dataframe:

df <- data.frame(date = c('2021-01-05','2021-01-05','2021-01-10'),
             creature =c('bird','dog','dog'))

I'd like to use pivot_wider to create column names for every individual item in creature, then populate the dataframe with TRUE or FALSE depending on whether that thing exists for that date or not.

 date       creature bird  dog  
  <chr>      <chr>  <lgl> <lgl>
1 2021-01-05 bird   TRUE  TRUE 
2 2021-01-05 dog    TRUE  TRUE 
3 2021-01-10 dog    FALSE TRUE

I can do this with dplyr, however, if there were dozens of levels, this would be annoying to do. Is there a way to do this with pivot_wider?

df %>% group_by(date) %>% 
      mutate(bird = ifelse('bird' %in% creature,TRUE,FALSE),
             dog = ifelse('dog' %in% creature, TRUE,FALSE))
dplyr r
1 Answers

You may try

library(dplyr)
library(tidyr)

df %>%
  pivot_wider(names_from = creature, id_cols = date, values_from = creature) %>%
  dplyr::mutate(across(!date, ~!is.na(.x))) %>%
  right_join(df, by = "date")

        date creature  bird  dog
1 2021-01-05     bird  TRUE TRUE
2 2021-01-05      dog  TRUE TRUE
3 2021-01-10      dog FALSE TRUE

Check for data with more animals(?)

df <- data.frame(date = c('2021-01-05','2021-01-05','2021-01-10', '2021-01-10', '2021-01-11'),
                 creature =c('bird','dog','dog', 'cat', 'dragon'))

df
        date creature
1 2021-01-05     bird
2 2021-01-05      dog
3 2021-01-10      dog
4 2021-01-10      cat
5 2021-01-11   dragon

df %>%
  pivot_wider(names_from = creature, id_cols = date, values_from = creature) %>%
  dplyr::mutate(across(!date, ~!is.na(.x))) %>%
  right_join(df, by = "date")

        date creature  bird   dog   cat dragon
1 2021-01-05     bird  TRUE  TRUE FALSE  FALSE
2 2021-01-05      dog  TRUE  TRUE FALSE  FALSE
3 2021-01-10      dog FALSE  TRUE  TRUE  FALSE
4 2021-01-10      cat FALSE  TRUE  TRUE  FALSE
5 2021-01-11   dragon FALSE FALSE FALSE   TRUE
user_167291750
Related