Creating cumulative frequency time series (overlay panels)

[Ed Jonathan]

Hi all,

I have a data set of clients (unique ID) with their week of immunization and age group. What I would like to do is create a time series graph that displays the cumulative count over time (week of immunization), and a separate line for each age bracket.

So far, I collapsed (count) the data by age groups and episode week and then used xtline to plot the counts over time and overlay to bring the panels (age groups) into a single graph.

Where I am stuck is being able to plot the cumulative frequencies over time by age bracket. I also tried collapsing (sum) by episode week but this ignores age brackets and I'm not sure it provides cumulative frequencies. I've seen methods for cumulative distribution (values adding up to 1), but this is not what I would like to do, as I need cumulative frequencies.

Can anybody please advise on how to best set up the data to generate this time series? I've included my code below:

collapse (count) clientid_new, by (epwk agegrp)
rename clientid_new count
label var count "Number of persons immunized"

label define agegroups 0 "0-11" 1 "12-17" 2 "18-24" 3 "25-29" 4 "30-39" 5 "40-49" 6 "50-59" 7 "60-69" 8 "70-79" 9 "80+"
label define weeks 0 "13-Dec-20" 1 "20-Dec-20" 2 "27-Dec-20" 3 "03-Jan-21" 4 "10-Jan-21" 5 "17-Jan-21" 6 "24-Jan-21" 7 "31-Jan-21" 8 "07-Feb-21" 9 "14-Feb-21" 10 "21-Feb-21" 11 "28-Feb-21" 12 "07-Mar-21" 13 "14-Mar-21" 14 "21-Mar-21" 15 "28-Mar-21" 16 "04-Apr-21" 17 "11-Apr-21" 18 "18-Apr-21" 19 "25-Apr-21" 20 "02-May-21" 21 "09-May-21" 22 "16-May-21" 23 "23-May-21" 24 "30-May-21" 25 "06-Jun-21" 26 "13-Jun-21" 27 "20-Jun-21" 28 "27-Jun-21" 29 "04-Jul-21" 30 "11-Jul-21" 31 "18-Jul-21" 32 "25-Jul-21" 33 "01-Aug-21" 34 "08-Aug-21" 35 "15-Aug-21" 36 "22-Aug-21" 37 "29-Aug-21"
label values agegrp agegroups
label values epwk weeks

tsset agegrp epwk
tsfill, full
replace count = 0 if missing(count)
xtline count, overlay

collapse (sum) count, by(epwk)
tsset epwk
**[stuck here]

Thanks in advance!

[Carlo Lazzaro]

Ed:
I'm not really clear withwhat you're after.
Maybe the following (trivila) toy-example can provide some inputs:

. use "https://www.stata-press.com/data/r16/nlswork.dta"
(National Longitudinal Survey.  Young Women 14-26 years of age in 1968)


. bysort idcode: g wanted=sum( wks_work)


. histogram wanted, ytitle(working_weeks) xtitle(N_workers)
(bin=44, start=0, width=22.931818)

.

[Ed Jonathan]

Thanks Carlo. It looks like what you have done here is summed the number of weeks worked (wks_work) by unique id (idcode). Sorry if I'm not more clear, trying my best to explain my data.

I am trying to do the same, however I have two groups that I would like to sum by. That is, for each age bracket I would like a cumulative weekly count across 37 weeks.

For example:

• for age bracket 12-17, I would like to plot time series from week 1-37, the cumulative counts, so week 10 includes counts from week 1-9+10, week 11 includes counts 1-10+11, and so on.
• repeat this with an overlay plot for age brackets 18-24, 25-29, 30-39, etc.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(agegrp epwk) long count
2  3    1
3  3    3
4  3   13
5  3   17
6  3   25
7  3   11
1  4    1
2  4   26
3  4   38
4  4   70
5  4  123
6  4  146
7  4   72
8  4    5
9  4   11
2  5   97
3  5  198
4  5  319
5  5  370
6  5  405
7  5  179
8  5   21
9  5    7
1  6   10
2  6  304
3  6  527
4  6  822
5  6  949
6  6 1006
7  6  497
8  6  102
9  6  120
2  7   40
3  7   57
4  7  118
5  7  176
6  7  228
7  7  256
8  7  343
9  7 1524
1  8    5
2  8  213
3  8  327
4  8  506
5  8  544
6  8  716
7  8  521
8  8  383
9  8 2167
1  9   16
2  9  330
3  9  521
4  9  834
5  9  936
6  9 1158
7  9  709
8  9  161
9  9  261
2 10  119
3 10  134
4 10  281
5 10  316
6 10  342
7 10  207
8 10   65
9 10   31
1 11    2
2 11   11
3 11   14
4 11   24
5 11   33
6 11   44
7 11   25
8 11   16
9 11   38
2 12    8
3 12    8
4 12    9
5 12    5
6 12   21
7 12   18
8 12   36
9 12   64
2 13    2
3 13    7
4 13   14
5 13   11
6 13   21
7 13   25
8 13   18
9 13   46
2 14    5
3 14   13
4 14   15
5 14   17
6 14   26
7 14   46
8 14   70
9 14  305
2 15    7
end