How to add missing months to a data frame?

Question

I have a dataset with three observations: January, February, and March. I would like to add the remaining months as observations of zero to the same datatable, but I'm having trouble appending these.

Here's my current code:

library(dplyr)

Period <- c("January 2015", "February 2015", "March 2015",
            "January 2016", "February 2016", "March 2016",
            "January 2017", "February 2017", "March 2017",
            "January 2018", "February 2018", "March 2018")

Month <- c("January", "February", "March",
           "January", "February", "March",
           "January", "February", "March",
           "January", "February", "March")

Dollars <- c(936, 753, 731, 
             667, 643, 588, 
             948, 894, 997, 
             774,745, 684)

dat <- data.frame(Period = Period, Month = Month, Dollars = Dollars)

dat2 <- dat %>%
  dplyr::select(Month, Dollars) %>%
  dplyr::group_by(Month) %>%
  dplyr::summarise(AvgDollars = mean(Dollars))

Any ideas for populating April through December in the dataset are greatly appreciated. Thanks in advance!

Thanks, I've taken a look, but I'm 1) a bit new to R, and 2) confused by the documentation. Do you have any experience with that package? Any advice? — Natasha Rostov
– Natasha Rostov, Commented Oct 3, 2018 at 20:08

Nova · Accepted Answer · 2018-10-04 14:26:45Z

3

Here's the way to do it using complete in one step:

library(tidyverse)

Then use complete:

dat2 <- data.frame(Period = Period, Month = Month, Dollars = Dollars) %>% 
  # make a "year" variable
  mutate(Year = word(Period, 2,2)) %>% 
  # remove period variable (we'll add it in later)
  select(-Period) %>% 
  # month.name is a base variable listing all months (thanks @Gregor).
  # nesting by "Year" lets complete know you only want the years listed in your dataset.
  complete(Month = month.name, nesting(Year), fill = list(Dollars = 0)) %>% 
  # Arrange by Year and month
  arrange(Year, Month) %>% 
  #remake the "period" variable 
  mutate(Period = paste(Month, Year)) %>% 
  group_by(Month) %>% 
  summarise(AvgDollars = mean(Dollars))

edited Oct 4, 2018 at 14:26

answered Oct 4, 2018 at 13:42

Nova

6,0202 gold badges47 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nghauran · Accepted Answer · 2018-10-03 20:33:11Z

Here is a two-step solution:

library(dplyr)
Sys.setlocale("LC_TIME", "English")
# first, define a dataframe with each month from January 2015 to December 2018
dat2 <- data.frame(Period = format(seq(as.Date("2015/1/1"),
                                       as.Date("2018/12/1"), by = "month"),
                                   format = "%B %Y"),
                   Month = substr(Period, 1, nchar(Period)-5)) 
# then, merge dat and dat2
dat %>%
  select(Period, Dollars) %>%
  right_join(dat2, by = "Period") %>%
  select(Period, Month, Dollars)
           Period    Month Dollars
1    January 2015  January     936
2   February 2015 February     753
3      March 2015    March     731
4      April 2015  January      NA
5        May 2015 February      NA
6       June 2015    March      NA
7       July 2015  January      NA
8     August 2015 February      NA
9  September 2015    March      NA
10   October 2015  January      NA
11  November 2015 February      NA
12  December 2015    March      NA
13   January 2016  January     667
14  February 2016 February     643
15     March 2016    March     588
16     April 2016  January      NA
17       May 2016 February      NA
18      June 2016    March      NA
19      July 2016  January      NA
20    August 2016 February      NA
21 September 2016    March      NA
22   October 2016  January      NA
23  November 2016 February      NA
24  December 2016    March      NA
25   January 2017  January     948
26  February 2017 February     894
27     March 2017    March     997
28     April 2017  January      NA
29       May 2017 February      NA
30      June 2017    March      NA
31      July 2017  January      NA
32    August 2017 February      NA
33 September 2017    March      NA
34   October 2017  January      NA
35  November 2017 February      NA
36  December 2017    March      NA
37   January 2018  January     774
38  February 2018 February     745
39     March 2018    March     684
40     April 2018  January      NA
41       May 2018 February      NA
42      June 2018    March      NA
43      July 2018  January      NA
44    August 2018 February      NA
45 September 2018    March      NA
46   October 2018  January      NA
47  November 2018 February      NA
48  December 2018    March      NA

Max · Accepted Answer · 2018-10-03 20:30:52Z

1

Maybe there's a more graceful solution with dplyr, but here is a quick solution without much typing:

dat <- rbind(data.frame(Period = Period, Month = Month, Dollars = Dollars),
             data.frame(Period = c(sapply(2015:2018, function(x) format(ISOdate(x,4:12,1),"%B %Y"))),
                        Month = c(sapply(2015:2018, function(x) format(ISOdate(x,4:12,1),"%B"))),
                        Dollars = 0))

answered Oct 3, 2018 at 20:30

Max

913 bronze badges

Collectives™ on Stack Overflow

How to add missing months to a data frame?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related