I have monthly data.
df = structure(list(year = c(2000, 2000, 2000, 2000, 2000, 2000, 2000,
2000, 2000, 2000, 2000, 2000, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2001, 2001, 2001, 2001, 2001, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2003, 2003, 2003, 2003,
2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2004, 2004, 2004,
2004, 2004, 2004, 2004, 2004, 2004, 2004, 2004, 2004, 2005, 2005,
2005, 2005, 2005, 2005, 2005, 2005, 2005, 2005, 2005, 2005, 2006,
2006, 2006, 2006, 2006, 2006, 2006, 2006, 2006, 2006, 2006, 2006,
2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007,
2007, 2008, 2008, 2008, 2008, 2008, 2008, 2008, 2008, 2008, 2008,
2008, 2008, 2009, 2009, 2009, 2009, 2009, 2009, 2009, 2009, 2009,
2009, 2009, 2009, 2010, 2010, 2010, 2010, 2010, 2010, 2010, 2010,
2010, 2010, 2010, 2010, 2011, 2011, 2011, 2011, 2011, 2011, 2011,
2011, 2011, 2011, 2011, 2011, 2012, 2012, 2012, 2012, 2012, 2012,
2012, 2012, 2012, 2012, 2012, 2012, 2013, 2013, 2013, 2013, 2013,
2013, 2013, 2013, 2013, 2013, 2013, 2013, 2014, 2014, 2014, 2014,
2014, 2014, 2014, 2014, 2014, 2014, 2014, 2014, 2015, 2015, 2015,
2015, 2015, 2015, 2015, 2015, 2015, 2015, 2015, 2015, 2016, 2016,
2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2017,
2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017,
2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018,
2018, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019, 2019,
2019, 2019, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020, 2020,
2020, 2020, 2020, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021,
2021, 2021, 2021, 2021, 2022, 2022, 2022, 2022, 2022), month = c(1,
2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4,
5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,
12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6,
7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1,
2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4,
5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,
12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6,
7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1,
2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9,
10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4,
5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,
12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5), DKF = c(1.35172264747094,
1.28304968251943, 1.24552331218312, 0.729910740042406, 0.783565950925784,
0.937813954394283, 0.65906459417623, 0.674455518391957, 1.00287050913478,
0.966700531783534, 1.06789122585464, 0.911228414774245, 0.888192557763822,
1.00949844928465, 0.923481876481075, 0.825695257044659, 0.782314855696782,
0.58656187528325, 0.59863983014387, 0.915297764110538, 0.795746136288842,
1.22892338526645, 1.21590370700258, 0.941111950448553, 1.25374308931,
1.37718209597035, 1.14589985915629, 0.780148229684933, 0.874595353682668,
1.14604519185711, 0.992538665048302, 0.839581039115371, 0.692248217126283,
1.09275417581988, 0.935509599103277, 1.05126624798179, 1.21389334983466,
0.471805056248264, 0.899796188921615, 1.13465513806389, 0.794802001196274,
0.988343658722104, 0.640972005628806, 0.696531137276774, 0.866950948408711,
0.847009150097212, 1.04974849173403, 1.37904271072424, 0.989335461118068,
1.01881679142021, 1.13543763056535, 0.838777240518135, 0.979653919054008,
1.08061837844478, 0.82277865797135, 0.864415700183831, 1.01881475212,
1.18967409949951, 1.1021120736495, 1.19724334825115, 1.45365715062549,
1.08444570569323, 1.04040352973806, 0.953457285992096, 0.725137661308166,
0.877195436230933, 0.682835067384357, 0.892233795081727, 0.735647931996617,
0.922564005579307, 1.00928979939415, 1.18028793823755, 0.890152351848971,
0.679322268329413, 1.0309894126979, 0.929363528877214, 1.13961805078013,
0.574606762924047, 0.426401185444772, 0.571742787930437, 1.03978114056251,
1.0409969616094, 1.40864382733085, 1.47171882529208, 1.63771100449271,
1.06492312886427, 1.33813773020147, 0.908832212801612, 0.735451214470096,
0.804321091051172, 1.0483540276271, 0.963899802129351, 1.17235236258944,
0.764144846076876, 1.18666132216437, 1.01695078775161, 1.63018952218757,
1.28201421070697, 1.28971580247142, 0.660807724144165, 0.545679150901317,
0.989673338945377, 0.895127320445369, 1.00091861466823, 0.998129718962755,
1.36044162478288, 1.30376568547356, 0.803882525480035, 0.94921605371591,0.895159054770847, 1.0233618058017, 0.882259653882889, 0.955360154852879,
0.996557971268051, 0.825908243110373, 0.9298631764269, 1.08834708436942,
1.08437892014292, 1.3100235359856, 0.973417429459011, 1.08183890002604,
0.927847366406903, 1.08444808684155, 1.00629071539255, 0.915932755912942,
0.612803068886559, 0.608025672639411, 0.701750575853826, 1.03903198040534,
1.14800014857184, 1.05248998940747, 1.08198391908056, 0.941280622360785,
1.31120847954812, 1.13122551755416, 1.05177260786535, 1.04841696109959,
0.769427097563231, 0.775582019621385, 1.01855462747317, 1.0083833797951,
1.21767260565366, 0.902038439994779, 1.530742287929, 1.31873683053679,
1.17684867563492, 1.22705516508575, 0.860405848607701, 0.987324553152812,
0.857033884245413, 0.810047643090631, 0.636071821981568, 1.1907394843672,
1.12575576970271, 1.04420179733264, 1.14185482971826, 1.23204137351913,
0.823136859844741, 1.19783933375901, 0.968567862081462, 0.833385934583554,
0.726920901458399, 0.65531909035838, 0.753610868811851, 0.717746126152522,
1.23409362388528, 1.10278565554561, 1.38071285042192, 1.57109049556651,
1.15502863605244, 1.0040881264057, 1.18055928781909, 0.862824150332591,
0.537844552456038, 0.860821018552511, 0.920860392801851, 0.99646117592799,
0.956061560482096, 1.03958008015503, 1.33464649069669, 1.3174068359515,
1.04555954326292, 1.22887573556177, 1.00018713250388, 1.05996066504234,
0.838163569225115, 0.987708754602534, 0.925535073701729, 0.966516775578336,
0.93742460871859, 1.15690926778802, 1.56516066856324, 1.13453461364929,
1.07620775402756, 0.72920410008137, 0.907737174204368, 0.888974524549598,
0.717220774853375, 0.76317057653277, 0.940517375365291, 0.720406617635943,
1.10312156292202, 1.123232002553, 1.20726483160498, 0.967021319031673,
1.12434915414135, 1.05898342360939, 1.03872228752315, 0.911642560323416,
1.10140036371953, 0.803567889633706, 0.861438036008085, 0.943845612289096,
1.37773221892607, 1.11543848974819, 1.38628788567699, 1.12229077846439,
0.777738621069224, 1.00783841143653, 1.05497270201962, 0.797402527566735,
0.713202674593307, 0.532925195511057, 0.826850443285887, 1.07156987452172,
1.19298649340662, 0.894209199082177, 1.24090494240861, 1.14344127974531,
1.17345412771589, 1.37780711496641, 0.94220463742751, 1.03520807951851,
0.903828446822579, 0.917272491366773, 0.662917167767267, 1.14045960329353,
1.00201318092992, 0.969530538933758, 1.27854249221013, 1.52105760296756,
1.41388975655703, 1.03575611985903, 0.874025512284813, 0.897765040255863,
0.771616959622039, 0.965510190966026, 0.52546635367643, 0.752653873261598,
1.19513283076679, 1.09133423242563, 1.09226790813337, 1.02349429992044,
1.0658161342807, 1.00435891304271, 0.98444807600341, 0.823101711995535,
0.596035963287678, 0.696231234694286, 0.905898095230998, 0.806258589573775,
1.15639565579428, 1.0473836165791, 1.07508003106277, 1.39930965230116,
1.43470445178599, 0.806252958074881, 0.896554076101869, 0.843930885601709
), GDT = c(NaN, NaN, NaN, NaN, NaN, NaN, NaN, NaN, NaN, NaN,
NaN, NaN, NaN, NaN, NaN, NaN, NaN, NaN, NaN, NaN, NaN, NaN, NaN,
NaN, 1.25680752678888, 1.30458733335219, 1.09228713674356, 0.731144402808708,
0.901800804314023, 0.957461410038454, 0.819706045078683, 0.475775452531874,
0.812826328094277, 1.11687992541631, 0.932483160688902, 1.32868910135585,
1.25384353492792, 0.646192521242945, 0.851337944481354, 1.08891671902872,
0.746902998018574, 0.751046136645327, 0.70437527862342, 0.763623414467365,
0.75329034048398, 0.896566916937618, 1.11444921972041, 1.13329678471406,
1.09856931409079, 0.976915278500223, 1.12570428553459, 0.867758325583477,
0.880289967257854, 0.864671728990408, 0.634607052573966, 0.861311927831495,
1.13575363170179, 1.2070018390393, 1.07689802953356, 1.13763566887133,
1.48447680661822, 1.16282885366174, 1.20113531052352, 0.996648017350864,
0.977974931770798, 0.847612653379458, 0.6559683522377, 0.891609365439509,
0.846739643723833, 1.07689102065316, 1.10535075441814, 1.35578899798082,
1.0695418060791, 0.815862950036911, 1.10864812068106, 0.933141131149222,
1.18091190791042, 0.685541451393349, 0.404296442260575, 0.612185677397922,
1.01217791932524, 1.01761890972469, 1.53155278763207, 1.19869206697511,
1.57503671592401, 1.07012864006763, 1.33385820101602, 0.902913700675377,
0.761414931585347, 0.534911873148555, 0.920372958269884, 1.01998782325545,
1.32191355352224, 0.719901338300631, 1.29176703032855, 0.976988798325431,
1.52553933047215, 1.16907195585609, 1.32156550368257, 0.801125844218432,
0.503506836843003, 1.00740245891658, 0.891408493131709, 0.944837200657525,
0.823918683623961, 1.23973756680251, 1.22183757894295, 0.855467987151896,
1.1655230420432, 0.846377970349997, 1.01031626947778, 0.694221826905899,
0.997598690803502, 0.835219291273416, 0.788676690864018, 0.967597700950264,
1.1173478944231, 1.12871596640232, 1.24400628814409, 1.15323180987247,
1.14131508515276, 0.841599262747386, 1.15832068934728, 0.939763994753187,
0.909608283325048, 0.539567448361289, 0.719121297140522, 0.777640699022277,
1.02647983909116, 1.2682869959843, 1.07672099722135, 1.00527060252935,
1.0876062962132, 1.34265032081886, 0.973223960164022, 0.985888324267638,0.981835566707787, 0.686341954172892, 0.906576243379172, 0.876936633998207,
0.964999966972588, 1.29737710047812, 1.04927666382123, 1.44863516284984,
1.33126269265413, 1.03465014934506, 1.13867747944518, 0.862860526953427,
0.872340007187503, 1.00542841537923, 0.791498516682757, 0.770442433890974,
1.16265642608057, 1.13848335318019, 1.1362172612138, 1.23191747108114,
1.10451873812134, 0.830316746522189, 1.1855719478445, 0.867873314844338,
0.834545244872349, 0.954259042579429, 0.510761440729461, 0.857352059460437,
0.804162233711449, 1.35035785960842, 1.09147154369777, 1.47792257450634,
1.58822912815973, 1.32352572467977, 1.00404155940566, 0.998397513087739,
0.709520762349026, 0.744935348307921, 0.671610877360585, 0.909270737815378,
0.815546276080398, 1.14386659153319, 1.16162564354924, 1.27837624701734,
1.2509811953427, 1.03686192287682, 1.17822112329784, 0.908344338075427,
1.11885426552648, 0.858920616382635, 0.91294015817663, 0.744091372599863,
0.925193621022607, 0.993464527722221, 1.26057830798364, 1.52772666775097,
1.32369134889825, 1.04770807356819, 0.872510959270273, 0.951320821074975,
1.02877888982045, 0.649033389675605, 0.86623645971998, 0.841392350299326,
0.76463936934526, 1.12450175026676, 1.02485975306901, 1.13331813154549,
0.984198409538324, 1.28842611347878, 1.08584621901382, 0.989998470042162,
0.907968234852318, 0.901943251975657, 0.757248685595277, 0.802915681540415,
0.711581924417606, 1.37281168224106, 1.13399234264557, 1.24045127338488,
1.07974406802222, 0.947085374332494, 1.07290914721875, 0.990714791041318,
0.841556067171633, 0.808896225154296, 0.574657618255475, 0.767183999412229,
1.11396317760533, 1.27431596304687, 1.20835099745381, 1.25266132003819,
1.27598553654126, 1.07956720653235, 1.34231205540578, 0.830593867852974,
1.00575276850364, 0.821124113895814, 0.956179422824695, 0.80817063216765,
1.05572161063818, 1.00832919606806, 0.99984019019911, 1.34001312811603,
1.48158628915112, 1.33738492784179, 1.11662521966552, 0.866537379275953,
0.801000202937038, 0.705113992247558, 0.786124784622247, 0.745816506447599,
0.806088174426318, 1.24742888677119, 1.15472260963184, 1.09413488657353,
0.904082358328098, 1.12579438593004, 1.06624680336326, 0.939307819392979,
0.754136657889693, 0.523110190297094, 0.703105808070076, 0.873448099614908,
0.682935766548446, 1.24204436344695, 1.11619576683155, 1.09225885313817,
1.38633786792471, 1.45443081854661, 0.796045224095568, 0.926073495538111,
0.878731858245736), GSB = c(1.42732855172889, 1.36647312947188,
1.16150897813652, 0.785130011428715, 0.742234323225591, 1.03158085317069,
0.724249368832566, 0.576616289839368, 1.03321264977837, 1.18572054608451,
1.3665028150932, 1.15738195634629, 0.990254963583316, 0.972439432715603,
1.04881501369725, 0.827347891552827, 0.743059371440597, 0.753632464847504,
0.634913492146464, 0.78268302152957, 1.02339641673239, 1.41291870721349,
1.26058099109012, 0.957684531351214, 1.26525692661594, 1.30652446348662,
1.08568909096472, 0.731810139804985, 0.921518285486672, 0.9534957346959,
0.810792520541632, 0.473428041749632, 0.796606248240461, 1.1170454368297,
0.932206387308422, 1.32238691545603, 1.2531210790686, 0.657630015832921,
0.838917566605179, 1.08812321973857, 0.752060257170111, 0.749896517032918,
0.711114060443491, 0.760781282695412, 0.753860610652583, 0.904774733512967,
1.10951191111161, 1.12130574439842, 1.1013591213235, 0.977348019377033,
1.11701609195591, 0.87200405920343, 0.872556052494623, 0.857021325377132,
0.635340384388317, 0.863489719401717, 1.13823587601944, 1.20658677844467,
1.08291774028172, 1.14062415857802, 1.49137680701881, 1.16958459694305,
1.21297015116148, 0.984767373682825, 0.994163222450573, 0.834848031155646,
0.665985191128456, 0.9008505537178, 0.863080984913686, 1.08139546295276,
1.11854595449102, 1.34827847154207, 1.06911865940539, 0.83237516374191,
1.12830131412176, 0.930910407307058, 1.16944849480839, 0.687980514180717,
0.410824817153568, 0.625631473978964, 1.00026315343146, 1.01362854052643,
1.535838255549, 1.18867257149527, 1.57793796485585, 1.05096429899665,
1.33142811376099, 0.896696841654529, 0.761640170532948, 0.528952439792091,
0.922786778344897, 1.00585987270401, 1.32577356526312, 0.71518173016873,
1.31026211025398, 0.981506299264178, 1.52845576784398, 1.1632060725133,
1.33190066187623, 0.825262840812395, 0.514210891588922, 1.00785186752109,
0.88887596845671, 0.944893403471237, 0.820422629539757, 1.24656931022391,
1.22405462073909, 0.85665580628169, 1.17342045551516, 0.850362539714914,
1.00808364638941, 0.683417586760761, 1.00625654087362, 0.832985312537523,
0.800867272414999, 0.970746722837862, 1.10843677516493, 1.1226606784441,
1.21999536748326, 1.14873313386296, 1.14756369362385, 0.82762418725203,
1.15042470403991, 0.931608702775515, 0.895852121407924, 0.538860802366275,
0.726094289481171, 0.784980913615277, 1.03694351875136, 1.26924685205612,
1.07927655011703, 1.01090378000378, 1.10189151869186, 1.31687283629651,0.960614843919144, 0.994412379000563, 0.984254310024599, 0.687323223554008,
0.907983830434, 0.87442866161275, 0.960146772357666, 1.30470414451929,
1.04589308912898, 1.44648089018604, 1.32230139968135, 1.02870548702175,
1.11096717313095, 0.862306450240927, 0.878637402896746, 1.00574287226242,
0.788663278137416, 0.772519335756429, 1.16676196757528, 1.13527282686076,
1.14305513406574, 1.20942791033866, 1.09326331768159, 0.837179762642395,
1.19120922729999, 0.866747706134549, 0.855566536601101, 0.937171840489563,
0.513270944026593, 0.853135233366321, 0.795076400026749, 1.36596557753374,
1.11327428132084, 1.49120767412332, 1.57944473726642, 1.3248747771118,
1.00694239988424, 0.985546324106932, 0.716530687223081, 0.740083388516196,
0.674970450884444, 0.907495557808628, 0.801660279237639, 1.14900140556192,
1.14546925979909, 1.27581962916626, 1.24845776961011, 1.03666254376605,
1.18102994375815, 0.901665612989167, 1.11830434755244, 0.857935607851881,
0.915911278935463, 0.75065321392388, 0.918228640562073, 0.996619825601208,
1.26156063477019, 1.52366255553435, 1.33331647680646, 1.05340941553594,
0.889807553711622, 0.942420906203159, 1.04330766831972, 0.641037678406638,
0.87083394976808, 0.833992365139702, 0.770339840738859, 1.11897336332565,
1.02345721805131, 1.13002946724832, 0.996526658895734, 1.27864385919028,
1.07995298291631, 0.99234207362247, 0.912531157558439, 0.897227146318543,
0.763494876576389, 0.797816176521968, 0.713537131318904, 1.37669490393184,
1.14713685078544, 1.23461989615391, 1.07925859275957, 0.953993099699868,
1.06974216310917, 0.987650860846011, 0.830843928883164, 0.804019738088617,
0.579737702083712, 0.766468043981474, 1.11388493446579, 1.27863699579457,
1.21358313075414, 1.24829777129734, 1.27485993419124, 1.08261654345824,
1.34550450533084, 0.830810660730245, 0.993499294347178, 0.831884375141765,
0.948442105049797, 0.830345729490111, 1.05143216605119, 1.01102105798185,
0.97848544443649, 1.33604429015038, 1.48190569008833, 1.34032810220027,
1.11664108918812, 0.862796712206531, 0.785093014145704, 0.695590718808535,
0.783004543061428, 0.760197596800998, 0.81081746698004, 1.25285969693509,
1.15864436163981, 1.09447985136254, 0.905660950258676, 1.12376514282394,
1.07164968976378, 0.935528377661535, 0.760637611146673, 0.515443723240943,
0.687631584465032, 0.877136012348124, 0.685850801882236, 1.2418144107684,
1.12306465162899, 1.07808554796647, 1.38145913270718, 1.45808940007713,
0.797777545449729, 0.930739111454714, 0.872254120160263)), class = "data.frame", row.names = c(NA,
-269L))
I would like to create a long-term statistic based on data from year 2002 to 2021 as follow :
LongTerm <- df %>%
filter(year %in% c(2002:2021))%>%
gather(key = "project",value = "value", -c(year,month))%>%
group_by(project,month)%>%
mutate(MeanRef = mean(value,na.rm = T))%>%
mutate(MinRef = min(value,na.rm = T))%>%
mutate(MaxRef = max(value,na.rm = T))
In the LongTerm dataset, for each project now we have the monthly value, long term mean, min and max.
how could I insert the data (MeanRef, MinRef and MaxRef) into my original dataset to be able to have the long term references for the dates beyond my reference dates ?
I thought I will create a dataset, this time without filter on year, and put the reference values :
test <- df %>%
gather(key = "project",value = "value", -c(year,month))%>%
group_by(project,month)%>%
mutate(MeanRef = NA)%>%
mutate(MinRef = NA)%>%
mutate(MaxRef = NA)
test$MeanRef[which(test$year %in% c(2002:2021))] = LongTerm$MeanRef
test$MinRef[which(test$year %in% c(2002:2021))] = LongTerm$MinRef
test$MaxRef[which(test$year %in% c(2002:2021))] = LongTerm$MaxRef
But now I have no Idea how to fill the rest of the NAs in test$MeanRef, test$MinRef and test$MaxRef ! (Consider that the values are repeated per project per month)
CodePudding user response：
Instead of filtering the data, may be we could calculate the mean/min/max on a subset of data based on the logical expression
library(dplyr)
library(tidyr)
out <- df %>%
gather(key = "project",value = "value", -c(year,month))%>%
group_by(project,month)%>%
mutate(MeanRef = mean(value[year %in% 2002:2021],na.rm = TRUE))%>%
mutate(MinRef = min(value[year %in% 2002:2021],na.rm = TRUE))%>%
mutate(MaxRef = max(value[year %in% 2002:2021],na.rm = TRUE))
If the values corresponding to 'year' outside the range should be NA, then use a case_when or replace
out <- df %>%
gather(key = "project",value = "value", -c(year,month))%>%
group_by(project,month)%>%
mutate(MeanRef = replace(rep(NA_real_, n()),
year %in% 2002:2021, mean(value[year %in% 2002:2021],na.rm = TRUE)))%>%
mutate(MinRef = replace(rep(NA_real_, n()),
year %in% 2002:2021, min(value[year %in% 2002:2021],na.rm = TRUE)))%>%
mutate(MaxRef = replace(rep(NA_real_, n()),
year %in% 2002:2021, max(value[year %in% 2002:2021],na.rm = TRUE)))