Convert wide to long format in R

Question

My data has a long format similar to the one below:

ID Language  MotherTongue  SpokenatHome   HomeLang
1    English   English                      English
1    French                   French        
1    Polish    Polish         
2    Lebanese  Lebanese        Lebanese    Labanese
2    Arabic                    Arabbic

Here is the output I am looking for:

ID  Language1 Language2 Language 3  MotherTongue1  MotherTongue2  SpokenatHome1 HomeLan
1   English    French     Polish     English         Polish        French       English
2   Lebanese   Arabic                Labanese                      Arabic

I'm using using the melt and dcast functions of the reshape2 package, but it does not work. Does anyone know how to do this? Thanks.

df<-df[,c("OEN", "Langugae","MotherTongue", "SpokenatHome", "MainHomeLanguage")]
dfl <- melt(df, id.vars=c("OEN", "Langugae"), measure.vars=c("MotherTongue", "SpokenatHome", "MainHomeLanguage"),
            variable.name="Language")

dfw <- dcast(dfl, OEN ~  Langugae , value.var="value" )

Answer 1

Hi you can try this (nonetheless it rely on plyr for indexing language) :

df <- read.table(text="ID Language
1    English
1    French
1    Spanish
1    Polish
2    English
2    French
3    Lebanese
3    Arabic", header=T)

# For creating an index of Language by ID (there is probably a better way to do this)
library(plyr)
df <- ddply(df, .(ID), mutate, ID2 =  1:length(ID)

# The same as above without using plyr :
df$ID2 <- unlist(tapply(X = df$ID, INDEX = df$ID, FUN = function(x) 1:length(x)))

# And use reshape for doing what you want
reshape(data = df, timevar = "ID2", v.names = "Language", idvar = "ID", direction = "wide")

#  ID Language.1 Language.2 Language.3 Language.4
#1  1    English     French    Spanish     Polish
#5  2    English     French       <NA>       <NA>
#7  3   Lebanese     Arabic       <NA>       <NA>

The same with your second dataset :

df2 <- read.table(text="ID Language  MotherTongue  SpokenatHome   HomeLang
1    English   English            NA      English
1    French       NA           French        NA
1    Polish    Polish         NA           NA
2    Lebanese  Lebanese        Lebanese    Labanese
2    Arabic       NA    NA             Arabbic", header=TRUE)

df2 <- ddply(df2, .(ID), mutate, ID2 =  1:length(ID))
reshape(data = df2, timevar = "ID2", v.names = c("Language", "MotherTongue", "SpokenatHome", "HomeLang"), idvar = "ID", direction = "wide")

Answer 2

Not very elegant:

df <- read.table(header = TRUE, as.is = TRUE, text = '
                 ID Language
1    English
1    French
1    Spanish
1    Polish
2    English
2    French
3    Lebanese
3    Arabic')


# split by ID
sp <- tapply(df$Language, df$ID, function(x) x)
# max length
mls <- max(sapply(x, length))

# make same length
spNA <- lapply(sp, function(x) {
  l <- length(x)
  if(l == mls){
    out <- x
  } else {
    out <- c(x, rep(NA, mls-l))
  }
  return(out)
  }
)
# rbind
do.call(rbind, spNA)

# [,1]       [,2]     [,3]      [,4]    
# 1 "English"  "French" "Spanish" "Polish"
# 2 "English"  "French" NA        NA      
# 3 "Lebanese" "Arabic" NA        NA    

Answer 3

Here is a reshape2 solution. Like the other answers, I had to add in a variable to represent the number of answer within each ID which I did with ddply from plyr.

df = read.table(text="ID Language  MotherTongue  SpokenatHome   HomeLang
1    English   English            NA      English
1    French       NA           French        NA
1    Polish    Polish         NA           NA
2    Lebanese  Lebanese        Lebanese    Labanese
2    Arabic       NA              Arabbic NA", header=TRUE)

require(reshape2)
df1 = melt(df, id.vars=c("ID"), variable.name = "type")

require(plyr)
# Add in variable for number of unique  answers per ID
df1 = ddply(df1, .(ID, type), mutate, num = 1:length(ID))
# Cast the dataset wide
df2 = dcast(df1, ID ~ type + num)

This gives multiple columns for each category (Language, HomeLang, etc.). If you need to remove columns that contain all NA, you can do so as below (which I found here).

df2[colSums(is.na(df2)) < nrow(df2)]