How to assign date quarters to integer values-CodePudding

I have a dataset that includes a date column consisting of the following:

date
0.25
0.50
0.75
1.00
1.25
1.50
1.75
2.00
2.25
(etc)

This indicates yearly quarters. What I am trying to do is convert each quarter to have a corresponding value, 0.25 being the first quarter, 0.50 being the second and so on.

What the outcome would look like is a new column with values 1:4 for each year's quarter. Something like:

date     quarter
0.25      1
0.50      2
0.75      3
1.00      4
1.25      1
1.50      2
1.75      3
2.00      4
(etc)

I know this could be done a long way (the dreaded for loop) but I am hoping a more succinct bit of code is possible. Thanks

CodePudding user response：

A possible solution:

library(dplyr)

df %>% 
  mutate(quarter = date - floor(date), 
         quarter = case_when(quarter == 0.25 ~ 1,
                             quarter == 0.50 ~ 2,
                             quarter == 0.75 ~ 3,
                             TRUE ~ 4)) 

#>   date quarter
#> 1 0.25       1
#> 2 0.50       2
#> 3 0.75       3
#> 4 1.00       4
#> 5 1.25       1
#> 6 1.50       2
#> 7 1.75       3
#> 8 2.00       4
#> 9 2.25       1

CodePudding user response：

Just do

date <- c(.25,.5,.75,1,1.25,1.5,1.75,2)

quarter <- (date-floor(date))/.25
quarter <- ifelse(quarter == 0, 4, quarter)
quarter
[1] 1 2 3 4 1 2 3 4

With (date-floor(date)) you keep just the decimal places of the number. Then you divide by .25 to see what quarter it is. And finally replacing zero by four.

EDIT

I see my answer has same logic as the other one. I keep it anyway because the explanation might help.

CodePudding user response：

Multiply the date by 4 to give a whole number value and subtract 1 giving 0, 1, 2, ... . Then take the remainder after dividing by 4 and add 1. Of the solutions we present below this one is the shortest and the solutions in this section are equally general and do not require that the quarters be consecutive or start with the first quarter.

Note that x %% 4 means the remainder after dividing by 4 and x %% 1 is the remainder after dividing by 1, i.e. the fractional part.

transform(DF, quarter = (4 * date - 1) %% 4   1)

An alternative is to convert to a factor and take the integer levels

transform(DF, quarter = as.integer(factor((date - 0.25) %% 1)))

Yet another approach is to use the yearqtr class in zoo. It represents year/quarter dates internally using year plus 0, 1/4, 1/2 and 3/4 for the 4 quarters.

library(zoo)
transform(DF, quarter = cycle(as.yearqtr(date - 0.25)))

Although a bit laborious this one is quite simple:

transform(DF, quarter = match(date %% 1, c(0.25, 0.5, 0.75, 0.00)))

Consecutive quarters

If we knew that the quarters were consecutive and start with the first quarter we could repeat the sequence 1, 2, 3, 4 to the required length like this. While less general it is very simple.

transform(DF, quarter = rep(1:4, length = nrow(DF)))

Another way if we knew that that the quarters were consecutive and start with the first quarter is to convert date to a ts series and use cycle.

transform(DF, quarter = c(cycle(ts(date, freq = 4))))

Note

The input in reproducible form:

DF <-
structure(list(date = c(0.25, 0.5, 0.75, 1, 1.25, 1.5, 1.75, 
2, 2.25)), class = "data.frame", row.names = c(NA, -9L))