The ETL application is used to transform the study programs and related courses from the Faculty of Computer Science and Engineering at the Ss. Cyril and Methodius University in Skopje. which can be found at the following URL.
- Data from the undergraduate-study-program-scraper is required to run this ETL application.
- Read the study programs data from the
study_programs.csv
file - Clean the
study_program_name
column by removing any leading or trailing whitespaces, as well as occurrences of multiple whitespaces, and converting the text to sentence case
- Generate the
study_program_id
column by indexing the study programs
- Read the curriculum data from the
curriculum.csv
file - Clean the
course_code
column by removing any leading or trailing whitespaces, as well as occurrences of multiple whitespaces - Clean the
study_program_name
andcourse_name_mk
columns by removing any leading or trailing whitespaces, as well as occurrences of multiple whitespaces, and converting the text to sentence case
- Handle invalid
course_code
values by extracting the course code from thecourse_name_mk
column, as well as removing the validcourse_code
fromcourse_name_mk
- Read the courses data from the
courses.csv
file - Clean the
course_code
column by removing any leading or trailing whitespaces, as well as occurrences of multiple whitespaces - Clean the
course_name_en
andcourse_name_mk
columns by removing any leading or trailing whitespaces, as well as occurrences of multiple whitespaces, and converting the text to sentence case - Clean the
course_professors
andcourse_prerequisite
columns by replacing newline characters with commas, removing any leading or trailing whitespaces, as well as occurrences of multiple whitespaces and replacingnulls
withнема
- Handle invalid
course_code
values by extracting the course code from thecourse_name_mk
column, as well as removing the validcourse_code
fromcourse_name_mk
andcourse_name_en
- Extract the
course_level
column from thecourse_code
column - Extract the
course_semester
column from the columnscourse_season
andcourse_academic_year
- Extract the
course_prerequisite_type
column from thecourse_prerequisite
column
- Generate the
course_id
column by indexing the courses
- Transform the
course_professors
column by splitting the values and removing the academic titles - Transform the
course_prerequisite
column by splitting the values and validating the course names and calculating the minimum number of subjects that need to be passed in order to enroll in the course
- Map
course_prerequisites
tocourse_prerequisite_ids
using the singletons forcourse_name_mk
- Map
course_professors
tocourse_professor_ids
using the singletons forcourse_professors
This ETL application will save the transformed data in four different files:
study_programs.csv
: contains the details of the study programscurriculum.csv
: contains the details of the study programs and related coursescourses.csv
: contains the details of the coursesmerged.csv
: contains the merged data fromcurriculum.csv
andcourses.csv
- Python 3.9 or later
Before running the scraper, make sure to set the following environment variables:
OUTPUT_DIRECTORY_PATH
: the path to the directory where the output files will be savedSTUDY_PROGRAMS_INPUT_DATA_FILE_PATH
: the path to the study programs data fileCURRICULA_INPUT_DATA_FILE_PATH
: the path to the curricula data fileCOURSE_INPUT_DATA_FILE_PATH
: the path to the courses data fileSTUDY_PROGRAMS_DATA_OUTPUT_FILE_NAME
: the name of the study programs output fileCURRICULA_DATA_OUTPUT_FILE_NAME
: the name of the curricula output fileCOURSE_DATA_OUTPUT_FILE_NAME
: the name of the courses output fileMERGED_DATA_OUTPUT_FILE_NAME
: the name of the merged output fileEXECUTOR_TYPE
: the type of executor that will be used to run the ETL pipelineMAX_WORKERS
: the number of threads that will be used to read and write the data
-
Clone the repository
git clone <repository_url>
-
Install the required packages
pip install -r requirements.txt
-
Run the scraper
python main.py
Make sure to replace <repository_url>
with the actual URL of the repository.