Dynamic Bar Chart w/ Line Overlay ggplot2 R

Question

I'm trying to replicate the graph found here: Bar&LineChartCombo. What I would like to do is create a bar chart which will include the total Amount value at the top of the bar chart. I would also like to include a total Rate line plot over the bar chart, ideally starting in the middle of the bar chart. My problem is, I can get the total Amount above the bar chart but I cannot figure out the correct way to overlay just the total Rate and display the total Rate value. If this is possible, I would also like to exclude a secondary axis but from what I've read it is needed to properly scale the secondary graph being overlaid.

Below is a random graph I found which demonstrates the point and line overlay I'm looking to create:

df<-data.frame(
    Group=c("A","B","C","D","E","Total","A","B","C","D","E","Total","A","B","C","D","E","Total"),
    Date=c("Feb 2021","Feb 2021","Feb 2021","Feb 2021","Feb 2021","Feb 2021","Apr 2021","Apr 2021","Apr 2021","Apr 2021","Apr 2021","Apr 2021","Jun 2021","Jun 2021","Jun 2021","Jun 2021","Jun 2021","Jun 2021"),
    Amount=c(100,200,300,400,500,1500,200,400,600,800,1000,3000,400,800,1200,1600,2000,6000),
    Rate=c(3.5,3.45,3.39,3.44,3.51,3.48,3.53,3.42,3.41,3.47,3.52,3.50,3.51,3.40,3.44,3.45,3.50,3.49))


ggplot(df,aes(x=factor(Date), y=Amount,fill=Group))  + 
    geom_bar(stat="identity", colour="sienna3")+
    geom_line(aes(x=factor(Date), y=(Rate)*max(Amount)),stat="identity")+
    geom_text(aes(label=Rate, x=factor(Date), y=Rate*max(Amount)), colour="black")+
    geom_text(aes(label = format(round(stat(y),0),big.mark=","), group = factor(Date)),stat = 'summary', fun = sum, vjust = -.25)+
    scale_y_continuous(sec.axis = sec_axis(~./max(df$Amount)))

Are you sure you want the `totals` in your data? It makes the lines you're trying to plot very close together — neuron, Nov 30 '21 at 03:36
I edited my post with a snapshot example of what I'm hoping to recreate. — a_js12, Nov 30 '21 at 14:35
No I do need the stacked bar chart, the referenced snapshot's point/line overlay is what I'm hoping to create. The purpose of the point/line overlay is to show the `rate` sensitivity to changes in the `amount` over time. — a_js12, Nov 30 '21 at 15:05
The problem that I am running into is that `(Rate)*(Amount)` produces a larger number than the bars themselves — neuron, Nov 30 '21 at 15:59

score 1 · Answer 1 · answered Nov 30 '21 at 03:51

So it seems that the problem is when you use max it is taking the max value for that column, which is 6000. 6000 multiplied by any of the rate values is a much larger number than any of the amounts.

Here is a plot with just the lines:

ggplot(df,aes(x=Date, y=Amount, group=Group, fill = Group)) + 
  #geom_bar(stat="identity", colour="sienna3")+
  geom_line(aes(x=Date, y=(Rate)*max(Amount))) +
  geom_text(aes(label=Rate, x=Date, y=Rate*max(Amount)), colour="black")+
  #geom_text(aes(label = format(round(stat(y),0),big.mark=","), group = factor(Date)),stat = 'summary', fun = sum, vjust = -.25)+
  scale_y_continuous(sec.axis = sec_axis(~./max(df$Amount)))

Here is what the plot looks like using max

ggplot(df,aes(x=Date, y=Amount, group=Group, fill = Group)) + 
  geom_bar(stat="identity", colour="sienna3")+
  geom_line(aes(x=Date, y=(Rate)*max(Amount))) +
  geom_text(aes(label=Rate, x=Date, y=Rate*max(Amount)), colour="black")+
  geom_text(aes(label = format(round(stat(y),0),big.mark=","), group = factor(Date)),stat = 'summary', fun = sum, vjust = -.25)+
  scale_y_continuous(sec.axis = sec_axis(~./max(df$Amount)))

Here is what the plot looks like if you remove max

ggplot(df,aes(x=Date, y=Amount, group=Group, fill = Group)) + 
  geom_bar(stat="identity", colour="sienna3")+
  geom_line(aes(x=Date, y=(Rate)*(Amount)),stat="identity") +
  geom_text(aes(label=Rate, x=Date, y=Rate*(Amount)), colour="black")+
  geom_text(aes(label = format(round(stat(y),0),big.mark=","), group = factor(Date)),stat = 'summary', fun = sum, vjust = -.25)+
  scale_y_continuous(sec.axis = sec_axis(~./max(df$Amount)))

Thanks for the detailed feedback, this is helpful! To respond to your initial comment, I could get rid of totals but I'd need to create a second dataset which would include just the total rates. I'm doing some reading now and it seems like its possible to create a ggplot with two datasets but from the examples I've seen, the datasets are of equal length. Ideally, my chart would include only the total rate as the overlay (line and values outside the bar chart in your graph above) which is why I'm now looking into two datasets. — a_js12, Nov 30 '21 at 14:04

Dynamic Bar Chart w/ Line Overlay ggplot2 R

1 Answers1