CODEWITHSUNDEEP
rdate
Jirka Čep
Jirka Čep

Reputation: 191

Convert quarterly data to monthly

I need to convert my data, which is on quarterly basis, to monthly, by dividing some variable by 3.

Example dataset:

df <- data.frame(Year = c(2018,2019,2020), qtr = c(1,3,2),
 amount = c(3,6,12), variable = c(5,6,7))
df

What I would need is to get months for every quarter, i.e. the final dataset would look like this:

data.frame(Year = c(2018,2018,2018,2019,2019,2019,2020,2020,2020), 
qtr = c(1,2,3,7,8,9,4,5,6), 
amount = c(1,1,1,2,2,2,4,4,4), 
variable = c(5,5,5,6,6,6,7,7,7))

Also, bonus question, how do I print the data frames in this environment

Upvotes: 1

Views: 1143

Answers (2)

Karthik S
Karthik S

Reputation: 11584

Does this work:

df %>% 
  mutate(qtr_start_mth = case_when(qtr == 1 ~ 1,
                                   qtr == 2 ~ 4,
                                   qtr == 3 ~ 7,
                                   qtr == 4 ~ 10),
         qtr_end_mth = case_when(qtr == 1 ~ 3,
                                 qtr == 2 ~ 6, 
                                 qtr == 3 ~ 9, 
                                 qtr == 4 ~ 12)) %>% 
  mutate(month = map2(qtr_start_mth, qtr_end_mth, `:`)) %>% 
  separate_rows() %>% 
  unnest(month) %>% 
  mutate(amount = amount /3) %>% 
  select(1,2,3,4,7)

# A tibble: 9 x 5
   Year   qtr amount variable month
  <dbl> <dbl>  <dbl>    <dbl> <int>
1  2018     1      1        5     1
2  2018     1      1        5     2
3  2018     1      1        5     3
4  2019     3      2        6     7
5  2019     3      2        6     8
6  2019     3      2        6     9
7  2020     2      4        7     4
8  2020     2      4        7     5
9  2020     2      4        7     6

Data used:

> dput(df)
structure(list(Year = c(2018, 2019, 2020), qtr = c(1, 3, 2), 
    amount = c(3, 6, 12), variable = c(5, 6, 7)), class = "data.frame", row.names = c(NA, 
-3L))
>

Upvotes: 2

zx8754
zx8754

Reputation: 56004

Using base:

do.call(rbind, 
        c(make.row.names = FALSE,
          lapply(split(df, df$Year), function(i){
            cbind(i, month = 1:3 + (i$qtr - 1) * 3, row.names = NULL)
          })))

#   Year qtr amount variable month
# 1 2018   1      3        5     1
# 2 2018   1      3        5     2
# 3 2018   1      3        5     3
# 4 2019   3      6        6     7
# 5 2019   3      6        6     8
# 6 2019   3      6        6     9
# 7 2020   2     12        7     4
# 8 2020   2     12        7     5
# 9 2020   2     12        7     6

Upvotes: 1

Related Questions