How to add N over bar graph after collapsing data

[Rukman Mecca]

Hello All,
I have a dataset in wide format where children's anthropometry is captured every month. I want to see the proportion of underweight children at each time point. For this, I have collapsed the dataset into the proportion of underweight children at each time point and then used graph bar. However, I would like to have N, i.e., the number of underweight child-ids, at each time point over the graph bar. Is it possible to do this after collapsing the data? I am using Stata 16.1

* Example generated by -dataex-. To install: ssc    install dataex
clear
input str6 child_id float(underwt_28 underwt_203    underwt_301 underwt_401    underwt_501    underwt_601)    double    sch_visit_tmng
"30001A" 0 . . . . .  28
"30001A" . 0 . . . . 203
"30001A" . . 0 . . . 301
"30001A" . . . 0 . . 401
"30001A" . . . . 0 . 501
"30001A" . . . . . 0 601
"30003A" . . . . . .  28
"30005A" 0 . . . . .  28
"30005A" . 0 . . . . 203
"30005A" . . 0 . . . 301
"30005A" . . . 0 . . 401
"30005A" . . . . 0 . 501
"30005A" . . . . . 0 601
"30006A" 0 . . . . .  28
"30006A" . 0 . . . . 203
"30006A" . . . . . . 301
"30006A" . . . 0 . . 401
"30006A" . . . . 0 . 501
"30006A" . . . . . 0 601
"30008A" 0 . . . . .  28
"30008A" . 0 . . . . 203
"30008A" . . 0 . . . 301
"30008A" . . . 0 . . 401
"30008A" . . . . 0 . 501
"30008A" . . . . . 0 601
"30010A" 0 . . . . .  28
"30010A" . 0 . . . . 203
"30010A" . . 0 . . . 301
"30010A" . . . 0 . . 401
"30010A" . . . . 0 . 501
"30010A" . . . . . 0 601
"30014A" 0 . . . . .  28
"30014A" . 0 . . . . 203
"30014A" . . 0 . . . 301
"30014A" . . . 0 . . 401
"30014A" . . . . 0 . 501
"30014A" . . . . . 0 601
"30016A" 0 . . . . .  28
"30016A" . 0 . . . . 203
"30016A" . . 0 . . . 301
"30016A" . . . . 1 . 501
"30016A" . . . . . 1 601
"30017A" 1 . . . . .  28
"30017A" . 0 . . . . 203
"30017A" . . 0 . . . 301
"30017A" . . . 0 . . 401
"30017A" . . . . 1 . 501
"30017A" . . . . . 0 601
"30019A" 0 . . . . .  28
"30019A" . 0 . . . . 203
"30019A" . . 0 . . . 301
"30019A" . . . 0 . . 401
"30019A" . . . . 0 . 501
"30019A" . . . . . 0 601
"30019B" 0 . . . . .  28
"30019B" . 0 . . . . 203
"30019B" . . 0 . . . 301
"30019B" . . . 0 . . 401
"30019B" . . . . 0 . 501
"30019B" . . . . . 0 601
"30021A" 0 . . . . .  28
"30021A" . 0 . . . . 203
"30021A" . . 0 . . . 301
"30021A" . . . 0 . . 401
"30021A" . . . . 0 . 501
"30021A" . . . . . 0 601
"30022A" 0 . . . . .  28
"30022A" . 0 . . . . 203
"30022A" . . 0 . . . 301
"30022A" . . . 0 . . 401
"30022A" . . . . 0 . 501
"30022A" . . . . . 0 601
"30025A" 0 . . . . .  28
"30025A" . 0 . . . . 203
"30025A" . . 0 . . . 301
"30025A" . . . 0 . . 401
"30025A" . . . . 0 . 501
"30025A" . . . . . 0 601
"30029A" 0 . . . . .  28
"30029A" . 0 . . . . 203
"30029A" . . 0 . . . 301
"30029A" . . . 0 . . 401
"30029A" . . . . 0 . 501
"30029A" . . . . . 0 601
"30032A" 0 . . . . .  28
"30032A" . 0 . . . . 203
"30032A" . . 0 . . . 301
"30032A" . . . 0 . . 401
"30032A" . . . . 0 . 501
"30032A" . . . . . 0 601
"30033A" 0 . . . . .  28
"30033A" . 0 . . . . 203
"30033A" . . 0 . . . 301
"30033A" . . . 0 . . 401
"30033A" . . . . 0 . 501
"30033A" . . . . . 0 601
"30034A" 1 . . . . .  28
"30034A" . 1 . . . . 203
"30034A" . . 1 . . . 301
"30034A" . . . 1 . . 401
end
label values underwt_28 underwt28
label def underwt28 0 "No underweight", modify
label def underwt28 1 "underweight", modify
label values underwt_203 underwt203
label def underwt203 0 "No underweight", modify
label def underwt203 1 "underweight", modify
label values underwt_301 underwt301
label def underwt301 0 "No underweight", modify
label def underwt301 1 "underweight", modify
label values underwt_401 underwt401
label def underwt401 0 "No underweight", modify
label def underwt401 1 "underweight", modify
label values underwt_501 underwt501
label def underwt501 0 "No underweight", modify
label def underwt501 1 "underweight", modify
label values underwt_601 underwt601
label def underwt601 0 "No underweight", modify
label def underwt601 1 "underweight", modify

Syntax used :

*Underweight
foreach t in 28 203 301 401 501 601 {
  gen underwt_`t' = 1 if wt_age_zscore <= -2.0 & wt_age_zscore >=-5.0 & sch_visit_tmng == `t'
  replace underwt_`t' = 0 if wt_age_zscore > -2.0 & wt_age_zscore <= 5.0 & sch_visit_tmng == `t'
  label var underwt_`t' "Underweight status at `t' days"
  label define underwt`t' 1 "underweight" 0 "No underweight"
  label values underwt_`t' underwt`t'
}

  
*Underweight graph
preserve
collapse (mean) underwt_28 underwt_203 underwt_301 underwt_401 underwt_501 underwt_601 if sch_visit_tmng == 28 | sch_visit_tmng == 203 | ///
  sch_visit_tmng == 301 | sch_visit_tmng == 401 | sch_visit_tmng == 501 | sch_visit_tmng == 601, by(sch_visit_tmng)
 
graph bar (mean) underwt_28 underwt_203 underwt_301 underwt_401 underwt_501 underwt_601, ///
  over(sch_visit_tmng) asyvars bar(1, color(blue)) bar(2, color(blue)) bar(3, color(blue)) bar(4, color(blue)) bar(5, color(blue)) bar(6, color(blue)) ytitle("Proportion of underweight") blabel(bar, format(%4.1f%%)) legend(off) ylab(0.05 "5" 0.1 "10" 0.15 "15" 0.2 "20" 0.25 "25" 0.3 "30" 0.35 "35")
gr_edit ..grpaxis.major.num_rule_ticks = 0
gr_edit ..grpaxis.edit_tick 2 24.8302 `"mon 2"', tickset(major)
gr_edit ..grpaxis.major.num_rule_ticks = 0
gr_edit ..grpaxis.edit_tick 3 41.6101 `"mon 3"', tickset(major)
gr_edit ..grpaxis.major.num_rule_ticks = 0
gr_edit ..grpaxis.edit_tick 4 58.3899 `"mon 4"', tickset(major)
gr_edit ..grpaxis.major.num_rule_ticks = 0
gr_edit ..grpaxis.edit_tick 5 75.1698 `"mon 5"', tickset(major)
gr_edit ..grpaxis.major.num_rule_ticks = 0
gr_edit ..grpaxis.edit_tick 6 91.9497 `"mon 6"', tickset(major)
gr_edit ..grpaxis.major.num_rule_ticks = 0
gr_edit ..grpaxis.edit_tick 1 8.05031 `"mon 1"', tickset(major)
graph save "Graph" "C:\Users\rukma\OneDrive - Society for Applied Studies\Work\Active Projects\SBT\Data\Graphs\underwt_prop.gph", replace

[Nick Cox]

I would just change to twoway bar. The point is that you can add other elements using other twoway commands.

Here is some technique.

Your pre-existing data structure with long layout is better for most purposes than what your code produces.

* Example generated by -dataex-. To install: ssc    install dataex
clear
input str6 child_id float(underwt_28 underwt_203    underwt_301 underwt_401    underwt_501    underwt_601)    double    sch_visit_tmng
"30001A" 0 . . . . .  28
"30001A" . 0 . . . . 203
"30001A" . . 0 . . . 301
"30001A" . . . 0 . . 401
"30001A" . . . . 0 . 501
"30001A" . . . . . 0 601
"30003A" . . . . . .  28
"30005A" 0 . . . . .  28
"30005A" . 0 . . . . 203
"30005A" . . 0 . . . 301
"30005A" . . . 0 . . 401
"30005A" . . . . 0 . 501
"30005A" . . . . . 0 601
"30006A" 0 . . . . .  28
"30006A" . 0 . . . . 203
"30006A" . . . . . . 301
"30006A" . . . 0 . . 401
"30006A" . . . . 0 . 501
"30006A" . . . . . 0 601
"30008A" 0 . . . . .  28
"30008A" . 0 . . . . 203
"30008A" . . 0 . . . 301
"30008A" . . . 0 . . 401
"30008A" . . . . 0 . 501
"30008A" . . . . . 0 601
"30010A" 0 . . . . .  28
"30010A" . 0 . . . . 203
"30010A" . . 0 . . . 301
"30010A" . . . 0 . . 401
"30010A" . . . . 0 . 501
"30010A" . . . . . 0 601
"30014A" 0 . . . . .  28
"30014A" . 0 . . . . 203
"30014A" . . 0 . . . 301
"30014A" . . . 0 . . 401
"30014A" . . . . 0 . 501
"30014A" . . . . . 0 601
"30016A" 0 . . . . .  28
"30016A" . 0 . . . . 203
"30016A" . . 0 . . . 301
"30016A" . . . . 1 . 501
"30016A" . . . . . 1 601
"30017A" 1 . . . . .  28
"30017A" . 0 . . . . 203
"30017A" . . 0 . . . 301
"30017A" . . . 0 . . 401
"30017A" . . . . 1 . 501
"30017A" . . . . . 0 601
"30019A" 0 . . . . .  28
"30019A" . 0 . . . . 203
"30019A" . . 0 . . . 301
"30019A" . . . 0 . . 401
"30019A" . . . . 0 . 501
"30019A" . . . . . 0 601
"30019B" 0 . . . . .  28
"30019B" . 0 . . . . 203
"30019B" . . 0 . . . 301
"30019B" . . . 0 . . 401
"30019B" . . . . 0 . 501
"30019B" . . . . . 0 601
"30021A" 0 . . . . .  28
"30021A" . 0 . . . . 203
"30021A" . . 0 . . . 301
"30021A" . . . 0 . . 401
"30021A" . . . . 0 . 501
"30021A" . . . . . 0 601
"30022A" 0 . . . . .  28
"30022A" . 0 . . . . 203
"30022A" . . 0 . . . 301
"30022A" . . . 0 . . 401
"30022A" . . . . 0 . 501
"30022A" . . . . . 0 601
"30025A" 0 . . . . .  28
"30025A" . 0 . . . . 203
"30025A" . . 0 . . . 301
"30025A" . . . 0 . . 401
"30025A" . . . . 0 . 501
"30025A" . . . . . 0 601
"30029A" 0 . . . . .  28
"30029A" . 0 . . . . 203
"30029A" . . 0 . . . 301
"30029A" . . . 0 . . 401
"30029A" . . . . 0 . 501
"30029A" . . . . . 0 601
"30032A" 0 . . . . .  28
"30032A" . 0 . . . . 203
"30032A" . . 0 . . . 301
"30032A" . . . 0 . . 401
"30032A" . . . . 0 . 501
"30032A" . . . . . 0 601
"30033A" 0 . . . . .  28
"30033A" . 0 . . . . 203
"30033A" . . 0 . . . 301
"30033A" . . . 0 . . 401
"30033A" . . . . 0 . 501
"30033A" . . . . . 0 601
"30034A" 1 . . . . .  28
"30034A" . 1 . . . . 203
"30034A" . . 1 . . . 301
"30034A" . . . 1 . . 401
end

egen underwt = rowmax(underwt_*)
egen mean = mean(underwt), by(sch_visit_tmng)
egen n = count(underwt), by(sch_visit_tmng)
gen toshow = "{it:n} = " + strofreal(n)
egen tag = tag(sch_visit_tmng)
egen timing = group(sch_visit_tmng), label

gen where = 0.01
twoway bar mean timing if tag, fcolor(stc1*0.2) yla(0 "0" 0.02(0.02)0.12, format(%03.2f)) ///
ytitle(proportion underweight) xtitle(timing) base(0) barw(0.8) xla(1/6, noticks valuelabel) ///
|| scatter where timing, ms(none) mla(toshow) mlabpos(12) mlabsize(medlarge) legend(off)

[Rukman Mecca]

Nick Cox This is exactly what I needed. Thank you for helping.

[Rukman Mecca]

Nick Cox A very minor thing: the 'n= ' value being displayed is total underwt at that time point. Can you suggest me how should I chnage the "toshow" if I like to see the count of underwt==1 at each time point or may be n/N. Pardon my ignorance!

[Nick Cox]

Sorry; you did say that in #1.

 
 egen n = total(underwt), by(sch_visit_tmng)

is sufficient as the values of 0 and missing make no different to the total and the total of the values of 1 is the count you want.

[Rukman Mecca]

Wonderful! Thank you very much Nick Cox. It worked well!