John Doe John Doe - 3 years ago 180
R Question

splitting column in R

I'm facing the following problem. I've got a table with a column called 'title'.
The title column contains rows with values like 'To kill a mockingbird (1960)'.

So basically the format of the column is [title] ([year]). What I need is two
columns: title and year, year without brackets.

One other problem is that some rows contain a title including brackets. But

basically the last 6 characters of every row are year wrapped in brackets.


How do I create the two columns, title and year?

what i have is: Books$title <- c("To kill a mockingbird (1960)", "Harry Potter and the order of the phoenix (2003)", "Of mice and men (something something) (1937)")

Title
To kill a mockingbird (1960)
Harry Potter and the order of the phoenix (2003)
Of mice and men (something something) (1937)


what I need is: Books$title <- c("To kill a mockingbird", "Harry Potter and the order of the phoenix", "Of mice and men (something something)")
Book$year <- c("1960", "2003", "1937")

Title Year
To kill a mockingbird (1960)
Harry Potter and the order of the phoenix (2003)
Of mice and men (something something) (1937)

Answer Source

We can work around substring the last 6 characters.

First we recreate your data.frame:

df <- read.table(h=T, sep="\n", stringsAsFactors = FALSE,
text="
Title
To kill a mockingbird (1960)
Harry Potter and the order of the phoenix (2003)
Of mice and men (something something) (1937)")

Then we create a new one. The first column, Title is everything from df$Title but the last 7 characters (we also remove the trailing space). The second column, Year is the last 6 characters from df$Title and we remove any space, opening or closing bracket. (gsub("[[:punct:]]", ...) would have worked as well.

data.frame(Title=substr(df$Title, 1, nchar(df$Title)-7),
           Year=gsub(" |\\(|\\)", "", substr(df$Title, nchar(df$Title)-6, nchar(df$Title))))


                                      Title Year
1                     To kill a mockingbird 1960
2 Harry Potter and the order of the phoenix 2003
3     Of mice and men (something something) 1937

Does that solve your problem?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download