Nikitau Nikitau -4 years ago 76
R Question

Calculating maximum of a time series within row subintervals

I have time series data (in xts form) that looks like:

EUR.CLOSE
2014-08-28 1.3182
2014-08-29 1.3132
2014-09-01 1.3128
2014-09-02 1.3133
2014-09-03 1.3150
2014-09-04 1.2944
2014-09-05 1.2951
2014-09-08 1.2895
2014-09-09 1.2937
2014-09-10 1.2917
2014-09-11 1.2925
2014-09-12 1.2963
2014-09-15 1.2940
2014-09-16 1.2960
2014-09-17 1.2865
2014-09-18 1.2923
2014-09-19 1.2829
2014-09-22 1.2849
2014-09-23 1.2847
2014-09-24 1.2780
2014-09-25 1.2751
2014-09-26 1.2684
2014-09-29 1.2685
2014-09-30 1.2631
2014-10-01 1.2623
2014-10-02 1.2669
2014-10-03 1.2516
2014-10-06 1.2655


And I have row intervals that are calculated based on certain signals. In this case, let's say the row intervals calculated are
rowintv <- c(5,10,17,22)
. I want the calculate the maximum closing price based on the periods of the row intervals. So for instance, I want the maximum between
data[1,], data[5,]
and
data[6,], data[10,]
and
data[11,], data[17,]
.

Attached is a sample dput:

structure(c(1.3182, 1.3132, 1.3128, 1.3133, 1.315, 1.2944, 1.2951,
1.2895, 1.2937, 1.2917, 1.2925, 1.2963, 1.294, 1.296, 1.2865,
1.2923, 1.2829, 1.2849, 1.2847, 1.278, 1.2751, 1.2684, 1.2685,
1.2631, 1.2623, 1.2669, 1.2516, 1.2655, 1.2669, 1.2734, 1.2691,
1.2628, 1.2752, 1.2658, 1.2838, 1.2809, 1.2761, 1.28, 1.2716,
1.2649, 1.2646, 1.2671, 1.2698, 1.2734, 1.2632, 1.2613, 1.2525,
1.2482, 1.2546, 1.2486, 1.2375, 1.2455, 1.2421, 1.2475, 1.2438,
1.2477, 1.2525, 1.245, 1.2536, 1.2554, 1.2539, 1.2391, 1.2442,
1.2474, 1.2506, 1.2467, 1.2452, 1.247, 1.2383, 1.2311, 1.2379,
1.2283, 1.2317, 1.2374, 1.2448, 1.2411, 1.2462, 1.2437, 1.2511,
1.2342, 1.2286, 1.2229, 1.223, 1.2172, 1.2196, 1.2225, 1.2183,
1.2152, 1.2156, 1.2098, 1.2104, 1.2002, 1.1933, 1.189, 1.1839,
1.1793, 1.1842, 1.1834, 1.1773, 1.1789, 1.1633, 1.1567, 1.1606,
1.155, 1.161, 1.1366, 1.1204, 1.1238, 1.1381, 1.1287, 1.132,
1.1291, 1.1341, 1.1481, 1.1345, 1.1477, 1.1316, 1.1325, 1.1321,
1.1336, 1.1403, 1.1394, 1.1355, 1.1411, 1.1397, 1.1368, 1.1381,
1.1335, 1.134, 1.1361, 1.1198, 1.1196, 1.1184, 1.1176, 1.1078,
1.103, 1.0844, 1.0852, 1.0698, 1.0547, 1.0635, 1.0496, 1.0568,
1.0597, 1.0864, 1.066, 1.0821, 1.0946, 1.0924, 1.097, 1.0884,
1.0889, 1.0833, 1.0731, 1.0763, 1.088, 1.0969, 1.0922, 1.0814,
1.0781, 1.0659, 1.0604, 1.0567, 1.0655, 1.0684, 1.0761, 1.0806,
1.0738, 1.0736, 1.0725, 1.0824, 1.0873, 1.0891, 1.0981, 1.1128,
1.1224, 1.1199, 1.1146, 1.1185, 1.1347, 1.1267, 1.1199, 1.1155,
1.1213, 1.1354, 1.141, 1.1451, 1.1315, 1.115, 1.1094, 1.1112,
1.1013, 1.0978, 1.0873, 1.0904, 1.0949, 1.0986, 1.0927, 1.1151,
1.1275, 1.1238, 1.1114, 1.1291, 1.1283, 1.1324, 1.1258, 1.1266,
1.1283, 1.1248, 1.1337, 1.1359, 1.1352, 1.1341, 1.1167, 1.1205,
1.1205, 1.1167, 1.1236, 1.1147, 1.1053, 1.1084, 1.1114, 1.1056,
1.1011, 1.1077, 1.1036, 1.1162, 1.1002, 1.1009, 1.095, 1.0875,
1.083, 1.0825, 1.0935, 1.0929, 1.0984, 1.0984, 1.1088, 1.106,
1.0984, 1.0932, 1.0984, 1.095, 1.0881, 1.0906, 1.0925, 1.0967,
1.1019, 1.1042, 1.1159, 1.115, 1.1109, 1.1078, 1.1024, 1.112,
1.1242, 1.1386, 1.1619, 1.1517, 1.1314, 1.1246, 1.1185, 1.1211,
1.1315, 1.1227, 1.1123, 1.1149, 1.117, 1.1203, 1.1207, 1.128,
1.1338, 1.1317, 1.1269, 1.129, 1.1435, 1.1298, 1.119, 1.112,
1.1186, 1.123, 1.1195, 1.1244, 1.1249, 1.1177, 1.1195, 1.1216,
1.1188, 1.1272, 1.1237, 1.1276, 1.1358, 1.1358, 1.1379, 1.1474,
1.1386, 1.1348, 1.1327, 1.1346, 1.1339, 1.1109, 1.1018, 1.1058,
1.1051, 1.0923, 1.0977, 1.1006, 1.1016, 1.0964, 1.0866, 1.0884,
1.0741, 1.0752, 1.0724, 1.0743, 1.0814, 1.0773, 1.0686, 1.0642,
1.066, 1.0734, 1.0646, 1.0636, 1.0643, 1.0624, 1.061, 1.0593,
1.0565, 1.0633, 1.0615, 1.094, 1.0881, 1.0837, 1.0892, 1.1025,
1.0941, 1.0986, 1.0992, 1.0931, 1.0912, 1.0826, 1.0868, 1.0915,
1.0957, 1.0912, 1.0963, 1.096, 1.0968, 1.092, 1.0933, 1.0862,
1.0856, 1.0831, 1.0748, 1.0781, 1.0932, 1.0922, 1.0859, 1.0858,
1.0877, 1.0865, 1.0916, 1.0892, 1.0908, 1.089, 1.0874, 1.0796,
1.0849, 1.087, 1.0893, 1.094, 1.0831, 1.0888, 1.0919, 1.1105,
1.1209, 1.1158, 1.1193, 1.1293, 1.1292, 1.1323, 1.1256, 1.1156,
1.1144, 1.1128, 1.1107, 1.113, 1.103, 1.102, 1.1013, 1.1018,
1.0934, 1.0873, 1.0868, 1.0868, 1.0957, 1.1005, 1.1014, 1.1011,
1.0999, 1.1177, 1.1156, 1.1103, 1.1109, 1.1224, 1.1318, 1.127,
1.1241, 1.1217, 1.1181, 1.1174, 1.1167, 1.1196, 1.1291, 1.1338,
1.138, 1.1391, 1.1391, 1.1384, 1.1399, 1.1378, 1.1399, 1.1408,
1.1386, 1.1274, 1.1268, 1.1284, 1.1313, 1.1358, 1.1297, 1.1288,
1.1222, 1.1268, 1.1297, 1.1322, 1.1352, 1.1451, 1.1534, 1.1496,
1.1487, 1.1405, 1.1404, 1.1383, 1.1372, 1.1426, 1.1377, 1.1309,
1.1321, 1.1313, 1.1216, 1.1203, 1.1224, 1.122, 1.1141, 1.1155,
1.1194, 1.1115, 1.1138, 1.1132, 1.1188, 1.1151, 1.1367, 1.1355,
1.1358, 1.1395, 1.1316, 1.1251, 1.1292, 1.1207, 1.126, 1.1225,
1.1277, 1.1314, 1.1242, 1.1296, 1.1385, 1.1117, 1.1025, 1.1065,
1.1125, 1.1106, 1.1136, 1.1154, 1.1076, 1.11, 1.1063, 1.1051,
1.1058, 1.1061, 1.109, 1.112, 1.1035, 1.1075, 1.1021, 1.1015,
1.1026, 1.0977, 1.0995, 1.0986, 1.1058, 1.1077, 1.1174, 1.1163,
1.1224, 1.1149, 1.113, 1.1086, 1.1088, 1.1117, 1.1176, 1.1137,
1.1162, 1.1184, 1.1279, 1.1289, 1.1354, 1.1325, 1.132, 1.1305,
1.1264, 1.1285, 1.1198, 1.1189, 1.1143, 1.1158, 1.1197, 1.1156,
1.1147, 1.1255, 1.1239, 1.126, 1.1233, 1.1235, 1.122, 1.125,
1.1244, 1.1155, 1.1175, 1.1151, 1.1189, 1.1208, 1.1226, 1.1254,
1.1215, 1.1217, 1.1222, 1.1235, 1.1211, 1.1204, 1.1205, 1.1151,
1.1201, 1.1139, 1.1054, 1.1007, 1.1056, 1.0972, 1.1, 1.0981,
1.0974, 1.0929, 1.0884, 1.0882, 1.0889, 1.0908, 1.0897, 1.0985,
1.0981, 1.1055, 1.1098, 1.1105, 1.1141, 1.1041, 1.1026, 1.091,
1.0893, 1.0855, 1.0737, 1.0722, 1.0691, 1.0626, 1.0588, 1.063,
1.0627, 1.0553, 1.0554, 1.0589, 1.0614, 1.065, 1.0589, 1.0661,
1.0664, 1.0764, 1.0718, 1.0753, 1.0615, 1.0561, 1.0635, 1.0626,
1.0536, 1.0414, 1.0451, 1.0402, 1.0388, 1.0424, 1.0437, 1.0456,
1.0455, 1.0457, 1.0413, 1.049, 1.0517, 1.0455, 1.0405, 1.0489,
1.0607, 1.0532, 1.0574, 1.0554, 1.0582, 1.0613, 1.0643, 1.0601,
1.0713, 1.063, 1.0664, 1.0703, 1.0765, 1.0731, 1.0748, 1.0682,
1.0699, 1.0695, 1.0798, 1.0769, 1.0759, 1.0783, 1.075, 1.0683,
1.0698, 1.0655, 1.0643, 1.0598, 1.0578, 1.0601, 1.0674, 1.0616,
1.0614, 1.0536, 1.0558, 1.0582), index = structure(c(1409202000,
1409288400, 1409547600, 1409634000, 1409720400, 1409806800, 1409893200,
1410152400, 1410238800, 1410325200, 1410411600, 1410498000, 1410757200,
1410843600, 1410930000, 1411016400, 1411102800, 1411362000, 1411448400,
1411534800, 1411621200, 1411707600, 1411966800, 1412053200, 1412139600,
1412226000, 1412312400, 1412571600, 1412658000, 1412744400, 1412830800,
1412917200, 1413176400, 1413262800, 1413349200, 1413435600, 1413522000,
1413781200, 1413867600, 1413954000, 1414040400, 1414126800, 1414386000,
1414472400, 1414558800, 1414645200, 1414731600, 1414994400, 1415080800,
1415167200, 1415253600, 1415340000, 1415599200, 1415685600, 1415772000,
1415858400, 1415944800, 1416204000, 1416290400, 1416376800, 1416463200,
1416549600, 1416808800, 1416895200, 1416981600, 1417068000, 1417154400,
1417413600, 1417500000, 1417586400, 1417672800, 1417759200, 1418018400,
1418104800, 1418191200, 1418277600, 1418364000, 1418623200, 1418709600,
1418796000, 1418882400, 1418968800, 1419228000, 1419314400, 1419400800,
1419487200, 1419573600, 1419832800, 1419919200, 1420005600, 1420092000,
1420178400, 1420437600, 1420524000, 1420610400, 1420696800, 1420783200,
1421042400, 1421128800, 1421215200, 1421301600, 1421388000, 1421647200,
1421733600, 1421820000, 1421906400, 1421992800, 1422252000, 1422338400,
1422424800, 1422511200, 1422597600, 1422856800, 1422943200, 1423029600,
1423116000, 1423202400, 1423461600, 1423548000, 1423634400, 1423720800,
1423807200, 1424066400, 1424152800, 1424239200, 1424325600, 1424412000,
1424671200, 1424757600, 1424844000, 1424930400, 1425016800, 1425276000,
1425362400, 1425448800, 1425535200, 1425621600, 1425877200, 1425963600,
1426050000, 1426136400, 1426222800, 1426482000, 1426568400, 1426654800,
1426741200, 1426827600, 1427086800, 1427173200, 1427259600, 1427346000,
1427432400, 1427691600, 1427778000, 1427864400, 1427950800, 1428037200,
1428296400, 1428382800, 1428469200, 1428555600, 1428642000, 1428901200,
1428987600, 1429074000, 1429160400, 1429246800, 1429506000, 1429592400,
1429678800, 1429765200, 1429851600, 1430110800, 1430197200, 1430283600,
1430370000, 1430456400, 1430715600, 1430802000, 1430888400, 1430974800,
1431061200, 1431320400, 1431406800, 1431493200, 1431579600, 1431666000,
1431925200, 1432011600, 1432098000, 1432184400, 1432270800, 1432530000,
1432616400, 1432702800, 1432789200, 1432875600, 1433134800, 1433221200,
1433307600, 1433394000, 1433480400, 1433739600, 1433826000, 1433912400,
1433998800, 1434085200, 1434344400, 1434430800, 1434517200, 1434603600,
1434690000, 1434949200, 1435035600, 1435122000, 1435208400, 1435294800,
1435554000, 1435640400, 1435726800, 1435813200, 1435899600, 1436158800,
1436245200, 1436331600, 1436418000, 1436504400, 1436763600, 1436850000,
1436936400, 1437022800, 1437109200, 1437368400, 1437454800, 1437541200,
1437627600, 1437714000, 1437973200, 1438059600, 1438146000, 1438232400,
1438318800, 1438578000, 1438664400, 1438750800, 1438837200, 1438923600,
1439182800, 1439269200, 1439355600, 1439442000, 1439528400, 1439787600,
1439874000, 1439960400, 1440046800, 1440133200, 1440392400, 1440478800,
1440565200, 1440651600, 1440738000, 1440997200, 1441083600, 1441170000,
1441256400, 1441342800, 1441602000, 1441688400, 1441774800, 1441861200,
1441947600, 1442206800, 1442293200, 1442379600, 1442466000, 1442552400,
1442811600, 1442898000, 1442984400, 1443070800, 1443157200, 1443416400,
1443502800, 1443589200, 1443675600, 1443762000, 1444021200, 1444107600,
1444194000, 1444280400, 1444366800, 1444626000, 1444712400, 1444798800,
1444885200, 1444971600, 1445230800, 1445317200, 1445403600, 1445490000,
1445576400, 1445835600, 1445922000, 1446008400, 1446094800, 1446181200,
1446444000, 1446530400, 1446616800, 1446703200, 1446789600, 1447048800,
1447135200, 1447221600, 1447308000, 1447394400, 1447653600, 1447740000,
1447826400, 1447912800, 1447999200, 1448258400, 1448344800, 1448431200,
1448517600, 1448604000, 1448863200, 1448949600, 1449036000, 1449122400,
1449208800, 1449468000, 1449554400, 1449640800, 1449727200, 1449813600,
1450072800, 1450159200, 1450245600, 1450332000, 1450418400, 1450677600,
1450764000, 1450850400, 1450936800, 1451023200, 1451282400, 1451368800,
1451455200, 1451541600, 1451628000, 1451887200, 1451973600, 1452060000,
1452146400, 1452232800, 1452492000, 1452578400, 1452664800, 1452751200,
1452837600, 1453096800, 1453183200, 1453269600, 1453356000, 1453442400,
1453701600, 1453788000, 1453874400, 1453960800, 1454047200, 1454306400,
1454392800, 1454479200, 1454565600, 1454652000, 1454911200, 1454997600,
1455084000, 1455170400, 1455256800, 1455516000, 1455602400, 1455688800,
1455775200, 1455861600, 1456120800, 1456207200, 1456293600, 1456380000,
1456466400, 1456725600, 1456812000, 1456898400, 1456984800, 1457071200,
1457330400, 1457416800, 1457503200, 1457589600, 1457676000, 1457931600,
1458018000, 1458104400, 1458190800, 1458277200, 1458536400, 1458622800,
1458709200, 1458795600, 1458882000, 1459141200, 1459227600, 1459314000,
1459400400, 1459486800, 1459746000, 1459832400, 1459918800, 1460005200,
1460091600, 1460350800, 1460437200, 1460523600, 1460610000, 1460696400,
1460955600, 1461042000, 1461128400, 1461214800, 1461301200, 1461560400,
1461646800, 1461733200, 1461819600, 1461906000, 1462165200, 1462251600,
1462338000, 1462424400, 1462510800, 1462770000, 1462856400, 1462942800,
1463029200, 1463115600, 1463374800, 1463461200, 1463547600, 1463634000,
1463720400, 1463979600, 1464066000, 1464152400, 1464238800, 1464325200,
1464584400, 1464670800, 1464757200, 1464843600, 1464930000, 1465189200,
1465275600, 1465362000, 1465448400, 1465534800, 1465794000, 1465880400,
1465966800, 1466053200, 1466139600, 1466398800, 1466485200, 1466571600,
1466658000, 1466744400, 1467003600, 1467090000, 1467176400, 1467262800,
1467349200, 1467608400, 1467694800, 1467781200, 1467867600, 1467954000,
1468213200, 1468299600, 1468386000, 1468472400, 1468558800, 1468818000,
1468904400, 1468990800, 1469077200, 1469163600, 1469422800, 1469509200,
1469595600, 1469682000, 1469768400, 1470027600, 1470114000, 1470200400,
1470286800, 1470373200, 1470632400, 1470718800, 1470805200, 1470891600,
1470978000, 1471237200, 1471323600, 1471410000, 1471496400, 1471582800,
1471842000, 1471928400, 1472014800, 1472101200, 1472187600, 1472446800,
1472533200, 1472619600, 1472706000, 1472792400, 1473051600, 1473138000,
1473224400, 1473310800, 1473397200, 1473656400, 1473742800, 1473829200,
1473915600, 1474002000, 1474261200, 1474347600, 1474434000, 1474520400,
1474606800, 1474866000, 1474952400, 1475038800, 1475125200, 1475211600,
1475470800, 1475557200, 1475643600, 1475730000, 1475816400, 1476075600,
1476162000, 1476248400, 1476334800, 1476421200, 1476680400, 1476766800,
1476853200, 1476939600, 1477026000, 1477285200, 1477371600, 1477458000,
1477544400, 1477630800, 1477890000, 1477976400, 1478062800, 1478149200,
1478235600, 1478498400, 1478584800, 1478671200, 1478757600, 1478844000,
1479103200, 1479189600, 1479276000, 1479362400, 1479448800, 1479708000,
1479794400, 1479880800, 1479967200, 1480053600, 1480312800, 1480399200,
1480485600, 1480572000, 1480658400, 1480917600, 1481004000, 1481090400,
1481176800, 1481263200, 1481522400, 1481608800, 1481695200, 1481781600,
1481868000, 1482127200, 1482213600, 1482300000, 1482386400, 1482472800,
1482732000, 1482818400, 1482904800, 1482991200, 1483077600, 1483336800,
1483423200, 1483509600, 1483596000, 1483682400, 1483941600, 1484028000,
1484114400, 1484200800, 1484287200, 1484546400, 1484632800, 1484719200,
1484805600, 1484892000, 1485151200, 1485237600, 1485324000, 1485410400,
1485496800, 1485756000, 1485842400, 1485928800, 1486015200, 1486101600,
1486360800, 1486447200, 1486533600, 1486620000, 1486706400, 1486965600,
1487052000, 1487138400, 1487224800, 1487311200, 1487570400, 1487656800,
1487743200, 1487829600), tzone = "", tclass = c("POSIXct", "POSIXt"
)), .indexCLASS = c("POSIXct", "POSIXt"), tclass = c("POSIXct",
"POSIXt"), .indexTZ = "", tzone = "", class = c("xts", "zoo"), .Dim = c(651L,
1L), .Dimnames = list(NULL, "EUR.CLOSE"))

Answer Source

Using findInterval to break the data into intervals as defined by rowintv

returning intervals to confirm it functions correctly

library(zoo) # may need to change seq_along if using different data type
lapply(seq_along(rowintv), function(x) data[findInterval(seq_along(data), rowintv, left.open = TRUE) == (x - 1)])

[[1]]
1409202000 1409288400 1409547600 1409634000 1409720400 
    1.3182     1.3132     1.3128     1.3133     1.3150 

[[2]]
1409806800 1409893200 1410152400 1410238800 1410325200 
    1.2944     1.2951     1.2895     1.2937     1.2917 

[[3]]
1410411600 1410498000 1410757200 1410843600 1410930000 1411016400 1411102800 
    1.2925     1.2963     1.2940     1.2960     1.2865     1.2923     1.2829 

[[4]]
1411362000 1411448400 1411534800 1411621200 1411707600 
    1.2849     1.2847     1.2780     1.2751     1.2684 

Obtain maximum values

sapply(seq_along(rowintv), function(x) max(data[findInterval(seq_along(data), rowintv, left.open = TRUE) == (x - 1)]))

[1] 1.3182 1.2951 1.2963 1.2849
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download