CODEWITHSUNDEEP
rsplittidyr
Mridul Garg
Mridul Garg

Reputation: 497

Splitting Columns in R based on space

I have a Location Column which looks like the following-

   Location 
   San Jose CA
   Santa Clara CA

I want to split into two columns. I'm using the separate function from tidyr, but giving the sep argument as " " gives me the following-

  City  State
  San   Jose
  Santa Clara

How can I split the column based on only the second space?

structure(list(Location = c("San Jose CA", "Santa Clara CA")),
          .Names = "Location", class = "data.frame", row.names = c(NA, -2L))

Upvotes: 3

Views: 20389

Answers (2)

akrun
akrun

Reputation: 887891

An option using base R involves replacing the last space with , (using sub) and then use read.csv to read the column.

read.csv(text=sub("\\s+(\\S+)$", ",\\1", df1$Location), 
        col.names=c('City', 'State'), header=FALSE)
#         City State
#1    San Jose    CA
#2 Santa Clara    CA

Upvotes: 1

JasonAizkalns
JasonAizkalns

Reputation: 20483

library(tidyr)

df <- data.frame(Location = c("San Jose CA", "Fremont CA", "Santa Clara CA"))
separate(df, Location, into = c("city", "state"), sep = " (?=[^ ]+$)")

#          city state
# 1    San Jose    CA
# 2     Fremont    CA
# 3 Santa Clara    CA

Upvotes: 12

Related Questions