Converting Yearmon Column to Last Date of the Month in R

Converting yearmon column to last date of the month in R

If the Date variable is an actual yearmon class vector, from the zoo package, the as.Date.yearmon method can do what you want via its argument frac.

Using your data, and assuming that the Date was originally a character vector

library("zoo")
df <- data.frame(Date = c("2014-07", "2014-08", "2014-09"),
                 Arrivals = c(100, 150, 200))

I convert this to a yearmon vector:

df <- transform(df, Date2 = as.yearmon(Date))

Assuming this is what you have, then you can achieve what you want using as.Date() with frac = 1:

df <- transform(df, Date3 = as.Date(Date2, frac = 1))

which gives:

> df
     Date Arrivals    Date2      Date3
1 2014-07      100 Jul 2014 2014-07-31
2 2014-08      150 Aug 2014 2014-08-31
3 2014-09      200 Sep 2014 2014-09-30

That shows the individual steps. If you only want the final Date this is a one-liner

## assuming `Date` is a `yearmon` object
df <- transform(df, Date = as.Date(Date, frac = 1))
## or if not a `yearmon`
df <- transform(df, Date = as.Date(as.yearmon(Date), frac = 1))

The argument frac in the fraction of the month to assign to the resulting dates when converting from yearmon objects to Date objects. Hence, to get the first day of the month, rather than convert to a character and paste on "-01" as your Question showed, it's better to coerce to a Date object with frac = 0.

If the Date in your df is not a yearmon class object, then you can solve your problem by converting it to one and then using the as.Date() method as described above.

Convert YearQtr series to End of the month Date

Using zoo

library(zoo)
qrtrs = c("1986Q1","1986Q2","1986Q3","1986Q4")
mnths = sapply(1:3, \(i) as.Date(as.yearmon(as.yearqtr(qrtrs)) + i/12) - 1)
sort(as.Date(mnths))

output

[1] "1986-01-31" "1986-02-28" "1986-03-31" "1986-04-30" "1986-05-31" "1986-06-30" "1986-07-31"
 [8] "1986-08-31" "1986-09-30" "1986-10-31" "1986-11-30" "1986-12-31"

This also works for leap years

Calculating first and last day of month from a yearmon object

We can use

library(dplyr)
library(lubridate)
library(zoo)
df %>% 
   mutate(firstday = day(year_mon), last = day(as.Date(year_mon, frac = 1)))

convert date in Month-Year format to last date of month

Using lubridate we can convert MonthYear to date object and use ceiling_date with unit = "Month" and subtract 1 day from it to get last day of the month.

library(lubridate)
ceiling_date(dmy(paste("01", dataset$MonthYear)), unit = "month") - 1
#[1] "2015-05-31" "2015-04-30" "2016-01-31" "2016-02-29" "2018-12-31"

Create end of the month date from a date variable

To get the end of months you could just create a Date vector containing the 1st of all the subsequent months and subtract 1 day.

date.end.month <- seq(as.Date("2012-02-01"),length=4,by="months")-1
date.end.month
[1] "2012-01-31" "2012-02-29" "2012-03-31" "2012-04-30"

How to convert dataframe with YearMonth column to times series in R

One option is to convert YearMonth to 1st date of a month and generate ts.

library(zoo)
dataset$YearMonth = as.Date(as.yearmon(as.character(dataset$YearMonth),"%Y%m"), frac = 0)
dataset
#   year  YearMonth sales
# 1 2017 2017-01-01   100
# 2 2017 2017-02-01   200
# 3 2017 2017-03-01   300
# 4 2017 2017-04-01   400

Just for ts another option is as:

dataset$YearMonth = as.yearmon(as.character(dataset$YearMonth),"%Y%m")

as.ts(dataset[-1])
# Time Series:
#   Start = 1 
# End = 4 
# Frequency = 1 
# YearMonth sales
# 1  2017.000   100
# 2  2017.083   200
# 3  2017.167   300
# 4  2017.250   400

Filter data to get last month for each month in R

We can convert to year month as grouping column and slice

library(zoo)
library(dplyr)
x %>% 
   mutate(date = as.Date(date)) %>% 
   arrange(date) %>%
   group_by(yearmon = as.yearmon(date)) %>% 
   slice(n()) %>%
   ungroup %>%
   select(-yearmon)
# A tibble: 3 x 2
#  date       value1
#  <date>      <dbl>
#1 2018-06-23     23
#2 2018-09-12     22
#3 2019-09-23     23

---

Or create the 'yearmon' column with format from base R

library(dplyr)
x %>% 
   mutate(date = as.Date(date)) %>% 
   arrange(date) %>%
   group_by(yearmon = format(date, '%Y-%m')) %>% 
   slice(n()) %>%
   ungroup %>%
   select(-yearmon)

---

Related Topics