Unlist data frame column wihtout losing information from other column

Question

I have found a post, which is very similar to my problem. I have a data.frame with a list of keywords, which are separated by semicolons in one coloumn and the year in another column. I would like to unlist the keywords without losing the information about the year.

I can separate the keywords with strsplit and unlist

keywords <- unlist(strsplit(df$keywords,";"))
l1 <- sapply(df$keywords, length)
Year <- rep(df$Year, l1)
length(Year)
length(keywords)
dfkeywords=data.frame(Year=Year, Keywords=keywords, stringsAsFactors = F)

but I fail to generate a vector of the year that is the same length as the keywords vector.

How do I do that in a smart way?

Best Pete

Just try: `library(splitstackshape); cSplit(df, "keywords", ";", "long")` and see if that takes care of what you are looking to do. — A5C1D2H2I1M1N2O1R2T1, Nov 18 '15 at 10:55
Can we get a reproducible example? http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Roman Luštrik, Nov 18 '15 at 10:55

score 0 · Answer 1 · answered Nov 18 '15 at 11:37

Calculate the lengths before unlisting the split keywords. So split the keywords

keywords = strsplit(df$keywords,";")

find the lengths (number of key words) in each record

lens = lengths(keywords)

create the data.frame

data.frame(Year=rep(df$Year, lens), Keywords=unlist(keywords),
           stringsAsFactors=FALSE)

score 0 · Answer 2 · answered Nov 18 '15 at 12:13

Assuming you have something that looks like this:

df <- data.frame(keywords = c("some;text", "some;other;text", "even;more;text;here"),
                 Year = c(2025, 2026, 2099))
df
#              keywords Year
# 1           some;text 2025
# 2     some;other;text 2026
# 3 even;more;text;here 2099

Then I would suggest that you just consider using cSplit from my "splitstackshape" package.

library(splitstackshape)
cSplit(df, "keywords", ";", "long")
#    keywords Year
# 1:     some 2025
# 2:     text 2025
# 3:     some 2026
# 4:    other 2026
# 5:     text 2026
# 6:     even 2099
# 7:     more 2099
# 8:     text 2099
# 9:     here 2099

Other approaches to consider would be:

"dplyr" + "tidyr"

library(dplyr)
library(tidyr)
df %>%
  mutate(keywords = strsplit(as.character(keywords), ";")) %>%
  unnest(keywords)

"data.table"

library(data.table)
as.data.table(df)[, list(keywords = unlist(strsplit(as.character(keywords), ";"))), 
                  by = Year]

Unlist data frame column wihtout losing information from other column

2 Answers2

"dplyr" + "tidyr"

"data.table"