Separate a column with characters and numbers to two seperated columns for each class

Question

I want to separate the 1st column of my dataframe named Demand Per Section to two separated columns named Units for characters and Demand for numeric values.

df<-structure(list(`Demand Per Section` = c("80 GM", "125ML", "350 ML", 
"100 GM", "538ML", "75GM", "25GM", "138GM", "138GM", "75GM"), 
    Formula = c("C10H8", "HNO3", "H2SO4", "C7H6O3", "CH3COOCOCH3", 
    "C10H8O", "NaOH", "C6H5NHNH2.HCl", "C6H12O6", "CH3COONa"), 
    `Element Name` = c("Naphthalene", "Nitric acid (concentrated)", 
    "Sulphuric acid(concentrated)", "2-hydroxybenzoic acid", 
    "Acetic anhydride", "2-Naphthol", "Sodium Hydroxide", "Phenyl hydrazine hydrochloride", 
    "Glucose", "Sodium acetate"), `Course Name` = c("Course 1", 
    "Course 1", "Course 1", "Course 1", "Course 1", "Course 1", 
    "Course 1", "Course 1", "Course 1", "Course 1"), Department = c("Chemsitry", 
    "Chemsitry", "Chemsitry", "Chemsitry", "Chemsitry", "Chemsitry", 
    "Chemsitry", "Chemsitry", "Chemsitry", "Chemsitry")), row.names = c(NA, 
-10L), class = c("tbl_df", "tbl", "data.frame"))

library(tidyverse)
df %>%
  mutate(Unit = str_extract(`Demand Per Section`, "[A-Z]?"), 
         Demand = str_extract(`Demand Per Section`, "[0-9]?")) %>%
  select(`Demand Per Section`,Unit,Demand)

score 4 · Accepted Answer · answered Feb 28 '22 at 16:16

As there are inconsistent spaces between the digits and letter, we may use a regex lookaround

library(dplyr)
library(tidyr)
df %>% 
  separate(`Demand Per Section`, into = c("Demand", "Unit"), 
     sep = "(?<=[0-9])(?=\\s?[A-Z])", remove = FALSE) %>% 
  mutate(Unit = trimws(Unit))

score 3 · Answer 2 · answered Feb 28 '22 at 16:31

Here is an alternative strategy using gsub and parse_number from readr:

library(dplyr)
library(readr)

df %>%
  mutate(Unit = gsub("[^a-zA-Z]", "", `Demand Per Section`), .before=2) %>% 
  mutate(Demand = parse_number(`Demand Per Section`), .before=2)

`Demand Per Section` Demand Unit  Formula       `Element Name`             `Course Name` Department
   <chr>                 <dbl> <chr> <chr>         <chr>                      <chr>         <chr>     
 1 80 GM                    80 GM    C10H8         Naphthalene                Course 1      Chemsitry 
 2 125ML                   125 ML    HNO3          Nitric acid (concentrated) Course 1      Chemsitry 
 3 350 ML                  350 ML    H2SO4         Sulphuric acid(concentrat~ Course 1      Chemsitry 
 4 100 GM                  100 GM    C7H6O3        2-hydroxybenzoic acid      Course 1      Chemsitry 
 5 538ML                   538 ML    CH3COOCOCH3   Acetic anhydride           Course 1      Chemsitry 
 6 75GM                     75 GM    C10H8O        2-Naphthol                 Course 1      Chemsitry 
 7 25GM                     25 GM    NaOH          Sodium Hydroxide           Course 1      Chemsitry 
 8 138GM                   138 GM    C6H5NHNH2.HCl Phenyl hydrazine hydrochl~ Course 1      Chemsitry 
 9 138GM                   138 GM    C6H12O6       Glucose                    Course 1      Chemsitry 
10 75GM                     75 GM    CH3COONa      Sodium acetate             Course 1      Chemsitry

Separate a column with characters and numbers to two seperated columns for each class

2 Answers2