# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: 'Python 3.8.3 64-bit (''base'': conda)'
#     name: python383jvsc74a57bd049d2ad84b55b4dd13fa90d845855b9525e9919441b5a45aaf0c8ce322013341e
# ---

# + id="cZDO5T067Tru"
import pandas as pd
import sqlite3 as sql
import numpy as np
import os
import glob

import sqlite3
from sqlite3 import Error
# -

# # Downloading Zipped Data From Github

data_path = '../data/raw/owid-datasets-master/datasets/'
data_path = os.path.realpath(data_path)
folders = os.listdir(data_path)

os.listdir()

# Below may not work if Windows 10 Long path name not enabled. Otherwise Windows cannot open files larger than 260 charachters. One can alternatively construct the SQL Database at this step, likely more effient.

titles = []
dfs = []
for folder in folders[:]:
    rel_path = os.path.join(data_path, folder)
    for file in os.listdir(rel_path):
        if file.endswith('.csv'):
            final_path = os.path.join(rel_path, file)
            df = pd.read_csv(final_path)
            dfs.append(df)
            titles.append(folder)

# ## Using SQL

# + tags=[]
OWID_db = '../data/raw/owid-datasets-master/'+ "OWID.db"
conn = sqlite3.connect(OWID_db)
for title, df in zip(titles, dfs):
  df.to_sql(title, conn, index=False)
conn.close()
# -

conn = sqlite3.connect(OWID_db)
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cursor.fetchall()

pd.read_sql( 'SELECT * FROM "%s" '  % (tables[-1][0]), conn)

# + [markdown] id="G0C5HW2lEPR-"
# # Scrapping Data from Github OWID Datasets

# + id="Qx1gTeSG8Stc"
import bs4
import requests

# + id="8kQQtiPu8Yml"
url = "https://github.com/owid/owid-datasets/tree/master/datasets"

webpage = requests.get(url).text

# + id="W-AXmUoF9LxL"
soup = bs4.BeautifulSoup(webpage)

# + id="aSP0mIXB9Xww"
results = soup.find_all('a', {'class':'js-navigation-open Link--primary'})

links =[]
titles = []
for result in results:
  rel_short_link = result['href'].split('/')[-1]
  data_url = 'https://raw.githubusercontent.com/owid/owid-datasets/master/datasets/' + rel_short_link+ '/'+rel_short_link+'.csv'
  
  titles.append(result['title'])
  links.append(data_url) 
# class="js-navigation-open Link--primary"

# + id="rGQ-cRXIBqSx"
df = pd.read_csv(links[-1])

# + colab={"base_uri": "https://localhost:8080/"} id="hUgTfW-2Spn3" outputId="1b61f0d2-b7e8-4d5b-d4c2-b1c4d9249207"
titles[:5]

# + [markdown] id="f6aYNSPQEY4e"
# ## Using SQL
#
# -

OWID_db = '../data/raw/owid-datasets-master/'+ "OWID.db"


# + colab={"base_uri": "https://localhost:8080/"} id="ElS6OxTqSwJn" outputId="84c4c93a-eebe-41ef-febf-41ffa492b3c9" tags=["outputPrepend"]
OWID_db = '../data/raw/owid-datasets-master/'+ "OWID.db"

conn = sqlite3.connect(OWID_db)
for title, link in zip(titles, links):
  df = pd.read_csv(link)
  df.to_sql(title, conn, index=False)
conn.close()

# + id="7coEV9z9TPEe"
conn = sqlite3.connect(OWID_db)
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cursor.fetchall()

# + colab={"base_uri": "https://localhost:8080/"} id="-9vOklfxVZKk" outputId="1f28f35e-24f8-447d-ef55-25dcb8c16db8"
len(tables)

# + colab={"base_uri": "https://localhost:8080/", "height": 419} id="WgyR2PLlYshT" outputId="47fb38b6-13ea-4d42-e3b4-c998e72d82da"
pd.read_sql( 'SELECT * FROM "%s" '  % (tables[-1][0]), conn)

# + id="SpMbZvO0btSf"


# + id="kyrOQazdbtPw"


# + id="nIIvoTDnQ-Fz"


StarcoderdataJupyterScriptsDedupFiltered