Tecnologías para el manejo masivo de datos

Durante esta lección vamos trabajar con el dataframe de pandas, y la base de datos relacional sqlite3.

El propósito de esta lección en particular sera, crear un marco de datos, examinaremos sus propiedades, y almacenaremos cada una de las series de datos que conforman al marco de datos como entradas en la base de datos sqlite.

Como primer paso importaremos algunas de las librerías a utilizar.

import pandas as pd
import sqlite3

Crearemos una tabla en la base de datos utilizando la siguiente declaración SQL.

CREATE TABLE stocks(
    date text,
    trans text, 
    symbol text, 
    qty real,
    price real
)

Utilizaremos la variable database_file para indicar que archivo almacena la base de datos que trabajaremos.

La variable create_table_str almacenara la declaración sql de creación de la tabla.

Y abriremos una conexión por medio de un manejador de contexto.


database_file = '/tmp/stocks.db'

create_table_str = "CREATE TABLE stocks( date text, trans text, symbol text, qty real, price real)"

with sqlite3.connect(database_file) as connection:
    connection.execute(create_table_str)

Ahora insertaremos algunas entradas a la tabla stocks de la base de datos.

Para esto desearemos generar datos aleatorios, primero iniciemos definiendo una función que nos permita generar rangos de fechas


from datetime import date, timedelta

def daterange(start, end, step=timedelta(1)):
    curr = start
    while curr < end:
        yield curr
        curr += step

list(daterange(date(2021,1,4), date(2021,4,26)))

Esta función es un generador, dada una entrada de fecha inicial y final nos genera a cada paso de la iteración una fecha separada por el paso indicado.

Ahora definiremos los símbolos que usaremos y los tipos de transacciones permitidas.

Los símbolos fueron tomados de la tabla que se encuentra en el siguiente sitio, utilizando solo la tabla mostrada para la letra A

symbols = ["A","AAC.U","AAI-C","AAN","AAT","ABB","ABC","ABG","ABR","ABR-B","ABT","ACA","ACC","ACEL","ACI","ACI.W","ACM","ACN.U","ACND","ACRE","ADC","ADE.U","ADNT","ADT","AEB","AEFC","AEL","AEL-B","AENZ","AER","AFB","AFG","AFGC","AFGE","AFL","AG","AGCO","AGI","AGM-C","AGM-E","AGM.A","AGO-B","AGO-F","AGRO","AGX","AHH","AHL-C","AHL-E","AHT-D","AHT-G","AHT-I","AIC","AIG","AIG.W","AIO","AIRC","AIV","AIZ","AIZP","AJA.W","AJG","AJX","AKO.A","AKR","AL-A","ALC","ALEX","ALI-A","ALI-E","ALL","ALL-G","ALL-I","ALLE","ALP-Q","ALT-A","ALTG","ALU.W","ALV","AM","AMBC","AMCR","AMG","AMH-D","AMH-F","AMH-H","AMN","AMP","AMR","AMRX","AMWL","AN","ANF","ANH-A","ANH-C","AOD","AON.U","AONE","AP","APD","APH","APO","APO-B","APS.U","APSG","APTS","AQN","AQNB","AR","ARC","ARCO","ARDC","ARE-A","ARG-A","ARGO","ARL","ARMK","AROC","ARR-C","ASA","ASA.W","ASAQ","ASB-C","ASB-E","ASC","ASGI","ASH","ASP.U","ASPL","ASR","AT","ATA.S","ATA.U","ATAC","ATC-D","ATC-G","ATC-I","ATEN","ATH","ATH-B","ATH-D","ATI","ATM.U","ATR","ATUS","AU","AVA","AVA.W","AVAN","AVD","AVLR","AVNT","AVTR","AVYA","AWI","AWP","AX","AXO","AXR","AXS-E","AYI","AZEK","AZRE","AZZ"]
transaction_type = ["BUY", "SELL"] 

Ya tenemos símbolos, ahora necesitamos generar transacciones para la base de datos.

Antes de generar entradas observemos la forma que deben tener los récords.

dummy_records = [
    ('2021-01-01', 'BUY', 'MB', 10, 11.11),
    ('2021-01-01', 'SELL', 'MB', 3, 15.12)
]
insert_stocks_statement = 'INSERT INTO stocks VALUES (?,?,?,?,?)'

with sqlite3.connect(database_file) as connection:
    connection.executemany(insert_stocks_statement, dummy_records)
    records_from_db = connection.execute("select * from stocks").fetchall())

records_from_db

Lo que debemos intentar insertar serán tuplas con los valores específicos para cada campo.

Para ello implementaremos las siguientes funciones donde utilizaremos algunos módulos estándar de python.

from random import choice, randint

start, end = date(2021,1,4), date(2021,4,26)

rand_date = lambda: choice(list(daterange(start,end)))
rand_trans = lambda: choice(transaction_type)
rand_symbol = lambda: choice(symbols)
rand_quantity = lambda: randint(1,51)
rand_price = lambda: randint(0, 10**5) / 100

random_records = []

for n in range(10):
    random_records.append((
        rand_date().strftime("%Y-%m-%d"),
        rand_trans(),
        rand_symbol(),
        rand_quantity(),
        rand_price()
    ))

random_records

Ya tenemos algunas entradas de datos aleatorias, ahora las insertaremos.

insert_stocks_statement = 'INSERT INTO stocks VALUES (?,?,?,?,?)'

with sqlite3.connect(database_file) as connection:
    connection.executemany(insert_stocks_statement, random_records)

Ejercicio, descartar todos los símbolos sitio, y almacenar los datos en una tabla adicional en la misma base de datos.

CREATE TABLE bourse (
    symbol text unique,
    name text
);
CREATE TABLE stocks (
    bourse text,
    symbol text,
    name text
)
CREATE TABLE stock_daily_stats (
    bourse text,
    stock text,
    high real,
    low real,
    close real,
    volumen integer,
    change real,
    change_percent integer
)

Tomando esta estructura ejecutamos las declaraciones para crear tablas.


database_file = '/tmp/real_stocks.db'

create_table_str = [
    "CREATE TABLE bourse ( symbol text unique, name text);",
    "CREATE TABLE stocks ( bourse text, symbol text, name text);",
    "CREATE TABLE stock_daily_stats ( bourse text, stock text, high real, low real, close real, volumen integer, change real, change_percent integer);",
]

with sqlite3.connect(database_file) as connection:
    for s in create_table_str:
        connection.execute(s)
    

Para inspeccionar las tablas que se crearon podemos ejecutar el siguiente query.

with sqlite3.connect(database_file) as connection:
    tables = connection.execute("SELECT * FROM sqlite_master WHERE type='table';").fetchall()

Ahora poblaremos la tabla de bolsas bourse.

Primero obtenemos una lista de bolsas y sus nombres.

from requests_html import HTMLSession
session = HTMLSession()
r = session.get('http://www.eoddata.com/symbols.aspx')
bourse_list = [(option.attrs["value"], option.text) for option in r.html.find("select")[0].find("option")]
bourse_insert_str = "INSERT INTO bourse VALUES (?, ?)"


with sqlite3.connect(database_file) as connection:
    connection.executemany(bourse_insert_str, bourse_list)

Y una vez insertados los registros podemos exportar la información a un marco de datos con pandas de la siguiente manera.

with sqlite3.connect(database_file) as connection:
    df = pd.read_sql("select * from bourse", connection)

df

Ahora buscaremos por bolsa cada una de las acciones que se intercambia en ella, su nombre y su símbolo, para ello haremos webscrapping de tablas que se encuentran en dicha pagina.

Explorando el sitio observamos que las tablas donde esta la información deseada son con el selector css table.quotes, mas específicamente la primer tabla que encontramos con ese selector.

Las tablas se encuentran paginadas por orden alfabético, de manera que el URL resulta ser

http://www.eoddata.com/stocklist/{BOLSA}/{LETRA}.htm

Donde LETRA puede ser un valor numérico, o un símbolo del abecedario.

Construimos una lista de posibles valores para letra de la siguiente manera.

Y una función que decodifique la tabla que deseamos.

pos_letras = [*[str(i) for i in range(1,10)], *[chr(65 + i) for i in range(26)]]

def scrape_table(bolsa, letra):
    session = HTMLSession()
    r = session.get(f"http://www.eoddata.com/stocklist/{bolsa}/{letra}.htm")
    rows = r.html.find("table.quotes")[0].find("tr")
    heading_row = rows[0]
    rows = rows[1:]
    headers = [cell.text for cell in heading_row.find("th") if cell.text != ""]
    headers.append("Change (%)")
    data = []
    for row in rows:
        data.append(dict(zip(headers, [cell.text for cell in row.find("td") if cell.text != ""])))
    return data 

scrape_table("AMEX", "A")