Setting colour palette based on month in a %b-%Y variable in ggplot2

Question

I have a data frame which includes a count grouped by MonthYear in the format %b-%Y using a rolling 12-month data frame.

I have created a plot using ggplot2 and plotly using the code below with the resulting graph.

# Create rolling 12-month data frame

end_date <- Sys.Date()  # Get the current date
start_date <- end_date %m-% months(12)  # Calculate 12 months ago

aefis_filtered <- aefis %>%
  filter(gen_reportdate >= start_date & gen_reportdate <= end_date)

# Create 12-month aefi dataframe

overviewdt <- aefis_filtered %>%
  mutate(MonthYear = format(ymd(gen_reportdate), "%b-%Y")) %>%
  group_by(MonthYear) %>%
  summarise(Count = n_distinct(other_record_id)) |> 
  mutate(Total = cumsum(Count)) |> as.data.frame()

# Plot

overall <- ggplot(overviewdt, aes(x=MonthYear, y=Count, fill=MonthYear, text = paste("Month - Year:", MonthYear, "\nCount:", Count))) +
  geom_bar(stat = "identity", width=0.7) +
  scale_fill_manual(values = c(vax_light_blue,vax_mid_blue,vax_dark_blue,vax_yellow,vax_orange,vax_dark_orange,
                               vax_light_green,vax_green,vax_dark_green,vax_light_purple,
                               vax_mid_purple,vax_dark_purple,vax_light_blue)) +
  labs(x = "", y = "") +
  theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank(),
        panel.background = element_blank(), axis.line = element_line(colour = "black"), axis.text = element_text(color = "black"),
        legend.position = "none")

ggplotly(overall, tooltip = 'text')

Rolling 12-month graph of AEFIs by month and year

As it uses a rolling 12-month dataset, the graph displays January for both 2023 and 2024. Is there a way to alter my code so that I can set a colour based on the month i.e. Jan-2023 and Jan-2024 would = vax_light_blue, Feb-2023 and Feb-2024 would = vax_mid_blue etc so that I don't have to change the scale_fill_manual each month?

I tried the following code but it resulted in all the bars being grey

month_colour_mapping <- c(
  "Jan" = vax_light_blue,
  "Feb" = vax_mid_blue,
  "Mar" = vax_dark_blue,
  "Apr" = vax_yellow,
  "May" = vax_orange,
  "Jun" = vax_dark_orange,
  "Jul" = vax_light_green,
  "Aug" = vax_green,
  "Sep" = vax_dark_green,
  "Oct" = vax_light_purple,
  "Nov" = vax_mid_purple,
  "Dec" = vax_dark_purple
)

overall <- ggplot(overviewdt, aes(x = MonthYear, y = Count, fill = MonthYear, 
                                  text = paste("Month - Year:", MonthYear, "\nCount:", Count))) +
  geom_bar(stat = "identity", width = 0.7) +
  scale_fill_manual(values = month_colour_mapping) +
  labs(x = "", y = "") +
  theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank(),
        panel.background = element_blank(), axis.line = element_line(colour = "black"),
        axis.text = element_text(color = "black"), legend.position = "none")

ggplotly(overall, tooltip = 'text')

Hoping someone can help. Please let me know if I haven't included enough information. Thanks in advance

EDIT: output of overviewdt dataframe below: structure(list(MonthYear = structure(1:13, levels = c("Jan-2023", "Feb-2023", "Mar-2023", "Apr-2023", "May-2023", "Jun-2023", "Jul-2023", "Aug-2023", "Sep-2023", "Oct-2023", "Nov-2023", "Dec-2023", "Jan-2024" ), class = "factor"), Count = c(7L, 13L, 14L, 6L, 11L, 4L, 1L, 5L, 2L, 4L, 5L, 5L, 3L), Total = c(7L, 20L, 34L, 40L, 51L, 55L, 56L, 61L, 63L, 67L, 72L, 77L, 80L)), row.names = c(NA, -13L), class = "data.frame")

Answer 1

You have to get from MonthYear the months and years (strictly only months), you can use different ways, this is one of:

overviewdt <- overviewdt %>%
  mutate(Month = str_sub(MonthYear, 1, 3),
         Year = str_sub(MonthYear, 5, 8))

and plot the df calling in fill Month and not MonthYear:

overall <- overviewdt %>%
  ggplot(aes(x = MonthYear, y=Count, fill=Month, text = paste("Month - Year:", MonthYear, "\nCount:", Count))) +
  geom_bar(stat = "identity", width=0.7) +
  scale_fill_manual(values = pal6) +
  labs(x = "", y = "") +
  theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank(),
        panel.background = element_blank(), axis.line = element_line(colour = "black"), axis.text = element_text(color = "black"),
        legend.position = "none", axis.text.x = element_text(angle = 90, hjust = 1))

ggplotly(overall, tooltip = 'text')

Answer 2

your issue might be related to the format of the MonthYear variable in your dataset. scale_fill_manual expects discrete values for the fill aesthetic. if the MonthYear is currently in a character or factor format, it might not match exactly with your color mapping. you can resolve this by converting the MonthYear variable into a factor with predefined levels based on the desired order. you can do this using the factor function and specifying the levels in the desired order.

# Define the desired order of months
month_order <- c("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

# Convert MonthYear to factor with specified levels
overviewdt$MonthYear <- factor(overviewdt$MonthYear, levels = month_order)

# Create the color mapping
month_colour_mapping <- c(
  "Jan" = vax_light_blue,
  "Feb" = vax_mid_blue,
  "Mar" = vax_dark_blue,
  "Apr" = vax_yellow,
  "May" = vax_orange,
  "Jun" = vax_dark_orange,
  "Jul" = vax_light_green,
  "Aug" = vax_green,
  "Sep" = vax_dark_green,
  "Oct" = vax_light_purple,
  "Nov" = vax_mid_purple,
  "Dec" = vax_dark_purple
)

# Plot with the modified MonthYear variable
overall <- ggplot(overviewdt, aes(x = MonthYear, y = Count, fill = MonthYear, 
                                  text = paste("Month - Year:", MonthYear, "\nCount:", Count))) +
  geom_bar(stat = "identity", width = 0.7) +
  scale_fill_manual(values = month_colour_mapping) +
  labs(x = "", y = "") +
  theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank(),
        panel.background = element_blank(), axis.line = element_line(colour = "black"),
        axis.text = element_text(color = "black"), legend.position = "none")

ggplotly(overall, tooltip = 'text')