help with spread or pivot_wider

[ksingh19]

Hi,

I am trying to spread the "value" column in my data below so that I have different columns for Brand1 and Brand2 with their values underneath as seen in the data. However, I am not able to apply spread or pivot_wider correctly here for some reason.

df2 <- data.frame(
  stringsAsFactors = FALSE,
                    date = c(NA,"1996-01-01",
                             "1996-02-01",NA,"1996-01-01","1996-02-01"),
                   class = c("Class 1",
                             "Class 1","Class 1","Class 1","Class 1","Class 1"),
             value = c("Brand1", "376", "393", "Brand2", "928", "932")
      )

Any help with how you would solve this would be appreciated.

Thank you!

[andresrcs]

A possible solution

library(tidyverse)

df2 <- data.frame(
    stringsAsFactors = FALSE,
    date = c(NA,"1996-01-01",
             "1996-02-01",NA,"1996-01-01","1996-02-01"),
    class = c("Class 1",
              "Class 1","Class 1","Class 1","Class 1","Class 1"),
    value = c("Brand1", "376", "393", "Brand2", "928", "932")
)

df2 %>% 
    mutate(brand = if_else(str_detect(value, "^\\D"), value, NA_character_)) %>% 
    fill(brand, .direction = "down") %>% 
    drop_na() %>% 
    pivot_wider(names_from = brand, values_from = value)
#> # A tibble: 2 x 4
#>   date       class   Brand1 Brand2
#>   <chr>      <chr>   <chr>  <chr> 
#> 1 1996-01-01 Class 1 376    928   
#> 2 1996-02-01 Class 1 393    932

^{Created on 2021-01-24 by the reprex package (v0.3.0.9001)}

[ksingh19]

Thank you @andresrcs! This works perfectly!