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
