Monday, December 29, 2014

First Day of the Month, Using R

Future-proofing is an important concept when designing automated reports. One thing that can get out of hand over time is when you accumulate so many periods of data that your charts start to look overcrowded. You can solve for this by limiting the number of periods to, say, 13 (I like 13 for monthly data, because you get a full year of data, plus you can compare the month-over-month of the most recent data).


You could approach this by limiting your data to anything in the last 390 days (30 days x 13 months), but your starting period will likely be cut-off. You can fix this by finding the first day of the month for each record, then going back to get a full 13 months of data.

Here's a quick one-liner to get the first day of the month for a given date:  subtract the day of the month from the full date, then add 1.

# get some dates for the toy example:
df1 <- data.frame(YourDate=as.Date("2012-01-01")+seq(from=1,to=900,by=11))
df1$DayOne <- df1$YourDate - as.POSIXlt(df1$YourDate)$mday + 1

6 comments:

  1. My slacker way using substr instead of the POSIX functions (which I can never remember...)

    df1$DayOne <- paste0(substr(df1$YourDate,1,8),"01")

    ReplyDelete
  2. I'm a fan of `lubridate::floor_date()`, but this is clever!

    ReplyDelete
  3. I like 13 for monthly data, because you get a full year of data, plus you can compare the Year - Over - Year of the most recent data ...

    ReplyDelete
  4. df1$month <- as.Date(cut(df1$YourDate, 'month'))

    ReplyDelete
    Replies
    1. I like it; haven't used cut much before.

      Delete
  5. Also, there is a "timeFirstDayInMonth" function in the "timeDate" package:

    library(timeDate)
    df1$DayOne <- timeFirstDayInMonth(df1$YourDate)

    ReplyDelete