{ "cells": [ { "cell_type": "markdown", "id": "a58c5797", "metadata": {}, "source": [ "# India Export Database" ] }, { "cell_type": "code", "execution_count": 2, "id": "d5065baf", "metadata": { "scrolled": true }, "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", "
HSCodeCommodityvaluecountryyear
02MEAT AND EDIBLE MEAT OFFAL.0.18AFGHANISTAN TIS2018
13FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUAT...0.00AFGHANISTAN TIS2018
24DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDI...12.48AFGHANISTAN TIS2018
36LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND ...0.00AFGHANISTAN TIS2018
47EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.1.89AFGHANISTAN TIS2018
..................
13701895TOYS, GAMES AND SPORTS REQUISITES; PARTS AND A...0.03ZIMBABWE2010
13701996MISCELLANEOUS MANUFACTURED ARTICLES.0.45ZIMBABWE2010
13702097WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.0.00ZIMBABWE2010
13702198PROJECT GOODS; SOME SPECIAL USES.0.00ZIMBABWE2010
13702299MISCELLANEOUS GOODS.0.07ZIMBABWE2010
\n", "

137023 rows × 5 columns

\n", "
" ], "text/plain": [ " HSCode Commodity value \\\n", "0 2 MEAT AND EDIBLE MEAT OFFAL. 0.18 \n", "1 3 FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUAT... 0.00 \n", "2 4 DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDI... 12.48 \n", "3 6 LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND ... 0.00 \n", "4 7 EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS. 1.89 \n", "... ... ... ... \n", "137018 95 TOYS, GAMES AND SPORTS REQUISITES; PARTS AND A... 0.03 \n", "137019 96 MISCELLANEOUS MANUFACTURED ARTICLES. 0.45 \n", "137020 97 WORKS OF ART COLLECTORS' PIECES AND ANTIQUES. 0.00 \n", "137021 98 PROJECT GOODS; SOME SPECIAL USES. 0.00 \n", "137022 99 MISCELLANEOUS GOODS. 0.07 \n", "\n", " country year \n", "0 AFGHANISTAN TIS 2018 \n", "1 AFGHANISTAN TIS 2018 \n", "2 AFGHANISTAN TIS 2018 \n", "3 AFGHANISTAN TIS 2018 \n", "4 AFGHANISTAN TIS 2018 \n", "... ... ... \n", "137018 ZIMBABWE 2010 \n", "137019 ZIMBABWE 2010 \n", "137020 ZIMBABWE 2010 \n", "137021 ZIMBABWE 2010 \n", "137022 ZIMBABWE 2010 \n", "\n", "[137023 rows x 5 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "import pandas as pd\n", "import sqlite3\n", "\n", "df = pd.read_csv('india_export.csv')\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 3, "id": "d44baa74", "metadata": {}, "outputs": [], "source": [ "conn = sqlite3.connect('india_export.db')\n", "cur = conn.cursor()\n", "cur.execute('''DROP TABLE IF EXISTS export''')\n", "df.to_sql('export', conn, if_exists='replace', index=False) # - writes the pd.df to SQLIte DB\n", "conn.commit()" ] }, { "cell_type": "code", "execution_count": 4, "id": "13dd0e50", "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", "
HSCodeCommodityvaluecountryyear
02MEAT AND EDIBLE MEAT OFFAL.0.18AFGHANISTAN TIS2018
13FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUAT...0.00AFGHANISTAN TIS2018
24DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDI...12.48AFGHANISTAN TIS2018
36LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND ...0.00AFGHANISTAN TIS2018
47EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.1.89AFGHANISTAN TIS2018
..................
13701895TOYS, GAMES AND SPORTS REQUISITES; PARTS AND A...0.03ZIMBABWE2010
13701996MISCELLANEOUS MANUFACTURED ARTICLES.0.45ZIMBABWE2010
13702097WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.0.00ZIMBABWE2010
13702198PROJECT GOODS; SOME SPECIAL USES.0.00ZIMBABWE2010
13702299MISCELLANEOUS GOODS.0.07ZIMBABWE2010
\n", "

137023 rows × 5 columns

\n", "
" ], "text/plain": [ " HSCode Commodity value \\\n", "0 2 MEAT AND EDIBLE MEAT OFFAL. 0.18 \n", "1 3 FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUAT... 0.00 \n", "2 4 DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDI... 12.48 \n", "3 6 LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND ... 0.00 \n", "4 7 EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS. 1.89 \n", "... ... ... ... \n", "137018 95 TOYS, GAMES AND SPORTS REQUISITES; PARTS AND A... 0.03 \n", "137019 96 MISCELLANEOUS MANUFACTURED ARTICLES. 0.45 \n", "137020 97 WORKS OF ART COLLECTORS' PIECES AND ANTIQUES. 0.00 \n", "137021 98 PROJECT GOODS; SOME SPECIAL USES. 0.00 \n", "137022 99 MISCELLANEOUS GOODS. 0.07 \n", "\n", " country year \n", "0 AFGHANISTAN TIS 2018 \n", "1 AFGHANISTAN TIS 2018 \n", "2 AFGHANISTAN TIS 2018 \n", "3 AFGHANISTAN TIS 2018 \n", "4 AFGHANISTAN TIS 2018 \n", "... ... ... \n", "137018 ZIMBABWE 2010 \n", "137019 ZIMBABWE 2010 \n", "137020 ZIMBABWE 2010 \n", "137021 ZIMBABWE 2010 \n", "137022 ZIMBABWE 2010 \n", "\n", "[137023 rows x 5 columns]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.read_sql('select * from export', conn)" ] }, { "cell_type": "code", "execution_count": 5, "id": "d08f712a", "metadata": {}, "outputs": [], "source": [ "conn.close()" ] }, { "cell_type": "markdown", "id": "b8d82301", "metadata": {}, "source": [ "# Steps to normalizing India export database\n", "\n", "- Inspect table and determine how many tables into convert the database into \n", " - Four tables:\n", " - year \n", " - country \n", " - commodity \n", " - a table that joins country, year, commmodity, and value \n", " \n", "- Steps to create year table\n", " - list of unique years\n", " - create statement\n", " - insert function \n" ] }, { "cell_type": "code", "execution_count": 6, "id": "c1c7f292", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import sqlite3\n", "from sqlite3 import Error\n", "\n", "def create_connection(db_file, delete_db=False):\n", " import os\n", " if delete_db and os.path.exists(db_file):\n", " os.remove(db_file)\n", "\n", " conn = None\n", " try:\n", " conn = sqlite3.connect(db_file)\n", " conn.execute(\"PRAGMA foreign_keys = 1\")\n", " except Error as e:\n", " print(e)\n", "\n", " return conn\n", "\n", "\n", "def create_table(conn, create_table_sql):\n", " try:\n", " c = conn.cursor()\n", " c.execute(create_table_sql)\n", " except Error as e:\n", " print(e)\n", " \n", "def execute_sql_statement(sql_statement, conn):\n", " cur = conn.cursor()\n", " cur.execute(sql_statement)\n", "\n", " rows = cur.fetchall()\n", "\n", " return rows\n" ] }, { "cell_type": "code", "execution_count": 7, "id": "66118759", "metadata": {}, "outputs": [], "source": [ "conn = create_connection('india_export.db')" ] }, { "cell_type": "code", "execution_count": 9, "id": "dccbae07", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018]\n" ] } ], "source": [ "# Get list of unique years\n", "\n", "sql_statement = \"SELECT DISTINCT year from export ORDER BY year\"\n", "\n", "years = execute_sql_statement(sql_statement, conn)\n", "years = list(map(lambda row: int(row[0]), years))\n", "print(years)" ] }, { "cell_type": "code", "execution_count": 11, "id": "f7f2e6ec", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
YEAR
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [YEAR]\n", "Index: []" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "create_table_sql = \"\"\"CREATE TABLE [Year] (\n", " [YEAR] INTEGER NOT NULL PRIMARY KEY\n", ");\n", "\"\"\"\n", "# conn_norm.close()\n", "\n", "conn_norm = create_connection('india_export_norm.db', True)\n", "create_table(conn_norm, create_table_sql)\n", "sql_statement = \"SELECT * FROM Year\"\n", "df = pd.read_sql_query(sql_statement, conn_norm)\n", "display(df)\n", "conn_norm.close()" ] }, { "cell_type": "code", "execution_count": 13, "id": "da483133", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
YEAR
\n", "
" ], "text/plain": [ "Empty DataFrame\n", "Columns: [YEAR]\n", "Index: []" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "conn_norm = create_connection('india_export_norm.db')\n", "sql_statement = \"SELECT * FROM Year\"\n", "df = pd.read_sql_query(sql_statement, conn_norm)\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 14, "id": "c9b46709", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(2022,)" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "(2022,)" ] }, { "cell_type": "code", "execution_count": 15, "id": "3aa65519", "metadata": {}, "outputs": [], "source": [ "def insert_year(conn, values):\n", " sql = ''' INSERT INTO YEAR(YEAR)\n", " VALUES(?) '''\n", " cur = conn.cursor()\n", " cur.execute(sql, values)\n", " return cur.lastrowid\n", "\n", "with conn_norm:\n", " for year in years:\n", " insert_year(conn_norm, (year, ))\n" ] }, { "cell_type": "code", "execution_count": 16, "id": "61b7c7c1", "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", "
YEAR
02010
12011
22012
32013
42014
52015
62016
72017
82018
\n", "
" ], "text/plain": [ " YEAR\n", "0 2010\n", "1 2011\n", "2 2012\n", "3 2013\n", "4 2014\n", "5 2015\n", "6 2016\n", "7 2017\n", "8 2018" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "sql_statement = \"SELECT * FROM Year\"\n", "df = pd.read_sql_query(sql_statement, conn_norm)\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 19, "id": "17bfb333", "metadata": {}, "outputs": [], "source": [ "conn_norm.close()\n" ] }, { "cell_type": "code", "execution_count": 18, "id": "16d9425c", "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", "
YEAR
02010
12011
22012
32013
42014
52015
62016
72017
82018
\n", "
" ], "text/plain": [ " YEAR\n", "0 2010\n", "1 2011\n", "2 2012\n", "3 2013\n", "4 2014\n", "5 2015\n", "6 2016\n", "7 2017\n", "8 2018" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "conn_norm = create_connection('india_export_norm.db')\n", "sql_statement = \"SELECT * FROM Year\"\n", "df = pd.read_sql_query(sql_statement, conn_norm)\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 20, "id": "eb032880", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['AFGHANISTAN TIS', 'ALBANIA', 'ALGERIA', 'AMERI SAMOA', 'ANDORRA', 'ANGOLA', 'ANGUILLA', 'ANTARTICA', 'ANTIGUA', 'ARGENTINA', 'ARMENIA', 'ARUBA', 'AUSTRALIA', 'AUSTRIA', 'AZERBAIJAN', 'BAHAMAS', 'BAHARAIN IS', 'BANGLADESH PR', 'BARBADOS', 'BELARUS', 'BELGIUM', 'BELIZE', 'BENIN', 'BERMUDA', 'BHUTAN', 'BOLIVIA', 'BOSNIA-HRZGOVIN', 'BOTSWANA', 'BR VIRGN IS', 'BRAZIL', 'BRUNEI', 'BULGARIA', 'BURKINA FASO', 'BURUNDI', 'C AFRI REP', 'CAMBODIA', 'CAMEROON', 'CANADA', 'CANARY IS', 'CAPE VERDE IS', 'CAYMAN IS', 'CHAD', 'CHANNEL IS', 'CHILE', 'CHINA P RP', 'CHRISTMAS IS.', 'COCOS IS', 'COLOMBIA', 'COMOROS', 'CONGO D. REP.', 'CONGO P REP', 'COOK IS', 'COSTA RICA', \"COTE D' IVOIRE\", 'CROATIA', 'CUBA', 'CURACAO', 'CYPRUS', 'CZECH REPUBLIC', 'DENMARK', 'DJIBOUTI', 'DOMINIC REP', 'DOMINICA', 'ECUADOR', 'EGYPT A RP', 'EL SALVADOR', 'EQUTL GUINEA', 'ERITREA', 'ESTONIA', 'ETHIOPIA', 'FALKLAND IS', 'FAROE IS.', 'FIJI IS', 'FINLAND', 'FR GUIANA', 'FR POLYNESIA', 'FR S ANT TR', 'FRANCE', 'GABON', 'GAMBIA', 'GEORGIA', 'GERMANY', 'GHANA', 'GIBRALTAR', 'GREECE', 'GREENLAND', 'GRENADA', 'GUADELOUPE', 'GUAM', 'GUATEMALA', 'GUERNSEY', 'GUINEA', 'GUINEA BISSAU', 'GUYANA', 'HAITI', 'HEARD MACDONALD', 'HONDURAS', 'HONG KONG', 'HUNGARY', 'ICELAND', 'INDONESIA', 'INSTALLATIONS IN INTERNATIONAL WATERS ', 'IRAN', 'IRAQ', 'IRELAND', 'ISRAEL', 'ITALY', 'JAMAICA', 'JAPAN', 'JERSEY ', 'JORDAN', 'KAZAKHSTAN', 'KENYA', 'KIRIBATI REP', 'KOREA DP RP', 'KOREA RP', 'KUWAIT', 'KYRGHYZSTAN', 'LAO PD RP', 'LATVIA', 'LEBANON', 'LESOTHO', 'LIBERIA', 'LIBYA', 'LIECHTENSTEIN', 'LITHUANIA', 'LUXEMBOURG', 'MACAO', 'MACEDONIA', 'MADAGASCAR', 'MALAWI', 'MALAYSIA', 'MALDIVES', 'MALI', 'MALTA', 'MARSHALL ISLAND', 'MARTINIQUE', 'MAURITANIA', 'MAURITIUS', 'MAYOTTE', 'MEXICO', 'MICRONESIA', 'MOLDOVA', 'MONACO', 'MONGOLIA', 'MONTENEGRO', 'MONTSERRAT', 'MOROCCO', 'MOZAMBIQUE', 'MYANMAR', 'N. MARIANA IS.', 'NAMIBIA', 'NAURU RP', 'NEPAL', 'NETHERLAND', 'NETHERLANDANTIL', 'NEUTRAL ZONE', 'NEW CALEDONIA', 'NEW ZEALAND', 'NICARAGUA', 'NIGER', 'NIGERIA', 'NIUE IS', 'NORFOLK IS', 'NORWAY', 'OMAN', 'PACIFIC IS', 'PAKISTAN IR', 'PALAU', 'PANAMA C Z', 'PANAMA REPUBLIC', 'PAPUA N GNA', 'PARAGUAY', 'PERU', 'PHILIPPINES', 'PITCAIRN IS.', 'POLAND', 'PORTUGAL', 'PUERTO RICO', 'QATAR', 'REUNION', 'ROMANIA', 'RUSSIA', 'RWANDA', 'SAHARWI A.DM RP', 'SAMOA', 'SAN MARINO', 'SAO TOME', 'SAUDI ARAB', 'SENEGAL', 'SERBIA', 'SEYCHELLES', 'SIERRA LEONE', 'SINGAPORE', 'SINT MAARTEN (DUTCH PART)', 'SLOVAK REP', 'SLOVENIA', 'SOLOMON IS', 'SOMALIA', 'SOUTH AFRICA', 'SOUTH SUDAN ', 'SPAIN', 'SRI LANKA DSR', 'ST HELENA', 'ST KITT N A', 'ST LUCIA', 'ST PIERRE', 'ST VINCENT', 'STATE OF PALEST', 'SUDAN', 'SURINAME', 'SWAZILAND', 'SWEDEN', 'SWITZERLAND', 'SYRIA', 'TAIWAN', 'TAJIKISTAN', 'TANZANIA REP', 'THAILAND', 'TIMOR LESTE', 'TOGO', 'TOKELAU IS', 'TONGA', 'TRINIDAD', 'TUNISIA', 'TURKEY', 'TURKMENISTAN', 'TURKS C IS', 'TUVALU', 'U ARAB EMTS', 'U K', 'U S A', 'UGANDA', 'UKRAINE', 'UNION OF SERBIA & MONTENEGRO', 'UNSPECIFIED', 'URUGUAY', 'US MINOR OUTLYING ISLANDS ', 'UZBEKISTAN', 'VANUATU REP', 'VATICAN CITY', 'VENEZUELA', 'VIETNAM SOC REP', 'VIRGIN IS US', 'WALLIS F IS', 'YEMEN REPUBLC', 'ZAMBIA', 'ZIMBABWE']\n" ] } ], "source": [ "# Get list of unique countries\n", "\n", "sql_statement = \"SELECT DISTINCT country from export ORDER BY country\"\n", "\n", "countries = execute_sql_statement(sql_statement, conn)\n", "countries = list(map(lambda row: row[0], countries))\n", "print(countries)" ] }, { "cell_type": "code", "execution_count": 22, "id": "d3ee95be", "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", "
Country
0AFGHANISTAN TIS
1ALBANIA
2ALGERIA
3AMERI SAMOA
4ANDORRA
......
243VIRGIN IS US
244WALLIS F IS
245YEMEN REPUBLC
246ZAMBIA
247ZIMBABWE
\n", "

248 rows × 1 columns

\n", "
" ], "text/plain": [ " Country\n", "0 AFGHANISTAN TIS\n", "1 ALBANIA\n", "2 ALGERIA\n", "3 AMERI SAMOA\n", "4 ANDORRA\n", ".. ...\n", "243 VIRGIN IS US\n", "244 WALLIS F IS\n", "245 YEMEN REPUBLC\n", "246 ZAMBIA\n", "247 ZIMBABWE\n", "\n", "[248 rows x 1 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "def insert_country(conn, values):\n", " sql = ''' INSERT INTO COUNTRY(COUNTRY)\n", " VALUES(?) '''\n", " cur = conn.cursor()\n", " cur.execute(sql, values)\n", " return cur.lastrowid\n", "\n", "create_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS [Country] (\n", " [Country] TEXT NOT NULL PRIMARY KEY\n", ");\n", "\"\"\"\n", "conn_norm = create_connection('india_export_norm.db')\n", "\n", "create_table(conn_norm, create_table_sql)\n", "\n", "with conn_norm:\n", " for country in countries:\n", " insert_country(conn_norm, (country, ))\n", " \n", "sql_statement = \"SELECT * FROM country\"\n", "df = pd.read_sql_query(sql_statement, conn_norm)\n", "display(df)\n" ] }, { "cell_type": "code", "execution_count": 23, "id": "c4ec8403", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[('AIRCRAFT, SPACECRAFT, AND PARTS THEREOF.',), ('ALBUMINOIDAL SUBSTANCES; MODIFIED STARCHES; GLUES; ENZYMES.',), ('ALUMINIUM AND ARTICLES THEREOF.',), ('ANIMAL OR VEGETABLE FATS AND OILS AND THEIR CLEAVAGE PRODUCTS; PRE. EDIBLE FATS; ANIMAL OR VEGETABLE WAXEX.',), ('ARMS AND AMMUNITION; PARTS AND ACCESSORIES THEREOF.',), ('ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, KNITTED OR CORCHETED.',), ('ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, NOT KNITTED OR CROCHETED.',), ('ARTICLES OF IRON OR STEEL',), ('ARTICLES OF LEATHER,SADDLERY AND HARNESS;TRAVEL GOODS, HANDBAGS AND SIMILAR CONT.ARTICLES OF ANIMAL GUT(OTHR THN SILK-WRM)GUT.',), ('ARTICLES OF STONE, PLASTER, CEMENT, ASBESTOS, MICA OR SIMILAR MATERIALS.',), ('BEVERAGES, SPIRITS AND VINEGAR.',), ('CARPETS AND OTHER TEXTILE FLOOR COVERINGS.',), ('CERAMIC PRODUCTS.',), ('CEREALS.',), ('CLOCKS AND WATCHES AND PARTS THEREOF.',), ('COCOA AND COCOA PREPARATIONS.',), ('COFFEE, TEA, MATE AND SPICES.',), ('COPPER AND ARTICLES THEREOF.',), ('CORK AND ARTICLES OF CORK.',), ('COTTON.',), (\"DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDIBLE PROD. OF ANIMAL ORIGIN, NOT ELSEWHERE SPEC. OR INCLUDED.\",), ('EDIBLE FRUIT AND NUTS; PEEL OR CITRUS FRUIT OR MELONS.',), ('EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.',), ('ELECTRICAL MACHINERY AND EQUIPMENT AND PARTS THEREOF; SOUND RECORDERS AND REPRODUCERS, TELEVISION IMAGE AND SOUND RECORDERS AND REPRODUCERS,AND PARTS.',), ('ESSENTIAL OILS AND RESINOIDS; PERFUMERY, COSMETIC OR TOILET PREPARATIONS.',), ('EXPLOSIVES; PYROTECHNIC PRODUCTS; MATCHES; PYROPHORIC ALLOYS; CERTAIN COMBUSTIBLE PREPARATIONS.',), ('FERTILISERS.',), ('FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUATIC INVERTABRATES.',), ('FOOTWEAR, GAITERS AND THE LIKE; PARTS OF SUCH ARTICLES.',), ('FURNITURE; BEDDING, MATTRESSES, MATTRESS SUPPORTS, CUSHIONS AND SIMILAR STUFFED FURNISHING; LAMPS AND LIGHTING FITTINGS NOT ELSEWHERE SPECIFIED OR INC',), ('FURSKINS AND ARTIFICIAL FUR, MANUFACTURES THEREOF.',), ('GLASS AND GLASSWARE.',), ('HEADGEAR AND PARTS THEREOF.',), ('IMPREGNATED, COATED, COVERED OR LAMINATED TEXTILE FABRICS; TEXTILE ARTICLES OF A KIND SUITABLE FOR INDUSTRIAL USE.',), ('INORGANIC CHEMICALS; ORGANIC OR INORGANIC COMPOUNDS OF PRECIOUS METALS, OF RARE-EARTH METALS, OR RADI. ELEM. OR OF ISOTOPES.',), ('IRON AND STEEL',), ('KNITTED OR CROCHETED FABRICS.',), ('LAC; GUMS, RESINS AND OTHER VEGETABLE SAPS AND EXTRACTS.',), ('LEAD AND ARTICLES THEREOF.',), ('LIVE ANIMALS.',), ('LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND THE LIKE; CUT FLOWERS AND ORNAMENTAL FOLIAGE.',), ('MAN-MADE FILAMENTS.',), ('MAN-MADE STAPLE FIBRES.',), ('MANUFACTURES OF STRAW, OF ESPARTO OR OF OTHER PLAITING MATERIALS; BASKETWARE AND WICKERWORK.',), ('MEAT AND EDIBLE MEAT OFFAL.',), ('MINERAL FUELS, MINERAL OILS AND PRODUCTS OF THEIR DISTILLATION; BITUMINOUS SUBSTANCES; MINERAL WAXES.',), ('MISCELLANEOUS ARTICLES OF BASE METAL.',), ('MISCELLANEOUS CHEMICAL PRODUCTS.',), ('MISCELLANEOUS EDIBLE PREPARATIONS.',), ('MISCELLANEOUS GOODS.',), ('MISCELLANEOUS MANUFACTURED ARTICLES.',), ('MUSICAL INSTRUMENTS; PARTS AND ACCESSORIES OF SUCH ARTICLES.',), ('NATURAL OR CULTURED PEARLS,PRECIOUS OR SEMIPRECIOUS STONES,PRE.METALS,CLAD WITH PRE.METAL AND ARTCLS THEREOF;IMIT.JEWLRY;COIN.',), ('NICKEL AND ARTICLES THEREOF.',), ('NUCLEAR REACTORS, BOILERS, MACHINERY AND MECHANICAL APPLIANCES; PARTS THEREOF.',), ('OIL SEEDS AND OLEA. FRUITS; MISC. GRAINS, SEEDS AND FRUIT; INDUSTRIAL OR MEDICINAL PLANTS; STRAW AND FODDER.',), ('OPTICAL, PHOTOGRAPHIC CINEMATOGRAPHIC MEASURING, CHECKING PRECISION, MEDICAL OR SURGICAL INST. AND APPARATUS PARTS AND ACCESSORIES THEREOF;',), ('ORES, SLAG AND ASH.',), ('ORGANIC CHEMICALS',), ('OTHER BASE METALS; CERMETS; ARTICLES THEREOF.',), ('OTHER MADE UP TEXTILE ARTICLES; SETS; WORN CLOTHING AND WORN TEXTILE ARTICLES; RAGS',), ('OTHER VEGETABLE TEXTILE FIBRES; PAPER YARN AND WOVEN FABRICS OF PAPER YARN.',), ('PAPER AND PAPERBOARD; ARTICLES OF PAPER PULP, OF PAPER OR OF PAPERBOARD.',), ('PHARMACEUTICAL PRODUCTS',), ('PHOTOGRAPHIC OR CINEMATOGRAPHIC GOODS.',), ('PLASTIC AND ARTICLES THEREOF.',), ('PREPARATIONS OF CEREALS, FLOUR, STARCH OR MILK; PASTRYCOOKS PRODUCTS.',), ('PREPARATIONS OF MEAT, OF FISH OR OF CRUSTACEANS, MOLLUSCS OR OTHER AQUATIC INVERTEBRATES',), ('PREPARATIONS OF VEGETABLES, FRUIT, NUTS OR OTHER PARTS OF PLANTS.',), ('PREPARED FEATHERS AND DOWN AND ARTICLES MADE OF FEATHERS OR OF DOWN; ARTIFICIAL FLOWERS; ARTICLES OF HUMAN HAIR.',), ('PRINTED BOOKDS, NEWSPAPERS, PICTURES AND OTHER PRODUCTS OF THE PRINTING INDUSTRY; MANUSCRIPTS, TYPESCRIPTS AND PLANS.',), ('PRODUCTS OF ANIMAL ORIGIN, NOT ELSEWHERE SPECIFIED OR INCLUDED.',), ('PRODUCTS OF THE MILLING INDUSTRY; MALT; STARCHES; INULIN; WHEAT GLUTEN.',), ('PROJECT GOODS; SOME SPECIAL USES.',), ('PULP OF WOOD OR OF OTHER FIBROUS CELLULOSIC MATERIAL; WASTE AND SCRAP OF PAPER OR PAPERBOARD.',), ('RAILWAY OR TRAMWAY LOCOMOTIVES, ROLLING-STOCK AND PARTS THEREOF; RAILWAY OR TRAMWAY TRACK FIXTURES AND FITTINGS AND PARTS THEREOF; MECHANICAL',), ('RAW HIDES AND SKINS (OTHER THAN FURSKINS) AND LEATHER',), ('RESIDUES AND WASTE FROM THE FOOD INDUSTRIES; PREPARED ANIMAL FODER.',), ('RUBBER AND ARTICLES THEREOF.',), ('SALT; SULPHUR; EARTHS AND STONE; PLASTERING MATERIALS, LIME AND CEMENT.',), ('SHIPS, BOATS AND FLOATING STRUCTURES.',), ('SILK',), ('SOAP, ORGANIC SURFACE-ACTIVE AGENTS, WASHING PREPARATIONS, LUBRICATING PREPARATIONS, ARTIFICIAL WAXES, PREPARED WAXES, POLISHING OR SCOURING PREP.',), ('SPECIAL WOVEN FABRICS; TUFTED TEXTILE FABRICS; LACE; TAPESTRIES; TRIMMINGS; EMBROIDERY.',), ('SUGARS AND SUGAR CONFECTIONERY.',), ('TANNING OR DYEING EXTRACTS; TANNINS AND THEIR DERI. DYES, PIGMENTS AND OTHER COLOURING MATTER; PAINTS AND VER; PUTTY AND OTHER MASTICS; INKS.',), ('TIN AND ARTICLES THEREOF.',), ('TOBACCO AND MANUFACTURED TOBACCO SUBSTITUTES.',), ('TOOLS IMPLEMENTS, CUTLERY, SPOONS AND FORKS, OF BASE METAL; PARTS THEREOF OF BASE METAL.',), ('TOYS, GAMES AND SPORTS REQUISITES; PARTS AND ACCESSORIES THEREOF.',), ('UMBRELLAS, SUN UMBRELLAS, WALKING-STICKS, SEAT-STICKS, WHIPS,RIDING-CROPS AND PARTS THEREOF.',), ('VEGETABLE PLAITING MATERIALS; VEGETABLE PRODUCTS NOT ELSEWHERE SPECIFIED OR INCLUDED.',), ('VEHICLES OTHER THAN RAILWAY OR TRAMWAY ROLLING STOCK, AND PARTS AND ACCESSORIES THEREOF.',), ('WADDING, FELT AND NONWOVENS; SPACIAL YARNS; TWINE, CORDAGE, ROPES AND CABLES AND ARTICLES THEREOF.',), ('WOOD AND ARTICLES OF WOOD; WOOD CHARCOAL.',), ('WOOL, FINE OR COARSE ANIMAL HAIR, HORSEHAIR YARN AND WOVEN FABRIC.',), (\"WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.\",), ('ZINC AND ARTICLES THEREOF.',)]\n", "98\n" ] } ], "source": [ "sql_statement = \"SELECT DISTINCT COMMODITY FROM export order by COMMODITY\"\n", "commodities = execute_sql_statement(sql_statement, conn)\n", "print(commodities)\n", "print(len(commodities))" ] }, { "cell_type": "code", "execution_count": 24, "id": "2cf17fe6", "metadata": {}, "outputs": [], "source": [ "def insert_commodity(conn, values):\n", " sql = ''' INSERT INTO COMMODITY (COMMODITY)\n", " VALUES(?) '''\n", " cur = conn.cursor()\n", " cur.execute(sql, values)\n", " return cur.lastrowid\n", "\n", "create_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS [COMMODITY] (\n", " [HSCODE] INTEGER NOT NULL PRIMARY KEY, \n", " [COMMODITY] TEXT NOT NULL\n", ");\n", "\"\"\"\n", "create_table(conn_norm, create_table_sql)\n", "\n" ] }, { "cell_type": "code", "execution_count": 25, "id": "aab307d3", "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", "
HSCODECOMMODITY
01AIRCRAFT, SPACECRAFT, AND PARTS THEREOF.
12ALBUMINOIDAL SUBSTANCES; MODIFIED STARCHES; GL...
23ALUMINIUM AND ARTICLES THEREOF.
34ANIMAL OR VEGETABLE FATS AND OILS AND THEIR CL...
45ARMS AND AMMUNITION; PARTS AND ACCESSORIES THE...
.........
9394WADDING, FELT AND NONWOVENS; SPACIAL YARNS; TW...
9495WOOD AND ARTICLES OF WOOD; WOOD CHARCOAL.
9596WOOL, FINE OR COARSE ANIMAL HAIR, HORSEHAIR YA...
9697WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.
9798ZINC AND ARTICLES THEREOF.
\n", "

98 rows × 2 columns

\n", "
" ], "text/plain": [ " HSCODE COMMODITY\n", "0 1 AIRCRAFT, SPACECRAFT, AND PARTS THEREOF.\n", "1 2 ALBUMINOIDAL SUBSTANCES; MODIFIED STARCHES; GL...\n", "2 3 ALUMINIUM AND ARTICLES THEREOF.\n", "3 4 ANIMAL OR VEGETABLE FATS AND OILS AND THEIR CL...\n", "4 5 ARMS AND AMMUNITION; PARTS AND ACCESSORIES THE...\n", ".. ... ...\n", "93 94 WADDING, FELT AND NONWOVENS; SPACIAL YARNS; TW...\n", "94 95 WOOD AND ARTICLES OF WOOD; WOOD CHARCOAL.\n", "95 96 WOOL, FINE OR COARSE ANIMAL HAIR, HORSEHAIR YA...\n", "96 97 WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.\n", "97 98 ZINC AND ARTICLES THEREOF.\n", "\n", "[98 rows x 2 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "with conn_norm:\n", " for commodity in commodities:\n", " insert_commodity(conn_norm, commodity)\n", " \n", "sql_statement = \"SELECT * FROM Commodity\"\n", "df = pd.read_sql_query(sql_statement, conn_norm)\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 26, "id": "3ea59544", "metadata": {}, "outputs": [], "source": [ "# Table 4 which is going link all the tables with values\n", "\n", "def insert_value(conn, values):\n", " sql = ''' INSERT INTO Export (HSCODE, COUNTRY, YEAR, VALUE)\n", " VALUES(?, ?, ?, ?) '''\n", " cur = conn.cursor()\n", " cur.execute(sql, values)\n", " return cur.lastrowid\n", "\n", "create_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS [Export] (\n", " [VALUEID] INTEGER NOT NULL PRIMARY KEY,\n", " [HSCODE] INTEGER NOT NULL, \n", " [COUNTRY] TEXT NOT NULL,\n", " [YEAR] INTEGER NOT NULL,\n", " [VALUE] REAL,\n", " FOREIGN KEY(COUNTRY) REFERENCES Country(COUNTRY),\n", " FOREIGN KEY(YEAR) REFERENCES YEAR(YEAR),\n", " FOREIGN KEY(HSCODE) REFERENCES Commodity(HSCODE)\n", ");\n", "\"\"\"\n", "create_table(conn_norm, create_table_sql)" ] }, { "cell_type": "code", "execution_count": 27, "id": "424ba151", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[('MEAT AND EDIBLE MEAT OFFAL.', 'AFGHANISTAN TIS', 2018, 0.18), ('FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUATIC INVERTABRATES.', 'AFGHANISTAN TIS', 2018, 0.0), (\"DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDIBLE PROD. OF ANIMAL ORIGIN, NOT ELSEWHERE SPEC. OR INCLUDED.\", 'AFGHANISTAN TIS', 2018, 12.48), ('LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND THE LIKE; CUT FLOWERS AND ORNAMENTAL FOLIAGE.', 'AFGHANISTAN TIS', 2018, 0.0), ('EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.', 'AFGHANISTAN TIS', 2018, 1.89), ('EDIBLE FRUIT AND NUTS; PEEL OR CITRUS FRUIT OR MELONS.', 'AFGHANISTAN TIS', 2018, 25.01), ('COFFEE, TEA, MATE AND SPICES.', 'AFGHANISTAN TIS', 2018, 13.75), ('CEREALS.', 'AFGHANISTAN TIS', 2018, 0.75), ('PRODUCTS OF THE MILLING INDUSTRY; MALT; STARCHES; INULIN; WHEAT GLUTEN.', 'AFGHANISTAN TIS', 2018, 0.01), ('OIL SEEDS AND OLEA. FRUITS; MISC. GRAINS, SEEDS AND FRUIT; INDUSTRIAL OR MEDICINAL PLANTS; STRAW AND FODDER.', 'AFGHANISTAN TIS', 2018, 10.02)]\n" ] } ], "source": [ "sql_statement = 'SELECT Commodity, country, year, value from export'\n", "values = execute_sql_statement(sql_statement, conn)\n", "print(values[:10])" ] }, { "cell_type": "code", "execution_count": 28, "id": "91a7b6f0", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[(1, 'AIRCRAFT, SPACECRAFT, AND PARTS THEREOF.'), (2, 'ALBUMINOIDAL SUBSTANCES; MODIFIED STARCHES; GLUES; ENZYMES.'), (3, 'ALUMINIUM AND ARTICLES THEREOF.'), (4, 'ANIMAL OR VEGETABLE FATS AND OILS AND THEIR CLEAVAGE PRODUCTS; PRE. EDIBLE FATS; ANIMAL OR VEGETABLE WAXEX.'), (5, 'ARMS AND AMMUNITION; PARTS AND ACCESSORIES THEREOF.'), (6, 'ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, KNITTED OR CORCHETED.'), (7, 'ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, NOT KNITTED OR CROCHETED.'), (8, 'ARTICLES OF IRON OR STEEL'), (9, 'ARTICLES OF LEATHER,SADDLERY AND HARNESS;TRAVEL GOODS, HANDBAGS AND SIMILAR CONT.ARTICLES OF ANIMAL GUT(OTHR THN SILK-WRM)GUT.'), (10, 'ARTICLES OF STONE, PLASTER, CEMENT, ASBESTOS, MICA OR SIMILAR MATERIALS.')]\n" ] } ], "source": [ "sql_statement = 'SELECT HSCODE, COMMODITY from COMMODITY'\n", "values = execute_sql_statement(sql_statement, conn_norm)\n", "print(values[:10])" ] }, { "cell_type": "code", "execution_count": 30, "id": "6005def1", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "{'AIRCRAFT, SPACECRAFT, AND PARTS THEREOF.': 1, 'ALBUMINOIDAL SUBSTANCES; MODIFIED STARCHES; GLUES; ENZYMES.': 2, 'ALUMINIUM AND ARTICLES THEREOF.': 3, 'ANIMAL OR VEGETABLE FATS AND OILS AND THEIR CLEAVAGE PRODUCTS; PRE. EDIBLE FATS; ANIMAL OR VEGETABLE WAXEX.': 4, 'ARMS AND AMMUNITION; PARTS AND ACCESSORIES THEREOF.': 5, 'ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, KNITTED OR CORCHETED.': 6, 'ARTICLES OF APPAREL AND CLOTHING ACCESSORIES, NOT KNITTED OR CROCHETED.': 7, 'ARTICLES OF IRON OR STEEL': 8, 'ARTICLES OF LEATHER,SADDLERY AND HARNESS;TRAVEL GOODS, HANDBAGS AND SIMILAR CONT.ARTICLES OF ANIMAL GUT(OTHR THN SILK-WRM)GUT.': 9, 'ARTICLES OF STONE, PLASTER, CEMENT, ASBESTOS, MICA OR SIMILAR MATERIALS.': 10, 'BEVERAGES, SPIRITS AND VINEGAR.': 11, 'CARPETS AND OTHER TEXTILE FLOOR COVERINGS.': 12, 'CERAMIC PRODUCTS.': 13, 'CEREALS.': 14, 'CLOCKS AND WATCHES AND PARTS THEREOF.': 15, 'COCOA AND COCOA PREPARATIONS.': 16, 'COFFEE, TEA, MATE AND SPICES.': 17, 'COPPER AND ARTICLES THEREOF.': 18, 'CORK AND ARTICLES OF CORK.': 19, 'COTTON.': 20, \"DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDIBLE PROD. OF ANIMAL ORIGIN, NOT ELSEWHERE SPEC. OR INCLUDED.\": 21, 'EDIBLE FRUIT AND NUTS; PEEL OR CITRUS FRUIT OR MELONS.': 22, 'EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.': 23, 'ELECTRICAL MACHINERY AND EQUIPMENT AND PARTS THEREOF; SOUND RECORDERS AND REPRODUCERS, TELEVISION IMAGE AND SOUND RECORDERS AND REPRODUCERS,AND PARTS.': 24, 'ESSENTIAL OILS AND RESINOIDS; PERFUMERY, COSMETIC OR TOILET PREPARATIONS.': 25, 'EXPLOSIVES; PYROTECHNIC PRODUCTS; MATCHES; PYROPHORIC ALLOYS; CERTAIN COMBUSTIBLE PREPARATIONS.': 26, 'FERTILISERS.': 27, 'FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUATIC INVERTABRATES.': 28, 'FOOTWEAR, GAITERS AND THE LIKE; PARTS OF SUCH ARTICLES.': 29, 'FURNITURE; BEDDING, MATTRESSES, MATTRESS SUPPORTS, CUSHIONS AND SIMILAR STUFFED FURNISHING; LAMPS AND LIGHTING FITTINGS NOT ELSEWHERE SPECIFIED OR INC': 30, 'FURSKINS AND ARTIFICIAL FUR, MANUFACTURES THEREOF.': 31, 'GLASS AND GLASSWARE.': 32, 'HEADGEAR AND PARTS THEREOF.': 33, 'IMPREGNATED, COATED, COVERED OR LAMINATED TEXTILE FABRICS; TEXTILE ARTICLES OF A KIND SUITABLE FOR INDUSTRIAL USE.': 34, 'INORGANIC CHEMICALS; ORGANIC OR INORGANIC COMPOUNDS OF PRECIOUS METALS, OF RARE-EARTH METALS, OR RADI. ELEM. OR OF ISOTOPES.': 35, 'IRON AND STEEL': 36, 'KNITTED OR CROCHETED FABRICS.': 37, 'LAC; GUMS, RESINS AND OTHER VEGETABLE SAPS AND EXTRACTS.': 38, 'LEAD AND ARTICLES THEREOF.': 39, 'LIVE ANIMALS.': 40, 'LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND THE LIKE; CUT FLOWERS AND ORNAMENTAL FOLIAGE.': 41, 'MAN-MADE FILAMENTS.': 42, 'MAN-MADE STAPLE FIBRES.': 43, 'MANUFACTURES OF STRAW, OF ESPARTO OR OF OTHER PLAITING MATERIALS; BASKETWARE AND WICKERWORK.': 44, 'MEAT AND EDIBLE MEAT OFFAL.': 45, 'MINERAL FUELS, MINERAL OILS AND PRODUCTS OF THEIR DISTILLATION; BITUMINOUS SUBSTANCES; MINERAL WAXES.': 46, 'MISCELLANEOUS ARTICLES OF BASE METAL.': 47, 'MISCELLANEOUS CHEMICAL PRODUCTS.': 48, 'MISCELLANEOUS EDIBLE PREPARATIONS.': 49, 'MISCELLANEOUS GOODS.': 50, 'MISCELLANEOUS MANUFACTURED ARTICLES.': 51, 'MUSICAL INSTRUMENTS; PARTS AND ACCESSORIES OF SUCH ARTICLES.': 52, 'NATURAL OR CULTURED PEARLS,PRECIOUS OR SEMIPRECIOUS STONES,PRE.METALS,CLAD WITH PRE.METAL AND ARTCLS THEREOF;IMIT.JEWLRY;COIN.': 53, 'NICKEL AND ARTICLES THEREOF.': 54, 'NUCLEAR REACTORS, BOILERS, MACHINERY AND MECHANICAL APPLIANCES; PARTS THEREOF.': 55, 'OIL SEEDS AND OLEA. FRUITS; MISC. GRAINS, SEEDS AND FRUIT; INDUSTRIAL OR MEDICINAL PLANTS; STRAW AND FODDER.': 56, 'OPTICAL, PHOTOGRAPHIC CINEMATOGRAPHIC MEASURING, CHECKING PRECISION, MEDICAL OR SURGICAL INST. AND APPARATUS PARTS AND ACCESSORIES THEREOF;': 57, 'ORES, SLAG AND ASH.': 58, 'ORGANIC CHEMICALS': 59, 'OTHER BASE METALS; CERMETS; ARTICLES THEREOF.': 60, 'OTHER MADE UP TEXTILE ARTICLES; SETS; WORN CLOTHING AND WORN TEXTILE ARTICLES; RAGS': 61, 'OTHER VEGETABLE TEXTILE FIBRES; PAPER YARN AND WOVEN FABRICS OF PAPER YARN.': 62, 'PAPER AND PAPERBOARD; ARTICLES OF PAPER PULP, OF PAPER OR OF PAPERBOARD.': 63, 'PHARMACEUTICAL PRODUCTS': 64, 'PHOTOGRAPHIC OR CINEMATOGRAPHIC GOODS.': 65, 'PLASTIC AND ARTICLES THEREOF.': 66, 'PREPARATIONS OF CEREALS, FLOUR, STARCH OR MILK; PASTRYCOOKS PRODUCTS.': 67, 'PREPARATIONS OF MEAT, OF FISH OR OF CRUSTACEANS, MOLLUSCS OR OTHER AQUATIC INVERTEBRATES': 68, 'PREPARATIONS OF VEGETABLES, FRUIT, NUTS OR OTHER PARTS OF PLANTS.': 69, 'PREPARED FEATHERS AND DOWN AND ARTICLES MADE OF FEATHERS OR OF DOWN; ARTIFICIAL FLOWERS; ARTICLES OF HUMAN HAIR.': 70, 'PRINTED BOOKDS, NEWSPAPERS, PICTURES AND OTHER PRODUCTS OF THE PRINTING INDUSTRY; MANUSCRIPTS, TYPESCRIPTS AND PLANS.': 71, 'PRODUCTS OF ANIMAL ORIGIN, NOT ELSEWHERE SPECIFIED OR INCLUDED.': 72, 'PRODUCTS OF THE MILLING INDUSTRY; MALT; STARCHES; INULIN; WHEAT GLUTEN.': 73, 'PROJECT GOODS; SOME SPECIAL USES.': 74, 'PULP OF WOOD OR OF OTHER FIBROUS CELLULOSIC MATERIAL; WASTE AND SCRAP OF PAPER OR PAPERBOARD.': 75, 'RAILWAY OR TRAMWAY LOCOMOTIVES, ROLLING-STOCK AND PARTS THEREOF; RAILWAY OR TRAMWAY TRACK FIXTURES AND FITTINGS AND PARTS THEREOF; MECHANICAL': 76, 'RAW HIDES AND SKINS (OTHER THAN FURSKINS) AND LEATHER': 77, 'RESIDUES AND WASTE FROM THE FOOD INDUSTRIES; PREPARED ANIMAL FODER.': 78, 'RUBBER AND ARTICLES THEREOF.': 79, 'SALT; SULPHUR; EARTHS AND STONE; PLASTERING MATERIALS, LIME AND CEMENT.': 80, 'SHIPS, BOATS AND FLOATING STRUCTURES.': 81, 'SILK': 82, 'SOAP, ORGANIC SURFACE-ACTIVE AGENTS, WASHING PREPARATIONS, LUBRICATING PREPARATIONS, ARTIFICIAL WAXES, PREPARED WAXES, POLISHING OR SCOURING PREP.': 83, 'SPECIAL WOVEN FABRICS; TUFTED TEXTILE FABRICS; LACE; TAPESTRIES; TRIMMINGS; EMBROIDERY.': 84, 'SUGARS AND SUGAR CONFECTIONERY.': 85, 'TANNING OR DYEING EXTRACTS; TANNINS AND THEIR DERI. DYES, PIGMENTS AND OTHER COLOURING MATTER; PAINTS AND VER; PUTTY AND OTHER MASTICS; INKS.': 86, 'TIN AND ARTICLES THEREOF.': 87, 'TOBACCO AND MANUFACTURED TOBACCO SUBSTITUTES.': 88, 'TOOLS IMPLEMENTS, CUTLERY, SPOONS AND FORKS, OF BASE METAL; PARTS THEREOF OF BASE METAL.': 89, 'TOYS, GAMES AND SPORTS REQUISITES; PARTS AND ACCESSORIES THEREOF.': 90, 'UMBRELLAS, SUN UMBRELLAS, WALKING-STICKS, SEAT-STICKS, WHIPS,RIDING-CROPS AND PARTS THEREOF.': 91, 'VEGETABLE PLAITING MATERIALS; VEGETABLE PRODUCTS NOT ELSEWHERE SPECIFIED OR INCLUDED.': 92, 'VEHICLES OTHER THAN RAILWAY OR TRAMWAY ROLLING STOCK, AND PARTS AND ACCESSORIES THEREOF.': 93, 'WADDING, FELT AND NONWOVENS; SPACIAL YARNS; TWINE, CORDAGE, ROPES AND CABLES AND ARTICLES THEREOF.': 94, 'WOOD AND ARTICLES OF WOOD; WOOD CHARCOAL.': 95, 'WOOL, FINE OR COARSE ANIMAL HAIR, HORSEHAIR YARN AND WOVEN FABRIC.': 96, \"WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.\": 97, 'ZINC AND ARTICLES THEREOF.': 98}\n" ] } ], "source": [ "commodity_lookup = {}\n", "for hscode, commodity in values:\n", " commodity_lookup[commodity] = hscode\n", " \n", "print(commodity_lookup)" ] }, { "cell_type": "code", "execution_count": 31, "id": "af3d7b47", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "45" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "commodity_lookup['MEAT AND EDIBLE MEAT OFFAL.']" ] }, { "cell_type": "code", "execution_count": 33, "id": "6185674b", "metadata": {}, "outputs": [], "source": [ "# create_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS [Export] (\n", "# [VALUEID] INTEGER NOT NULL PRIMARY KEY,\n", "# [HSCODE] INTEGER NOT NULL, \n", "# [COUNTRY] TEXT NOT NULL,\n", "# [YEAR] INTEGER NOT NULL,\n", "# [VALUE] REAL,\n", "# FOREIGN KEY(COUNTRY) REFERENCES Country(COUNTRY),\n", "# FOREIGN KEY(YEAR) REFERENCES YEAR(YEAR),\n", "# FOREIGN KEY(HSCODE) REFERENCES Commodity(HSCODE)\n", "# );\n", "# \"\"\"\n", "\n", "sql_statement = 'SELECT Commodity, country, year, value from export'\n", "values = execute_sql_statement(sql_statement, conn)\n", "# with conn_norm:\n", "count = 0\n", "with conn_norm:\n", " for value in values:\n", " text = value[0]\n", " hscode = commodity_lookup[text]\n", " insert_tuple = (hscode, value[1], value[2], value[3])\n", " insert_value(conn_norm, insert_tuple)\n" ] }, { "cell_type": "code", "execution_count": 37, "id": "481d58f5", "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", "
HSCODECOMMODITYCOUNTRYYEARVALUE
045MEAT AND EDIBLE MEAT OFFAL.AFGHANISTAN TIS20180.18
128FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUAT...AFGHANISTAN TIS20180.00
221DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDI...AFGHANISTAN TIS201812.48
341LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND ...AFGHANISTAN TIS20180.00
423EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS.AFGHANISTAN TIS20181.89
..................
13701890TOYS, GAMES AND SPORTS REQUISITES; PARTS AND A...ZIMBABWE20100.03
13701951MISCELLANEOUS MANUFACTURED ARTICLES.ZIMBABWE20100.45
13702097WORKS OF ART COLLECTORS' PIECES AND ANTIQUES.ZIMBABWE20100.00
13702174PROJECT GOODS; SOME SPECIAL USES.ZIMBABWE20100.00
13702250MISCELLANEOUS GOODS.ZIMBABWE20100.07
\n", "

137023 rows × 5 columns

\n", "
" ], "text/plain": [ " HSCODE COMMODITY \\\n", "0 45 MEAT AND EDIBLE MEAT OFFAL. \n", "1 28 FISH AND CRUSTACEANS, MOLLUSCS AND OTHER AQUAT... \n", "2 21 DAIRY PRODUCE; BIRDS' EGGS; NATURAL HONEY; EDI... \n", "3 41 LIVE TREES AND OTHER PLANTS; BULBS; ROOTS AND ... \n", "4 23 EDIBLE VEGETABLES AND CERTAIN ROOTS AND TUBERS. \n", "... ... ... \n", "137018 90 TOYS, GAMES AND SPORTS REQUISITES; PARTS AND A... \n", "137019 51 MISCELLANEOUS MANUFACTURED ARTICLES. \n", "137020 97 WORKS OF ART COLLECTORS' PIECES AND ANTIQUES. \n", "137021 74 PROJECT GOODS; SOME SPECIAL USES. \n", "137022 50 MISCELLANEOUS GOODS. \n", "\n", " COUNTRY YEAR VALUE \n", "0 AFGHANISTAN TIS 2018 0.18 \n", "1 AFGHANISTAN TIS 2018 0.00 \n", "2 AFGHANISTAN TIS 2018 12.48 \n", "3 AFGHANISTAN TIS 2018 0.00 \n", "4 AFGHANISTAN TIS 2018 1.89 \n", "... ... ... ... \n", "137018 ZIMBABWE 2010 0.03 \n", "137019 ZIMBABWE 2010 0.45 \n", "137020 ZIMBABWE 2010 0.00 \n", "137021 ZIMBABWE 2010 0.00 \n", "137022 ZIMBABWE 2010 0.07 \n", "\n", "[137023 rows x 5 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "sql_statement = \"\"\"SELECT \n", "export.hscode,\n", "Commodity.COMMODITY,\n", "export.country,\n", "export.year,\n", "export.value\n", "FROM Export\n", "JOIN Commodity ON Commodity.hscode = export.hscode\n", "\"\"\"\n", "df = pd.read_sql_query(sql_statement, conn_norm)\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 46, "id": "c2e8597d", "metadata": {}, "outputs": [], "source": [ "conn_norm.close()" ] }, { "cell_type": "code", "execution_count": 42, "id": "79342f4f", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "270581.031" ] }, "execution_count": 42, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import time\n", "time.monotonic() \n", "\n" ] }, { "cell_type": "code", "execution_count": 44, "id": "bc52be45", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1.5\n" ] } ], "source": [ "\n", "conn_norm = create_connection('india_export_norm.db')\n", "cur = conn_norm.cursor()\n", "cur.execute('DROP TABLE EXPORT')\n", "create_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS [Export] (\n", " [VALUEID] INTEGER NOT NULL PRIMARY KEY,\n", " [HSCODE] INTEGER NOT NULL, \n", " [COUNTRY] TEXT NOT NULL,\n", " [YEAR] INTEGER NOT NULL,\n", " [VALUE] REAL,\n", " FOREIGN KEY(COUNTRY) REFERENCES Country(COUNTRY),\n", " FOREIGN KEY(YEAR) REFERENCES YEAR(YEAR),\n", " FOREIGN KEY(HSCODE) REFERENCES Commodity(HSCODE)\n", ");\n", "\"\"\"\n", "create_table(conn_norm, create_table_sql)\n", "\n", "with conn_norm:\n", " start_time = time.monotonic() \n", " for value in values:\n", " text = value[0]\n", " hscode = commodity_lookup[text]\n", " insert_tuple = (hscode, value[1], value[2], value[3])\n", " insert_value(conn_norm, insert_tuple)\n", "end_time = time.monotonic() \n", "\n", "\n", "print(end_time-start_time)" ] }, { "cell_type": "code", "execution_count": 45, "id": "204fffb2", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[(45, 'AFGHANISTAN TIS', 2018, 0.18), (28, 'AFGHANISTAN TIS', 2018, 0.0), (21, 'AFGHANISTAN TIS', 2018, 12.48), (41, 'AFGHANISTAN TIS', 2018, 0.0), (23, 'AFGHANISTAN TIS', 2018, 1.89), (22, 'AFGHANISTAN TIS', 2018, 25.01), (17, 'AFGHANISTAN TIS', 2018, 13.75), (14, 'AFGHANISTAN TIS', 2018, 0.75), (73, 'AFGHANISTAN TIS', 2018, 0.01), (56, 'AFGHANISTAN TIS', 2018, 10.02), (38, 'AFGHANISTAN TIS', 2018, 0.04), (92, 'AFGHANISTAN TIS', 2018, None), (4, 'AFGHANISTAN TIS', 2018, 0.37), (85, 'AFGHANISTAN TIS', 2018, 15.92), (16, 'AFGHANISTAN TIS', 2018, 0.0), (67, 'AFGHANISTAN TIS', 2018, 0.63), (69, 'AFGHANISTAN TIS', 2018, 5.04), (49, 'AFGHANISTAN TIS', 2018, 3.08), (11, 'AFGHANISTAN TIS', 2018, 0.01), (78, 'AFGHANISTAN TIS', 2018, 0.22), (88, 'AFGHANISTAN TIS', 2018, 91.22), (80, 'AFGHANISTAN TIS', 2018, 0.02), (46, 'AFGHANISTAN TIS', 2018, 0.59), (35, 'AFGHANISTAN TIS', 2018, 1.08), (59, 'AFGHANISTAN TIS', 2018, 7.15), (64, 'AFGHANISTAN TIS', 2018, 80.27), (27, 'AFGHANISTAN TIS', 2018, 0.0), (86, 'AFGHANISTAN TIS', 2018, 0.21), (25, 'AFGHANISTAN TIS', 2018, 5.63), (83, 'AFGHANISTAN TIS', 2018, 0.32), (2, 'AFGHANISTAN TIS', 2018, 0.04), (26, 'AFGHANISTAN TIS', 2018, 0.01), (65, 'AFGHANISTAN TIS', 2018, 0.0), (48, 'AFGHANISTAN TIS', 2018, 1.31), (66, 'AFGHANISTAN TIS', 2018, 2.35), (79, 'AFGHANISTAN TIS', 2018, 8.32), (77, 'AFGHANISTAN TIS', 2018, None), (9, 'AFGHANISTAN TIS', 2018, 0.19), (95, 'AFGHANISTAN TIS', 2018, 0.01), (75, 'AFGHANISTAN TIS', 2018, 0.0), (63, 'AFGHANISTAN TIS', 2018, 2.48), (71, 'AFGHANISTAN TIS', 2018, 0.71), (82, 'AFGHANISTAN TIS', 2018, 0.23), (20, 'AFGHANISTAN TIS', 2018, 2.38), (62, 'AFGHANISTAN TIS', 2018, None), (42, 'AFGHANISTAN TIS', 2018, 46.97), (43, 'AFGHANISTAN TIS', 2018, 39.19), (94, 'AFGHANISTAN TIS', 2018, 0.07), (12, 'AFGHANISTAN TIS', 2018, 0.03), (84, 'AFGHANISTAN TIS', 2018, 1.74), (34, 'AFGHANISTAN TIS', 2018, 0.07), (37, 'AFGHANISTAN TIS', 2018, 3.21), (6, 'AFGHANISTAN TIS', 2018, 43.18), (7, 'AFGHANISTAN TIS', 2018, 89.25), (61, 'AFGHANISTAN TIS', 2018, 21.08), (29, 'AFGHANISTAN TIS', 2018, 18.35), (33, 'AFGHANISTAN TIS', 2018, 0.0), (91, 'AFGHANISTAN TIS', 2018, None), (70, 'AFGHANISTAN TIS', 2018, 0.01), (10, 'AFGHANISTAN TIS', 2018, 0.46), (13, 'AFGHANISTAN TIS', 2018, 0.45), (32, 'AFGHANISTAN TIS', 2018, 0.23), (53, 'AFGHANISTAN TIS', 2018, 2.77), (36, 'AFGHANISTAN TIS', 2018, 0.84), (8, 'AFGHANISTAN TIS', 2018, 61.16), (18, 'AFGHANISTAN TIS', 2018, 0.73), (3, 'AFGHANISTAN TIS', 2018, 38.36), (39, 'AFGHANISTAN TIS', 2018, 0.0), (98, 'AFGHANISTAN TIS', 2018, None), (60, 'AFGHANISTAN TIS', 2018, None), (89, 'AFGHANISTAN TIS', 2018, 2.81), (47, 'AFGHANISTAN TIS', 2018, 1.15), (55, 'AFGHANISTAN TIS', 2018, 10.11), (24, 'AFGHANISTAN TIS', 2018, 13.08), (76, 'AFGHANISTAN TIS', 2018, None), (93, 'AFGHANISTAN TIS', 2018, 16.09), (1, 'AFGHANISTAN TIS', 2018, 0.56), (57, 'AFGHANISTAN TIS', 2018, 7.25), (15, 'AFGHANISTAN TIS', 2018, 0.32), (52, 'AFGHANISTAN TIS', 2018, 0.02), (30, 'AFGHANISTAN TIS', 2018, 0.17), (90, 'AFGHANISTAN TIS', 2018, 0.09), (51, 'AFGHANISTAN TIS', 2018, 1.59), (97, 'AFGHANISTAN TIS', 2018, 0.01), (74, 'AFGHANISTAN TIS', 2018, 0.03), (50, 'AFGHANISTAN TIS', 2018, 0.0), (28, 'ALBANIA', 2018, 0.32), (72, 'ALBANIA', 2018, 0.32), (23, 'ALBANIA', 2018, 0.17), (22, 'ALBANIA', 2018, 0.06), (17, 'ALBANIA', 2018, 4.01), (14, 'ALBANIA', 2018, 0.16), (56, 'ALBANIA', 2018, 0.95), (92, 'ALBANIA', 2018, 0.01), (4, 'ALBANIA', 2018, 0.0), (85, 'ALBANIA', 2018, 0.08), (67, 'ALBANIA', 2018, 0.01), (69, 'ALBANIA', 2018, 0.09), (49, 'ALBANIA', 2018, 0.17), (88, 'ALBANIA', 2018, 0.74)]\n" ] } ], "source": [ "insert_tuples = []\n", "\n", "for value in values:\n", " text = value[0]\n", " hscode = commodity_lookup[text]\n", " insert_tuple = (hscode, value[1], value[2], value[3])\n", " insert_tuples.append(insert_tuple)\n", "print(insert_tuples[:100])" ] }, { "cell_type": "code", "execution_count": 47, "id": "a97d1e3b", "metadata": {}, "outputs": [], "source": [ "\n", "conn_norm = create_connection('india_export_norm.db')\n", "cur = conn_norm.cursor()\n", "cur.execute('DROP TABLE EXPORT')\n", "create_table_sql = \"\"\"CREATE TABLE IF NOT EXISTS [Export] (\n", " [VALUEID] INTEGER NOT NULL PRIMARY KEY,\n", " [HSCODE] INTEGER NOT NULL, \n", " [COUNTRY] TEXT NOT NULL,\n", " [YEAR] INTEGER NOT NULL,\n", " [VALUE] REAL,\n", " FOREIGN KEY(COUNTRY) REFERENCES Country(COUNTRY),\n", " FOREIGN KEY(YEAR) REFERENCES YEAR(YEAR),\n", " FOREIGN KEY(HSCODE) REFERENCES Commodity(HSCODE)\n", ");\n", "\"\"\"\n", "create_table(conn_norm, create_table_sql)\n", "sql = ''' INSERT INTO Export (HSCODE, COUNTRY, YEAR, VALUE)\n", " VALUES(?, ?, ?, ?) '''\n", "cur = conn_norm.cursor()\n", "start_time = time.monotonic() \n", "cur.executemany(sql, insert_tuples)\n", "conn_norm.commit()\n", "end_time = time.monotonic() " ] }, { "cell_type": "code", "execution_count": 48, "id": "70246268", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "0.9219999999622814\n" ] } ], "source": [ "print(end_time-start_time)" ] }, { "cell_type": "code", "execution_count": null, "id": "225df5c7", "metadata": {}, "outputs": [], "source": [ "x = 'abcd'\n", "x.upper" ] }, { "cell_type": "code", "execution_count": null, "id": "a28b1be5", "metadata": {}, "outputs": [], "source": [ "conn_norm.commit" ] }, { "cell_type": "code", "execution_count": null, "id": "626d32cb", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "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.8.12" } }, "nbformat": 4, "nbformat_minor": 5 }