<jupyter_start><jupyter_text>Student DB<jupyter_code>STUDENT_COLUMN_NAMES = ['SID', 'Name', 'Father', 'DoB', 'Gender', 'Reg Date', 'Reg Status', 'Degree Status', 'Address', 'Qualification']
std_col_types = {
    "SID": Text,
    "Name": Text,
    "Father": Text,
    "DoB":  DateTime,
    "Gender": String(1),
    "Reg Date": DateTime,
    "Reg Status": String(1),
    "Degree Status": String(1),
    "Address": Text,
    "Qualification": Text,
    "Degree": String(2),
    }<jupyter_output><empty_output><jupyter_text>BS Level<jupyter_code>bs_student_files = data_dir.rglob('Lhr_Student_[0-9]*.txt')
# list(bs_student_files)
bs_std_dfs = []
for std_file in bs_student_files:
    df = pd.read_csv(std_file, index_col=None, header=0)
    bs_std_dfs.append(df)<jupyter_output><empty_output><jupyter_text>Add Transfer Student<jupyter_code>bs_std_dfs.append(pd.read_csv(data_dir / 'Transfer.txt', index_col=None, header=0))
df_bs_std = pd.concat(bs_std_dfs, axis=0, ignore_index=True)
df_bs_std = df_bs_std.sort_values(by=['SID'])
original_order = list(df_bs_std.columns.values)
original_order
new_order = [
    'SID',
    'St_Name',
    'Father_Name',
    'Date of Birth',
    'Gender',
    'Reg Date',
    'Reg Status',
    'Degree Status',
    'Address',
    'Last Degree'
    ]
df_bs_std = df_bs_std[new_order]
df_bs_std.columns = STUDENT_COLUMN_NAMES
df_bs_std.SID = "LHR_BS_" + df_bs_std.SID.astype(str)
df_bs_std["Degree"] = "BS"
df_bs_std.head()
df_bs_std.describe(include="all")<jupyter_output><empty_output><jupyter_text>MS Level<jupyter_code>ms_student_files = data_dir.rglob('Lhr_Student_MS_[0-9]*.txt')
# list(ms_student_files)
ms_std_dfs = []
for std_file in ms_student_files:
    df = pd.read_csv(std_file, index_col=None, header=0)
    ms_std_dfs.append(df) 
df_ms_std = pd.concat(ms_std_dfs, axis=0, ignore_index=True)
df_ms_std = df_ms_std.sort_values(by=['SID'])
new_order = [
    'SID',
    'St_Name',
    'Father_Name',
    'Date of Birth',
    'Gender',
    'Reg Date',
    'Reg Status',
    'Degree Status',
    'Address',
    'Last Degree'
    ]
df_ms_std = df_ms_std[new_order]
df_ms_std.columns = STUDENT_COLUMN_NAMES
df_ms_std.SID = "LHR_MS_" + df_ms_std.SID.astype(str)
df_ms_std["Degree"] = "MS"
df_ms_std.head()
df_ms_std.describe(include="all")<jupyter_output><empty_output><jupyter_text>Concat BS and MS<jupyter_code>df_std = pd.concat([df_bs_std, df_ms_std], ignore_index=True)
df_std.Gender = df_std.Gender.map({0: 'M', 1: 'F'})
df_std["Reg Date"] = pd.to_datetime(df_std["Reg Date"])
df_std["DoB"] = pd.to_datetime(df_std["DoB"])
df_std.head()
df_std.describe(include="all")<jupyter_output><empty_output><jupyter_text>Store Student data in DB<jupyter_code>engine = create_engine('sqlite:///University.db', echo=True)
sqlite_connection = engine.connect()
std_table = "LHR_Student"
df_std.to_sql(
    std_table, 
    sqlite_connection, 
    if_exists='replace', 
    index=False, 
    dtype=std_col_types)
sqlite_connection.close()<jupyter_output><empty_output><jupyter_text>Process BS Course Reg<jupyter_code>COURSE_COLUMN_NAMES = ['SID', 'Course', 'Score', 'Semester', 'Discipline', 'Degree']
reg_col_types = {
    "SID": Text,
    "Courses": String(8),
    "Score": Integer,
    "Semester":  String(10),
    "Year": DateTime,
    "Discipline": String(3),
    "Degree": String(2)
    }
bs_reg_files = data_dir.rglob('Lhr_Detail_[0-9]*.txt')
# sorted(bs_reg_files)
bs_reg_dfs = []
for std_file in bs_reg_files:
    df = pd.read_csv(std_file, index_col=None, header=0)
    bs_reg_dfs.append(df) 

df_bs_reg = pd.concat(bs_reg_dfs, axis=0, ignore_index=True)
df_bs_reg = df_bs_reg.sort_values(by=['SID'])
list(df_bs_reg.columns.values)
new_order_reg = ['SID', 'Course', 'Marks', 'Semester', 'Discipline', 'Degree']
df_bs_reg = df_bs_reg[new_order_reg]
df_bs_reg.columns = COURSE_COLUMN_NAMES
df_bs_reg.SID = "LHR_BS_" + df_bs_reg.SID.astype(str)
df_bs_reg.head()
df_bs_reg.describe(include='all')<jupyter_output><empty_output><jupyter_text>Process MS Course Reg<jupyter_code>ms_reg_files = data_dir.rglob('Lhr_Detail_MS_[0-9]*.txt')
# sorted(ms_reg_files)
ms_reg_dfs = []
for std_file in ms_reg_files:
    df = pd.read_csv(std_file, index_col=None, header=0)
    ms_reg_dfs.append(df) 

df_ms_reg = pd.concat(ms_reg_dfs, axis=0, ignore_index=True)
df_ms_reg = df_ms_reg.sort_values(by=['SID'])
new_order_reg = ['SID', 'Course', 'Marks', 'Semester', 'Discipline', 'Degree']
df_ms_reg = df_ms_reg[new_order_reg]
df_ms_reg.columns = COURSE_COLUMN_NAMES
df_ms_reg.SID = "LHR_MS_" + df_ms_reg.SID.astype(str)
df_ms_reg.head()
df_ms_reg.describe(include="all")<jupyter_output><empty_output><jupyter_text>Concat BS and MS Course Reg Tables<jupyter_code>df_reg = pd.concat([df_bs_reg, df_ms_reg], ignore_index=True)
df_reg.head()
df_reg['Year'] = df_reg.Semester.str.extract('(\d+)')
df_reg.Semester = df_reg.Semester.str.replace('\d+', '')
df_reg["Year"] = pd.to_datetime(df_reg["Year"], format='%y')
new_order_reg = ['SID', 'Course', 'Score', 'Semester', 'Year', 'Discipline', 'Degree']
df_reg = df_reg[new_order_reg]
df_reg.tail()
df_reg.Semester.isnull()
df_reg.describe(include='all')
engine = create_engine('sqlite:///University.db', echo=True)
sqlite_connection = engine.connect()

reg_table = "LHR_Course_Reg"
df_reg.to_sql(
    reg_table, 
    sqlite_connection, 
    if_exists='replace', 
    index=False, 
    dtype=reg_col_types)

sqlite_connection.close()<jupyter_output>2020-11-15 12:27:10,983 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2020-11-15 12:27:10,984 INFO sqlalchemy.engine.base.Engine ()
2020-11-15 12:27:10,986 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2020-11-15 12:27:10,987 INFO sqlalchemy.engine.base.Engine ()
2020-11-15 12:27:11,001 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("LHR_Course_Reg")
2020-11-15 12:27:11,002 INFO sqlalchemy.engine.base.Engine ()
2020-11-15 12:27:11,003 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info("LHR_Course_Reg")
2020-11-15 12:27:11,004 INFO sqlalchemy.engine.base.Engine ()
2020-11-15 12:27:11,006 INFO sqlalchemy.engine.base.Engine SELECT name FROM sqlite_master WHERE type='table' ORDER BY name
2020-11-15 12:27:11,007 INFO sqlalchemy.engine.base.Engine ()
2020-11-15 12:27:11,009 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_xinfo("LHR_Course_Reg")
2020-11-15 12:27:11,011 INFO[...]
{'fraction_non_alphanumeric': 0.15525042908410047, 'fraction_numerical': 0.03713527851458886, 'mean_word_length': 6.114317425083241, 'pattern_counts': {'":': 18, '<': 36, '<?xml version=': 0, '>': 36, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '91633', 'license': 'MIT', 'n_tokens_mistral': 2857, 'n_tokens_neox': 2624, 'n_words': 398, 'path': '1.data-cleaning/Lahore.ipynb', 'repo_name': 'aadimator/CSE553-Data-Mining', 'size': '6409'}