Calculating average of 5 highest observations in a given month

Pia · September 18, 2019, 8:38am

Hi all,

I have a data table that includes the date in the first column, and the respective returns in the following columns (the head is the stock identifier). My data table looks like this:

Now, I am looking to calculate the average of the five highest returns in a given month, under the condition that there are at least 15 return observations in each month.

I can manage calculating the simple maximum of each month, but getting all conditions under one roof exceeds my newcomer skills.

I would greatly appreciate any help!

andresrcs · September 18, 2019, 12:15pm

Can you please share a small part of the data set in a copy-paste friendly format? A screenshot is not helpful and also not a good thing to do here.

In case you don't know how to do it, there are many options, which include:

If you have stored the data set in some R object, dput function is very handy.
In case the data set is in a spreadsheet, check out the datapasta package. Take a look at this link.

Pia · September 18, 2019, 12:33pm

Hi @andresrcs,

thanks for your answer. I have extracted some data via dput, but I can only upload jpeg, png or pdf here. I converted it into PDF, but I believe its not sufficient for you...
DailyReturns.pdf (34.0 KB)

andresrcs · September 18, 2019, 12:35pm

You just have to paste the result here, no need for uploading anything.

Pia · September 18, 2019, 12:42pm

here we go @andresrcs:

structure(list(date = structure(c(17533, 17534, 17535, 17536, 
17539, 17540, 17541, 17542, 17543, 17547, 17548, 17549, 17550, 
17553, 17554, 17555, 17556, 17557, 17560, 17561, 17562, 17563, 
17564, 17567, 17568, 17569, 17570, 17571, 17574, 17575, 17576, 
17577, 17578, 17582, 17583, 17584, 17585, 17588, 17589, 17590, 
17591, 17592, 17595, 17596, 17597, 17598, 17599, 17602, 17603, 
17604, 17605, 17606, 17609, 17610, 17611, 17612, 17613, 17616, 
17617, 17618, 17619, 17623, 17624, 17625, 17626, 17627, 17630, 
17631, 17632, 17633, 17634, 17637, 17638, 17639, 17640, 17641, 
17644, 17645, 17646, 17647, 17648, 17651, 17652, 17653, 17654, 
17655, 17658, 17659, 17660, 17661, 17662, 17665, 17666, 17667, 
17668, 17669, 17672, 17673, 17674, 17675, 17676, 17680, 17681, 
17682, 17683, 17686, 17687, 17688, 17689, 17690, 17693, 17694, 
17695, 17696, 17697, 17700, 17701, 17702, 17703, 17704, 17707, 
17708, 17709, 17710, 17711), tzone = "UTC", tclass = "Date", class = "Date"), 
    `10026` = c(NA, -0.00998786767581905, 0.0138127158236847, 
    -0.00955052427703185, 0.000741739716790146, -0.00720975675493563, 
    -0.0127595357676122, 0.00776838940132052, -0.000613957295859224, 
    0.00109215017064845, 0.0367516705304787, -0.01025971719829, 
    0.0219947490202173, -0.026332899869961, 0.00313856427378956, 
    -0.0123818399680468, 0.0172553248854137, -0.0145109992048768, 
    0.00551334633228007, -0.0706117686392511, -0.00395719088012159, 
    0.00794560820572077, -0.0183459236309247, -0.0273031829464154, 
    0.0200389507626124, -0.00860856449120728, -0.0190738445098826, 
    0.0130135431641067, 0.00261416087833322, 0.00275618275058243, 
    0.0278582571874304, 0.00303555941023403, 0.00230587980977082, 
    -0.00359453604640292, -0.0191198398903435, -0.00970945200441331, 
    0.00586793433855748, 0.0125535371437011, -0.0070011668611436, 
    -0.0134400705052877, 0.00126554008784319, 0.0202230483271375, 
    0.0107127240926979, 0.0162232316677482, 0.00276713495104319, 
    -0.002334960730206, 0.00567375886524824, -0.00486600846262342, 
    -0.00070873786407788, -0.0147507279448781, -0.025552366339334, 
    0.00967646624316743, -0.00424317799400087, -0.00330614943795471, 
    -0.013563246351172, -0.000896801606874886, -0.0126403141361257, 
    0.0240133341423632, -0.000739680480816629, -0.0034053893988748, 
    0.0144109344822463, -0.0167691857059168, 0.0236091457510985, 
    0.0130238649592549, 0.00933706816059776, -0.0145876325339785, 
    0.0022385181975737, 0.00943886515158621, 0, 0.000142683787103248, 
    -0.00413938053097351, 0.0205676523267631, 0.0109542869180534, 
    0.000138987289018733, -0.0133342583975098, -0.00985436687165719, 
    -7.10883628349812e-05, -0.00277264325323467, 0.00128323946674258, 
    0.000142399430402396, -0.00989535132056663, -0.0120074777106702, 
    -0.0388617276762973, -0.0182479731772566, -0.00647840505938613, 
    0.0301194666884437, 0.0177091183119822, 0.0033320992225101, 
    0.00177129151291533, 0.000441947808903276, 0.00942562592047125, 
    -0.00809746133644595, 0.00544245054056058, 0.0141174007667761, 
    0.0119734564339298, 0.00285103349964344, 0.00106616915422886, 
    -0.0255591746141871, 0.00582877959927153, 0.0057225642883012, 
    0.00165665514261004, 0.00740634159730047, 0.0251248376070781, 
    -0.0139256370978972, 0.010097443863861, 0.0195036700454385, 
    0.0110394953373558, -0.00874872838250251, 6.84181718664245e-05, 
    0.00759389751659034, 0.0033269961977187, 0.00629356432293404, 
    0.00416953597848013, 0.0113848773516692, 0.00463514766256123, 
    -0.000329554442393909, 0.00962616206237232, 0.00176314242800246, 
    0.00352027402348587, 0.00610627517214501, -0.000645661157024802, 
    0.0144074815867683, -0.0171962943249661, -0.00602682911023267, 
    -0.00593297692006778), `10028` = c(NA, 0.0102061855670104, 
    -0.0205122971731809, -0.0103146488851844, 0.0631645436361723, 
    -0.0690167343301317, 0.0316953839608594, -0.00824742268041234, 
    0.0343035343035343, -0.0733668341708542, 0.0734273318872016, 
    -0.000303122158229807, 0.0309278350515465, -0.0287254901960784, 
    0.00938730190774195, 0.0501, -0.0191410341872202, 0, -0.0679611650485438, 
    0, 0.046875, -0.0348258706467661, -0.020618556701031, -0.0313684210526315, 
    0.000869376222560447, 0.0206297502714439, -0.0106382978723403, 
    -0.0322580645161291, 0.0222222222222221, 0, 0.0158695652173912, 
    -0.00481489407233038, 0.02128803354478, -0.0209495736393304, 
    0.0103225806451612, 0.00234142188165176, 0.00934380972605653, 
    -0.000525983589312107, -0.000105252078728579, -0.0210526315789473, 
    -0.0053763440860215, 0.0810810810810809, -0.053, -0.0390707497360083, 
    0.0219780219780219, -0.0108602150537634, 0.000108707468203129, 
    0, 0.0080434782608696, -0.0187621306879447, 0.000109890109890154, 
    -0.000109878035380762, 0, -0.0307692307692308, -0.0258503401360545, 
    0.0416666666666667, -0.0100558659217878, -0.0244920993227992, 
    -0.0396852944579429, 0.013975903614458, -0.0137832699619772, 
    -0.0240963855421685, 0.0246913580246912, 0.0110843373493976, 
    -0.0238322211630123, -0.00769042968750011, 0.017099274203469, 
    0.0293904208998548, 0.0222065562213607, -0.0219540229885058, 
    0, 0.00434833705488313, 0.0063187456119822, 0.00813953488372099, 
    -0.00807381776239913, 0, -0.000116279069767411, -0.0232585184323759, 
    0.0121443028932016, -0.0295259381249265, 0.0060606060606061, 
    0.0120481927710843, -0.0119047619047619, -0.00614457831325299, 
    -0.0180627954903624, -0.0246913580246914, -0.0126582278481012, 
    0.0384615384615385, -0.0234567901234568, -0.00126422250316061, 
    -0.0341772151898735, -0.0252948885976408, -0.0181524808390481, 
    -0.0153382634894549, 0.0739916550764952, -0.0282310282310283, 
    0.0126599147121536, -0.0130280300039479, -0.0253333333333333, 
    0.0292749658002736, -0.00318979266347685, -0.0270666666666667, 
    -0.0132931341647253, 0.125, -0.0903703703703704, 0.0177795874049946, 
    -0.0266702226963595, -0.0135635018495684, 0.0048611111111112, 
    -0.00884588804422959, 0.0181285734207224, -0.0001369675386933, 
    -0.0273972602739726, -0.0140845070422535, 0.157142857142857, 
    -0.109876543209877, 0.014008321775312, 0.0258514567090686, 
    -0.00346666666666673, -0.0445544554455446, 0.00504131074079273, 
    0.0590776090288421, -0.0723589001447179, 0.00694936888384623, 
    0.0140845070422535), `10032` = c(NA, 0.00130975769482644, 
    0.0122629169391759, 0.00492650621870472, 0.0466929197138954, 
    -0.00906019656019663, -0.0119324345265769, 0.0123902132998746, 
    0.0204492641363285, -0.0318809776833157, 0.00925199937274579, 
    -0.0658794282162835, 0.0073186959414504, -0.00974240422721273, 
    0.00533600133400025, -0.0106153590976945, 0.0149203688181057, 
    0.0122233234225306, -0.0119125326370758, -0.00990916597853009, 
    -0.00333611342785656, 0.0123849372384937, -0.0110762109439577, 
    -0.027415580073554, 0.0134066689584049, 0.00729308005427409, 
    -0.0313184037716787, -0.00104293412132805, 0.010266225856969, 
    -0.0072338959696866, 0.0152671755725191, 0.0105946684894054, 
    0.000338180588434156, 0.0270453008789724, -0.00971033574720204, 
    0.00515206913744382, 0.00793650793650791, 0.0150918635170605, 
    -0.0105042016806723, -0.0148619957537155, -0.00513925729442977, 
    0.0236627228795201, 0.00455803353410378, 0.0134500081024145, 
    0.00959385992964501, -0.0023756731073804, 0.0204794411811398, 
    0.0132233976353453, -0.0102871180715493, -0.00853242320819103, 
    0.00688468158347666, 0.0226884226884227, -0.0132198753988756, 
    -0.00615953187557727, -0.00185931205453993, -0.0276311704439616, 
    -0.0370370370370371, -0.00116047745358094, -0.0293775933609959, 
    0.000341997264021954, 0.0210256410256409, -0.0165745856353591, 
    0.0156622403813413, 0.00134093194770379, 0.00150652828925346, 
    -0.0207253886010362, -0.00273084144051894, 0.0255005990073593, 
    0.00901201602136181, 0.00628514720476359, 0.0175871137409598, 
    0.00242287191083834, 0.00032226877215602, 0.00982603092783507, 
    -0.0012761205933961, -0.00511100463184799, 0.000481618237277326, 
    -0.00706033376123227, -0.00646412411118302, -0.110279765777489, 
    0.0157221206581353, -0.0129589632829373, 0.0255288110867979, 
    0.00782361308677104, -0.000705716302046522, 0.027542372881356, 
    -0.0020618556701032, 0.00447658402203865, -0.00308536167295181, 
    0.0110041265474552, 0.00544217687074822, -0.00541271989174563, 
    -0.00935374149659862, 0.0116738197424893, 0.00916341422026123, 
    0.00504456028249556, -0.00150577212648495, -0.0102211796246648, 
    -0.0135432537667174, -0.00858074480864934, 0.003635104725636, 
    0.00569161779924121, 0.0157777396672953, -0.0182340030390005, 
    0.012725709372313, 0.00950925454236717, 0.0218671152228762, 
    0.00954732510288059, -0.000978314038806327, -0.00440672433491107, 
    0.00508196721311482, -0.00505627140760079, -0.00786885245901636, 
    0.00908790482485133, 0.00425740953004738, -0.00538072721343552, 
    -0.000983606557377059, 0.0129635707253035, -0.00437388627895663, 
    0.00325414904002597, -0.0311385014596172, 0.0132239705390023, 
    -0.0079299520898729, -0.00932556203164026, 0.000840477391158112
    ), `10044` = c(NA, 0.0256410256410258, 0.00249999999999995, 
    -0.00249376558603487, -0.0083333333333333, 0.000840336134453779, 
    0.00923593618807717, 0.0391014975041597, -0.0120096076861489, 
    -0.00729335494327388, 0.00489795918367353, -0.00649878147847283, 
    0.017170891251022, -0.00401929260450151, 0.00322841000807106, 
    -0.00876910699919553, 0.0136352568785003, -0.00712627111858444, 
    0, 0.0024193548387097, -0.00321802091713586, -0.00968523002421318, 
    0.00407497962510184, -0.0089285714285714, -0.000819000819000992, 
    0.00491803278688518, -0.00407830342577475, 0.0141195741195741, 
    -0.00180901925313359, 0.00970873786407767, 0.00881410256410242, 
    -0.0015885623510723, 0.000795544948289484, -0.0174880763116058, 
    0.00404530744336573, -0.0112812248186946, -0.00488997555012216, 
    0.00654381654381653, -0.00681047038625204, -0.000507938588586221, 
    0.00327868852459035, -0.000816993464052285, -0.00899427636958305, 
    -0.00165016501650161, -0.00165289256198342, 0.0124172185430464, 
    -0.00368765331152898, -0.0102503918784725, -0.0074626865671642, 
    -0.0158730158730159, -0.00169779286926997, 0.056122448979592, 
    -0.0177133655394526, -0.012295081967213, 0, -0.0032365145228217, 
    0.00907501456997761, -0.0053877887788778, 0.00956473408711944, 
    -0.0115036976170912, 0, 0.00332502078137997, -0.00248550124275071, 
    0.00249169435215957, 0.0149130074565038, -0.0204081632653061, 
    0.00916666666666655, 0, -0.000825763831544202, 0.000371900826446225, 
    0.00293279358916121, -0.00329489291598029, 0.00543801652892562, 
    -0.00458662808857624, -0.00328654004954576, 0.000480522278007101, 
    -0.00960582974494861, -0.0142140468227426, 0.000848176420695568, 
    -0.0144067796610169, 0, -0.00343938091143603, 0.00690250215703192, 
    -0.00783204798628967, -0.00592472319624138, 0.00955690703735868, 
    -0.00413080895008611, -0.00535776011061173, -0.00086880973066894, 
    0.0057304347826086, -0.00310395213515591, -0.000867302688638327, 
    7.81249999999289e-05, 0.00426181982310414, 0.00172860847018153, 
    -0.00431406384814503, -0.00519930675909863, -0.00958188153310113, 
    0.00143359718557612, 0.00108902804247202, -0.00514971005465537, 
    -0.00352733686067008, 0.000884955752212369, -0.0141467727674625, 
    0.011659192825112, -0.0328014184397163, 0.00412465627864345, 
    -0.00502053856686446, -0.000917431192660523, -0.00183654729109284, 
    -0.00183992640294384, -0.00184331797235016, -0.000923361034164349, 
    0.0138632162661738, 0.0209662716499543, 0.00714285714285712, 
    0.00443262411347534, 0.00220653133274507, 0.00660501981505934, 
    -0.00349956255468054, -0.00482879719051799, -0.00926334362593739, 
    -0.0178094390026715, -0.00815956482320945, 0.0457038391224862
    ), `10051` = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, 
    NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_
    ), `10065` = c(NA, 0.00794701986754975, 0.00854139290407363, 
    0.00195439739413694, 0.00260078023407018, 0.00324254215304798, 
    0, 0.00646412411118291, 0.00642260757867685, 0, 0.00382897255902992, 
    -0.000635727908455119, 0.00381679389312972, 0.00633713561470217, 
    0.0100755667506296, -0.00311720698254359, -0.00250156347717334, 
    0.00752351097178683, -0.00560049782202865, -0.00876095118898623, 
    0.0037878787878789, -0.00125786163522013, -0.0214105793450883, 
    -0.0444015444015444, 0.0154882154882154, 0.00265251989389914, 
    -0.0337301587301587, 0.00889801505817944, 0.0128900949796471, 
    0.00200937709310112, 0.0140374331550801, 0.007910349373764, 
    0.00327011118378029, -0.00651890482398954, -0.00262467191601057, 
    0.00131578947368438, 0.0151116951379762, 0.00970873786407767, 
    -0.00641025641025639, -0.0109677419354839, -0.0123939986953685, 
    -0.00198150594451796, 0.00727994705493051, 0.00131406044678051, 
    0.000485564304461894, 0.00476146752889006, 0.0182767624020888, 
    0.00448717948717947, -0.00957243139757502, -0.009020618556701, 
    -0.0013003901170352, 0, -0.0110677083333334, 0.00197498354180392, 
    0.000657030223390365, -0.0262639527248851, -0.0215778826702629, 
    0.0248104755341143, -0.0161398789509077, -0.00273410799726592, 
    0.0123372172721041, -0.022342586323629, 0.0110803324099724, 
    0.0123287671232877, 0.00744248985115026, -0.0188045668233715, 
    0.00136892539356603, 0.0143540669856459, -0.00202156334231807, 
    0.00405131667792014, -0.00268997982515129, 0.00674308833445725, 
    0.0107166778298728, 0.00331345261762772, -0.00330250990752978, 
    -0.0119284294234592, 0.00201207243460755, -0.0120481927710843, 
    -0.00135501355013545, 0.0135685210312075, 0.000669344042838027, 
    -0.00468227424749157, 0.000672043010752743, -0.00402955003357963, 
    -0.00202292650033709, 0.0128378378378378, 0.000667111407605114, 
    0, 0.00733333333333319, 0.00992720052945062, 0.00262123197903019, 
    0.00130718954248366, -0.00391644908616195, 0, -0.00196592398427253, 
    -0.00328299409061072, 0.00856389986824779, -0.000653167864141047, 
    0.000653594771241828, 0.000653167864141047, -0.00391644908616195, 
    -0.00982961992136311, 0.013236267372601, -0.00587851077726975, 
    0.0124835742444152, 0.0019467878001298, 0.00388601036269431, 
    0.00774193548387081, 0.000640204865557159, 0.000639795265515053, 
    0.00127877237851659, 0.00510855683269473, -0.00190597204574328, 
    0.00190961171228521, -0.00254129606099118, -0.00254777070063694, 
    -0.00510855683269473, 0.00192554557124525, -0.00320307495195382, 
    0.00514138817480725, -0.0159846547314578, 0.00129954515919417, 
    -0.0064892926670993, 0.00326583932070545, 0.00390625), `10104` = c(NA, 
    0.023161055114733, 0.00985118423810527, 0.00601909506019083, 
    0.0105219723540333, 0.00163331972233571, -0.00529963310232384, 
    0.00307377049180335, 0.0114402451481102, 0.00161583518481123, 
    0.0137124420246018, -0.0007957032027055, 0.00696794744176787, 
    0.00257018584420732, 0.00808519029777166, 0.00645539906103298, 
    0.00291545189504361, 0.0222868217054264, -0.0153554502369668, 
    -0.0188679245283019, 0.0123626373626373, 0.000775344058926208, 
    -0.0290528762347473, -0.0406941950927588, 0.0272405905593678, 
    -0.0107287449392712, -0.041538776345406, 0.0190008539709647, 
    0.00879949717159012, 0.00706126687435105, 0.0197978964734997, 
    0.0240647118301314, 0.00138230647709325, -0.0161703805955433, 
    -0.00922028462617763, 0.00323690066761073, 0.018350473885864, 
    0.0132673267326733, -0.00859878835255046, -0.0011827321111767, 
    -0.0189461219656601, 0.0122711728022531, 0.0192766295707472, 
    0.00409436537336716, 0.00427184466019415, 0.00792730085073479, 
    0.0161135622482256, -0.00132150273739851, -0.0060491493383743, 
    -0.00494484594903, 0.0009556574923546, -0.00190949016612552, 
    -0.00612205854218484, -0.0943214629451397, -0.000637619553666147, 
    -0.0240323266695024, -0.0239703639137068, 0.0377316365260103, 
    -0.0236660929432012, -0.00881445570736017, 0.0171187194308582, 
    -0.0161748633879781, -0.00266607420573195, 0.0138115393183336, 
    0.00988793671720511, -0.0245865970409052, 0.00156145438322564, 
    0.0204899777282852, -0.00458315146224353, 0.00591975443981596, 
    0.00435919790758499, -0.000651041666666741, 0.0132464712269273, 
    0.00900128589798554, -0.00552251486830924, -0.0126014523707818, 
    -0.00930131948950896, -0.00589519650655013, 0.00483197891500109, 
    0.00459016393442635, -0.0093559617058312, 0.00307489567318253, 
    0.00613093934749287, -0.00957562568008719, -0.00988793671720489, 
    0.0144252108300045, 0.0078757383504704, -0.00282179292381168, 
    0.0139312146277755, 0.00751395448690428, -0.00234391647134025, 
    0, -0.000213583938487805, -0.00170903653065591, -0.00406591054996785, 
    -0.00472711645895996, 0.0192141623488773, -0.0177928404998942, 
    0.0114298037524261, -0.00938166311300637, 0.0116229014205769, 
    -0.0157446808510638, 0.0170773886727194, -0.00701381509032939, 
    0.013484589041096, -0.000633579725448863, -0.00401521555367701, 
    0.0131551029068533, -0.00146596858638748, 0.0104865771812082, 
    0.000207555002075477, 0.00539531023033835, -0.00371517027863777, 
    -0.0490988191423245, 0.00827886710239656, 0.00518582541054458, 
    -0.00537403267411862, -0.0745623514156041, 0.00653900046707157, 
    0.0232018561484919, 0.00408163265306127, 0.00293586269196022, 
    -0.0216167529835621, 0.0089758342922901, 0.00501824817518237
    ), `10107` = c(NA, 0.00465386852821403, 0.0088013896931094, 
    0.0123981173229251, 0.00102052386891938, -0.000679655641141808, 
    -0.00453411924733627, 0.00296060122978825, 0.0172570390554041, 
    -0.0139508928571429, 0.0202603282399547, -0.000443754160195375, 
    -0.00110987791342942, 0.0178888888888888, 0.00316559327584343, 
    -0.000870511425462572, 0.00555434545850586, 0.0187371385248565, 
    -0.00148841165213698, -0.0125638841567292, 0.0244770325641579, 
    -0.00789390590464156, -0.0263102058137068, -0.0411854434517324, 
    0.0378409090909091, -0.0188328041169386, -0.0513335565227094, 
    0.0372897306199271, 0.0107734180086185, 0.00785369684730175, 
    0.0109094957141267, 0.0203722057042175, -0.00712281459097774, 
    0.00782608695652165, -0.0132657463330458, 0.00262323751229654, 
    0.0254006322904174, 0.0144588560493302, -0.0127855795430727, 
    -0.00456475583864124, -0.00981124026874269, 0.0021540118470651, 
    0.00634067705534669, -0.00341734301580532, 0.00578654093441933, 
    0.00607287449392713, 0.0223445938790638, 0.00238243215247547, 
    -0.024387723468017, -0.0059315750450164, 0.00351624933404393, 
    0.00445954555107231, -0.0180761099365749, 0.0025837011519001, 
    -0.00697949103403839, -0.0290873702422145, -0.0290678249248246, 
    0.0757054370268408, -0.04595862657283, -0.000894154465183816, 
    0.0210314352835888, -0.0301303823819437, 0.013443289652056, 
    0.0292052168097203, 0.000541535795516079, -0.0232734358086165, 
    0.00598470575196708, 0.0232455657155448, -0.0109819121447028, 
    0.018724145438711, -0.00534302201325065, 0.0117103566824237, 
    0.0201762769459488, 0.00385135838451145, -0.00342181667357944, 
    -0.0115492664655082, 0.00368421052631573, -0.0233875196643942, 
    -0.00869845360824739, 0.0211244718882029, 0.016549968173138, 
    -0.0240033395950741, 0.0158254918733962, -0.0156842105263157, 
    0.00598866431397704, 0.0115871159774636, 0.0111391340899538, 
    -0.00426106838495111, 0.0117941759732805, 0.0100061893955024, 
    -0.00214482688183015, 0.00337768679631534, -0.00724268081199642, 
    -0.00174681463214121, -0.00998455995882652, 0.00187149095446038, 
    0.012868410128684, -0.00102459016393441, 0.011897435897436, 
    -0.00354753699574295, 0.000508595259892219, -0.00355835705571361, 
    0.00959085807570648, -0.00111167256189992, 0.0197288547146905, 
    0.00873102490326416, 0.00511458640700302, 0.0029357079949115, 
    -0.0157088496438677, 0.00743457573354478, -0.0057069762865295, 
    0.00257298367144987, -0.00454051919849974, 0.00565195835399113, 
    -0.0127193847367384, 0.00729052232098271, 0, 0.0100138806266112, 
    -0.00716599587709832, -0.00721771801463322, -0.0201175181754805, 
    0.00701290781583497, -0.0155429955591441, 0.0111749026040597, 
    -0.000202778059413933), `10113` = c(NA, 0.0104477611940299, 
    0.00377482356802883, 0.00784826684107265, 0, 0.00470473718364706, 
    -0.00339092523817219, 0.00729099157485424, 0.00643397136882728, 
    -0.010708007032124, 0.0126009693053313, -0.00143586470963619, 
    0.00383447835117412, 0.00381983129078467, -0.0152211828127478, 
    0.00193205602962476, 0.00723123895227396, 0.0100510529674538, 
    -0.0124782814721213, -0.0099168266154831, 0.00840064620355419, 
    -0.00528676706183917, -0.0304396843291995, -0.0340531561461794, 
    0.0268271711092003, -0.0113883771562552, -0.0408266982890056, 
    0.00459201695513967, 0.0265471167369902, -0.00291145744134269, 
    0.0273102026794916, 0.0183915733154989, -0.00197011984895745, 
    0.00575752590886669, 0.00098135426889101, 0.00392156862745097, 
    0.0139973958333333, -0.00914927768860352, -0.0153086019763486, 
    -0.00534671382742447, -0.0186900430036389, -0.000182032698466172, 
    0.00591376822344203, 0.0122339534104239, 0.0024834437086092, 
    0.00611065235342689, 0.0142810242941562, 0.00614986243728755, 
    -0.0128679427376549, -0.00716962685351141, -0.000656491055309405, 
    -0.00131384463787154, -0.00888011840157865, 0.00464576074332168, 
    0.00495458298926521, -0.0305669679539852, -0.0149177826750297, 
    0.021510927551196, -0.0213948787061994, -0.0108452401446032, 
    0.0223633832231116, -0.0244276108604987, 0.00959692898272557, 
    0.000518492913930135, 0.00846432889963733, -0.0119904076738609, 
    0.00416088765603329, 0.0229627071823204, 0.00236286919831219, 
    0.00791379020037031, -0.00735048446374875, -0.0090878492090205, 
    0.0110394021739131, 0.00974298672938012, -0.00661953086008982, 
    -0.00656650829563854, -0.00775455158462579, 0.00152905198776754, 
    -0.0171331636980493, 0.00621332412840858, 0.00257289879931388, 
    -0.000342172797262674, -0.000684579839123733, -0.00976194553861964, 
    -0.00951227948806632, 0.00646062510913215, 0.00208188757807082, 
    -0.00485976454293624, 0.00679722961135565, 0.0160705028512182, 
    -0.00816326530612244, 0.00411522633744865, 0.00887978142076506, 
    0.00778605280974953, -0.00461874370171311, 0.000253100480891, 
    0.00472334682861009, -0.001846877098724, -0.00790580319596312, 
    -0.00254323499491349, -0.00934897161312254, -0.0195607412491421, 
    0.00861743087154343, 0.000478897286942725, 0.00607006590357262, 
    0.0114600930873987, 0.0125300810569444, 0.0111092408685407, 
    -0.0169801897785917, 0.00999153259949193, -0.00570087189805502, 
    -0.00387858347386172, -0.0101574403250381, -0.00444672481614494, 
    -0.0125408005497338, -0.00521920668058451, -0.012242042672263, 
    0.0056657223796035, -0.0119718309859155, -0.00409836065573765, 
    -0.0331007335838254, 0.00388601036269431, -0.0171428571428571, 
    -0.0031882970742686, 0.00169332079021633)), class = c("data.table", 
"data.frame"), row.names = c(NA, -125L), .internal.selfref = <pointer: 0x7f878680d6e0>)

andresrcs · September 18, 2019, 1:29pm

I'm not familiar with data.table but here is a tidyverse based alternative in case you are interested.

library(tidyverse)
library(lubridate)

df %>% 
    gather(stock, return, -date) %>% 
    mutate(month = month(date, label = TRUE)) %>% 
    group_by(month, stock) %>% 
    top_n(5, return) %>%
    summarise(mean_return = mean(return)) %>% 
    spread(stock, mean_return)
#> # A tibble: 6 x 9
#> # Groups:   month [12]
#>   month `10026` `10028` `10032` `10044` `10065` `10104` `10107` `10113`
#>   <ord>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
#> 1 Jan    0.0195  0.0536  0.0213 0.0210  0.00811  0.0166  0.0197 0.00987
#> 2 Feb    0.0163  0.0181  0.0166 0.00882 0.0134   0.0217  0.0271 0.0226 
#> 3 Mar    0.0171  0.0333  0.0203 0.0181  0.0135   0.0205  0.0263 0.0153 
#> 4 Apr    0.0155  0.0211  0.0169 0.00716 0.0124   0.0133  0.0225 0.0124 
#> 5 May    0.0198  0.0559  0.0183 0.00564 0.0104   0.0153  0.0128 0.00963
#> 6 Jun    0.0133  0.0556  0.0141 0.0199  0.00688  0.0139  0.0114 0.0102

^{Created on 2019-09-18 by the reprex package (v0.3.0.9000)}

valeri · September 18, 2019, 1:38pm

Just a small augmentation to @andresrcs answer to incoroprate the "minimum 15 non-missing returns per month" condition:

df %>% 
	gather(stock, return, -date) %>% 
	mutate(month = month(date, label = TRUE)) %>% 
	group_by(month, stock) %>% 
	mutate(non_na_s = sum(!is.na(return))) %>% 
	filter(non_na_s >= 15) %>% 
	top_n(5, return) %>%
	summarise(mean_return = mean(return)) %>% 
	spread(stock, mean_return)

Pia · September 18, 2019, 1:48pm

Thank you for putting effort into this @andresrcs and @valeri. I am new to R, so maybe my question is unnecessary, but what exactly do I need to change in your code suggestions for it to work in my code as well? I am just getting error messages when copying and replacing df


# A tibble: 6 x 7,594
# Groups:   month [12]
  month `10026` `10028` `10032` `10044` `10065` `10104` `10107` `10113` `10138` `10145` `10158` `10180`
  <ord>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
1 Jan        NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA
2 Feb        NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA
3 Mär        NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA
4 Apr        NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA
5 Mai        NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA
6 Jun        NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA      NA
# … with 7,581 more variables: `10182` <dbl>, `10200` <dbl>, `10201` <dbl>, `10207` <dbl>,
#   `10220` <dbl>, `10232` <dbl>, `10239` <dbl>, `10252` <dbl>, `10253` <dbl>, `10257` <dbl>,```

andresrcs · September 18, 2019, 3:25pm

You shouldn't need to modify anything else, I'm not able to reproduce your issue with the sample data, very likely there is some sort of problem with your actual dataset, any chance you could share a link to the full dataset?

Yarnabrina · September 18, 2019, 4:08pm

Adding a data.table solution, partly because it is tagged as such, and mainly because I'm learning it. It's not much elegant, perhaps can be made much better and much much faster.

library(data.table)
molten_dataset <- melt(data = dataset, id.vars = "date", variable.name = "stocks", value.name = "returns", variable.factor = FALSE)
molten_dataset[, month := droplevels(x = factor(x = months(x = date), levels = month.name))]
molten_dataset[, available_observation_count := sum(!is.na(x = returns)), keyby = .(month, stocks)]
molten_dataset <- molten_dataset[available_observation_count >= 15]
setorder(x = molten_dataset, month, stocks, -returns, na.last = TRUE)
molten_dataset <- molten_dataset[molten_dataset[, .I[seq_len(length.out = .N) <= 5], keyby = .(month, stocks)]$V1]
dcast(data = molten_dataset, formula = (month ~ stocks), fun.aggregate = mean, value.var = "returns")
#>       month      10026      10028      10032       10044       10065
#> 1:  January 0.01951657 0.05357408 0.02134314 0.020956921 0.008110323
#> 2: February 0.01628198 0.01806643 0.01663919 0.008820853 0.013447235
#> 3:    March 0.01711665 0.03334903 0.02026125 0.018091621 0.013493174
#> 4:    April 0.01551876 0.02110638 0.01685962 0.007155101 0.012409673
#> 5:      May 0.01980886 0.05587761 0.01830537 0.005636054 0.010379708
#> 6:     June 0.01328659 0.05559602 0.01406554 0.019867075 0.006876341
#>         10104      10107       10113
#> 1: 0.01659264 0.01972409 0.009869739
#> 2: 0.02169091 0.02707247 0.022614692
#> 3: 0.02050234 0.02629900 0.015307830
#> 4: 0.01328744 0.02249514 0.012361271
#> 5: 0.01525418 0.01279453 0.009630200
#> 6: 0.01386079 0.01141665 0.010232203

Pia · September 18, 2019, 6:08pm

I ran over my entire code again and now it works just like it did for you two! looking at your code I am just now realizing how far out of my R skills this is.... also thank you @Yarnabrina for providing the alternative way with data.table! it's great to have 2 solutions that come up with the exact same numbers!!! thank you!!!

Just one more thing to add: I am just working with a sample date set containing data from Jan-Jun 2018 right now to speed up the process of writing the code. Once finished, I will run it with a much larger data set covering 40+ years of data, which means I will also have to order by year and by date. I would guess adding "year" to the code may do the trick..?

 df%>% 
  gather(stock, return, -date) %>% 
  mutate(year = year(date, label = TRUE)) %>% 
  mutate(month = month(date, label = TRUE)) %>% 
  group_by(year,month, stock) %>% 
  mutate(non_na_s = sum(!is.na(return))) %>% 
  filter(non_na_s >= 15) %>% 
  top_n(5, return) %>%
  summarise(mean_return = mean(return)) %>% 
  spread(stock, mean_return)```

valeri · September 18, 2019, 6:17pm

Perhaps even better to use the round_date or floor_date function to round your dates to a month and then group_by that variable. See documentation here: https://rdrr.io/cran/lubridate/man/round_date.html

Just an example of how that would work:

lubridate::floor_date(Sys.Date(), unit = 'month')
#> [1] "2019-09-01"

Created on 2019-09-18 by the reprex package (v0.3.0)

andresrcs · September 19, 2019, 3:29am

Maybe a little bit of an overkill for this task but tsibble package is pretty handy for time aggregations, see this example:

library(tidyverse)
library(tsibble)

df %>% 
    gather(stock, return, -date) %>%
    as_tsibble(key = stock, index = date) %>% 
    group_by_key() %>%
    index_by(year_month = ~ yearmonth(.)) %>%
    mutate(non_na_s = sum(!is.na(return))) %>% 
    filter(non_na_s >= 15) %>% 
    top_n(5, return) %>%
    summarise(mean_return = mean(return)) %>% 
    spread(stock, mean_return)
#> # A tsibble: 6 x 9 [1M]
#>   year_month `10026` `10028` `10032` `10044` `10065` `10104` `10107`
#>        <mth>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>   <dbl>
#> 1   2018 ene  0.0195  0.0536  0.0213 0.0210  0.00811  0.0166  0.0197
#> 2   2018 feb  0.0163  0.0181  0.0166 0.00882 0.0134   0.0217  0.0271
#> 3   2018 mar  0.0171  0.0333  0.0203 0.0181  0.0135   0.0205  0.0263
#> 4   2018 abr  0.0155  0.0211  0.0169 0.00716 0.0124   0.0133  0.0225
#> 5   2018 may  0.0198  0.0559  0.0183 0.00564 0.0104   0.0153  0.0128
#> 6   2018 jun  0.0133  0.0556  0.0141 0.0199  0.00688  0.0139  0.0114
#> # … with 1 more variable: `10113` <dbl>

Pia · September 19, 2019, 6:50am

Fantastic, that works perfectly!

valeri · September 19, 2019, 7:11am

Nice ... didn't know about tsibble

system · September 26, 2019, 7:11am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.