<commit_before>#!/usr/bin/env python

from __future__ import print_function
import click
from glob import glob
import os
import pandas as pd
import shutil
import pdb

from energyPATHWAYS.generated.text_mappings import MappedCols

from postgres import (find_key_col, find_parent_col, mkdirs, Simple_mapping_tables,
                       Tables_without_classes, Tables_to_ignore)

def _read_csv(pathname, tbl_name):
    # Avoid reading empty strings as nan
    str_cols = MappedCols.get(tbl_name, [])
    converters = {col: str for col in str_cols}
    converters['sensitivity'] = str

    df = pd.read_csv(pathname, index_col=None, converters=converters)
    return df

def get_key_col(dbdir, table_name):
    path = os.path.join(dbdir,  table_name + '.csv')
    df = _read_csv(path, table_name)
    key_col = find_key_col(table_name, df.columns)
    return key_col

def denormalize(dbdir, outdir, table_name, child_name):
    parentPath = os.path.join(dbdir,  table_name + '.csv')
    childPath  = os.path.join(dbdir,  child_name + '.csv')
    mergedPath = os.path.join(outdir, table_name + '.csv')

    # if not force and os.path.exists(mergedPath):
    #     mtime = os.path.getmtime(mergedPath)
    #     if os.path.getmtime(parentPath) < mtime and os.path.getmtime(parentPath) < mtime:
    #         print("Already merged table", table_name)
    #         return

    print("Merging", table_name)

    parentDF = _read_csv(parentPath, table_name)
    childDF  = _read_csv(childPath,  child_name)

    # In these cases, the child table already holds the desired result
    if table_name in ['Geographies', 'OtherIndexes', 'GeographiesSpatialJoin']:
        childDF.to_csv(mergedPath, index=None)
        return {'data_table': True}

    key_col = find_key_col(table_name, parentDF.columns)
    par_col = find_parent_col(child_name, childDF.columns)

    if not (key_col and par_col):
        if not key_col:
            print("*** Table {}: key_col not found".format(table_name))

        if not par_col:
            print("*** Table {}: par_col not found".format(table_name))

        return None

    if key_col == par_col:
        # if the parent and child match on a column with the same name, we
        # rename the child column before merging so "merge" doesn't rename them.
        new_col = 'CHILD_' + par_col
        childDF.rename(index=str, columns={par_col: new_col}, inplace=True)
        par_col = new_col

    merged = pd.merge(parentDF, childDF, left_on=key_col, right_on=par_col, how='left')

    # Drop rows for totals whose values are zeros; these are regenerated by the model.
    # We do this first since it eliminates many rows in these two large tables.
    if table_name in ('DemandEnergyDemands', 'DemandServiceDemands'):
        zero_totals = merged.query('input_type == "total" and value == 0')
        merged.drop(zero_totals.index, axis=0, inplace=True)

    # Missing child data records show up in the merged DF with the parent column
    # as NaN. We set the rest of the columns from the child record to '_missing'
    child_cols = list(set(childDF.columns) - set([par_col]))

    # dropped this for now
    # merged.loc[merged[par_col].isnull(), child_cols] = '_missing'

    # drop the redundant parent column and child id column, if present
    to_drop = [par_col]
    if 'id' in child_cols:
        to_drop.append('id')
        child_cols.remove('id')

    merged.drop(to_drop, axis=1, inplace=True)

    # try to sort things as best we can
    orig_column_order = merged.columns
    sort_order = ['name', 'sector', 'subsector', 'node', 'sensitivity', 'gau', 'oth_1', 'oth_2', 'final_energy', 'demand_technology', 'year', 'vintage', 'day_type', 'month', 'hour', 'weather_datetime']
    if key_col not in sort_order:
        sort_order = [key_col] + sort_order
    all_nulls = merged.isnull().all()
    columns_that_exist = [col for col in sort_order if col in merged.columns and not all_nulls[col]]
    if columns_that_exist:
        merged = merged.set_index(columns_that_exist).sort_index().reset_index()[orig_column_order]<commit_msg>Add value of supply_node col to name column in SupplyCosts<commit_after>#!/usr/bin/env python

from __future__ import print_function
import click
from glob import glob
import os
import pandas as pd
import shutil
import pdb

from energyPATHWAYS.generated.text_mappings import MappedCols

from postgres import (find_key_col, find_parent_col, mkdirs, Simple_mapping_tables,
                       Tables_without_classes, Tables_to_ignore)

def _read_csv(pathname, tbl_name):
    # Avoid reading empty strings as nan
    str_cols = MappedCols.get(tbl_name, [])
    converters = {col: str for col in str_cols}
    converters['sensitivity'] = str

    df = pd.read_csv(pathname, index_col=None, converters=converters)
    return df

def get_key_col(dbdir, table_name):
    path = os.path.join(dbdir,  table_name + '.csv')
    df = _read_csv(path, table_name)
    key_col = find_key_col(table_name, df.columns)
    return key_col

def denormalize(dbdir, outdir, table_name, child_name):
    parentPath = os.path.join(dbdir,  table_name + '.csv')
    childPath  = os.path.join(dbdir,  child_name + '.csv')
    mergedPath = os.path.join(outdir, table_name + '.csv')

    # if not force and os.path.exists(mergedPath):
    #     mtime = os.path.getmtime(mergedPath)
    #     if os.path.getmtime(parentPath) < mtime and os.path.getmtime(parentPath) < mtime:
    #         print("Already merged table", table_name)
    #         return

    print("Merging", table_name)

    parentDF = _read_csv(parentPath, table_name)
    childDF  = _read_csv(childPath,  child_name)

    # In these cases, the child table already holds the desired result
    if table_name in ['Geographies', 'OtherIndexes', 'GeographiesSpatialJoin']:
        childDF.to_csv(mergedPath, index=None)
        return {'data_table': True}

    key_col = find_key_col(table_name, parentDF.columns)
    par_col = find_parent_col(child_name, childDF.columns)

    if not (key_col and par_col):
        if not key_col:
            print("*** Table {}: key_col not found".format(table_name))

        if not par_col:
            print("*** Table {}: par_col not found".format(table_name))

        return None

    if key_col == par_col:
        # if the parent and child match on a column with the same name, we
        # rename the child column before merging so "merge" doesn't rename them.
        new_col = 'CHILD_' + par_col
        childDF.rename(index=str, columns={par_col: new_col}, inplace=True)
        par_col = new_col

    merged = pd.merge(parentDF, childDF, left_on=key_col, right_on=par_col, how='left')

    # Drop rows for totals whose values are zeros; these are regenerated by the model.
    # We do this first since it eliminates many rows in these two large tables.
    if table_name in ('DemandEnergyDemands', 'DemandServiceDemands'):
        zero_totals = merged.query('input_type == "total" and value == 0')
        merged.drop(zero_totals.index, axis=0, inplace=True)

    # Missing child data records show up in the merged DF with the parent column
    # as NaN. We set the rest of the columns from the child record to '_missing'
    child_cols = list(set(childDF.columns) - set([par_col]))

    # dropped this for now
    # merged.loc[merged[par_col].isnull(), child_cols] = '_missing'

    # drop the redundant parent column and child id column, if present
    to_drop = [par_col]
    if 'id' in child_cols:
        to_drop.append('id')
        child_cols.remove('id')

    merged.drop(to_drop, axis=1, inplace=True)

    # try to sort things as best we can
    orig_column_order = merged.columns
    sort_order = ['name', 'sector', 'subsector', 'node', 'sensitivity', 'gau', 'oth_1', 'oth_2', 'final_energy', 'demand_technology', 'year', 'vintage', 'day_type', 'month', 'hour', 'weather_datetime']
    if key_col not in sort_order:
        sort_order = [key_col] + sort_order
    all_nulls = merged.isnull().all()
    columns_that_exist = [col for col in sort_order if col in merged.columns and not all_nulls[col]]
    if columns_that_exist:
        merged = merged.set_index(columns_that_exist).sort_index().reset_index()[orig_column_order]

    if table_name == 'SupplyCost':
        merged.name = merged.name + ' - ' + merged.supply_node
{'fraction_non_alphanumeric': 0.10388045312123403, 'fraction_numerical': 0.001205109664979513, 'mean_word_length': 3.50542888165038, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 3, 'https://': 0, '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}}
{'dir': 'git-commits-cleaned', 'id': '2004812', 'n_tokens_mistral': 2743, 'n_tokens_neox': 2646, 'n_words': 754}