Taking columns names, splitting them and melting them into a dataframe

Question

I have a data frame of housing values spanning over 20~ years. The column names are the months and years i.e. 04-1996, 05-1996, 06-1996 and so on. I want to plot time series data for those months and years and have to take those column names and plot them into the resulting data frame.

I tried this to test it out:

df2<-melt(df, na.rm=T, id.vars=c("RegionName","CountyName"), measure.vars=c("04-1996", "05-1996", "06-1996"))

And it gave me the years and such in the respective rows, which is good. I'm close.

I currently have:

    Region  City      Apr-1996 May-1996  June-1996
1   10025   New York  10000    10000     10000
2   10023   New York  10000    10000     10000
3   10128   New York  10000    10000     10000

All the way until May 2015.

I would ideally like something that looks like:

    Region  City      Month Year    Value
1   10025   New York  04    1996    123100
2   10023   New York  05    1997    72700
3   10128   New York  06    1998    91600

I'm seeing errors saying Error: id variables not found in data: Month-Year when I tried adding id.vars to my melt.

But when I go to create a for loop to go through all the column names, it craps out on me giving me errors. Any direction would be awesome.

Could show the sample input corresponding to your desired output? We need to know what you're starting with. Please use `dput()` so class an structure information are preserved and it is copy/pasteable. — Gregor Thomas, Nov 04 '20 at 21:01

score 1 · Accepted Answer · edited Nov 05 '20 at 16:16

Here is an example taken from what I understood of your data:

library(dplyr)
lirary(tidyr)

tb <- tibble(Region = c(10025, 10023),
       City = c("New York","New York"),
       `04-1996` = c(10, 20),
       `05-1996` = c(20, 30))

Result:

# A tibble: 2 x 4
  Region City     `04-1996` `05-1996`
   <dbl> <chr>        <dbl>     <dbl>
1  10025 New York        10        20
2  10023 New York        20        30

Calculation:

tb %>% 
  tidyr::pivot_longer(-c(Region, City), names_to = "M-Y", values_to = "Value") %>% 
  tidyr::separate("M-Y", into = c("Month", "Year"), sep = "-")

Result:

  Region City     Month Year  Value
   <dbl> <chr>    <chr> <chr> <dbl>
1  10025 New York 04    1996     10
2  10025 New York 05    1996     20
3  10023 New York 04    1996     20
4  10023 New York 05    1996     30

Thank you!! This is exactly what I was looking for. – WittyAFC Nov 04 '20 at 21:31 — WittyAFC, Nov 04 '20 at 21:31

Taking columns names, splitting them and melting them into a dataframe

1 Answers1