def chartsOne(): survey = request.args.get('survey') company = request.args.get('company') userid = request.args.get('userid') host, base, colection, dbuser, pwd = bl.mongoInit() sectors = ['R1', 'R2', 'R3', 'R4'] df = pd.DataFrame( columns=['sector', 'subsector', 'cid', 'qid', 'qscore', 'qconfidence']) for sector in sectors: document = bl.getSurveyDetails(userid, survey, company, host, base, colection, dbuser, pwd, sector) for i in document: df = df.append( { 'sector': i['rows']['sector'], 'subsector': i['rows']['subsector'], 'cid': i['rows']['cid'], 'qid': i['rows']['qid'], 'qscore': i['rows']['qscore'], 'qconfidence': i['rows']['qconfidence'] }, ignore_index=True) df = df.sort_values(['sector', 'subsector']) R1_df = df[df['sector'] == 'R1'] R2_df = df[df['sector'] == 'R2'] R3_df = df[df['sector'] == 'R3'] R4_df = df[df['sector'] == 'R4'] print("BHARATH") R1_df = cl.calculate(R1_df) R2_df = cl.calculate(R2_df) R3_df = cl.calculate(R3_df) R4_df = cl.calculate(R4_df) framelist = [R1_df, R2_df, R3_df, R4_df] mf = pd.DataFrame(columns=R1_df.columns.values) for i in framelist: phy = i[i['subsector'] == 'Physical'] org = i[i['subsector'] == 'Organisational'] tech = i[i['subsector'] == 'Technical'] i['avgsectscore'] = (phy.iloc[0]['cscore'] + org.iloc[0]['cscore'] + tech.iloc[0]['cscore']) / 3 mf = mf.append(i) mf.index = pd.RangeIndex(len(mf.index)) print(mf) return 'Done'
def chartsOne(): survey = request.args.get('survey') company = request.args.get('company') userid = request.args.get('userid') host, base, colection, dbuser, pwd = bl.mongoInit() df = pd.DataFrame( columns=['sector', 'subsector', 'cid', 'qid', 'qscore', 'qconfidence']) for sector in sectors: document = bl.getSurveyDetails(userid, survey, company, host, base, colection, dbuser, pwd, sector) for i in document: df = df.append( { 'sector': i['rows']['sector'], 'subsector': i['rows']['subsector'], 'cid': i['rows']['cid'], 'qid': i['rows']['qid'], 'qscore': i['rows']['qscore'], 'qconfidence': i['rows']['qconfidence'] }, ignore_index=True) df = df.sort_values(['sector', 'subsector']) R1_df = df[df['sector'] == 'R1'] R2_df = df[df['sector'] == 'R2'] R3_df = df[df['sector'] == 'R3'] R4_df = df[df['sector'] == 'R4'] R1_df = cl.calculate(R1_df) R2_df = cl.calculate(R2_df) R3_df = cl.calculate(R3_df) R4_df = cl.calculate(R4_df) framelist = [R1_df, R2_df, R3_df, R4_df] mf = pd.DataFrame(columns=R1_df.columns.values) for i in framelist: phy = i[i['subsector'] == 'Physical'] org = i[i['subsector'] == 'Organisational'] tech = i[i['subsector'] == 'Technical'] i['avgsectscore'] = (phy.iloc[0]['cscore'] + org.iloc[0]['cscore'] + tech.iloc[0]['cscore']) / 3 mf = mf.append(i) mf.index = pd.RangeIndex(len(mf.index)) avgR1_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')]) avgR1_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')]) avgR1_org = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R1')]) avgR1_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')]) avgR2_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')]) avgR2_org = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R2')]) avgR2_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')]) avgR3_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')]) avgR3_org = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R3')]) avgR3_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')]) avgR4_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')]) avgR4_org = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R4')]) avgR4_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')]) # mf[(mf['subsector']=='Physical') & (mf['sector']=='R1')]['subsector_avg']=avgR1_phy ## mf['subsector_avg'] = np.where( (mf['subsector']=='Physical') & (mf['sector']=='R1')) filter1 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')] filter1['subsector_avg'] = avgR1_phy filter2 = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R1')] filter2['subsector_avg'] = avgR1_org filter3 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')] filter3['subsector_avg'] = avgR1_tech filter4 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')] filter4['subsector_avg'] = avgR2_phy filter5 = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R2')] filter5['subsector_avg'] = avgR2_org filter6 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')] filter6['subsector_avg'] = avgR2_tech filter7 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')] filter7['subsector_avg'] = avgR3_phy filter8 = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R3')] filter8['subsector_avg'] = avgR3_org filter9 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')] filter9['subsector_avg'] = avgR3_tech filter10 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')] filter10['subsector_avg'] = avgR4_phy filter11 = mf[(mf['subsector'] == 'Organisational') & (mf['sector'] == 'R4')] filter11['subsector_avg'] = avgR4_org filter12 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')] filter12['subsector_avg'] = avgR4_tech dfs = [ filter1, filter2, filter3, filter4, filter5, filter6, filter7, filter8, filter9, filter10, filter11, filter12 ] df = pd.concat(dfs) js = df.to_json() # print(df) return js
def chartsByDept(): import time import xlwt from xlwt import Workbook survey = request.args.get('survey') company = request.args.get('company') dept = request.args.get('department') host, base, colection, dbuser, pwd = bl.mongoInit('users') df = pd.DataFrame( columns=['sector', 'subsector', 'cid', 'qid', 'qscore', 'qconfidence']) for sector in sectors: document = bl.getSurveyDetailsByDept(survey, company, host, base, colection, dbuser, pwd, sector, dept) for i in document: df = df.append( { 'sector': i['rows']['sector'], 'subsector': i['rows']['subsector'], 'cid': i['rows']['cid'], 'cname': i['rows']['cname'], 'qid': i['rows']['qid'], 'qscore': i['rows']['qscore'], 'qconfidence': i['rows']['qconfidence'] }, ignore_index=True) df = df.sort_values(['sector', 'subsector']) df['qconfidence'] = df['qconfidence'].astype(int) df['qscore'] = df['qscore'].astype(int) R1_df = df[df['sector'] == 'R1'] R2_df = df[df['sector'] == 'R2'] R3_df = df[df['sector'] == 'R3'] R4_df = df[df['sector'] == 'R4'] R1_df = cl.calculate(R1_df, colection) R2_df = cl.calculate(R2_df, colection) R3_df = cl.calculate(R3_df, colection) R4_df = cl.calculate(R4_df, colection) framelist = [R1_df, R2_df, R3_df, R4_df] mf = pd.DataFrame(columns=R1_df.columns.values) for i in framelist: i['avgsectscore'] = i['qscore'].sum() / len(i['qscore']) mf = mf.append(i) mf.index = pd.RangeIndex(len(mf.index)) #phy=i[i['subsector']=='Physical'] #org=i[i['subsector']=='Organizational'] #tech=i[i['subsector']=='Technical'] #i['avgsectscore']=(phy.iloc[0]['subsector_avg']+org.iloc[0]['subsector_avg']+tech.iloc[0]['subsector_avg'])/3 #mf=mf.append(i) #mf.index = pd.RangeIndex(len(mf.index)) #avgR1_phy=mf[(mf['subsector']=='Physical') & (mf['sector']=='R1')].qscore.sum()/len(mf[(mf['subsector']=='Physical') & (mf['sector']=='R1')]) avgR1_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')]) avgR1_org = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R1')]) avgR1_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')]) avgR2_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')]) avgR2_org = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R2')]) avgR2_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')]) avgR3_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')]) avgR3_org = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R3')]) avgR3_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')]) avgR4_phy = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')]) avgR4_org = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R4')]) avgR4_tech = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')].qscore.sum() / len( mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')]) # mf[(mf['subsector']=='Physical') & (mf['sector']=='R1')]['subsector_avg']=avgR1_phy ## mf['subsector_avg'] = np.where( (mf['subsector']=='Physical') & (mf['sector']=='R1')) filter1 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R1')] filter1['subsector_avg'] = avgR1_phy filter2 = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R1')] filter2['subsector_avg'] = avgR1_org filter3 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R1')] filter3['subsector_avg'] = avgR1_tech filter4 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R2')] filter4['subsector_avg'] = avgR2_phy filter5 = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R2')] filter5['subsector_avg'] = avgR2_org filter6 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R2')] filter6['subsector_avg'] = avgR2_tech filter7 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R3')] filter7['subsector_avg'] = avgR3_phy filter8 = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R3')] filter8['subsector_avg'] = avgR3_org filter9 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R3')] filter9['subsector_avg'] = avgR3_tech filter10 = mf[(mf['subsector'] == 'Physical') & (mf['sector'] == 'R4')] filter10['subsector_avg'] = avgR4_phy filter11 = mf[(mf['subsector'] == 'Organizational') & (mf['sector'] == 'R4')] filter11['subsector_avg'] = avgR4_org filter12 = mf[(mf['subsector'] == 'Technical') & (mf['sector'] == 'R4')] filter12['subsector_avg'] = avgR4_tech dfs = [ filter1, filter2, filter3, filter4, filter5, filter6, filter7, filter8, filter9, filter10, filter11, filter12 ] df = pd.concat(dfs) time = int(time.time()) uniqId = 545 ## Sent from UI later.. can be admin userid fileName = 'dataframe_{}.xlsx'.format(time) wb = Workbook() sheet1 = wb.add_sheet('Sheet 1') wb.save(fileName) writer = ExcelWriter(fileName) df.to_excel(writer, 'Sheet1', index=False) writer.save() return fileName