Data in Google Sheets analyseren met Python: een stapsgewijze gids

3. Stel een verbinding in

Klik binnen een workbook op View > Databases en klik op het +-icoon. Je krijgt nu een overzicht van alle databasetechnologieën waarmee DataLab verbinding kan maken:

Selecteer Google Sheets. Je wordt nu gevraagd DataCamp toestemming te geven om bestanden in je Google Drive te openen.

Klik op “Grant permission to Google Drive.” Tot slot verschijnt er een bestandskiezer:

Selecteer het Spreadsheet-bestand waarvan je data wilt lezen. Dit is ofwel de kopie van de voorbeeld-Google Sheet die we hebben voorzien, of je eigen Google Sheet.

4. Query het Google Sheets-bestand

Je kunt nu het Google Sheets-bestand waar je net verbinding mee hebt gemaakt bevragen! Dit doe je met een SQL-cel, een bouwsteen in DataLab om databases met SQL te bevragen. Als je een workbook hebt gemaakt vanuit de meegeleverde template, staat deze SQL-cel al klaar in je notebook. Ben je vanaf nul begonnen, dan kun je er een toevoegen via de launcher en te klikken op “SQL”:

Klik in de SQL-cel zelf op de Source-dropdown linksboven.

Selecteer de spreadsheet die je wilt analyseren (in het voorbeeld is dat "Unicorn Companies"). Schrijf vervolgens de volgende SQL-query:

SELECT * FROM unicorn_companiesMogelijk gemaakt door

Als je op Run klikt, haalt deze query alle rijen op uit het blad unicorn_companies in de Google Sheet Unicorn Companies en slaat het resultaat op in een nieuwe pandas DataFrame df:

Een pandas DataFrame is een tabelachtige datastructuur die je in Python kunt gebruiken om data op te slaan en te manipuleren. Laten we een Python-cel toevoegen en de volgende code uitvoeren, die de mediane financiering voor de unicorn-bedrijven in de dataset per land berekent:

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

In de SQL-cel hierboven kozen we voor een eenvoudige SQL-query om alle data op te halen, maar je kunt net zo goed een meer geavanceerde SQL-statement gebruiken om bijvoorbeeld de 10 meest waardevolle unicorn-bedrijven in de Verenigde Staten op te halen:

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

Dat is alles! Voor meer pro-tips over hoe je de SQL-query verder kunt fine-tunen, bijvoorbeeld om in te zoomen op een selectie van een sheet, bekijk het bijbehorende DataLab-documentatieartikel.

Zoals eerder vermeld kan deze naadloze verbinding met Google Sheets alleen worden gebruikt om data uit een Google Sheet te lezen. Wil je zowel data lezen uit als resultaten terugschrijven naar Google Sheets, kijk dan in de volgende sectie waarin wordt uitgelegd hoe je de Google Sheets API inzet voor een maximaal flexibele setup.

De Google Sheets API gebruiken

Kies deze optie als je zowel data uit een Google Sheet wilt lezen als de resultaten van je berekeningen in Python wilt terugschrijven naar dezelfde of een andere Google Sheet.

Zoals in de introductie vermeld, gaat de toegenomen flexibiliteit ten koste van een wat uitgebreidere setup. Concreet nemen we de volgende stappen:

• Een Google Service Account configureren
• Een Google-spreadsheet met data maken
• Een nieuw workbook maken
• Je serviceaccount-gegevens in DataLab opslaan
• Google Sheets-data lezen
• De data in Python analyseren
• Resultaten naar Google Sheets schrijven

Laten we ze stuk voor stuk doornemen.

1. Configureer een Google Service Account

Om programmatisch toegang te krijgen tot data in Google Sheets, moet je een Google serviceaccount aanmaken. Dat is een speciaal type account dat door programma’s kan worden gebruikt om toegang te krijgen tot Google-resources zoals een spreadsheet. Je gebruikt dit serviceaccount vervolgens om DataLab te verbinden met Google Sheets.

Je hoeft dit Google serviceaccount maar één keer per Google-account in te richten waarin je spreadsheets wilt benaderen; de volgende keer dat je met spreadsheetdata in hetzelfde account werkt, kun je deze stap overslaan.

Volg de onderstaande stappen om het serviceaccount te maken en de benodigde credentials te genereren.

• Zorg dat je bent ingelogd met je Google-account.
• Navigeer naar de Google API Library
• Maak een nieuw project door te klikken in de dropdown in de navigatiebalk.
• Zoek naar de “Google Sheets API” en schakel deze in. Dit kan tot 10 seconden duren.
• Ga in de navigatiebalk “APIs and services” links naar het tabblad “Credentials”
• Klik op “+ CREATE CREDENTIALS” en selecteer “Service Account”
• Geef in de eerste stap (service account details) een naam aan het serviceaccount, bijv. “gsheet-operator”, en klik op “Create and continue”
• Selecteer in de tweede stap de rol “Owner” en klik op “Continue”
• Wijzig in de derde stap niets en klik op “Done”
• Terug op de pagina Credentials, klik op het serviceaccount dat je zojuist hebt gemaakt.
• Ga naar het tabblad Keys, klik op “Add Key > Create new key”
• Kies “JSON” en klik vervolgens op “Create.” Het JSON-bestand met je serviceaccount-credentials wordt automatisch naar je computer gedownload.

Je hebt nu een serviceaccount en een JSON-credentialsbestand! Ga naar je downloadmap of waar het JSON-bestand is opgeslagen, open het en kijk even. Het zou er ongeveer zo uit moeten zien:

{
  "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": "[email protected]",
  "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"
}
Mogelijk gemaakt door

Daar staat een veld “client_email” in: gsheets-operator@<google-project-name>.iam.gserviceaccount.com. Kopieer dit e-mailadres naar je klembord; je hebt het in de volgende stap nodig.

2. Maak een Google-spreadsheet met data

Voordat je data in spreadsheets kunt analyseren, moet je zorgen dat je een spreadsheet met data hebt. Als je geen dataset bij de hand hebt, kun je beginnen met een Google Sheet die we voor deze tutorial hebben voorbereid: open de voorbeeldspreadsheet en klik in de Google Sheet op “File > Make a copy”, geef een naam op en klik op “Make a copy.” Als je al een spreadsheet met data hebt die je wilt analyseren, open die spreadsheet dan gewoon.

Of je nu werkt met een duplicaat van de voorbeeldspreadsheet of met je eigen spreadsheet, je moet het Google serviceaccount dat je in de eerste stap hebt gemaakt toegang geven tot de spreadsheet:

• Klik op “Share”
• Voeg het e-mailadres van het serviceaccount dat je in de vorige stap naar je klembord hebt gekopieerd toe als editor van de spreadsheet (bijv. gsheets-operator@<google-project-name>.iam.gserviceaccount.com)
• Klik op “Send”

Oké, serviceaccount: check. Google-spreadsheet met de juiste toegang: check. Tijd om Python-code te schrijven!

3. Maak een nieuw workbook

Vervolgens maak je een nieuw dataproject in DataLab, ook wel een workbook genoemd. Je kunt ofwel een leeg workbook vanaf nul maken, handig als je alle code zelf wilt schrijven, of een workbook maken waarin alle code al is opgenomen.

4. Sla je serviceaccount-credentials op in DataLab

Om het JSON-bestand met serviceaccount-credentials in je nieuw aangemaakte workbook te gebruiken, moet je het in DataLab opslaan.

Je zou de inhoud van het JSON-bestand kunnen kopiëren en plakken in een codecel van je nieuw aangemaakte workbook:

Dit is niet veilig en wordt afgeraden. Als je je notebook met iemand anders deelt, kan diegene de credentials in platte tekst zien. Ze zouden je dan kunnen imiteren en toegang krijgen tot resources waartoe dit serviceaccount toegang heeft.

Deze aanpak vereist ook dat je deze code herhaalt in elk workbook waarmee je toegang tot spreadsheetdata wilt. Als je de credentials wilt roteren, moet je elk workbook nalopen en bewerken.

Een veiligere en schaalbaardere aanpak is om het JSON-bestand op te slaan in een zogeheten omgevingsvariabele. Wanneer je deze omgevingsvariabele koppelt, wordt deze beschikbaar in je Python-sessie. In je workbook:

• Klik op het tabblad “Environment” aan de linkerkant
• Klik op het plus-icoon naast “Environment Variables”
• In de “Add Environment Variables”-modal:
  • Stel “Name” in op GOOGLE_JSON
  • Stel “Value” in op de volledige inhoud van het gedownloade JSON-bestand met serviceaccount-credentials. Dit doe je door het JSON-bestand te openen, alles te selecteren, te kopiëren naar je klembord en vervolgens te plakken in het Value-veld.
  • Stel “Environment Variables Set Name” in op “Google Service Account” (dit kan in principe van alles zijn)

Klik na het invullen op “Create”, “Next” en tot slot “Connect.” Je DataLab-sessie wordt herstart en GOOGLE_JSON is nu beschikbaar als omgevingsvariabele in je workbook. Je kunt dit verifiëren door een Python-cel met de volgende code te maken en uit te voeren:

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

Als je dezelfde serviceaccount-credentials in een ander workbook wilt hergebruiken, hoef je de omgevingsvariabele niet opnieuw in te stellen: je kunt de omgevingsvariabele in andere workbooks hergebruiken.

5. Lees Google Sheets-data

Nu komt het leuke deel: Python-code schrijven om verbinding te maken met de Google Sheets-data.

Installeer en importeer de juiste packages

Begin met het installeren en importeren van alle packages die nodig zijn om een verbinding met je Google API-account op te zetten. We gebruiken google-auth om te authenticeren tegen de Google API, gspread om eenvoudig met Google-spreadsheets te werken en pandas om tabeldata in Python te manipuleren.

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

Stel een client in met gescope credentials

Laad de omgevingsvariabele GOOGLE_JSON en sla deze op in een variabele:

import os
google_json = os.environ["GOOGLE_JSON"]Mogelijk gemaakt door

Maak een credentials-object van de serviceaccount-JSON:

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

Ken de juiste ‘API scope’ toe aan de credentials zodat deze de juiste permissies heeft om met de Google Sheet-spreadsheet te communiceren:

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

Maak een nieuwe client met deze gescope credentials:

client = gspread.authorize(creds_with_scope)Mogelijk gemaakt door

Lees spreadsheetdata in een DataFrame

Met de client klaar kunnen we de data lezen uit de spreadsheet die we eerder hebben gemaakt.

Maak eerst een nieuwe sheet-instantie vanuit een spreadsheet-URL:

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

Zoom vervolgens in op het werkblad (de tab in de Google-spreadsheet) waarvan je de data wilt ophalen. Laten we het eerste blad pakken met index 0:

worksheet = spreadsheet.get_worksheet(0)
Mogelijk gemaakt door

Haal eerst alle records op als JSON:

records_data = worksheet.get_all_records()
Mogelijk gemaakt door

Zet de JSON-records vervolgens om in een pandas DataFrame:

records_df = pd.DataFrame.from_dict(records_data)
records_df
Mogelijk gemaakt door

Gelukt!

6. Analyseer de spreadsheetdata in Python

Je kunt nu Python gebruiken om de data te analyseren die je uit de Google-spreadsheet hebt ingelezen. De minimale code hieronder gaat ervan uit dat je met de voorbeeldspreadsheet werkt. Als je met een andere dataset werkt, werken deze stappen niet. Bekijk DataCamp’s Data Manipulation met pandas-cursus als je dieper wilt duiken!

Laten we de mediane financiering per land berekenen:

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

7. Schrijf de resultaten naar Google Sheets

Als je wilt dat de spreadsheetgebruikers met wie je samenwerkt de vruchten van je werk in een spreadsheet zien, kun je de resultaten van je Python-analyse terugschrijven naar een nieuw Google-werkblad (oftewel tab) binnen de oorspronkelijke spreadsheet.

In dit voorbeeld maken we een nieuw werkblad median_country als het nog niet bestaat en voegen we vervolgens de inhoud van de DataFrame median_funding_df in, inclusief de header met kolomnamen. Deze code is gemaakt met hulp van de 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)Mogelijk gemaakt door

Als je je spreadsheet opnieuw bezoekt, zie je een nieuw werkblad met de data uit de DataFrame! Dit terugschrijven van resultaten is alleen mogelijk als je de op de Google Sheets API gebaseerde manier gebruikt om verbinding te maken met Google Sheets; niet als je de ingebouwde Google Sheets-connector van DataCamp gebruikt die eerder in deze tutorial is geïntroduceerd.

Conclusie

Afhankelijk van je behoeften kan het analyseren van data in Google Sheets met Python naadloos zijn of een flinke initiële setup vereisen. Wil je alleen data uit een sheet lezen, ga dan voor de ingebouwde connector en je bent in minder dan vijf klikken onderweg. Wil je zowel data lezen uit als schrijven naar Google Sheets, dan loont het om de Google Sheets API in te schakelen en een serviceaccount op te zetten.

In beide gevallen geldt: zodra alles goed is geconfigureerd, is toegang krijgen tot data uit Google Sheets bijna net zo eenvoudig als het bevragen van een CSV-bestand. Het grote voordeel is echter dat je je stakeholders kunt bedienen waar zij zitten: de kans is groot dat sommige collega’s zweren bij spreadsheets.

Weg met de tijd van moeizame CSV-exporten en welkom in de tijd van krachtige, automatische workflows om met Google Sheets-data te werken zonder DataLab te verlaten.