<jupyter_start><jupyter_text>Table of Contents1&nbsp;&nbsp;Load historical daily exchange rates ECB2&nbsp;&nbsp;Seed the table  Load historical daily exchange rates ECB<jupyter_code># import wget
import numpy as np
import pandas as pd
import pandas_gbq
import logging
def load_data(opt:str='new'):
    """
    opt: {'hist', 'new'}
    """
    urls = {
        "hist":"https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip",
        "new":"https://www.ecb.europa.eu/stats/eurofxref/eurofxref.zip"}
    hist= pd.read_csv(urls[opt], compression='zip')
    return hist

def process_data(hist:pd.DataFrame)->pd.DataFrame:
    value_cols = [x for x in hist.columns  if (x.strip() not in ['Date', ''])and('Unnamed' not in x)]
    hist = hist.pipe(pd.melt,
                    id_vars=['Date'], 
                    var_name='CurrencyCode',
                    value_vars=value_cols,
                    value_name='EuroToCurrency')
    hist['CurrencyToEuro']=1/hist['EuroToCurrency']
    hist['Date']=pd.to_datetime(hist['Date'], utc=True)
    hist['CurrencyCode']=hist['CurrencyCode'].str.strip()
    return hist

indata['Date'].max()>max_hist_date<jupyter_output><empty_output><jupyter_text>Seed the table<jupyter_code>def data_is_new(indata):
    indata=load_data('new')
    indata=process_data(indata)
    indata['Date'].max()
    max_hist_date=pandas_gbq.read_gbq("""
            SELECT max(Date) FROM `ds-smartsupply.currency_exchange.euro_rates_daily`
            """).values.item()
    return indata['Date'].max()>max_hist_date

def upload_to_bq(hist_bq:pd.DataFrame, if_exists:str='replace'):
    project_id = 'whatever'
    dataset_id = 'currency_exchange'
    table_id = 'euro_rates_daily'
    pandas_gbq.to_gbq(hist_bq,  f'{dataset_id}.{table_id}', project_id=project_id, if_exists=if_exists, location='europe-west2')
    message_string = f'{len(hist_bq)} rows uploaded to table `{dataset_id}.{table_id}`'
    print(message_string)
    return({'status':message_string})


def seed_historical():
    try:
        hist = load_data('hist')
        hist = process_data(hist)
        upload_to_bq(hist)
        return 'success'
    except: return 'failure'

def append_new():
    try:
        new = load_data('new')
        new = process_data(new)
        if data_is_new(indata=new):
            upload_to_bq(new, if_exists='append')
        else:
            print('no new data to append')
        return 'success'
    except:
        return 'failure'<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13941341904379267, 'fraction_numerical': 0.0016070711128967456, 'mean_word_length': 3.5272727272727273, 'pattern_counts': {'":': 2, '<': 9, '<?xml version=': 0, '>': 12, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '580236', 'license': 'MIT', 'n_tokens_mistral': 880, 'n_tokens_neox': 867, 'n_words': 145, 'path': 'notebooks/Daily Exchange Rates.ipynb', 'repo_name': 'shuyi-li/ecb_eurorate_daily', 'size': '2489'}