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 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)