waealu waealu - 25 days ago 8
R Question

R - Split a one column dataframe into multiple columns

I have a dataframe that is a column with all the state abbreviations:

Name
AK
AL
AR
AZ
CO
CT
DC
FL


I want to take this column and split it into multiple columns such that no column has more than 5 cells.

Name1 Name2
AK CT
AL DC
AR FL
AZ
CO


I can create the code for what I want to do, but there has to be a better way:

states <- as.data.frame(state.abb)

new.table <- as.data.frame(states[1:5,])

i <- 6
k <- 2

repeat{
new.table[,k] <- as.data.frame(states[(i):(i+4),])
i <- i + 5
k <- k + 1
if(i>nrow(states)){
break
}
}

Answer

If NA is okay to use for the blank values, then we can do the following. Assuming your data is named df, we can first create a vector of values to be used for splitting the data.

(x <- rep(1:ceiling(nrow(df) / 5), each = 5, length.out = nrow(df)))
# [1] 1 1 1 1 1 2 2 2

Now we can split the data, loop the resulting list to make each element length 5, and coerce to data frame. Column names are created on-the-fly here. It may be more efficient to create them afterward.

as.data.frame(lapply(split(df$Name, paste0(names(df), x)), "length<-", 5))
#   Name1 Name2
# 1    AK    CT
# 2    AL    DC
# 3    AR    FL
# 4    AZ  <NA>
# 5    CO  <NA>