2

So I have a column that reads as such. There are 12 values for each year. This continues from 2002 to 2022. The task that I am challenged with is how to make these appear like below. How would I do such a transformation?

  col_name

  2002mq1m1
  2002mq1m2
  2002mq1m3
  2002mq2m1
  2002mq2m2
  2002mq2m3
  ......
  2002mq4m3


  2002m1
  2002m2
  2002m3
  2002m4
  2002m5
  2002m6
  .....
  2002m12
  2003m1
Tim Wilcox
  • 1,275
  • 2
  • 19
  • 43

1 Answers1

3

The key is to recognize the functional relationship which is

3 * (x - 1) + y = m

with

x = quarter

y = month number y of the quarter x

m = month [1, 12]

This can then be translated to a R formula which uses the correct elements of the string for x and y to calculate m.

df$col_name <- paste0(
   substr(df$col_name, 1, 5),
   3 * (as.numeric(substr(df$col_name, 7, 7)) - 1) + as.numeric(substr(df$col_name, 9, 9))
)
hyman
  • 315
  • 3
  • 13