Untitled

# 4C: Gráfico con el porcentaje de establecimientos premiados con el 60% y con el 100% para cada año en cada región.
import numpy as np
from matplotlib import pyplot as plt
conn = psycopg2.connect(**params)
cursor = conn.cursor()


cursor.execute(""" 
with exr as (
    select nom_reg_rbd,count(*) as estabs from region r
    inner join provincia p on r.cod_reg_rbd = p.cod_reg_rbd
    inner join comuna c2 on p.cod_pro_rbd = c2.cod_pro_rbd
    inner join establecimiento e on c2.cod_com_rbd = e.cod_com_rbd
    group by 1 order by estabs desc
    ), premio100 as (
        select agno,nom_reg_rbd,count(s.rbd) as cantidad_premiados from sned s
        inner join establecimiento e on e.rbd = s.rbd
        inner join comuna c on e.cod_com_rbd = c.cod_com_rbd
        inner join provincia p on c.cod_pro_rbd = p.cod_pro_rbd
        inner join region r on p.cod_reg_rbd = r.cod_reg_rbd
        where sel_35=1
        group by 1,2 order by agno
    ),premio60 as (
        select agno,nom_reg_rbd,count(s.rbd) as cantidad_premiados from sned s
        inner join establecimiento e on e.rbd = s.rbd
        inner join comuna c on e.cod_com_rbd = c.cod_com_rbd
        inner join provincia p on c.cod_pro_rbd = p.cod_pro_rbd
        inner join region r on p.cod_reg_rbd = r.cod_reg_rbd
        where sel_25=1
        group by 1,2 order by agno
    )
select premio60.agno,premio60.nom_reg_rbd,(((premio100.cantidad_premiados)*1.0/(estabs)*1.0)*100) as proporcion_100,
       (((premio60.cantidad_premiados)*1.0/(estabs)*1.0)*100) as proporcion_60
from exr,premio60,premio100
where exr.nom_reg_rbd=premio60.nom_reg_rbd and exr.nom_reg_rbd=premio100.nom_reg_rbd and premio100.agno=premio60.agno
group by 1,2,3,4 order by premio60.nom_reg_rbd;
    """)

rows = cursor.fetchall()

prop=range(1,3)
agno=range(1,4)

r1_100=[]
r1_60=[]
r2_100=[]
r2_60=[]
r3_100=[]
r3_60=[]

for i in rows: 
  if i[1] == 'ANTOF':
    r1_100.append(i[2])
    r1_60.append(i[3])
  if i[1] == 'ARAUC':
    r2_100.append(i[2])
    r2_60.append(i[3])
  if i[1] == 'ATCMA':
    r3_100.append(i[2])
    r3_60.append(i[3])

fig, (ax1,ax2,ax3) = plt.subplots(nrows=1, ncols=3)

ax1.plot(agno, r1_100, label='100%',marker='o')
ax1.plot(agno, r1_60, label='60%',marker='o')

ax2.plot(agno, r2_100, label='100%', marker='o')
ax2.plot(agno, r2_60, label='60%', marker='o')

ax3.plot(agno, r3_100, label='100%', marker='o')
ax3.plot(agno, r3_60, label='60%', marker='o')

ax1.set_title('Antofagasta')
ax1.set_xlabel('agnos: 2015-2017,2019')
ax1.set_ylabel('matriculas')
ax1.legend()

ax2.set_title('Araucania')
ax2.set_xlabel('agnos: 2015-2017,2019')
ax2.set_ylabel('matriculas')
ax2.legend()

ax3.set_title('Atacama')
ax3.set_xlabel('agnos: 2015-2017,2019')
ax3.set_ylabel('matriculas')
ax3.legend()

plt.tight_layout()
plt.show()

cursor.close()
conn.close()