CODEWITHSUNDEEP
rstrsplit
WittyAFC
WittyAFC

Reputation: 5

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

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.

Upvotes: 0

Views: 214

Answers (1)

DPH
DPH

Reputation: 4344

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

Upvotes: 1

Related Questions