How to create monthly periods in R-CodePudding

I have monthly data, and I would like to add another column for the period. The column would say M01 for January, M02 for February, M03 for March, and so on. Is there a way to do this?

This is what I have:

unemployment = data.frame(Month = c("Sept 2002", "Oct 2002", "Nov 2002", "Dec 2002", "Jan 2003", "Feb 2003"), 
                   Total = c(5.7, 5.7, 5.9, 
6, 5.8, 5.9))

> unemployment

      Month Total
1 Sept 2002   5.7
2  Oct 2002   5.7
3  Nov 2002   5.9
4  Dec 2002   6.0
5  Jan 2003   5.8
6  Feb 2003   5.9

This is what I want:

      Month Period Total
1 Sept 2002 M09      5.7
2  Oct 2002 M10      5.7
3  Nov 2002 M11      5.9
4  Dec 2002 M12      6.0
5  Jan 2003 M01      5.8
6  Feb 2003 M02      5.9

EDIT Updated code to show all 12 months

structure(list(Month = c("Jan", "Feb", "Mar", "Apr", "May", "June"
), Year = c("2003", "2003", "2003", "2003", "2003", "2003"), 
    Unemp_percent = c(5.8, 5.9, 5.9, 6, 6.1, 6.3)), row.names = 5:10, class = "data.frame")

CodePudding user response：

You can use mutate and gsub and match the first 3 letters with the built in month.abb data

library(dplyr)

unemployment |> 
  mutate(.after = Month,  
Period = paste0("M", match(gsub("(.{3})(.*)", "\\1", Month ), month.abb)))

      Month Period Total
1 Sept 2002     M9   5.7
2  Oct 2002    M10   5.7
3  Nov 2002    M11   5.9
4  Dec 2002    M12   6.0
5  Jan 2003     M1   5.8
6  Feb 2003     M2   5.9

CodePudding user response：

Using dplyr:

unemployment %>% 
  mutate(Period = case_when(grepl("Jan",Month) ~ "M01",
                            grepl("Feb",Month) ~ "M02",
                            grepl("Mar",Month) ~ "M03",
                            grepl("Apr",Month) ~ "M04",
                            grepl("May",Month) ~ "M05",
                            grepl("June",Month) ~ "M06",
                            grepl("July",Month) ~ "M07",
                            grepl("Aug",Month) ~ "M08",
                            grepl("Sept",Month) ~ "M09",
                            grepl("Oct",Month) ~ "M10",
                            grepl("Nov",Month) ~ "M11",
                            grepl("Dec",Month) ~ "M12"))

CodePudding user response：

Just make a reference table of months and periods, then left_join.

library(tidyverse)

months <- data.frame(mo = c("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec"),
                     Period = paste0("M", 1:12))

unemployment %>%
  mutate(mo = str_extract(Month, "^[:alpha:] ")) %>%
  left_join(months, by = "mo")

      Month Total   mo Period
1 Sept 2002   5.7 Sept     M9
2  Oct 2002   5.7  Oct    M10
3  Nov 2002   5.9  Nov    M11
4  Dec 2002   6.0  Dec    M12
5  Jan 2003   5.8  Jan     M1
6  Feb 2003   5.9  Feb     M2

CodePudding user response：

Here´s another alternative:

unemployment %>% 
  mutate(month = gsub("(^.{3}).*", "\\1", Month),
         Period = paste0("M", as.numeric(factor(x$month, month.abb)))) %>% 
  select(Month, Period, Total)

Output:

      Month Period Total
1 Sept 2002     M9   5.7
2  Oct 2002    M10   5.7
3  Nov 2002    M11   5.9
4  Dec 2002    M12   6.0
5  Jan 2003     M1   5.8
6  Feb 2003     M2   5.9