Overlap line graph on a bar graph

[Chiara Piazzo]

Dear all,

I would like to produce a graph similar to this one - https://peltiertech.com/images/2013-...mpleChart4.png.

I have data for two variables; count and z by city and week. I would like to have a horizontal bar plot of count by city and a line plot of weekly average of the variable z.

I have tried;

graph bar countall, horizontal over(city) || line tmin week

But get the following error

option | not allowed

I guess the issue is that I want the bar plot to be by city but line plot (of weekly average of variable z) by week and overlay them.

I have provided a snippet of the data below, any help will be greatly appreciated.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 city byte week float(count z)
"Boston"  1 3.5  1.9
"Akron"   1 3.1  1.5
"NYC"     1 2.8 -4.1
"Houston" 1 2.5 12.8
"OKC"     1 2.7  1.7
"Boston"  2 2.7    2
"Akron"   2 9.5  1.4
"NYC"     2 5.3    4
"Houston" 2 4.5 11.2
"OKC"     2 2.1    4
"Boston"  3 2.7  1.7
"Akron"   3 9.5  1.4
"NYC"     3 5.3  3.9
"Houston" 3 4.5 10.9
"OKC"     3 2.1  4.3
end

Thank you!

Sincerely,

Milu

[Andrew Musau]

Thanks for the data example. Use twoway

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 city byte week float(count z)
"Boston"  1 3.5  1.9
"Akron"   1 3.1  1.5
"NYC"     1 2.8 -4.1
"Houston" 1 2.5 12.8
"OKC"     1 2.7  1.7
"Boston"  2 2.7    2
"Akron"   2 9.5  1.4
"NYC"     2 5.3    4
"Houston" 2 4.5 11.2
"OKC"     2 2.1    4
"Boston"  3 2.7  1.7
"Akron"   3 9.5  1.4
"NYC"     3 5.3  3.9
"Houston" 3 4.5 10.9
"OKC"     3 2.1  4.3
end

encode city, gen(City)
bys City: egen Count= mean(count)
bys week: egen mz= mean(z)
label var mz "Mean wage"
label var week "Week"

twoway (bar Count City , hor ylabel(, valuelabel angle(0)) xlab(0 (2) 8) xvarlab("")///
barw(0.7) xscale(axis(1) range(0)) bcolor(navy) ) (scatter week mz, connect(line)///
xaxis(2) yaxis(2) ylab(1 (1) 3, axis(2)) graphregion(color(white)) leg(off))

[Chiara Piazzo]

Dear Andrew Musau, thank you so much for the neat solution. It works great, except the labels for the cities. The actual dataset contains more than a hundred cities. I have tried changing the axis labels around without luck. What am I doing wrong?

Apologies if this should be a separate question? Thank you again for the solution!

[Andrew Musau]

I do not think that it is practical to display 100+ city names on the axis. Maybe someone else can suggest a better graph for your data.

[Chiara Piazzo]

Thanks for your reply, now that I think of it, I agree...trying to find an alternative.