How to create a data frame from a data frame?

enter image description here

So I managed to extract the data I needed from a CSV file and it looks like the table on the picture. Obviously the columns represent the years, and each row represents the month 1 = jan, 2 = february ... How can I do to create a new data frame that has 120 rows: one for each month of the years 2004 to 2013; and two columns: one being the date and one being the value associated with the date (year and month)?

Thank you

1 answer

  • answered 2018-03-13 20:51 mysteRious

    You can use melt to do something like this (it's called "going from wide to long format"). I simulated a data frame x that looks like your image:

    x <- matrix(rexp(100, rate=.1), ncol=10) # simulate a 12 col x 10 row matrix
    mos <- seq(1,12)
    yrs <- seq(2004,2013)
    colnames(x) <- yrs
    x.df <- cbind(mos,x)
    data_long <- melt(x.df, id.vars=c("mos"))