{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Initial Setup" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [], "source": [ "import os\n", "import numpy as np\n", "import pandas as pd\n", "from dbfread import DBF\n", "from sqlalchemy import create_engine\n", "from tqdm import tqdm" ] }, { "cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [], "source": [ "server = '43.224.110.84' \n", "database = 'tempBR' \n", "username = 'sa' \n", "password = '8qrVA9sb'\n", "\n", "engine = create_engine(\n", " f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver=ODBC+Driver+17+for+SQL+Server'\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Check these before proceeding" ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [], "source": [ "year = 21\n", "session_id = 2 # take from db\n", "exam_id = 3 # take from db\n", "\n", "path = 'data/CSC/Hsif21_Roll_data/dhah_mod' # take data path" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Database write setup" ] }, { "cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [], "source": [ "chunksize = 1000\n", "method = 'multi'" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [], "source": [ "subjects_df = pd.read_sql(\n", " \"\\\n", " SELECT subject_code, subject_id\\\n", " FROM Subjects s, SubjectDetails sd\\\n", " WHERE exam_type = 'HSC' AND exam_id = 3 AND s.subjectdetail_id = sd.subjectdetail_id\\\n", " \",\n", " engine,\n", " index_col='subject_code'\n", ")" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [], "source": [ "centers_df = pd.read_sql(\n", " \"\\\n", " SELECT center_code, center_id\\\n", " FROM Centers\\\n", " \",\n", " engine,\n", " index_col='center_code'\n", ")" ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [], "source": [ "student_id_start = pd.read_sql(\n", " \"\\\n", " SELECT max(student_id) + 1\\\n", " FROM Students\\\n", " \",\n", " engine\n", ").iloc[0,0]" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [], "source": [ "srr_id_start = pd.read_sql(\n", " \"\\\n", " SELECT max(srr_id) + 1\\\n", " FROM Registrations\\\n", " \",\n", " engine\n", ").iloc[0,0]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Process Single File" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [], "source": [ "def get_reg_nos():\n", " return set(pd.read_sql(\n", " f\"\\\n", " SELECT registration_no\\\n", " FROM Registrations\\\n", " WHERE exam_id = {exam_id}\\\n", " \",\n", " engine\n", " ).iloc[:,0])\n", "\n", "reg_nos = get_reg_nos()" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [], "source": [ "def get_raw_data(file, reg_nos):\n", " df = pd.DataFrame(DBF(f'{path}/{file}'))\n", " # remove empty rolls\n", " df['ROLL_NO'].replace('', np.nan, inplace=True)\n", " df.dropna(subset=['ROLL_NO'], inplace=True)\n", " # remove duplicate regno\n", " df = df.groupby('REGNO').first().reset_index()\n", " df = df.loc[~df['REGNO'].isin(reg_nos)]\n", " reg_nos.union(df['REGNO'].tolist())\n", " return df\n", "\n", "file = 'DHS100.DBF'\n", "df = get_raw_data(file, reg_nos)" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['REGNO', 'NAME', 'SESS1', 'SESS2', 'CLASS_ROLL', 'SC', 'BOARD',\n", " 'C_TYPE', 'SEX', 'EXAM', 'FNAME', 'MOTHER', 'GROUP', 'OPTIONAL', 'SUB1',\n", " 'SUB2', 'SUB3', 'SUB4', 'SUB5', 'SUB6', 'SUB7', 'SUB8', 'SUB9', 'SUB10',\n", " 'SUB11', 'SUB12', 'SUB13', 'SUB14', 'ERR', 'CHK', 'V_SC', 'EIIN',\n", " 'OLD_ERR', 'FAILSUB20', 'FAILSUB19', 'FAILSUB18', 'FAILSUB17',\n", " 'ROLL_NO', 'ROLL_20', 'ROLL_19', 'ROLL_18', 'ROLL_17', 'SL_NO',\n", " 'GEN_SL', 'RL_SLNO', 'SYLL', 'PRN_STAT', 'COR_STAT', 'REG_PRN',\n", " 'S_ROLL', 'S_PYR', 'S_REGNO', 'S_SESS1', 'S_BOARD', 'IMAGE_PATH',\n", " 'NATIONALIT', 'ESIF', 'MEDIUM', 'SHIFT', 'C_CODE', 'CHK_E', 'S_GROUP'],\n", " dtype='object')" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.columns" ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
student_namemother_namefather_namegender
student_id
339586MD. MIRAJUL HASAN KHONDAKERABUL KALAM KHONDAKERFATEMA NASRIN0
339587MD. MEHERAB HOSSAINMD. SHAH ALAMMAHMUDA AFROZE0
339588JIHAD KHANJEWEL KHANAYESHA AKTER0
339589SHIBLEE NOMANMD. FOYEZ NORIKAMRUNNAHER AKI0
339590KABITA SIKDERJALAL SIKDERNURMAHAL BEGUM1
...............
341930ABDUL AZIZ EMONGULAM FARUK BHUIYANKHOSHNEHAR BEGUM0
341931MD. KAZI SIAMMD. KAZI SHAFIKUL ISLAMHASINA BEGUM0
341932MUHAMMED MAHDIMUHAMMED MONIRUZZAMANSHAHEEN ARA BEGUM0
341933MAHMUDUL HASANBABUL HOSSAINMUNNI0
341934B. M ATIKUR RAHMANMD. NURUL ISLAM LITONMOST. ARJINA BEGUM0
\n", "

2103 rows × 4 columns

\n", "
" ], "text/plain": [ " student_name mother_name \\\n", "student_id \n", "339586 MD. MIRAJUL HASAN KHONDAKER ABUL KALAM KHONDAKER \n", "339587 MD. MEHERAB HOSSAIN MD. SHAH ALAM \n", "339588 JIHAD KHAN JEWEL KHAN \n", "339589 SHIBLEE NOMAN MD. FOYEZ NORI \n", "339590 KABITA SIKDER JALAL SIKDER \n", "... ... ... \n", "341930 ABDUL AZIZ EMON GULAM FARUK BHUIYAN \n", "341931 MD. KAZI SIAM MD. KAZI SHAFIKUL ISLAM \n", "341932 MUHAMMED MAHDI MUHAMMED MONIRUZZAMAN \n", "341933 MAHMUDUL HASAN BABUL HOSSAIN \n", "341934 B. M ATIKUR RAHMAN MD. NURUL ISLAM LITON \n", "\n", " father_name gender \n", "student_id \n", "339586 FATEMA NASRIN 0 \n", "339587 MAHMUDA AFROZE 0 \n", "339588 AYESHA AKTER 0 \n", "339589 KAMRUNNAHER AKI 0 \n", "339590 NURMAHAL BEGUM 1 \n", "... ... ... \n", "341930 KHOSHNEHAR BEGUM 0 \n", "341931 HASINA BEGUM 0 \n", "341932 SHAHEEN ARA BEGUM 0 \n", "341933 MUNNI 0 \n", "341934 MOST. ARJINA BEGUM 0 \n", "\n", "[2103 rows x 4 columns]" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def get_students(df, student_id_start):\n", " students_df = pd.DataFrame()\n", " students_df[['student_name','mother_name','father_name','gender']] = df[['NAME', 'FNAME', 'MOTHER', 'SEX']]\n", " students_df.index += student_id_start\n", " students_df.index.name = 'student_id'\n", " return students_df\n", "\n", "students_df = get_students(df, student_id_start)\n", "students_df" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
roll_nosession_idgroup_idexam_idregistration_typeregistration_noprevious_year_roll
srr_id
346611605298283Regular1310660961
346612106540203Regular1410425654
346613253771223Regular1410477816
346614106444203Regular1410702097
346615611958283Regular1410788908
........................
348955606591283Regular4110999813
348956606553283Regular4110999814
348957109432203Regular4110999816
348958253805223Regular4110999841
348959253802223Regular4110999842
\n", "

2103 rows × 7 columns

\n", "
" ], "text/plain": [ " roll_no session_id group_id exam_id registration_type \\\n", "srr_id \n", "346611 605298 2 8 3 Regular \n", "346612 106540 2 0 3 Regular \n", "346613 253771 2 2 3 Regular \n", "346614 106444 2 0 3 Regular \n", "346615 611958 2 8 3 Regular \n", "... ... ... ... ... ... \n", "348955 606591 2 8 3 Regular \n", "348956 606553 2 8 3 Regular \n", "348957 109432 2 0 3 Regular \n", "348958 253805 2 2 3 Regular \n", "348959 253802 2 2 3 Regular \n", "\n", " registration_no previous_year_roll \n", "srr_id \n", "346611 1310660961 \n", "346612 1410425654 \n", "346613 1410477816 \n", "346614 1410702097 \n", "346615 1410788908 \n", "... ... ... \n", "348955 4110999813 \n", "348956 4110999814 \n", "348957 4110999816 \n", "348958 4110999841 \n", "348959 4110999842 \n", "\n", "[2103 rows x 7 columns]" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def get_registrations(df, srr_id_start):\n", " registrations_df = pd.DataFrame()\n", " registrations_df['roll_no'] = df['ROLL_NO']\n", " registrations_df['session_id'] = session_id\n", " registrations_df['group_id'] = df['GROUP']\n", " registrations_df['exam_id'] = exam_id\n", " registrations_df['registration_type'] = 'Regular' # take from provided data\n", " registrations_df['registration_no'] = df['REGNO']\n", " registrations_df['previous_year_roll'] = df[[f'ROLL_{y}' for y in range(year-1, year-5, -1)]].apply(lambda x: \",\".join(filter(None, x)), axis=1)\n", " registrations_df.index += srr_id_start\n", " registrations_df.index.name = 'srr_id'\n", " return registrations_df\n", "\n", "registrations_df = get_registrations(df, srr_id_start)\n", "registrations_df" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
center_id
srr_id
34661116
34661216
34661316
34661416
34661516
......
34895516
34895616
34895716
34895816
34895916
\n", "

2103 rows × 1 columns

\n", "
" ], "text/plain": [ " center_id\n", "srr_id \n", "346611 16\n", "346612 16\n", "346613 16\n", "346614 16\n", "346615 16\n", "... ...\n", "348955 16\n", "348956 16\n", "348957 16\n", "348958 16\n", "348959 16\n", "\n", "[2103 rows x 1 columns]" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def get_centermappings(df, registrations_df):\n", " centermappings_df = df.join(centers_df, on='C_CODE')[['center_id']]\n", " centermappings_df.index = registrations_df.index\n", " return centermappings_df\n", "\n", "centermappings_df = get_centermappings(df, registrations_df)\n", "centermappings_df" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
srr_idsubject_idtype
034661116NaN
134661216NaN
234661316NaN
334661416NaN
434661516NaN
............
2733534895662NaN
2733634895735NaN
2733634895764NaN
2733734895860NaN
2733834895960NaN
\n", "

27409 rows × 3 columns

\n", "
" ], "text/plain": [ " srr_id subject_id type\n", "0 346611 16 NaN\n", "1 346612 16 NaN\n", "2 346613 16 NaN\n", "3 346614 16 NaN\n", "4 346615 16 NaN\n", "... ... ... ...\n", "27335 348956 62 NaN\n", "27336 348957 35 NaN\n", "27336 348957 64 NaN\n", "27337 348958 60 NaN\n", "27338 348959 60 NaN\n", "\n", "[27409 rows x 3 columns]" ] }, "execution_count": 38, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def get_subjectregistrations(df, registrations_df):\n", " temp_df = df[[f'SUB{i}' for i in range(1, 15)] + ['OPTIONAL']]\n", " temp_df.index = registrations_df.index\n", " temp_df.reset_index(inplace=True)\n", " subjectregistrations_df = temp_df\\\n", " .drop('OPTIONAL', axis=1)\\\n", " .melt('srr_id')\\\n", " .drop('variable', axis=1)\\\n", " .merge(temp_df[['srr_id', 'OPTIONAL']], how='left', left_on=['srr_id', 'value'], right_on=['srr_id', 'OPTIONAL'])\n", " subjectregistrations_df['value'].replace('', np.nan, inplace=True)\n", " subjectregistrations_df.dropna(subset=['value'], inplace=True)\n", " subjectregistrations_df.loc[subjectregistrations_df['OPTIONAL'].notna(), 'OPTIONAL'] = 'optional'\n", " subjectregistrations_df = subjectregistrations_df.join(subjects_df, on='value').rename(columns={'OPTIONAL': 'type'})[['srr_id', 'subject_id', 'type']]\n", " return subjectregistrations_df\n", "\n", "subjectregistrations_df = get_subjectregistrations(df, registrations_df)\n", "subjectregistrations_df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Final Loop" ] }, { "cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "100%|██████████| 289/289 [01:00<00:00, 4.75it/s]\n" ] } ], "source": [ "reg_nos = get_reg_nos()\n", "\n", "for file in tqdm(os.listdir(path)):\n", " df = get_raw_data(file, reg_nos)\n", "\n", " students_df = get_students(df, student_id_start)\n", " # students_df.to_sql('Students', engine, if_exists='append', chunksize=chunksize, method=method)\n", "\n", " registrations_df = get_registrations(df, srr_id_start)\n", " # registrations_df.to_sql('Registrations', engine, if_exists='append', chunksize=chunksize, method=method)\n", "\n", " centermappings_df = get_centermappings(df, registrations_df)\n", " # centermappings_df.to_sql('CenterMappings', engine, if_exists='append', chunksize=chunksize, method=method)\n", "\n", " subjectregistrations_df = get_subjectregistrations(df, registrations_df)\n", " # subjectregistrations_df.to_sql('SubjectRegistrations', engine, if_exists='append', index=False, chunksize=chunksize, method=method)\n", "\n", " student_id_start += len(students_df)\n", " srr_id_start += len(registrations_df)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.11.0 (main, Oct 24 2022, 18:26:48) [MSC v.1933 64 bit (AMD64)]" }, "orig_nbformat": 4, "vscode": { "interpreter": { "hash": "fea6dd9932537b91dffa77fea81469d8a800d8c034f646a51a7e54bb7dc9f8ea" } } }, "nbformat": 4, "nbformat_minor": 2 }