Count date observations in a month

Question

I have a dataframe containing daily prices of a stock exchange with corresponding dates for several years. These dates are tradingdates and is thus excluded weekends and holidays. Ex:

df$date <- c(as.Date("2017-03-30", "2017-03-31", "2017-04-03", "2017-04-04")

I have used lubridate to extract a column containg which month each date is in, but what I struggle with is creating a column that for each month of every year, calculates which number of trading day in the month it is. I.e. from the example, a counter that will start at 1 for 2017-04-03 as this is the first observation of the month and not 3 as it is the third day of the month and end at the last observation of the month. So that the column would look like this:

df$DayofMonth <- c(22, 23, 1, 2)

and not

df$DayofMonth <- c(30, 31, 3, 4)

Is there anybody that can help me?

Answer 1

Maybe this helps:

library(data.table)
library(stringr)
df <-  setDT(df)
df[,YearMonth:=str_sub(Date,1,7)]
df[, DayofMonth := seq(.N), by = YearMonth]

You have a column called YearMonth with values like these '2020-01'. Then for each group (month) you give each date an index which in your case would correspond to the trading day.

As you can see this would lead to 1 for the date '2017-04-03' since it is the first trading day that month. This works if your df is sorted from first date to latest date.

Answer 2

There is a way using lubridate to extract the date components and dplyr.

library(dplyr)
library(lubridate)
df <- data.frame(date = as.Date(c("2017-03-30", "2017-03-31", "2017-04-03", "2017-04-04")))
df %>%
  mutate(month = month(date),
         year = year(date),
         day = day(date)) %>%
  group_by(year, month) %>%
  mutate(DayofMonth = day - min(day) + 1) 
# A tibble: 4 x 5
# Groups:   year, month [2]
  date       month  year   day DayofMonth
  <date>     <dbl> <dbl> <int>      <dbl>
1 2017-03-30     3  2017    30          1
2 2017-03-31     3  2017    31          2
3 2017-04-03     4  2017     3          1
4 2017-04-04     4  2017     4          2

Answer 3

You can try the following :

• For each date find out the first day of that month.
• Count how many working days are present between first_day_of_month and current date.

library(dplyr)  
library(lubridate)

df %>%
  mutate(first_day_of_month = floor_date(date, 'month'), 
         day_of_month = purrr::map2_dbl(first_day_of_month, date, 
         ~sum(!weekdays(seq(.x, .y, by = 'day')) %in% c('Saturday', 'Sunday'))))

#        date first_day_of_month day_of_month
#1 2017-03-30         2017-03-01           22
#2 2017-03-31         2017-03-01           23
#3 2017-04-03         2017-04-01            1
#4 2017-04-04         2017-04-01            2

You can drop the first_day_of_month column if not needed.

data

df <- data.frame(Date = as.Date(c("2017-03-30", "2017-03-31", 
                                  "2017-04-03", "2017-04-04")))