def get_tablePK(table, cursor):
cursor.execute("SELECT * FROM " + table)
df1 = uniFormatTable(pd.DataFrame(cursor.fetchall()))
df2 = pd.read_csv(nfv.getPath(finalpath, table + ".csv"),
float_precision="high")
df2 = uniFormatDF(df2)
df1 = np.array(df1)
pk = np.array([])
for index, row in df2.iterrows():
if (serviceTable(table) == False):
for row2 in df1:
if ((np.equal(np.array(row), np.array(row2[1:]))).all()):
pk = np.append(pk, row2[0])
else:
if (isEducationalCenter(table)):
for row2 in df1:
if ((np.equal(np.array(row[:-2]), row2[1:-3])).all()):
pk = np.append(pk, row2[0])
else:
if (isOtherCenter(table) == False):
for row2 in df1:
if ((np.equal(np.array(row[:-1]), row2[1:-2])).all()):
pk = np.append(pk, row2[0])
else:
for row2 in df1:
if ((np.equal(np.array(row[:-1]), row2[1:-1])).all()):
pk = np.append(pk, row2[0])
return pd.DataFrame(pk)
def makeNMtable(elem, cursor):
x = nm.getTableName(elem)
if (nm.is_non_zero_file(nfv.getPath(finalpath, x + ".csv"))):
if (nm.specialTable(elem) == False):
tablePK = nm.get_tablePK(x, cursor)
communityPK = nm.get_communityPK(elem, cursor)
arrayCommunity = np.array([])
for index, row in tablePK.iterrows():
arrayCommunity = np.append(arrayCommunity, communityPK[0][0])
nmTableFK = nt.concatDF(tablePK, pd.DataFrame(arrayCommunity))
if (os.path.isfile(finalpath + "/" + elem + ".csv")):
if (nm.is_non_zero_file(nfv.getPath(finalpath,
elem + ".csv"))):
df = np.array(pd.read_csv(finalpath + "/" + elem + ".csv"))
df = pd.DataFrame(df)
nmTableFK = nt.concatDF(nmTableFK, df)
else:
nmTableFK = nm.get_specialTable(x, elem, cursor)
nt.mkCSV(nmTableFK, elem + ".csv")
def NMsqlBody(communityType, tablesList, f, cursor, query):
otherTable = np.array([])
for row in tablesList:
for elem in row:
if ((communityType == 0 and elem.find("_has_camp") == -1)
or (communityType == 1)):
if (nmw.NMTable(elem)):
x = nmt.getTableName(elem)
if (nmt.is_non_zero_file(nfv.getPath(
finalpath, x + ".csv"))):
if (nmw.specialTable(elem) == False):
f.write(query.getQuery1() + elem + ".csv'\n" +
query.getQuery2() + " " + elem + "\n" +
query.getQuery3() + "\n" +
query.getQuery4() + "\n")
cursor.execute("SHOW columns FROM " + elem)
columnList = cursor.fetchall()
string = np.array([], dtype=str)
for column in columnList:
if (nmw.validColumn(column)):
string = np.append(string, column[0])
f.write(" (")
for column in string:
if (column != string[-1]):
f.write("@" + column + ",")
else:
f.write("@" + column + ")\n")
f.write("SET ")
for column in string:
if (column != string[-1]):
if (column == string[0]):
f.write(column + " = NULLIF(@" +
column + ",''),\n")
else:
f.write(" " + column +
" = NULLIF(@" + column +
",''),\n")
else:
if (column == string[0]):
f.write(column + " = NULLIF(@" +
column + ",'');\n\n")
else:
f.write(" " + column +
" = NULLIF(@" + column +
",'');\n\n")
else:
otherTable = np.append(otherTable, elem)
for elem in otherTable:
if (elem == "inf_expandplanbeneficiaries_has_inf_energyinfrastructure"
):
nmw.writteExpanPlan(query, f, cursor, communityType)
def get_specialTableFKs(table, tableHas, x, y, cursor):
cursor.execute("SELECT * FROM " + table)
df1 = uniFormatTable(pd.DataFrame(cursor.fetchall()))
df2 = pd.read_csv(nfv.getPath(finalpath, tableHas + ".csv"),
header=None,
float_precision="high")
df2 = uniFormatDF(df2)
df1 = np.array(df1)
pk = np.array([])
arrayCommunity = np.array([])
communityPK = get_communityPK(tableHas, cursor)
for index, row in df2.iterrows():
for row2 in df1:
if (row[x] == row2[1]):
pk = np.append(pk, row2[0])
pk = pd.DataFrame(pk)
for index, row in pk.iterrows():
arrayCommunity = np.append(arrayCommunity, communityPK[0][0])
arrayCommunity = pd.DataFrame(arrayCommunity)
result = nt.concatDF(pk, nt.concatDF(arrayCommunity, df2))
result = result.drop(result.columns[[y]], axis=1)
return result
def __init__(self, communityType):
Bibliography = pd.read_excel(
nfv.getPath(nfv.mainpath, "Bibliography_120220.xlsx"))
Bibliography = nfv.fixBibliography(Bibliography)
self.Bibliography = nfv.setDataByIndex(Bibliography, communityType)
self.Entities = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath, "NAUTIA_1_0_Entities_Interview_results.csv"),
float_precision="high"), communityType)
self.LocalLeaders = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(nfv.mainpath,
"NAUTIA_1_0_Local_leaders_v3_results.csv"),
float_precision="high"), communityType)
self.HouseHold = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath, "NAUTIA_1_0_Survey_household_v6_results.csv"),
float_precision="high"), communityType)
self.WomenGroup = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath, "NAUTIA_1_0_Women_Focus_Group2_results.csv"),
float_precision="high"), communityType)
self.SanitationInfra = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath,
"NAUTIA_V1_0_Sanitation_Infrastructre_results.csv"),
float_precision="high"), communityType)
self.Priorities = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(nfv.mainpath,
"NAUTIA_1_0_Priorities_v3_results.csv"),
float_precision="high"), communityType)
self.GeneralForm = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(nfv.mainpath,
"NAUTIA_1_0_General_form_v3_results.csv"),
float_precision="high"), communityType)
self.PublicSpace = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(nfv.mainpath,
"NAUTIA_1_0_Public_Space_results.csv"),
float_precision="high"), communityType)
self.WaterInf = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath, "NAUTIA_1_0_Water_Infrastructure_results.csv"),
float_precision="high"), communityType)
self.SanitationInf = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath,
"NAUTIA_V1_0_Sanitation_Infrastructre_results.csv"),
float_precision="high"), communityType)
self.WasteManagementInf = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath,
"NAUTIA_1_0_Waste_Management_Infrastructure_results.csv"),
float_precision="high"), communityType)
self.EnergyINF = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath, "NAUTIA_1_0_Energy_Infrastructure_results.csv"),
float_precision="high"), communityType)
self.Business = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath, "NAUTIA1_0_Business_surveys_v3_results.csv"),
float_precision="high"), communityType)
self.MobilityINF = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath,
"NAUTIA_1_0__Transport_servicesaccess_points_results.csv"),
float_precision="high"), communityType)
self.ComunalServices = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath, "NAUTIA_1_0_Communal_Services_results.csv"),
float_precision="high"), communityType)
self.GeneralCitizen = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath,
"NAUTIA_1_0_General_Citizen_Focus_Group_results.csv"),
float_precision="high"), communityType)
self.Shelter = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(nfv.mainpath,
"NAUTIA_1_0_Shelter_results.csv"),
float_precision="high"), communityType)
self.FarmyardCrop = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(
nfv.mainpath, "NAUTIA_1_0_Farmyard_and_Crops_results.csv"),
float_precision="high"), communityType)
def generalData(Bibliography):
df1 = nfv.dfFix(Bibliography, "Mujeres menores de 5 años (%)",
"Total population")
df2 = nfv.dfFix(Bibliography, "Growth rate of populatoin (%)", "Culture")
GD_Demography = nt.concatDF(df1, df2)
nt.mkCSV(GD_Demography, "GD_Demography.csv")
GD_Ethnicgroup = nfv.dfFix(Bibliography, "Ethnich group 1", "Religion").T
nt.mkCSV(GD_Ethnicgroup, "GD_Ethnicgroup.csv")
df1 = nfv.dfFix(Bibliography, "Parliamentary republic",
"Territorial and Urbanistic")
GD_Government = df1
GD_Government = GD_Government.isin(["Si"])
GD_Government = GD_Government.any(
) #Lista con indice de columna y True si un contiene un True o False en caso contrario
GD_Government = list(
GD_Government[GD_Government == True].index) #lista de indices con true
GD_Government = pd.DataFrame(GD_Government)
nt.mkCSV(GD_Government, "GD_Government.csv")
GD_Economy = nfv.dfFix(Bibliography, "Agriculture (%)", "Government")
nt.mkCSV(GD_Economy, "GD_Economy.csv")
df1 = nfv.dfFix(Bibliography, "Urban population (%)", "Population density")
df2 = nfv.dfFix(Bibliography, "Urban (inhabitants/hectares)",
"Infrastructures")
GD_Urbanism = nt.concatDF(df1, df2)
nt.mkCSV(GD_Urbanism, "GD_Urbanism.csv")
df1 = nfv.dfFix(Bibliography, "Rural agua (%)",
"Access to improved sanitation")
df2 = nfv.dfFix(Bibliography, "Rural saneamiento(%)",
"Access to electricity")
df3 = nfv.dfFix(Bibliography, "Rural electricidad (%)",
"Matrix of electricity generation")
GD_Infrastructure = nt.concatDF(nt.concatDF(df1, df2), df3)
nt.mkCSV(GD_Infrastructure, "GD_Infrastructure.csv")
GD_ElectricGenerationMix = nfv.dfFix(Bibliography, "Hydropower (%)",
"High voltage (kV)")
nt.mkCSV(GD_ElectricGenerationMix, "GD_ElectricGenerationMix.csv")
GD_ServiceAccess = nfv.dfFix(Bibliography, "Illiteracy rate (%)",
"Shelter")
nt.mkCSV(GD_ServiceAccess, "GD_ServiceAccess.csv")
GD_Shelter = nfv.dfFix(Bibliography, "Slum population rate (%)",
"SPECIFIC INFORMATION - SETTLEMENTS LEVEL")
nt.mkCSV(GD_Shelter, "GD_Shelter.csv")
Comun = pd.read_excel(nfv.getPath(nt.mainpath, "Bibliography_120220.xlsx"))
Comun = nfv.fixBibliography(Comun)
GD_Religion = nfv.dfFix(Comun, "Religion 1", "Language")
df1 = nfv.dropRow(GD_Religion, 1)
np_array1 = np.array(df1)
df2 = nfv.dropRow(GD_Religion, 0)
np_array2 = np.array(df2)
np_array3 = np.concatenate((np_array1, np_array2), axis=1)
GD_Religion = pd.DataFrame(np_array3)
GD_Religion = GD_Religion.transpose()
GD_Religion = GD_Religion[0].unique()
GD_Religion = pd.DataFrame(GD_Religion)
GD_Religion = GD_Religion.dropna()
nt.mkCSV(GD_Religion, "GD_Religion.csv")
GD_Language = nfv.dfFix(Comun, "Language 1", "Economy and well-being")
df1 = nfv.dropRow(GD_Language, 1)
np_array1 = np.array(df1)
df2 = nfv.dropRow(GD_Language, 0)
np_array2 = np.array(df2)
np_array3 = np.concatenate((np_array1, np_array2), axis=1)
GD_Language = pd.DataFrame(np_array3)
GD_Language = GD_Language.transpose()
GD_Language = GD_Language[0].unique()
GD_Language = pd.DataFrame(GD_Language)
GD_Language = GD_Language.dropna()
nt.mkCSV(GD_Language, "GD_Language.csv")
def writteExpanPlan(query, f, cursor, communityType):
Entities = nfv.setDataByIndex(
pd.read_csv(nfv.getPath(mainpath,
"NAUTIA_1_0_Entities_Interview_results.csv"),
float_precision="high"), communityType)
inf_expandplanbeneficiaries = nfv.dfFix(Entities,
"ENERGY:Covered_services",
"ENERGY:Power_failure")
inf_expandplanbeneficiaries = nt.separateValues(
inf_expandplanbeneficiaries)
cursor.execute("SELECT * FROM inf_expandplanbeneficiaries")
array = np.array(cursor.fetchall())
pk = np.array([])
for row in array:
for row2 in np.array(inf_expandplanbeneficiaries):
for elem in row2:
if (row[1] == elem):
pk = np.append(pk, elem)
cursor.execute(
"SELECT idINF_EnergyInfrastructure FROM INF_EnergyInfrastructure ORDER BY idINF_EnergyInfrastructure DESC LIMIT 1"
)
pkInf = np.array(cursor.fetchall())
cursor.execute(
"SELECT Community_idCommunity FROM INF_EnergyInfrastructure ORDER BY Community_idCommunity DESC LIMIT 1"
)
fkInf = np.array(cursor.fetchall())
v1 = v2 = np.array([])
for elem in pk:
v1 = np.append(v1, pkInf)
v2 = np.append(v2, fkInf)
result = nt.concatDF(pd.DataFrame(pk),
nt.concatDF(pd.DataFrame(v1), pd.DataFrame(v2)))
nt.mkCSV(result,
"inf_expandplanbeneficiaries_has_inf_energyinfrastructure.csv")
cursor.execute(
"SHOW columns FROM inf_expandplanbeneficiaries_has_inf_energyinfrastructure"
)
columnList = cursor.fetchall()
table = "inf_expandplanbeneficiaries_has_inf_energyinfrastructure"
f.write(query.getquery1() + table + ".csv'\n" + query.getquery2() + " " +
table + "\n" + query.getquery3() + "\n" + query.getquery4() + "\n")
pk = True
string = np.array([], dtype=str)
for column in columnList:
if (pk):
pk = False
else:
string = np.append(string, column[0])
f.write(" (")
for column in string:
if (column != string[-1]):
f.write("@" + column + ",")
else:
f.write("@" + column + ")\n")
f.write("SET ")
for column in string:
if (column != string[-1]):
if (column == string[0]):
f.write(column + " = NULLIF(@" + column + ",''),\n")
else:
f.write(" " + column + " = NULLIF(@" + column + ",''),\n")
else:
if (column == string[0]):
f.write(column + " = NULLIF(@" + column + ",'');\n\n")
else:
f.write(" " + column + " = NULLIF(@" + column + ",'');\n\n")
GD_Infrastructure = nt.concatDF(nt.concatDF(df1, df2), df3)
nt.mkCSV(GD_Infrastructure, "GD_Infrastructure.csv")
GD_ElectricGenerationMix = nfv.dfFix(Bibliography, "Hydropower (%)",
"High voltage (kV)")
nt.mkCSV(GD_ElectricGenerationMix, "GD_ElectricGenerationMix.csv")
GD_ServiceAccess = nfv.dfFix(Bibliography, "Illiteracy rate (%)", "Shelter")
nt.mkCSV(GD_ServiceAccess, "GD_ServiceAccess.csv")
GD_Shelter = nfv.dfFix(Bibliography, "Slum population rate (%)",
"SPECIFIC INFORMATION - SETTLEMENTS LEVEL")
nt.mkCSV(GD_Shelter, "GD_Shelter.csv")
#%%COMMUN DATA
Comun = pd.read_excel(nfv.getPath(nt.mainpath, "Bibliography_120220.xlsx"))
Comun = nfv.fixBibliography(Comun)
GD_Religion = nfv.dfFix(Comun, "Religion 1", "Language")
df1 = nfv.dfFix(GD_Religion, 1)
np_array1 = np.array(df1)
df2 = nfv.dfFix(GD_Religion, 0)
np_array2 = np.array(df2)
np_array3 = np.concatenate((np_array1, np_array2), axis=1)
GD_Religion = pd.DataFrame(np_array3)
GD_Religion = GD_Religion.transpose()
GD_Religion = GD_Religion[0].unique()
GD_Religion = pd.DataFrame(GD_Religion)
GD_Religion = GD_Religion.dropna()
nt.mkCSV(GD_Religion, "GD_Religion.csv")