<jupyter_start><jupyter_text>Programme pour creer la table teams<jupyter_code># Import des librairies
import pandas as pd
import requests
import pandas_gbq
import getpass
from google.oauth2 import service_account
pd.set_option('display.max_columns', None)
# On va chercher les equipes
url = 'https://statsapi.web.nhl.com/api/v1/teams'
r = requests.get(url)
json_data = r.json()
teams = pd.json_normalize(json_data['teams'], sep = "_")
# On garde seulement les colonnes interessantes
teams = teams[['id', 'abbreviation', 'teamName', 'locationName','officialSiteUrl', 'active', 'venue_name', 'venue_city']]

# On renomme pour avoir des noms coherents avec ce qui se trouve dans la BD
teams = teams.rename(columns={'id': 'team_id',
                              'abbreviation': 'abbreviation',
                              'teamName': 'team_name',
                              'locationName': 'team_city',
                              'officialSiteUrl': 'website_url',
                              'active': 'is_active',
                              'venue_name': 'venue_name',
                              'venue_city': 'venue_city'
                             })
teams.head()
def get_keys():
    '''
    This function will return the path to Google Keys for each user.
    '''
    if getpass.getuser()=='antoinetl':
        credential_keys = '/Users/antoinetl/Documents/code/Google Keys/My First Project-4938b2ab0dc6.json'
    elif getpass.getuser()=='philippejacques':
        credential_keys = '/Users/philippejacques/Desktop/Projet/HockeyPrediction/Google Keys/hockey-prediction-qc-b427ad188c20.json'
    return credential_keys
# Connexion GBQ
credential_keys = get_keys()

# Connexion GBQ
credentials = service_account.Credentials.from_service_account_file(
    credential_keys,
    )
pandas_gbq.context.credentials = credentials
# GCP project ID.
project_id = "hockey-prediction-qc"

# table_id that corresponds to the name of the table.
table_id = 'hockey_prediction_qc.teams'

# Insert into GCP
pandas_gbq.to_gbq(teams,
                  table_id,
                  project_id=project_id,
                  if_exists='replace')<jupyter_output>1it [00:03,  3.99s/it]
{'fraction_non_alphanumeric': 0.10651974288337925, 'fraction_numerical': 0.01147842056932966, 'mean_word_length': 2.8027923211169283, 'pattern_counts': {'":': 0, '<': 4, '<?xml version=': 0, '>': 4, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '462690', 'license': 'MIT', 'n_tokens_mistral': 720, 'n_tokens_neox': 688, 'n_words': 150, 'path': 'notebooks/extract_teams.ipynb', 'repo_name': 'FrischWaugh/nhl_prediction', 'size': '2178'}