How to plot group bar plot with linear trendline in R

Question

This bar chart was created using MS excel. Now, I want to make a bar chart like this using Plotly or ggplot2 in R:

Here is my data:

data <- data.frame(Year=c('2020-2021' , '2021-2022' , '2021-2022' ,'2021-2022', '2021-2022'),
                   Quarter=c('Q4 (Jan-Mar)' ,'Q1 (April-June)' , 'Q2 (Jul-Sep)' ,'Q3 (Oct-Dec)' ,'Q4 (Jan-Mar)'),
                   Budget=c(153870.19 ,156815.8, 163501.44, 187891,246433),
                   Expenditure=c(69593.11, 104570, 98682, 104948, 130012))

How can I do this?

Answer 1

You can try

library(tidyverse)

data %>% 
  pivot_longer(-1:-2) %>% 
  mutate(name2 = paste(name, "planned")) %>% 
  ggplot(aes(interaction(Quarter,Year, sep = "\n"), value)) + 
   geom_col(aes(fill =name), 
            position  = position_dodge2(width = 0.6), alpha = 0.8) +
   geom_smooth(method = "lm", aes(color = name2, group=name), se =  F, 
               linetype = 2,position = position_dodge2(width = 0.6)) + 
   labs(x="") + 
   theme_bw() + 
   theme(legend.title = element_blank(),
         legend.position = "top")