Cómo analizar datos en Google Sheets con Python: guía paso a paso

3. Configura una conexión

Dentro del workbook, haz clic en View > Databases y luego en el icono +. Verás un listado de todas las tecnologías de bases de datos a las que DataLab te permite conectarte:

Selecciona Google Sheets. Ahora se te pedirá conceder a DataCamp permiso para acceder a archivos en tu Google Drive.

Haz clic en «Grant permission to Google Drive». Por último, aparecerá un selector de archivos:

Selecciona el archivo de hoja de cálculo del que quieras leer datos. Puede ser la copia de la hoja de ejemplo que te hemos proporcionado o tu propia hoja de Google.

4. Consulta el archivo de Google Sheets

¡Ya puedes consultar el archivo de Google Sheets que acabas de conectar! Lo harás con una celda SQL, un bloque en DataLab para consultar bases de datos con SQL. Si has creado un workbook a partir de la plantilla, ya tendrás esta celda SQL lista en tu cuaderno. Si empezaste desde cero, puedes añadirla abriendo el launcher y haciendo clic en «SQL»:

En la celda SQL, haz clic en el desplegable Source en la esquina superior izquierda.

Selecciona la hoja que quieres analizar (en el ejemplo es "Unicorn Companies"). Después, escribe la siguiente consulta SQL:

SELECT * FROM unicorn_companies

Si haces clic en Run, esta consulta recuperará todas las filas de la hoja unicorn_companies dentro del Google Sheet Unicorn Companies y guardará el resultado en un nuevo DataFrame de pandas df:

Un DataFrame de pandas es una estructura de datos en forma de tabla que puedes usar en Python para almacenar y manipular datos. Vamos a añadir una celda de Python y ejecutar el siguiente código, que calcula la mediana de la financiación de las empresas unicornio del conjunto de datos por país:

median_funding_df = df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df

En la celda SQL anterior optamos por una consulta sencilla para traer todos los datos, pero también puedes usar una sentencia SQL más elaborada para obtener, por ejemplo, las 10 empresas unicornio más valiosas de Estados Unidos:

SELECT *
FROM unicorn_companies
WHERE country = 'United States'
ORDER BY valuation DESC
LIMIT 10

¡Eso es todo! Para más trucos sobre cómo afinar aún más la consulta SQL, por ejemplo para centrarte en una selección de una hoja, echa un vistazo al artículo correspondiente de la documentación de DataLab.

Como comentábamos antes, esta conexión fluida con Google Sheets solo permite leer datos de una hoja de Google. Si quieres leer y además escribir resultados en Google Sheets, pasa a la siguiente sección, donde explicamos cómo aprovechar la API de Google Sheets para una configuración con la máxima flexibilidad.

Usar la API de Google Sheets

Elige esta opción si quieres leer datos de una hoja de Google y también escribir los resultados de tus cálculos en Python en la misma hoja u otra diferente.

Como se indicó en la introducción, ganar flexibilidad implica una configuración inicial más completa. En concreto, seguiremos estos pasos:

• Configurar una cuenta de servicio de Google
• Crear una hoja de Google con datos
• Crear un nuevo workbook
• Guardar tus credenciales de la cuenta de servicio en DataLab
• Leer datos de Google Sheets
• Analizar los datos en Python
• Escribir los resultados en Google Sheets

Vamos a ver cada uno por separado.

1. Configura una cuenta de servicio de Google

Para acceder por programación a los datos en Google Sheets, necesitas crear una cuenta de servicio de Google, un tipo especial de cuenta que pueden usar los programas para acceder a recursos de Google como una hoja de cálculo. Usarás esta cuenta de servicio para conectar DataLab con Google Sheets.

Solo tienes que configurar esta cuenta de servicio una vez por cada cuenta de Google en la que quieras acceder a hojas; podrás saltarte este paso la próxima vez que trabajes con datos de hojas en la misma cuenta.

Sigue los pasos a continuación para crear la cuenta de servicio y generar las credenciales necesarias.

• Asegúrate de haber iniciado sesión con tu cuenta de Google.
• Ve a la biblioteca de APIs de Google
• Crea un proyecto nuevo haciendo clic en el desplegable de la barra de navegación.
• Busca «Google Sheets API» y actívala. Puede tardar hasta 10 segundos.
• En la barra de la izquierda «APIs y servicios», ve a la pestaña «Credenciales»
• Haz clic en «+ CREATE CREDENTIALS» y selecciona «Service Account»
• En el primer paso (detalles de la cuenta de servicio), indica un nombre para la cuenta de servicio, p. ej., «gsheet-operator», y haz clic en «Create and continue»
• En el segundo paso, selecciona el rol «Owner» y haz clic en «Continue»
• En el tercer paso, no cambies nada y haz clic en «Done»
• De vuelta en la página de credenciales, haz clic en la cuenta de servicio que acabas de crear.
• Ve a la pestaña Keys, haz clic en «Add Key > Create new key»
• Elige «JSON» y luego haz clic en «Create». El archivo JSON con las credenciales de tu cuenta de servicio se descargará automáticamente en tu ordenador.

¡Ya tienes una cuenta de servicio y un archivo JSON de credenciales! Ve a tu carpeta de descargas (o donde se haya descargado el JSON), ábrelo y échale un vistazo. Debería parecerse a esto:

{
  "type": "service_account",
  "project_id": "<your-project-name>",
  "private_key_id": "<something-private>",
  "private_key": "-----BEGIN PRIVATE KEY-----\nM<some-very-private-stuff\n",
  "client_email": "gsheets-operator@steam-verve-386214.iam.gserviceaccount.com",
  "client_id": "123456789012345678901",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/gsheets-operator%40<project-name>.iam.gserviceaccount.com"
}

Verás un campo «client_email»: gsheets-operator@<google-project-name>.iam.gserviceaccount.com. Copia este email en tu portapapeles; lo necesitarás en el siguiente paso.

2. Crea una hoja de Google con datos

Antes de poder analizar datos en hojas de cálculo, asegúrate de tener una hoja con datos. Si no tienes un dataset a mano, puedes empezar desde una hoja de Google que hemos preparado para este tutorial: abre la hoja de ejemplo y, una vez en la hoja, haz clic en «Archivo > Hacer una copia», ponle un nombre y haz clic en «Hacer una copia». Si ya tienes una hoja con datos que quieras analizar, simplemente ábrela.

Tanto si trabajas con un duplicado de la hoja de ejemplo como con tu propia hoja, debes dar acceso a la hoja a la cuenta de servicio de Google que creaste en el primer paso:

• Haz clic en «Compartir»
• Añade como editor el email de la cuenta de servicio que copiaste antes (p. ej., gsheets-operator@<google-project-name>.iam.gserviceaccount.com)
• Haz clic en «Enviar»

Bien: cuenta de servicio, listo. Hoja de Google con los permisos correctos, lista. ¡Hora de escribir algo de Python!

3. Crea un nuevo workbook

Ahora crea un nuevo proyecto de datos dentro de DataLab, también llamado workbook. Puedes crear un workbook vacío desde cero, útil si quieres escribir todo el código tú mismo, o crear un workbook que ya tenga incluido todo el código.

4. Guarda tus credenciales de la cuenta de servicio en DataLab

Para usar el JSON de credenciales de la cuenta de servicio en tu nuevo workbook, necesitas guardarlo en DataLab.

Podrías copiar y pegar el contenido del JSON dentro de una celda de código de tu workbook recién creado:

Este enfoque no es seguro y no se recomienda. Si compartes tu cuaderno con alguien, podrá ver las credenciales en texto claro. Entonces podría suplantarte y acceder a los recursos a los que esa cuenta de servicio tenga acceso.

Además, este enfoque te obligaría a repetir este código en cada workbook que quieras usar para acceder a datos de hojas. Si quieres rotar las credenciales, tendrías que ir editando cada workbook.

Un enfoque más seguro y escalable es guardar el JSON en una variable de entorno. Cuando conectes esta variable, estará disponible en tu sesión de Python. En tu workbook:

• Haz clic en la pestaña «Environment» en el lateral izquierdo
• Haz clic en el icono de suma junto a «Environment Variables»
• En la ventana «Add Environment Variables»:
  • Establece «Name» como GOOGLE_JSON
  • Establece «Value» con el contenido completo del archivo JSON de la cuenta de servicio que se descargó. Puedes hacerlo abriendo el archivo JSON, seleccionándolo todo, copiándolo al portapapeles y pegándolo en el campo Value.
  • Pon «Environment Variables Set Name» como «Google Service Account» (puede ser cualquier nombre)

Tras completar todos los campos, haz clic en «Create», «Next» y, por último, «Connect». Tu sesión de DataLab se reiniciará y GOOGLE_JSON estará disponible como variable de entorno en tu workbook. Puedes comprobarlo creando una celda de Python con el siguiente código y ejecutándola:

import os
google_json = os.environ["GOOGLE_JSON"]
print(google_json)

Si quieres reutilizar las mismas credenciales de la cuenta de servicio en otro workbook, no necesitas volver a configurar la variable de entorno: puedes reutilizarla en otros workbooks.

5. Lee datos de Google Sheets

Vamos a la parte divertida: escribir código en Python para conectar con los datos de Google Sheets.

Instala e importa los paquetes adecuados

Empieza instalando e importando todos los paquetes necesarios para establecer la conexión con tu cuenta de la API de Google. Usaremos google-auth para autenticarnos frente a la API de Google, gspread para interactuar fácilmente con hojas de Google y pandas para manipular datos tabulares en Python.

%%capture
!pip install gspread
from google.oauth2 import service_account
import pandas as pd
import gspread
import json
import os

Configura un cliente con credenciales con alcance

Carga la variable de entorno GOOGLE_JSON y guárdala en una variable:

import os
google_json = os.environ["GOOGLE_JSON"]

Crea un objeto de credenciales a partir del JSON de la cuenta de servicio:

service_account_info = json.loads(google_json)
credentials = service_account.Credentials.from_service_account_info(service_account_info)

Asigna el «alcance» de API correcto a las credenciales para que tengan permisos para interactuar con la hoja de Google:

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds_with_scope = credentials.with_scopes(scope)

Crea un nuevo cliente con estas credenciales con alcance:

client = gspread.authorize(creds_with_scope)

Lee datos de las hojas en un DataFrame

Con el cliente listo, podemos leer los datos de la hoja que creamos antes.

Primero, crea una instancia de hoja a partir de la URL de la hoja de cálculo:

spreadsheet = client.open_by_url('https://docs.google.com/spreadsheets/d/spreadsheet_id/edit#gid=0')

Después, céntrate en la worksheet (la pestaña dentro de la hoja de Google) de la que quieras obtener los datos. Tomemos la primera hoja usando el índice 0:

worksheet = spreadsheet.get_worksheet(0)

Primero, obtén todos los registros como JSON:

records_data = worksheet.get_all_records()

Luego, convierte esos registros JSON en un DataFrame de pandas:

records_df = pd.DataFrame.from_dict(records_data)
records_df

¡Listo!

6. Analiza los datos de las hojas en Python

Ahora puedes usar Python para analizar los datos que has leído de la hoja de Google. El código mínimo de abajo asume que trabajas con la hoja de ejemplo. Si trabajas con otro dataset, estos pasos pueden no servir tal cual. Échale un vistazo al curso Data Manipulation with pandas de DataCamp si quieres profundizar.

Calculemos la financiación mediana por país:

median_funding_df = records_df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df

7. Escribe los resultados en Google Sheets

Si quieres que las personas con las que colaboras vean el fruto de tu trabajo directamente en una hoja, puedes escribir los resultados de tu análisis en Python en una nueva worksheet (es decir, pestaña) dentro de la hoja original.

En este ejemplo, creamos una nueva worksheet median_country si aún no existe y luego insertamos el contenido del DataFrame median_funding_df, incluyendo la fila de cabeceras. ¡Este código se generó con la ayuda del AI Assistant!

# Convierte el DataFrame median_funding_df a una lista de listas
medians_list = median_funding_df.values.tolist()

# Añade la fila de cabeceras a la lista
medians_list.insert(0, median_funding_df.columns.tolist())

# Comprueba si la hoja 'median_by_country' ya existe
titles = [sheet.title for sheet in spreadsheet.worksheets()]
sheet_name = "median_by_country"
if sheet_name not in titles:
    # Crea una nueva worksheet llamada 'median_by_country' en el Google Sheet
    medians_sheet = spreadsheet.add_worksheet(title=sheet_name, rows=len(medians_list), cols=len(medians_list[0]))
else:
    medians_sheet = spreadsheet.worksheet(sheet_name)

# Escribe medians_list en la nueva worksheet
medians_sheet.clear()
medians_sheet.insert_rows(medians_list, row=1)

Si vuelves a tu hoja, verás una nueva pestaña con los datos del DataFrame. Este "escritura de vuelta" de resultados solo es posible si usas el método basado en la API de Google Sheets; no funciona con el conector nativo de Google Sheets de DataCamp presentado al inicio del tutorial.

Conclusión

Según lo que necesites, analizar datos de Google Sheets con Python puede ser muy sencillo o requerir algo más de configuración inicial. Si solo quieres leer datos de una hoja, usa el conector nativo y estarás en marcha en menos de cinco clics. Si necesitas leer y además escribir datos en Google Sheets, compensa habilitar la API de Google Sheets y configurar una cuenta de servicio.

En ambos casos, una vez que todo está bien configurado, acceder a datos desde Google Sheets es casi tan fácil como consultar un archivo CSV. La gran ventaja es que podrás trabajar donde están tus stakeholders: es probable que parte de tu equipo sea fan de las hojas de cálculo.

Se acabaron las exportaciones de CSV interminables: da paso a flujos de trabajo potentes y automáticos para trabajar con datos de Google Sheets sin salir de DataLab.