Dear all,

I have a question about how to identify the weekday of the high and low points in time series analysis.


My data look like this:
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input float(odate daily_average dow daily_aggregate)
21833  558.8615 5 228433.4
21834 602.16187 6 248830.3
21835  599.1369 0 286873.3
21836 586.83997 1 288542.2
21837   487.384 2 396684.7
21840 588.78766 5 251097.6
21841 583.47736 6 264955.8
21842  598.8483 0 280131.6
21843  569.3498 1 292125.6
21847  546.3105 5 322303.1
21848 546.90594 6 284829.7
21849  592.6006 0 312704.8
21853  702.3023 4 565021.4
21854   570.108 5   281427
21855  587.7522 6 292463.9
21856 579.67017 0   285388
21857 576.75354 1 294538.6
21858  504.4952 2 354774.8
21859  564.3304 3 244320.1
21860  722.5038 4 742229.9
21861 541.64734 5   240482
21862 538.60876 6 309879.1
21864  630.0263 1 341794.4
21865 496.88385 2 271955.2
21866  659.5681 3 224037.5
21867  718.9317 4 551383.1
21868  597.2248 5 230995.3
21869  617.5574 6 297261.5
21870  608.1977 0 337398.2
21871  573.3346 1 471880.2
21872  513.1751 2 413158.5
21873  579.5194 3   296647
21874  731.8616 4   841382
21875  591.5007 5   251893
21876 585.56494 6 283856.1
21877 600.34674 0 261585.1
21878  571.5742 1 298434.8
21879  478.3791 2 462645.1
21880  570.5222 3 320055.3
21881  719.6547 4 846669.6
21882 573.55774 5 321442.8
21883  559.4268 6 306342.7
21884  571.9596 0 378978.4
21885  609.8727 1 330358.8
21886    514.05 2 410256.6
21887  573.0786 3 325302.1
21888  727.6231 4 965301.9
21889  568.8781 5 316153.5
21890  598.8592 6 326133.6
21891 604.44745 0 371609.3
21892  589.0873 1 425666.1
21893  484.4622 2 612013.8
21894  593.1388 3 395843.8
21895  750.2917 4  1170309
21896 597.18036 5 382975.6
21897  594.0722 6 336415.6
21898  612.2352 0 442558.3
21901 593.78156 3 356800.1
21902  731.9205 4 967405.1
21903  587.6961 5 269775.3
21904  585.5294 6   265819
21905  592.0468 0   293197
21906  597.9922 1 292557.6
21907  457.2679 2 288545.6
21908 586.17267 3 211657.5
21909  754.5541 4 787074.3
21910  602.9098 5 315600.2
21911  594.4632 6 251086.5
21912  596.5747 0 316811.3
21913 606.11554 1 353601.5
21914 540.07556 2 453573.3
21915  611.2679 3   315442
21916  716.9801 4  1161746
21917  571.1439 5 383375.1
21918 558.21387 6 493767.7
21919  588.6963 0 568176.2
21920  577.4242 1 610477.4
21921  496.9474 2 670616.1
21922 558.11566 3   476779
21923  750.2371 4  1372432
21924  562.8652 5 421542.8
21925  594.6396 6 388884.8
21926 604.78296 0 429245.3
21927  596.4515 1 462107.4
21929  606.9549 3 435527.3
21930  738.0359 4  1283441
21931 600.69617 5 434392.3
21932  570.9493 6 417687.4
21933 561.29755 0 455406.7
21934 554.81696 1 578193.9
21935  493.2901 2 954382.8
21936 518.47144 3  1323431
21937  639.7033 4  2410524
21938 559.89703 5 655217.1
21939  595.8712 6 507413.9
21942   605.671 2 775126.8
21943   614.989 3 909818.9
21944  704.0444 4  1231788
21945    512.14 5  1171733
21946  415.8226 6  1432087
end
format %tdCCYY-NN-DD odate

I have created the figure as below for daily_average and daily_aggregate separately using code Array



Code:
gen week = odate - mod(dow(odate) - 7, 7)
format week %tdd_m
label define DOW 0 "Sun" 1 "Mon" 2 "Tue" 3 "Wed" 4 "Thu" 5 "Fri" 6 "Sat"
bys week: egen high = max(daily_average)
bys week: egen low = min(daily_average)
bys week: egen highdate = max(cond(daily_average==high,odate,.))
bys week: egen highday = max(cond(daily_average==high,dow,.))
bys week: egen lowdate = max(cond(daily_average==low,odate ,.))
bys week: egen lowday = max(cond(daily_average==low,dow ,.))
label values *day DOW
twoway line daily_average odate || scatter high highdate,mlabel(highday) mlabpos(12)||scatter low lowdate,mlabel(lowday) mlabpos(6)||,legend(off) scheme(s1mono)

The issue now is that I want to put daily_aggregate on the same figure with daily_aggregate has its own y-axis. I tried the code below following the code I used before,

Code:
bys week: egen high1 = max(daily_aggregate)
bys week: egen low1 = min(daily_aggregate)
bys week: egen highdate1 = max(cond(daily_aggregate==high,odate,.))
bys week: egen highday1 = max(cond(daily_aggregate==high,dow,.))
bys week: egen lowdate1 = max(cond(daily_aggregate==low,odate ,.))
bys week: egen lowday1 = max(cond(daily_aggregate==low,dow ,.))
label values *day DOW  
twoway (tsline daily_average)(tsline daily_aggregate, yaxis(2)) || scatter high highdate,mlabel(highday) mlabpos(12)||scatter low lowdate,mlabel(lowday) mlabpos(6)||scatter high1 highdate1,mlabel(highday1) mlabpos(12)||scatter low1 lowdate1,mlabel(lowday1) mlabpos(6)||,leg(on) xtitle("date") ylab(, angle(h))
However, I was not able to get the figure as I expected with this command. I wonder if anyone has any idea of how to revise the code to generate the graph that I want.

Thank you and look forward to your reply.