I am trying to reproduce with R a chart that I made in Excel that should represent a 95% confidence interval (CI) around a time series forecast. An Excel spreadsheet looks like this:
So, basically, the initial historical time series and from a certain point in time the forecast that it can be with its corresponding CI.
They, as is done in Excel, are slightly inefficient:
- I have four time series that span most of the time;
- The actual / historical time series (blue line above) simply stops when the forecast begins;
- The forecast (dashed red above) is simply hidden below blue until the start of the forecast period;
- Then I have a time series representing the difference between the upper bound and the lower bound of CI, which plays around stacked charts in Excel, becomes the shaded area in the table above.
Obviously, the calculation to generate the forecast and CI is much faster and easier to generalize and use with R, and although I could do the task on R and then just copy the output to Excel to draw a chart, doing everything in R would be much nicer.
At the end of the question, I provided the source data for dput() , as suggested by @MLavoie.
Here are the packages that I downloaded (not sure if you need them all here, but these are the ones that I usually work with):
require(zoo) require(xts) require(lattice) require(latticeExtra)
My data looks like the first 100 lines:
> head(data) fifth_percentile Median nintyfifth_percentile 2017-06-18 1.146267 1.146267 1.146267 2017-06-19 1.134643 1.134643 1.134643 2017-06-20 1.125664 1.125664 1.125664 2017-06-21 1.129037 1.129037 1.129037 2017-06-22 1.147542 1.147542 1.147542 2017-06-23 1.159989 1.159989 1.159989
Then, after 100 data points, the time series begin to diverge, and at the end they look like this:
> tail(data) fifth_percentile Median nintyfifth_percentile 2017-12-30 0.9430930 1.125844 1.341603 2017-12-31 0.9435227 1.127391 1.354928 2018-01-01 0.9417235 1.124625 1.355527 2018-01-02 0.9470077 1.124088 1.361420 2018-01-03 0.9571596 1.127299 1.364005 2018-01-04 0.9515535 1.127978 1.369536
Solution provided by DaveTurek
Thanks to DaveTurek, I found the answer. However, the only difference is that for my xts frame, apparently, I need to first convert each column to a number (using as.numeric() ). I don’t know if this is due to the fact that I am doing something wrong with xts and the grill, or is this the only way to achieve this using the DaveTurek suggestion. Let's try to investigate it further.
Here is the code to create the chart:
x = index(data[1:100,2]) y = as.numeric(data[1:100,2]) ex.x = index(data[101:200,2]) ex.y = as.numeric(data[101:200,2]) ex.lo = as.numeric(data[101:200,1]) ex.hi = as.numeric(data[101:200,3]) xyplot(y~x, ylim = c(0.9,1.4), panel=function(x,y,...) { panel.lines(x,y,lwd=2,col=4) panel.polygon(c(ex.x,rev(ex.x)),c(ex.lo,rev(ex.hi)),border=NA,col=5) panel.lines(ex.x,ex.y,lwd=2,col=2) })
And here is the final result:
Here is the final dataset from dput () that I am trying to build:
> dput(data) structure(c(1.14626724930899, 1.13464279067717, 1.12566420479952, 1.12903662366847, 1.14754211999921, 1.15998855701439, 1.15274364578958, 1.16226441955745, 1.16169992687419, 1.16520028734587, 1.16823402018407, 1.19832130049664, 1.18411773220697, 1.18531274215286, 1.16421444455115, 1.17108139956539, 1.18392357740377, 1.20103911352579, 1.17791736605905, 1.18277944964829, 1.20162550199013, 1.19665058179752, 1.19411188122108, 1.19367558590966, 1.19803272562951, 1.20600155861871, 1.22189449901607, 1.22072774140118, 1.22312376195254, 1.25355505518571, 1.25895911759195, 1.2613354420716, 1.24440525381363, 1.24444079462029, 1.24168652168112, 1.24154936710117, 1.23440527301777, 1.22592718438811, 1.21709102449773, 1.21448030929365, 1.23109601090898, 1.24401127451953, 1.23953314346685, 1.21863565024168, 1.20834325548551, 1.20281193695583, 1.20405850724191, 1.19608032796923, 1.22008184095742, 1.21675995421116, 1.20198916403093, 1.20029121301547, 1.18822375424598, 1.19007923345344, 1.19285965857709, 1.1971013197471, 1.1776860331227, 1.18028531916998, 1.18394951589397, 1.16712430930941, 1.17827461393349, 1.18751430033172, 1.21482260909863, 1.2167262724184, 1.21729489152574, 1.21847062594996, 1.21932070698031, 1.19678189566773, 1.17678214957629, 1.17586968485613, 1.16903708967946, 1.16967697995898, 1.14498266161799, 1.12782282645368, 1.11540004479973, 1.12639853863918, 1.11402516325222, 1.10511837662567, 1.10600107687395, 1.10243149863659, 1.10404564773364, 1.12949458422398, 1.11679224666313, 1.11338078540871, 1.10762728498848, 1.12437898939299, 1.11572706259347, 1.1148111967932, 1.12358625045939, 1.11169207274881, 1.13009253108247, 1.13772927166761, 1.12550770863279, 1.13062401691547, 1.12821231512428, 1.13174620070443, 1.13072790983063, 1.1428325334377, 1.12739171867048, 1.1214997813059, 1.11870510839984, 1.096148222775, 1.08805136310032, 1.08701594286129, 1.08047984136855, 1.07939438148434, 1.0684082570972, 1.06497159411023, 1.05820047926833, 1.06322519359802, 1.06234781015662, 1.05431808916504, 1.054405104791, 1.05330182895869, 1.04787681441803, 1.041698698458, 1.03870702538097, 1.03300007904201, 1.02741553353049, 1.03525701392318, 1.0339774223954, 1.0328464056954, 1.03100871401712, 1.03348765946373, 1.03473218333386, 1.02942612874379, 1.02109481188296, 1.02301597272716, 1.01553904377803, 1.0031650628692, 1.00779708136199, 1.01322764666693, 1.01964272925677, 1.02125480865504, 1.02300342204156, 1.02563993245866, 1.02972111884963, 1.02048756192688, 1.00481457379443, 1.00512607721887, 1.01094340128446, 1.01377432300649, 1.01170553705668, 1.00551128145228, 1.00612634442438, 1.00735643866839, 1.0080606590012, 0.985706701720841, 0.982234200010558, 0.975314534071082, 0.973611418201841, 0.968118612511537, 0.973092829667201, 0.975599110408158, 0.967214930243667, 0.968569928969912, 0.963572085616274, 0.964901787179726, 0.957782708788541, 0.951868416101986, 0.956694066411684, 0.956937537219092, 0.956303331651844, 0.947880835881923, 0.956308493824626, 0.948146077843001, 0.945939091828748, 0.945082701640947, 0.937222489932819, 0.937989843132858, 0.948712728941467, 0.939050882255992, 0.946264846068344, 0.944926693194716, 0.946825914432391, 0.939070104432721, 0.950666108330947, 0.949365988007735, 0.943616625744159, 0.946600795357699, 0.941276090147603, 0.939957902451166, 0.941523527816784, 0.946611480333791, 0.959236316317354, 0.96165367272139, 0.957508302724503, 0.954774123925477, 0.960811125123549, 0.956525507301749, 0.948237690612711, 0.951299123137395, 0.945212566792479, 0.94507842203255, 0.942735006048921, 0.943093032220433, 0.943522672031737, 0.941723495992432, 0.947007713852018, 0.95715960245335, 0.951553478810637, 1.14626724930899, 1.13464279067717, 1.12566420479952, 1.12903662366847, 1.14754211999921, 1.15998855701439, 1.15274364578958, 1.16226441955745, 1.16169992687419, 1.16520028734587, 1.16823402018407, 1.19832130049664, 1.18411773220697, 1.18531274215286, 1.16421444455115, 1.17108139956539, 1.18392357740377, 1.20103911352579, 1.17791736605905, 1.18277944964829, 1.20162550199013, 1.19665058179752, 1.19411188122108, 1.19367558590966, 1.19803272562951, 1.20600155861871, 1.22189449901607, 1.22072774140118, 1.22312376195254, 1.25355505518571, 1.25895911759195, 1.2613354420716, 1.24440525381363, 1.24444079462029, 1.24168652168112, 1.24154936710117, 1.23440527301777, 1.22592718438811, 1.21709102449773, 1.21448030929365, 1.23109601090898, 1.24401127451953, 1.23953314346685, 1.21863565024168, 1.20834325548551, 1.20281193695583, 1.20405850724191, 1.19608032796923, 1.22008184095742, 1.21675995421116, 1.20198916403093, 1.20029121301547, 1.18822375424598, 1.19007923345344, 1.19285965857709, 1.1971013197471, 1.1776860331227, 1.18028531916998, 1.18394951589397, 1.16712430930941, 1.17827461393349, 1.18751430033172, 1.21482260909863, 1.2167262724184, 1.21729489152574, 1.21847062594996, 1.21932070698031, 1.19678189566773, 1.17678214957629, 1.17586968485613, 1.16903708967946, 1.16967697995898, 1.14498266161799, 1.12782282645368, 1.11540004479973, 1.12639853863918, 1.11402516325222, 1.10511837662567, 1.10600107687395, 1.10243149863659, 1.10404564773364, 1.12949458422398, 1.11679224666313, 1.11338078540871, 1.10762728498848, 1.12437898939299, 1.11572706259347, 1.1148111967932, 1.12358625045939, 1.11169207274881, 1.13009253108247, 1.13772927166761, 1.12550770863279, 1.13062401691547, 1.12821231512428, 1.13174620070443, 1.13072790983063, 1.1428325334377, 1.12739171867048, 1.1214997813059, 1.11870510839984, 1.11811303551412, 1.11855383782522, 1.11981261957516, 1.12096887905804, 1.12162710713999, 1.12015553029278, 1.12189306008921, 1.1236834173899, 1.12204149206779, 1.12075809542535, 1.12116672935174, 1.12216772364685, 1.11821915571021, 1.12117719223463, 1.11896003906963, 1.11563621625852, 1.1183625095638, 1.12053072892388, 1.1216348268255, 1.12317377733957, 1.11873136428952, 1.12267083202989, 1.12642930089215, 1.13027646770951, 1.13129632891931, 1.12700346009603, 1.12060488827701, 1.12390899402613, 1.13129350591169, 1.12786650327192, 1.1274201121913, 1.13101906643359, 1.12727135093377, 1.12458327192256, 1.12259738972645, 1.12097982776572, 1.12073621452193, 1.12364872830763, 1.12644326299714, 1.12556263098661, 1.12797963752343, 1.12734519199847, 1.1261793072762, 1.12911407446825, 1.12754878937943, 1.12777579027467, 1.12554965831588, 1.12324469267853, 1.12231558194992, 1.12135908710208, 1.11923353817423, 1.12345300992675, 1.12186883237389, 1.12173652640663, 1.12488148969114, 1.12664301925369, 1.12294230775256, 1.12393650688095, 1.13038044949978, 1.12822226676967, 1.12934384230215, 1.1217648908055, 1.12218158739803, 1.12302651609468, 1.12682187689922, 1.13537701046932, 1.13172108462183, 1.1374053505525, 1.13498257452656, 1.12692005654471, 1.13210629725645, 1.12868775509168, 1.13073909215368, 1.13098804355869, 1.13353301668386, 1.13336476594698, 1.13233873705211, 1.12667020676157, 1.12133152301322, 1.12418759586717, 1.12048022460741, 1.12798162212357, 1.13053093896994, 1.12019367019997, 1.12422483586498, 1.11303086301782, 1.11986711815552, 1.12504718249418, 1.11341517044014, 1.12495096618792, 1.12995127061511, 1.13538401552385, 1.13145536081928, 1.1264465959783, 1.12584386458867, 1.1273908895838, 1.12462482614994, 1.1240880626286, 1.12729907535003, 1.12797751377714, 1.14626724930899, 1.13464279067717, 1.12566420479952, 1.12903662366847, 1.14754211999921, 1.15998855701439, 1.15274364578958, 1.16226441955745, 1.16169992687419, 1.16520028734587, 1.16823402018407, 1.19832130049664, 1.18411773220697, 1.18531274215286, 1.16421444455115, 1.17108139956539, 1.18392357740377, 1.20103911352579, 1.17791736605905, 1.18277944964829, 1.20162550199013, 1.19665058179752, 1.19411188122108, 1.19367558590966, 1.19803272562951, 1.20600155861871, 1.22189449901607, 1.22072774140118, 1.22312376195254, 1.25355505518571, 1.25895911759195, 1.2613354420716, 1.24440525381363, 1.24444079462029, 1.24168652168112, 1.24154936710117, 1.23440527301777, 1.22592718438811, 1.21709102449773, 1.21448030929365, 1.23109601090898, 1.24401127451953, 1.23953314346685, 1.21863565024168, 1.20834325548551, 1.20281193695583, 1.20405850724191, 1.19608032796923, 1.22008184095742, 1.21675995421116, 1.20198916403093, 1.20029121301547, 1.18822375424598, 1.19007923345344, 1.19285965857709, 1.1971013197471, 1.1776860331227, 1.18028531916998, 1.18394951589397, 1.16712430930941, 1.17827461393349, 1.18751430033172, 1.21482260909863, 1.2167262724184, 1.21729489152574, 1.21847062594996, 1.21932070698031, 1.19678189566773, 1.17678214957629, 1.17586968485613, 1.16903708967946, 1.16967697995898, 1.14498266161799, 1.12782282645368, 1.11540004479973, 1.12639853863918, 1.11402516325222, 1.10511837662567, 1.10600107687395, 1.10243149863659, 1.10404564773364, 1.12949458422398, 1.11679224666313, 1.11338078540871, 1.10762728498848, 1.12437898939299, 1.11572706259347, 1.1148111967932, 1.12358625045939, 1.11169207274881, 1.13009253108247, 1.13772927166761, 1.12550770863279, 1.13062401691547, 1.12821231512428, 1.13174620070443, 1.13072790983063, 1.1428325334377, 1.12739171867048, 1.1214997813059, 1.11870510839984, 1.14162401974592, 1.15630966411729, 1.15992199767135, 1.16683144867851, 1.16928280999155, 1.17287782220285, 1.18184525262982, 1.17555305757354, 1.18031492211593, 1.18142628277888, 1.18307577052783, 1.18257404220722, 1.19421117710041, 1.19403330560815, 1.19510080390052, 1.2058940348108, 1.19848571699109, 1.20138771250604, 1.20660682710938, 1.20790011589089, 1.20963951875753, 1.21572259411602, 1.21379678812156, 1.220302087399, 1.22062959185172, 1.22743877731977, 1.23135277550334, 1.24075667733246, 1.24169498945046, 1.23529301399753, 1.2399941777708, 1.24823732280171, 1.23861121958778, 1.24816319854615, 1.25252933549084, 1.25133386983018, 1.24512546001264, 1.2617641352045, 1.25486018976211, 1.25424601859098, 1.25820538036104, 1.25968528498312, 1.26939611029084, 1.27883933177157, 1.27926882841012, 1.27951234203094, 1.28997494816278, 1.29391898267335, 1.2971442938215, 1.29733541086814, 1.30376525837809, 1.31025722802128, 1.29718190520268, 1.27919305871102, 1.28685138548374, 1.28594279969497, 1.28695233433419, 1.30277136510213, 1.29178316107299, 1.29586799884087, 1.30076586308517, 1.30881154838964, 1.32171887794143, 1.3197588324899, 1.3121332301804, 1.31744410759858, 1.31402945919721, 1.30926303329755, 1.32019231597949, 1.31449633135152, 1.31730801686101, 1.31834557852015, 1.3175761022299, 1.33430488507454, 1.34091614601639, 1.33606628597812, 1.33180446732765, 1.33630738683041, 1.33449101077219, 1.32521028784732, 1.32241490851887, 1.31488015995544, 1.31913131799656, 1.32901121011698, 1.33177659436063, 1.32577077582349, 1.31960627618725, 1.31307169067904, 1.32148403094167, 1.33104893196281, 1.33491831741272, 1.3386091981919, 1.35730874062825, 1.3460340606746, 1.34160318929376, 1.35492848895938, 1.35552729646417, 1.36141957863605, 1.36400538435282, 1.369536167295), .indexCLASS = "Date", tclass = "Date", .indexTZ = "UTC", tzone = "UTC", class = c("xts", "zoo"), index = structure(c(1497744000, 1497830400, 1497916800, 1498003200, 1498089600, 1498176000, 1498262400, 1498348800, 1498435200, 1498521600, 1498608000, 1498694400, 1498780800, 1498867200, 1498953600, 1499040000, 1499126400, 1499212800, 1499299200, 1499385600, 1499472000, 1499558400, 1499644800, 1499731200, 1499817600, 1499904000, 1499990400, 1500076800, 1500163200, 1500249600, 1500336000, 1500422400, 1500508800, 1500595200, 1500681600, 1500768000, 1500854400, 1500940800, 1501027200, 1501113600, 1501200000, 1501286400, 1501372800, 1501459200, 1501545600, 1501632000, 1501718400, 1501804800, 1501891200, 1501977600, 1502064000, 1502150400, 1502236800, 1502323200, 1502409600, 1502496000, 1502582400, 1502668800, 1502755200, 1502841600, 1502928000, 1503014400, 1503100800, 1503187200, 1503273600, 1503360000, 1503446400, 1503532800, 1503619200, 1503705600, 1503792000, 1503878400, 1503964800, 1504051200, 1504137600, 1504224000, 1504310400, 1504396800, 1504483200, 1504569600, 1504656000, 1504742400, 1504828800, 1504915200, 1505001600, 1505088000, 1505174400, 1505260800, 1505347200, 1505433600, 1505520000, 1505606400, 1505692800, 1505779200, 1505865600, 1505952000, 1506038400, 1506124800, 1506211200, 1506297600, 1506384000, 1506470400, 1506556800, 1506643200, 1506729600, 1506816000, 1506902400, 1506988800, 1507075200, 1507161600, 1507248000, 1507334400, 1507420800, 1507507200, 1507593600, 1507680000, 1507766400, 1507852800, 1507939200, 1508025600, 1508112000, 1508198400, 1508284800, 1508371200, 1508457600, 1508544000, 1508630400, 1508716800, 1508803200, 1508889600, 1508976000, 1509062400, 1509148800, 1509235200, 1509321600, 1509408000, 1509494400, 1509580800, 1509667200, 1509753600, 1509840000, 1509926400, 1510012800, 1510099200, 1510185600, 1510272000, 1510358400, 1510444800, 1510531200, 1510617600, 1510704000, 1510790400, 1510876800, 1510963200, 1511049600, 1511136000, 1511222400, 1511308800, 1511395200, 1511481600, 1511568000, 1511654400, 1511740800, 1511827200, 1511913600, 1.512e+09, 1512086400, 1512172800, 1512259200, 1512345600, 1512432000, 1512518400, 1512604800, 1512691200, 1512777600, 1512864000, 1512950400, 1513036800, 1513123200, 1513209600, 1513296000, 1513382400, 1513468800, 1513555200, 1513641600, 1513728000, 1513814400, 1513900800, 1513987200, 1514073600, 1514160000, 1514246400, 1514332800, 1514419200, 1514505600, 1514592000, 1514678400, 1514764800, 1514851200, 1514937600, 1515024000 ), tzone = "UTC", tclass = "Date"), .Dim = c(201L, 3L), .Dimnames = list( NULL, c("fifth_percentile", "Median", "nintyfifth_percentile" )))