Home > Software design >  R Extract first two characters from a column in a dataframe
R Extract first two characters from a column in a dataframe

Time:05-28

I have a dataset with multiple and I would like to extract the first two characters from the sr column. Lastly, these characters will be stored in a new column.

Basically, I want to have a new column permit_type that has the first two character values from sr i.e. AP, SP and MP.

How can I do this?

Sample data

structure(list(date_received = c("11/30/2021  ", "11/30/2021  ", 
"11/30/2021  ", "11/30/2021  ", "11/30/2021  ", "11/17/2021  ", 
"12/3/2021  ", "12/3/2021  ", "12/13/2021  "), date_approved = c("11/30/2021", 
"11/30/2021", "11/30/2021", "11/30/2021", "11/30/2021", "11/17/2021", 
"12/3/2021", "12/3/2021", "12/3/2021"), sr = c("AP-21-080", "SP-21-081", 
"AP-21-082", "SP-21-083", "MP-21-084", "AP-21-085", "AP-21-086", 
"MP-21-087", "SP-21-088"), permit = c("AP1766856 Classroom C", 
"AP1766858 Classroom A", "AP1766862 Landscape Area", "AP1766864 Classroom B", 
"AO1766867", "06-SE-2420566", "06-E-2425187", "", "06-SM-2424110"
)), row.names = c(NA, -9L), class = c("tbl_df", "tbl", "data.frame"
))

Method 1

library(tidyverse)
df$permit_type= df%>% str_split_fixed(df$sr, "-", 2)

# Error
Error in str_split_fixed(., df$sr, "-", 2) : 
  unused argument (2)

Method 2

df$permit_type = df%>% str_extract(sr, "^.{2}")

# Error
Error in str_extract(., sr, "^.{2}") : unused argument ("^.{2}")

Method 3

df = df %>%  mutate(permit_type = str_extract_all(sr, "\\b[a-z]{2}")) 

# Returns permit_type with `Character(0)` values

CodePudding user response:

For the last option, it should be uppercase characters ([A-Z]) instead of lowercase ([a-z]) as the input 'sr' column shows only uppercase. In addition, str_extract_all is used when there are multiple occurrences of the pattern and it returns a list (simplify = FALSE by default). Here, the example showed a single occurence, thus str_extract would be more useful as it returns a vector

library(dplyr)
library(stringr)
df %>% 
   mutate(permit_type = str_extract(sr, "\\b[A-Z]{2}"))
# A tibble: 9 × 5
  date_received  date_approved sr        permit                     permit_type
  <chr>          <chr>         <chr>     <chr>                      <chr>      
1 "11/30/2021  " 11/30/2021    AP-21-080 "AP1766856 Classroom C"    AP         
2 "11/30/2021  " 11/30/2021    SP-21-081 "AP1766858 Classroom A"    SP         
3 "11/30/2021  " 11/30/2021    AP-21-082 "AP1766862 Landscape Area" AP         
4 "11/30/2021  " 11/30/2021    SP-21-083 "AP1766864 Classroom B"    SP         
5 "11/30/2021  " 11/30/2021    MP-21-084 "AO1766867"                MP         
6 "11/17/2021  " 11/17/2021    AP-21-085 "06-SE-2420566"            AP         
7 "12/3/2021  "  12/3/2021     AP-21-086 "06-E-2425187"             AP         
8 "12/3/2021  "  12/3/2021     MP-21-087 ""                         MP         
9 "12/13/2021  " 12/3/2021     SP-21-088 "06-SM-2424110"            SP         

With str_split_fixed directly applying on the data, we can wrap the call within {}

df%>% 
   {str_split_fixed(.$sr, "-", 2)[,1]} 
[1] "AP" "SP" "AP" "SP" "MP" "AP" "AP" "MP" "SP"

Similar issue in the second case

df%>% 
  {str_extract(.$sr, "^.{2}")}
[1] "AP" "SP" "AP" "SP" "MP" "AP" "AP" "MP" "SP"

CodePudding user response:

in Base R, you could use:

transform(df, permit_type = substr(sr,1,2))

  date_received date_approved        sr                   permit permit_type
1  11/30/2021      11/30/2021 AP-21-080    AP1766856 Classroom C          AP
2  11/30/2021      11/30/2021 SP-21-081    AP1766858 Classroom A          SP
3  11/30/2021      11/30/2021 AP-21-082 AP1766862 Landscape Area          AP
4  11/30/2021      11/30/2021 SP-21-083    AP1766864 Classroom B          SP
5  11/30/2021      11/30/2021 MP-21-084                AO1766867          MP
6  11/17/2021      11/17/2021 AP-21-085            06-SE-2420566          AP
7   12/3/2021       12/3/2021 AP-21-086             06-E-2425187          AP
8   12/3/2021       12/3/2021 MP-21-087                                   MP
9  12/13/2021       12/3/2021 SP-21-088            06-SM-2424110          SP
  • Related