a %j<@sdZddlmZddlZddlZedZdZej ddej ddej d dej d d e dd Z d dZ ddZ ddZ ddZhdZddZddZddZddZddZd d!Zd"d#Zed$kredS)%aRPre-match features for every match in matches_joined.csv (ML experiment). Run after ml_join.py, with the isolated env: E:\soccer\ml_env\Scripts\python.exe research\ml_features.py Every feature is known BEFORE kickoff. Team form is built from each club's previous matches only (shift, then exponentially weighted), across divisions, so a promoted club carries its lower-division form with it. Groups (column prefix): elo_ pre-match Elo (ml_elo.py) h_/a_ home/away team form, EWM half-life 5 matches: goals, shots, shots on target, corners, points, and Understat xG/npxG/PPDA/deep completions where the league has them; plus rest days bk_ bookmaker implied probabilities, de-vigged: market-average opening and closing, Pinnacle closing, and the open->close move k_ Kalshi: de-vigged mid at KO-24h / -6h / -1h / -15m / KO, the ask and bid at KO (what a bet would actually pay), volume and open interest tv_ Transfermarkt squad market value: top-18 / top-11 players by value on the club's books at the last month-end before the match (first tiers only; valuations frozen 2026-07-06) sf_ SoFIFA squad ratings from the latest snapshot on or before the match (added once that download finishes) Writes research/ml_data/features.csv. )PathNzE:\soccer\research\ml_datahours)minutes)24hZ6hZ1hZ15mkocCs.d||jtjdd}|j|jddddS)Nrcoerce)errorsaxisr)applypd to_numericdivsum)dfcolsinvr!E:\soccer\research\ml_features.pydevig(src Cst|j|j|jd|j|j|j|j|j |j |j |j |j |j|j|j|j|j|j|jd}t|j|j|jd|j|j|j|j|j |j |j |j |j|j |j|j|j|j|j|jd}t||ggdjdd}t|j|jk|j|jkgddgd|d<gd }|jd d d }|D]"}||d d|d|<q||d<|d j!j"j#dd|d<dd|Dddg}||j$dk%d|&d}||j$dk%d|&d} |'| S)zCLong table (one row per team per match) -> EWM of previous matches.r)middateteamhomegfgasfsastfstacfcaxgfxganpxgfnpxgappdappda_oppdeepfdeepar)rrrT)droppts)r r!r"r#r$r%r&r'r2r(r)r*r+r,r-r.r/rF)sortcSs|djtddS)Nrr1)halflife min_periods)shiftewm HALF_LIFEmean)srrrFs zteam_form..f_Zf_nr)upperZf_restcSsg|]}d|qS)r<r.0crrr Jzteam_form..rZh_a_)(r DataFrameindexDateHomeTeamFTHGFTAGZHSASHSTASTZHCZACZ us_home_xgZ us_away_xgZ us_home_np_xgZ us_away_np_xgZ us_home_ppdaZ us_away_ppdaZus_home_deep_completionsZus_away_deep_completionsAwayTeamconcat sort_values reset_indexnpselectr r!groupby transformcumcountdiffdtdaysclipr set_index add_prefixjoin) mrZawaytZstatsgrAZfeatharrr team_form.s8 *  rccCstj|jd}gdgdgdgddD]<\}}t||}||d|dd|d d|d g<q0d D]*}|d ||d ||d|<qrt|ddgjdddf|d<|d|d<|S)NrF)ZAvgCHZAvgCDZAvgCA)ZAvgHZAvgDZAvgA)ZPSCHZPSCDZPSCA)ZB365CHZB365CDZB365CA)ZavgcZavgoZpscZb365cZbk__h_dZ_ahdaZbk_avgc_Zbk_avgo_Zbk_move_zAvgC>2.5zAvgC<2.5rZ bk_over25_cZAHChZ bk_ahc_line)rrErFitemsrto_numpyiloc)r^outnamerpr:rrr bookmakerPs   ,(  rnc Cstjtddgddgdd}g}tjdddd D]}||j|kd d }||j|tjd d k}|jdddd}| t ||j dd|j dd| dq8t|jddidd}tj |jd}dD]\}} || dgj| did} | jt| d<| d} tj| |ddddd} | d} t| jjdd |d!|d"<t| jjdd |d!|d#<q|j|j|d$<|S)%zTransfermarkt squad value per club at month-end snapshots. A player counts for the club named on his latest valuation as of the snapshot; valuations are revised a few times a year, so month-ends are finer than the data itself. Z transfermarktzplayer_valuations.csv.gz) player_idrmarket_value_in_eurcurrent_club_idr)usecols parse_datesz 2019-06-30z 2026-07-31ME)freqrori&rYrpF ascendingrqcSs|dS)Nheadrxrrrr;nrCzsquad_value..cSs|dS)N rzr|rrrr;orC)snaptop18top11nclubcolumnsrrd))ratm_home_club_id)rbtm_away_club_idrGbackward)left_onright_onby directionrFj@)lowerZtv_Z_top18Z_top11 tv_diff18)rread_csvMLrP date_rangerrTtail TimedeltaappendrErprsizerQrOrenamerFdropnarastypeint merge_asofr[rRlog10rrZrZ tv_h_top18Z tv_a_top18) fvZsnapsmecurvalssnrksidecolqjrrr squad_value]s:   "r>"ussvZvflifasZscssZfkskderr'Ztsg1ZkulubuZfootballsdZsscfcudZkrcZkaaZcalcioZtheZsporZrscrcZkvZcdr&ZvfbZbkacZafccCsTddl}|dt|dd}ddd|D}dd d|DS) NrZNFKDasciiignorecss|]}|r|ndVqdS) N)isalnum)r@chrrr rCz_norm..rcss|]}|tvr|VqdSN)_STOP)r@r_rrrrrC) unicodedata normalizestrencodedecoderr]split)r}rrrr_norms rcCsNddlm}t|t|}}|r&|s*dS||vs:||vr>dS|d||S)Nr)SequenceMatcherggffffff?)ZdifflibrrZratio)rbbrrrr_sims rc sVddl}ddlm}ittd}||j}t|j|j | d|j D],\}} h dd||fDqJ| tdjd d }|D]L\}} t| tr| d r | d | d h |gt| d pgqig} } |d D]&\} } | jtjdd| jtjdd}}||j| k|j||@}tt|jt|jB| dddg!}rt"|sqt#$fdd|D}|| \}}t||D]b\}}|||fdkr|| | ||df<n,| %| ||d|t&|||fdfqq| rRt'dt"| d| D]}t'd|q@| S)aSoFIFA (league, team_id) -> league_data name, per division. SoFIFA has no fixtures, so co-occurrence cannot work. Instead, within each division, SoFIFA clubs and the clubs league_data has in that division within a year of the snapshots are paired one-to-one (Hungarian assignment) by the best name similarity over every alias the other sources gave us (Understat / Transfermarkt / Kalshi names from name_map.csv, and team_map.json). Pairs below 0.6 similarity are dropped and printed. rN)linear_sum_assignmentz name_map.csvZ src_labelcss$|]}t|tr|s|VqdSr) isinstancerisdigit)r@r}rrrrrCz"sofifa_team_map..zE:\soccer\data\team_map.jsonzutf-8)encodingZfdcoaliases fdco_leagueimrvteam_id team_namecs$g|]\}fddDqS)cs,g|]$}tfdd||hDqS)c3s|]}t|VqdSr)r)r@rbtnrrrrCz8sofifa_team_map....)maxget)r@r)aliasrrrrBrCz.sofifa_team_map...r)r@_rfdrrrBrCz#sofifa_team_map..g333333?rzsofifa: z+ clubs left unmapped (best pair below 0.6):z )(jsonZscipy.optimizerrrrokzipZfd_nameZsrc_namer setdefaultupdateloadsr read_textrhrdictlistrT update_dtminrrDivrGbetweensortedsetrHrNdrop_duplicatesrilenrRarrayrroundprint)r"spinerrnmrbrZtmapkrrkdroppedrr`lohispZteamsSrrAirdrrrsofifa_team_maps<   $$2 .2rcCsft|dd}|dr"dn|dr0dnd}zt|d|WSty`tjYS0dS) Nu€rMg.AKg@@rZMK) rreplacestripendswithfloatrstrip ValueErrorrRnan)r}multrrr_moneys   rcsBtd}|stj|jdSt|}tj|jdd|d<t||fddt |j |j D|d<||j }|jt|d <|jd jjd }t|d k|gd |gdggdd|d<g}|ddgD]\\}}jdddfdd}|||jdjjdd|dd|dd|dd|dddj dj!t"t#j$d%dd qt|d!} tj|jd} d"D]v\} } |d#| gj&| d$id%'d#} tj(| | d#d!d$d&tj)d'd(d)*d*}d+D]}||| d,| d-|<q q| j+| j,| d.<| S)/zFSoFIFA squad ratings from the latest snapshot on or before each match.zsofifa_player_ratings.csvrdz %b %d, %Y)formatrcsg|]\}}||fqSr)r)r@rr_)mprrrBrCzsofifa..rvalrrZGK)ZCBLBZRBZLWBZRWB)ZCDMZCMZCAMZLMZRM)gkdefrattlineoaFrwcsj|kj|Sr)r r r{r9)r r)r`rrr;rCzsofifa..r~ryrrr rr1r) rrrdepthrr rr age11pot11val18r))rarH)rbrNrGrrrxrv)rrrr tolerancerF) rrrr rr rrrZsf_r sf_diff11)-rexistsrrErFr to_datetime update_daterrrrrnotnacopyvlmapr positionsfillnarrrRrSisinrTrPrr r{r9rjZaeptrrrrrrQrrr[Z sf_h_top11Z sf_a_top11)rrpathr"posrowsZdt_rtoprrkrrrrrAr)r`rrsofifasL     "  "  r$c s||j}|jr"tj|jdStjtdddgd}tjtdddgd}|jj d|d<|jj d|d<| d}| d}g}| D]\}}t |j d}d|i}d |jfd |jfd |jffD]V\} } ||j| k} ||j| k} tD]\} || }d kr.| tjd dfn| tjddf\}}||j|k|j||k@d}t|r|jjd|jjd}}||d|dd| <dkr|||d| <|d| <|jjd|d| <q| | j|k| j|tjddk@}t|r|jntj|d| <q| |qt|!dtD]fdddD}t"fdd|DrP|jdd}|dd <dD].}dd||d!d|<qqPS)"aMid / bid / ask per side at fixed offsets before kickoff. A candle is stamped with the END of its period, so the bar stamped at KO covers the minute before kickoff. Bars exist only where the book existed; the value at time t is the last bar at or before t (a quiet book simply kept its quote), but never older than 3h for the 1-minute offsets or 12h for KO-24h, so a stale quote from the day before cannot pose as current. rdZkalshi_historyzcandles_1m.csvts_utc)rszcandles_1h.csvNrrarrbr rr1rrrk_mid_rr Zk_bid_Zk_ask_Zk_oi_Zk_vol3h_csg|]}dd|qS)r'rr)r@r:)labrrrBrCzkalshi..rgc3s|]}|vVqdSrrr?)rrrrrCzkalshi..rZ k_overround_Zk_p_)# kalshi_eventremptyrrErFrrr%rX tz_convertrPiterrows Timestampkalshi_kickoff_utcZkalshi_tk_homeZ kalshi_tk_tieZkalshi_tk_awayZticker K_OFFSETSrhrrrZ bid_closerjZ ask_closeZ open_interestvolumerrRrrr[all)r^Zkmc1rr"rrr recrZtkb1bhoffr_srctolr}bidZaskprertotr:r)rr(rkalshisL   &."  $& 0r<c Cs>tjtddgdd}|gd}|t|t|}|t|}|t||}t |}||}|j tddd||j dk}t d t |d |jd d tdt d t |d|jd|dtjtddt ddD]4}||vrt d|dd||dqdS)Nzmatches_joined.csvrGF)rs low_memory)rZseasonrGTimerHrNrIrJZFTRZelo_homeZelo_awayelo_diffZ elo_exp_homer)r.Z tm_game_idrrz features.csvrdz 2025-05-01z features: z rows x rz cols -> zsince 2025-05-01: z matches, z with Kalshi, k_p_ko_h)dtypez with a Kalshi price at KOz, fill rate of key features since 2025-05-01:) r?Zh_f_sfZh_f_xgfrrZ bk_avgc_hZbk_psc_hZ k_p_24h_hZk_p_1h_hr@Zk_ask_hz Z12rz6.1%)rrrrr]rcrnrr$r<to_csvrGrrshaper)rrrSeriesrr9)r^baserrkwrArrrmains" (  rG__main__)__doc__pathlibrnumpyrRpandasrrr8rr/rrcrnrrrrrrr$r<rG__name__rrrrs. " " , $1