melt and cast dataframe adding new variables

Question

Im running into a bit of trouble trying to reshape my dataframe: sites_w_richness DATASET LINK HERE.

I have been trying to get the data to organize into four columns

1. site
2. longitude
3. latitude
4. year
5. scenario
6. richness value.

Unfortunatley the year (2050 and 2070) and scenario (2.6 and 4.5) data are embedded in the headers of columns 3 to 6 of the dataset. Is there a way to extract the information from the headers and recast the dataframe into the six columns I need?

I have tried this but all I am getting are na's.

#melt and cast to format 
require (reshape)
sites_richness<-read.csv('sites_w_richness.csv', header=T)
rich.melt<-melt (sites_richness, id=1:2)
rich.cast<-cast(rich.melt, Longitude ~ Latitude ~ variable ~ value)

Answer 1

reshape has been superceeded by reshape2, which is much faster and memory efficient. It also has a function colsplit which performs as you wish

library(reshape2)
# melt (using site, longitude and latitude as ids)
rich.melt <- melt(site_richness, id = 1:3)
# create a variable without `site_richness_` prefix
rich.melt$v2 <- rich.melt$variable
levels(rich.melt$v2) <- gsub('^site_richness_','',levels(rich.melt$v2))
# use colsplit to split on `_` and combine with the newest data
rich.melt2 <- cbind(rich.melt, colsplit(rich.melt$v2, pattern = '_', names = c('scenario','year')))
# drop unwanted columns and reorder
rich.melt.final <- rich.melt2[, c("Site", "Longitude", "Latitude", 
                                 "scenario", "year", "species_richness")]
head(rich.melt.final)
     Site Longitude Latitude scenario year species_richness
# 1  ABSF  -78.6492  37.4343      2.6 2050                4
# 2 ALLSP  -74.1487  40.1481      2.6 2050               31
# 3  ANSF  -71.9341  42.7743      2.6 2050               49
# 4  ARSP  -68.0148  46.6067      2.6 2050               19
# 5  BAXP  -68.8520  46.1645      2.6 2050               23
# 6  BBSP  -71.3822  43.1643      2.6 2050               35