pivot_wider only on one column in R

Here is my df:

   Analyte    name         Limit_Type
   <chr>      <chr>        <chr>     
 1 " BOD(C)"  Limit1       " 50%ile" 
 2 " BOD(C)"  Limit1_Value "10"         
 3 " CBOD(C)" Limit1       " 90%ile" 
 4 " CBOD(C)" Limit1_Value "15"      
 5 " CBOD(C)" Limit2       " NA"     
 6 " CBOD(C)" Limit2_Value  NA

I want to essentially "spread" or pivot_wider() the 'Limit_Type' column so that my df looks like this:

   Analyte    Limit_Type Limit
   <chr>      <chr>      <chr>
 1 " BOD(C)"  " 50%ile"  10    
 2 " CBOD(C)" " 90%ile"  15    
 3 " CBOD(C)" "NA"       NA

is this possible with dplyr?

thanks.

Solution

You can change the name column to Limit and Limit_Type based on the presence of 'Value'. Get the data in wide format using pivot_wider

library(dplyr)
library(tidyr)

df %>%
  mutate(name = ifelse(grepl('Value', name), 'Limit', 'Limit_Type')) %>%
  pivot_wider(names_from = name, values_from = Limit_Type, values_fn = list) %>%
  unnest(cols = c(Limit_Type, Limit))

#  Analyte    Limit_Type Limit
#  <chr>      <chr>      <chr>
#1 " BOD(C)"  " 50%ile"  10   
#2 " CBOD(C)" " 90%ile"  15   
#3 " CBOD(C)" " NA"      NA

data

df <- structure(list(Analyte = c(" BOD(C)", " BOD(C)", " CBOD(C)", 
" CBOD(C)", " CBOD(C)", " CBOD(C)"), name = c("Limit1", "Limit1_Value", 
"Limit1", "Limit1_Value", "Limit2", "Limit2_Value"), Limit_Type = c(" 50%ile", 
"10", " 90%ile", "15", " NA", NA)), class = "data.frame", row.names = c(NA, -6L))