Create and fill columns based on value contained in a column

I want to reshape my data so that I create two new columns, conditional on their value in third column.

My data right now looks like this:

Name       value
df_ox_val    1  
df_cat_val   2   
df_cat_val   1  
df_ox_val    4  
df_ox_val    5  
...

I want to reshape it to this:

ox      cat  
         1  
2  
1  
         4  
         5  
...

I tried this:

df %>%  
  mutate(ox = case_when("ox" %in% name ~ value),  
        (cat = case_when("cat" %in% name ~ value))

But this created two columns full of NA values.

I also tried this:

df %>%  
  pivot_wider(  
    id_cols = c("Name"),  
    values_from = value,  
    names_from = "Name"  
  )

But this created separate columns for each value, rather than consolidating into two columns based on whether their "Name" includes ox or cat.

Basically, ox = "Name" when "Name" contains "ox" (and same for "cat"), but I'm not sure how to code that.

Solution

By adding an id column you can pivot_wider. You will possibly need a strategy for handling the NAs that will populate the resulting data frame. Since the values in those columns are numeric you might replace them with 0 if that makes sense in the context of your work. You won't be able to replace NA with an empty value without changing the variable's type.

First, some toy data

df <- data.frame(Name = sample(c('df_ox','df_cat'), 20, replace = TRUE),
                 value = sample(1:5, 20, replace = TRUE))

Add an id and pivot:

library(dplyr)
library(tidyr)

df %>%
  mutate(id = row_number()) %>%
  pivot_wider(names_from = Name,
              values_from = value) 
#> # A tibble: 20 × 3
#>       id df_cat df_ox
#>    <int>  <int> <int>
#>  1     1      3    NA
#>  2     2     NA     1
#>  3     3     NA     3
#>  4     4     NA     3
#>  5     5     NA     1
#>  6     6      2    NA
#>  7     7      1    NA
#>  8     8      5    NA
#>  9     9      2    NA
#> 10    10     NA     2
#> 11    11      5    NA
#> 12    12     NA     2
#> 13    13     NA     5
#> 14    14     NA     5
#> 15    15      1    NA
#> 16    16     NA     5
#> 17    17      4    NA
#> 18    18      3    NA
#> 19    19      4    NA
#> 20    20      5    NA

^{Created on 2023-09-25 with reprex v2.0.2}