How can I separate one column into two in R so that the all capital letter words are in one column?

Question

I have a one column like this:

x <- c('WV West Virginia','FL Florida','CA California','SC South Carolina')

# [1] WV West Virginia                  FL Florida                        
# [3] CA California                     SC South Carolina

How can I separate the abbreviation from the whole state name. And I want to give the two new columns two different headers. I think I can only solve this by separating the all upper letter words away.

I want to clarify the the column. It's a columns with four rows: [1] WV West Virginia [2 ]FL Florida [3] CA California [4] SC South Carolina — Jess, Nov 11 '15 at 14:46
Possible duplicate of [Splitting a dataframe string column into multiple different columns](http://stackoverflow.com/questions/18641951/splitting-a-dataframe-string-column-into-multiple-different-columns) — Heroka, Nov 11 '15 at 14:48
But all can I substring the all-upper-letter words and words starts with capital letter? — Jess, Nov 11 '15 at 14:55
I don't think this is a duplicate since the linked question has a fixed separator whereas this question does not — rawr, Nov 11 '15 at 15:26
@rawr Oops, didn't saw that you posted a similar code in the comments. If you are posting that as a solution, I will delete mine. — akrun, Nov 11 '15 at 15:31
@akrun yours is simpler and returns a data frame. One could also do `read.fwf(file = textConnection(x), widths = c(2, 100))` — rawr, Nov 11 '15 at 15:34

Pierre L · Answer 1 · 2015-11-11T15:40:35.707

With tidyr we can use separate to expand the column into two while specifying the new names. The argument extra=merge limits the output to the given columns. The separator will default to non-alpha-numerics:

library(tidyr)
separate(df, x, c("Abb", "State"), extra="merge")
#  Abb          State
#1  WV  West Virginia
#2  FL        Florida
#3  CA     California
#4  SC South Carolina

Data

x = c('WV West Virginia', 'FL Florida','CA California', 'SC South Carolina')

score 3 · Answer 2 · answered Nov 11 '15 at 15:02

Two approaches without external packages:

Approach 1: you could use substring in combination with nchar.

dat <-data.frame(raw=c("WV West Virginia","FL Florida", "CA California","SC South Carolina"),
                 stringsAsFactors=F)


dat$code <- substr(dat$raw,1,2)
dat$state <- substr(dat$raw, 4, nchar(dat$raw))

> dat
                raw code          state
1  WV West Virginia   WV  West Virginia
2        FL Florida   FL        Florida
3     CA California   CA     California
4 SC South Carolina   SC South Carolina

Approach two: you could use regular expressions to replace parts of your strings:

##approach two: regex
dat$code <- sub(" .+","",dat$raw)
dat$state <- sub("[A-Z]{2} ","",dat$raw)

score 3 · Answer 3 · answered Nov 11 '15 at 15:11

Use the state.* constants that come with the base datasets package

DF = data.frame(raw=c("WV West Virginia","FL Florida","CA California","SC South Carolina"))

DF$state.abbr <- substr(DF$raw, 1, 2)
DF$state.name <- state.name[ match(DF$state.abbr, state.abb) ]

#                 raw state.abbr     state.name
# 1  WV West Virginia         WV  West Virginia
# 2        FL Florida         FL        Florida
# 3     CA California         CA     California
# 4 SC South Carolina         SC South Carolina

This way, you can afford to have typos or other oddities in the state names.

score 2 · Answer 4 · answered Nov 11 '15 at 14:59

2

Use the reshape2 package.

    library(reshape2)
    x <- rbind('WV West Virginia','FL Florida','CA California','SC South Carolina')
    colsplit(x," ",c("Code","State"))

Output:

  Code          State
1   WV  West Virginia
2   FL        Florida
3   CA     California
4   SC South Carolina

answered Nov 11 '15 at 14:59

Ansjovis86

1,506
5
17
48

You should assume that the input variable is already a vector. Typing out each value as you did in `rbind('WV West...)` may not be feasible. Imagine if it was all 50 states saved in a vector called `vec`? They would have to write out each. Instead something like `do.call(rbind, as.list(vec))` to work with what the must already have. – Pierre L Nov 11 '15 at 15:05
@PierreLafortune OP said it was a column and `rbind` makes a column. Seems somewhere between harmless and natural to construct the example this way. – Frank Nov 11 '15 at 15:19
In that case, rbind is unnecessary `colsplit(x, " ", c("Code", "State"))` works with vectors, no need to add dimensions. – Pierre L Nov 11 '15 at 15:28

akrun · Answer 5 · 2015-11-11T15:41:20.360

Based on @rawr's comment, we could split 'x' at white space that follows the first two characters, i.e. showed by the regex lookaround ((?<=^.{2})). The output will be a list, which we rbind, convert to data.frame and then cbind with the original vector 'x'.

 cbind(x, as.data.frame(do.call(rbind,strsplit(x, '(?<=^.{2})\\s+', perl=TRUE)),
                    stringsAsFactors=FALSE))
 #                x V1             V2
 #1  WV West Virginia WV  West Virginia
 #2        FL Florida FL        Florida
 #3     CA California CA     California
 #4 SC South Carolina SC South Carolina

Or instead of the regex lookaround, we could use stri_split with n=2 and split at whitespace.

 library(stringi)
 cbind(x,as.data.frame(do.call(rbind,stri_split(x, regex='\\s+', n=2))))

score 0 · Answer 6 · answered Nov 11 '15 at 15:48

Here's a data.table/ gsub approach:

x <- c('WV West Virginia','FL Florida','CA California','SC South Carolina')

data.table::data.table(x)[, 
    abb := gsub("(^[A-Z]{2})( .+)", "\\1", x)][, 
    state := gsub("(^[A-Z]{2})( .+)", "\\2", x)][]

##                    x abb           state
## 1:  WV West Virginia  WV   West Virginia
## 2:        FL Florida  FL         Florida
## 3:     CA California  CA      California
## 4: SC South Carolina  SC  South Carolina

How can I separate one column into two in R so that the all capital letter words are in one column?

6 Answers6