def get_branch_data(): branch_id = request.args.get('branch_id') db = DatabaseHelper() data = db.get_branch(branch_id) return api_helper.return_response(data)
def read_admission(self, channel, year, file_location): df = pd.read_excel(file_location, converters={ 'เลขที่ใบสมัคร': str, 'รหัสสถานศึกษา': str }) try: df = df.loc[1:, [ 'เลขที่ใบสมัคร', 'คำนำหน้านาม(ไทย)', 'ชื่อ(ไทย)', 'นามสกุล(ไทย)', 'GPAX', 'รหัสสถานศึกษา', 'สาขาวิชาที่สมัคร', 'ได้เข้าศึกษา' ]] df.rename(columns={ 'เลขที่ใบสมัคร': 'application_no', 'คำนำหน้านาม(ไทย)': 'gender', 'ชื่อ(ไทย)': 'firstname', 'นามสกุล(ไทย)': 'lastname', 'รหัสสถานศึกษา': 'school_id', 'สาขาวิชาที่สมัคร': 'branch', 'ได้เข้าศึกษา': 'decision' }, inplace=True) except Exception as e: print(e) return inner_res_helper.make_inner_response( False, "Please check your file or table head " + str(e.args[0]), "Please check your file or table head " + str(e.args[0])) # admission table admission_table = df.loc[:, [ 'application_no', 'firstname', 'lastname', 'gender', 'decision' ]] admission_table['admission_year'] = year admission_table['upload_date'] = datetime.now().timestamp() admission_table.loc[admission_table['gender'] == 'นาย', ['gender']] = 'M' admission_table.loc[admission_table['gender'].str.contains('นาง'), ['gender']] = 'F' admission_table.loc[admission_table['decision'] == 'ไม่', ['decision']] = -1 admission_table.loc[admission_table['decision'] == 'ใช่', ['decision']] = 1 admission_table['decision'].fillna(-1, inplace=True) # admission in branch table admission_branch = df.loc[:, ['application_no', 'branch']] # get branch data from database db = DatabaseHelper() branch = db.get_branch() branch = branch['value'] for i in branch: branch_name = i['branch_name'] if admission_branch.loc[admission_branch['branch'].str. contains(branch_name.split()[0]), ['branch']].shape[0] > 0: admission_branch.loc[admission_branch['branch'].str. contains(branch_name.split()[0]), ['branch']] = str(i['branch_id']) admission_branch.rename(columns={'branch': 'branch_id'}, inplace=True) # admission from table admission_from = df.loc[:, ['application_no']] admission_from['channel_id'] = channel # admission studied admission_studied = df.loc[:, ['application_no', 'GPAX', 'school_id']] # admission_studied['school_id'] = '1010335002' # make json data to send to database helper class out_function_data = { 'admission_table': admission_table.to_json(orient='index'), 'admission_branch': admission_branch.to_json(orient='index'), 'admission_from': admission_from.to_json(orient='index'), 'admission_studied': admission_studied.to_json(orient='index') } return inner_res_helper.make_inner_response( True, "Data for insert in to database", out_function_data)
def read_alumni_personal_data(self, data: pd.DataFrame, personal_header, graduated_year): try: data = data.loc[1:, :] data.drop_duplicates(subset=personal_header[0], keep=False, inplace=True) data.rename(columns={ personal_header[0]: 'alumni_id', personal_header[1]: 'gpax', personal_header[2]: 'branch_name', personal_header[3]: 'company', personal_header[4]: 'status', personal_header[5]: 'job_description', personal_header[6]: 'salary', personal_header[7]: 'institution', personal_header[9]: 'branch', personal_header[10]: 'apprentice', personal_header[8]: 'faculty' }, inplace=True) data.astype({'alumni_id': str}) # alumni table alumni = data.loc[:, ['alumni_id', 'gpax']] alumni['graduated_year'] = graduated_year alumni.loc[alumni['gpax'] == 'ไม่ระบุ', ['gpax']] = -1 alumni.astype({'gpax': float}) # alumni graduated table db = DatabaseHelper() branch = db.get_branch() branch = branch['value'] alumni_graduated = data.loc[:, ['alumni_id', 'branch_name']] for i in branch: branch_name = i['branch_name'] if \ alumni_graduated.loc[ alumni_graduated['branch_name'].str.contains(branch_name.split()[0]), [ 'branch_name']].shape[ 0] > 0: alumni_graduated.loc[alumni_graduated['branch_name'].str. contains(branch_name.split()[0]), ['branch_name']] = str(i['branch_id']) alumni_graduated.rename(columns={'branch_name': 'branch_id'}, inplace=True) # alumni apprentice table apprentice = db.get_apprentice_status_list() apprentice = apprentice['value'] apprentice_table = data.loc[:, ['alumni_id', 'apprentice']] for i in apprentice: title = i['status_title'] title_id = i['status_id'] if apprentice_table.loc[ apprentice_table['apprentice'].str.contains(title), ['apprentice']].shape[0] > 0: apprentice_table.loc[ apprentice_table['apprentice'].str.contains(title), ['apprentice']] = str(title_id) apprentice_table.rename(columns={'apprentice': 'apprentice_id'}, inplace=True) # alumni working table working_status = db.get_working_status_list() working_status = working_status['value'] working_table = data.loc[:, [ 'alumni_id', 'status', 'company', 'institution', 'job_description', 'faculty', 'branch', 'salary' ]] for i in working_status: title = i['status_title'] title_id = i['status_id'] if working_table.loc[working_table['status'].str. contains(title), ['status']].shape[0] > 0: working_table.loc[ working_table['status'].str.contains(title), ['status']] = str(title_id) working_table.rename(columns={'status': 'status_id'}, inplace=True) working_table.loc[working_table['salary'].str.contains("ไม่ระบุ"), ['salary']] = np.nan working_table.loc[working_table['salary'] == "", ['salary']] = np.nan working_table['salary'] = working_table['salary'].astype(float) working_table.loc[working_table['company'] == "", ['company']] = None working_table.loc[working_table['institution'] == "", ['institution']] = None working_table.loc[working_table['job_description'] == "", ['job_description']] = None working_table.loc[working_table['faculty'] == "", ['faculty']] = None working_table.loc[working_table['branch'] == "", ['branch']] = None out_function_data = { 'alumni': alumni.to_json(orient='index'), 'alumni_graduated': alumni_graduated.to_json(orient='index'), 'working_table': working_table.to_json(orient='index'), 'apprentice_table': apprentice_table.to_json(orient='index') } return inner_res_helper.make_inner_response( True, "Data for insert to data base", out_function_data) except Exception as e: print(e) return inner_res_helper.make_inner_response( False, "Error", "Having problem when prepare data.")
def read_new_student_file(self, file_location): df = pd.read_excel(file_location, converters={ 'STUDENT_CODE': str, 'APPLICATION_NO': str, 'INSTITUTE_CODE': str }) if df is None: return inner_res_helper.make_inner_response( response=False, message="Cannot read file", value="Cannot read file") try: df.rename(columns={ 'STUDENT_CODE': 'student_id', 'APPLICATION_NO': 'application_no', 'FIRSTNAME_TH': 'firstname', 'LASTNAME_TH': 'lastname', 'SEX_NAME': 'gender', 'PROGRAM_PROJECT_NAME_TH': 'branch_name', 'INSTITUTE_CODE': 'school_id', 'OLDGPA': 'old_gpa' }, inplace=True) except Exception as e: print(e) return inner_res_helper.make_inner_response( False, "Please check your file or table head " + str(e.args[0]), "Please check your file or table head " + str(e.args[0])) # change gender from full text to M or F df.loc[df['gender'] == 'ชาย', ['gender']] = 'M' df.loc[df['gender'] == 'หญิง', ['gender']] = 'F' # get branch data db = DatabaseHelper() branch = db.get_branch() branch = branch['value'] # change branch name to branch id for i in branch: branch_name = i['branch_name'] if df.loc[df['branch_name'].str.contains(branch_name.split()[0]), ['branch_name']].shape[0] > 0: df.loc[df['branch_name'].str.contains(branch_name.split()[0]), ['branch_name']] = str(i['branch_id']) # data frame for student table student = df.loc[:, ['student_id', 'firstname', 'lastname', 'gender']] # data frame for entrance table entrance = df.loc[:, ['student_id', 'application_no']] # data frame for graduated graduated = df.loc[:, ['student_id', 'school_id', 'old_gpa']] graduated.rename(columns={'old_gpa': 'gpax'}, inplace=True) graduated.fillna("0000000000", inplace=True) # data frame for has status table has_status = df.loc[:, ['student_id']] has_status['status_id'] = 1 # data frame for study in study_in = df.loc[:, ['student_id', 'branch_name']] study_in.rename(columns={'branch_name': 'branch_id'}, inplace=True) out_function_data = { 'student': student.to_json(orient='index'), 'entrance': entrance.to_json(orient='index'), 'graduated': graduated.to_json(orient='index'), 'has_status': has_status.to_json(orient='index'), 'study_in': study_in.to_json(orient='index') } return inner_res_helper.make_inner_response( True, "Data for insert in to database", out_function_data)
def analyze_student_status(self, year=None): connect = DatabaseHelper() data = connect.get_all_admission(year) value = {} if data['value']: df = pd.DataFrame(data['value']) df = df[df['admission_year'] == int(year)] if not df.empty: status_data = analyze_helper.set_fullname( connect.get_status_list()) status_dic = analyze_helper.set_dict(status_data.index, status_data.status_title) channel_data = analyze_helper.set_fullname( connect.get_admission_channel()) channel_sample = self.split_channel(channel_data) 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_dict = analyze_helper.set_dict( channel_sample.index, channel_sample.channel_name) branch = connect.get_branch() branch_data = analyze_helper.set_branch(branch['value']) branch_dict = analyze_helper.set_dict(branch_data.index, branch_data.branch_name) group_brance = df.groupby(['channel_id', 'branch_id' ]).size().unstack(fill_value=0) group_brance = analyze_helper.check_list_column( branch_data.index, group_brance) group_brance = analyze_helper.check_list( channel_data.index, group_brance) group_brance = analyze_helper.set_fullname_column( branch_dict, group_brance) group_brance = analyze_helper.set_fullname_index( channel_dict, group_brance) branch_list = group_brance.columns.tolist() channel_id_list = channel_data.index.tolist() table_count = [] for c_id in channel_id_list: by_channel = {} data = df[df['channel_id'] == c_id] if not data.empty: count = len(data) max_data = data.branch_id.value_counts().max() min_data = data.branch_id.value_counts().min() else: count = 0 max_data = 0 min_data = 0 by_channel['channel'] = channel_dict[c_id] by_channel['count'] = str(count) by_channel['max_data'] = str(max_data) by_channel['min_data'] = str(min_data) table_count.append(by_channel) all_student = len(df) channel_count = df.channel_id.value_counts() group = df[(df['status_id'] == 2) | (df['status_id'] == 3)] group = group.groupby(['channel_id', 'status_id' ]).size().unstack(fill_value=0) if group.empty: group = pd.DataFrame(0, index=np.arange(len(channel_count)), columns=[2, 3]) group['channel'] = channel_count.index group.set_index('channel', inplace=True) group = group.rename(columns={2: "probation", 3: "drop"}) list_name = group.columns.tolist() group = pd.merge(channel_count, group, left_index=True, right_index=True, how='inner') group.rename(columns={group.columns[0]: "all"}, inplace=True) all_admission = group['all'].sum() for name in list_name: group['per_Type_' + str(name)] = group.apply( lambda row: (row[name] / row['all']) * 100, axis=1) group['per_Stu_' + str(name)] = group.apply( lambda row: (row[name] / all_student) * 100, axis=1) group['per_all_student'] = (group['all'] / all_admission) * 100 group = group.round(2).sort_index() group_check_index = analyze_helper.check_list( channel_data.index, group) group_fullname = analyze_helper.set_fullname_index( channel_dict, group_check_index) value = { 'branch': branch_list, 'count_by_brance': group_brance.to_dict('index'), 'all_student': str(all_student), 'table': group_check_index.to_dict('index'), 'table_count': table_count, } response = True message = "Don't have Data" else: value = {} response = False message = "Don't have Data" else: value = {} response = False message = "Don't have Data" return inner_res_helper.make_inner_response(response=response, message=message, value=value)
def analyze_admission_admin(self, year=None): connect = DatabaseHelper() data = connect.get_all_admission_admin(year) value = {} if data['value']: df = pd.DataFrame(data['value']) df = df[df['admission_year'] == int(year)] if not df.empty: branch = connect.get_branch() branch_data = analyze_helper.set_branch(branch['value']) branch_dict = analyze_helper.set_dict(branch_data.index, branch_data.branch_name) channel_data = analyze_helper.set_fullname( connect.get_admission_channel()) channel_sample = self.split_channel(channel_data) data_split = self.split_channel(df) channel_round = channel_sample.channel_round.drop_duplicates( ).to_list() analyze_by_round = [] for i in channel_round: analyze_by_round_s = {} data_in_round = data_split[data_split['channel_round'] == i] if data_in_round.empty: data_channel_sample = channel_sample[ channel_sample['channel_round'] == i] data_group = data_channel_sample.groupby( ['channel_round', 'channel_name']).size().unstack(fill_value=0) data_group.iloc[:] = 0 data_group.reset_index(inplace=True) name = data_group.iloc[0, 0] data_group = data_group.drop(columns=['channel_round']) analyze_by_round_s['name'] = name analyze_by_round_s['analyze'] = data_group.to_dict( 'index') else: data_group = data_in_round.groupby( ['channel_round', 'channel_name']).size().unstack(fill_value=0) channel_sample_selector = (channel_sample[ channel_sample['channel_round'] == i]) data_group_check_channel = analyze_helper.check_list_column( channel_sample_selector.channel_name, data_group) data_group_check_channel.reset_index(inplace=True) name = data_group_check_channel.iloc[0, 0] data_group_check_channel = data_group_check_channel.drop( columns=['channel_round']) analyze_by_round_s['name'] = name analyze_by_round_s[ 'analyze'] = data_group_check_channel.to_dict( 'index') analyze_by_round.append(analyze_by_round_s) value = {'analyze_by_round': analyze_by_round} response = True message = "Analyze Successfully" else: value = {} response = False message = "Don't have Data" else: value = {} response = False message = "Don't have Data" return inner_res_helper.make_inner_response(response=response, message=message, value=value)
def analyze_alumni_work(self, year=None): connect = DatabaseHelper() data = connect.get_all_alumni(year) if data['value']: df = pd.DataFrame(data['value']) df['graduated_gpax'] = df['graduated_gpax'].astype(int) branch = connect.get_branch() branch_data = analyze_helper.set_branch(branch['value']) status_working = analyze_helper.set_fullname( connect.get_working_status_list()) status_apprentice = analyze_helper.set_fullname( connect.get_apprentice_status_list()) branch_dic = analyze_helper.set_dict(branch_data.index, branch_data.branch_name) status_working_dic = analyze_helper.set_dict( status_working.index, status_working.status_title) status_apprentice_dic = analyze_helper.set_dict( status_apprentice.index, status_apprentice.status_title) df_brach = df.groupby('branch_id').size() df_branch_finish = analyze_helper.check_list( branch_data.index.values, df_brach) count_by_status = df.groupby('work_id').size() count_by_status_finish = analyze_helper.check_list( status_working.index.values, count_by_status) count_by_training = df.groupby('apprentice_id').size() count_by_training_finish = analyze_helper.check_list( status_apprentice.index.values, count_by_training) df_gpax = df[df['graduated_gpax'] != -1] gpax_by_branch = df_gpax.groupby( 'branch_id')['graduated_gpax'].mean() gpax_by_branch_2decimal = gpax_by_branch.round(2) gpax_by_branch_finish = analyze_helper.check_list( branch_data.index.values, gpax_by_branch_2decimal) list_salary = { 1: 'น้อยกว่า 10,000', 2: '10,000-19,999', 3: '20,000-30,000', 4: 'มากกว่า 30,000' } salary_branch_trining = [] list_analze = {} df_salary = df[df['salary'].notna()] df_salary = df_salary.copy() df_salary['salary'] = df_salary['salary'].astype(int) # df_salary.loc[:, ['salary']] =df_salary['salary'].astype(int) salary_all_branch_trining = self.__salary_branch_training( df_salary[['salary', 'apprentice_id']]) salary_all_branch_trining_check_index = analyze_helper.check_list_column( status_apprentice.index.values, salary_all_branch_trining) salary_all_branch_trining_check_column = analyze_helper.check_list( list_salary.keys(), salary_all_branch_trining_check_index) salary_all_branch_trining_index = analyze_helper.set_fullname_column( status_apprentice_dic, salary_all_branch_trining_check_column) salary_all_branch_trining_finist = analyze_helper.set_fullname_index( list_salary, salary_all_branch_trining_index) list_analze['dept_name'] = 'ทั้งหมด' list_analze['num_student'] = len(df) list_analze[ 'salary_all_branch_training'] = salary_all_branch_trining_finist.to_dict( 'index') salary_branch_trining.append(list_analze) list_branch_traning = df_brach.index.tolist() for i in list_branch_traning: list_analze = {} data = df[df['branch_id'] == i] if not data.empty: analyze_salart = self.__salary_branch_training( data[['salary', 'apprentice_id']]) analyze_salart = analyze_helper.check_list_column( status_apprentice.index.values, analyze_salart) analyze_salart = analyze_helper.check_list( list_salary.keys(), analyze_salart) analyze_salart = analyze_helper.set_fullname_column( status_apprentice_dic, analyze_salart) analyze_salart = analyze_helper.set_fullname_index( list_salary, analyze_salart) list_analze['dept_name'] = branch_dic[i] list_analze['num_student'] = len(data) list_analze[ 'salary_all_branch_training'] = analyze_salart.to_dict( 'index') else: analyze_salart = pd.DataFrame( 0, index=np.arange(len(list_salary)), columns=status_apprentice.status_title.tolist()) analyze_salart['list_salary'] = list_salary.values() analyze_salart.set_index('list_salary', inplace=True) list_analze['dept_name'] = branch_dic[i] list_analze['num_student'] = 0 list_analze[ 'salary_all_branch_training'] = analyze_salart.to_dict( 'index') salary_branch_trining.append(list_analze) list_branch_traning.insert(0, 'all') value = { 'count_student': len(df.index), 'count_by_branch': analyze_helper.set_fullname_index(branch_dic, df_branch_finish).to_dict(), 'count_by_status': analyze_helper.set_fullname_index( status_working_dic, count_by_status_finish).to_dict(), 'count_by_training': analyze_helper.set_fullname_index( status_apprentice_dic, count_by_training_finish).to_dict(), 'salary_all_branch_training': dict(zip(list_branch_traning, salary_branch_trining)), 'gpax_by_branch': analyze_helper.set_fullname_index( branch_dic, gpax_by_branch_finish).to_dict(), } 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)