Is there a way to aggregate (e.g., sum, mean) intervals along a pandas series? I did it the long and non-elegant way but I feel like there has to be a built-in way to do this since people do a lot of time series analysis with pandas (this isn't time series btw). Maybe with use of the pd.Interval object?
import numpy as np
import pandas as pd
from collections import OrderedDict
data = pd.Series({0.0: 0.0027077328584676173,0.0041841004184100415: 0.002791971614505515,0.008368200836820083: 0.0029836729636686537,0.012552301255230124: 0.00310210056994597,0.016736401673640166: 0.003123592522767784,0.02092050209205021: 0.0031416073125521624,0.025104602510460247: 0.0031485613317755977,0.02928870292887029: 0.0031557637903831775,0.03347280334728033: 0.00326313482475896,0.03765690376569037: 0.003301728046497936,0.04184100418410042: 0.0033366539533600335,0.046025104602510455: 0.0033702672902052584,0.050209205020920494: 0.0033868374139273388,0.05439330543933054: 0.003396846064522763,0.05857740585774058: 0.0034311293771955432,0.06276150627615062: 0.0034554861912855788,0.06694560669456066: 0.0035232368934153594,0.0711297071129707: 0.0035777792118595695,0.07531380753138074: 0.003592954000065738,0.0794979079497908: 0.00361514147677572,0.08368200836820083: 0.003623149988144421,0.08786610878661087: 0.0037193787063349777,0.09205020920502091: 0.0037478924475459496,0.09623430962343095: 0.003916888466467329,0.10041841004184099: 0.00394360032517084,0.10460251046025104: 0.0039620222687681865,0.10878661087866108: 0.003999287617522048,0.11297071129707112: 0.004006491834823014,0.11715481171548116: 0.004013290425253716,0.1213389121338912: 0.004042553765475244,0.12552301255230125: 0.0040505029191929675,0.12970711297071127: 0.004055597770467728,0.13389121338912133: 0.004089750738907019,0.13807531380753138: 0.0041098023814089,0.1422594142259414: 0.004120096873249847,0.14644351464435146: 0.004131372273604568,0.15062761506276148: 0.004148097669469449,0.15481171548117154: 0.004157794773892295,0.1589958158995816: 0.004174682633106945,0.1631799163179916: 0.004192587889456918,0.16736401673640167: 0.004213104284729356,0.1715481171548117: 0.004221311516318688,0.17573221757322174: 0.004256186252391833,0.1799163179916318: 0.004261480055637909,0.18410041841004182: 0.004270623452687678,0.18828451882845187: 0.004272709261097341,0.1924686192468619: 0.004279286262277982,0.19665271966527195: 0.004290354456229598,0.20083682008368198: 0.00429265413096169,0.20502092050209203: 0.004305005522847871,0.20920502092050208: 0.004314790518864923,0.2133891213389121: 0.004327997376040813,0.21757322175732216: 0.00434312903011855,0.22175732217573219: 0.004356549753616868,0.22594142259414224: 0.004386888022361544,0.2301255230125523: 0.00439303729089733,0.23430962343096232: 0.004422070255932851,0.23849372384937237: 0.004443617899683411,0.2426778242677824: 0.0044989564223775055,0.24686192468619245: 0.004513232611165534,0.2510460251046025: 0.0045208678536288955,0.25523012552301255: 0.004539524631430706,0.25941422594142255: 0.004529556750098442,0.2635983263598326: 0.004508705475906116,0.26778242677824265: 0.004513649029044765,0.2719665271966527: 0.004518791534154726,0.27615062761506276: 0.0045270028215164395,0.28033472803347276: 0.004505184101319499,0.2845188284518828: 0.004521666183678893,0.28870292887029286: 0.004526391971589177,0.2928870292887029: 0.004523250600927973,0.29707112970711297: 0.004520018053252983,0.30125523012552297: 0.004538980712530086,0.305439330543933: 0.0045380561011169425,0.30962343096234307: 0.004539931867225878,0.3138075313807531: 0.0045297933358983316,0.3179916317991632: 0.004542771255317727,0.3221757322175732: 0.004549828843957832,0.3263598326359832: 0.004550485282391987,0.3305439330543933: 0.004556193280889516,0.33472803347280333: 0.004554870690649502,0.3389121338912134: 0.004559534142340666,0.3430962343096234: 0.004583898836441291,0.34728033472803344: 0.004619896072890582,0.3514644351464435: 0.004629519750548382,0.35564853556485354: 0.004631610767251927,0.3598326359832636: 0.0045911260198158626,0.3640167364016736: 0.0044512433308574436,0.36820083682008364: 0.004417195683430246,0.3723849372384937: 0.004431198416061163,0.37656903765690375: 0.004561920332142275,0.3807531380753138: 0.004559507831241725,0.3849372384937238: 0.004584901700961176,0.38912133891213385: 0.004556538719067685,0.3933054393305439: 0.004400983367912775,0.39748953974895396: 0.004188471438719717,0.40167364016736395: 0.0038262699511768168,0.405857740585774: 0.0039044800555612983,0.41004184100418406: 0.00387564153741927,0.4142259414225941: 0.003870709224378518,0.41841004184100417: 0.003894154807582368,0.42259414225941416: 0.00390737259800472,0.4267782426778242: 0.003936119939132963,0.43096234309623427: 0.003972450812900475,0.4351464435146443: 0.004406118869896118,0.4393305439330544: 0.0044837934879721035,0.44351464435146437: 0.004490398409393102,0.4476987447698744: 0.004499745959393594,0.4518828451882845: 0.00449448764954703,0.45606694560669453: 0.004457123237103855,0.4602510460251046: 0.004464632572625272,0.4644351464435146: 0.004508066166919865,0.46861924686192463: 0.0045105979897272774,0.4728033472803347: 0.004505734523272998,0.47698744769874474: 0.004522906547021988,0.4811715481171548: 0.004530161635791457,0.4853556485355648: 0.004495068064600183,0.48953974895397484: 0.004527807040916445,0.4937238493723849: 0.00454281822078233,0.49790794979079495: 0.004546211323345219,0.502092050209205: 0.004563819954971688,0.506276150627615: 0.0045677911679777505,0.5104602510460251: 0.00455560553195888,0.5146443514644351: 0.004586589223692534,0.5188284518828451: 0.004588770108021919,0.5230125523012552: 0.004602982044814485,0.5271966527196652: 0.004623879446332334,0.5313807531380753: 0.004618186519268873,0.5355648535564853: 0.004604133378887333,0.5397489539748953: 0.004594606999344009,0.5439330543933054: 0.004568094153516638,0.5481171548117154: 0.004588293120147941,0.5523012552301255: 0.004626074414188226,0.5564853556485355: 0.004639315612458638,0.5606694560669455: 0.004685143582983844,0.5648535564853556: 0.004670852404960463,0.5690376569037656: 0.00468121382761154,0.5732217573221757: 0.004658405249000472,0.5774058577405857: 0.004661443589063638,0.5815899581589957: 0.00460113491679092,0.5857740585774058: 0.004556595584244846,0.5899581589958158: 0.0045202273086159565,0.5941422594142259: 0.0044427665710583255,0.5983263598326359: 0.004472507983456033,0.6025104602510459: 0.004456961221103522,0.606694560669456: 0.00444082043873469,0.610878661087866: 0.004396049264964732,0.6150627615062761: 0.004345753980548967,0.6192468619246861: 0.00430494132074726,0.6234309623430961: 0.004264570087850253,0.6276150627615062: 0.004244123983908276,0.6317991631799162: 0.004263653049596264,0.6359832635983264: 0.004185144165863291,0.6401673640167364: 0.004056405551783485,0.6443514644351463: 0.003966541024361489,0.6485355648535565: 0.003981233010011401,0.6527196652719665: 0.00397190810460413,0.6569037656903766: 0.003967510079291008,0.6610878661087866: 0.003965428020018801,0.6652719665271966: 0.00387382189495957,0.6694560669456067: 0.0038599000871521023,0.6736401673640167: 0.003851531230568594,0.6778242677824268: 0.003904170708117067,0.6820083682008368: 0.003909372339127324,0.6861924686192468: 0.003931760908237683,0.6903765690376569: 0.0039400922809776084,0.6945606694560669: 0.0039187551434621696,0.698744769874477: 0.003880264039297912,0.702928870292887: 0.003880679304655641,0.707112970711297: 0.0039045303647447637,0.7112970711297071: 0.0038977460002945752,0.7154811715481171: 0.003884401464574688,0.7196652719665272: 0.003930485365011453,0.7238493723849372: 0.0038919127972051166,0.7280334728033472: 0.0039208211496246665,0.7322175732217573: 0.00392084218507647,0.7364016736401673: 0.004114168862989343,0.7405857740585774: 0.004298515841489408,0.7447698744769874: 0.004313146328842769,0.7489539748953974: 0.004507075983770279,0.7531380753138075: 0.0045112961676646406,0.7573221757322175: 0.004502879888996614,0.7615062761506276: 0.004499522893319595,0.7656903765690376: 0.0044611809466458775,0.7698744769874476: 0.004441373919506444,0.7740585774058577: 0.004431994018855501,0.7782426778242677: 0.004410341974647154,0.7824267782426778: 0.004407266614458285,0.7866108786610878: 0.00439699422839193,0.7907949790794978: 0.004377436635858676,0.7949790794979079: 0.004360062449603033,0.7991631799163179: 0.004334990916415593,0.8033472803347279: 0.0043053152894044775,0.807531380753138: 0.004299540898698937,0.811715481171548: 0.004292780780235888,0.8158995815899581: 0.0042857760464103944,0.8200836820083681: 0.0042838512495085475,0.8242677824267781: 0.00427540230624067,0.8284518828451882: 0.004265220326618783,0.8326359832635982: 0.004258643325438144,0.8368200836820083: 0.004248216730453809,0.8410041841004183: 0.004238868110347041,0.8451882845188283: 0.004220947550812318,0.8493723849372384: 0.00416931971618869,0.8535564853556484: 0.004167417025061434,0.8577405857740585: 0.004165033746474712,0.8619246861924685: 0.00415209361043586,0.8661087866108785: 0.0041256577903094884,0.8702928870292886: 0.004103651664874053,0.8744769874476986: 0.00405500931549898,0.8786610878661087: 0.00404009152774367,0.8828451882845187: 0.0040284345896393825,0.8870292887029287: 0.004002117941651392,0.8912133891213389: 0.003998528797899053,0.8953974895397488: 0.003994857427249794,0.899581589958159: 0.003990108081722969,0.903765690376569: 0.003971101442863881,0.907949790794979: 0.00395866568386823,0.9121338912133891: 0.003951203770431331,0.9163179916317991: 0.003939577813334258,0.9205020920502092: 0.003932404801693227,0.9246861924686192: 0.003927200798503225,0.9288702928870292: 0.0038992386991205073,0.9330543933054393: 0.0038972893493800834,0.9372384937238493: 0.0038936905671402796,0.9414225941422594: 0.0038847709817518374,0.9456066945606694: 0.003881249225429545,0.9497907949790794: 0.0038697328019833897,0.9539748953974895: 0.0038606570992151027,0.9581589958158995: 0.0038558659892030827,0.9623430962343096: 0.0038426052071063067,0.9665271966527196: 0.003829384812537015,0.9707112970711296: 0.0038083104150539605,0.9748953974895397: 0.003781030302166009,0.9790794979079497: 0.0037369702476914555,0.9832635983263598: 0.0037201759305723722,0.9874476987447698: 0.0036871177527256637,0.9916317991631798: 0.0036257442541351424,0.9958158995815899: 0.0035866395461282012,1.0: 0.0035637164646374914})
interval_points = np.linspace(0,1,11)
interval_aggregate = OrderedDict()
for i in range(len(interval_points) - 1):
lower = interval_points[i]
upper = interval_points[i 1]
if i == 0:
interval_aggregate[(lower, upper)] = data[data.index.map(lambda x: lower <= x < upper)].sum()
else:
interval_aggregate[(lower, upper)] = data[data.index.map(lambda x: lower < x <= upper)].sum()
aggregate_data = pd.Series(interval_aggregate)
aggregate_data.sum()
# 1.0
CodePudding user response：
You are looking for groupby on pd.cut:
data.groupby(pd.cut(data.index, interval_points, include_lowest=True)).agg(['mean','count','sum'])
Output:
mean count sum
(-0.001, 0.1] 0.003351 24 0.080414
(0.1, 0.2] 0.004136 24 0.099263
(0.2, 0.3] 0.004452 24 0.106853
(0.3, 0.4] 0.004528 24 0.108668
(0.4, 0.5] 0.004299 24 0.103173
(0.5, 0.6] 0.004595 24 0.110278
(0.6, 0.7] 0.004078 24 0.097881
(0.7, 0.8] 0.004233 24 0.101600
(0.8, 0.9] 0.004165 24 0.099967
(0.9, 1.0] 0.003829 24 0.091904