R transform data with multiple values

Question

I'm trying to transform my data so that each keyword has a value rather than grouped by value. My data is currently organized like this:

df:

score1 score2 keyword1 keyword2 keyword3
.2    .4    brown    fox    jump
.7    .2    hello    bye    
.1    .9    foo        

I would like my data to look like this:

keyword score1 score2
brown    .2    .4     
fox    .2    .4    
jump    .2    .4    
hello    .7    .2    
bye    .7    .2   
foo    .1    .9  

data:

df = structure(list(score1 = c(.2, .7, .1), score2 = c(.4, .2, .9), keyword1 = c("brown", "hello", "foo"), keyword2 = c("fox", "bye"), keyword3 = "jump"), .Names = c("score1", "score2", "keyword1", "keyword2", "keyword3"), row.names = c(NA, -5L), class = "data.frame")

any suggestions?

Answer 1

Here is one method using melt from the data.table package:

# drop some missing obs
df <- df[1:3, ]
# create ID variable
df$id <- 1:nrow(df)

# load data.table
library(data.table)
# reshape long (melt)
newdf <- melt(df, id.vars=c("id", "score1", "score2"), 
     measure.vars=c("keyword1", "keyword2", "keyword3"), value.name="keyword")

Although not necessary in the example, I added an id variable so that this method will work on larger datasets to cover the case where score1 and score2 may be identical for multiple observations. This creates two more columns than in your example, the id column and a column with "keyword1" and so on. It is easy enough to drop these. In addition, there are some rows present that are NA, do to the non-rectangular shape of the input data. These may be dropped with is.na:

# drop rows with missing values in keyword column
newdf <- newdf[!is.na(newdf$keyword),]