Combine bar graphs by age group and a line graph over time

[Yichao Wang]

Dear Statalist forum members,

I need some advice on how to combine bar graphs by group and line graph over time. I want to plot the evolution of the overall rate over time using a line graph, but for some years like 2000, 2010, 2019, I want to plot bar graphs by groups. I draw the figure I want to produce and my dataset looks like this. Can somebody help? Thanks in advance!

input year    str10 category    str10 group    data
2000    "Age"    "0-14"    11.5
2000    "Age"    "15-19"    501.7
2000    "Age"    "20-24"    592.2
2000    "Age"    "25-29"    292.1
2000    "Age"    "30-34"    156.1
2000    "Age"    "35-39"    100.4
2000    "Age"    "40-44"    60.6
2000    "Age"    "45-54"    27.4
2000    "Age"    "55-64"    9.5
2000    "Age"    "65+"    2.6
2000    "Age"    "Unknown"    
2000    "Overall"    "Overall"    128.6
2001    "Age"    "0-14"    11.3
2001    "Age"    "15-19"    491.9
2001    "Age"    "20-24"    582.6
2001    "Age"    "25-29"    294.7
2001    "Age"    "30-34"    156.2
2001    "Age"    "35-39"    97.3
2001    "Age"    "40-44"    59.2
2001    "Age"    "45-54"    26.4
2001    "Age"    "55-64"    8.9
2001    "Age"    "65+"    2.4
2001    "Age"    "Unknown"    
2001    "Overall"    "Overall"    126.9
2002    "Age"    "0-14"    10.3
2002    "Age"    "15-19"    466.6
2002    "Age"    "20-24"    549.5
2002    "Age"    "25-29"    292.8
2002    "Age"    "30-34"    153.9
2002    "Age"    "35-39"    97.8
2002    "Age"    "40-44"    59.9
2002    "Age"    "45-54"    27.2
2002    "Age"    "55-64"    8.5
2002    "Age"    "65+"    2.2
2002    "Age"    "Unknown"    
2002    "Overall"    "Overall"    122.3
2003    "Age"    "0-14"    9.1
2003    "Age"    "15-19"    435
2003    "Age"    "20-24"    513
2003    "Age"    "25-29"    281.5
2003    "Age"    "30-34"    147.7
2003    "Age"    "35-39"    94.5
2003    "Age"    "40-44"    59.6
2003    "Age"    "45-54"    26.9
2003    "Age"    "55-64"    8.3
2003    "Age"    "65+"    2.1
2003    "Age"    "Unknown"    
2003    "Overall"    "Overall"    115.5
2004    "Age"    "0-14"    8.1
2004    "Age"    "15-19"    416.6
2004    "Age"    "20-24"    488.8
2004    "Age"    "25-29"    282.2
2004    "Age"    "30-34"    149.3
2004    "Age"    "35-39"    94.4
2004    "Age"    "40-44"    60.9
2004    "Age"    "45-54"    29
2004    "Age"    "55-64"    9.1
2004    "Age"    "65+"    2
2004    "Age"    "Unknown"    
2004    "Overall"    "Overall"    112.7
2005    "Age"    "0-14"    7.8
2005    "Age"    "15-19"    426.4
2005    "Age"    "20-24"    500.2
2005    "Age"    "25-29"    287.1
2005    "Age"    "30-34"    150.9
2005    "Age"    "35-39"    95.5
2005    "Age"    "40-44"    63
2005    "Age"    "45-54"    29.8
2005    "Age"    "55-64"    9.1
2005    "Age"    "65+"    2.1
2005    "Age"    "Unknown"    
2005    "Overall"    "Overall"    114.9
2006    "Age"    "0-14"    7.7
2006    "Age"    "15-19"    447.9
2006    "Age"    "20-24"    519.8
2006    "Age"    "25-29"    300.8
2006    "Age"    "30-34"    160.8
2006    "Age"    "35-39"    98.4
2006    "Age"    "40-44"    65
2006    "Age"    "45-54"    32.1
2006    "Age"    "55-64"    9.9
2006    "Age"    "65+"    2.1
2006    "Age"    "Unknown"    
2006    "Overall"    "Overall"    120.1
2007    "Age"    "0-14"    7.2
2007    "Age"    "15-19"    453.7
2007    "Age"    "20-24"    522.2
2007    "Age"    "25-29"    291.6
2007    "Age"    "30-34"    156.6
2007    "Age"    "35-39"    90.9
2007    "Age"    "40-44"    60.3
2007    "Age"    "45-54"    30.5
2007    "Age"    "55-64"    9.7
2007    "Age"    "65+"    1.9
2007    "Age"    "Unknown"    
2007    "Overall"    "Overall"    118.1
2008    "Age"    "0-14"    6.7
2008    "Age"    "15-19"    446.7
2008    "Age"    "20-24"    506.7
2008    "Age"    "25-29"    264.7
2008    "Age"    "30-34"    141.9
2008    "Age"    "35-39"    78.8
2008    "Age"    "40-44"    51.7
2008    "Age"    "45-54"    25.3
2008    "Age"    "55-64"    7.8
2008    "Age"    "65+"    1.7
2008    "Age"    "Unknown"    
2008    "Overall"    "Overall"    110.7
2009    "Age"    "0-14"    5.3
2009    "Age"    "15-19"    402.1
2009    "Age"    "20-24"    464.2
2009    "Age"    "25-29"    230.4
2009    "Age"    "30-34"    124.4
2009    "Age"    "35-39"    68.3
2009    "Age"    "40-44"    43
2009    "Age"    "45-54"    21
2009    "Age"    "55-64"    6.4
2009    "Age"    "65+"    1.4
2009    "Age"    "Unknown"    
2009    "Overall"    "Overall"    98.2
2010    "Age"    "0-14"    5.4
2010    "Age"    "15-19"    401.5
2010    "Age"    "20-24"    486.7
2010    "Age"    "25-29"    240.7
2010    "Age"    "30-34"    126.6
2010    "Age"    "35-39"    68.6
2010    "Age"    "40-44"    44.3
2010    "Age"    "45-54"    21.2
2010    "Age"    "55-64"    6
2010    "Age"    "65+"    1.3
2010    "Age"    "Unknown"    
2010    "Overall"    "Overall"    100
2011    "Age"    "0-14"    5.7
2011    "Age"    "15-19"    406.9
2011    "Age"    "20-24"    504.2
2011    "Age"    "25-29"    250.2
2011    "Age"    "30-34"    132.4
2011    "Age"    "35-39"    72
2011    "Age"    "40-44"    46
2011    "Age"    "45-54"    23.4
2011    "Age"    "55-64"    7.2
2011    "Age"    "65+"    1.4
2011    "Age"    "Unknown"    
2011    "Overall"    "Overall"    103.3
2012    "Age"    "0-14"    5.6
2012    "Age"    "15-19"    381.6
2012    "Age"    "20-24"    510.2
2012    "Age"    "25-29"    273.3
2012    "Age"    "30-34"    150.3
2012    "Age"    "35-39"    83.1
2012    "Age"    "40-44"    52.2
2012    "Age"    "45-54"    28
2012    "Age"    "55-64"    8.4
2012    "Age"    "65+"    1.5
2012    "Age"    "Unknown"    
2012    "Overall"    "Overall"    106.7
2013    "Age"    "0-14"    4.7
2013    "Age"    "15-19"    340.4
2013    "Age"    "20-24"    495.5
2013    "Age"    "25-29"    287.9
2013    "Age"    "30-34"    160.2
2013    "Age"    "35-39"    92
2013    "Age"    "40-44"    56.7
2013    "Age"    "45-54"    31.6
2013    "Age"    "55-64"    9.7
2013    "Age"    "65+"    1.8
2013    "Age"    "Unknown"    
2013    "Overall"    "Overall"    105.4
2014    "Age"    "0-14"    4.4
2014    "Age"    "15-19"    325.2
2014    "Age"    "20-24"    508
2014    "Age"    "25-29"    317
2014    "Age"    "30-34"    178.6
2014    "Age"    "35-39"    104.7
2014    "Age"    "40-44"    61.8
2014    "Age"    "45-54"    35.4
2014    "Age"    "55-64"    11.4
2014    "Age"    "65+"    2
2014    "Age"    "Unknown"    
2014    "Overall"    "Overall"    110
2015    "Age"    "0-14"    4.2
2015    "Age"    "15-19"    341.5
2015    "Age"    "20-24"    549.6
2015    "Age"    "25-29"    369.8
2015    "Age"    "30-34"    211.2
2015    "Age"    "35-39"    128.7
2015    "Age"    "40-44"    74.7
2015    "Age"    "45-54"    43.7
2015    "Age"    "55-64"    14.8
2015    "Age"    "65+"    2.5
2015    "Age"    "Unknown"    
2015    "Overall"    "Overall"    123.3
2016    "Age"    "0-14"    4.5
2016    "Age"    "15-19"    379.4
2016    "Age"    "20-24"    618.2
2016    "Age"    "25-29"    442
2016    "Age"    "30-34"    264.2
2016    "Age"    "35-39"    163.9
2016    "Age"    "40-44"    97.1
2016    "Age"    "45-54"    56.5
2016    "Age"    "55-64"    19.7
2016    "Age"    "65+"    3.2
2016    "Age"    "Unknown"    
2016    "Overall"    "Overall"    145.1
2017    "Age"    "0-14"    5
2017    "Age"    "15-19"    439
2017    "Age"    "20-24"    707.1
2017    "Age"    "25-29"    522.4
2017    "Age"    "30-34"    326.4
2017    "Age"    "35-39"    206.8
2017    "Age"    "40-44"    123.1
2017    "Age"    "45-54"    69.7
2017    "Age"    "55-64"    26
2017    "Age"    "65+"    4.1
2017    "Age"    "Unknown"    
2017    "Overall"    "Overall"    171
2018    "Age"    "0-14"    5
2018    "Age"    "15-19"    433.8
2018    "Age"    "20-24"    723.2
2018    "Age"    "25-29"    550.3
2018    "Age"    "30-34"    364.3
2018    "Age"    "35-39"    224.8
2018    "Age"    "40-44"    136.9
2018    "Age"    "45-54"    75.2
2018    "Age"    "55-64"    28.6
2018    "Age"    "65+"    4.5
2018    "Age"    "Unknown"    
2018    "Overall"    "Overall"    178.6
2019    "Age"    "0-14"    4.9
2019    "Age"    "15-19"    443.5
2019    "Age"    "20-24"    750.2
2019    "Age"    "25-29"    577.3
2019    "Age"    "30-34"    392.3
2019    "Age"    "35-39"    246.5
2019    "Age"    "40-44"    152.4
2019    "Age"    "45-54"    81.5
2019    "Age"    "55-64"    32.1
2019    "Age"    "65+"    5
2019    "Age"    "Unknown"    
2019    "Overall"    "Overall"    187.8
end

[Nick Cox]

Thanks for the data example which is helpful, although data should be system missing . not blank when missing.

I have to say that I don't think your design works well for real!

Here is some code that isn't quite what you ask for and then some code that does something different. I am ignoring the unequal bin widths for age and assuming that the last variable is scaled by population concerned.

clear
input year    str10 gregory    str10 group    data
2000    "Age"    "0-14"    11.5
2000    "Age"    "15-19"    501.7
2000    "Age"    "20-24"    592.2
2000    "Age"    "25-29"    292.1
2000    "Age"    "30-34"    156.1
2000    "Age"    "35-39"    100.4
2000    "Age"    "40-44"    60.6
2000    "Age"    "45-54"    27.4
2000    "Age"    "55-64"    9.5
2000    "Age"    "65+"    2.6
2000    "Age"    "Unknown"    .
2000    "Overall"    "Overall"    128.6
2001    "Age"    "0-14"    11.3
2001    "Age"    "15-19"    491.9
2001    "Age"    "20-24"    582.6
2001    "Age"    "25-29"    294.7
2001    "Age"    "30-34"    156.2
2001    "Age"    "35-39"    97.3
2001    "Age"    "40-44"    59.2
2001    "Age"    "45-54"    26.4
2001    "Age"    "55-64"    8.9
2001    "Age"    "65+"    2.4
2001    "Age"    "Unknown"    .
2001    "Overall"    "Overall"    126.9
2002    "Age"    "0-14"    10.3
2002    "Age"    "15-19"    466.6
2002    "Age"    "20-24"    549.5
2002    "Age"    "25-29"    292.8
2002    "Age"    "30-34"    153.9
2002    "Age"    "35-39"    97.8
2002    "Age"    "40-44"    59.9
2002    "Age"    "45-54"    27.2
2002    "Age"    "55-64"    8.5
2002    "Age"    "65+"    2.2
2002    "Age"    "Unknown"    .
2002    "Overall"    "Overall"    122.3
2003    "Age"    "0-14"    9.1
2003    "Age"    "15-19"    435
2003    "Age"    "20-24"    513
2003    "Age"    "25-29"    281.5
2003    "Age"    "30-34"    147.7
2003    "Age"    "35-39"    94.5
2003    "Age"    "40-44"    59.6
2003    "Age"    "45-54"    26.9
2003    "Age"    "55-64"    8.3
2003    "Age"    "65+"    2.1
2003    "Age"    "Unknown"    .
2003    "Overall"    "Overall"    115.5
2004    "Age"    "0-14"    8.1
2004    "Age"    "15-19"    416.6
2004    "Age"    "20-24"    488.8
2004    "Age"    "25-29"    282.2
2004    "Age"    "30-34"    149.3
2004    "Age"    "35-39"    94.4
2004    "Age"    "40-44"    60.9
2004    "Age"    "45-54"    29
2004    "Age"    "55-64"    9.1
2004    "Age"    "65+"    2
2004    "Age"    "Unknown"    .
2004    "Overall"    "Overall"    112.7
2005    "Age"    "0-14"    7.8
2005    "Age"    "15-19"    426.4
2005    "Age"    "20-24"    500.2
2005    "Age"    "25-29"    287.1
2005    "Age"    "30-34"    150.9
2005    "Age"    "35-39"    95.5
2005    "Age"    "40-44"    63
2005    "Age"    "45-54"    29.8
2005    "Age"    "55-64"    9.1
2005    "Age"    "65+"    2.1
2005    "Age"    "Unknown"    .
2005    "Overall"    "Overall"    114.9
2006    "Age"    "0-14"    7.7
2006    "Age"    "15-19"    447.9
2006    "Age"    "20-24"    519.8
2006    "Age"    "25-29"    300.8
2006    "Age"    "30-34"    160.8
2006    "Age"    "35-39"    98.4
2006    "Age"    "40-44"    65
2006    "Age"    "45-54"    32.1
2006    "Age"    "55-64"    9.9
2006    "Age"    "65+"    2.1
2006    "Age"    "Unknown"    .
2006    "Overall"    "Overall"    120.1
2007    "Age"    "0-14"    7.2
2007    "Age"    "15-19"    453.7
2007    "Age"    "20-24"    522.2
2007    "Age"    "25-29"    291.6
2007    "Age"    "30-34"    156.6
2007    "Age"    "35-39"    90.9
2007    "Age"    "40-44"    60.3
2007    "Age"    "45-54"    30.5
2007    "Age"    "55-64"    9.7
2007    "Age"    "65+"    1.9
2007    "Age"    "Unknown"    .
2007    "Overall"    "Overall"    118.1
2008    "Age"    "0-14"    6.7
2008    "Age"    "15-19"    446.7
2008    "Age"    "20-24"    506.7
2008    "Age"    "25-29"    264.7
2008    "Age"    "30-34"    141.9
2008    "Age"    "35-39"    78.8
2008    "Age"    "40-44"    51.7
2008    "Age"    "45-54"    25.3
2008    "Age"    "55-64"    7.8
2008    "Age"    "65+"    1.7
2008    "Age"    "Unknown"    .
2008    "Overall"    "Overall"    110.7
2009    "Age"    "0-14"    5.3
2009    "Age"    "15-19"    402.1
2009    "Age"    "20-24"    464.2
2009    "Age"    "25-29"    230.4
2009    "Age"    "30-34"    124.4
2009    "Age"    "35-39"    68.3
2009    "Age"    "40-44"    43
2009    "Age"    "45-54"    21
2009    "Age"    "55-64"    6.4
2009    "Age"    "65+"    1.4
2009    "Age"    "Unknown"    .
2009    "Overall"    "Overall"    98.2
2010    "Age"    "0-14"    5.4
2010    "Age"    "15-19"    401.5
2010    "Age"    "20-24"    486.7
2010    "Age"    "25-29"    240.7
2010    "Age"    "30-34"    126.6
2010    "Age"    "35-39"    68.6
2010    "Age"    "40-44"    44.3
2010    "Age"    "45-54"    21.2
2010    "Age"    "55-64"    6
2010    "Age"    "65+"    1.3
2010    "Age"    "Unknown"    .
2010    "Overall"    "Overall"    100
2011    "Age"    "0-14"    5.7
2011    "Age"    "15-19"    406.9
2011    "Age"    "20-24"    504.2
2011    "Age"    "25-29"    250.2
2011    "Age"    "30-34"    132.4
2011    "Age"    "35-39"    72
2011    "Age"    "40-44"    46
2011    "Age"    "45-54"    23.4
2011    "Age"    "55-64"    7.2
2011    "Age"    "65+"    1.4
2011    "Age"    "Unknown"    .
2011    "Overall"    "Overall"    103.3
2012    "Age"    "0-14"    5.6
2012    "Age"    "15-19"    381.6
2012    "Age"    "20-24"    510.2
2012    "Age"    "25-29"    273.3
2012    "Age"    "30-34"    150.3
2012    "Age"    "35-39"    83.1
2012    "Age"    "40-44"    52.2
2012    "Age"    "45-54"    28
2012    "Age"    "55-64"    8.4
2012    "Age"    "65+"    1.5
2012    "Age"    "Unknown"    .
2012    "Overall"    "Overall"    106.7
2013    "Age"    "0-14"    4.7
2013    "Age"    "15-19"    340.4
2013    "Age"    "20-24"    495.5
2013    "Age"    "25-29"    287.9
2013    "Age"    "30-34"    160.2
2013    "Age"    "35-39"    92
2013    "Age"    "40-44"    56.7
2013    "Age"    "45-54"    31.6
2013    "Age"    "55-64"    9.7
2013    "Age"    "65+"    1.8
2013    "Age"    "Unknown"    .
2013    "Overall"    "Overall"    105.4
2014    "Age"    "0-14"    4.4
2014    "Age"    "15-19"    325.2
2014    "Age"    "20-24"    508
2014    "Age"    "25-29"    317
2014    "Age"    "30-34"    178.6
2014    "Age"    "35-39"    104.7
2014    "Age"    "40-44"    61.8
2014    "Age"    "45-54"    35.4
2014    "Age"    "55-64"    11.4
2014    "Age"    "65+"    2
2014    "Age"    "Unknown"  .  
2014    "Overall"    "Overall"    110
2015    "Age"    "0-14"    4.2
2015    "Age"    "15-19"    341.5
2015    "Age"    "20-24"    549.6
2015    "Age"    "25-29"    369.8
2015    "Age"    "30-34"    211.2
2015    "Age"    "35-39"    128.7
2015    "Age"    "40-44"    74.7
2015    "Age"    "45-54"    43.7
2015    "Age"    "55-64"    14.8
2015    "Age"    "65+"    2.5
2015    "Age"    "Unknown"    .
2015    "Overall"    "Overall"    123.3
2016    "Age"    "0-14"    4.5
2016    "Age"    "15-19"    379.4
2016    "Age"    "20-24"    618.2
2016    "Age"    "25-29"    442
2016    "Age"    "30-34"    264.2
2016    "Age"    "35-39"    163.9
2016    "Age"    "40-44"    97.1
2016    "Age"    "45-54"    56.5
2016    "Age"    "55-64"    19.7
2016    "Age"    "65+"    3.2
2016    "Age"    "Unknown"    .
2016    "Overall"    "Overall"    145.1
2017    "Age"    "0-14"    5
2017    "Age"    "15-19"    439
2017    "Age"    "20-24"    707.1
2017    "Age"    "25-29"    522.4
2017    "Age"    "30-34"    326.4
2017    "Age"    "35-39"    206.8
2017    "Age"    "40-44"    123.1
2017    "Age"    "45-54"    69.7
2017    "Age"    "55-64"    26
2017    "Age"    "65+"    4.1
2017    "Age"    "Unknown"    .
2017    "Overall"    "Overall"    171
2018    "Age"    "0-14"    5
2018    "Age"    "15-19"    433.8
2018    "Age"    "20-24"    723.2
2018    "Age"    "25-29"    550.3
2018    "Age"    "30-34"    364.3
2018    "Age"    "35-39"    224.8
2018    "Age"    "40-44"    136.9
2018    "Age"    "45-54"    75.2
2018    "Age"    "55-64"    28.6
2018    "Age"    "65+"    4.5
2018    "Age"    "Unknown"    .
2018    "Overall"    "Overall"    178.6
2019    "Age"    "0-14"    4.9
2019    "Age"    "15-19"    443.5
2019    "Age"    "20-24"    750.2
2019    "Age"    "25-29"    577.3
2019    "Age"    "30-34"    392.3
2019    "Age"    "35-39"    246.5
2019    "Age"    "40-44"    152.4
2019    "Age"    "45-54"    81.5
2019    "Age"    "55-64"    32.1
2019    "Age"    "65+"    5
2019    "Age"    "Unknown"    .
2019    "Overall"    "Overall"    187.8
end


gen gr = real(substr(group, 1, 2))

gen year2 = cond(gr == 15, year - 1, cond(gr == 20, year - 0.5, cond(gr == 25, year, cond(gr == 30, year + 0.5, cond(gr == 35, year +1, .)))))

twoway bar data year2 if inlist(year, 2000, 2010, 2019), barw(0.5) bfcolor(blue*0.2) || connected data year if group == "Overall"  , legend(off) name(G1, replace)

encode group if !inlist(group, "Unknown", "Overall") , gen(group2)
// tabplot is from Stata Journal
tabplot group2 year [iw=data] if inlist(year, 2000, 2005, 2010, 2015, 2019), horizontal yreverse barw(1) xsize(7) bfcolor(green*0.2) showval(offset(0.26) format(%2.0f))  note("") ytitle(Age group) xtitle("") xsc(r(0.8, .)) name(G2, replace)

Guess: the data are social not medical. My own age group is so low!

[Justin Niakamal]

I've found Nick's fabplot (Stata Journal) to be helpful in instances like these. Something along the lines of

encode group, gen(grp)
fabplot connected data grp if group != "Overall", by(year, compact rows(3)) xlab(1(1)10,  angle(v) valuelabel) select(year >= 2005)

where you can adjust your preferred years in the select() option.

Cox, Nicholas J. "Speaking Stata: Front-and-back plots to ease spaghetti and paella problems." The Stata Journal 21, no. 2 (2021): 539-554.
https://journals.sagepub.com/doi/pdf...6867X211025838

[Yichao Wang]

Thank you so much, Nick and Justin. Your comments are very helpful!

[Nick Cox]

Justin Niakamal had a nice idea. Here are some further tweaks. This assumes the data example and code in #2 have been run and that fabplot has been installed.

fabplot line data group2 , by(year, compact rows(3)) xlab(1(1)10,  angle(v) valuelabel) select(inlist(year, 2000, 2005, 2010, 2015, 2019)) ysc(log) yla(1 10 100 1000) xtitle(Age group) front(connected) name(G3, replace)

Making the default plot line reduces background noise while keeping the front series connected maintains the emphasis. Logarithmic scale is an option, although not necessarily better here.

[Yichao Wang]

Thank you again Nick Cox