tidyr split a column with character and numerical values into two separate columns in R

user_12547996 0
Viewed 90

I have a data set which has an offense column which has the offense description and its associated offense code. The offense code is sometimes entirely in numeric and sometimes a combination of numeric and character.

How can I split this column into two different columns one for the offense code and one for offense description using tidyr in R?

Example data column:

Crime
123 Crime Description A
345 Crime Description B
678 Crime Description C
91011 Crime Description D
678(a)(1) Crime Description E
345(a)(32)(i) Crime Description F
143(a)(16) Crime Description G 
678.08(a) Crime Description H
976.D1 Crime Description I
tidyr r
3 Answers

You could use sub here:

Crime$offense_code <- sub("^(\\d+(?:\\.\\w+)?(?:\\(.*?\\))*) .*$", "\\1", Crime$data)
Crime$offense_desc <- sub("^\\d+(?:\\.\\w+)?(?:\\(.*?\\))* (.*)$", "\\1", Crime$data)
Crime

                               data  offense_code        offense_desc
1           123 Crime Description A           123 Crime Description A
2           345 Crime Description B           345 Crime Description B
3           678 Crime Description C           678 Crime Description C
4         91011 Crime Description D         91011 Crime Description D
5     678(a)(1) Crime Description E     678(a)(1) Crime Description E
6 345(a)(32)(i) Crime Description F 345(a)(32)(i) Crime Description F
7    143(a)(16) Crime Description G    143(a)(16) Crime Description G
8     678.08(a) Crime Description H     678.08(a) Crime Description H
9        976.D1 Crime Description I        976.D1 Crime Description I

The general regex used here says to match:

^               from the start of the data field
\\d+            an integer
(?:\\.\\w+)?    followed by optional dot and word component
(?:\\(.*?\\))*  followed by zero or more (...) terms
[ ]             a single space
.*              then match the entire description
$               until the end of the data field
user_18632290

You can split on the first whitespace. Using tidyr::separate you can use -

tidyr::separate(df, 'Crime', c('offense_code', 'offense_description'), 
                sep = '\\s', extra = 'merge')

#   offense_code offense_description
#1           123 Crime Description A
#2           345 Crime Description B
#3           678 Crime Description C
#4         91011 Crime Description D
#5     678(a)(1) Crime Description E
#6 345(a)(32)(i) Crime Description F
#7    143(a)(16) Crime Description G
#8     678.08(a) Crime Description H
#9        976.D1 Crime Description I

Add remove = FALSE if you want to keep the original column in the output.

user_39629140

Using read.csv from base R

read.csv(text = sub("\\s+", ",", df1$Crime), header = FALSE, col.names = c('offense_code', 'offense_description'))
   offense_code offense_description
1           123 Crime Description A
2           345 Crime Description B
3           678 Crime Description C
4         91011 Crime Description D
5     678(a)(1) Crime Description E
6 345(a)(32)(i) Crime Description F
7    143(a)(16) Crime Description G
8     678.08(a) Crime Description H
9        976.D1 Crime Description I

data

df1 <- structure(list(Crime = c("123 Crime Description A", "345 Crime Description B", 
"678 Crime Description C", "91011 Crime Description D", "678(a)(1) Crime Description E", 
"345(a)(32)(i) Crime Description F", "143(a)(16) Crime Description G", 
"678.08(a) Crime Description H", "976.D1 Crime Description I"
)), class = "data.frame", row.names = c(NA, -9L))
user_37322710
Related