
    \j                        S r SSKrSSKrSSKJrJr  SSKJr  SSKr	SSK
r
SSKr\" \5      R                  5       R                  R                  r\S-  S-  S-  r\S-  S-  S-  rS	\4S
 jr\S:X  a  \R*                  " \" 5       5        gg)a  
Aggancia gli xG di Understat (data/processed/xg.csv) alle partite nel database.

I nomi squadra di Understat differiscono da quelli di football-data
("AC Milan" vs "Milan"): la mappatura viene imparata automaticamente
accoppiando le partite per (lega, data, risultato esatto) nei casi non
ambigui, poi applicata a tutto il dataset.

Aggiunge (se mancano) le colonne home_xg / away_xg alla tabella matches
e le popola. Idempotente.

Uso:
    python scripts/import_xg.py
    N)Counterdefaultdict)Pathdata	processedzxg.csvzteam_mapping.csvreturnc                  L   [         R                  " SS9n [        R                  " U 5        U R	                  5       n[
        R                  " [        S/S9nUS   R                  R                  5       US'   [        R                  " UR                  UR                  UR                  UR                  SS9nUR!                  5       nUR#                  S5        UR%                  5       S   S:X  a,  UR#                  S	5        UR'                  5         [)        S
5        UR#                  S5        [
        R*                  " UR-                  5       / SQS9n[
        R.                  " US   5      US'   / SQnUR1                  U5      R3                  S 5      nUR1                  U5      R3                  S 5      nUR5                  XvS9n	[7        [8        5      n
U	R;                  SS9 HI  nXR<                     UR>                  ==   S-  ss'   XR@                     URB                  ==   S-  ss'   MK     0 nU
RE                  5        H\  u  pURG                  S5      S   u  nn[I        URK                  5       5      nUU-  S:  a  [)        SU S[M        U5       35        XU'   M^     [
        R*                  " [O        URE                  5       5      SS/S9RQ                  [R        SS9  [)        S[U        U5       S[R        RV                   S35        [Y        US   5      [Y        US   5      -  [Y        U5      -
  nU(       a  [)        S[O        U5       35        US   R[                  U5      US '   US   R[                  U5      US!'   UR]                  S S!/S"9R_                  5       n/ nURa                  S#S$9Ra                  S%S&9nURc                  SS'0S9nS( H  nURd                  (       a    OpUR_                  5       nUS   [
        Rf                  " US)9-   US''   UR5                  U/ S*QS+S,9nURi                  U5        UUS%   Rk                  US%   5      )    nM     [
        Rl                  " US#S-9nURo                  S./S/S09nUUS1   US2   :g  US3   US4   :g  -     n[U        U5      (       a1  [)        S5[U        U5       S635        URq                  URr                  S9nUR;                  SS9 Vs/ s HA  n[u        URv                  5      [u        URx                  5      [{        UR|                  5      4PMC     nnS7n[        S[U        U5      U5       H+  nUR                  S8UUUU-    5        UR'                  5         M-     [)        S9[U        U5       S:[U        U5       S;[U        U5      [U        U5      -  S< S35        UR#                  S=5        [)        S>5        UR-                  5        H/  u  nn n![)        S?US@ SA[{        U!=(       d    S5      SB SCU  35        M1     UR                  5         gs  snf )DNzImporta xG Understat nel DB)descriptiondate)parse_dates	tigertips)hostportuserpassworddatabasez
        SELECT COUNT(*) FROM information_schema.columns
        WHERE table_schema = 'tigertips' AND table_name = 'matches'
          AND column_name = 'home_xg'
    r   z{ALTER TABLE matches ADD COLUMN home_xg DECIMAL(6,3) NULL AFTER away_red, ADD COLUMN away_xg DECIMAL(6,3) NULL AFTER home_xgz5Colonne home_xg/away_xg aggiunte alla tabella matchesa3  
        SELECT m.id, l.code, m.match_date, h.name, a.name, m.home_goals, m.away_goals
        FROM matches m
        JOIN leagues l ON l.id = m.league_id
        JOIN teams h ON h.id = m.home_team_id
        JOIN teams a ON a.id = m.away_team_id
        WHERE l.code IN ('E0', 'SP1', 'D1', 'I1', 'F1')
    )idleaguer   home_dbaway_dbhgag)columns)r   r   r   r   c                     [        U 5      S:H  $ N   lengs    ,C:\wamp64\www\Tigertips\scripts\import_xg.py<lambda>main.<locals>.<lambda>F       Q1    c                     [        U 5      S:H  $ r   r   r   s    r!   r"   r#   G   r$   r%   )onF)indexr   g?z&  [ATTENZIONE] mappatura ambigua per 'z': 	understatfootball_datazMappatura nomi: z squadre (salvata in )home_usaway_usz2  [ATTENZIONE] squadre Understat senza mappatura: r   r   )subsetT)dropxg_idx)names	join_date)r   r   )days)r   r2   r   r   )_us_db)r'   suffixes)ignore_indexr   first)r.   keephg_ushg_dbag_usag_dbz  [ATTENZIONE] z. accoppiamenti con risultato diverso, scartatii  z;UPDATE matches SET home_xg = %s, away_xg = %s WHERE id = %sz
Partite Understat: z | agganciate al DB: z (z.1%z
        SELECT l.code, COUNT(*), SUM(m.home_xg IS NOT NULL)
        FROM matches m JOIN leagues l ON l.id = m.league_id
        GROUP BY l.code ORDER BY l.code
    zCopertura xG nel DB:z  z<5 z>6z / )BargparseArgumentParserconfigadd_db_args
parse_argspdread_csvXG_PATHdt	normalizepymysqlconnectr   r   r   r   cursorexecutefetchonecommitprint	DataFramefetchallto_datetimegroupbyfiltermerger   r   
itertuplesr,   r   r-   r   itemsmost_commonsumvaluesdictsortedto_csvMAP_PATHr   namesetmapdropnacopyreset_indexrenameempty	Timedeltaappendisinconcatdrop_duplicatesr/   r(   floatxg_hxg_aintr   rangeexecutemanyclose)"parserargsxgconncurdbkey	db_unique	xg_uniquepairedvotesrname_mapus_namecounterbestn_besttotalunmappedxg_okmatched_parts	remaining
db_renamedshiftattemptmergedmatchedbadrowsbatchstartcodetotwith_xgs"                                     r!   mainr      s   $$1NOF
vD	W6(	3BF((*BvJ??						$(MMKID
++-C KK  	
 ||~aA I 	J 	EFKK  	 
clln /B 
CB6
+BvJ )C

3&&'<=I

3&&'<=I__Y_/F +G 4EU+ii#q(#ii#q(# , H!KKM**1-a0fGNN$%E>C:7)3tG}oVW  * LL()K3QR		&	S]O+@q
QRByM"SI%773x=HHB6(CSBTUV yM%%h/ByMyM%%h/ByMIIi3I499;EM!!t!,88x8HIFK#89J??.."&v51IIz"O(6  8 	V$y277x8HIIJ	  iiD9G%%dV'%BG 77#ww'777#ww'779 :C
3xxCz)WXY,,SYY,/ ''e'464 166]E!&&M3qtt954 	 6Eq#d)U+UU55=1	3 ,
 
!#b'*?G~ N'lSW$S), -KK  	
 

 !!llnc74)1SA.r2#cU;< - 	JJL)6s   #AX!__main__)__doc__r@   syscollectionsr   r   pathlibr   pandasrE   rJ   rB   __file__resolveparentPROJECT_ROOTrG   r_   rp   r   __name__exit r%   r!   <module>r      s     
 ,    H~%%'..55

+
-
8& ;.1CCrc rj zHHTV r%   