Grouped bar and line charts on R using ggplot2

Question

I have a table made in Excel, converted to a dataframe it looks like this:

                Sistema DetNP DetProg Indisp Total TotalMax
1     MOTOR EQUIPO MOVIL   744     977  3.54%  1721      977
2         SISTEMA CHASIS    36     873  1.87%   909      873
3       TREN DE POTENCIA   247     527  1.59%   774      527
4                    PMs     0     736  1.52%   736      736
5    SUSPENSION Y RUEDAS   100     346  0.92%   446      346
6     SISTEMA HIDRAULICO   118     181  0.62%   299      181
7  SISTEMA ELECTRICO 24V   144      30  0.36%   174      144
8                 CABINA   116      20  0.28%   136      116
9    SISTEMA LUBRICACION    24      40  0.13%    64       40
10     SISTEMA DE FRENOS    17      44  0.13%    61       44

From this, i need to make this chart using only ggplot if possible:

enter image description here

So far by now using reshape and melting i reached until this, but im not able to plot the lines:

molten <- melt(dataset, id = c("Sistema","Indisp","Total","TotalMax"))
ggplot(data=molten, aes(x=time, y=Total, fill=variable)) + geom_bar(stat="identity", position=position_dodge()) + geom_line(aes(x=Sistema, y=Indisp),stat="identity")

enter image description here

Thanks for your help

Can you please share the output of `dput(dataset)` in the body of your question, so that we can reproduce the table in the same formats you have it and reproduce your code? — Jon Spring, Aug 01 '19 at 22:11

Ryan John · Accepted Answer · 2019-08-02T14:18:43.867

Here's at least a start - the first issues is dealing with two different scales. (Two different scale Axis). Edit: cleaning it up a little more. Thanks to @Joe for the suggestions.

 library(tidyverse)
df <- tibble::tribble(
  ~Sistema, ~DetNP, ~DetProg, ~Indisp, ~Total, ~TotalMax,
  "MOTOR EQUIPO MOVIL",    744,      977,  0.0354,   1721,       977,
  "SISTEMA CHASIS",     36,      873,  0.0187,    909,       873,
  "TREN DE POTENCIA",    247,      527,  0.0159,    774,       527,
  "PMs",      0,      736,  0.0152,    736,       736,
  "SUSPENSION Y RUEDAS",    100,      346,  0.0092,    446,       346,
  "SISTEMA HIDRAULICO",    118,      181,  0.0062,    299,       181,
  "SISTEMA ELECTRICO 24V",    144,       30,  0.0036,    174,       144,
  "CABINA",    116,       20,  0.0028,    136,       116,
  "SISTEMA LUBRICACION",     24,       40,  0.0013,     64,        40,
  "SISTEMA DE FRENOS",     17,       44,  0.0013,     61,        44
)
df

library(ggplot2)
library(reshape2)
library(scales)

molten <- melt(df, id = c("Sistema","Indisp","Total","TotalMax"))
head(molten, 2)

scaleFactor <- max(molten$value) / max(molten$Indisp)

ggplot(data=molten) + 
  geom_bar(aes( x=reorder(Sistema, -Indisp), y=value, fill=variable), 
           stat="identity", position=position_dodge()) + 
  geom_point(aes( x=reorder(Sistema, -Indisp), y=Indisp * scaleFactor, color = "red"),
             stat = "identity", show.legend = FALSE) +
  geom_line(aes( x=reorder(Sistema, -Indisp), y=Indisp * scaleFactor, 
                 group = variable, color = "red"), stat = "identity", show.legend = FALSE) +
  scale_y_continuous(name="HORAS", sec.axis=sec_axis(~./scaleFactor, name="Indisp",
                                                      labels = percent_format(accuracy  = 0.1))) +
  theme_minimal() + 
  theme(axis.title.y.right = element_text(color = 'red'), 
        axis.text.y.right = element_text(color = 'red'))+
  labs(title = "TOP TEN INDISPONIBLIDAD POR HORAS EN SISTEMAS - JUL 2019")

I forgot to include the labels, both for percents and counts. — Ryan John, Aug 01 '19 at 23:06
You could also use x=reorder(Sistema, -Indisp) in your call to geom_bar, to get the order in the image. — Joe, Aug 01 '19 at 23:14
Depending on how closely you're trying to match it, you could also try these: theme_minimal() + theme(axis.title.y.right = element_text(color = 'red'), axis.text.y.right = element_text(color = 'red')) — Joe, Aug 01 '19 at 23:21

Grouped bar and line charts on R using ggplot2

1 Answers1