Converting date string (fiscal month and year) to date

girlzcancode · December 9, 2020, 11:22pm

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.

andresrcs · December 9, 2020, 11:58pm

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)}

williaml · December 10, 2020, 12:02am

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"

technocrat · December 10, 2020, 1:32am

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

girlzcancode · December 10, 2020, 4:32am

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)

williaml · December 10, 2020, 4:41am

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.

FAQ: What's a reproducible example (`reprex`) and how do I create one? meta

Why reprex? Getting unstuck is hard. Your first step here is usually to create a reprex, or reproducible example. The goal of a reprex is to package your code, and information about your problem so that others can run it and feel your pain. Then, hopefully, folks can more easily provide a solution. What's in a Reproducible Example? Parts of a reproducible example: background information - Describe what you are trying to do. What have you already done? complete set up - include any library() calls and data to reproduce your issue. data for a reprex: Here's a discussion on setting up data for a reprex make it run - include the minimal code required to reproduce your error on the data…

williaml · December 10, 2020, 4:58am

What is the output of this then COPA_RT$Year?

Isn't it just a column in COPA_RT?

girlzcancode · December 10, 2020, 5:23am

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

girlzcancode · December 10, 2020, 7:53pm

@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.

williaml · December 10, 2020, 9:19pm

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

williaml · December 10, 2020, 10:12pm

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)

girlzcancode · December 10, 2020, 10:51pm

@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")

williaml · December 10, 2020, 11:25pm

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.

girlzcancode · December 11, 2020, 12:25am

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

system · January 1, 2021, 12:25am

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.