[datatable-help] question on wide to long conversion

[Bacou, Melanie]

You will need to first combine the first 2 rows into a single header row 
with unique header names before you can melt it, e.g.

require(stringr)

dt <- fread("path to your csv file", skip=1)
setnames(dt, 4:11, c(paste0("pH", 1976:1981), paste0("SO4", 1976:1981)))
dt <- melt(dt, id.vars=1:3, variable.name="year")
dt[, variable := str_sub(year, 1, -5)]
dt[, year := str_sub(year, -4, -1)]
dt <- dcast(dt, Lake+year~variable)

--Mel.



On 9/9/2016 12:56 PM, chaceh wrote:
> I have a data table that I'd like to convert from wide to long. I know of 2
> good ways to do this, either with melt() or reshape(). The problem is, my
> first row (i.e. column names) are not easy to manipulate. here's a screen
> shot of the data:
>
> <http://r.789695.n4.nabble.com/file/n4724572/Screen_Shot_2016-09-09_at_12.png>
>
> What I want to do, is create a column for the lake, year, ph, and SO4, so it
> would look something like this:
>
> Lake     year    pH     SO4
> 1          1976   4.59   6.5
> 1          1977   -9999 -9999
> 1          1978   4.48   7.3
> 1          1981   4.63   6
> 2          1976   4.97   5.5
> and so on
>
> because the column names are not the year, I'm not exactly sure how to melt
> or reshape it to fit what I want. any suggestions? Thanks a lot for your
> help
>
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/question-on-wide-to-long-conversion-tp4724572.html
> Sent from the datatable-help mailing list archive at Nabble.com.
> _______________________________________________
> datatable-help mailing list
> datatable-help at lists.r-forge.r-project.org
> https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help