Converting date string (fiscal month and year) to date

General
#1

Hi There.

I am trying to convert a column called: Book Fiscal Month String to an actual date.

The Month string date consists of dates such as: 2018-M01, 2018-M02, 2018-M03, etc.

Here's the catch, the dates are in fiscal months. I would like to convert the fiscal dates to the following:

M01: November
M02: December
M03: January
M04: February
M05: March
M06: April
M07: May
M08: June
M09: July
M10: August
M11: September
M12: October

I've scoured the web and haven't found anything to help me figure this out, or maybe I'm just being a nob.

Thanks for any insight.

#2

Is this what you mean?

library(stringr)
library(dplyr)

# Sample data on a copy/paste friendly format (replace this with your actual data frame)
sample_df <- data.frame(stringsAsFactors = FALSE,
                        book_fiscal_month = c("2018-M01", "2018-M02", "2018-M03")
)

# Relevant code
fiscal_months <- c("M01" = "November",
                   "M02" = "December",
                   "M03" = "January",
                   "M04" = "February",
                   "M05" = "March",
                   "M06" = "April",
                   "M07" = "May",
                   "M08" = "June",
                   "M09" = "July",
                   "M10" = "August",
                   "M11" = "September",
                   "M12" = "October")

sample_df %>% 
    mutate(date = str_replace_all(book_fiscal_month, fiscal_months))
#>   book_fiscal_month          date
#> 1          2018-M01 2018-November
#> 2          2018-M02 2018-December
#> 3          2018-M03  2018-January

Created on 2020-12-09 by the reprex package (v0.3.0.9001)

#3

Or something like this?

library(dplyr)
library(zoo)
library(stringr)


x <- str_remove("2018-M01", "M")
y <- as.yearmon(x)
as.Date(y)

> x
[1] "2018-01"
> y
[1] "Jan 2018"
> as.Date(y)
[1] "2018-01-01"
#4

Do you want the entries to be datetime objects formatted as strings or just strings?

#5

this seemed to work, although, it's not creating a new column of stored values.

COPA_RT$Book.Fiscal.Month.String <- str_remove("2018-M01", "M")
COPA_RT$Year <- as.yearmon(COPA_RT$Book.Fiscal.Month.String)
COPA_RT$Year <- as.Date(COPA_RT$Year)

#6

Actually, I misread your post and am not sure why yours doesn't create a new column. The notes below still apply though.

Using the reproducible example created by @andresrcs earlier.

library(stringr)
library(dplyr)
library(zoo)

# Sample data on a copy/paste friendly format (replace this with your actual data frame)
sample_df <- data.frame(stringsAsFactors = FALSE,
                        book_fiscal_month = c("2018-M01", "2018-M02", "2018-M03")
)

sample_df %>% 
  mutate(month = str_remove(book_fiscal_month, "M")) %>% 
  mutate(month = as.yearmon(month)) %>% 
  mutate(month = as.Date(month))

  book_fiscal_month      month
1          2018-M01 2018-01-01
2          2018-M02 2018-02-01
3          2018-M03 2018-03-01

Next time, create a reproducible example.

#7

What is the output of this then COPA_RT$Year?

Isn't it just a column in COPA_RT?

#8

that's what I thought but there's no additional column called Year with the new dates, which was puzzling.

#9

@williaml

this is so frustrating. In the original file, which is a csv, the column header is called: Book.Fiscal.Month.String with the associated dates of 2018-M01. That's it, so I don't understand why the following didn't create a new column called 'year' and store the new values into that column. Any help is greatly appreciated.

COPA_RT$Book.Fiscal.Month.String <- str_remove("2018-M01", "M")
COPA_RT$Year <- as.yearmon(COPA_RT$Book.Fiscal.Month.String)
COPA_RT$Year <- as.Date(COPA_RT$Year)

I even tried your other solution and still no luck in capturing a new column:

COPA_RT %>%
mutate(month = str_remove(Book.Fiscal.Month.String, "M")) %>%
mutate(month = as.yearmon(month)) %>%
mutate(month = as.Date(month))

write.csv(COPA_RT_Rev, file=paste("COPA_RT_Rev",fileTag,format(Sys.time(),"%d-%b-%Y %H_%M"),".csv", sep=""), row.names = FALSE)

It shows in the output that month is a new column with the new dates, but when I open up the file after it's been written to a csv, there's no new 'month' column at the end like it showed in the output.

#10

Yeah, that is strange. Can you provide a reproducible example of your dataset perhaps?

#11

Are you writing COPA_RT_Rev instead of COPA_RT?

Try:

write.csv(COPA_RT, file=paste("COPA_RT_Rev",fileTag,format(Sys.time(),"%d-%b-%Y %H_%M"),".csv", sep=""), row.names = FALSE)
#12

@williaml

Man, it's the little ish that always gets me! That worked! So, I see the new column of 'Year' but it's now only showing 01/01/18.

I guess, how do I change the M01 to a November such as the below?

This is what worked:

COPA_RT$Book.Fiscal.Month.String <- str_remove("2018-M01", "M")
COPA_RT$Year <- as.yearmon(COPA_RT$Book.Fiscal.Month.String)
COPA_RT$Year <- as.Date(COPA_RT$Year)

Relevant code

fiscal_months <- c("M01" = "November",
"M02" = "December",
"M03" = "January",
"M04" = "February",
"M05" = "March",
"M06" = "April",
"M07" = "May",
"M08" = "June",
"M09" = "July",
"M10" = "August",
"M11" = "September",
"M12" = "October")

#13

So just two months back then?

library(dplyr)
library(zoo)
library(stringr)
library(lubridate)

x <- str_remove("2018-M01", "M")
y <- as.yearmon(x)
z <- as.Date(y) %m+% months(-2)

> x 
[1] "2018-01"
> y
[1] "Jan 2018"
> z 
[1] "2017-11-01"

or %m+% months(+10) if it is November 2018.

1 Like
#14

YEAH!!!! You ARE THE ISH! Thank YOU!

1 Like
#15

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.