R: Turn Months into Quarters

May 23, 2022

I have a dataset that looks like this:

> ex
 # A tibble: 10 × 2
 tenor   delivery_window
 <chr>   <chr>          
 1 month   Nov 22         
 2 quarter Jan 22         
 3 year    Cal 24         
 4 year    Cal 22         
 5 month   Feb 22         
 6 quarter Jan 21         
 7 month   Sep 22         
 8 quarter Jan 21         
 9 month   Jun 21         
10 month   Aug 21

And which I want to turn into something like this:

> ex
 # A tibble: 10 × 3
 tenor   delivery_window new_tenor
 <chr>   <chr>           <chr>    
 1 month   Nov 22          Nov 22   
 2 quarter Jan 22          Q1 22    
 3 year    Cal 24          Cal 24   
 4 year    Cal 22          Cal 22   
 5 month   Feb 22          Feb 22   
 6 quarter Jan 21          Q1 21    
 7 month   Sep 22          Sep 22   
 8 quarter Jan 21          Q1 21    
 9 month   Jun 21          Jun 21   
10 month   Aug 21          Aug 21

That is, if the tenor is quarter, I want to show only the quarter corresponding to the delivery window, not the month. Monthly and Yearly tenors can remain as they are.

Can someone please give me a hint as to how to achieve this? Thank you in advance.

EDIT

The new_tenor should be Q1 YY for months from Jan to Mar, Q2 YY for months from Apr YY to Jun YY, Q3 YY for months from Jul YY to Sep YY, and Q4 YY for months from Oct YY to Dec YY.

>Solution :

We can convert to yearqtr with as.yearqtr (from zoo), and use case_when to replace the elements in ‘delivery_window’ with the converted value

library(dplyr)
library(stringr)
library(zoo)
ex <- ex %>%
        mutate(new_tenor = case_when(tenor == 'quarter' 
   ~ str_replace(as.yearqtr(paste('1', delivery_window), 
       '%d %b %Y'), "(\\d+) (\\w+)", "\\2 \\1")
, TRUE ~ delivery_window))