.j J`dZddlmZddlZddlZdZdZdZ dZ dZ d Z d Z d Zdd Zdd ZdddZy)abThe analyst's workbook. Four sheets, in the order someone actually reads them: Summary what this run found, at a glance Estimates every telecast, filterable, with the evidence behind each Markets totals per market Method the parameters that produced the numbers, and their provenance The Method sheet matters as much as the estimates. Every figure in this workbook comes out of a model whose exponents, curves and intervals were measured rather than assumed, and a reader who cannot see those values has to take the estimates on trust. It also states plainly what is NOT applied. ) annotationsNz#0F1120z#4B3FD6z#E0243Fz#0F9B8Ez#D7DAE6z#7C8199z#F4F5F9c &i}|jdddtdd|d<|jddtd |d <|jdd dtd td |d<|jddtddd|d<|jdddtdddd|d<|jdddtdddd|d<|jdddt dddd|d<|jddtd |d<|jddddtddddtd |d<|jdddt d |d!<|jddddt d"|d#<|jddddt d$|d%<|jdddddt d&|d'<|jddd(dt d$|d)<|jddd(ddt d&|d*<|jdddtddd(d|d+<|jdddtddd(d|d,<|jdddt ddd(d|d-<|jddd(dt d$|d.<|jddd/dt d$|d0<|jddtdt d1|d2<|jddt dt d1|d3<|jddtddd4|d5<|jddtddd4|d6<|jddt dddd7|d8<|S)9NCalibriTvcenter) font_name font_sizebold font_colorvaligntitleConsolas )rr r sub )rr r r bottom border_colorh2lefttop)rr r alignr kpi_lblz#,##0.0#)rr r r rr num_formatkpi_val kpi_val_t kpi_val_ckpi_notez#FFFFFF) rr r r bg_colorrr text_wrapborderrhdr )rr r$rcell)rr r r$rcell_brr rr$rnum)rr rr r$rnum_bz#,##0numwnumwbkpi_valw kpi_valw_t kpi_valw_cintz0.#pct)rr r r$rmonoflag)rr r r#r bodybody_m)rr r r#r r warn) add_formatINKMUTEDINDIGOTEALCRIMSONRULE)wbfs 5/var/www/html/crystal/crystal2/engine/excel_report.py_formatsrBs AYRQU.1Y HIAgJ}}:AUZ[\AeHmm)"d+.!SRSAdG==zY^+1U"DEAiL==yrSW06S\0:"<=AiL]]UY26S\2<$>?AkN]]UY29FV_2<$>?AkNMM Z_"`aAjM}}91d,53QW(1PQ.134AeH IBRS/356AfI--ibRV+,d!DEAhK}}:BV`()4ABAeHZbXb(,4 QRAgJ  JRW^)*DBCAfIZbX_(,4 QRAgJMM TX17&T]18#:;AjMmm)"VZ37&T]3:%<=AlOmm)"VZ3:VW`3:%<=AlO}}:BV]()4ABAeH}}:BV[()4ABAeH JQV[)*DBCAfI JQV])*DBCAfI IBVY,0ECDAfI--ibX].2e!EFAhK IBV],0E4QRAfI Hc` t|jS#ttf$rYywxYw)NF)float is_integer TypeError ValueError)vs rA_wholerJ`s1Qx""$$ z "s --cdddd|}t|r dddd|}||} |j||||d |j|d z||| |r|j|d z|||d yy) Nrrr)itcr.r/r0)rrrrr!rr )rJwrite) wsr@rowcollabelvaluenotetonekeyfmts rA_kpirYgs + >t DC e}$<(**-/ C&CHHS#ua l+HHS1Wc5#&  q#tQz]3 rCcVddl}|j}dD].}||jvstj||d||<0|j dtj d|jjd|d<|d j}|jt|d d d } t| } | jd } | jd| jdd| jdd| j!ddd| d| j!dd|d|dtj"j%d| d|j&|d f} |j&|df} t)|dj+tjj-dk7j/}t1| rt3| j/dz nd}t5| | dddt1|dt5| | ddd t)|j/d!|j7zd"d#d$t5| | ddd%t9|dd&t5| | dd'd(t1| r#t9t3| j;dndd)t5| | d*dd+t1| r#t9t3| j7dndd,t5| | d*dd-||rd.nd/|rd0nd$| j!d1dd2| d3d4}| j!|dd5| d6| j!|dd| d6|dz }| j=|dgd7| d8|j&|j?d9jAd:d;djCd?d @}tE|jG|dzAD]\}\}}| j!|dt|| dBt3|d?}| jI|d|| tK|rdCndD| jI|dt)|dE| dFt3|dG}| jI|dH|| tK|rdCndD| jI|dt3|dI| dJt1|r1| jM|dzd|t1|zddKtNd tNdL|t1|zdz}| j!|ddM| d6| j!|dd| d6|dz }| j=|dgdN| d8|j&|j?dOjAd;d=PjCdEd @}tE|jG|dzAD]\}\}}| j!|dt|| dQ| jI|dt)|dE| dF| jI|dt3|dEtQt)|j/dz | jSdRdSdTdtTdU| jI|dHt3|dI| dJ| j!|t1|zdzddV| dWgdX}|Dcgc]}|d|jvs|}}| jdY}|jd|jWddtE|D]4\}\}}}}|j||||j!d||| d86|jYddZ|jCd d d[\}tE|jGdAD]\}\} }tE|D]\}\}!}"}#}$|j |!}|:t[|t2rt]j^|rtj`|r|jc||d| dQp|$d]vr;|$}!|$d^vrtK|r |$dDk(rdCnd_}!|jI||t3|| |!|j!||t|| |$|jeddtQt1|dt1|dz tgd`tE|Dd}%|%.t1|r#|jMd|%t1||%dadbdcdddetgdftE|Dd}&|&1t1|r&|jMd|&t1||&dKtNd dg| jdh}'|'jd|'jddi|'jdjdk|'j=ddgdl| d8|'jYddZ|'jWdd|j?d9jAd:d;dmdzwrite_report..sETQ! 0DqE 3_color_scalez#F6C6CBz#FDF1D6z#CFE9E3)r min_color mid_color max_colorc38K|]\}}|ddk(s|yw)rr[Nrrs rArzwrite_report..s LTQ!8K0KqLr)rrrMarketsrzB:G)rorprq EstimatedrrrsFlagged)r[countct|jtjjdk7j S)Nrb)r1astypestrstripru)ss rAzwrite_report..s0c188C=+<+<+B+B+D+J*O*O*Q&RrC)ryrzestr{r|flrrMethodzB:B"zC:CrzD:D>zHow these numbers were producedu`case-based structured-analogy forecasting · multiplicative decomposition · empirical intervals calibrationcalibration_sourcezassumed constants exponents sigma_by_tiercross_market_backtest ParameterValuezHow it was obtainedrzCalibration sourcezdMeasured from the evidence base itself, or the fallback constants if no calibration file is present.uExponent · TV universetvuz.3fzkWeighted least squares on log audience with event fixed effects absorbed. Assumed 1.0 before it was fitted.uExponent · channel sharesharez As above.uExponent · sport affinityaffinityz Fitted onn_cells,z cells / n_eventsz eventsu{Identification comes from how markets differ within the same event — the same comparison a cross-market projection makes.)zHour & weekday curvesmeasuredzuWithin-stratum ratio estimation: each hour's median as a ratio to its own group's median, across thousands of groups.)z 80% intervaluexp(±1.2816 · σ)uqσ is the residual standard deviation of log error per tier, from hold-out backtests — not an assumed constant.r3zMeasured accuracy)zMedian error, same-marketz67%z'Hold-one-row-out over 1,200 broadcasts.zMedian error, cross-market median_APEz.0f%z89%zLeave-one-market-out: every row for a market removed, then estimated from the rest of the world. This is the harder and more honest test.zWithin a factor of 2 hit_within_2xz27%z+Cross-market. Roughly one estimate in four.uσ, cross-market4z.2fz2.45uEAn 80% interval of about ×/÷ 23. Wide because the evidence says so.zWhat is NOT appliedzRound / match stagez not appliedzA final is currently weighted the same as a group match. The round is captured by enrichment but the weight table is not yet read by the estimator.r7u%Event interest · broadcast intensityz5Both reference tables are available and not yet used.zQStated here because a number is only as good as the reader's ability to check it.)7 xlsxwritercopycolumnspd to_numericgetSeriesrcfillnanotnaWorkbookrrB add_worksheethide_gridlines set_columnrO Timestampnowlocr1rrrulenrErYrxroundrw write_rowgroupbyagg sort_values enumerateiterrows write_numberrJconditional_formatr;maxr8r> freeze_panesset_row isinstancenpisfiniteisna write_blank autofilternextr<getattrclose)4outpathrun_idr refrdrNokr?r@rPrr|flaggedtotal_mrmkrLnamerQrIr2tierscolswer_krSwidth_fmtview_rW_l_wrXciaiwmrwmethcalsrcexpsigcmrowsabaccr3s4 rA write_reportrssu  A8  >==1h7AaD8fbii!'':;BB2FAfI   % % 'B   SYEX\(] ^B A  ) $BaMM%MM%HHQ'74HHQeWN6(*ll&&(9;<=eHF %%'' (C 55\! "D1V9##C(,,224:??ABG+.s8eCGGI'GQ1)3q6;?Q1k3rvvx=  M# i (#/Q1,eGQ.? "$Q1+cRUhU53F-J\] Q1'TuTYY[/A1)EXY Q1*G$ *=gsSVXHHR9:;J-I AHHQ=!D'*BHHQ2qw,GFALLA_5 %%)  N + 315R29O  Q ;w%; 0$BKKMQ?<;D# As4y!H+. G rq!QF1I&SX8YZ 1c#c(mQuX6 E R__Q1a&)QV6WX 1eCK0!E(; <  2w a!eQCG Q'1,0fN O SWqBHHROQtW-rxxAr1T7/K!GBLLQMqQVxX UU2Y  z *c18NcOk#k/ $ENN$4BFC<;D# As4y!F), 1c#c(mQuX6 1eCHoCM10EE JR5;qZ^'`a b 1eCK0!E(; <HHR#e*_q !Z x[  *D  1!qtqyy0A 1D 1  + &BaOOAq'0(# #Bud aE" Auah'(JJq" ==,6= RD : / 8As%.t_ / !A!RS AyZ51"++a.RWWUVZq!T1V9566**vay$'5L&gC1eAh#7As1vqv. / /MM!QCIq)3t9q=9 EYt_Et LB ~#d) aSY'6,5I,57 8 LYt_Ld SB ~#d) aSY'1UYZ \  ) $BaMM%bmmE26LLA@AB5KJJq"rq!, 99^ $ C26S28W+RS  U K5K 1  $CLLN!<8;D# As4y!H+. G ! "BOOAq!QPQvX]=^$_ 1c#c(mQuX6 1c#e*oqx8 E a "//!Q1vayVV[;\"] 1eCK$415qx@ 1c#d)nah78 3x aCHa'1SWX Z   X &E  UA 0 0 ;U=M=MeUW=X UB KK177D KK1LMNuXW #}b ) /RC #+-@ AC ''+  $"C ''/ " (bC ( ) /RB A KK1k1U8,ekk!Q5.R KK1+QuX6 a8L s%\ ] "swwua'8&= - . %!) KKQ=!F)4 KKQ89:6D KKQBAhKP KKQ=!F)4 KKQRTUV^T_` KKQ234X;@HHJI 2s ~&$~&)returnbool)rbrL)N)rz pd.DataFramerrr rrNone)__doc__ __future__rnumpyrpandasrr9r;r=r<r>r:BANDrBrJrYrrrCrAr$sP #  ? D 4hrC