10. Importar y exportar datos#

En general los datos con los que trabajaremos estarán guardados en disco como archivos o integrados en una base de datos. En esta lección veremos como crear un DataFrame desde

  • Archivos en formato CSV

  • Archivos en formato Microsoft Excel

  • Bases de datos SQL

  • Archivos JSON

Un DataFrame también puede ser exportado a los formatos antes mencionados

10.1. Importar datos tabulares en formato CSV#

Un archivo CSV (Comma-Separated Values) es una tabla en formato texto plano cuyas columnas están separadas por comas (u otro delimitador)

Ejemplo

Consideremos la base de datos “Dow Jones Index” del repositorio UCI, la cual se distribuye en formato CSV

Nota

El Dow Jones es un índice bursatil muy utilizado ya que refleja el comportamiento del mercado accionario norteamericano

Descarguémos la base de datos y observemos las primeras cinco lineas

!wget -cq https://archive.ics.uci.edu/ml/machine-learning-databases/00312/dow_jones_index.zip

SSL_INIT

!unzip -o -q dow_jones_index.zip

!head -5 dow_jones_index.data

quarter,stock,date,open,high,low,close,volume,percent_change_price,percent_change_volume_over_last_wk,previous_weeks_volume,next_weeks_open,next_weeks_close,percent_change_next_weeks_price,days_to_next_dividend,percent_return_next_dividend

1,AA,1/7/2011,$15.82,$16.72,$15.78,$16.42,239655616,3.79267,,,$16.71,$15.97,-4.42849,26,0.182704

1,AA,1/14/2011,$16.71,$16.71,$15.64,$15.97,242963398,-4.42849,1.380223028,239655616,$16.19,$15.79,-2.47066,19,0.187852

1,AA,1/21/2011,$16.19,$16.38,$15.60,$15.79,138428495,-2.47066,-43.02495926,242963398,$15.87,$16.13,1.63831,12,0.189994

1,AA,1/28/2011,$15.87,$16.63,$15.82,$16.13,151379173,1.63831,9.355500109,138428495,$16.18,$17.14,5.93325,5,0.185989

Del archivo CSV podemos ver que cada fila tiene un

  • identificador textual de la acción: AA

  • una fecha de observación: 1/7/2011

  • un precio de apertura, máximo, mínimo y cierre para la fecha: $15.82, $16.72, $15.78, $16.42

  • entre otros

También podemos notar algunos aspectos típicos de los archivos CSV

  • Las columnas están separadas por comas

  • La primera fila del archivo CSV contiene el header, es decir los nombres de las columnas

  • Las columnas son de tipos distintos: ¿Qué tipos puedes identificar en el ejemplo anterior?

A continuación veremos como importar y escribir un archivo CSV usando pandas

Función pd.read_csv

Leer un archivo CSV como DataFrame es directo usando la función read_csv

A continuación se resaltan los argumentos principales

pd.read_csv(
    filepath_or_buffer: Union[str, pathlib.Path, IO[~AnyStr]], # path completo al archivo CSV
    sep=',', # String o expresión regular que se usará para delimitar las columnas
    header='infer', # Puede ser un int (fila donde está el header) o una lista de de int's
    names=None, # Lista de strings con nombres de columnas (útil si el CSV no tiene header)
    index_col=None, # La columna que se usará como header
    usecols=None, # Lista: subconjunto de columnas que se desean importar (por defecto se importan todas)
    converters=None, # Se explica en detalle más adelante junto a otros argumentos de parsing
    parse_dates=None, # Se explica en detalle más adelante junto a otros argumentos de fecha
    ...
    )

Más adelante veremos más argumentos y un ejemplo de uso

Atributo to_csv()

Podemos crear un archivo CSV desde un DataFrame usando el atributo to_csv como se muestra a continuación

    df = pd.DataFrame(data)
    df.to_csv("mis_datos.csv")

  • Esto crea un archivo mis_data.csv en el directorio actual

  • Por defecto guardara las nombres de columna como un header y usará “,” como delimitador

10.2. Análisis sintático o parsing#

En general un archivo de texto plano podría contener

  • valores numéricos continuos

  • valores numéricos discretos

  • fechas

  • coordenadas

  • monedas

  • direcciones

  • etiquetas de texto

  • y un largo etcétera

Los programas que leen e importan archivos de texto plano como CSV deben interpretar estos valores y convertirlos al formato más adecuado, por ejemplo

  • flotante

  • entero

  • booleano

  • string

Se llama parser o analizador sintático al programa que analiza los textos y luego

  • filtra y/o completa los textos invalidos

  • convierte los datos a un formato estándar

Pandas hace este proceso de forma automática y podemos hacer algunos ajustes usando los argumentos disponibles en read_csv

Por ejemplo

pd.read_csv(
    ...
    dtypes=None # Diccionario donde la llave es el nombre de la columna y el valor el tipo requerido
    na_values=None, # String o lista de strings con valores que serán reconocidos como NaN
    decimal='.', # String que se usará para reconocer el punto decimal
    comment=None, # String, todos las lineas que empiezen con este string serán ignoradas
    converters=None # Se explica a continuación
    ...
    )

Si las opciones automáticas no son suficientes se puede hacer parsing en base a reglas manualmente creadas usando el argumento converters

converters recibe un diccionario con “reglas de parseo” con la siguiente sintaxis

    {'nombre de la columna 1': funcion_parseadora1, 
     'nombre de la columna 2': funcion_parseadora2,
     ...
    }

Notar que funcion_parseadoraX puede ser una función explicita o anómina (lambda)

Ejemplo

Los datos de la columna de precio de apertura (open) de “dow_jones_index.data” están formateados como

'$15.84'

que corresponde a un signo dolar seguido de un número real con punto decimal

Para parsear este valor debemos escribir una función que

  1. Elimine el signo dolar del string

  2. Convierta el resto del string en flotante

Por ejemplo

def remove_dollar(text):
    # return float(x[1:]) # Elimina el primer caracter
    return float(x.strip("$")) # Elimina todos los $ del string

Luego agregamos esta función a un diccionario con la llave open y se lo entregamos al argumento converters, es decir

parser = {'open': remove_dollar}

Se puede lograr lo mismo usando una función anónima, por ejemplo:

parser = {'open': lambda x: float(x.strip("$"))}

10.2.1. Interpretación/parseo de fechas#

Un dato textual muy usual en datos tabulares y series de tiempo son las fechas

Sin embargo el formato de fecha puede variar considerablemente entre distintas bases de datos

pandas tiene un tipo denominado Timestamp el cual se puede construir con la función pd.to_datetime() a partir de un string

Pandas identifica automaticamente fechas y horas en distintos formatos

Ejemplo

>>> pd.to_datetime("1/5/2018") # Formato norteamericano Mes/Día/Año 
Timestamp('2018-01-05 00:00:00')

>>> pd.to_datetime("May/1/2018") # También se acepta un string para el mes
Timestamp('2018-05-01 00:00:00')

>>> pd.to_datetime("1st of May of 2018") # También se puede usar una frase "Día del Mes del Año"
Timestamp('2018-05-01 00:00:00')

>>> pd.to_datetime("2018") # Autocompletación por defecto para fechas incompletas
Timestamp('2018-01-01 00:00:00')

>>> pd.to_datetime("14:45") # Si usamos sólo la hora se usa la fecha actual
Timestamp('2020-06-12 14:45:00')

>>> pd.to_datetime("May/1/2018 14:45") # Timestamp completo
Timestamp('2018-05-01 14:45:00')

Podemos controlar el parseo de fechas en read_csv con los argumentos

pd.read_csv(
    ...
    parse_dates=False # Booleano o lista con las columnas que deben ser interpretadas como fechas
    infer_datetime_format=False, # Inferir una función parseadora de forma automática
    dayfirst=False, # Formato día/mes/año o mes/día/año
    date_parser=None, # Función provista por el usuario que toma un string y retorna TimeStamp
    ...
    )

Las fechas/tiempos en formato TimeStamp pueden usarse como índices

Esto nos permite recuperar rapidamente todos los eventos dentro de un intervalo de tiempo

10.3. Importar datos a partir de archivos excel#

Muchas empresas e instituciones manejan sus datos como hojas de cálculo o spreadsheets construidas usando software como Microsoft Excel, Openoffice/Libreoffice calc o Google spreadsheets

pandas permite importar como DataFrame una hoja de cálculo en formatos xls, xlsx, xlsm, xlsb, odf usando la función read_excel

Muchos de los argumentos de read_csv están disponibles en read_excel, los “nuevos” argumentos son

pd.read_excel(io, # string o path a la hoja de cálculo
              sheet_name=0, # Entero, string o lista, especifica la(s) hoja (s) que vamos a importar
              ...
             )

Nota

Para trabajar con archivos excel se requieren algunas librerías adicionales las cuales puede instalarse facilmente con conda

conda install openpyxl

Ejemplo

Consideremos los siguientes datos del censo chileno de 2017 en formato Excel de donde importaremos datos de vivienda por comuna

Esto corresponde a la segunda hoja (sheet_name=1) y en particular las columnas de 1 a 14

Importemos la planilla e inspecciones sus primeras filas

!wget -cq http://www.censo2017.cl/wp-content/uploads/2017/12/Cantidad-de-Viviendas-por-Tipo.xlsx
import pandas as pd
df = pd.read_excel("Cantidad-de-Viviendas-por-Tipo.xlsx", 
                   sheet_name=1, # Importamos la segunda hoja (vivienda)
                   usecols=list(range(1, 14)), # Importamos las columnas 1 a 20
                   header=1, # El header está en la segunda fila
                   skiprows=[2], # Eliminamos la fila 2 ya que es invalida
                   index_col='ORDEN' # Usaremos la columna orden como índice
                  ).dropna() # Eliminamos las filas con NaN

display(df.head())
NOMBRE REGIÓN Código Región NOMBRE PROVINCIA Código Provincia NOMBRE COMUNA Código Comuna Viviendas Particulares Ocupadas con Moradores Presentes Viviendas Particulares Ocupadas con Moradores Ausentes Viviendas Particulares Desocupadas (en Venta, para arriendo, Abandonada u otro) Viviendas Particulares Desocupadas\n(de Temporada) Viviendas Colectivas TOTAL VIVIENDAS
ORDEN
1 ARICA Y PARINACOTA 15.0 ARICA 151.0 ARICA 15101.0 62129.0 4574.0 4045.0 1666.0 225.0 72639.0
2 ARICA Y PARINACOTA 15.0 ARICA 151.0 CAMARONES 15102.0 431.0 96.0 158.0 242.0 21.0 948.0
3 ARICA Y PARINACOTA 15.0 PARINACOTA 152.0 PUTRE 15201.0 540.0 197.0 143.0 995.0 42.0 1917.0
4 ARICA Y PARINACOTA 15.0 PARINACOTA 152.0 GENERAL LAGOS 15202.0 218.0 90.0 162.0 216.0 11.0 697.0
5 TARAPACÁ 1.0 IQUIQUE 11.0 IQUIQUE 1101.0 56007.0 3673.0 5481.0 1564.0 261.0 66986.0

10.4. Consultas a bases de datos SQL#

Pandas es capaz de conectar y hacer consultas en lenguaje SQL a una base de datos externa y retornar el resultado como un DataFrame usando la función read_sql_query

pd.read_sql_query(sql, # Consulta SQL en formato string
                  con, # dirección a la base de datos o objeto de conexión
                  index_col=None, # Selecciona la columna que actuara como índice del DataFrame
                  parse_dates=None, # Igual que read_csv y read_excel
                  ...
                 )

También se puede usar el atributo

df.to_sql(name, # string: el nombre de la tabla
          con, # Engine con conexión
          if_exists: str = 'fail', # Que hacer si la tabla ya existe: fail, replace, append
          index: bool = True, # Escribir el índice del dataframe como columna
          ...
         )

¿Qué es SQL?

Structured Query Languaje (SQL) es un lenguaje estándar ampliamente usado para consultar, crear, modificar y eliminar bases de datos relacionales.

¿Qué es una base de datos relacional?

Es un tipo de base de datos organizada como múltiples tablas. Por ejemplo

id_cliente

nombre

apellido

1

Pablo

Huijse

2

Luis

Alvarez

3

Cristobal

Navarro

CLIENTES

id_orden

platanos

manzanas

id_cliente

1

0

5

1

2

2

2

3

3

3

1

1

ORDENES

  • Las filas se llaman entidades y las columnas atributos

  • Cada tabla tiene una lalve primaria: id_orden e id_cliente

  • La tabla ORDENES está relacionada a la tabla CLIENTES con la llave foranea: id_clientes

  • Las tablas no pueden tener el mismo nombre

¿Dónde corre la base de datos relacional?

La base de datos relacional corre en un sistema de manejo

Algunos ejemplos populares son MySQL, PostgreSQL y SQLite3

Ejemplo básico de una consulta SQL

SQL es un lenguaje de alto nivel. Algunos comandos comunes son

  • SELECT: recuperar un subconjunto de la tabla

  • INSERT: insertar datos en una tabla

  • UPDATE: actualizar datos en una tabla

  • DELETE: eliminar datos de la tabla

La tabla que se quiere manipular se selecciona con el keyword FROM

Se agregan condiciones usando el keyword WHERE

Se puede usar * como alias para “todas las columnas”

Por ejemplo

    SELECT A, B, C FROM mi_tabla WHERE D > 1

Esto recupera las valores de las columnas A, B y C que tegan un valor de la columna D mayor que 1 a partir de la tabla “mi_tabla”

Ejemplo Crear una tabla en SQLite a partir de un dataframe

import sqlite3  # SQLite3 es parte de la librería estándar de Python

# Creamos una base de datos persistente
with sqlite3.connect('censo.db') as conn:

    df.to_sql("censo_viviendas", # Insertamos una tabla llamada censo_viviendos
              conn, # Usamos el objeto conexión que acabos de crear
              if_exists='replace', 
              index=False)

Ejemplo: Obtener un DataFrame a partir de la base de datos SQL anterior con las viviendas ocupadas por comuna de la provincia de Valdivia

Advertencia

Si los nombres de las columnas tienen espacios en blanco debe encerrarlos con paréntesis cuadrados al hacer la consulta

arg1 = "Viviendas Particulares Ocupadas con Moradores Presentes"
arg2 = "NOMBRE COMUNA"
arg3 = "NOMBRE PROVINCIA"
sql_string = f"SELECT [{arg1}], [{arg2}] FROM censo_viviendas WHERE [{arg3}] = 'VALDIVIA'"
display(sql_string)

"SELECT [Viviendas Particulares Ocupadas con Moradores Presentes], [NOMBRE COMUNA] FROM censo_viviendas WHERE [NOMBRE PROVINCIA] = 'VALDIVIA'"

with sqlite3.connect('censo.db') as conn:    
    display(pd.read_sql_query(sql_string, conn))
Viviendas Particulares Ocupadas con Moradores Presentes NOMBRE COMUNA
0 53624.0 VALDIVIA
1 1842.0 CORRAL
2 5643.0 LANCO
3 6446.0 LOS LAGOS
4 2317.0 MÁFIL
5 6808.0 MARIQUINA
6 6476.0 PAILLACO
7 11615.0 PANGUIPULLI

Nota

  • sqlite permite conectar a una base de datos local: RAM, disco, o disco externo montado

  • sqlite no está diseñado para soportar múltiples usuarios conectados a una misma base de datos

  • Otras alternativas: SQL Alchemy, PostgreSQL+Python, Peewee

10.5. Leer y guardar DataFrames en formato JSON#

Podemos usar el atributo to_json para convertir un DataFrame a este formato

df.to_json(
    path_or_buf = None, # Ubicación en disco
    orient = None, # Indica el formato del string JSON
    ...
    )

Por ejemplo

>>> df.to_json("pandas.json", orient='table')

crea un archivo pandas.json en el directorio actual

Luego la función read_json

>>> df = pd.read_json("pandas.json", orient='table')

regenera el DataFrame que teníamos