Create new variables based on specific factor levels in time series data with dplyr

user_9181336 0
Viewed 55

I've got some time series data where both the steps of the sequence (ranging from 1 to 8) as well as its topic (>100) are encoded as character factor levels within a single variable. Here is a minimal example (I omitted timestamps which would be increasing within each id):

id <- c(1,rep(2,5),rep(3,4))
step <- c("call", "call", "agent", "forest", "forward", "resolved", "call", "agent", "beach", "resolved")
(df <- data.frame(id,step))
   id     step
1   1     call
2   2     call
3   2    agent
4   2   forest
5   2  forward
6   2 resolved
7   3     call
8   3    agent
9   3    beach
10  3 resolved

I now want to split this information into two dedicated variables (step and topic), thereby shrinking the dataframe in rows and making it wider, while also repeating the topic for each row of the time series and adding an "NA" when there is no topic. Using base R to split this into two dataframes and merging them back together gets the job done:

step <- subset(df, step %in% c("call", "agent", "forward", "resolved"))
topic <- subset(df, step %in% c("forest", "beach"))
topic$topic <- topic$step
topic$step <- NULL
(newdf <- merge(step,topic, all=TRUE))
  id     step  topic
1  1     call   <NA>
2  2     call forest
3  2    agent forest
4  2  forward forest
5  2 resolved forest
6  3     call  beach
7  3    agent  beach
8  3 resolved  beach

This is somewhat clunky though and I'm looking for a more elegant dplyr/tidyverse approach to this. pivot_wider() doesn't seem to be able to do this. Any ideas?

dplyr r
2 Answers

This isn't particularly elegant, but it works:

steps <- c("call", "agent", "forward", "resolved")
df %>%
  mutate(type = ifelse(step %in% steps, "step", "topic"),
         row = cumsum(type == "step")) %>%
  pivot_wider(names_from = type, values_from = step) %>%
  group_by(id) %>%
  fill(topic, .direction = "updown") %>% 
  ungroup()



# A tibble: 8 x 4
     id   row step     topic 
  <dbl> <int> <chr>    <chr> 
1     1     1 call     NA    
2     2     2 call     forest
3     2     3 agent    forest
4     2     4 forward  forest
5     2     5 resolved forest
6     3     6 call     beach 
7     3     7 agent    beach 
8     3     8 resolved beach
user_68518250

Thanks for providing a minimal example of your problem

id <- c(1,rep(2,5),rep(3,4))
step <- c("call", "call", "agent", "forest", "forward",
  "resolved", "call", "agent", "beach", "resolved")
df <- data.frame(id,step)
df
#>    id     step
#> 1   1     call
#> 2   2     call
#> 3   2    agent
#> 4   2   forest
#> 5   2  forward
#> 6   2 resolved
#> 7   3     call
#> 8   3    agent
#> 9   3    beach
#> 10  3 resolved

This is a possible solution using tidyverse

library(dplyr)
library(tidyr)

df %>% 
  # define in column type_c if step is an step or a topic
  # you need a unique id for each row to use pivot_wider in this case
  mutate(
    type_c = if_else(step %in% c("forest", "beach"), "topic", "step"), 
    unique_id = 1:nrow(df)) %>% 
  pivot_wider(names_from = type_c, values_from = c(id, step)) %>% 
  mutate(id = coalesce(id_step, id_topic)) %>%
  select(id, step = step_step, topic = step_topic) %>% 
  # Need group_by to apply the function fill 
  group_by(id) %>%
  # fill replaces NA, in each id,  with a value found in any direction "downup"
  fill(topic, .direction = "downup") %>% 
  # get rid off the NA in column step that pivot_wider created for each topic
  filter(!is.na(step)) 
#> # A tibble: 8 x 3
#> # Groups:   id [3]
#>      id step     topic 
#>   <dbl> <chr>    <chr> 
#> 1     1 call     <NA>  
#> 2     2 call     forest
#> 3     2 agent    forest
#> 4     2 forward  forest
#> 5     2 resolved forest
#> 6     3 call     beach 
#> 7     3 agent    beach 
#> 8     3 resolved beach

Created on 2021-06-08 by the reprex package (v0.3.0)

user_66790660
Related