a
    (3¼j D  ã                   @   sÆ   d Z ddlZddlmZmZ ddlmZ ddlZedƒZ	edƒZ
dZg d¢Zd	d
ddddœZd	d
ddddddddddœZdd„ Zdd„ Zd&dd„Zd'dd„Zdd „ Zd!d"„ Zd#d$„ Zed%krÂeƒ  dS )(a:  Join every data source to the league_data match table (ML experiment).

Run with the isolated env (re-run any time; it reads whatever the downloads
have produced so far):
    E:\soccer\ml_env\Scripts\python.exe research\ml_join.py

Spine: league_data -- one row per match, 22 European divisions, 2019/20 on,
with results, match stats and bookmaker odds (opening and closing).

Attached per match:
  elo_*        research/ml_elo.py (pre-match ratings)
  us_*         Understat xG / npxG / PPDA / deep completions (top 5 only)
  tm_*         Transfermarkt game and club ids (first tiers; lineups/values
               are joined later through these ids)
  kalshi_*     Kalshi event, the three market tickers and kickoff time

TEAM NAMES. Every source spells clubs differently ("Man United", "Manchester
United", "MUN", club id 985). Nothing is hand-mapped: each source's names are
mapped to league_data's by CO-OCCURRENCE -- the same division on the same date
(+-1 day), counted over the whole history, so the true pairing wins by a wide
margin. Unresolved or contested names are listed, never guessed. The existing
data/team_map.json (used by the live system) is only a cross-check.

Writes research/ml_data/:
  matches_joined.csv   the spine plus the ids above
  name_map.csv         every source name -> league_data name, with its votes
é    N)ÚCounterÚdefaultdict)ÚPathzE:\soccer\research\ml_datazE:\soccer\league_dataé   );ÚDivÚDateÚTimeÚHomeTeamÚAwayTeamÚFTHGZFTAGÚFTRZHTHGZHTAGÚHSÚASÚHSTÚASTZHFZAFZHCZACZHYZAYZHRZARZPSHZPSDZPSAÚPSCHZPSCDZPSCAZAvgHZAvgDZAvgAÚAvgCHZAvgCDZAvgCAZMaxCHZMaxCDZMaxCAZB365HZB365DZB365AZB365CHZB365CDZB365CAZBFEHZBFEDZBFEAZBFECHZBFECDZBFECAzAvg>2.5zAvg<2.5zAvgC>2.5zAvgC<2.5ZAHhZAHChZAvgAHHZAvgAHAZAvgCAHHZAvgCAHAZE0ZSP1ZD1ZI1ZF1)zENG-Premier LeaguezESP-La LigazGER-BundesligazITA-Serie AzFRA-Ligue 1ZN1ZP1ZB1ZSC0ZT1ZG1)ZGB1ZES1ZL1ZIT1ZFR1ZNL1ZPO1ZBE1ZSC1ZTR1ZGR1c               
   C   sÚ   g } t t d¡ƒD ]f}dD ]>}z"tj||dddd„ d�}W  qZW q tyV   Y qY q0 q|j d¡d	 |d
< |  |¡ qtj	| dd�j
ddgd�}tj|d ddd�|d< |j
dgd� g d¢¡}| ddg¡jdd�S )Nz*_[0-9][0-9][0-9][0-9].csv)z	utf-8-sigÚcp1252ÚskipFc                 S   s   | t v S ©N)ÚKEEP)Úc© r   úE:\soccer\research\ml_join.pyÚ<lambda>9   ó    zload_spine.<locals>.<lambda>)ÚencodingÚon_bad_linesÚ
low_memoryÚusecolsÚ_r   ZseasonT)Úignore_indexr	   r   )Úsubsetr   Úcoerce)ÚdayfirstÚerrors©r   r   r	   r
   r   )Údrop)ÚsortedÚLDÚglobÚpdÚread_csvÚUnicodeDecodeErrorÚstemÚsplitÚappendÚconcatÚdropnaÚto_datetimeÚdrop_duplicatesÚsort_valuesÚreset_index)ÚframesÚfÚencÚdr   r   r   Ú
load_spine3   s     ÿ
r;   c                 C   sF   dd l }| dt| ƒ¡ dd¡ ¡  ¡ } d d dd„ | D ƒ¡ ¡ ¡S )	Nr   ZNFKDÚasciiÚignoreú Ú c                 s   s   | ]}|  ¡ r|nd V  qdS )r>   N)Úisalnum)Ú.0Úchr   r   r   Ú	<genexpr>H   r   z_fold.<locals>.<genexpr>)ÚunicodedataÚ	normalizeÚstrÚencodeÚdecodeÚlowerÚjoinr/   )ÚxrD   r   r   r   Ú_foldE   s     rL   ç)\�Âõ(ì?c                    s*  ddl m‰ ttƒ}t|j|j|jƒD ]\}}}||  ||f¡ q&i }|  	¡ D ]Ô\\}}	}
t
|
tƒshqPt|
ƒ‰ g }| |d¡D ]D}|ht|p�i  |d¡ƒB }t‡ ‡fdd„|D ƒƒ}| ||f¡ q€|jdd� |rP|d d |krPt|ƒdk�s|d d |d d  d	krP|d d |||	f< qP|S )
a  (div, src_name) -> spine name from a readable label ("Huddersfield",
    "Sudtirol Bolzano"), where one spine club in that division is clearly the
    closest spelling. Only used to START the co-occurrence propagation; a
    seed that the fixtures contradict loses to them in cooccur().r   )ÚSequenceMatcherr   c                 3   sX   | ]P}t |ƒˆ v sˆ t |ƒv r<ttt |ƒƒtˆ ƒƒd kr<dnˆdt |ƒˆ ƒ ¡ V  qdS )é   ç      ð?N)rL   ÚminÚlenZratio)rA   Ún©ÚLrN   r   r   rC   \   s   ÿ6zname_seed.<locals>.<genexpr>T©Úreverser   g{®Gáz´?)ZdifflibrN   r   ÚsetÚzipr   r	   r
   ÚupdateÚitemsÚ
isinstancerF   rL   ÚgetÚmaxr0   ÚsortrR   )ÚlabelsÚspineZextra_aliasÚcutZteamsÚdvÚhÚaÚoutÚsrc_nameÚlabelZscÚtÚnamesÚbestr   rT   r   Ú	name_seedK   s(    
ÿ>rl   c                    s†  t tƒ}t|j|jj ¡ |j|jƒD ]"\}}}}|||f  	||f¡ q$tt| d | j
j ¡ | j| jƒƒ}	t tƒ}
|	D ]š\}}}}tt td ƒD ]|}| ||tj|d� fd¡}|dkr¾dndtt|ƒdƒ }|D ]8\‰‰ |
||f ˆ  |7  < |
||f ˆ   |7  < qÔq’qvt|�pi ƒ}|
 ¡ D ]ª\}}| d¡}|d t|ƒdk�rZ|d d nd	 \‰}}|t|d
ƒ }||v�r(ˆ|d k�sÈ|dk�r |dk�sÈ|dk�r´|dk�sÈ|dk�r(|dk�r(ˆ||< �q(‡fdd„}t tƒi  ‰}tdƒD �]®}t tƒ‰|	D ]ð\}}}}| ||f¡| ||f¡ ‰‰ tt td ƒD ]²}| ||tj|d� fd¡}ˆ�rª‡fdd„|D ƒ}t|ƒdk�rªˆ||f |d   d7  <  �qˆ �rB‡ fdd„|D ƒ}t|ƒdk�rBˆ||f |d   d7  <  �q�qB�qi }ˆ ¡ D ]`\}}| d¡}|d t|ƒdk�r8|d d nd \‰}}|dk�r|d| k�rˆ||< �qdd„ ˆ ¡ D ƒ}|i |¥|¥|ƒ\}}||k�r  �q¨|}�qög }t|
ƒtˆƒB D ]À}ˆ |¡�pÐtƒ }| d¡}| |¡�p|�rú|d d n|
|  d¡d d ‰| ˆd¡}t‡fdd„| ¡ D ƒdd�}| 	||d |d ˆ|t|t|dƒ dƒ||v | |d¡dœ¡ �q¼||fS )a–  Map src team names to spine names, per division, from the fixtures.

    src: DataFrame[div, date, home, away]. Returns (mapping, report rows).

    Pass 1 -- same-day votes. Every spine fixture in the division on the same
    date (+-1 day) votes for its home club as the src home club's name, and
    likewise for away. On a weekend where the whole division plays at once
    that is nearly every club, so this only separates teams in leagues that
    spread their fixtures; it is kept to START the mapping, not to finish it.

    Pass 2 -- anchored propagation. Once one side of a src fixture is known
    (from pass 1 or a name seed), the spine fixture that day with that club
    in that slot is unique, which pins down the OTHER side exactly. Repeated
    until nothing new resolves. A name is accepted with >=2 anchored votes and
    at least twice its runner-up; each spine name goes to one src name only.
    Údivr   ©Údaysr   r   rP   g333333Ó?é   g        g•Ö&è.>é   é   g      ø?g      @c                    s  t tƒ}|  ¡ D ]*\}‰ ||d ˆ f  | |d¡|f¡ qi i  }}| ¡ D ]¾\\}‰ }|jdd� ˆ ||d d < |dd … D ]ˆ\}}	ˆ |	tƒ ¡}
t‡ fdd„|
 ¡ D ƒdd�}|
 ˆ d¡dkrê|
 ˆ d¡d	t|d
ƒ krêˆ ||	< q‚dˆ › d|d d d › �||	< q‚qN||fS )Nr   TrV   r   c                    s   g | ]\}}|ˆ kr|‘qS r   r   ©rA   ri   rS   ©Útgtr   r   Ú
<listcomp>˜   r   z+cooccur.<locals>.dedupe.<locals>.<listcomp>©Údefaultrq   rO   ç      à?zlost z to )r   Úlistr[   r0   r]   r_   r   r^   )ÚmpÚstrengthZclaimsÚkeyrf   Úlostrc   ÚlstÚvZloserr   Zrunner)Úanchoredrt   r   Údedupe‹   s    $
*
$zcooccur.<locals>.dedupeé   c                    s    g | ]}|d  ˆ kr|d ‘qS )r   r   r   ©rA   rK   )ÚHr   r   rv   §   r   zcooccur.<locals>.<listcomp>c                    s    g | ]}|d  ˆ kr|d ‘qS ©r   r   r   r„   )ÚAr   r   rv   ¬   r   c                 S   s$   i | ]\}}||  d ¡d d  “qS r†   )Úmost_common)rA   Úkr   r   r   r   Ú
<dictcomp>¶   r   zcooccur.<locals>.<dictcomp>c                    s   g | ]\}}|ˆ kr|‘qS r   r   rs   rt   r   r   rv   Â   r   rw   ry   r?   )Úsourcerm   rg   Úfd_nameÚvotesÚmarginÚokÚnote)r   rz   rY   r   r   ÚdtrE   r	   r
   r0   ÚdateÚhomeÚawayr   ÚrangeÚTOLr]   r+   Ú	Timedeltar^   rR   Údictr[   rˆ   rX   Úround)Úsrcra   r‹   ÚseedZby_dayrc   Údterd   re   Zsrc_rowsr�   r‰   ZcandsÚwÚmappingr}   r   Útopr€   ÚsecondÚmr‚   r~   r    ÚdayZoppÚnewr|   ÚmergedÚrowsr   )r‡   r…   r�   ru   r   Úcooccure   s€    ("
,@ÿÿ
,

4þr¦   c                    s<  i }t t| j| jj ¡ | j| jƒƒD ] \}\}}}	}
||||	|
|f< q$i ‰ t tˆd ˆjj ¡ ˆj	ˆj
ƒƒD ]Œ\}\}}}	}
| ||	f¡| ||
f¡ }}|rl|s¤qldddt d … D ]>}| ||||tj|d� f¡}|dur¸|ˆ vr¸|ˆ |<  qlq¸ql|  ¡ }tj‡ ‡‡fdd„|D ƒtˆ  ¡ ƒd	�}| |¡tˆ ƒfS )
zˆLeft-join src rows (already name-mapped) onto the spine by
    (div, home, away, date within TOL). Returns the spine with prefixed cols.rm   )r   éÿÿÿÿr   Nrp   r   rn   c                    s.   i | ]&}ˆ› |› �ˆ|   ¡ tˆ  ¡ ƒ “qS r   )Úto_numpyrz   Úvalues)rA   r   ©ZhitÚprefixrš   r   r   rŠ   Û   r   zattach.<locals>.<dictcomp>©Úindex)Ú	enumeraterY   r   r   r‘   rE   r	   r
   r’   r“   r”   r]   r–   r+   r—   ÚcopyÚ	DataFramerz   ÚkeysrJ   rR   )ra   rš   rž   Úcolsr«   ÚidxÚirc   rœ   rd   re   Újr…   r‡   r‰   rf   Úaddr   rª   r   ÚattachÉ   s(    ÿ2
ÿr·   c                  C   s˜  t  td d ¡} t tdƒjdd�¡}dd„ | ¡ D ƒ}g }|  d¡D �]B\}}d	d
„ |j	D ƒ}t
|ƒdksH|jjd |vr€qH| dd¡d dd … }|\}}	|||	 kr´||	fn||	| krÈ|	|fnd\}
}|
sÖqHtt|j	|jƒƒ}|j|jdkdf }| ||jjd  |jjd |t  |jjd ¡ d ¡|jjd |jjd |
|||
 | d¡|| t
|ƒ�r‚|
d|dddi |jd ¡nd dœ¡ qHt  |¡S )NZkalshi_historyzmarkets.csvzE:\soccer\data\league_map.jsonúutf-8©r   c                 S   s&   i | ]\}}|  d ¡r|  d ¡|“qS )Zkalshi_series)r]   )rA   r‰   r€   r   r   r   rŠ   ã   r   z!kalshi_events.<locals>.<dictcomp>Úeventc                 S   s   g | ]}|d kr|‘qS )ÚTIEr   )rA   Úsr   r   r   rv   æ   r   z!kalshi_events.<locals>.<listcomp>rp   r   ú-r   é   )NNZyesÚsider»   r…   r‡   ÚD)rm   Úseriesrº   r’   Úkickoff_utcÚtitler“   r”   Útk_homeÚtk_tieÚtk_awayÚresult)r+   r,   ÚMLÚjsonÚloadsr   Ú	read_textr[   Úgroupbyr¿   rR   rÁ   Úilocr/   r˜   rY   ZtickerÚlocrÇ   r0   r3   rÂ   Ú
tz_convertrÃ   r]   r°   )r¡   ZlmZs2dr¥   ZevÚgZsidesÚsuffixre   Úbr“   r”   ZtkÚresr   r   r   Úkalshi_eventsà   s.    0(û
rÔ   c                  C   sT  t ƒ } tdt| ƒ› d| j ¡ › d| j ¡ d›d| j ¡ d›�ƒ tj	t
d dgd�}| j|g d	¢ g d
¢dd�} g }t 	t
d ¡}|j t¡|d< t |j¡|d< |jdddœd�}||jdk }t|| dƒ\}}||7 }g d¢}t| |||dƒ\} }td|› dt|ƒ› d|t|ƒ d›d�ƒ tj	t
d d dd �}||j t¡  ¡ }|j t¡|d< t |j¡|d< ||jdk }|j t¡|j t¡ |d< |d< t|| dƒ\}}tt|j|jƒƒtt|j |j!ƒƒB }	|D ]}
|	 "|
d! ¡|
d"< �qÒ||7 }t| ||g d#¢d$ƒ\} }td%|› dt|ƒ› d|t|ƒ d›d�ƒ t#ƒ }|j$jj%d&d'd(d)�j &d*¡}t 't(|d ƒd+ t(|jƒt(|j ƒ t(|jd, ƒt(|jd- ƒ d.œ¡}| )dd/g¡j* +d0d1„ ¡ ,¡ }t- .t/d2ƒj0d3d4�¡}t1t2ƒ}| 3¡ D ]J\}}t4|tƒ�râ| "d5¡�râ||d5   5|gt(| "d6¡�p"g ƒ ¡ �qât|| d7t6|| |ƒd8�\}}|D ] }
| "|
d |
d! f¡|
d"< �qN||7 }t| ||g d9¢d:ƒ\} }| j7 8¡ | j7| j9k@  :¡ }td;|› dt|ƒ› d<|tt|ƒd=ƒ d›d>|› �ƒ t- .t/d2ƒj0d3d4�¡}d?d@„ | ;¡ D ƒ}d, }}| 3¡ D ]h\\}}}||f|v �r|||f |k�rL|d=7 }n0|d=7 }tdA|› dB|› dC|›dD|||f ›�ƒ �qtdE|› dF|› dG�ƒ |j<|j= t2| j> ?¡ ƒ¡dH�}||d  t2| jƒ¡  )dI¡j@ +dJdKg¡}tdLƒ tdMdN AdOdP„ | B¡ D ƒ¡ dQtC|dJ  :¡ ƒ› dtC|dK  :¡ ƒ› � dR|dJ  :¡ |dK  :¡  d›d� ƒ t '|¡}|jDt
dS ddT� ||jE  }t|ƒ�r°tt|ƒ› dU�ƒ t| FdVdg¡jGddWdX�ƒ | jDt
dY ddT� | | jdZk }| )d[¡j+d\d]d^d1„ fd_d`d1„ fdadbd1„ fdcddd1„ fdedfd1„ fdgdhd1„ fdi�}tdjƒ t| G¡ ƒ tdkt
dY › dRt| ƒ› dl�ƒ d S )mNzspine: z
 matches, z divisions, z%Y-%m-%dz -> zelo_matches.csvr   )Úparse_dates)r   r   r	   r
   Zelo_homeZelo_awayZelo_diffZelo_exp_homer&   Úleft)ÚonÚhowzunderstat_team_matches.csvrm   r’   r“   r”   )Z	home_teamZ	away_team)Úcolumnsz
2019-07-01Ú	understat)Úgame_idZhome_xgZaway_xgZ
home_np_xgZ
away_np_xgZ	home_ppdaZ	away_ppdaZhome_deep_completionsZaway_deep_completionsZhome_expected_pointsZaway_expected_pointsZus_zunderstat: ú/z games joined (z.1%ú)Ztransfermarktzgames.csv.gzF)r   rg   Z	src_label)rÛ   Úhome_club_idÚaway_club_idZtm_ztransfermarkt: z:.*$r?   T)Úregexz vs rp   r   r§   )rm   ÚabrÚnamerá   c                 S   s   |   ¡ jd S )Nr   )ÚmoderÍ   ©rK   r   r   r   r   %  r   zmain.<locals>.<lambda>zE:\soccer\data\team_map.jsonr¸   r¹   ÚfdcoÚaliasesÚkalshi)r›   )rº   rÁ   rÂ   rÄ   rÅ   rÆ   rÇ   Zkalshi_zkalshi: z events joined (r   z(); result disagrees with league_data on c                 S   sF   i | ]>}t |tƒr| d ¡r| d¡r| d¡| d ¡f| d¡“qS )rç   rå   rm   )r\   r˜   r]   )rA   r€   r   r   r   rŠ   7  s   ÿzmain.<locals>.<dictcomp>z  team_map disagrees: r>   z -> cooccur z, team_map zkalshi names vs team_map.json: z agree, z	 disagree)ÚjoinedrÁ   ÚsumÚsizez:kalshi events joined per series (leagues with odds files):z  z, c                 s   sF   | ]>\}}|  d d¡dd… › dt|d ƒ› dt|d ƒ› �V  qdS )ZGAMEr?   rp   Nr>   ré   rÜ   rê   )ÚreplaceÚint)rA   r¼   Úrr   r   r   rC   E  s   ÿzmain.<locals>.<genexpr>z  -> total z (zname_map.csvr¬   z unresolved names:r‹   é<   )r­   Úmax_rowszmatches_joined.csvz
2025-05-01r   )r	   rê   Úkalshi_eventc                 S   s   |   ¡  ¡ S r   ©Únotnaré   rä   r   r   r   r   U  r   Z
us_game_idc                 S   s   |   ¡  ¡ S r   rñ   rä   r   r   r   r   V  r   Z
tm_game_idc                 S   s   |   ¡  ¡ S r   rñ   rä   r   r   r   r   W  r   r   c                 S   s   |   ¡  ¡ S r   rñ   rä   r   r   r   r   X  r   r   c                 S   s   |   ¡  ¡ S r   rñ   rä   r   r   r   r   Y  r   r   c                 S   s   |   ¡  ¡ S r   rñ   rä   r   r   r   r   Z  r   )Úmatchesrç   rÚ   ÚtmZpinnacle_closeZ	avg_closeZshotsz-
since 2025-05-01 (matches with each source):zwrote z rows))Hr;   ÚprintrR   r   Únuniquer   rQ   r^   r+   r,   rÈ   ÚmergeZleagueÚmapÚUS_DIVr3   r’   Úrenamer¦   r·   Zcompetition_idÚisinÚTM_DIVr¯   rÞ   ÚastyperF   rß   r˜   rY   r“   Zhome_club_namer”   Zaway_club_namer]   rÔ   rÃ   rë   r/   r°   rz   rÌ   râ   ÚaggÚto_dictrÉ   rÊ   r   rË   r   rX   r[   r\   rZ   rl   Zkalshi_resultrò   r   ré   r©   Úassignrº   rð   r2   rè   rJ   Úiterrowsrì   Úto_csvr�   r5   Ú	to_string)ra   ZeloZreportÚusr{   Úrepr²   rS   rô   rj   rí   Úkevri   ÚlabZtmapÚaliasr‰   r€   ZbadZliveZagreeÚdisrc   ÚabÚfdÚksZunresÚcovr   r   r   Úmainù   s¾    
ÿ
ÿþ*"$*$ÿ,ÿ*ÿ
,*ÿ&þ ý







úr  Ú__main__)NrM   )N)Ú__doc__rÉ   Úcollectionsr   r   Úpathlibr   Úpandasr+   rÈ   r)   r–   r   rù   rü   r;   rL   rl   r¦   r·   rÔ   r  Ú__name__r   r   r   r   Ú<module>   s.   ÿ
ÿ

dg