Profile - SQL Supabaase Data Prep

YAML for SQL table names

The following YAML for the SQL insert resides at profile/impacts/exibase/US-source
Yaml-2-SQL table python from From ChatGPT which resides in the textarea below.
A version of the following resides in our generate_import_factors.py which we are replacing with a direct pull from the Exiobase API.

The previous Exiobase CSV file exports pulled by the US EPA code reside in impacts/exiobase/US-source. Initilly the following was created to use yaml to rename CSV columns when pulling into Supabase. We are aiming to avoid using the US EPA csv files with a new version of the Exiobase pull that works for all countries.

import yaml
import pandas as pd
import sqlalchemy
from sqlalchemy import create_engine, Table, MetaData

# Load the YAML file
with open('create-database.yaml', 'r') as file:
    config = yaml.safe_load(file)

# Create SQL Alchemy Engine
engine = create_engine('sqlite:///example.db')
metadata = MetaData()

# Function to convert column names to CamelCase without underscores
def to_camel_case(snake_str):
    components = snake_str.split('_')
    return components[0].capitalize() + ''.join(x.title() for x in components[1:])

# Process each table in the YAML configuration
for table_name, table_config in config.items():
    csv_file = table_config['source']
    df = pd.read_csv(csv_file)

columns_map = table_config.get('columns', {})
    omit_columns = table_config.get('omit', [])

# Drop omitted columns
    df.drop(columns=omit_columns, errors='ignore', inplace=True)

# Rename columns as per the YAML configuration
    df.rename(columns=columns_map, inplace=True)

# Generate new column names for remaining columns
    new_columns = {}
    for col in df.columns:
        if col not in columns_map.values():
            new_columns[col] = to_camel_case(col)
    df.rename(columns=new_columns, inplace=True)

# Exclude 'Year' column if not explicitly included in columns_map
    if 'Year' not in columns_map.values() and 'Year' in df.columns:
        df.drop(columns=['Year'], inplace=True)

# Append "US" to the table name
    table_name = table_name + "US"

# Insert/Update the data into the database
    if engine.dialect.has_table(engine, table_name):
        # Update existing table
        temp_table_name = table_name + "_temp"
        df.to_sql(temp_table_name, engine, if_exists='replace', index=False)
        with engine.connect() as conn:
            conn.execute(f"""
                INSERT OR REPLACE INTO {table_name}
                SELECT * FROM {temp_table_name}
            """)
            conn.execute(f"DROP TABLE {temp_table_name}")
    else:
        # Create and insert new table
        df.to_sql(table_name, engine, if_exists='replace', index=False)

YAML for SQL table names

Not in use, outputting to .csv files instead.
See overview of trade data pulls at profile/trade.