<jupyter_start><jupyter_text>Extract DANE<jupyter_code># Load packages
import pandas as pd
import numpy as np
SEDE_CARATULA = pd.read_csv("DataDANE/Carátula única sede educativa.csv", sep = ';' , encoding = "ISO-8859-1")


SEDE_PRINCIPAL = pd.read_csv("DataDANE/Sedes_principales_sector oficial.csv", sep = ';' , encoding = "ISO-8859-1")
SEDE_PLANTAS = pd.read_csv("DataDANE/Relación sedes principal y adscrita.csv", sep = ';' , encoding = "ISO-8859-1")
SEDE_ADSCRITA = pd.read_csv("DataDANE/Relación sede principal y adscritas (sector oficial).csv", sep = ';' , encoding = "ISO-8859-1")
GRUPOS_FLEX = pd.read_csv("DataDANE/Número de grupos en modelos educativos flexibles.csv", sep = ';' , encoding = "ISO-8859-1")
SEDE_MATRICULA = pd.read_csv("DataDANE/tipo de matricula propia o contratada, según nivel educativo por jornada.csv", sep = ';' , encoding = "ISO-8859-1")

TIC_CANT = pd.read_csv("DataDANE/Tenencia y número de equipos de cómputo por sede educativa.csv", sep = ';' , encoding = "ISO-8859-1")
TIC_SERVICIOS = pd.read_csv("DataDANE/Tenencia acceso.csv", sep = ';' , encoding = "ISO-8859-1")

SEDE_ADSCRITA

SEDE_ADSCRITA_P = SEDE_ADSCRITA[['ï»¿SEDE_CODIGO','SEDEADSC_CODIGO','SEDEADSC_NOMBRE','DEPTO','CODIGOINTERNOMUNI','MUNI','AREA_NOMBRE']]
SEDE_ADSCRITA_P
SEDE_PRINCIPAL
SEDE_ADSCRITA[SEDE_ADSCRITA['ï»¿SEDE_CODIGO'] == 205670000720]
SEDE_PRINCIPAL[SEDE_PRINCIPAL['SEDEPRINC_CODIGO'] == 205670000720]
#df_suffix = pd.merge(df1, df2, left_on='id',right_on='id',how='outer',suffixes=('_left','_right'))

#df_suffix = pd.merge(SEDE_ADSCRITA, SEDE_PRINCIPAL, left_on='ï»¿SEDE_CODIGO',right_on='SEDEPRINC_CODIGO',how='outer',suffixes=('_left','_right'))
print(SEDE_PRINCIPAL['ï»¿SEDE_CODIGO'].nunique())
print(SEDE_ADSCRITA['SEDEADSC_CODIGO'].nunique())
SEDE_PLANTAS
#SEDE_PLANTAS['ï»¿SEDE_CODIGO'].nunique()
#df_suffix = pd.merge(SEDE_PLANTAS, SEDE_PRINCIPAL, left_on='ï»¿SEDE_CODIGO',right_on='ï»¿SEDE_CODIGO',how='outer',suffixes=('_left','_right'))
#df_suffix
SEDE_CARATULA['MUNI']

SEDE_CARATULA_P=SEDE_CARATULA[['ï»¿SEDE_CODIGO','ESTADO_ID','NOVEDAD_NOMBRE','SEDE_NOMBRE','ES_SISTEMA_ETNOEDUCATIVO',
                               'CODIGOINTERNOMUNI','MUNI','AREA_NOMBRE','SECTOR_NOMBRE']]
SEDE_CARATULA.columns
SEDE_CARATULA_P
#df_suffix = pd.merge(SEDE_CARATULA_P, SEDE_ADSCRITA_P, left_on='ï»¿SEDE_CODIGO',right_on='SEDEADSC_CODIGO',how='outer',suffixes=('_left','_right'))
#df_suffix
SEDE_PRINCIPAL[SEDE_PRINCIPAL['ï»¿SEDE_CODIGO'] == 205670000720]
GRUPOS_FLEX.tail()
df_merge_col = pd.merge(GRUPOS_FLEX, SEDE_CARATULA_P[['ï»¿SEDE_CODIGO','MUNI']], on='ï»¿SEDE_CODIGO')
df_merge_col['MUNI'].unique()


GRUPOS_FLEX[GRUPOS_FLEX['MODELOEDUC_NOMBRE'] == "Ser humano"]
SEDE_MATRICULA.head()
df_merge_col = pd.merge(SEDE_MATRICULA, SEDE_CARATULA_P[['ï»¿SEDE_CODIGO','CODIGOINTERNOMUNI','MUNI']], on='ï»¿SEDE_CODIGO')
df_merge_col
df_merge_col[df_merge_col['JORNADA_CODIGO'] == 2].groupby('MUNI')[['JORMAT_CANTIDAD_ALUMNO']].sum()
df_merge_col[df_merge_col['JORNADA_CODIGO'] == 1].groupby('MUNI')[['JORMAT_CANTIDAD_ALUMNO']].sum()
df=df_merge_col.pivot_table(index='MUNI',columns='JORNADA_NOMBRE',aggfunc=sum, values='JORMAT_CANTIDAD_ALUMNO')
df
df.fillna(0,inplace=True)
#df.columns = df.columns.droplevel()
df.columns.name = None
df.reset_index(inplace=True)
df
df.rename(columns={'Completa':'SED_01',
                   'Mañana':'SED_02',
                   'Tarde':'SED_03',
                   'Nocturna':'SED_04',
                   'Fin de Semana':'SED_05',
                   'Única':'SED_06'},
                   inplace=True)
df
SED_07=df_merge_col.groupby('MUNI')[['JORNADA_NOMBRE']].nunique()
SED_07.fillna(0,inplace=True)
SED_07.columns.name = None
SED_07.reset_index(inplace=True)

SED_07
df = pd.merge(df, SED_07, on='MUNI')
df.rename(columns={'JORNADA_NOMBRE':'SED_07'},
                   inplace=True)
df
SED_08=df_merge_col.groupby('MUNI')[['JORMAT_CANTIDAD_ALUMNO']].sum()
SED_08.fillna(0,inplace=True)
SED_08.columns.name = None
SED_08.reset_index(inplace=True)

SED_08
df = pd.merge(df, SED_08, on='MUNI')
df.rename(columns={'JORMAT_CANTIDAD_ALUMNO':'SED_08'},
                   inplace=True)
df
SED_09=df_merge_col.pivot_table(index='MUNI',columns='NIVELENSE_CODIGO',aggfunc=sum, values='JORMAT_CANTIDAD_ALUMNO')
SED_09
SED_09.fillna(0,inplace=True)
SED_09.columns.name = None
SED_09.reset_index(inplace=True)
SED_09.rename(columns={1:'SED_09',
                       2:'SED_10',
                       3:'SED_11',
                       4:'SED_12',
                       5:'SED_13'},
                   inplace=True)
SED_09
df = pd.merge(df, SED_09, on='MUNI')
df
df_merge_col['TIPOMATRI_NOMBRE'].unique()
SED_14=df_merge_col[df_merge_col['TIPOMATRI_NOMBRE'] == "Matrícula contratada"].groupby('MUNI')[['JORMAT_CANTIDAD_ALUMNO']].sum()
SED_14.fillna(0,inplace=True)
SED_14.columns.name = None
SED_14.reset_index(inplace=True)

df = pd.merge(df, SED_14,how='left', on='MUNI')
df.rename(columns={'JORMAT_CANTIDAD_ALUMNO':'SED_14'},
                   inplace=True)
df
SED_15=df_merge_col[df_merge_col['TIPOMATRI_NOMBRE'] == "Matrícula propia"].groupby('MUNI')[['JORMAT_CANTIDAD_ALUMNO']].sum()
SED_15.fillna(0,inplace=True)
SED_15.columns.name = None
SED_15.reset_index(inplace=True)

df = pd.merge(df, SED_15,how='left',on='MUNI')
df.rename(columns={'JORMAT_CANTIDAD_ALUMNO':'SED_15'},
                   inplace=True)
df
df
df['SED_14'] = (df['SED_14'] / df['SED_08'])
df['SED_15'] = (df['SED_15'] / df['SED_08'])
df
TIC_CANT
TIC_CANT_merge = pd.merge(TIC_CANT, SEDE_CARATULA_P[['ï»¿SEDE_CODIGO','CODIGOINTERNOMUNI','MUNI']], on='ï»¿SEDE_CODIGO')
TIC_CANT_merge
TIC_CANT_merge['EQUIPOCOM_NOMBRE'].unique()
TIC_CANT_merge_1 = TIC_CANT_merge[(TIC_CANT_merge['EQUIPOCOM_NOMBRE'] == 'Computadores de escritorio') | 
               (TIC_CANT_merge['EQUIPOCOM_NOMBRE'] == 'Computadores portátiles')]
TIC_CANT_merge_1
TIC_CANT_merge_2 = TIC_CANT_merge[(TIC_CANT_merge['EQUIPOCOM_NOMBRE'] == 'Tabletas')]
TIC_CANT_merge_2 
df_merge_col_1 = df_merge_col.groupby(['ï»¿SEDE_CODIGO','MUNI'])[['JORMAT_CANTIDAD_ALUMNO']].sum()
#SED_15.fillna(0,inplace=True)
df_merge_col_1.columns.name = None
df_merge_col_1.reset_index(inplace=True)
df_merge_col_1
TIC_CANT_merge_1_1 = TIC_CANT_merge_1.groupby(['ï»¿SEDE_CODIGO','MUNI'])[['SEDECOM_CANTIDAD']].sum()
TIC_CANT_merge_1_1
TIC_CANT_merge_1_1 = pd.merge(TIC_CANT_merge_1_1, df_merge_col_1, on='ï»¿SEDE_CODIGO')
TIC_CANT_merge_1_1
#TIC_CANT_merge_1_1['TIC_01']= TIC_CANT_merge_1_1['SEDECOM_CANTIDAD'] / TIC_CANT_merge_1_1['JORMAT_CANTIDAD_ALUMNO'] 
TIC_CANT_merge_1_1['TIC_01']= (TIC_CANT_merge_1_1['SEDECOM_CANTIDAD']) / (TIC_CANT_merge_1_1['JORMAT_CANTIDAD_ALUMNO'])
TIC_CANT_merge_1_1
TIC_01 = TIC_CANT_merge_1_1.groupby('MUNI')[['TIC_01']].mean()
TIC_01.columns.name = None
TIC_01.reset_index(inplace=True)
TIC_01
df = pd.merge(df, TIC_01, how='left',on='MUNI')

df
TIC_CANT_merge_2_1 = TIC_CANT_merge_2.groupby(['ï»¿SEDE_CODIGO','MUNI'])[['SEDECOM_CANTIDAD']].sum()
TIC_CANT_merge_2_1
TIC_CANT_merge_2_1 = pd.merge(TIC_CANT_merge_2_1, df_merge_col_1, on='ï»¿SEDE_CODIGO')
TIC_CANT_merge_2_1
TIC_CANT_merge_2_1['TIC_02']= (TIC_CANT_merge_2_1['SEDECOM_CANTIDAD']) / (TIC_CANT_merge_2_1['JORMAT_CANTIDAD_ALUMNO'])
TIC_CANT_merge_2_1
TIC_02 = TIC_CANT_merge_2_1.groupby('MUNI')[['TIC_02']].mean()
TIC_02.columns.name = None
TIC_02.reset_index(inplace=True)
TIC_02
df = pd.merge(df, TIC_02,how='left', on='MUNI')

df
import missingno as msno 
TIC_SERVICIOS

msno.matrix(TIC_SERVICIOS) 
TIC_SERVICIOS_merge = pd.merge(TIC_SERVICIOS, SEDE_CARATULA_P[['ï»¿SEDE_CODIGO','CODIGOINTERNOMUNI','MUNI']], on='ï»¿SEDE_CODIGO')
TIC_SERVICIOS_merge
#TIC_03 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_ELECTRICIDAD'] == "1"].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
#TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_ELECTRICIDAD'] == 1].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_SERVICIOS_Total = TIC_SERVICIOS_merge.groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
#TIC_SERVICIOS_Total.fillna(0,inplace=True)
TIC_SERVICIOS_Total.columns.name = None
TIC_SERVICIOS_Total.reset_index(inplace=True)
TIC_SERVICIOS_Total.rename(columns={'ï»¿SEDE_CODIGO':'total'},
                   inplace=True)
TIC_SERVICIOS_Total
TIC_03_1 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_ELECTRICIDAD'] == 1].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_03_1.fillna(0,inplace=True)
TIC_03_1.columns.name = None
TIC_03_1.reset_index(inplace=True)
TIC_03_1.rename(columns={'ï»¿SEDE_CODIGO':'TIC_03_1'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_03_1,how='left', on='MUNI')

TIC_03_0 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_ELECTRICIDAD'] == 0].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()

TIC_03_0.columns.name = None
TIC_03_0.reset_index(inplace=True)
TIC_03_0.rename(columns={'ï»¿SEDE_CODIGO':'TIC_03_0'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_03_0,how='left', on='MUNI')
#TIC_SERVICIOS_Total.fillna(0,inplace=True)

TIC_SERVICIOS_Total

TIC_04_1 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_TELEVISION'] == 1].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_04_1.fillna(0,inplace=True)
TIC_04_1.columns.name = None
TIC_04_1.reset_index(inplace=True)
TIC_04_1.rename(columns={'ï»¿SEDE_CODIGO':'TIC_04_1'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_04_1,how='left', on='MUNI')

TIC_04_0 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_TELEVISION'] == 0].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()

TIC_04_0.columns.name = None
TIC_04_0.reset_index(inplace=True)
TIC_04_0.rename(columns={'ï»¿SEDE_CODIGO':'TIC_04_0'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_04_0,how='left', on='MUNI')
TIC_SERVICIOS_Total.fillna(0,inplace=True)

TIC_SERVICIOS_Total
TIC_05_1 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_LINEA_TEL'] == 1].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_05_1.fillna(0,inplace=True)
TIC_05_1.columns.name = None
TIC_05_1.reset_index(inplace=True)
TIC_05_1.rename(columns={'ï»¿SEDE_CODIGO':'TIC_05_1'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_05_1,how='left', on='MUNI')

TIC_05_0 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_LINEA_TEL'] == 0].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()

TIC_05_0.columns.name = None
TIC_05_0.reset_index(inplace=True)
TIC_05_0.rename(columns={'ï»¿SEDE_CODIGO':'TIC_05_0'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_05_0,how='left', on='MUNI')
TIC_SERVICIOS_Total.fillna(0,inplace=True)

TIC_SERVICIOS_Total
TIC_06_1 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_RADIO'] == 1].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_06_1.columns.name = None
TIC_06_1.reset_index(inplace=True)
TIC_06_1.rename(columns={'ï»¿SEDE_CODIGO':'TIC_06_1'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_06_1,how='left', on='MUNI')

TIC_06_0 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_RADIO'] == 0].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_06_0.columns.name = None
TIC_06_0.reset_index(inplace=True)
TIC_06_0.rename(columns={'ï»¿SEDE_CODIGO':'TIC_06_0'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_06_0,how='left', on='MUNI')
TIC_SERVICIOS_Total.fillna(0,inplace=True)

TIC_SERVICIOS_Total
TIC_07_1 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_LAN'] == 1].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_07_1.columns.name = None
TIC_07_1.reset_index(inplace=True)
TIC_07_1.rename(columns={'ï»¿SEDE_CODIGO':'TIC_07_1'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_07_1,how='left', on='MUNI')

TIC_07_0 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_LAN'] == 0].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_07_0.columns.name = None
TIC_07_0.reset_index(inplace=True)
TIC_07_0.rename(columns={'ï»¿SEDE_CODIGO':'TIC_07_0'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_07_0,how='left', on='MUNI')
TIC_SERVICIOS_Total.fillna(0,inplace=True)

TIC_SERVICIOS_Total
TIC_08_1 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_INTERNET'] == 1].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_08_1.columns.name = None
TIC_08_1.reset_index(inplace=True)
TIC_08_1.rename(columns={'ï»¿SEDE_CODIGO':'TIC_08_1'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_08_1,how='left', on='MUNI')

TIC_08_0 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_INTERNET'] == 0].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_08_0.columns.name = None
TIC_08_0.reset_index(inplace=True)
TIC_08_0.rename(columns={'ï»¿SEDE_CODIGO':'TIC_08_0'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_08_0,how='left', on='MUNI')
TIC_SERVICIOS_Total.fillna(0,inplace=True)

TIC_SERVICIOS_Total
TIC_10_1 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_EQUIPO_COMPUTO'] == 1].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_10_1.columns.name = None
TIC_10_1.reset_index(inplace=True)
TIC_10_1.rename(columns={'ï»¿SEDE_CODIGO':'TIC_10_1'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_10_1,how='left', on='MUNI')

TIC_10_0 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_EQUIPO_COMPUTO'] == 0].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_10_0.columns.name = None
TIC_10_0.reset_index(inplace=True)
TIC_10_0.rename(columns={'ï»¿SEDE_CODIGO':'TIC_10_0'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_10_0,how='left', on='MUNI')
TIC_SERVICIOS_Total.fillna(0,inplace=True)

TIC_SERVICIOS_Total
TIC_11_1 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_SERVICIO_EXTERNO'] == 1].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_11_1.columns.name = None
TIC_11_1.reset_index(inplace=True)
TIC_11_1.rename(columns={'ï»¿SEDE_CODIGO':'TIC_11_1'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_11_1,how='left', on='MUNI')

TIC_11_0 = TIC_SERVICIOS_merge[TIC_SERVICIOS_merge['SEDETE_SERVICIO_EXTERNO'] == 0].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
TIC_11_0.columns.name = None
TIC_11_0.reset_index(inplace=True)
TIC_11_0.rename(columns={'ï»¿SEDE_CODIGO':'TIC_11_0'},inplace=True)
TIC_SERVICIOS_Total = pd.merge(TIC_SERVICIOS_Total, TIC_11_0,how='left', on='MUNI')
TIC_SERVICIOS_Total.fillna(0,inplace=True)

TIC_SERVICIOS_Total
msno.matrix(TIC_SERVICIOS_Total) 
TIC_SERVICIOS_Total = TIC_SERVICIOS_Total.drop(['total'], axis=1)
TIC_SERVICIOS_Total
df = pd.merge(df, TIC_SERVICIOS_Total,how='left', on='MUNI')

df
SEDE_PRINCIPAL.groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()


SEDE_CARATULA
SED_16 = SEDE_CARATULA[SEDE_CARATULA['NOVEDAD_NOMBRE'] == "Rinde"].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
SED_16.fillna(0,inplace=True)
SED_16.columns.name = None
SED_16.reset_index(inplace=True)
SED_16.rename(columns={'ï»¿SEDE_CODIGO':'SED_16'},
                   inplace=True)
df = pd.merge(df, SED_16,how='left', on='MUNI')

df
SEDE_CARATULA['AREA_NOMBRE'].unique()
SEDE_CARATULA_RINDE = SEDE_CARATULA[SEDE_CARATULA['NOVEDAD_NOMBRE'] == "Rinde"]

SED_17 = SEDE_CARATULA_RINDE[SEDE_CARATULA_RINDE['AREA_NOMBRE'] == "Urbana"].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
SED_17.fillna(0,inplace=True)
SED_17.columns.name = None
SED_17.reset_index(inplace=True)
SED_17.rename(columns={'ï»¿SEDE_CODIGO':'SED_17'},
                   inplace=True)
df = pd.merge(df, SED_17,how='left', on='MUNI')

df
SED_18 = SEDE_CARATULA_RINDE[SEDE_CARATULA_RINDE['AREA_NOMBRE'] == "Rural"].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
SED_18.fillna(0,inplace=True)
SED_18.columns.name = None
SED_18.reset_index(inplace=True)
SED_18.rename(columns={'ï»¿SEDE_CODIGO':'SED_18'},
                   inplace=True)
df = pd.merge(df, SED_18,how='left', on='MUNI')

df
df['SED_19']= df['SED_17'] / df['SED_16']
df
SED_20 = SEDE_CARATULA_RINDE[SEDE_CARATULA_RINDE['SEDE_SISTEMA_ETNOEDUCATIVO'] == "1"].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
SED_20.fillna(0,inplace=True)
SED_20.columns.name = None
SED_20.reset_index(inplace=True)
SED_20.rename(columns={'ï»¿SEDE_CODIGO':'SED_20'},
                   inplace=True)
df = pd.merge(df, SED_20, how='left',on='MUNI')

df
df['SED_21']= df['SED_20'] / df['SED_16']
df
SEDE_PLANTAS
SEDE_PLANTAS_merge = pd.merge(SEDE_PLANTAS, SEDE_CARATULA_P[['ï»¿SEDE_CODIGO','CODIGOINTERNOMUNI','MUNI']], on='ï»¿SEDE_CODIGO')
SEDE_PLANTAS_merge

SED_23=SEDE_PLANTAS_merge[SEDE_PLANTAS_merge['TIPO'] == "Adscrita"].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
SED_23.fillna(0,inplace=True)
SED_23.columns.name = None
SED_23.reset_index(inplace=True)
SED_23.rename(columns={'ï»¿SEDE_CODIGO':'SED_23'},
                   inplace=True)
df = pd.merge(df, SED_23,how='left', on='MUNI')

df
SED_24=SEDE_PLANTAS_merge[SEDE_PLANTAS_merge['TIPO'] == "Principal"].groupby('MUNI')[['ï»¿SEDE_CODIGO']].nunique()
SED_24.fillna(0,inplace=True)
SED_24.columns.name = None
SED_24.reset_index(inplace=True)
SED_24.rename(columns={'ï»¿SEDE_CODIGO':'SED_24'},
                   inplace=True)
df = pd.merge(df, SED_24,how='left', on='MUNI')

df

#df = pd.merge(df, SEDE_CARATULA_P[['CODIGOINTERNOMUNI','MUNI']],how='inner',on='MUNI')
#df
SEDE_MUNICIPIO=SEDE_CARATULA[['CODIGOINTERNOMUNI','MUNI']].copy()
SEDE_MUNICIPIO.drop_duplicates(keep='first', inplace=True)
SEDE_MUNICIPIO
df = pd.merge(df,SEDE_MUNICIPIO, how='left',on='MUNI')
df
cols = df.columns.tolist()
cols
df=df[['CODIGOINTERNOMUNI',
       'MUNI',
         'SED_01',
         'SED_05',
         'SED_02',
         'SED_04',
         'SED_03',
         'SED_06',
         'SED_07',
         'SED_08',
         'SED_09',
         'SED_10',
         'SED_11',
         'SED_12',
         'SED_13',
         'SED_14',
         'SED_15',
         'TIC_01',
         'TIC_02',
         'TIC_03_1',
         'TIC_03_0',
         'TIC_04_1',
         'TIC_04_0',
         'TIC_05_1',
         'TIC_05_0',
         'TIC_06_1',
         'TIC_06_0',
         'TIC_07_1',
         'TIC_07_0',
         'TIC_08_1',
         'TIC_08_0',
         'TIC_10_1',
         'TIC_10_0',
         'TIC_11_1',
         'TIC_11_0',
         'SED_16',
         'SED_17',
         'SED_18',
         'SED_19',
         'SED_20',
         'SED_21',
         'SED_23',
         'SED_24']]
df
df.to_csv('VariablesCarlos.csv',  sep='\t' , encoding = "ISO-8859-1")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1694552635909814, 'fraction_numerical': 0.04498326841845411, 'mean_word_length': 7.400921658986175, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '431081', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 9320, 'n_tokens_neox': 9060, 'n_words': 721, 'path': 'notebooks/DANE Data Cleaning.ipynb', 'repo_name': 'ljparrag/ds4ateam67dropout', 'size': '18229'}