Separate wider

The separate_wider_delim() function splits a string column into multiple columns by a delimiter.

Tidyverse reference page

Dataset

For demonstration we’ll load the crop_and_soil_tbl data from the mgrtibbles package (hyperlink includes install instructions).

crop_and_soil_tbl details

#Load package
library("mgrtibbles")
#mammal_sleep_tbl tibble for demonstration
mgrtibbles::crop_and_soil_tbl |>
    #View first four columns with select
    dplyr::select(1:4)

# A tibble: 8,000 × 4
   Soil_type Crop_type Fertiliser Temperature_celsius_kelvin
   <chr>     <chr>     <chr>      <chr>                     
 1 Sandy     Maize     Urea       26_-247                   
 2 Loamy     Sugarcane DAP        29_-244                   
 3 Black     Cotton    14-35-14   34_-239                   
 4 Red       Tobacco   28-28      32_-241                   
 5 Clayey    Paddy     Urea       28_-245                   
 6 Sandy     Barley    17-17-17   26_-247                   
 7 Red       Cotton    20-20      25_-248                   
 8 Loamy     Wheat     Urea       33_-240                   
 9 Sandy     Millets   28-28      30_-243                   
10 Black     Oil seeds 14-35-14   29_-244                   
# ℹ 7,990 more rows

Separate a column

Split the column Temperature_celsius_kelvin into 2 columns by the delimiter “_“. This will produce a Temp_celsius column and a Temp_kelvin column.

As Temperature_celsius_kelvin was a string column we then need to mutate the 2 separated columns into numeric columns.

crop_and_soil_tbl |>
    #Select first four columns
    dplyr::select(1:4) |>
    #Separate wider
    tidyr::separate_wider_delim(Temperature_celsius_kelvin, delim="_",
    names = c("Temp_celsius", "Temp_kelvin")) |>
    #Mutate across the two columns to convert them to numeric columns
    dplyr::mutate(dplyr::across(Temp_celsius:Temp_kelvin, as.numeric))

# A tibble: 8,000 × 5
   Soil_type Crop_type Fertiliser Temp_celsius Temp_kelvin
   <chr>     <chr>     <chr>             <dbl>       <dbl>
 1 Sandy     Maize     Urea                 26        -247
 2 Loamy     Sugarcane DAP                  29        -244
 3 Black     Cotton    14-35-14             34        -239
 4 Red       Tobacco   28-28                32        -241
 5 Clayey    Paddy     Urea                 28        -245
 6 Sandy     Barley    17-17-17             26        -247
 7 Red       Cotton    20-20                25        -248
 8 Loamy     Wheat     Urea                 33        -240
 9 Sandy     Millets   28-28                30        -243
10 Black     Oil seeds 14-35-14             29        -244
# ℹ 7,990 more rows