Converting column months into rows in R

Question

I've received data in this format:

Returns_data = data.frame(Year = 2020:2022,
                          Jan = c("0.14", "0.05", "-0.02"),
                          Feb = c("0.1", "0.03", "-0.07"),
                          Mar = c("-0.09", "0.01", "-0.06"),
                          Apr = c("0.03", "0.08", "-0.03"),
                          May = c("0.01", "0.12", "0.04"),
                          Jun = c("-0.2", "-0.05", "-0.24"),
                          Jul = c("0.11", "-0.15", "0.21"),
                          Aug = c("-0.04", "-0.09", "0.12"),
                          Sep = c("-0.08", "0.03", "0.06"),
                          Oct = c("0.05", "0.02", "-0.02"),
                          Nov = c("0.03", "-0.01", "0.05"),
                          Dec = c("0.01", "0.17", "-0.01"))

How can I can convert it so that it looks like it returns one variable that there are only to column variables: Date and Returns.

Unclear what exactly you want this to look like—would the dates be e.g. "Feb 2021"? Either way, it's almost certainly a duplicate of one of the reshape-wide-to-long posts here, e.g. https://stackoverflow.com/q/2185252/5325862 — camille, Jun 13 '22 at 14:48

score 2 · Answer 1 · answered Jun 13 '22 at 14:48

You can do

tidyr::pivot_longer(Returns_data, -1, names_to = "Month") |>
  dplyr::transmute(Date = paste(Month, Year), Returns = value)
#> # A tibble: 36 x 2
#>    Date     Returns
#>    <chr>    <chr>  
#>  1 Jan 2020 0.14   
#>  2 Feb 2020 0.1    
#>  3 Mar 2020 -0.09  
#>  4 Apr 2020 0.03   
#>  5 May 2020 0.01   
#>  6 Jun 2020 -0.2   
#>  7 Jul 2020 0.11   
#>  8 Aug 2020 -0.04  
#>  9 Sep 2020 -0.08  
#> 10 Oct 2020 0.05   
#> # ... with 26 more rows

^{Created on 2022-06-13 by the reprex package (v2.0.1)}

score 2 · Answer 2 · answered Jun 13 '22 at 14:48

2

library(tidyverse)

Returns_data %>%  
  gather(-Year, key = "month", value = "return") %>%  
  unite(col = date, c("Year", "month")) # Can be dropped if you want separate Year and month columns. 

# A tibble: 36 x 2
   date     return
   <chr>    <chr> 
 1 2020_Jan 0.14  
 2 2021_Jan 0.05  
 3 2022_Jan -0.02 
 4 2020_Feb 0.1   
 5 2021_Feb 0.03  
 6 2022_Feb -0.07 
 7 2020_Mar -0.09 
 8 2021_Mar 0.01  
 9 2022_Mar -0.06 
10 2020_Apr 0.03  
# ... with 26 more rows

answered Jun 13 '22 at 14:48

Chamkrai

5,912
1
4
14

1

This uses outdated tidyverse syntax with `gather()` – Jun 13 '22 at 14:59
Yup, pivot_longer and pivot_wider are to be used instead of gather and spead. I think the API has been fairly stable for a while. – Jun 13 '22 at 15:04

Converting column months into rows in R

2 Answers2