def analyze_project_ar(self, year=None):
connect = DatabaseHelper()
data = connect.get_activity_ar(year)
if data['value']:
data = pd.DataFrame(data['value'])
project = analyze_helper.set_fullname(connect.get_project_list())
project = project[project['project_type'] == 1]
project.drop(columns=['project_type'], inplace=True)
project_dict = analyze_helper.set_dict(project.index,
project.project_name)
# get_branch=connect.get_department(None)
# branch=[]
# for i in get_branch['value']:
# for index in range(len(i['branch'])):
# branch.append(i['branch'][index])
# branch_data = analyze_helper.set_branch(branch)
# branch_data.drop(columns=['amount_student'],inplace=True)
# branch_dict = analyze_helper.set_dict(branch_data.index, branch_data.branch_name)
get_branch = connect.get_department_ds()
get_branch = pd.DataFrame(get_branch['value'])
branch_data = get_branch[['branch_id', 'branch_name']]
branch_data = branch_data.set_index('branch_id')
branch_dict = analyze_helper.set_dict(branch_data.index,
branch_data.branch_name)
list_project = project.index.tolist()
project_set = []
i = 0
for pindex in list_project:
df = data[data['project_id'] == pindex]
name_project = project_dict[pindex]
if not df.empty:
analyze_by_activity = df.groupby(['branch_name']).size()
# analyze_by_activity = analyze_helper.set_fullname_index(branch_dict, analyze_by_activity)
analyze_by_activity_gpax = df.groupby(['branch_name'
])['gpax'].mean()
# analyze_by_activity_gpax = analyze_helper.set_fullname_index(branch_dict, analyze_by_activity_gpax)
analyze_by_activity_gpax = analyze_by_activity_gpax.round(
2)
list_pr = {
'project_name':
name_project,
'analyze_by_activity':
analyze_by_activity.to_dict(),
'analyze_by_activity_gpax':
analyze_by_activity_gpax.to_dict()
}
project_set.append(list_pr)
else:
list_branch = branch_data.branch_name.tolist()
df_empty = pd.DataFrame(list_branch,
columns=['branch_name'])
df_empty['value'] = 0
df_empty.set_index('branch_name', inplace=True)
list_pr = {
'project_name': name_project,
'analyze_by_activity': df_empty.value.to_dict(),
'analyze_by_activity_gpax': df_empty.value.to_dict()
}
project_set.append(list_pr)
i = i + 1
value = {
'project_set': project_set,
}
response = True
message = "Analyze Successfully"
else:
value = {}
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response=response,
message=message,
value=value)
def analyze_ar(self, year=None):
connect = DatabaseHelper()
data = connect.get_activity_ar(year)
if data['value']:
data = pd.DataFrame(data['value'])
activity = analyze_helper.set_fullname(connect.get_activity_list())
dupli_activity = activity.activity_name.duplicated(keep=False)
activity.loc[dupli_activity, 'activity_name'] = activity.loc[dupli_activity, 'activity_name'] + ' (' + \
activity['education_year'].astype(str) + ')'
activityAr = activity[activity['project_type'] == 1]
activity_data = activityAr.drop(columns='project_type')
activityAr = activityAr[['activity_name']]
activity_dict = analyze_helper.set_dict(activityAr.index,
activityAr.activity_name)
# get_branch=connect.get_department(None)
# branch=[]
# for i in get_branch['value']:
# for index in range(len(i['branch'])):
# branch.append(i['branch'][index])
# branch_data = analyze_helper.set_branch(branch)
# branch_data.drop(columns=['amount_student'],inplace=True)
# branch_dict = analyze_helper.set_dict(branch_data.index, branch_data.branch_name)
get_branch = connect.get_department_ds()
get_branch = pd.DataFrame(get_branch['value'])
branch_data = get_branch[['branch_id', 'branch_name']]
branch_data = branch_data.set_index('branch_id')
branch_dict = analyze_helper.set_dict(branch_data.index,
branch_data.branch_name)
# activity_data keep data activity such as activity_year and activity_budget
# activity_dict keep data activity such as activity_year and activity_budget in syntex dic
count_school = data.school_name.value_counts()
sort_count_school = count_school.sort_values(
ascending=False).head()
gpax_school = data.groupby(['school_name'])['gpax'].mean()
sort_gpax_school = gpax_school.sort_values(ascending=False).head()
analyze_by_activity = data.groupby(['project_id', 'branch_name'
]).size().unstack(fill_value=0)
# analyze_by_activity = analyze_helper.check_list(activityAr.index, analyze_by_activity)
# analyze_by_activity = analyze_helper.set_fullname_column(branch_dict, analyze_by_activity)
# analyze_by_activity = analyze_helper.set_fullname_index(activity_dict, analyze_by_activity)
analyze_by_activity_gpax = data.groupby(
['project_id',
'branch_name'])['gpax'].mean().unstack(fill_value=0)
# analyze_by_activity_gpax = analyze_helper.check_list(activityAr.index, analyze_by_activity_gpax)
# analyze_by_activity_gpax = analyze_helper.set_fullname_index(activity_dict, analyze_by_activity_gpax)
# analyze_by_activity_gpax = analyze_helper.set_fullname_column(branch_dict, analyze_by_activity_gpax)
analyze_by_activity_gpax = analyze_by_activity_gpax.round(2)
value = {
'count_school':
sort_count_school.to_dict(),
'gpax':
sort_gpax_school.to_dict(),
'activity_by_branch_count':
[analyze_by_activity.to_dict('index')],
'activity_by_branch_gpax':
[analyze_by_activity_gpax.to_dict('index')]
}
response = True
message = "Analyze Successfully"
else:
value = {}
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response=response,
message=message,
value=value)
def student_admin(self):
value = {}
connect = DatabaseHelper()
data = connect.get_all_student()
if data['value']:
df = pd.DataFrame(data['value'])
# get_branch=connect.get_department(None)
# dept_data = pd.io.json.json_normalize(get_branch['value'], 'branch', ['dept_id','dept_name'])
# department_data = dept_data[['dept_id','dept_name']].set_index('dept_id')
# department_data.drop_duplicates(inplace=True)
get_branch = connect.get_department_ds()
get_branch = pd.DataFrame(get_branch['value'])
department_data = get_branch[['dept_id', 'dept_name']]
department_data = department_data.set_index('dept_id')
status_data = analyze_helper.set_fullname(
connect.get_status_list())
status_dic = analyze_helper.set_dict(status_data.index,
status_data.status_title)
list_department = department_data.index.unique().tolist()
analyze_by_dept = []
for dept in list_department:
analyze = {}
df_dept = df[df['dept_id'] == dept]
department_selector = get_branch[get_branch['dept_id'] == dept]
branch_data = analyze_helper.set_branch(
department_selector[['branch_id', 'branch_name']])
branch_dic = analyze_helper.set_dict(branch_data.index,
branch_data.branch_name)
status_by_branch = self.__status_by_branch(
df_dept, list(branch_data.index.values),
list(status_data.index.values))
status_by_branch_index = analyze_helper.set_fullname_index(
branch_dic, status_by_branch)
status_by_branch_finist = analyze_helper.set_fullname_column(
status_dic, status_by_branch_index)
status_by_year = self.__count_status(
df_dept[['student_year', 'education_status']],
list(status_data.index.values))
status_by_year_finist = analyze_helper.set_fullname_column(
status_dic, status_by_year)
count_by_branch = df_dept.groupby(['branch_id']).size()
count_by_branch = analyze_helper.check_list(
branch_data.index, count_by_branch)
count_by_branch = analyze_helper.set_fullname_index(
branch_dic, count_by_branch)
analyze['dept_id'] = dept
# print(branch_data)
analyze['branch'] = count_by_branch.to_dict()
analyze['status_by_year'] = [
status_by_year_finist.to_dict('index')
]
analyze['df_status_by_branch'] = [
status_by_branch_finist.to_dict('index')
]
analyze_by_dept.append(analyze)
value = {'analyze_by_dept': analyze_by_dept}
response = True
message = "Analyze Student Successfully"
else:
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response, message, value)
def analyze_by_dept(self, dept):
value = {}
connect = DatabaseHelper()
data = connect.get_all_student(dept)
if data['value']:
df = pd.DataFrame(data['value'])
# get_branch=connect.get_department(dept)
# if dept:
# branch_data = analyze_helper.set_branch(get_branch['value'][0][2])
# else :
# print(get_branch)
# branch=[]
# for i in get_branch['value']:
# for index in range(len(i['branch'])):
# branch.append(i['branch'][index])
# branch_data = analyze_helper.set_branch(branch)
get_branch = connect.get_department_ds()
get_branch = pd.DataFrame(get_branch['value'])
if dept:
get_branch = get_branch[get_branch['dept_id'] == dept]
branch_data = get_branch[['branch_id', 'branch_name']]
branch_data = branch_data.set_index('branch_id')
status_data = analyze_helper.set_fullname(
connect.get_status_list())
branch_dic = analyze_helper.set_dict(branch_data.index,
branch_data.branch_name)
status_dic = analyze_helper.set_dict(status_data.index,
status_data.status_title)
status_by_branch = self.__status_by_branch(
df, list(branch_data.index.values),
list(status_data.index.values))
# print(df)
status_by_branch_index = analyze_helper.set_fullname_index(
branch_dic, status_by_branch)
status_by_branch_finist = analyze_helper.set_fullname_column(
status_dic, status_by_branch_index)
status_by_year = self.__count_status(
df[['student_year', 'education_status']],
list(status_data.index.values))
status_by_year_finist = analyze_helper.set_fullname_column(
status_dic, status_by_year)
count_by_branch = df.groupby(['branch_id']).size()
count_by_branch = analyze_helper.check_list(
branch_data.index, count_by_branch)
count_by_branch = analyze_helper.set_fullname_index(
branch_dic, count_by_branch)
value['dept_name'] = get_branch.iloc[0, 1]
value['all_stu_dept'] = self.__count_student_dept(df)
value['branch'] = [count_by_branch.to_dict()]
value['status_by_year'] = [status_by_year_finist.to_dict('index')]
value['df_status_by_branch'] = [
status_by_branch_finist.to_dict('index')
]
response = True
message = "Analyze Student Successfully"
else:
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response, message, value)
def analyze_admission(self, year=None):
connect = DatabaseHelper()
data = connect.get_all_admission(year)
value = {}
if data['value']:
df = pd.DataFrame(data['value'])
# real data
# deparment = connect.get_department()
# deparment = pd.io.json.json_normalize(deparment['value'], max_level=0)
# # branch_data = analyze_helper.set_branch(branch['value'])
# deparment_data = deparment[['dept_id','dept_name']]
# deparment_data.set_index('dept_id',inplace=True)
deparment = connect.get_department_ds()
deparment_data = pd.DataFrame(deparment['value'])
deparment_data = deparment_data[['dept_id', 'dept_name']]
deparment_data.set_index('dept_id', inplace=True)
status_data = analyze_helper.set_fullname(
connect.get_status_list())
channel_data = analyze_helper.set_fullname(
connect.get_admission_channel())
channel_sample = self.split_channel(channel_data)
###check duplicate channel anme
dupli_channel = channel_sample.channel_name.duplicated(keep=False)
channel_sample.loc[dupli_channel, 'channel_name'] = channel_sample.loc[dupli_channel, 'channel_name'] + ' (' + \
channel_sample['channel_round'] + ')'
channel_sample_for_dict = channel_sample[['channel_name']]
round_data = channel_sample[['round_id', 'round_name']]
school = analyze_helper.set_fullname(connect.get_school_lis())
deparment_dict = analyze_helper.set_dict(deparment_data.index,
deparment_data.dept_name)
school_dict = analyze_helper.set_dict(school.index,
school.school_title)
status_dic = analyze_helper.set_dict(status_data.index,
status_data.status_title)
channel_dic = analyze_helper.set_dict(
channel_sample_for_dict.index,
channel_sample_for_dict.channel_name)
data_split_now = df.copy()
data_not_year = df.copy()
if year:
data_split_now = df.loc[df['admission_year'] == int(year)]
round_list = channel_sample['round_id'].unique().tolist()
data_not_none = data_split_now.dropna()
gpa_by_branch = []
response_round = False
for i in round_list:
gpa_by_count = {}
channel_list = channel_sample[channel_sample['round_id'] == i]
list_channel = channel_list.index.tolist()
if (not data_not_none.empty):
count_by_branch = data_not_none['channel_id'].isin(
list_channel)
count_by_branch = data_not_none[count_by_branch]
count_by_branch = count_by_branch.groupby([
'channel_id', 'dept_id'
])['current_gpax'].mean().unstack(fill_value=0)
count_by_branch = count_by_branch.round(2)
count_by_branch_check_branch = analyze_helper.check_list_column(
deparment_data.index, count_by_branch)
count_by_branch_check_channel = analyze_helper.check_list(
channel_list.index, count_by_branch_check_branch)
check_by_round_channel = analyze_helper.set_fullname_column(
deparment_dict, count_by_branch_check_branch)
check_by_round_channel = analyze_helper.set_fullname_index(
channel_dic, check_by_round_channel)
gpa_by_count[
'gpa_by_branch'] = check_by_round_channel.to_dict(
'index')
gpa_by_branch.append(gpa_by_count)
response_round = True
count_channel = data_split_now.groupby('channel_id').size()
count_channel_check_channel = analyze_helper.check_list(
channel_sample.index, count_channel)
count_channel_check_channel = analyze_helper.set_fullname_index(
channel_dic, count_channel_check_channel)
count_school = data_split_now.school_id.value_counts()
sort_count_school = count_school.sort_values(
ascending=False).head()
sort_count_school_fullname = analyze_helper.set_fullname_index(
school_dict, sort_count_school)
count_by_status = data_split_now.groupby(
['channel_id', 'status_id']).size().unstack(fill_value=0)
count_by_status_check_status = analyze_helper.check_list_column(
status_data.index, count_by_status)
count_by_status_check_channel = analyze_helper.check_list(
channel_sample.index, count_by_status_check_status)
count_by_status_fullname = analyze_helper.set_fullname_column(
status_dic, count_by_status_check_channel)
count_by_status_fullname = analyze_helper.set_fullname_index(
channel_dic, count_by_status_fullname)
# used year and branch but used year and year-1
year_select = []
if year:
year_select.append(int(year))
year_select.append(int(year) - 1)
else:
max_year = data_not_year.admission_year.max()
year_select.append(max_year)
year_select.append(max_year - 1)
data_compare = data_not_year[data_not_year['admission_year'].isin(
year_select)]
compare_year = data_compare.groupby(
['channel_id', 'admission_year']).size().unstack(fill_value=0)
if compare_year.empty:
channel_name = channel_sample['channel_name'].unique().tolist()
compare_year_success = pd.DataFrame(0,
index=np.arange(
len(channel_name)),
columns=year_select)
compare_year_success['channel_name'] = channel_name
compare_year_success.set_index('channel_name', inplace=True)
else:
compare_year_check_channel = analyze_helper.check_list(
channel_sample.index, compare_year)
compare_year_success = analyze_helper.check_list_column(
year_select, compare_year_check_channel)
compare_year_success = analyze_helper.set_fullname_index(
channel_dic, compare_year_success)
value = {
'count_channel': count_channel_check_channel.to_dict(),
'count_by_branch': dict(zip(round_list, gpa_by_branch)),
'count_by_school': [sort_count_school_fullname.to_dict()],
'compare_year': [compare_year_success.to_dict('index')],
'count_by_status': [count_by_status_fullname.to_dict('index')],
'response_round': response_round
}
response = True
message = "Analyze Successfully"
else:
response = False
message = "Don't have Data"
return inner_res_helper.make_inner_response(response=response,
message=message,
value=value)