# impl2 · §4 densidades, banda vieja, evaluable, migración -> salidas/impl2/migracion_celdas.csv, trimestres.csv, migracion_fuentes.csv import pandas as pd, numpy as np, sys print('python', sys.version.split()[0], 'pandas', pd.__version__, 'numpy', np.__version__) c = pd.read_csv('/w/tmp/mig_counts.csv', keep_default_na=False, na_values=[]) # anchura (euros) de cada banda; N1 cerrado [18.000,18.150] -> 150 W = {'resto': {'V':500,'V1':605,'N':500,'N1':150,'belowV':1500,'belowV1':1815}, 'obras': {'V':1000,'V1':1210,'N':1000,'N1':1210,'belowV':1500,'belowV1':1815,'belowV_x2':3000,'belowV1_x2':3630}} cells = c[(c.periodo=='antes') & (c.n_total>0)][['source','base','clase']].drop_duplicates().values.tolist() def get(s,b,cl,p,col): r = c[(c.source==s)&(c.base==b)&(c.clase==cl)&(c.periodo==p)] return int(r[f'{cl}_{col}'].iloc[0]) if len(r) else 0 def gtot(s,b,cl,p): r = c[(c.source==s)&(c.base==b)&(c.clase==cl)&(c.periodo==p)] return int(r['n_total'].iloc[0]) if len(r) else 0 def dens(n, nn): # permil del normalizador return 1000.0*n/nn if nn>0 else np.nan rows=[] for s,b,cl in cells: d = {'source':s,'base':b,'clase':cl} for p in ('antes','despues2T','despues'): d[f'n_total_{p}'] = gtot(s,b,cl,p) d[f'n_norm_{p}'] = get(s,b,cl,p,'norm') if cl=='resto': d[f'n_fable_{p}'] = get(s,b,cl,p,'fable') bands = ['V','N'] if b=='sin' else ['V2','V1','N2','N1'] for bd in bands: col = {'V2':'V','N2':'N'}.get(bd,bd) w = W[cl][col] for p in ('antes','despues2T','despues'): n = get(s,b,cl,p,col); nn = d[f'n_norm_{p}'] d[f'{bd}_n_{p}'] = n d[f'{bd}_permil_{p}'] = dens(n,nn) d[f'{bd}_permil500_{p}'] = dens(n,nn)*500.0/w if cl=='resto': d[f'{bd}_permilFable_{p}'] = dens(n, d[f'n_fable_{p}']) # banda vieja if b=='sin': old='V' else: a2, a1 = d['V2_permil500_antes'], d['V1_permil500_antes'] old = 'V1' if (not np.isnan(a1) and (np.isnan(a2) or a1 > a2)) else 'V2' # empate -> V2 new = ['N'] if b=='sin' else ['N2','N1'] d['banda_vieja'] = old colold = {'V2':'V','V':'V','V1':'V1'}[old] belowcols = [('belowV' if colold=='V' else 'belowV1')] if cl=='obras': belowcols.append(belowcols[0]+'_x2') nold = d[f'{old}_n_antes']; wold = W[cl][colold] for k,bc in enumerate(belowcols): nb = get(s,b,cl,'antes',bc); wb = W[cl][bc] suf = '' if k==0 else '_x2' d[f'below_n_antes{suf}'] = nb; d[f'below_anchura{suf}'] = wb # densidad por 500 € de la banda >= 2 x media por 500 € de los € inferiores (mismo normalizador -> se cancela) d[f'pico{suf}'] = bool(nold >= 20 and (nold*500.0/wold) >= 2.0*(nb*500.0/wb)) d[f'pico_ratio{suf}'] = (nold*500.0/wold)/(nb*500.0/wb) if nb>0 else np.inf d['evaluable'] = bool(d['n_norm_antes']>=200 and d['n_norm_despues2T']>=100 and d['pico']) if cl=='obras': d['evaluable_x2'] = bool(d['n_norm_antes']>=200 and d['n_norm_despues2T']>=100 and d['pico_x2']) ra, rd = d[f'{old}_permil_antes'], d[f'{old}_permil_despues2T'] d['vieja_ratio_d2T_antes'] = rd/ra if (ra and not np.isnan(ra) and ra>0 and not np.isnan(rd)) else np.nan d['migra'] = bool(d['evaluable'] and rd <= 0.5*ra) if cl=='obras': d['migra_x2'] = bool(d['evaluable_x2'] and rd <= 0.5*ra) for nb_ in new: na, nd = d[f'{nb_}_permil_antes'], d[f'{nb_}_permil_despues2T'] d[f'{nb_}_ratio_d2T_antes'] = nd/na if (not np.isnan(na) and na>0 and not np.isnan(nd)) else np.nan rows.append(d) M = pd.DataFrame(rows) M.to_csv('/w/out/migracion_celdas.csv', index=False) # trimestres 2017T1-2019T4 qs = [f'{y}T{q}' for y in (2017,2018,2019) for q in (1,2,3,4)] T=[] for s,b,cl in cells: for p in qs: d={'source':s,'base':b,'clase':cl,'trimestre':p,'n_total':gtot(s,b,cl,p),'n_norm':get(s,b,cl,p,'norm')} if cl=='resto': d['n_fable']=get(s,b,cl,p,'fable') for bd in (['V','N'] if b=='sin' else ['V2','V1','N2','N1']): col={'V2':'V','N2':'N'}.get(bd,bd); w=W[cl][col]; n=get(s,b,cl,p,col) d[f'{bd}_n']=n; d[f'{bd}_permil']=dens(n,d['n_norm']); d[f'{bd}_permil500']=dens(n,d['n_norm'])*500.0/w T.append(d) T = pd.DataFrame(T) for k in [x for x in T.columns if x.endswith('_n') or x in ('n_total','n_norm','n_fable')]: T[k] = T[k].astype('Int64') T.to_csv('/w/out/trimestres.csv', index=False) # veredicto por fuente (solo resto) R = M[M.clase=='resto'] F=[] for s,g in R.groupby('source'): F.append({'source':s,'celdas_resto':len(g),'celdas_evaluables':int(g.evaluable.sum()), 'celdas_migran':int(g.migra.sum()), 'veredicto': 'migracion_visible' if g.migra.any() else ('sin_migracion' if g.evaluable.any() else 'no_evaluable')}) F=pd.DataFrame(F); F.to_csv('/w/out/migracion_fuentes.csv', index=False) pd.set_option('display.width',250); pd.set_option('display.max_columns',40); pd.set_option('display.max_rows',200) show=['source','base','clase','n_norm_antes','n_norm_despues2T','banda_vieja','below_n_antes','pico','pico_ratio','evaluable','vieja_ratio_d2T_antes','migra'] print(M[show].to_string()) print(F.to_string()) print('fuentes con migracion visible:', int((F.veredicto=='migracion_visible').sum()), list(F[F.veredicto=='migracion_visible'].source))