{ "cells": [ { "cell_type": "code", "execution_count": 2, "id": "75690d0d", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: sqlalchemy in site-packages (2.0.52)\n", "Requirement already satisfied: greenlet>=1 in site-packages (from sqlalchemy) (3.5.5)\n", "Requirement already satisfied: typing-extensions>=4.6.0 in site-packages (from sqlalchemy) (4.16.0)\n", "Requirement already satisfied: psycopg2 in site-packages (2.9.12)\n" ] } ], "source": [ "!pip install sqlalchemy\n", "from sqlalchemy import create_engine\n", "!pip install psycopg2" ] }, { "cell_type": "code", "execution_count": 3, "id": "141a4d94", "metadata": {}, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 4, "id": "1bbfe405", "metadata": {}, "outputs": [], "source": [ "HOST = \"\"\n", "PORT = \"\"\n", "USER = \"\"\n", "PASSWORD = \"\"\n", "DATABASE = \"university\"\n", "\n", "engine = create_engine(\n", " f\"postgresql+psycopg2://{USER}:{PASSWORD}@{HOST}:{PORT}/{DATABASE}?sslmode=require\"\n", ")" ] }, { "cell_type": "code", "execution_count": 5, "id": "46a99718", "metadata": {}, "outputs": [], "source": [ "query=\"select*from schema_42.professor\"\n", "with engine.connect() as connection:\n", " df=pd.read_sql(query,connection)" ] }, { "cell_type": "code", "execution_count": 6, "id": "e2b52edf", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "6" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.to_sql(\n", " name=\"university\",\n", " schema=\"schema_42\",\n", " con=engine,\n", " if_exists=\"replace\",\n", " index=False\n", ")" ] }, { "cell_type": "code", "execution_count": 48, "id": "f78233a2", "metadata": {}, "outputs": [], "source": [ "professor = pd.read_csv(r\"data/professor.csv\")" ] }, { "cell_type": "code", "execution_count": 49, "id": "e855dea4", "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", "
professor_idprof_first_nameprof_last_namefaculty_email
0101JohnSmithJSMITH@SOMETHING.EDU.SM
1102JaneDoeJANE.DOE@WRONGDOMAIN.COM
2103RobertMartinRMARTIN@SOMETHING.EDU.SM
3104EmilyChenECHEN@WRONGDOMAIN.COM
4105MichaelStoneMSTONE@SOMETHING.EDU.SM
5106SarahConnorSCONNOR@SOMETHING.EDU.SM
6107DavidKimDKIM@WRONGDOMAIN.COM
7108LauraWilsonLWILSON@SOMETHING.EDU.SM
8109JamesGoslingJGOSLING@SOMETHING.EDU.SM
9110MarcusAureliusMAURELIUS@WRONGDOMAIN.COM
\n", "
" ], "text/plain": [ " professor_id prof_first_name prof_last_name faculty_email\n", "0 101 John Smith JSMITH@SOMETHING.EDU.SM\n", "1 102 Jane Doe JANE.DOE@WRONGDOMAIN.COM\n", "2 103 Robert Martin RMARTIN@SOMETHING.EDU.SM\n", "3 104 Emily Chen ECHEN@WRONGDOMAIN.COM\n", "4 105 Michael Stone MSTONE@SOMETHING.EDU.SM\n", "5 106 Sarah Connor SCONNOR@SOMETHING.EDU.SM\n", "6 107 David Kim DKIM@WRONGDOMAIN.COM\n", "7 108 Laura Wilson LWILSON@SOMETHING.EDU.SM\n", "8 109 James Gosling JGOSLING@SOMETHING.EDU.SM\n", "9 110 Marcus Aurelius MAURELIUS@WRONGDOMAIN.COM" ] }, "execution_count": 49, "metadata": {}, "output_type": "execute_result" } ], "source": [ "professor" ] }, { "cell_type": "code", "execution_count": 50, "id": "109e3ce8", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 10 entries, 0 to 9\n", "Data columns (total 4 columns):\n", " # Column Non-Null Count Dtype\n", "--- ------ -------------- -----\n", " 0 professor_id 10 non-null int64\n", " 1 prof_first_name 10 non-null str \n", " 2 prof_last_name 10 non-null str \n", " 3 faculty_email 10 non-null str \n", "dtypes: int64(1), str(3)\n", "memory usage: 452.0 bytes\n" ] } ], "source": [ "professor.info()" ] }, { "cell_type": "code", "execution_count": 51, "id": "03d1a152", "metadata": {}, "outputs": [], "source": [ "professor[\"faculty_email\"]=professor[\"faculty_email\"].str.lower()" ] }, { "cell_type": "code", "execution_count": 52, "id": "1bd2a220", "metadata": {}, "outputs": [], "source": [ "professor = professor[professor[\"faculty_email\"].str.contains(\".edu.sm\")].reset_index(drop=True)" ] }, { "cell_type": "code", "execution_count": 53, "id": "c79243b9", "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", "
professor_idprof_first_nameprof_last_namefaculty_email
0101JohnSmithjsmith@something.edu.sm
1103RobertMartinrmartin@something.edu.sm
2105MichaelStonemstone@something.edu.sm
3106SarahConnorsconnor@something.edu.sm
4108LauraWilsonlwilson@something.edu.sm
5109JamesGoslingjgosling@something.edu.sm
\n", "
" ], "text/plain": [ " professor_id prof_first_name prof_last_name faculty_email\n", "0 101 John Smith jsmith@something.edu.sm\n", "1 103 Robert Martin rmartin@something.edu.sm\n", "2 105 Michael Stone mstone@something.edu.sm\n", "3 106 Sarah Connor sconnor@something.edu.sm\n", "4 108 Laura Wilson lwilson@something.edu.sm\n", "5 109 James Gosling jgosling@something.edu.sm" ] }, "execution_count": 53, "metadata": {}, "output_type": "execute_result" } ], "source": [ "professor" ] }, { "cell_type": "code", "execution_count": 12, "id": "355e60d2", "metadata": {}, "outputs": [], "source": [ "classes = pd.read_csv(r\"data/class.csv\")" ] }, { "cell_type": "code", "execution_count": 13, "id": "9bf870e1", "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", "
class_idclass_titleprofessor_idroom_number
0201Intro to Databases101Room A1
1202Data Visualization102Room B2
2203Advanced PostgreSQL103Room A2
3204Python for Analytics104Room C1
4205Business Intelligence Tools105Room B1
5206System Analysis106Room C2
6207Data Warehousing107Room A3
7208Machine Learning Basics108Room B3
8209Algorithms109Room C3
9210Statistical Modeling110Room A4
\n", "
" ], "text/plain": [ " class_id class_title professor_id room_number\n", "0 201 Intro to Databases 101 Room A1\n", "1 202 Data Visualization 102 Room B2\n", "2 203 Advanced PostgreSQL 103 Room A2\n", "3 204 Python for Analytics 104 Room C1\n", "4 205 Business Intelligence Tools 105 Room B1\n", "5 206 System Analysis 106 Room C2\n", "6 207 Data Warehousing 107 Room A3\n", "7 208 Machine Learning Basics 108 Room B3\n", "8 209 Algorithms 109 Room C3\n", "9 210 Statistical Modeling 110 Room A4" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "classes" ] }, { "cell_type": "code", "execution_count": 14, "id": "ab4bffbc", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 10 entries, 0 to 9\n", "Data columns (total 4 columns):\n", " # Column Non-Null Count Dtype\n", "--- ------ -------------- -----\n", " 0 class_id 10 non-null int64\n", " 1 class_title 10 non-null str \n", " 2 professor_id 10 non-null int64\n", " 3 room_number 10 non-null str \n", "dtypes: int64(2), str(2)\n", "memory usage: 452.0 bytes\n" ] } ], "source": [ "classes.info()" ] }, { "cell_type": "code", "execution_count": 59, "id": "3b1a2a53", "metadata": {}, "outputs": [], "source": [ "classes=classes[classes['professor_id'].isin(professor['professor_id'])].reset_index(drop=True)" ] }, { "cell_type": "code", "execution_count": 60, "id": "045e666a", "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", "
class_idclass_titleprofessor_idroom_number
0201Intro to Databases101Room A1
1203Advanced PostgreSQL103Room A2
2205Business Intelligence Tools105Room B1
3206System Analysis106Room C2
4208Machine Learning Basics108Room B3
5209Algorithms109Room C3
\n", "
" ], "text/plain": [ " class_id class_title professor_id room_number\n", "0 201 Intro to Databases 101 Room A1\n", "1 203 Advanced PostgreSQL 103 Room A2\n", "2 205 Business Intelligence Tools 105 Room B1\n", "3 206 System Analysis 106 Room C2\n", "4 208 Machine Learning Basics 108 Room B3\n", "5 209 Algorithms 109 Room C3" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "classes" ] }, { "cell_type": "code", "execution_count": 15, "id": "ccc4cb51", "metadata": {}, "outputs": [], "source": [ "major = pd.read_csv(r\"data/major.csv\")" ] }, { "cell_type": "code", "execution_count": 16, "id": "36895d1c", "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", "
major_idmajor_namedepartment_head
01Computer ScienceDr. Alan Turing
12Data AnalyticsDr. Grace Hopper
23Business IntelligenceDr. Edgar Codd
34Information SystemsDr. Ada Lovelace
45MathtimaticsDr. John von Neumann
\n", "
" ], "text/plain": [ " major_id major_name department_head\n", "0 1 Computer Science Dr. Alan Turing\n", "1 2 Data Analytics Dr. Grace Hopper\n", "2 3 Business Intelligence Dr. Edgar Codd\n", "3 4 Information Systems Dr. Ada Lovelace\n", "4 5 Mathtimatics Dr. John von Neumann" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "major" ] }, { "cell_type": "code", "execution_count": 17, "id": "f8e2b350", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 5 entries, 0 to 4\n", "Data columns (total 3 columns):\n", " # Column Non-Null Count Dtype\n", "--- ------ -------------- -----\n", " 0 major_id 5 non-null int64\n", " 1 major_name 5 non-null str \n", " 2 department_head 5 non-null str \n", "dtypes: int64(1), str(2)\n", "memory usage: 252.0 bytes\n" ] } ], "source": [ "major.info()" ] }, { "cell_type": "code", "execution_count": 18, "id": "8cfe71ec", "metadata": {}, "outputs": [], "source": [ "major[\"major_name\"]=major[\"major_name\"].replace(\"Mathtimatics\",\"Mathematics\")" ] }, { "cell_type": "code", "execution_count": 19, "id": "9bfe8eea", "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", "
major_idmajor_namedepartment_head
01Computer ScienceDr. Alan Turing
12Data AnalyticsDr. Grace Hopper
23Business IntelligenceDr. Edgar Codd
34Information SystemsDr. Ada Lovelace
45MathematicsDr. John von Neumann
\n", "
" ], "text/plain": [ " major_id major_name department_head\n", "0 1 Computer Science Dr. Alan Turing\n", "1 2 Data Analytics Dr. Grace Hopper\n", "2 3 Business Intelligence Dr. Edgar Codd\n", "3 4 Information Systems Dr. Ada Lovelace\n", "4 5 Mathematics Dr. John von Neumann" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "major" ] }, { "cell_type": "code", "execution_count": 82, "id": "c351d4b7", "metadata": {}, "outputs": [], "source": [ "student = pd.read_csv(r\"data/student.csv\")" ] }, { "cell_type": "code", "execution_count": 83, "id": "e8d5091c", "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \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_idfirst_namelast_namebirth_datemajor_id
01001AliceJohnson1900-04-121
11002BobWilliams2004-08-222
21003CharlieBrown2005-11-053
31004DianaPrince2003-02-154
41005EvanWright2004-05-302
51006FionaGallagher1800-07-191
61007GeorgeMiller2003-12-115
71008HannahAbbott2004-09-092
81009IanMalcolm2005-01-243
91010JuliaRoberts2004-03-141
101011KevinHart2003-10-314
111012LunaLovegood2005-06-155
121013TestStudent1ToDelete2000-01-011
131014NathanDrake2004-04-202
141015OliviaBenson2005-08-083
151016PeterParker2003-05-181
161017QuinnFabray2004-11-224
171018TestStudent2ToDelete2000-01-012
181019SamWinchester2005-02-102
191020TinaFey2003-07-253
201021TestStudent3ToDelete2000-01-013
\n", "
" ], "text/plain": [ " student_id first_name last_name birth_date major_id\n", "0 1001 Alice Johnson 1900-04-12 1\n", "1 1002 Bob Williams 2004-08-22 2\n", "2 1003 Charlie Brown 2005-11-05 3\n", "3 1004 Diana Prince 2003-02-15 4\n", "4 1005 Evan Wright 2004-05-30 2\n", "5 1006 Fiona Gallagher 1800-07-19 1\n", "6 1007 George Miller 2003-12-11 5\n", "7 1008 Hannah Abbott 2004-09-09 2\n", "8 1009 Ian Malcolm 2005-01-24 3\n", "9 1010 Julia Roberts 2004-03-14 1\n", "10 1011 Kevin Hart 2003-10-31 4\n", "11 1012 Luna Lovegood 2005-06-15 5\n", "12 1013 TestStudent1 ToDelete 2000-01-01 1\n", "13 1014 Nathan Drake 2004-04-20 2\n", "14 1015 Olivia Benson 2005-08-08 3\n", "15 1016 Peter Parker 2003-05-18 1\n", "16 1017 Quinn Fabray 2004-11-22 4\n", "17 1018 TestStudent2 ToDelete 2000-01-01 2\n", "18 1019 Sam Winchester 2005-02-10 2\n", "19 1020 Tina Fey 2003-07-25 3\n", "20 1021 TestStudent3 ToDelete 2000-01-01 3" ] }, "execution_count": 83, "metadata": {}, "output_type": "execute_result" } ], "source": [ "student" ] }, { "cell_type": "code", "execution_count": 84, "id": "c1fd063f", "metadata": {}, "outputs": [], "source": [ "student=student[~student[\"first_name\"].str.contains(\"Test\",case=False)]" ] }, { "cell_type": "code", "execution_count": 85, "id": "3426b5b7", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Index: 18 entries, 0 to 19\n", "Data columns (total 5 columns):\n", " # Column Non-Null Count Dtype\n", "--- ------ -------------- -----\n", " 0 student_id 18 non-null int64\n", " 1 first_name 18 non-null str \n", " 2 last_name 18 non-null str \n", " 3 birth_date 18 non-null str \n", " 4 major_id 18 non-null int64\n", "dtypes: int64(2), str(3)\n", "memory usage: 864.0 bytes\n" ] } ], "source": [ "student.info()" ] }, { "cell_type": "code", "execution_count": 86, "id": "ae5d1ac6", "metadata": {}, "outputs": [], "source": [ "student[\"birth_date\"]=pd.to_datetime(student[\"birth_date\"])" ] }, { "cell_type": "code", "execution_count": 87, "id": "6897757f", "metadata": {}, "outputs": [], "source": [ "today=pd.Timestamp(\"today\")\n", "max_birth = today - pd.DateOffset(years=18)\n", "min_birth = today - pd.DateOffset(years=40)" ] }, { "cell_type": "code", "execution_count": 88, "id": "340e1016", "metadata": {}, "outputs": [], "source": [ "student = student[\n", " (student[\"birth_date\"] <= max_birth) &\n", " (student[\"birth_date\"] >= min_birth)\n", "]" ] }, { "cell_type": "code", "execution_count": 89, "id": "408f0a85", "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", " \n", " \n", " \n", " \n", " \n", " \n", "
student_idfirst_namelast_namebirth_datemajor_id
11002BobWilliams2004-08-222
21003CharlieBrown2005-11-053
31004DianaPrince2003-02-154
41005EvanWright2004-05-302
61007GeorgeMiller2003-12-115
71008HannahAbbott2004-09-092
81009IanMalcolm2005-01-243
91010JuliaRoberts2004-03-141
101011KevinHart2003-10-314
111012LunaLovegood2005-06-155
131014NathanDrake2004-04-202
141015OliviaBenson2005-08-083
151016PeterParker2003-05-181
161017QuinnFabray2004-11-224
181019SamWinchester2005-02-102
191020TinaFey2003-07-253
\n", "
" ], "text/plain": [ " student_id first_name last_name birth_date major_id\n", "1 1002 Bob Williams 2004-08-22 2\n", "2 1003 Charlie Brown 2005-11-05 3\n", "3 1004 Diana Prince 2003-02-15 4\n", "4 1005 Evan Wright 2004-05-30 2\n", "6 1007 George Miller 2003-12-11 5\n", "7 1008 Hannah Abbott 2004-09-09 2\n", "8 1009 Ian Malcolm 2005-01-24 3\n", "9 1010 Julia Roberts 2004-03-14 1\n", "10 1011 Kevin Hart 2003-10-31 4\n", "11 1012 Luna Lovegood 2005-06-15 5\n", "13 1014 Nathan Drake 2004-04-20 2\n", "14 1015 Olivia Benson 2005-08-08 3\n", "15 1016 Peter Parker 2003-05-18 1\n", "16 1017 Quinn Fabray 2004-11-22 4\n", "18 1019 Sam Winchester 2005-02-10 2\n", "19 1020 Tina Fey 2003-07-25 3" ] }, "execution_count": 89, "metadata": {}, "output_type": "execute_result" } ], "source": [ "student" ] }, { "cell_type": "code", "execution_count": 61, "id": "f1b9fee9", "metadata": {}, "outputs": [], "source": [ "enrollment =pd.read_csv(r\"data/enrollment.csv\")" ] }, { "cell_type": "code", "execution_count": 62, "id": "84ada3a2", "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
enrollment_idstudent_idclass_idgrade
050011001201A
150021002202B
250031003203A
350041004204C
450051005205B
550061006201Pending
650071007210A
750081008202B
850091009207Pending
950101010209A
1050111011206C
1150121012210B
1250131013201DROP
1350141014208Pending
1450151015205A
1550161016203B
1650171017204DROP
1750181018202DROP
1850191019207A
1950201020205Pending
2050211021203DROP
2150221001209B
2250231002204A
2350241005208Pending
2450251006209DROP
2550261008207A
2650271015203B
\n", "
" ], "text/plain": [ " enrollment_id student_id class_id grade\n", "0 5001 1001 201 A\n", "1 5002 1002 202 B\n", "2 5003 1003 203 A\n", "3 5004 1004 204 C\n", "4 5005 1005 205 B\n", "5 5006 1006 201 Pending\n", "6 5007 1007 210 A\n", "7 5008 1008 202 B\n", "8 5009 1009 207 Pending\n", "9 5010 1010 209 A\n", "10 5011 1011 206 C\n", "11 5012 1012 210 B\n", "12 5013 1013 201 DROP\n", "13 5014 1014 208 Pending\n", "14 5015 1015 205 A\n", "15 5016 1016 203 B\n", "16 5017 1017 204 DROP\n", "17 5018 1018 202 DROP\n", "18 5019 1019 207 A\n", "19 5020 1020 205 Pending\n", "20 5021 1021 203 DROP\n", "21 5022 1001 209 B\n", "22 5023 1002 204 A\n", "23 5024 1005 208 Pending\n", "24 5025 1006 209 DROP\n", "25 5026 1008 207 A\n", "26 5027 1015 203 B" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "enrollment" ] }, { "cell_type": "code", "execution_count": 63, "id": "3ebb58b0", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 27 entries, 0 to 26\n", "Data columns (total 4 columns):\n", " # Column Non-Null Count Dtype\n", "--- ------ -------------- -----\n", " 0 enrollment_id 27 non-null int64\n", " 1 student_id 27 non-null int64\n", " 2 class_id 27 non-null int64\n", " 3 grade 27 non-null str \n", "dtypes: int64(3), str(1)\n", "memory usage: 996.0 bytes\n" ] } ], "source": [ "enrollment .info()" ] }, { "cell_type": "code", "execution_count": 68, "id": "6fd1e66e", "metadata": {}, "outputs": [], "source": [ "enrollment=enrollment[enrollment[\"student_id\"].isin(student[\"student_id\"])].reset_index(drop=True)" ] }, { "cell_type": "code", "execution_count": 69, "id": "ccd25bc9", "metadata": {}, "outputs": [], "source": [ "enrollment=enrollment[enrollment[\"class_id\"].isin(classes[\"class_id\"])].reset_index(drop=True)" ] }, { "cell_type": "code", "execution_count": 70, "id": "3a525d75", "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", "
enrollment_idstudent_idclass_idgrade
050031003203A
150051005205B
250101010209A
350111011206C
450141014208Pending
550151015205A
650161016203B
750201020205Pending
850241005208Pending
950271015203B
\n", "
" ], "text/plain": [ " enrollment_id student_id class_id grade\n", "0 5003 1003 203 A\n", "1 5005 1005 205 B\n", "2 5010 1010 209 A\n", "3 5011 1011 206 C\n", "4 5014 1014 208 Pending\n", "5 5015 1015 205 A\n", "6 5016 1016 203 B\n", "7 5020 1020 205 Pending\n", "8 5024 1005 208 Pending\n", "9 5027 1015 203 B" ] }, "execution_count": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "enrollment" ] }, { "cell_type": "code", "execution_count": 71, "id": "45308ed0", "metadata": {}, "outputs": [], "source": [ "enrollment[\"grade\"]=enrollment[\"grade\"].str.lower()" ] }, { "cell_type": "code", "execution_count": 72, "id": "6ed67a9e", "metadata": {}, "outputs": [], "source": [ "enrollment[\"grade\"]=enrollment[\"grade\"].replace(\"pending\",\"b\")" ] }, { "cell_type": "code", "execution_count": 73, "id": "d52d39fb", "metadata": {}, "outputs": [], "source": [ "enrollment=enrollment[enrollment[\"grade\"] != \"drop\"]" ] }, { "cell_type": "code", "execution_count": 74, "id": "56747c03", "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", "
enrollment_idstudent_idclass_idgrade
050031003203a
150051005205b
250101010209a
350111011206c
450141014208b
550151015205a
650161016203b
750201020205b
850241005208b
950271015203b
\n", "
" ], "text/plain": [ " enrollment_id student_id class_id grade\n", "0 5003 1003 203 a\n", "1 5005 1005 205 b\n", "2 5010 1010 209 a\n", "3 5011 1011 206 c\n", "4 5014 1014 208 b\n", "5 5015 1015 205 a\n", "6 5016 1016 203 b\n", "7 5020 1020 205 b\n", "8 5024 1005 208 b\n", "9 5027 1015 203 b" ] }, "execution_count": 74, "metadata": {}, "output_type": "execute_result" } ], "source": [ "enrollment" ] }, { "cell_type": "code", "execution_count": 75, "id": "edb3e169", "metadata": {}, "outputs": [], "source": [ "enrollment[\"grade\"]=enrollment[\"grade\"].str.upper()" ] }, { "cell_type": "code", "execution_count": 76, "id": "f66e8c15", "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", "
enrollment_idstudent_idclass_idgrade
050031003203A
150051005205B
250101010209A
350111011206C
450141014208B
550151015205A
650161016203B
750201020205B
850241005208B
950271015203B
\n", "
" ], "text/plain": [ " enrollment_id student_id class_id grade\n", "0 5003 1003 203 A\n", "1 5005 1005 205 B\n", "2 5010 1010 209 A\n", "3 5011 1011 206 C\n", "4 5014 1014 208 B\n", "5 5015 1015 205 A\n", "6 5016 1016 203 B\n", "7 5020 1020 205 B\n", "8 5024 1005 208 B\n", "9 5027 1015 203 B" ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" } ], "source": [ "enrollment" ] }, { "cell_type": "code", "execution_count": 77, "id": "e6b03800", "metadata": {}, "outputs": [], "source": [ "engine.dispose()" ] }, { "cell_type": "code", "execution_count": 78, "id": "76059972", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "6" ] }, "execution_count": 78, "metadata": {}, "output_type": "execute_result" } ], "source": [ "professor.to_sql(\n", " name=\"professor\",\n", " schema=\"schema_42\",\n", " con=engine,\n", " if_exists= \"replace\",\n", " index= False\n", ")" ] }, { "cell_type": "code", "execution_count": 79, "id": "c4660514", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "6" ] }, "execution_count": 79, "metadata": {}, "output_type": "execute_result" } ], "source": [ "classes.to_sql(\n", " name=\"classes\",\n", " schema=\"schema_42\",\n", " con=engine,\n", " if_exists= \"replace\",\n", " index= False\n", ")" ] }, { "cell_type": "code", "execution_count": 80, "id": "a2e6e162", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "5" ] }, "execution_count": 80, "metadata": {}, "output_type": "execute_result" } ], "source": [ "major.to_sql(\n", " name=\"major\",\n", " schema=\"schema_42\",\n", " con=engine,\n", " if_exists= \"replace\",\n", " index= False\n", ")" ] }, { "cell_type": "code", "execution_count": 81, "id": "48b9a598", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "16" ] }, "execution_count": 81, "metadata": {}, "output_type": "execute_result" } ], "source": [ "student.to_sql(\n", " name=\"student\",\n", " schema=\"schema_42\",\n", " con=engine,\n", " if_exists= \"replace\",\n", " index= False\n", ")" ] }, { "cell_type": "code", "execution_count": 90, "id": "2c87eeb2", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "10" ] }, "execution_count": 90, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#data type in col grade was changed in database from int to text\n", "enrollment.to_sql(\n", " name=\"enrollment\",\n", " schema=\"schema_42\",\n", " con=engine,\n", " if_exists= \"replace\",\n", " index= False\n", ")" ] }, { "cell_type": "code", "execution_count": null, "id": "b44a1eb7", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "base", "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.14.6" } }, "nbformat": 4, "nbformat_minor": 5 }