
    jѭ                        d Z ddlmZ ddlZddlZddlmZm	Z	m
Z
mZmZmZmZmZmZmZmZ ddZddZddd	d
ZddddZd	ZddZddZd ZdeedfdZdddZy)u  Viewership report — the tournament book.

Modelled on the workbook GSIQ analysts build by hand, with the same sheets in
the same order so it drops straight into an existing process:

    Cover                what this run covers, and what is in the book
    Audience_by_Program  every telecast, with the analog it was projected from
    AMA_By_Day           country x date, cumulative
    AMA_By_Day_LIVE      live only
    AMA_By_Day_NON_LIVE  everything else
    OTT_AMA_By_Day       linear TV scaled to on-demand viewing
    OOH_AMA_By_Day       out-of-home
    PAN_AMA_By_Day       pan-regional feeds, which are not one market
    Method               the parameters behind the numbers, and their limits

Four things the hand-built version does not carry, and should:

  * an 80% interval on every estimate. The manual book reports a point and a
    confidence *label*; 82% of its rows are labelled Low or Very Low without
    saying what that means in audience terms.
  * totals on every pivot.
  * OTT and OOH multipliers named per market rather than applied silently.
  * a Reach column only where reach was actually computed. The manual book
    carries an empty one on all 2,359 rows, which reads as a missing number
    rather than an absent method.
    )annotationsN   )AMBERBRANDCRIMSONINDIGOINKMUTEDTEALbuildheadernumkeyprint_setupc                ,   d| j                   v r| d   nd}|t        j                  |d      nt        j                  t              }t        |j                         j                               }|r#t	        |j                               dz  d|dd	fS d
| j                   v rd| j                   v rt        j                  | d
   d      }t        j                  | d   d      }||z
  j                  j                         dz  }|j                  |dk\  |dz         }|j                  |dkD  |dk  z        }t        |j                         j                               }|r t	        |j                               d|ddfS t	        t        |             dfS )a  Total hours of coverage in the schedule.

    Sums the real durations where the feed supplied them and says so; where it
    did not, falls back to an hour per telecast and says THAT, so nobody reads
    an assumption as a measurement. Lives here rather than being written out
    twice because the dashboard and the workbook must not be able to disagree
    about it.

    Returned UNROUNDED. The dashboard tiles want a whole number and round it
    themselves; the Summary sheet renders the exact figure as hh:mm:ss, and
    rounding here would throw away the minutes before it ever got there.

    Returns (hours, provenance note).
    durationNcoerceerrorsdtype   zfrom ,z telecasts carrying a durationstart_dtend_dt      @r         8@zmeasured across 
 telecastsu=   estimated at 1h per telecast — the feed carried no duration)columnspd
to_numericSeriesfloatintnotnasumto_datetimedttotal_secondswherelen)dcoldurhavesespanns           2/var/www/html/crystal/crystal2/engine/vr_report.pybroadcast_hoursr4   $   s^   $ &2!J-C14"--H
-biiV[F\Csyy{ !DSWWY"$d1X5S&TTT
 QYY8qyy#8NN1Z=:NN1X;x8Azz'')F2 zz$!)TD[1zz4!8
34

  "#$(81Z&HHHQ=YYY    c                   dddddd}|d| j                   vr|S i i }}| d   j                         j                         D ]1  }	 |j                  t	        |            }|s!|d   |d   c||<   ||<   3 |s|S | d   j                         }d| j                   v r| d   j                  t              }nrd	| j                   v r| d	   j                  t              }nKt        |      \  sr| j                  fd
d      }n!t        j                  d| j                        }| || z     }	t        j                  |	d   d      }
|
|	d   j                  |      j                  t               j#                  d      z  }|
|	d   j                  |      j                  t               j#                  d      z  }t!        t%        j&                  |j(                              |d<   t!        t%        j&                  |j(                              |d<   t+        |	j,                  |dkD  df   j/                               |d<   t+        |	j,                  |dkD  df   j/                               |d<   t1        |      |d<   |S # t
        $ r d}Y +w xY w)a  OTT and out-of-home totals in thousands, plus the market counts.

    Must agree with the OTT_AMA_By_Day and OOH_AMA_By_Day sheets to the cent,
    so it applies exactly the filters those pivots do: rows carrying an
    estimate, **excluding pan feeds**, times each market's own multiplier. A
    market with no multiplier contributes zero rather than a guess.

    Lives beside broadcast_hours() and for the same reason -- the dashboard
    tiles and the workbook must not be able to show two different OTT figures
    for one run.

    Returns zeros when `ref` is absent or carries no ott_multipliers, which is
    honest: the platform sheets read zero in that case too.
            r   )ott_000ooh_000ott_marketsooh_marketsmarkets_with_multiplierNcountry_namer   estimated_ama_000is_panpan_feedc           	         t        j                  t        | d         t        | d         f      xs j                  t        | d                     S Nr=   channel_name)boolgetstr)rpan_chanpan_pairs    r3   <lambda>z!platform_totals.<locals>.<lambda>|   sM    $x||S>1B-CS>IZE[,\]  G"*,,s1^3D/E"FH r5   axisFindexr   r   r8   r9   r:   r;   r<   )r   dropnauniqueottrF   	Exceptionr$   astyperD   _pan_lookupapplyr   r!   rN   r    mapr"   fillnanpnansumvaluesr#   locnuniquer*   )r+   refoutott_mooh_mcgotokr?   singleamaohrH   rI   s                @@r3   platform_totalsrh   P   st    c!A&')C
{nAII5
r5E~%%'..0 0	''#a&/C !$QQE!HeAh0 
	
		%	%	'B1998##D)	qyy	 :%%d+ )-(xWWHNO  QF YYuAGG4FrVG|_F
--23H
ECf^$((/66u=DDSIIAf^$((/66u=DDSIIA299QXX./C	N299QXX./C	NVZZA~(=>FFHICVZZA~(=>FFHIC%(ZC!"JE  	C	s   I==JJg      ?g      ?)
HIGHLIGHTSPRE_POST_ANALYSISOTHERSgUUUUUU?g      ?g      ?)practice
qualifyingracec                    t        | xs d      j                         j                         }|t        v r	t        |   S t        j                  t        |xs d      j                         j                         t              S )N )rF   stripupperASSUMED_HOURSASSUMED_BY_ROUNDrE   lowerDEFAULT_HOURS)ttyperndts      r3   _assumed_hoursrz      sb    EKR &&(AMQCI2 4 4 6 < < >NNr5   c                   d| j                   v rt        j                  | d   d      dz  n$t        j                  | j                  t
              }d| j                   v rd| j                   v rt        j                  | d   d      }t        j                  | d   d      }||z
  j                  j                         dz  }|j                  |d	k\  |dz         }|j                  |d	kD  |d
k  z        }|j                  |      }g }t        | j                         |      D ]h  \  \  }}	}
t        j                  |
      }|rt        |
      n)t        |	j                  d      |	j                  d            }t!        |	j                  dd            }t        |j                  |d	      xs d	      }t        |j                  |d	      xs d	      }|j#                  |t!        |	j                  dd            t!        |	j                  dd      xs d      t%        |dz        dz  |rdndt        j                  |	j                  d            rt        |	d         nd|t%        ||z  dz        dz  t%        ||z  dz        dz  d	       k |S )z8One row per telecast for the Broadcast_Hours_Calc sheet.r   r   r   r   )rN   r   r   r   r   r   r   telecast_typematch_levelr=   rp   i  measuredassumed
confidenceN)	marketrw   roundhourssourcer   ott_mult	ott_hours	ooh_hours)r   r   r    r!   rN   r"   r&   r'   r(   r)   rW   zipiterrowsr$   rz   rE   rF   appendr   )r+   r_   r`   r-   r/   r0   r1   rowsidxrG   hrsr~   rg   r   omhms                   r3   _hours_rowsr      s5    AII% ==:x84?+-99177%+P QYY8qyy#8NN1Z=:NN1X;x8Azz'')F2zz$!)TD[1zz4!8
34jjDQZZ\3/ a#88C="E#JEE/"AEE-$8):QUU>2./599VQ',1-599VQ',1-34}b17R8 1t8_t+$,j) hhquu\':; !<1AEq2v}-4q2v}-4
 	, Kr5   c           	     R   | i i fS 	 | j                   j                  d      }||j                  ri i fS |j                  d      D ci c]Y  }t	        |j
                        t	        |j                        ft	        |j                        t	        |j                  xs d      f[ }}|j                  d      D ci c]D  }t	        |j                        t	        |j                        t	        |j                  xs d      fF }}||fS c c}w c c}w # t        $ r i i fcY S w xY w)aL  (country, channel) -> pan region, from tvviewers.pan_mapping.

    A pan feed covers many markets from one signal, so it must never be added
    into a single-market total. Guessing that from the channel name is how a
    legitimate national channel ends up misfiled; the mapping table already
    records it, 3,965 rows of it.
    pan_mappingFrM   rp   )
framesrE   empty
itertuplesrF   r=   rC   pan_channel_name
pan_regionrR   )r]   mrG   by_pairby_chans        r3   rT   rT      s+    {2vJJNN=)9r6M LLuL57 'Q^^)<=**+S1C-DEF 7 7 LLuL57 q~~&Q-?-?)@#allFXVXBY(ZZ 7 777  2vs6   ,D D 	AD'D =A	DD 
D D&%D&rp   c	                   t        |j                        }	|	dz   }
t        | ||
||||      }| j                  d       | j	                  ddd       | j	                  d|	d       | j	                  |	dz   |	dz   d       | j	                  |	dz   |	dz   d       | j	                  |	d	z   |	d	z   d       | j                  |dd
|d          t        |j                  d      D ]%  \  }}| j                  ||t        |      |d          ' | j                  ||	dz   d|d          | j                  ||	dz   d|d          | j                  ||	d	z   d|d          | j                  |d       | j                  |dz   d       t        |j                         |dz         D ]  \  }\  }}||z
  dz  dk(  rdnd}| j                  |dt        |      |d|z             t        |j                  d      D ]v  \  }}||   }t        j                  |      s|dk(  r| j                  ||d|d|z             @t        t        |      d      }| j!                  ||||t#        |d|                x t        t        t%        j&                  |j(                              d      }| j!                  ||	dz   ||t#        |d|                |dn|j+                  |      }|t        j                  |      r| j                  ||	dz   d|d|z             n/| j!                  ||	dz   t        t        |            |d|z             |	dk\  sddlm} | j1                  ||	d	z   d| j3                         d ||d||	      d|dt4        dd         |t        |      z   }| j                  |dz   dd!t        |      d"d#|d$          t        |j                  d      D ]^  \  }}t        t        t%        j&                  ||   j(                              d      }| j!                  |dz   |||t#        |d%                ` | j!                  |dz   |	dz   t        t        t%        j&                  |j(                              d      |d%          |t        |      rt        t%        j6                  |j8                  D cg c]"  }|j+                  |t$        j:                        $ c}            }| j!                  |dz   |	dz   t        j                  |      rdn
t        |      |d&          n| j                  |dz   |	dz   d|d%          | j                  |dz   |	d	z   d|d%          t        |      r:| j=                  |dz   |	dz   ||	dz   d'|d|d(       | j?                  |d||	dz          tA        | |
|       dd)lm!}  ||dz   |	dz         t        |      r(t        t%        j&                  |j(                              nd*|j                  D cg c]-  }t        t%        j&                  ||   j(                              / c}t        |      | j3                         ||dz   |	d+S c c}w c c}w ),a2  country x date grid: cumulative, a trend sparkline, and a totals row.

    `conf` is a market -> mean confidence Series. Every sheet carries it: a
    grid of audience figures with no idea which rows are well evidenced invites
    the reader to treat all of them alike, and on this model they are not.
          r      r               Markethdrstarthdrn
Cumulative
ConfidenceTrendhdrc   _brp   tbNr2   nbnw)xl_range'z'!columnTzero)rangetypeseries_color
high_point
high_colorempty_cellszAll markets (r   )totltottotwdata_bar)r   	bar_color	bar_solidbar_border_colorxl_rowcol_to_cellr7   )
total_celltotal
day_totalsmarketssheethdr_row	total_rowncols)"r*   r   r   hide_gridlines
set_columnwrite	enumeraterF   set_rowfreeze_panesr   r   isnawrite_blankr   r"   write_numberr   rX   rY   rZ   rE   xlsxwriter.utilityr   add_sparklineget_namer   nanmeanrN   nanconditional_format
autofilterr   r   )wsfpiveventsheet_titlenoter   tabconfr   widthr0jra   inamerowbvvalcumcvr   lasttvr2   mcr   s                               r3   _pivotr      s    EAIE	Aue[$	<BaMM!QMM!UBMM%!)UQY+MM%!)UQY+MM%!)UQY+HHRHah'#++Q/ +1
QA&	*+HHRL!F)4HHRL!F)4HHRGQvY/JJr2OOBFA#CLLN"q&A S;D#VqLA%D2
As4y!D1H+.ckk3 	CDAqAAwwqzQ!Vq!T1S1W:6E!Ha(1c1VCa-@+AB	C E"))CJJ/0!4
519c1VCq-A+BC\Ttxx~:NN1eaiqqz:OOAuqy%b	*:AdQhKH A:3Q	&(kkmXaAu5MN )"'&,R S+S4 C=DHHTAXqM#c(1Q76C#++Q/ ?153q6==12A6
q!R6"e+<)=>? OOD1HeaiuRYYszz5J/KQ)OQRSXQYZCH 2::CIIFqtxx2662FGH
q%!)WWR[eBi6	D 	taxD!E(;NN4!8UQYah7
3x
b1feaiuqy'1	,0iQ	R 	b!T519-E2 5+D1Heai@58XU299SZZ013 EHKKPq53q6==!9:P3x"++-q5B B G( Qs   +'Y=2Yc                ;  |}~ dd l }| j                         }dD ].  }||j                  v st        j                  ||   d      ||<   0 |j                  dt        j                  d|j                              j                  d      |d<   dD ],  }||j                  vrd||<   ||   j                  d      ||<   . t        j                  |j                  d	|j                  d
            d      j                  j                  |d<   t        |      \  ~}}~fd}t        |      r|j                  |d      nt        j                  t              }	|	j!                  d       |d<   |	j!                  d       |d<   |d   j#                  t$              |d<   |d   j#                  t&              j&                  j)                         j+                  d      |d<   |d   j-                         }
i i }}|W|d   j/                         j1                         D ]3  }d }	 |j3                  t'        |            }|s#|d   |d   c||<   ||<   5 |d   j!                  |      |d<   |d   j!                  |      |d<   |j7                  t'        |      ddi      }|j9                  d       t;        |      ||d   j-                         j=                         r.|d   j/                         j?                         j@                  d   nd}|xs |xs d jC                         }|jE                  d!      |jF                  |
df   }tI        |d   j/                         j1                         D cg c]  }| c}      }g d"}d# }|d$   j!                  |      |d%<   d&|j                  vrd|d&<   |d&   j                  d      |d&<   d'D ].  }||j                  v st        j                  ||   d      ||<   0 d(|j                  vrYd)|j                  v r8d*|j                  v r*|d*   |d)   z
  j                  jK                         d+z  |d(<   ntL        jN                  |d(<   |D cg c]  }|d   |j                  v s| }}d,d-d.d/d0d1d2d3d4d5	}|jE                  d6      }tQ        ||t        |      |d7d8tR              }|jU                  d9       tW        |      D ]M  \  }\  }}}}|jY                  |||       |j[                  |||||   j]                  d0      r|d:   n|d;          O |j_                  |d<       |ja                  |dz   d=       |jc                  dd>d?@      }tW        |je                         |dz   A      D ]N  \  }\  } }!||z
  d9z  dk(  rdBnd}"tW        |      D ]'  \  }\  }#}$}%}||   |"z   }&|!j                  |#      }'|'?tg        |'th              rtM        jj                  |'      rt        jl                  |'      s|'dk(  r|jo                  ||d ||&          ~|dCv rGtq        ti        |'      d9      }(|js                  |||(||   dDv r|tu        |(||   |"         n||&          |dEk(  r:|jw                  ||t        jx                  |'      j{                         ||&          |j[                  ||t'        |'      ||&          * Q |t        |      z   })|j}                  |dt        |)|dz         t        |      dz
         t        dF tW        |      D        d       }*|*+t        |      r |j                  |dz   |*|)|*dGtR        ddH       t        dI tW        |      D        d       }+|+(t        |      r|j                  |dz   |+|)|+dJdKdLdMdN       t        dO tW        |      D        d       },|,Zt        |      rO|j                  |dz   d|)t        |      dz
  dPdQ|d9z    dRt        dS|,z         z  |j                  dTdUi      dV       t        |t        |      |       dW }-dX }.||
|d    z     }/t        |jE                  dY      | |-|/      |dZd[tR        tR         |.|/      	      }0t        |jE                  d\      | |-|/|/d            |d]d^t        t         |.|/|/d            	       t        |jE                  d_      | |-|/|/d             |d`dat        t         |.|/|/d             	       |/j                         }1|1d   |1d   j                  d      z  |1d<   t        |jE                  db      | |-|1      |dcddt        t         |.|1      	      }2|/j                         }3|3d   |3d   j                  d      z  |3d<   t        |jE                  de      | |-|3      |dfdgt        t         |.|3      	      }4|jE                  dh      }5tQ        |5|di|djdkt              }6|5jU                  d9       |5jY                  ddd<       |5jY                  dddl       |5jY                  d9dmdn       |5jY                  dododp       g dq}7tW        |7      D ]&  \  }}8|5j[                  |6||8|d9k\  r|d:   n|d;          ( |5j_                  |6dr       |5ja                  |6dz   d9       |6dz   }d}9|/j                  dds      D ]  \  }:};ti        |j                  |:d      xs d      }<ti        |j                  |:d      xs d      }=g dt}>|;j                  dds      D ]  \  }?}@ti        |@jF                  |@d   df   j                               }Ati        |@jF                  |@d    df   j                               }B|A|Bz   }C|A|B|C|<z  |C|=z  g}D|9d9z  dk(  rdBnd}"|5j[                  |dt'        |:      |d,|"z             |5j[                  |dt'        |?      |d.|"z             tW        Dd9A      D ]3  \  }}'tq        |'d9      }E|5js                  |||E|tu        |Ed0|"                5 tq        t        D      d9      }F|5js                  |dm|F|tu        |Fd1|"                d$@j                  v r=@d$   j-                         j=                         rti        @d$   j                               nd }G|G|5jo                  |dod |d0|"z             n#|5js                  |dotq        G      |du|"z             t        |>D      D H"cg c]
  \  }H}"|H|"z    }>}H}"|dz  } |5j[                  |dt'        |:      |dv          |5j[                  |ddw|dv          tW        |>d9A      D ]2  \  }}'tq        |'d9      }I|5js                  |||I|tu        |Idx                4 tq        t        |>      d9      }J|5js                  |dm|J|tu        |Jdx                d$|;j                  v r=|;d$   j-                         j=                         rti        |;d$   j                               nd }K|K|5jo                  |dod |dx          n |5js                  |dotq        K      |dy          |dz  }|9dz  }9 |5j}                  |6dt        |dz
  |6dz         dm       t        |5do|6       ||
|d   z     }L|jE                  dz      }Mt        |L      rLj                  ddgddd{|      }N|Nj                  D H"cg c]  \  }H}"|H d}|"  c}"}HN_        t        M||N|d~dt        t        d$Lj                  v rZLj                  t        |Ld   |Ld         D H"cg c]  \  }H}"|H d}|"  c}"}H      j                  d      d$   j                         nd 	       njtQ        M|dm|d~dt              }O|MjU                  d9       |MjY                  ddd       |Mj[                  |Odd|d          |Mj[                  |Od9z   dd|d          |jE                  d      }PtQ        |P|d|ddt              }8|PjU                  d9       dD ]  \  }}QPjY                  |||Q        g d}RtW        |R      D ]#  \  }}Pj[                  |8||||d=k\  rd:nd;          % Pj_                  |8dr       |Pja                  |8dz   d       t        |||      }StW        |S|8dz   A      D ]X  \  }}T||8z
  d9z  dk(  rdBnd}"Pj[                  |dTd   |d,|"z             |Pj[                  |d|Td   |d.|"z             |Pj[                  |d9|Td   |d.|"z             |Pjs                  |d=|Td   dz  |d|"z             |Pj[                  |d|Td   |d.|"z             |Pjs                  |d|Td   |tu        |Td   d0|"                |Pjs                  |dm|Td   dz  |d|"z             |Pjs                  |do|Td   dz  |d|"z             |Td$   Pjo                  |did |d0|"z             3Pjs                  |ditq        Td$         |du|"z             [ |8t        S      z   }Ut        d |SD              }Vt        d |SD              }Wt        d |SD              }XPj[                  |Udz   ddt        |S      dd|dv          dD ]  }Pjo                  Udz   |d |dx           d=VfdmWfdoXffD ]"  \  }}'Pjs                  Udz   ||'dz  |d          $ Sr Pj}                  |8dUt        R      dz
         t        Pt        R      |8       |jE                  d      }YtQ        |Y|d|ddt              }Z|YjU                  d9       |YjY                  dd       |YjY                  dd       |YjY                  dd       t        |di       xs i }[|[j                  d      xs i }\[j                  d      xs i }][j                  d      xs i }^Z}TYj[                  |Tdd|d;          |Yj[                  |Tdd|d;          |Yj                  |Td9|Td=d|d;          |Yj_                  |Tdr       tW        dt        |dd      dfd\j                  dd      ddfd|\j                  dd      ddfd|\j                  dd      ddfdddd|^j                  ddī      dz  dƛdǝdfd|^j                  dd˫      dz  dƛdǝdfd]j                  ddϫ      dЛdfg
|Tdz   A      D ]  \  }\  }H}"}|Tz
  d9z  dk(  rdBnd}_Yj[                  |dH|d-|_z             |Yj[                  |dt'        |"      |d.|_z             |Yj                  |d9|d=||d          |Yj_                  |dr        Tdz   }`t        d      D ]   }Yj[                  `||dk(  rdnd|d          " tW        g dբ`dz   A      D ]a  \  }\  }H}Yj[                  |d|H|d-          |Yj[                  |dd։|d          |Yj                  |d9|d=||d          |Yj_                  |d٫       c t        YdZd>ګ       tQ        |d|d!|r||k7  r|ndt        dܫ      }ajU                  d9       jY                  ddd       jY                  dddݫ       jY                  d9d9dޫ       jY                  d=d=d       jY                  dddݫ       j[                  |add߉|d          j[                  |add|d          j[                  |ad9d|d          j[                  |ad=d|d          j[                  |add|d          j_                  |ad       tq        ti        |j                               dz  d9      }b|r5t        jx                  |d         ddt        jx                  |d         dnd}ct        |      }dd$|j                  v r8|
j=                         r(ti        |jF                  |
d$f   j                               nd}et        d SD              }f|frdfddt        S      ddnd}gd|d   j                         ddfd|d   j                         ddfdbddfdd|2d    dd|2d   dddtq        |2d    dz  d9      fdd|4d    dd|4d   dddtq        |4d    dz  d9      fddad=z    d|adz    dddtq        |b|2d    dz  z   |4d    dz  z   d9      fdcd d	d
fg}hdedz  dfdt        t        |            dfdVdfdWdfdXdfd|V|Wz   |Xz   dfd|gd
fg}idF|fd	}j|hD Tcg c]  }Tt        |T      dk(  rTng Td  }h}TtW        |hadz   A      D ]j  \  }\  }k}l}m}n}o|az
  d9z  dk(  rdBnd}"j[                  |dk|d-|"z              j|dln|"o       j[                  |d9m|d          j_                  |dݫ       l tW        iadz   A      D ]>  \  }\  }k}l}n|az
  d9z  dk(  rdBnd}"j[                  |d=k|d-|"z              j|dln|"       @ t        h      t        i      k\  rhni}pat        |p      z   d9z   }q|0d   d9k\  rVddlWmX}r qdz   }sdm}tt        d|0d   dz         D ]f  } r|2d   |      }u |r|4d   |      }6d}o	 ti        |2d   |dz
     |4d   |dz
     z         }oj                  st|z   dz
  du d|6 |d0   o       h jY                  t|t|0d   z   dz
  d d ddi       |j                  ddi      }v|vj                  d|0d   |0d    d|0d    |0d   g|0d   |0d   d|0d   |0d   gt        d!d"dd#idd$       |vj                  d%|0d   |0d    d|0d    |0d   gd!s|t|s|t|0d   z   dz
  gt        d!d"dd#idd$       |vj                  d&d'ddt        d(d)       |vj                  d*d'dt        d+d,       dd-d.d/d0d1}w|vj                  d2dd3d4|wd5d6id7       |vj                  d8d'dd>d9d2dd3d4d|wd#did:       |vj                  d5d6id5d;id<       |vj                  d=d5d;ii       |vj                  d>d?d@       j                  qd|v       qdAz   }Tt        d=      D ]  }j[                  T|g dB|   |d;          ! j_                  TdC       g dD}xtW        |x|Tdz   A      D ]f  \  }\  }y}z}{|Tz
  d9z  dk(  rdBnd}_j[                  |dy|d-|_z             j[                  |dz|d,|_z             j[                  |d9{|d.|_z             h Tt        x      z   d9z   }`j                  |`d|`d9dE|d          j_                  |`dޫ       j                          j                  d       j                  dd       |j                          y # t4        $ r d }Y w xY wc c}w c c}w c c}"}Hw c c}"}Hw c c}"}Hw c c}Tw # t4        $ r Y ww xY w(G  Nr   )r>   r   interval80_lowinterval80_highevidence_rowsr   r   flagrp   rM   )sports_teamsr}   sports_categorysource_channelr|   
prog_titlebroadcast_date	prog_datedayc                    j                  t        | d         t        | d         f      xs j                  t        | d               S rB   )rE   rF   )r   rH   rI   s    r3   _pan_ofzwrite_vr.<locals>._pan_ofF  sH    ||S^!45s3~;N7OPQ 6||CN 345	6r5   r   rK   r   c                    | r| d   S dS )Nr   rp    r   s    r3   rJ   zwrite_vr.<locals>.<lambda>J  s    !!A$  r5   r@   c                    | r| d   S dS )Nr   rp   r  r  s    r3   rJ   zwrite_vr.<locals>.<lambda>K  s    AAaD 2 r5   r   r?   r|   LIVEis_liver>   r=   rQ   oohnan_inf_to_errorsTauto
event_namezViewership reportSummary))r=   r      cell)rC   Channelr   r  )r   	Programme   r  )r   Start   r'   )r   Endr  r'   )r   Duration
   r-   )r>   
AMA ('000)r   numb)r   z80% lowr   num)r   z80% highr   r  )r   r   r   r  )
conf_levelLevelr   mono)strategyzStrategy usedr   r  )r   zEvidence rowsr   r#   )source_countryzAMA source market   r  )r   zAMA source channelr  r  )r|   Telecast typer   r  )r   Sport   r  )r}   Roundr   r  )r   Teams   r  )r  Eventr   r  )r   zReview flagr  warnc                j    t        j                  |       ry| dk\  rdS | dk\  rdS | dk\  rdS | dk\  rd	S d
S )Nrp   Z   z	Very HighK   High<   Medium-   LowzVery Low)r   r   )ra   s    r3   levelzwrite_vr.<locals>.level  sT    771: Bw 	Pa2gF 	P!"bX	P78Bwe	PDN	Pr5   r   r  r  )r   r   r   r   r   g     @ry   r   tmtwr2   r   nindndur)	r  cellbr  r'  r  r  r#   r'   r-   Audience_by_ProgramzAudience by programmez"every telecast, sorted by audiencer   r   r   r   r   Fr   )	ascendingna_positionr   r   )r  r  r#   r-   )r2   r   r'   c              3  8   K   | ]  \  }}|d    dk(  s|  yw)r   r>   Nr  .0r   ra   s      r3   	<genexpr>zwrite_vr.<locals>.<genexpr>  s      LTQ!8K0KqL   r   )r   r   r   c              3  8   K   | ]  \  }}|d    dk(  s|  yw)r   r   Nr  r;  s      r3   r=  zwrite_vr.<locals>.<genexpr>  s     ETQ!0DqEr>  3_color_scalez#F7C9CFz#FDF0D3z#CDE9E2)r   	min_color	mid_color	max_colorc              3  8   K   | ]  \  }}|d    dk(  s|  yw)r   r   Nr  r;  s      r3   r=  zwrite_vr.<locals>.<genexpr>  s     ?TQ!q?r>  formulaz=LEN($%sz)>0A   bg_colorz#FFF6F7)r   criteriaformatc                    | j                   rt        j                         S | j                  dddd      j	                         S )Nr=   r   r>   r%   rN   r   rZ   aggfunc)r   r   	DataFramepivot_table
sort_indexframes    r3   r   zwrite_vr.<locals>.piv  s>    ;;<<>!!!)<e " M	r5   c                |    | j                   sd| j                  vry| j                  d      d   j                         S )zBMarket -> mean confidence, for the rows this sheet actually shows.r   Nr=   )r   r   groupbymeanrP  s    r3   cmeanzwrite_vr.<locals>.cmean  s4    ;;,emm;}}^,\:??AAr5   
AMA_By_DayzAMA by market and dayuB   thousands · single markets only, pan feeds are on their own sheetAMA_By_Day_LIVEzLIVE telecasts only	thousandsAMA_By_Day_NON_LIVEzNon-live telecastsu4   highlights, delayed, archive and studio · thousandsOTT_AMA_By_DayzOn-demand (OTT) audienceum   linear AMA x each market's tv_to_ott multiplier · a market with no multiplier shows zero rather than a guessOOH_AMA_By_DayzOut-of-home audiencez1linear AMA x each market's out-of-home multiplierAMA_BY_COUNTRY_BY_DAY   zAll platforms by market and dayu=   thousands · total is the sum of the four columns to its leftr"           r   )r   DatezLinear LIVEzLinear non-liveOTTOOHTotalr   r   )sort)r7   r7   r7   r7   r   r   z	All datesr   r   PAN_AMA_By_Dayr%   rK  u    · zPan-regional feedsul   a pan feed covers many markets from one signal, so it is never added into a single-market total · thousands)_krg  znone found in this file`   z"No pan-regional feeds in this fileh2u   Matched against tvviewers.pan_mapping. None of this file's channels are registered as a pan feed — if you expected some, the EPG export may not include the pan channels.r   Broadcast_Hours_Calc	   zBroadcast hours, per telecastz.the Summary hours total is a SUM over column D)	)r   r   )r   r  )r      )r   r_  )r   r   )   r   )r^  r"  )r`  r"  )r]  r   )	r   r   r#  HoursSourcezOTT eligiblez	OTT hoursz	OOH hoursr   r   rw   r   r   r   nhmsr   r   rm  r   r   r   c              3  &   K   | ]	  }|d      yw)r   Nr  r<  rG   s     r3   r=  zwrite_vr.<locals>.<genexpr>q  s     )aqz)   c              3  &   K   | ]	  }|d      yw)r   Nr  rr  s     r3   r=  zwrite_vr.<locals>.<genexpr>r       .1+.rs  c              3  &   K   | ]	  }|d      yw)r   Nr  rr  s     r3   r=  zwrite_vr.<locals>.<genexpr>s  ru  rs  zAll telecasts (r   r   )r   r   r   rm  r]  tothmsMethodzMethod and provenanceu@   case-based structured-analogy forecasting · measured parameterszA:A"   zB:Br  zC:D(   calibration	exponentssigma_by_tiercross_market_backtest	ParameterValue
ProvenancezCalibration sourcecalibration_sourcezassumed constantsz6Estimated from the evidence base itself where present.u   Exponent · TV universetvuz.3fz\Weighted least squares on log audience, event fixed effects absorbed. Was assumed to be 1.0.u   Exponent · channel sharesharez	As above.u   Exponent · sport affinityaffinity)zHour & weekday curvesr~   z Within-stratum ratio estimation.)z80% intervalu   exp(±1.2816·σ)u-   σ from hold-out backtest, per evidence tier.)zMedian error, same marketz67%z'Hold-one-row-out over 1,200 broadcasts.zMedian error, cross-market
median_APEg{Gz?d   z.0f%zLeave-one-market-out: every row for a market removed, then estimated from the rest of the world. The harder and more honest test.zWithin a factor of 2hit_within_2xgHzG?zCross-market.u   σ, cross-market4g@z.2fu$   An 80% interval of roughly ×/÷ 23.bodymr   zWhat is NOT applied))zRound / match stagezjA final is weighted the same as a group match. The round is captured but the weight table is not yet read.)u   Event interest · intensityz/Both reference tables are available and unused.)Reachu   No reach figure is produced. A reach column is omitted rather than shipped empty — an absent method should not look like a missing number.)DuplicationzsAudiences are not de-duplicated across channels or days, so totals are viewer-broadcasts rather than unique people.znot appliedpill_nobodywr  )	landscapezviewership report)bigr  .   zAudience & Coveragehdr_bighdrn_bigDetailzBroadcast Coverage   i  z%-du   –z	%-d %b %Yr7   c              3  2   K   | ]  }|d    dk(  sd  yw)r   r~   r   Nr  rr  s     r3   r=  zwrite_vr.<locals>.<genexpr>  s     ?qQx[J%>?s   zmeasured on z of r   u7   assumed per telecast type — the feed carried no timesMarketszcountries and feedsr#   ChannelsrC   zbroadcast channels / serviceszTotal TV Audience (AMA mil.)z*summed across telecasts; not unique peopler  zTotal OTT Audience (mil.)z=OTT_AMA_By_Day!r   z/1000zon-demand audience across r   z marketsr   zTotal OOH Audience (mil.)z=OOH_AMA_By_Day!zout-of-home audience across zGlobal Total Audience (mil.)z=SUM(Bz:BzTV + OTT + OOH audiencezBroadcast periodz-day windowtextzMean Confidence Scoreg      Y@pctz#Total number of Programme Telecastsz#Total TV Broadcast Hours (hh:mm:ss)hmsz$Total OTT Broadcast Hours (hh:mm:ss)z$Total OOH Broadcast Hours (hh:mm:ss)z'Global Total Broadcast Hours (hh:mm:ss)zBroadcast hours sourcec                D   |dk(  r$j                  | |t        |      d|z             y t        |t              r=|j                  d      r,j	                  | ||t        |xs dd|         |xs d       y |dk(  r'j                  | |t        |      dz  d|z             y |d	k(  r$j                  | |t        |      d
|z             y |dk(  r$j                  | |t        |      d|z             y j                  | |t        |      t        |d|                y )Nr  r1  =r   r2   r  r   rp  r  npctr#   r   )r   rF   
isinstance
startswithwrite_formular   r   r"   )r   r,   valuekindr   cachedr   r   s         r3   _putzwrite_vr.<locals>._put  s   6>HHS#s5z1TAX;7s#(8(8(= S#uav{C0K.LfkXYZU] OOCeElT&91VaZ=IU]OOCeElAfqjMBU]OOCeElAdQhK@OOCeElAfUC6K4LMr5   r   r   r   r   z+OOH_AMA_By_Day!hiddenr   linezTV Audience (AMA '000)r   r   g      @)colorr   none)r   
categoriesrZ   r  markersmoothzOTT + OOH Audience (AMA '000)zAudience by Broadcast DayCalibri)r   sizeboldr  )r   	name_fontbottom)r   r  r  )positionfontz#CCCCCCg      ?dash)r  r   	dash_type)visibler  Consolas)r   r  z#,##0r  z#D9D9D9)num_font
num_formatmajor_gridlinesr  r  )r   r  r  )r   r  r  r  minr  r  z#FFFFFF)borderfillr  i  i,  )r   heightrl  )zWorkbook sectionPurposeOutputr   )	)r7  z;Every telecast with market, channel, audience and interval.zTelecast-level audience)r\  z)All platforms combined by market and day.zTotal audience by country/day)rV  z*Average-minute audience by market and day.zCumulative AMA)rW  zLive telecasts only.zLive AMA)rY  z(Highlights, delayed, archive and studio.zNon-live AMA)rZ  zOn-demand viewing by market.zOTT AMA)r[  zOut-of-home viewing by market.zOOH AMA)rf  z5Pan-regional feeds, kept out of single-market totals.zPan-regional AMA)rx  z3Parameters, assumptions and everything not applied.Methodologyu4  Audience figures are average-minute audiences in thousands unless a row says millions. Summing them across telecasts counts a viewer once per broadcast, so any total is viewer-broadcasts rather than unique people. Every estimate carries the evidence tier and the 80% interval that produced it — see Method.N)i
xlsxwritercopyr   r   r    rE   r!   rN   rW   r&   r'   daterT   r*   rU   objectrV   rS   rD   rF   rr   eqr$   rO   rP   rQ   rR   Workbookset_calc_modebuild_formatsanymodeilocrq   add_worksheetr[   sortedr(   rX   r   r   r   r   r   r   r   r  r   r   sort_valuesr   r  r"   isfiniter   r   r   r   r   write_datetime	Timestampto_pydatetimer   maxnextr   chr
add_formatr   r   r   r
   r   r   rS  r%   rT  r   rN  assignr   getattrmerge_ranger   r   r\   r#   r   r   r  	add_chart
add_series	set_titler	   
set_legend
set_x_axis
set_y_axisset_chartareaset_plotareaset_sizeinsert_chartset_landscape	set_paperfit_to_pagesclose)r^   pathrun_idtitler]   r  r+   ra   r  pan_hitrc   r_   r`   rb   wb	canonicalev_nameestxdayscolsr0  FMAPwphrr   rg  lblwfmviewr   _r   r   k_l_wkeyr   r   r   aicifir   rU  rd   r   rf   rQ   rg   r  wchchdrshhshadecountrygmult_omult_hrun_totr   gdlivenontot_linvalsvvrtcdatv2stcgpanwpanphpwhwdthcolshrowsrG   hlasttv_hott_hooh_hwmr   calexpsigcmbbr2hstv_mperiodn_days	mean_confn_meashsrcleftrightr  metricr  detailr  r  r   	chart_row_cellhelperhcol0occhGRIDcontentsa_b_c_r   rH   rI   r   s                                                                                                                               @@@@r3   write_vrrE  5  s   
A 8		>==1h7AaD8 fbii!'':;BB2FAfI- AIIAaDt{{2!	
 ~~aee$4aeeK6HI%-//1r$$ eH$S)Hh6 +.a&agggAg&biif6MGKK ;<AjMkk"=>AlOJ-&&t,AhK_%,,S155;;=@@HAiL	
		%	%	'B r5E
>"))+224 	4ACggc!fo %(VSV"a%(	4  $$U+AeH $$U+AeH			SY)<d(C	DBVbA
 lO))+//1 <'')..055a879 8	8%8??AG			)	$B
%%''
(Cahoo/668919:D1D,P
 o))%0AlOqyy( 
,-44R8A $ 9		>>>!A$x8AaD9 ""x199'<x[1Z=8<<JJLwVAjMFFAjM1!qtqyy0A1D1$dTfND			/	0B	As4y'+B4f
>Ba( RBQ
aA
Q$r(*=*=c*BQvY%QR JJr2OOBFA==,6=RD Q? /8AsVqLA%D2"+D/ 	/A2r2r(Q,C
AyZ51"++a.RWWUVZ[\`b[bq!T1S6244E!Ha(1c?CBx;?V&d2h": ;\]^a\bdt!!!QQ(E(E(G3PAs1vqv.	// D	>DMM"aT26*CIM:	LYt_Ld	SB	~#d)
b1fb$'1UYZ	\	EYt_Et	LB	~#d)
b1fb$'6Y,5IO	P 
?Yt_?	FB	~#d)
b1fas4y1}'0-5bdV3+?#b2g,+N)+
I7N)OQ	R CIr"B rQx[L !F	  .3v;"O65=
*B 2-.3vfY>O7P3QSZ +tTy)*+- 212As66)CTBT;U7VX_A5%	**+,.
 	A23ahooa6HHA
!!"23QA%78=ueAhPC
 	A23ahooa6HHA
!!"23QA!>8C 
		1	2B	Aq'#DOQU
WBaMM!QR]]1a4bmmAq"6MMM!Q#D4 ?2
QaAfIQuX>?JJr2OOBFA
QAEnn^$n? *
uyy!,12uyy!,12&yyTy2 	GC9/B BCGGIJD9~/BBCGGIJCSjG#w/61ABD	QBAHHQ3w<374HHQ3s8Qtax[1!$a0 A11a[1b!F2sA,>*?@A s4y!$BOOAq"ar4(;&<= "RZZ/B|4D4J4J4L4P4P4R <(--/0  zq!T1S1W:61eBi4!8=),Wd);<Aq1u<G<FA/	0 	As7|QvY/
A{AfI.gQ/ 	>DAq1+COOAq#qU);'<=	> 3w<#
1b!F2u$5"67*q/D/D/F/J/J/L AlO((*+RV 	:NN1aqx0OOAq%)QvY7	Q
U*V MM"aQUBF+Q/Ar
B8
C,-D
3x__L*#=u$7  H-.WW5TQaSQC=5tQ7$89:>  3;;. 

"3|#4c*oFHA!D} H
 IGDM,00448	; D!Q)=-t5A1b!

2q>$H

261FGHy	R 
		0	1B	Aq'#B@%
IBa. !3
aC !EE%  ;1
Q1qAvV59:;JJr2OOBFA 5%(E%rAv. G1VqLA%D2
Aq{AcAgJ/
Aqz1TAX;/
Aqz1TAX;/
1aj4/6A:?
Aq{AdQhK0
1a
mQva
mS!/L-MN
1ant3Qvz]C
1ant3Qvz]C\?"NN1aqqz2OOAq%,"8!D1H+FG UOE)5))D...E...EHHUQY_SZN!<aiH 5
uqy!T1U845TQJE
3 =1
	1a$h(<=
b!UCJN3CJ# 
		(	#B	Aq'#:RTY
[BaMM%bmmE26eR8P
#}b
)
/RC
''+

$"C
''/
"
(bC	(	)	/RB
AHHQ;%)288Aq'1U8+LNN1aA|QuX6

1b8I!	ws,@BUV	A	C	"swwua'8&=
"	# 
%!)<S(AKP	%#''*a*@)ETQ^W	%FF<&s*3/q	1
C	D 
 BFF?D$A#$Ec#J!!Lo^	T 237:`a!#" U# 9Aq!$ !eq[A%T2
Aq!D2I,'!QA$))M
q!Q1aj1


1b+. 
RB1X J
Qa-R4IJ   !V 	6Aq 	Aq!D'"BHHQ=!I,$O
q!Q1aj1


1b ArU+ 
Aq'9&9+?	EX4
!B aMM!QR]]1a4bmmAq"6MMM!QR]]1a4
 HHR)1Y<8HHRB*&HHRHa	l+HHR(!I,7HHRB*&JJr2swwy!D(!,D d1g&s+6",,tBx2H1ST YF !AII-"&&( quuR-.3356@C  ?E??F 6!*DUAjAT 	 
An%--/1FN	Q~&..02QSXY	'	5u	>	$(8\9J8K5&Q%c)nQ%7x	@%	s7|d"A	&	( 
%(8\9J8K5&Q'Iq'9	BE	s7|d"A	&	( 
(6"Q$r"Q$q)A	"E	tc'lT))CL4,??	C	E 
Vx{%;VDD" 
!)e"3U;	.CFUC	.e<	/>	/>	2D5L54H%P	!40EN N& 7;;Q1A*A*t*,;D;4=d"q&4Q 00FE64VqLA%D2
Avq{+Q5$6*
Avqz*


1b %.e26$B #  FE4VqLA%D2
Avq{+Q5$"#
 t9E
*4D
 SY"I	'{aA Rq"W+/* 	-As;'+Bs;'+BFs<0Q7!$\!21q5!9 : ; VUQY]/t3CB4HsVV-	- 	eUR[014dD8TBRS\\66*+
 	,g;9q"Y-GU'{B{OQ;GU"T2v&
 	 	3g;9q"Y-GU &%G9Lq9PQ#d3v&
 	 	9,5r4-0$23 	4 	8(11sKM 	N  9t7=*? @
J#B%,*. '35 	6 	|-65$Q,6#B%,*. &~/ 	0 	Wi$87IBVWX
'9!567
cS12
	1b)BA1X O
A@CQuXNOJJq"
H %XQU; )<BB!eq[A%T2
Ar1TBY<(
Ar1S2X;'
Ar1TBY<(	) 
S]	Q	BNN2q"a6 89yB JJr2QA)>HHJO  & :X 2D =2 6H^ <L  sa    Av4*	Aw Aw7Aw*Aw
Aw"Awc5Aw"i"Aw'v4AwwAww'	Aw4w3Aw4)r+   pd.DataFramereturnztuple[float, str])r+   rF  rG  dict)rw   rF   rx   rF   rG  r"   )r+   rF  r_   rH  r`   rH  rG  z
list[dict]r  )r^   rF  r  rF   r  rF   rG  None)__doc__
__future__r   numpyrX   pandasr   vr_styler   r   r   r   r	   r
   r   r   r  r   r   r   r4   rh   rs   rt   rv   rz   r   rT   r   rE  r  r5   r3   <module>rO     s   4 #  L L L L)ZX:B  #L 'wL O#L4 13f&UBpI	r5   