def validateparameter(parameterid,alterations,columns): data = db.selectallfrom("data") datadf = data[data['isActive'] == 1] pardf = datadf[datadf['fk_parameterid'] == parameterid] valrules = db.selectallfrom("validationrule") temprules = db.selectfromwhere("fk_parameterid,fk_validationruleid","validationruleparameter_map", "fk_parameterid = ?",(int(parameterid),)) valruleids = temprules['fk_validationruleid'].tolist() validationrules = valrules[valrules['validationruleid'].isin(valruleids)] for i in range(len(validationrules)): temprule = validationrules['rule'].iloc[i] for j in range(len(pardf)): tempRow = pardf.iloc[j] value = tempRow['value'] dataid = tempRow['dataid'] orgid = tempRow['fk_organisationid'] tempdf = datadf[datadf['fk_organisationid'] == orgid] result = valparse.check(temprule,value,tempdf) if result == False: row = pd.DataFrame([[int(dataid),temprule,value,parameterid]],columns = columns) alterations = alterations.append(row) return alterations
def newProject(projectName, projectYear, aggMethodName, aggMethodDesc): methodid = createNewMethod(aggMethodName, aggMethodDesc) users = db.selectallfrom("user") userid = users['userid'].iloc[0] db.insertvaluessingle( "project(name,fk_userid,fk_methodid,currentPhase,currentYear,currentProject)", "(?,?,?,?,?,?)", (projectName, int(userid), int(methodid), int(1), projectYear, 0)) projects = db.selectallfrom('project') currentProject = projects[projects['name'] == projectName] projectid = currentProject['projectid'].iloc[len(currentProject) - 1] changeCurrentProject(projectid) templates = db.selectallfrom("template") tempTable = templates[templates['vistype'] == "table"]['templateDict'].iloc[0] tempCat = templates[templates['vistype'] == "categories"]['templateDict'].iloc[0] tempBar = templates[templates['vistype'] == "barchart"]['templateDict'].iloc[0] db.insertvaluessingle("template(vistype,templateDict,fk_projectid)", "(?,?,?)", ("table", tempTable, int(projectid))) db.insertvaluessingle("template(vistype,templateDict,fk_projectid)", "(?,?,?)", ("categories", tempCat, int(projectid))) db.insertvaluessingle("template(vistype,templateDict,fk_projectid)", "(?,?,?)", ("barchart", tempBar, int(projectid)))
def write(): st.title("Validation rules") st.write("Add validation rule") options = get_indicators() indicator_selected = st.selectbox("Select Indicator", options=options) realid = indicator_selected[0] currentNaNmethoddf = db.selectfromwhere("realid,nanmethod","parameter", "realid = ?",(realid,)) currentNaNMethod = currentNaNmethoddf['nanmethod'].iloc[0] st.write("Current way to deal with empty values: " + nanMethods[currentNaNMethod]) #if st.button("Update NaN-method"): NaNoptions = ["None selected","Convert to 0", "Convert to no", "Set empty values to inactive"] nanMethod = st.selectbox("Update NaN-method",options = NaNoptions) if st.button("Update method"): if nanMethod == "None selected": db.updatevalues("parameter", "nanmethod = ?", "realid = ?",(0,realid)) elif nanMethod == "Convert to 0": db.updatevalues("parameter", "nanmethod = ?", "realid = ?",(1,realid)) elif nanMethod == "Convert to no": db.updatevalues("parameter", "nanmethod = ?", "realid = ?",(2,realid)) else: db.updatevalues("parameter", "nanmethod = ?", "realid = ?",(3,realid)) indicators = db.selectallfrom("indicator") parameters = db.selectallfrom("parameter") parameters = parameters[parameters["fk_methodid"] == currentMethod] questions = db.selectallfrom("question") valrules = db.selectallfrom("validationrule") valrules = valrules[valrules["fk_methodid"] == currentMethod] parid = parameters[parameters["realid"] == realid]['parameterid'].iloc[0] #ind = questions[questions['realid'] == realid] #parid = questions[questions['fk_parameterid'] == par].iloc[0] #print(parid) temprules = db.selectfromwhere("fk_parameterid,fk_validationruleid","validationruleparameter_map", "fk_parameterid = ?",(int(parid),)) valruleids = temprules['fk_validationruleid'].tolist() validationrules = valrules[valrules['validationruleid'].isin(valruleids)] st.write("Other validation rules: ") rows = selectable_data_table(validationrules[['rule','description']]) if st.button("Delete selected"): for i in rows: db.deletevalue("validationruleparameter_map","fk_parameterid = ? and fk_validationruleid = ?", (int(parid),int(validationrules['validationruleid'].iloc[i]))) newRule = st.text_input("New rule") description = st.text_input("Description") if st.button("Save rule"): db.insertvaluessingle("validationrule(rule,description,fk_methodid)","(?,?,?)",(newRule,description,int(currentMethod))) justAddedrule = db.selectfromwhere("rule,validationruleid","validationrule","rule = ?",(newRule,)) ruleid = justAddedrule['validationruleid'].iloc[0] db.insertvaluessingle("validationruleparameter_map(fk_parameterid,fk_validationruleid)","(?,?)",(int(parid),int(ruleid)))
def write(): st.write("Welcome to the indicator computation page") st.write("The indicators you have added: ") parameters = db.selectallfrom("parameter") parameters = parameters[parameters["fk_methodid"] == currentMethod] parameterlist = parameters["parameterid"].values indicators = db.selectallfrom("indicator") indicators = indicators[indicators["fk_parameterid"].isin(parameterlist)] st.write(indicators[['indicatorid','formula','realid']]) if st.button("Compute indicators!"): parser.computeInds(indicators)
def main(): options = get_projects() try: currentProject = db.selectallfrom("project") currProjectName = currentProject[currentProject['currentProject'] == 1]['name'].iloc[0] currProjectYear = currentProject[currentProject['currentProject'] == 1]['currentYear'].iloc[0] except: currProjectName = "No current project selected..." currProjectYear = "No current project selected..." st.sidebar.title("Current project:") st.sidebar.write("Project name: " + currProjectName) st.sidebar.write("Project year: " + currProjectYear) project_selected = st.sidebar.selectbox("Change project", options=options) projectid = project_selected[0] if st.sidebar.button("Change current project"): changeCurrentProject(projectid) st.sidebar.title("Navigation") selection = st.sidebar.radio("Go to", list(PAGES.keys())) page = PAGES[selection] with st.spinner(f"Loading {selection} ..."): ast.shared.components.write_page(page)
def createNewMethod(name, description): db.insertvaluessingle("method(name,description)", "(?,?)", (name, description)) methods = db.selectallfrom('method') currentMethod = methods[methods['name'] == name] methodid = currentMethod['methodid'].iloc[len(currentMethod) - 1] return methodid
def constructFigure(df, type): title = "Test graph to edit template" if type == "barchart": fig = go.Figure() variables = ["Var1", "Var2", "Var3", "Var4", "Var5"] for var in variables: tempdf = df.query(f"Variable =='{var}'") fig.add_trace( go.Bar(x=[tempdf['Year'], tempdf['Population']], y=tempdf['Value'], name=var)) return fig elif type == "categories": fig = go.Figure() variables = ["Var1", "Var2", "Var3"] for var in variables: tempdf = df.query(f"Variable =='{var}'") fig.add_trace( go.Bar(x=tempdf['Value'], y=[tempdf['Year'], tempdf['Population']], name=var, orientation='h', text=tempdf['Value'], textposition='auto', width=0.5)) templates = db.selectallfrom("template") fig.update_layout(title=title, barmode="stack") return fig elif type == "table": labels = [ "Number of employees", "Number of projects", "Number of vacancies", "Number of customers" ] pops = df.Population.unique() hVals = [""] for pop in pops: hVals.append(pop) header = dict(values=hVals) cVals = [] cVals.append(labels) for i in range(len(pops)): tempResult = [] for j in range(len(labels)): tempVal = df[df['Population'] == pops[i]]['Value'].iloc[j] tempResult.append(tempVal) cVals.append(tempResult) cells = dict(values=cVals) return header, cells else: return
def get_projects(): projects = db.selectallfrom("project") result = [] for i in range(len(projects)): name = projects['name'][i] id = projects['projectid'][i] result.append((id,name)) return result
def changeCurrentProject(newid): currentProject = db.selectallfrom("project") try: currid = currentProject[currentProject['currentProject'] == 1]['projectid'].iloc[0] db.updatevalues("project", "currentProject = ?", "projectid = ?", (int(0),int(currid))) except: print("There is no current project!") db.updatevalues("project", "currentProject = ?", "projectid = ?", (int(1),int(newid)))
def get_indicators(): parameters = db.selectallfrom("parameter") parameters = parameters[parameters["fk_methodid"] == currentMethod] result = [] for i in range(len(parameters)): name = parameters['name'].iloc[i] realid = parameters['realid'].iloc[i] result.append((realid,name)) return result
def write(): st.title("Validation") columns = ["dataid","rule","value","parameterid"] # Get all the alterations that are going to be done alterations = pd.DataFrame(columns = columns) parameters = get_indicators() if st.button("Validate!"): for i in range(len(parameters)): realid = parameters[i][0] parameteriddf = db.selectfromwhere("realid,fk_methodid,parameterid","parameter","realid = ? and fk_methodid = ?",(realid,int(currentMethod),)) parameterid = parameteriddf['parameterid'].iloc[0] data = db.selectallfrom("data") datadf = data[data['fk_parameterid'] == parameterid] datadf = datadf[datadf['isActive'] == 1] nanmethoddf = db.selectfromwhere("parameterid,nanmethod","parameter","parameterid = ?",(int(parameterid),)) nanmethod = nanmethoddf['nanmethod'].iloc[0] nandf = datadf[datadf['value'] == "nan"] print("Nandf: " + str(len(nandf))) if len(nandf) > 0: if nanmethod == 0: st.write("There are several NaN values in the data for this indicator, but there has not been a method selected yet how to handle these. Please do so under the validation rules section and come back to this page!") valparse.applynanMethod(nanmethod,nandf) alterations = validateparameter(parameterid,alterations,columns) if len(alterations) > 0: for j in range(len(alterations)): val = alterations['value'].iloc[j] dataid = alterations['dataid'].iloc[j] parid = alterations['parameterid'].iloc[j] rule = alterations['rule'].iloc[j] description = "Rule: " + rule db.insertvaluessingle("alteration(old_val,new_val,description,fk_dataid,fk_projectid)","(?,?,?,?,?)", (val,"?",description,int(dataid),int(currentProject))) else: st.write("All good! According to the validation rules, there are no wrong values.") alts = db.selectallfrom("alteration") alts = alts[alts['fk_projectid'] == currentProject] if len(alts) > 0: interactivetable(alts) else: st.write("Currently no flagged alterations!")
def getParameterValue(realid, df): ### Cache this, otherwise every calculation needs to update parameters parameterdf = db.selectallfrom('parameter') parameterdf = parameterdf[parameterdf["fk_methodid"] == currentMethod] parid = parameterdf[parameterdf['realid'] == realid]['parameterid'].iloc[0] #print("My parameterid is this: " + str(parid)) result = df[df['fk_parameterid'] == parid]['value'].iloc[0] if result == "nan": return np.nan return interpret(result, df)
def write(): projects = db.selectallfrom("project") currentPhase = projects[projects['currentProject'] == 1]['currentPhase'].iloc[0] methodid = projects[projects['currentProject'] == 1]['fk_methodid'].iloc[0] projectid = projects[projects['currentProject'] == 1]['projectid'].iloc[0] projectYear = projects[projects['currentProject'] == 1]['currentYear'].iloc[0] st.title("Project management") st.write("Data import progress: ") progressTable(currentPhase) st.write("Current data import: ") stepSwitcher(currentPhase, methodid, projectid, projectYear)
def makeInactive(dataid,newValue,alterationid): data = db.selectallfrom("data") datadf = data[data['isActive'] == True] row = datadf[datadf['dataid'] == dataid] db.updatevalues("data","isActive = ?","dataid = ?",(int(0),int(dataid))) currentversion = row['version'].iloc[0] tempsplit = currentversion.split(".") fineversion = int(tempsplit[1]) + 1 newVersion = tempsplit[0] + "." + str(fineversion) year = row['year'].iloc[0] parid = row['fk_parameterid'].iloc[0] projectid = row['fk_projectid'].iloc[0] orgid = row['fk_organisationid'].iloc[0] try: db.deletevalue('alteration','alterationid = ? and fk_projectid = ?',(int(alterationid),int(currentProject))) except: print("Nope this didn't work !")
def computeInds(indicators): organisations = db.selectallfrom("organisation") organisations = organisations[organisations['fk_projectid'] == currentProject] errorList = [(0,0)] for i in range(len(organisations)): orgid = organisations['organisationid'].iloc[i] print("Current organisation: " + str(orgid) + " - " + str(i) + " of " + str(len(organisations))) for j in range(len(indicators)): realid = indicators['realid'].iloc[j] formula = indicators['formula'].iloc[j] parameterid = indicators['fk_parameterid'].iloc[j] try: value = calculateIndicator(orgid,formula) except: value = "nan" errorList.append((orgid,parameterid)) if str(value) == "nan": db.insertvaluessingle("data(value,year,version,isActive,fk_parameterid,fk_projectid,fk_organisationid)","(?,?,?,?,?,?,?)",(value,2019,"1.0",0,int(parameterid),int(currentProject),int(orgid))) else: db.insertvaluessingle("data(value,year,version,isActive,fk_parameterid,fk_projectid,fk_organisationid)","(?,?,?,?,?,?,?)",(value,2019,"1.0",1,int(parameterid),int(currentProject),int(orgid)))
import sys import streamlit as st import awesome_streamlit as ast import pandas as pd import re import altair as alt import gspread from oauth2client.service_account import ServiceAccountCredentials import mariadb import src.db.database as db import src.functions.parser as parser projects = db.selectallfrom("project") currentProject = projects[projects['currentProject'] == 1]['projectid'].iloc[0] currentMethod = projects[projects['currentProject'] == 1]['fk_methodid'].iloc[0] def write(): st.write("Welcome to the indicator computation page") st.write("The indicators you have added: ") parameters = db.selectallfrom("parameter") parameters = parameters[parameters["fk_methodid"] == currentMethod] parameterlist = parameters["parameterid"].values indicators = db.selectallfrom("indicator") indicators = indicators[indicators["fk_parameterid"].isin(parameterlist)] st.write(indicators[['indicatorid','formula','realid']]) if st.button("Compute indicators!"): parser.computeInds(indicators)
def constructFigure(self, df, type, title): if type == "barchart": fig = go.Figure() labels = df.Label.unique() for label in labels: tempdf = df.query(f"Label =='{label}'") fig.add_trace( go.Bar(x=[tempdf['Year'], tempdf['Population']], y=tempdf['Value'], name=label, text=round(tempdf['Value']), textposition='auto')) templates = db.selectallfrom("template") layoutJSON = templates[templates['fk_projectid'] == currentProject] layoutJSON = layoutJSON[layoutJSON['vistype'] == type]['templateDict'].iloc[0] layout = json.loads(layoutJSON) fig.update_layout(layout) fig.update_layout(barmode="group", title=title) elif type == "categories": fig = go.Figure() labels = df.Label.unique() for label in labels: tempdf = df.query(f"Label =='{label}'") fig.add_trace( go.Bar(x=tempdf['Value'], y=[tempdf['Year'], tempdf['Population']], name=label, orientation='h', text=round(tempdf['Value']), textposition='auto', width=0.5)) templates = db.selectallfrom("template") layoutJSON = templates[templates['fk_projectid'] == currentProject] layoutJSON = layoutJSON[layoutJSON['vistype'] == type]['templateDict'].iloc[0] layout = json.loads(layoutJSON) fig.update_layout(layout) fig.update_layout(title=title, barmode="stack") elif type == "table": labels = df.Label.unique() pops = df.Population.unique() hVals = [""] for pop in pops: hVals.append(pop) header = dict(values=hVals) cVals = [] cVals.append(labels) for i in range(len(pops)): tempResult = [] for j in range(len(labels)): tempVal = df[df['Population'] == pops[i]]['Value'].iloc[j] tempResult.append(tempVal) cVals.append(tempResult) cells = dict(values=cVals) templates = db.selectallfrom("template") layoutJSON = templates[templates['fk_projectid'] == currentProject] layoutJSON = layoutJSON[layoutJSON['vistype'] == type]['templateDict'].iloc[0] layout = json.loads(layoutJSON) header.update({'fill_color': layout['headerFillCol']}) header.update({'line_color': layout['headerLineCol']}) cells.update({'fill_color': layout['cellsFillCol']}) cells.update({'line_color': layout['cellsLineCol']}) fig = go.Figure(data=[go.Table(header=header, cells=cells)]) fig.update_layout(title=title) #fig.Table.update_layout(fill_color = 'paleturquoise') else: return return fig
def constructDf(self, type, formula, label, population, period): rawData = db.selectallfrom("data") rawData = rawData[rawData["fk_projectid"] == currentProject] rawData = rawData[rawData['isActive'] == 1] if type == "barchart": dims = self.calculateDimensions(formula, population, period) years = self.parsePeriod(period) labels = self.parseVariable(label) pops = self.parseVariable(population) vars = self.parseVariable(formula) labelVardf = pd.DataFrame(data={'label': labels, 'var': vars}) colPeriod = [] colVariable = [] colLabel = [] colPopulation = [] colValue = [] for year in years: for pop in pops: print(pop) for var in vars: colPeriod.append(year) colPopulation.append(pop) colVariable.append(var) colLabel.append(labelVardf[labelVardf['var'] == var] ['label'].iloc[0]) popiddf = db.selectfromwhere( "name,fk_projectid,populationid", "population", "name = ? and fk_projectid = ?", ( pop, int(currentProject), )) popid = popiddf['populationid'].iloc[0] poporgmapdf = db.selectfromwhere( "fk_populationid,fk_organisationid", "populationorganisation_map", "fk_populationid = ?", (int(popid), )) popOrgList = [] for i in range(len(poporgmapdf)): tempOrg = poporgmapdf["fk_organisationid"].iloc[i] popOrgList.append(tempOrg) tempDF = rawData[rawData['year'] == str(year)] tempDF = tempDF[tempDF['fk_organisationid'].isin( popOrgList)] #st.write(tempDF) #### Calculate the value based on year, pop and var(formula) result = aggparse.interpret(var, tempDF) colValue.append(result) data = { 'Year': colPeriod, 'Variable': colVariable, 'Label': colLabel, 'Population': colPopulation, 'Value': colValue } resultdf = pd.DataFrame(data=data) st.write(resultdf) elif type == "categories": dims = self.calculateDimensions(formula, population, period) years = self.parsePeriod(period) labels = self.parseVariable(label) pops = self.parseVariable(population) vars = self.parseVariable(formula) labelVardf = pd.DataFrame(data={'label': labels, 'var': vars}) colPeriod = [] colVariable = [] colLabel = [] colPopulation = [] colValue = [] for year in years: for pop in pops: for var in vars: colPeriod.append(year) colPopulation.append(pop) colVariable.append(var) colLabel.append(labelVardf[labelVardf['var'] == var] ['label'].iloc[0]) popiddf = db.selectfromwhere( "name,fk_projectid,populationid", "population", "name = ? and fk_projectid = ?", ( pop, int(currentProject), )) popid = popiddf['populationid'].iloc[0] poporgmapdf = db.selectfromwhere( "fk_populationid,fk_organisationid", "populationorganisation_map", "fk_populationid = ?", (int(popid), )) popOrgList = [] for i in range(len(poporgmapdf)): tempOrg = poporgmapdf["fk_organisationid"].iloc[i] popOrgList.append(tempOrg) tempDF = rawData[rawData['year'] == str(year)] tempDF = tempDF[tempDF['fk_organisationid'].isin( popOrgList)] #st.write(tempDF) #### Calculate the value based on year, pop and var(formula) result = aggparse.interpret(var, tempDF) colValue.append(result) data = { 'Year': colPeriod, 'Variable': colVariable, 'Label': colLabel, 'Population': colPopulation, 'Value': colValue } resultdf = pd.DataFrame(data=data) st.write(resultdf) elif type == "table": dims = self.calculateDimensions(formula, population, period) years = self.parsePeriod(period) labels = self.parseVariable(label) pops = self.parseVariable(population) vars = self.parseVariable(formula) labelVardf = pd.DataFrame(data={'label': labels, 'var': vars}) colPeriod = [] colVariable = [] colLabel = [] colPopulation = [] colValue = [] for year in years: for pop in pops: for var in vars: colPeriod.append(year) colPopulation.append(pop) colVariable.append(var) colLabel.append(labelVardf[labelVardf['var'] == var] ['label'].iloc[0]) popiddf = db.selectfromwhere( "name,fk_projectid,populationid", "population", "name = ? and fk_projectid = ?", ( pop, int(currentProject), )) popid = popiddf['populationid'].iloc[0] poporgmapdf = db.selectfromwhere( "fk_populationid,fk_organisationid", "populationorganisation_map", "fk_populationid = ?", (int(popid), )) popOrgList = [] for i in range(len(poporgmapdf)): tempOrg = poporgmapdf["fk_organisationid"].iloc[i] popOrgList.append(tempOrg) tempDF = rawData[rawData['year'] == str(year)] tempDF = tempDF[tempDF['fk_organisationid'].isin( popOrgList)] #st.write(tempDF) #### Calculate the value based on year, pop and var(formula) result = aggparse.interpret(var, tempDF) colValue.append(result) data = { 'Year': colPeriod, 'Variable': colVariable, 'Label': colLabel, 'Population': colPopulation, 'Value': colValue } resultdf = pd.DataFrame(data=data) st.write(resultdf) else: return return resultdf
def getData(): rawData = db.selectallfrom('data') rawData = rawData[rawData["fk_projectid"]== currentProject] data = rawData[rawData['isActive'] == 1] return data
def write(): st.title("Template page") options = getVisTypes() visTypeSelected = st.selectbox("Select visualisation type", options=options) projects = db.selectallfrom("project") currentProject = projects[projects["currentProject"] == 1]['projectid'].iloc[0] templates = db.selectallfrom("template") projectTemplates = templates[templates["fk_projectid"] == currentProject] myTemplateJSON = projectTemplates[projectTemplates['vistype'] == visTypeSelected]['templateDict'].iloc[0] myTemplate = json.loads(myTemplateJSON) if visTypeSelected == "barchart": showLegend = st.checkbox("Show/Hide the legend", value=myTemplate['showlegend']) theme = getTheme(myTemplate['template']) colorway = getColorway(myTemplate['colorway']) data = composeDummyData(visTypeSelected) figure = constructFigure(data, visTypeSelected) layout = { "colorway": colorway, "showlegend": showLegend, "template": theme } layoutJSON = json.dumps(layout, indent=4) if st.button("Commit"): db.updatevalues("template", "templateDict = ?", "fk_projectid = ? AND vistype = ?", (layoutJSON, int(currentProject), "barchart")) figure.update_layout(layout) st.write(figure) elif visTypeSelected == "table": data = composeDummyData(visTypeSelected) header, cells = constructFigure(data, visTypeSelected) colorCols = st.beta_columns((1, 1)) headerFillCol = colorCols[0].text_input( "Header fill", value=myTemplate['headerFillCol']) headerLineCol = colorCols[1].text_input( "Header line", value=myTemplate['headerLineCol']) cellsFillCol = colorCols[0].text_input( "Cells fill", value=myTemplate['cellsFillCol']) cellsLineCol = colorCols[1].text_input( "Cells line", value=myTemplate['cellsLineCol']) header.update({'fill_color': headerFillCol}) header.update({'line_color': headerLineCol}) cells.update({'fill_color': cellsFillCol}) cells.update({'line_color': cellsLineCol}) layout = { "headerFillCol": headerFillCol, "headerLineCol": headerLineCol, "cellsFillCol": cellsFillCol, "cellsLineCol": cellsLineCol } layoutJSON = json.dumps(layout, indent=4) if st.button("Commit"): db.updatevalues("template", "templateDict = ?", "fk_projectid = ? AND vistype = ?", (layoutJSON, int(currentProject), "table")) figure = go.Figure(data=[go.Table(header=header, cells=cells)]) st.write(figure) elif visTypeSelected == "categories": print("Jup") showLegend = st.checkbox("Show/Hide the legend", value=myTemplate['showlegend']) theme = getTheme(myTemplate['template']) print("Jup2") data = composeDummyData(visTypeSelected) print("Skrt") figure = constructFigure(data, visTypeSelected) print("Jup3") colorway = getColorway(myTemplate['colorway']) layout = { "colorway": colorway, "showlegend": showLegend, "template": theme } layoutJSON = json.dumps(layout, indent=4) if st.button("Commit"): db.updatevalues("template", "templateDict = ?", "fk_projectid = ? AND vistype = ?", (layoutJSON, int(currentProject), "categories")) print(visTypeSelected) figure.update_layout(layout) st.write(figure)