Come analizzare i dati in Google Sheets con Python: guida passo passo

3. Configura una connessione

All’interno di un workbook, clicca su View > Databases e poi sull’icona +. Ti verrà mostrata una panoramica di tutte le tecnologie di database a cui DataLab consente di connetterti:

Seleziona Google Sheets. Ora ti verrà chiesto di concedere a DataCamp l’autorizzazione ad accedere ai file nel tuo Google Drive.

Clicca su “Concedi autorizzazione a Google Drive”. Infine, apparirà un selettore di file:

Seleziona il file Spreadsheet da cui vuoi leggere i dati. Può essere la copia del Google Sheet di esempio che abbiamo fornito oppure un tuo Google Sheet.

4. Interroga il file di Google Sheets

Ora puoi interrogare il file di Google Sheets a cui ti sei appena connesso! Lo farai con una cella SQL, un blocco di base in DataLab per interrogare database con SQL. Se hai creato un workbook dal template fornito, avrai già questa cella SQL pronta nel tuo notebook. Se hai iniziato un workbook da zero, puoi aggiungerla aprendo il launcher e cliccando su “SQL”:

Nella cella SQL, clicca sul menu a tendina Source nell’angolo in alto a sinistra.

Seleziona il foglio di calcolo che vuoi analizzare (nell’esempio è "Unicorn Companies"). Poi scrivi la seguente query SQL:

SELECT * FROM unicorn_companies

Se clicchi su Run, questa query recupererà tutte le righe del foglio unicorn_companies all’interno del Google Sheet Unicorn Companies e salverà il risultato in un nuovo DataFrame Pandas df:

Un DataFrame pandas è una struttura di dati simile a una tabella che puoi usare in Python per archiviare e manipolare dati. Aggiungiamo una cella Python ed eseguiamo il seguente codice, che calcola il finanziamento mediano per le aziende unicorno nel dataset, per paese:

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

Nella cella SQL sopra abbiamo usato una query semplice per prendere tutti i dati, ma puoi anche usare un’istruzione SQL più sofisticata per recuperare, ad esempio, le 10 aziende unicorno più preziose negli Stati Uniti:

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

Tutto qui! Per altri suggerimenti su come perfezionare ulteriormente la query SQL, ad esempio per focalizzarti su una selezione di un foglio, dai un’occhiata all’articolo di documentazione di DataLab corrispondente.

Come accennato, questa connessione fluida con Google Sheets può essere usata solo per leggere i dati da un Google Sheet. Se vuoi sia leggere i dati sia scrivere i risultati su Google Sheets, passa alla sezione successiva, che spiega come sfruttare la Google Sheets API per una configurazione massimamente flessibile.

Usare la Google Sheets API

Scegli questa opzione se vuoi sia leggere i dati da un Google Sheet sia scrivere il risultato dei tuoi calcoli in Python sullo stesso o su un altro Google Sheet.

Come detto nell’introduzione, l’aumento di flessibilità ha il costo di una configurazione iniziale più articolata. In particolare, i passaggi saranno:

• Configurare un Google Service Account
• Creare un foglio Google con dati
• Creare un nuovo workbook
• Archiviare le credenziali del service account in DataLab
• Leggere i dati di Google Sheets
• Analizzare i dati in Python
• Scrivere i risultati su Google Sheets

Vediamoli uno per uno.

1. Configura un Google Service Account

Per accedere in modo programmatico ai dati in Google Sheets, devi creare un Google service account, un tipo speciale di account utilizzabile dai programmi per accedere a risorse Google come un foglio di calcolo. Userai poi questo service account per connettere DataLab a Google Sheets.

Devi impostare questo service account una sola volta per ogni account Google in cui vuoi accedere ai fogli di calcolo; potrai saltare questo passaggio la prossima volta che lavorerai con i dati dei fogli nello stesso account.

Segui i passaggi qui sotto per creare il service account e generare le credenziali necessarie.

• Assicurati di aver effettuato l’accesso con il tuo account Google.
• Vai alla Google API Library
• Crea un nuovo progetto cliccando sul menu a tendina nella barra di navigazione.
• Cerca “Google Sheets API” e abilitala. Può richiedere fino a 10 secondi.
• Nella barra laterale “API e servizi”, vai alla scheda “Credenziali”.
• Clicca su “+ CREA CREDENZIALI” e seleziona “Service Account”.
• Nel primo passaggio (dettagli del service account), fornisci un nome per il service account, ad esempio “gsheet-operator”, e clicca su “Crea e continua”.
• Nel secondo passaggio, seleziona il ruolo “Owner” e clicca su “Continua”.
• Nel terzo passaggio, non cambiare nulla e clicca su “Fine”.
• Tornato nella pagina Credenziali, clicca sul service account appena creato.
• Vai alla scheda Keys, clicca su “Add Key > Create new key”.
• Scegli “JSON”, poi clicca su “Create”. Il file JSON con le credenziali del tuo service account verrà scaricato automaticamente sul tuo computer.

Ora hai un service account e un file di credenziali JSON! Vai nella cartella Download o dove è stato scaricato il file JSON, aprilo e dai un’occhiata. Dovrebbe essere simile a questo:

{
  "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"
}

C’è un campo “client_email”: gsheets-operator@<google-project-name>.iam.gserviceaccount.com. Copia questa email negli appunti: ti servirà nel passaggio successivo.

2. Crea un foglio Google con dati

Prima di poter analizzare i dati nei fogli di calcolo, devi assicurarti di avere un foglio con dei dati. Se non hai un dataset a portata di mano, puoi partire da un Google Sheet che abbiamo preparato per questo tutorial: apri il foglio di esempio e, una volta nel Google Sheet, clicca su “File > Crea una copia”, specifica un nome e clicca su “Crea una copia”. Se hai già un foglio con i dati che vuoi analizzare, apri semplicemente quel foglio.

Che tu stia lavorando con una copia del foglio di esempio o con un tuo foglio, devi concedere al Google service account creato nel primo passaggio l’accesso al foglio:

• Clicca su “Condividi”
• Aggiungi l’email del service account che hai copiato negli appunti nel passaggio precedente come editor del foglio (ad es. gsheets-operator@<google-project-name>.iam.gserviceaccount.com)
• Clicca su “Invia”

Perfetto: service account, fatto. Foglio Google con i permessi giusti, fatto. È il momento di scrivere un po’ di codice Python!

3. Crea un nuovo workbook

Il passo successivo è creare un nuovo progetto dati in DataLab, chiamato anche workbook. Puoi creare un workbook vuoto da zero, utile se vuoi scrivere tutto il codice da solo, oppure creare un workbook con tutto il codice incluso.

4. Archivia le credenziali del service account in DataLab

Per usare il JSON delle credenziali del service account nel tuo nuovo workbook, devi salvarlo in DataLab.

Potresti copiare e incollare il contenuto del JSON dentro una cella di codice del tuo nuovo workbook:

Questo però non è un approccio sicuro e non è consigliato. Se condividi il notebook con qualcun altro, potrà vedere le credenziali in chiaro. Potrebbe quindi impersonarti e ottenere accesso alle risorse a cui questo service account ha accesso.

Questo approccio inoltre ti costringerebbe a ripetere questo codice in ogni workbook che vuoi usare per accedere ai dati dei fogli. Se vuoi ruotare le credenziali, dovrai aprire e modificare ogni workbook.

Un approccio più sicuro e scalabile è salvare il JSON in una variabile d’ambiente. Quando connetti questa variabile, sarà disponibile nella tua sessione Python. Nel tuo workbook:

• Clicca sulla scheda “Environment” a sinistra
• Clicca sull’icona più accanto a “Environment Variables”
• Nella finestra “Add Environment Variables”:
  • Imposta “Name” su GOOGLE_JSON
  • Imposta “Value” con l’intero contenuto del file JSON del service account scaricato. Puoi farlo aprendo il file JSON, selezionando tutto, copiandolo negli appunti e incollandolo nel campo Value.
  • Imposta “Environment Variables Set Name” su “Google Service Account” (può essere qualunque nome)

Dopo aver compilato tutti i campi, clicca su “Create”, “Next” e infine “Connect”. La tua sessione DataLab si riavvierà e GOOGLE_JSON sarà ora disponibile come variabile d’ambiente nel tuo workbook. Puoi verificarlo creando una cella Python con il seguente codice ed eseguendola:

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

Se vuoi riutilizzare le stesse credenziali del service account in un altro workbook, non devi reimpostare la variabile d’ambiente: puoi riutilizzarla negli altri workbook.

5. Leggi i dati di Google Sheets

Ora viene il bello: scrivere il codice Python per connettersi ai dati in Google Sheets.

Installa e importa i pacchetti giusti

Inizia installando e importando tutti i pacchetti necessari per configurare la connessione al tuo account Google API. Useremo google-auth per l’autenticazione verso le API di Google, gspread per interfacciarci facilmente con i fogli Google e pandas per manipolare dati tabellari in Python.

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

Configura un client con credenziali e scope

Carica la variabile d’ambiente GOOGLE_JSON e salvala in una variabile:

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

Crea un oggetto credenziali a partire dal JSON del service account:

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

Assegna alle credenziali il giusto ‘scope’ delle API, così da avere i permessi corretti per interagire con il foglio Google:

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

Crea un nuovo client con queste credenziali con scope:

client = gspread.authorize(creds_with_scope)

Leggi i dati dei fogli in un DataFrame

Con il client pronto, possiamo leggere i dati dal foglio creato in precedenza.

Per prima cosa, crea una nuova istanza di sheet a partire dall’URL del foglio di calcolo:

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

Poi focalizzati sul worksheet (la scheda nel foglio Google) da cui vuoi ottenere i dati. Prendiamo il primo foglio usando l’indice 0:

worksheet = spreadsheet.get_worksheet(0)

Per prima cosa, recupera tutti i record come JSON:

records_data = worksheet.get_all_records()

Poi converti i record JSON in un DataFrame pandas:

records_df = pd.DataFrame.from_dict(records_data)
records_df

Fatto!

6. Analizza i dati dei fogli in Python

Ora puoi usare Python per analizzare i dati letti dal foglio Google. Il codice minimo qui sotto presuppone che tu stia lavorando con il foglio di esempio. Se stai lavorando con un altro dataset, questi passaggi potrebbero non funzionare. Dai un’occhiata al corso Data Manipulation with pandas di DataCamp se vuoi approfondire!

Calcoliamo il finanziamento mediano per paese:

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

7. Scrivi i risultati su Google Sheets

Se vuoi che gli utenti del foglio con cui collabori vedano i frutti del tuo lavoro in un foglio di calcolo, puoi scrivere i risultati della tua analisi Python in un nuovo worksheet (cioè una scheda) all’interno del foglio originale.

In questo esempio creiamo un nuovo worksheet median_country se non esiste ancora, quindi inseriamo il contenuto del DataFrame median_funding_df, includendo l’intestazione con i nomi delle colonne. Questo codice è stato generato con l’aiuto dell’AI Assistant!

# Convert the median_funding_df DataFrame to a list of lists
medians_list = median_funding_df.values.tolist()

# Add the header row to the list
medians_list.insert(0, median_funding_df.columns.tolist())

# Check if the 'median_by_country' sheet already exists
sheet_titles = [sheet.title for sheet in spreadsheet.worksheets()]
sheet_name = "median_by_country"
if sheet_name not in sheet_titles:
    # Create a new worksheet named 'median_by_country' in the 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)

# Write the medians_list to the new worksheet
medians_sheet.clear()
medians_sheet.insert_rows(medians_list, row=1)

Se visiti di nuovo il tuo foglio, vedrai un nuovo worksheet con i dati del DataFrame! Questo “write-back” dei risultati è possibile solo se usi la modalità di connessione basata sulla Google Sheets API; non è disponibile con il connettore integrato di Google Sheets di DataCamp introdotto prima nel tutorial.

Conclusioni

A seconda delle tue esigenze, analizzare i dati in Google Sheets con Python può essere immediato o richiedere una certa configurazione iniziale. Se vuoi solo leggere i dati da un foglio, usa il connettore integrato e in meno di cinque clic sei operativo. Se vuoi sia leggere sia scrivere dati su Google Sheets, conviene abilitare la Google Sheets API e impostare un service account.

In entrambi i casi, una volta configurato tutto correttamente, accedere ai dati da Google Sheets è quasi facile quanto interrogare un file CSV. Il grande vantaggio è che puoi incontrare gli stakeholder dove si trovano: è probabile che alcuni dei tuoi colleghi giurino sui fogli di calcolo.

Basta con i tempi delle esportazioni CSV laboriose: benvenuti ai flussi di lavoro potenti e automatici per lavorare con i dati di Google Sheets senza mai lasciare DataLab.