Exemple #1
0
def create_consolidated_tables():
    allsources = eia.fossilfuels + eia.elec_sources + \
        eia.nuclear + eia.renewables
    allsectors = ["TC", "AC", "CC", "IC", "RC"] + eia.elec_sectors

    for year in config.STUDY_YEARS:
        strings = {
            "renewables": sqlhelper.set_repr(eia.renewables),
            "elec_sources": sqlhelper.set_repr(eia.elec_sources),
            "elec_sectors": sqlhelper.set_repr(eia.elec_sectors),
            "allsources": sqlhelper.set_repr(allsources),
            "allsectors": sqlhelper.set_repr(allsectors),
            "from_table": "%s.seds_us_%d" % (config.EIA_SCHEMA, year),
            "tablename": "%s.seds_short_%d" % (config.EIA_SCHEMA, year),
            }

        db.execute("DROP TABLE IF EXISTS %(tablename)s CASCADE" % strings)
        db.execute("""
        SELECT source, sector,
               case when sum(use_btu) = 0 then 0
                    else sum(ex) / sum(use_btu) end as price,
               sum(use_btu) as use_btu,
               sum(ex) as ex
          INTO %(tablename)s
          FROM (SELECT case when source in %(renewables)s then 'RE'
                            when source in %(elec_sources)s then 'ES'
                            else source end as source,
                       case when sector in %(elec_sectors)s then 'EI'
                            else sector end as sector,
                       price, use_btu, ex
                  FROM %(from_table)s
                 WHERE source in %(allsources)s
                   AND sector in %(allsectors)s) e
         GROUP by source, sector order by source, sector""" % strings)
Exemple #2
0
 def generate_where(self, table_alias="", extra=[]):
     conditions = self.universal_conditions[:]
     if len(table_alias) and not table_alias.endswith("."):
         table_alias = table_alias + "."
     if len(self.from_blacklist):
         conditions.append("%s%s NOT IN %s" \
             % (table_alias, self.strings["from_sector"],
                sqlhelper.set_repr(self.from_blacklist)))
     if len(self.to_blacklist):
         conditions.append("%s%s NOT IN %s" \
             % (table_alias, self.strings["to_sector"],
                sqlhelper.set_repr(self.to_blacklist)))
     if len(conditions) > 0:
         return " AND ".join(conditions)
     return "TRUE"
Exemple #3
0
def get_national_value(country, year, measurement, env_series="CO2"):
    strings = {
        "schema": config.WIOD_SCHEMA,
        "year": year,
        "fd_sectors": sqlhelper.set_repr(config.default_fd_sectors),
        }

    if measurement == "env":
        envsql = """SELECT value FROM %(schema)s.env_%(year)d
                     WHERE country = $1 AND measurement = $2
                       AND industry = 'total'"""
        envstmt = db.prepare(envsql % strings)
        return envstmt(country, env_series)[0][0]

    if measurement == "gdp":
        gdpsql = """SELECT sum(value) FROM %(schema)s.indbyind_%(year)d
                     WHERE country = $1 AND to_ind in %(fd_sectors)s"""
        gdpstmt = db.prepare(gdpsql % strings)
        gdp = gdpstmt(country)[0][0]
        return imfdata.convert_to_2005(gdp, country, year)

    if measurement == "ppppc":
        ppppc = imfdata.get_imf_value(country, year, "ppp_pc")
        if ppppc is None:
            # above is worldbank version. imf version might not be chained
            ppppc = imfdata.get_imf_value(country, year, "PPPPC")
        return ppppc

    if measurement == "pop":
        return imfdata.get_imf_value(country, year, "pop")

    return imfdata.get_imf_value(country, year, measurement)
Exemple #4
0
def get_io_harmonizer(iogen):
    env_blacklist = sqlhelper.set_repr(config.env_sector_blacklist)
    sel = matrixutils.generate_selector_matrix(
        "%s.sector_map" % config.WIOD_SCHEMA,
        iogen.get_sectors(), "io_code", "env_code",
        ["io_code is not null", "env_code is not null",
         "env_code not in %s" % env_blacklist])
    return sel
Exemple #5
0
    def generate_env_stmt(self, series):
        strings = self.strings
        strings["series_list"] = sqlhelper.set_repr(series)

        sql = """SELECT %(ind_col)s, sum(%(value)s)
                   FROM %(envtable)s
                  WHERE %(basic_condition)s
                    AND %(series_col)s IN %(series_list)s
                  GROUP BY %(ind_col)s""" % strings

        return db.prepare(sql)
Exemple #6
0
def trade_sector_stats(countries, is_export):
    minyear = min(config.STUDY_YEARS)
    maxyear = max(config.STUDY_YEARS)

    strings = {
        "minyear": minyear,
        "maxyear": maxyear,
        "schema": config.WIOD_SCHEMA,
        "is_export": is_export,
        "countries": sqlhelper.set_repr(countries),
        "blacklist": sqlhelper.set_repr(config.bad_data_blacklist),
        }

    if is_export:
        strings["view"] = "export_view"
        strings["is_export_str"] = "true"
    else:
        strings["view"] = "import_view"
        strings["is_export_str"] = "false"

    db.execute("""CREATE OR REPLACE VIEW %(view)s AS
    SELECT year, industry, sum(value) as value
      FROM trade_results
     WHERE is_export is %(is_export_str)s
       AND country IN %(countries)s
       AND country NOT IN %(blacklist)s
     GROUP BY year, industry""" % strings)

    stmt = db.prepare("""
    SELECT a.year, a.industry, a.value, a.value / b.value * 100
      FROM %(view)s a,
           (SELECT year, sum(value) as value
              FROM %(view)s
             GROUP BY year) b
     WHERE a.year in (%(minyear)d, %(maxyear)d)
       AND a.year = b.year""" % strings)

    print()
    print(countries)
    print()
    print_result(stmt(), minyear, maxyear)
Exemple #7
0
def trade_sector_stats(countries, is_export):
    minyear = min(config.STUDY_YEARS)
    maxyear = max(config.STUDY_YEARS)

    strings = {
        "minyear": minyear,
        "maxyear": maxyear,
        "schema": config.WIOD_SCHEMA,
        "is_export": is_export,
        "countries": sqlhelper.set_repr(countries),
        "blacklist": sqlhelper.set_repr(config.bad_data_blacklist),
    }

    if is_export:
        strings["view"] = "export_view"
        strings["is_export_str"] = "true"
    else:
        strings["view"] = "import_view"
        strings["is_export_str"] = "false"

    db.execute("""CREATE OR REPLACE VIEW %(view)s AS
    SELECT year, industry, sum(value) as value
      FROM trade_results
     WHERE is_export is %(is_export_str)s
       AND country IN %(countries)s
       AND country NOT IN %(blacklist)s
     GROUP BY year, industry""" % strings)

    stmt = db.prepare("""
    SELECT a.year, a.industry, a.value, a.value / b.value * 100
      FROM %(view)s a,
           (SELECT year, sum(value) as value
              FROM %(view)s
             GROUP BY year) b
     WHERE a.year in (%(minyear)d, %(maxyear)d)
       AND a.year = b.year""" % strings)

    print()
    print(countries)
    print()
    print_result(stmt(), minyear, maxyear)
Exemple #8
0
def envgen_for_year(year, additional_sectors=[]):
    blacklist = list(config.env_sector_blacklist)
    for sector in additional_sectors:
        blacklist.remove(sector)
    env_sector_blacklist = sqlhelper.set_repr(blacklist)
    return EnvMatrixGenerator(
        envtable="%s.env_%d" % (config.WIOD_SCHEMA, year),
        ind_col_name="industry",
        series_col_name="measurement",
        value_col_name="value",
        universal_conditions=[
            "industry NOT IN " + env_sector_blacklist,
            "country = $1"
            ])
Exemple #9
0
def get_wiod_env_vector(country, year, env_series):
    strings = {
        "year": year,
        "schema": config.WIOD_SCHEMA,
        "blacklist": sqlhelper.set_repr(config.env_sector_blacklist_hh),
        "measurements": sqlhelper.set_repr(env_series),
    }

    vector = NamedMatrix(rows=common.env_sectors_with_hh, cols=["value"])

    stmt = db.prepare("""SELECT industry, sum(value)
                           FROM %(schema)s.env_%(year)d
                          WHERE country = $1
                            AND measurement IN %(measurements)s
                            AND industry NOT IN %(blacklist)s
                          GROUP BY industry""" % strings)
    result = stmt(base_country)

    for row in result:
        if row[1] is not None:
            vector.set_element(row[0], "value", row[1])

    return vector
Exemple #10
0
def get_wiod_env_vector(country, year, env_series):
    strings = {
        "year": year,
        "schema": config.WIOD_SCHEMA,
        "blacklist": sqlhelper.set_repr(config.env_sector_blacklist_hh),
        "measurements": sqlhelper.set_repr(env_series),
        }

    vector = NamedMatrix(rows=common.env_sectors_with_hh, cols=["value"])

    stmt = db.prepare("""SELECT industry, sum(value)
                           FROM %(schema)s.env_%(year)d
                          WHERE country = $1
                            AND measurement IN %(measurements)s
                            AND industry NOT IN %(blacklist)s
                          GROUP BY industry""" % strings)
    result = stmt(base_country)

    for row in result:
        if row[1] is not None:
            vector.set_element(row[0], "value", row[1])

    return vector
Exemple #11
0
def env_sectors_for_year(year, include_hh=False):
    if include_hh:
        blacklist = config.env_sector_blacklist_hh
    else:
        blacklist = config.env_sector_blacklist
    envgen = EnvMatrixGenerator(
        envtable="%s.env_%d" % (config.WIOD_SCHEMA, year),
        ind_col_name="industry",
        series_col_name="measurement",
        value_col_name="value",
        universal_conditions=[
            "industry NOT IN " + sqlhelper.set_repr(blacklist),
            ])
    return envgen.get_sectors()
Exemple #12
0
def create_views():
    va_sectors = set(config.va_sectors.values())
    fd_sectors = set(config.fd_sectors.values())

    for year in config.STUDY_YEARS:
        strings = {
            "test_schema": common.config.TEST_SCHEMA,
            "schema": config.WIOD_SCHEMA,
            "extra_schema": "wiod_plus",
            "year": year,
            "fd_sectors": sqlhelper.set_repr(fd_sectors),
            "va_sectors": sqlhelper.set_repr(va_sectors),
            "margins": sqlhelper.set_repr(config.margin_sectors)
            }

        ### indbyind tables ignoring imports
        db.execute(
            """CREATE OR REPLACE VIEW %(schema)s.indbyind_%(year)d AS
               SELECT country, from_ind, to_ind, value
                 FROM %(schema)s.niot_%(year)d
                WHERE NOT is_import
             UNION
               SELECT country, from_ind, 'IMP', sum(value)
                 FROM %(schema)s.niot_%(year)d
                WHERE is_import
                GROUP BY country, from_ind""" % strings)

        #continue

        # co2 intensity views
        # put in test since we're just checking results
        sql = """CREATE OR REPLACE VIEW %(test_schema)s.co2_intensity_%(year)d AS
            SELECT a.country, CAST(a.gdp as int) gdp,
                   CAST(b.emissions as int) emissions,
                   b.emissions / a.gdp AS intensity
              FROM (SELECT country, sum(value) AS gdp
                      FROM %(schema)s.indbyind_%(year)d
                     WHERE from_ind not in %(va_sectors)s
                       AND to_ind in %(fd_sectors)s
                     GROUP BY country) a,
                   (SELECT country, value AS emissions
                      FROM %(schema)s.env_%(year)d where industry = 'total'
                       AND measurement = 'CO2') b
             WHERE a.country = b.country
             ORDER BY country""" % strings
        db.execute(sql)

        # commodity output proportions tables for all countries
        sql = """CREATE OR REPLACE VIEW %(schema)s.comshare_%(year)d AS
            SELECT make.country, make.commodity, make.industry,
                   make.value / totals.value AS use_share
              FROM (SELECT country, commodity, industry, value
                      FROM wiod.int_make_%(year)d
                     WHERE commodity not in %(va_sectors)s
                       AND industry not in %(margins)s) make,
                   (SELECT country, commodity, sum(value) as value
                      FROM wiod.int_make_%(year)d
                     WHERE commodity not in %(va_sectors)s
                       AND industry not in %(margins)s
                     GROUP BY country, commodity) totals
             WHERE make.country = totals.country
               AND make.commodity = totals.commodity""" % strings
        db.execute(sql)

        for country in config.countries:
            strings["country"] = country.lower()
            table = "%(extra_schema)s.%(country)s_io_import_%(year)d" % strings
            strings["io_import_table"] = table

            sql = "DROP TABLE IF EXISTS %(io_import_table)s" % strings
            db.execute(sql)

            sql = """SELECT comshare.country,
                            comshare.industry AS from_sector,
                            use.industry AS to_sector,
                            sum(use.value * comshare.use_share) AS value
                       INTO %(io_import_table)s
                       FROM %(schema)s.comshare_%(year)d comshare,
                            (SELECT from_country, industry, commodity, value
                               FROM %(schema)s.int_use_%(year)d
                              WHERE to_country = $1
                                AND from_country <> $1) use
                      WHERE comshare.country = use.from_country
                        AND comshare.commodity = use.commodity
                      GROUP BY comshare.country, comshare.industry,
                            use.industry""" % strings
    
            print(sql)
            stmt = db.prepare(sql)
            stmt(country)
Exemple #13
0
def parse_io():
    io_files = {
        1996: "410281134571.xls",
        1999: "4102715414971.xls",
        2001: "4122111363671.xls",
        2004: "611239581071.xls",
        2006: "9121414285971.xls",
        2007: "1139203871.xls",
        2008: "1139204871.xls",
        2009: "11229101502.xls",
        2010: "1122910141371.xls",
        }

    for (year, filename) in io_files.items():
        tablename = "%s.io_%d" % (config.SCHEMA, year)
    
        # millions are in NTD
        table = SQLTable(tablename,
                         ["from_sector", "to_sector", "millions"],
                         ["varchar(255)", "varchar(255)", "float"])
        table.create()
        table.truncate()
    
        path = fileutils.getcache(filename, "tw/%d" % year)
        wb = xlrd.open_workbook(path)
        sheet = wb.sheets()[0]
        to_codes = sheet.row_values(0)
        to_names = sheet.row_values(1)
        for rowindex in range(2, sheet.nrows):
            row = sheet.row_values(rowindex)
            from_code = row[0].strip()
            from_name = row[1].strip()
            for i in range(2, len(to_names)):
                to_name = to_names[i].strip()
                value = row[i]
                table.insert([from_name, to_name, value])

        if year == 2010:
            strings = {
                "viewname": "%s.io_view_%d" % (config.SCHEMA, year),
                "tablename": tablename,
                "maptable": "%s.sector_map_%d" % (config.SCHEMA, year),
                "to_blacklist": sqlhelper.set_repr(config.to_blacklists[year]),
                "from_blacklist":
                    sqlhelper.set_repr(config.from_blacklists[year]),
                }

            sql = """CREATE OR REPLACE VIEW %(viewname)s AS
                SELECT from_map.io_sector AS from_sector,
                       to_map.io_sector as to_sector,
                       sum(millions) as millions
                  FROM %(tablename)s io,
                       (SELECT DISTINCT io_sector, io_commod
                          FROM %(maptable)s) from_map,
                       (SELECT DISTINCT io_sector, io_ind
                          FROM %(maptable)s) to_map
                 WHERE io.to_sector NOT IN %(to_blacklist)s
                   AND io.from_sector NOT IN %(from_blacklist)s
                   AND from_map.io_commod = io.from_sector
                   AND to_map.io_ind = io.to_sector
                 GROUP BY from_map.io_sector, to_map.io_sector""" % strings

            print(sql)
            db.execute(sql)
Exemple #14
0
def create_hybrid_tables():
    for year in config.STUDY_YEARS:
        strings = {
            # table names
            "eia_table": "%s.seds_us_%d" % (config.EIA_SCHEMA, year),
            "io_table": "%s.transact_view_%d" % (config.IO_SCHEMA, year),
            "map_table": "%s.eia_code_map_%d" % (config.IO_SCHEMA, year),
            "hybrid_table": "hybrid_transactions_%d" % year,

            # selected sector codes
            "pa_trans_code": eia.source_naics_map['PA-trans'][year],
            "pa_nontrans_code": eia.source_naics_map['PA-nontrans'][year],
        }

        runsql("DROP TABLE IF EXISTS %s CASCADE" % strings["hybrid_table"])
        runsql("""
            CREATE TABLE %(hybrid_table)s (
                from_sector varchar(6),
                to_sector varchar(6),
                expenditure float
            )""" % strings)

        for source in eia.sources:
            strings["shares_view"] = "%s_ex_proportions_%s" % (source.lower(),
                                                               year)
            strings["source"] = source
            strings["source_naics"] = eia.source_naics_map[source][year]

            # seds prices are c.i.f., but when allocating btu among user
            # industries it is unfair to assign based on c.i.f. since what
            # they end up using is f.o.b.
            subquery = """
                SELECT codes.eia_sector, sum(io.fob) as fob
                  FROM %(io_table)s io, %(map_table)s codes
                 WHERE codes.eia_source = '%(source)s'
                   AND io.from_sector = '%(source_naics)s'
                   AND io.to_sector = codes.io_sector
                 GROUP BY codes.eia_sector""" % strings

            strings["subquery"] = subquery

            # the price each industry ends up actually paying for energy
            # should be the f.o.b. price which is (fob / cif) of the seds price
            runsql("""
            CREATE OR REPLACE VIEW %(shares_view)s AS
                SELECT io.to_sector, codes.eia_sector,
                       cast(io.fob as float) / cast(io.cif as float) as fob_share,
                       cast(io.fob as float) / cast(totals.fob as float) as ex_share
                  FROM %(io_table)s io, %(map_table)s codes,
                       (%(subquery)s) totals
                 WHERE codes.eia_source = '%(source)s'
                   AND io.from_sector = '%(source_naics)s'
                   AND io.to_sector = codes.io_sector
                   AND totals.eia_sector = codes.eia_sector""" % strings)

            # split petroleum
            if source == 'PA':
                strings["aviation_pa"] = sqlhelper.set_repr(
                    eia.aviation_petroleum)
                strings["other_pa"] = sqlhelper.set_repr(eia.other_petroleum)
                strings["all_pa"] = sqlhelper.set_repr(eia.aviation_petroleum +
                                                       eia.other_petroleum)

                strings["pa_nontrans_view"] = "pa_nontrans_%d" % year
                strings["pa_trans_view"] = "pa_trans_%d" % year
                strings[
                    "pa_trans_shares_view"] = "pa_trans_proportions_%d" % year
                strings["aviation_code"] = eia.air_transportation_codes[year]

                # non transportation petroleum use
                runsql("""
                CREATE OR REPLACE VIEW %(pa_nontrans_view)s AS
                    SELECT shares.to_sector, shares.eia_sector,
                           sum(shares.ex_share * eia.use_btu) as btu,
                           sum(shares.ex_share * eia.use_btu
                               * eia.price * shares.fob_share) as ex
                      FROM %(shares_view)s shares, %(eia_table)s eia
                     WHERE eia.source in %(all_pa)s
                       AND shares.eia_sector = eia.sector
                       -- these two below are double counted
                       AND eia.source || eia.sector not in ('DFEI', 'PCIC')
                       AND eia.sector <> 'AC'
                     GROUP BY shares.to_sector, shares.eia_sector""" % strings)

                # petroleum use for transportation other than air
                runsql("""
                CREATE OR REPLACE VIEW %(pa_trans_view)s AS
                    SELECT io.to_sector, io.fob,
                           io.fob - nontrans.ex as remaining
                      FROM %(io_table)s io, %(pa_nontrans_view)s nontrans
                     WHERE io.from_sector = '%(source_naics)s'
                       AND io.to_sector = nontrans.to_sector
                       -- remaining is negative for IC and EI
                       AND nontrans.eia_sector in ('CC', 'RC')
                   UNION
                    SELECT io.to_sector, io.fob,
                           cast(io.fob as float) as remaining
                      FROM %(io_table)s io, %(map_table)s codes
                     WHERE io.from_sector = '%(source_naics)s'
                       AND io.to_sector = codes.io_sector
                       AND codes.eia_source = 'PA'
                       AND codes.eia_sector = 'AC'
                       AND io.to_sector <> '%(aviation_code)s' """ % strings)

                # proportions for petroleum allocated to transportation
                runsql("""
                CREATE OR REPLACE VIEW %(pa_trans_shares_view)s AS
                    SELECT use.to_sector, use.remaining / total.total as ex_share
                      FROM %(pa_trans_view)s use,
                           (SELECT sum(remaining) as total
                              FROM %(pa_trans_view)s) total""" % strings)

                # allocate all of JF and AV to air transportation
                runsql("""
                INSERT INTO %(hybrid_table)s
                    SELECT '%(pa_trans_code)s', io.to_sector, sum(eia.use_btu)
                      FROM %(io_table)s io,
                           %(eia_table)s eia
                     WHERE eia.source in %(aviation_pa)s
                       and eia.sector = 'AC'
                       and io.from_sector = '%(source_naics)s'
                       and io.to_sector = '%(aviation_code)s'
                     GROUP BY io.to_sector """ % strings)

                # allocate all other transportation
                runsql("""
                INSERT INTO %(hybrid_table)s
                    SELECT '%(pa_trans_code)s',
                           shares.to_sector, sum(shares.ex_share * eia.use_btu)
                      FROM %(pa_trans_shares_view)s shares, %(eia_table)s eia
                     WHERE eia.source in %(other_pa)s
                       AND eia.sector = 'AC'
                     GROUP BY shares.to_sector""" % strings)

                # allocate non-transportation petroleum use
                runsql("""
                INSERT INTO %(hybrid_table)s
                    SELECT '%(pa_nontrans_code)s', to_sector, btu
                      FROM %(pa_nontrans_view)s""" % strings)
                #WHERE eia_sector in ('IC', 'EI')"""

                # dependencies in reverse order
                runsql("DROP VIEW %s" % strings["pa_trans_shares_view"])
                runsql("DROP VIEW %s" % strings["pa_trans_view"])
                runsql("DROP VIEW %s" % strings["pa_nontrans_view"])
                runsql("DROP VIEW %s" % strings["shares_view"])

            else:
                runsql("""
                INSERT INTO %(hybrid_table)s
                    SELECT '%(source_naics)s',
                           shares.to_sector, shares.ex_share * eia.use_btu
                      FROM %(shares_view)s shares, %(eia_table)s eia
                     WHERE eia.source = '%(source)s'
                       AND shares.eia_sector = eia.sector""" % strings)

                runsql("DROP VIEW %s" % strings["shares_view"])

        # insert remainder of standard io table
        energy_sectors = []
        for source in eia.sources:
            energy_sectors.append(eia.source_naics_map[source][year])

        strings["sectors"] = ", ".join(["'%s'" % s for s in energy_sectors])

        db.execute("""
            INSERT INTO %(hybrid_table)s
            SELECT from_sector, to_sector, fob
              FROM %(io_table)s
             WHERE from_sector not in (%(sectors)s)""" % strings)

        # split petroleum column proportional to trans and nontrans uses
        stmt = db.prepare("""
            SELECT trans.use_btu / total.use_btu as trans_share
              FROM (SELECT use_btu FROM %(eia_table)s
                     WHERE source = 'PA' AND sector = 'AC') trans,
                   (SELECT use_btu FROM %(eia_table)s
                     WHERE source = 'PA' AND sector = 'TC') total""" % strings)

        result = stmt()
        if len(result) and len(result[0]):
            strings["pa_naics"] = eia.source_naics_map['PA'][year]
            strings["trans_share"] = result[0][0]
            strings["nontrans_share"] = 1 - result[0][0]

            # transportation petroleum use column
            runsql("""
                INSERT INTO %(hybrid_table)s
                SELECT from_sector, '%(pa_trans_code)s',
                       %(trans_share).4f * expenditure
                  FROM %(hybrid_table)s
                 WHERE to_sector = '%(pa_naics)s' """ % strings)

            # non-transportation petroleum use column
            runsql("""
                UPDATE %(hybrid_table)s
                   SET expenditure = %(nontrans_share).4f * expenditure,
                       to_sector = '%(pa_nontrans_code)s'
                 WHERE to_sector = '%(pa_naics)s' """ % strings)
Exemple #15
0
def create_hybrid_tables():
    for year in config.STUDY_YEARS:
        strings = {
            # table names
            "eia_table": "%s.seds_us_%d" % (config.EIA_SCHEMA, year),
            "io_table": "%s.transact_view_%d" % (config.IO_SCHEMA, year),
            "map_table": "%s.eia_code_map_%d" % (config.IO_SCHEMA, year),
            "hybrid_table": "hybrid_transactions_%d" % year,
            # selected sector codes
            "pa_trans_code": eia.source_naics_map["PA-trans"][year],
            "pa_nontrans_code": eia.source_naics_map["PA-nontrans"][year],
        }

        runsql("DROP TABLE IF EXISTS %s CASCADE" % strings["hybrid_table"])
        runsql(
            """
            CREATE TABLE %(hybrid_table)s (
                from_sector varchar(6),
                to_sector varchar(6),
                expenditure float
            )"""
            % strings
        )

        for source in eia.sources:
            strings["shares_view"] = "%s_ex_proportions_%s" % (source.lower(), year)
            strings["source"] = source
            strings["source_naics"] = eia.source_naics_map[source][year]

            # seds prices are c.i.f., but when allocating btu among user
            # industries it is unfair to assign based on c.i.f. since what
            # they end up using is f.o.b.
            subquery = (
                """
                SELECT codes.eia_sector, sum(io.fob) as fob
                  FROM %(io_table)s io, %(map_table)s codes
                 WHERE codes.eia_source = '%(source)s'
                   AND io.from_sector = '%(source_naics)s'
                   AND io.to_sector = codes.io_sector
                 GROUP BY codes.eia_sector"""
                % strings
            )

            strings["subquery"] = subquery

            # the price each industry ends up actually paying for energy
            # should be the f.o.b. price which is (fob / cif) of the seds price
            runsql(
                """
            CREATE OR REPLACE VIEW %(shares_view)s AS
                SELECT io.to_sector, codes.eia_sector,
                       cast(io.fob as float) / cast(io.cif as float) as fob_share,
                       cast(io.fob as float) / cast(totals.fob as float) as ex_share
                  FROM %(io_table)s io, %(map_table)s codes,
                       (%(subquery)s) totals
                 WHERE codes.eia_source = '%(source)s'
                   AND io.from_sector = '%(source_naics)s'
                   AND io.to_sector = codes.io_sector
                   AND totals.eia_sector = codes.eia_sector"""
                % strings
            )

            # split petroleum
            if source == "PA":
                strings["aviation_pa"] = sqlhelper.set_repr(eia.aviation_petroleum)
                strings["other_pa"] = sqlhelper.set_repr(eia.other_petroleum)
                strings["all_pa"] = sqlhelper.set_repr(eia.aviation_petroleum + eia.other_petroleum)

                strings["pa_nontrans_view"] = "pa_nontrans_%d" % year
                strings["pa_trans_view"] = "pa_trans_%d" % year
                strings["pa_trans_shares_view"] = "pa_trans_proportions_%d" % year
                strings["aviation_code"] = eia.air_transportation_codes[year]

                # non transportation petroleum use
                runsql(
                    """
                CREATE OR REPLACE VIEW %(pa_nontrans_view)s AS
                    SELECT shares.to_sector, shares.eia_sector,
                           sum(shares.ex_share * eia.use_btu) as btu,
                           sum(shares.ex_share * eia.use_btu
                               * eia.price * shares.fob_share) as ex
                      FROM %(shares_view)s shares, %(eia_table)s eia
                     WHERE eia.source in %(all_pa)s
                       AND shares.eia_sector = eia.sector
                       -- these two below are double counted
                       AND eia.source || eia.sector not in ('DFEI', 'PCIC')
                       AND eia.sector <> 'AC'
                     GROUP BY shares.to_sector, shares.eia_sector"""
                    % strings
                )

                # petroleum use for transportation other than air
                runsql(
                    """
                CREATE OR REPLACE VIEW %(pa_trans_view)s AS
                    SELECT io.to_sector, io.fob,
                           io.fob - nontrans.ex as remaining
                      FROM %(io_table)s io, %(pa_nontrans_view)s nontrans
                     WHERE io.from_sector = '%(source_naics)s'
                       AND io.to_sector = nontrans.to_sector
                       -- remaining is negative for IC and EI
                       AND nontrans.eia_sector in ('CC', 'RC')
                   UNION
                    SELECT io.to_sector, io.fob,
                           cast(io.fob as float) as remaining
                      FROM %(io_table)s io, %(map_table)s codes
                     WHERE io.from_sector = '%(source_naics)s'
                       AND io.to_sector = codes.io_sector
                       AND codes.eia_source = 'PA'
                       AND codes.eia_sector = 'AC'
                       AND io.to_sector <> '%(aviation_code)s' """
                    % strings
                )

                # proportions for petroleum allocated to transportation
                runsql(
                    """
                CREATE OR REPLACE VIEW %(pa_trans_shares_view)s AS
                    SELECT use.to_sector, use.remaining / total.total as ex_share
                      FROM %(pa_trans_view)s use,
                           (SELECT sum(remaining) as total
                              FROM %(pa_trans_view)s) total"""
                    % strings
                )

                # allocate all of JF and AV to air transportation
                runsql(
                    """
                INSERT INTO %(hybrid_table)s
                    SELECT '%(pa_trans_code)s', io.to_sector, sum(eia.use_btu)
                      FROM %(io_table)s io,
                           %(eia_table)s eia
                     WHERE eia.source in %(aviation_pa)s
                       and eia.sector = 'AC'
                       and io.from_sector = '%(source_naics)s'
                       and io.to_sector = '%(aviation_code)s'
                     GROUP BY io.to_sector """
                    % strings
                )

                # allocate all other transportation
                runsql(
                    """
                INSERT INTO %(hybrid_table)s
                    SELECT '%(pa_trans_code)s',
                           shares.to_sector, sum(shares.ex_share * eia.use_btu)
                      FROM %(pa_trans_shares_view)s shares, %(eia_table)s eia
                     WHERE eia.source in %(other_pa)s
                       AND eia.sector = 'AC'
                     GROUP BY shares.to_sector"""
                    % strings
                )

                # allocate non-transportation petroleum use
                runsql(
                    """
                INSERT INTO %(hybrid_table)s
                    SELECT '%(pa_nontrans_code)s', to_sector, btu
                      FROM %(pa_nontrans_view)s"""
                    % strings
                )
                # WHERE eia_sector in ('IC', 'EI')"""

                # dependencies in reverse order
                runsql("DROP VIEW %s" % strings["pa_trans_shares_view"])
                runsql("DROP VIEW %s" % strings["pa_trans_view"])
                runsql("DROP VIEW %s" % strings["pa_nontrans_view"])
                runsql("DROP VIEW %s" % strings["shares_view"])

            else:
                runsql(
                    """
                INSERT INTO %(hybrid_table)s
                    SELECT '%(source_naics)s',
                           shares.to_sector, shares.ex_share * eia.use_btu
                      FROM %(shares_view)s shares, %(eia_table)s eia
                     WHERE eia.source = '%(source)s'
                       AND shares.eia_sector = eia.sector"""
                    % strings
                )

                runsql("DROP VIEW %s" % strings["shares_view"])

        # insert remainder of standard io table
        energy_sectors = []
        for source in eia.sources:
            energy_sectors.append(eia.source_naics_map[source][year])

        strings["sectors"] = ", ".join(["'%s'" % s for s in energy_sectors])

        db.execute(
            """
            INSERT INTO %(hybrid_table)s
            SELECT from_sector, to_sector, fob
              FROM %(io_table)s
             WHERE from_sector not in (%(sectors)s)"""
            % strings
        )

        # split petroleum column proportional to trans and nontrans uses
        stmt = db.prepare(
            """
            SELECT trans.use_btu / total.use_btu as trans_share
              FROM (SELECT use_btu FROM %(eia_table)s
                     WHERE source = 'PA' AND sector = 'AC') trans,
                   (SELECT use_btu FROM %(eia_table)s
                     WHERE source = 'PA' AND sector = 'TC') total"""
            % strings
        )

        result = stmt()
        if len(result) and len(result[0]):
            strings["pa_naics"] = eia.source_naics_map["PA"][year]
            strings["trans_share"] = result[0][0]
            strings["nontrans_share"] = 1 - result[0][0]

            # transportation petroleum use column
            runsql(
                """
                INSERT INTO %(hybrid_table)s
                SELECT from_sector, '%(pa_trans_code)s',
                       %(trans_share).4f * expenditure
                  FROM %(hybrid_table)s
                 WHERE to_sector = '%(pa_naics)s' """
                % strings
            )

            # non-transportation petroleum use column
            runsql(
                """
                UPDATE %(hybrid_table)s
                   SET expenditure = %(nontrans_share).4f * expenditure,
                       to_sector = '%(pa_nontrans_code)s'
                 WHERE to_sector = '%(pa_naics)s' """
                % strings
            )
Exemple #16
0
def do_kyoto_table():
    minyear = min(config.STUDY_YEARS)
    maxyear = max(config.STUDY_YEARS)

    minstrings = {
        "schema": config.WIOD_SCHEMA,
        "year": minyear,
        "fd_sectors": sqlhelper.set_repr(config.default_fd_sectors),
        }
    maxstrings = minstrings.copy()
    maxstrings["year"] = maxyear

    envsql = """SELECT value FROM %(schema)s.env_%(year)d
                 WHERE country = $1 AND measurement = $2
                   AND industry = 'total'"""

    envstmt_i = db.prepare(envsql % minstrings)
    envstmt_f = db.prepare(envsql % maxstrings)

    un_stmt = db.prepare(
        "SELECT value FROM %s.mdg_emissions" % config.UN_SCHEMA +
        " WHERE country = $1 AND year = $2")

    data = {}
    (eu_i, eu_f, un_eu_90, un_eu_i, un_eu_f) = (0, 0, 0, 0, 0)
    for (country, name) in config.countries.items():
        env_i = envstmt_i(country, "CO2")[0][0]
        env_f = envstmt_f(country, "CO2")[0][0]
        percent = (env_f - env_i) / env_i * 100

        (un_env_90, un_env_91, un_env_i, un_env_f,
         un_percent, un_percent_90) = \
            (0, 0, 0, 0, None, None)
        result = un_stmt(country, 1990)
        if len(result):
            un_env_90 = result[0][0]
        else:
            # use 1991 as a proxy for 1990 for some countries if applicable
            # germany is the only annex b country that is applicable
            # so hopefully it won't mess up eu15 calculation too much
            result = un_stmt(country, 1991)
            if len(result):
                un_env_91 = result[0][0]
        result = un_stmt(country, minyear)
        if len(result):
            un_env_i = result[0][0]
        result = un_stmt(country, maxyear)
        if len(result):
            un_env_f = result[0][0]

        if un_env_i and un_env_f:
            un_percent = (un_env_f - un_env_i) / un_env_i * 100

        if un_env_90 and un_env_f:
            un_percent_90 = (un_env_f - un_env_90) / un_env_90 * 100

        data[country] = (env_i, env_f, percent, un_percent, un_percent_90)

        if country in config.eu15:
            eu_i += env_i
            eu_f += env_f
            un_eu_i += un_env_i
            un_eu_f += un_env_f
            if un_env_90:
                un_eu_90 += un_env_90
            else:
                un_eu_90 += un_env_91

    eu_percent = (eu_f - eu_i) / eu_i * 100
    un_eu_percent = (un_eu_f - un_eu_i) / un_eu_i * 100
    un_eu_percent_90 = (un_eu_f - un_eu_90) / un_eu_90 * 100

    print("%s & %s & %s & %d\\%% & %.1f\\%% & %.1f\\%% & %.1f \\NN" %
          ("EU-15".ljust(18),
           utils.add_commas(eu_i).rjust(9),
           utils.add_commas(eu_f).rjust(9),
           -8, eu_percent, un_eu_percent, un_eu_percent_90))

    for (target, countries) in config.annex_b_countries.items():
        for country in countries:
            vals = data[country]
            if vals[4] is None:
                percent_90 = ""
            else:
                percent_90 = "%.1f" % vals[4]
            print("%s & %s & %s & %d\\%% & %.1f\\%% & %.1f & %s \\NN" %
                  (config.countries[country].ljust(18),
                   utils.add_commas(vals[0]).rjust(9),
                   utils.add_commas(vals[1]).rjust(9),
                   target, vals[2], vals[3], percent_90))
Exemple #17
0
 def set_fd_sectors(self, sectors):
     self.strings["fd_sectors"] = sqlhelper.set_repr(sectors)
     self.fd_sectors = sectors
Exemple #18
0
def do_plots():
    for (name, measurements) in config.env_series_names.items():
        data = {}
        for year in config.STUDY_YEARS:
            strings = {
                "schema": config.WIOD_SCHEMA,
                "year": year,
                "fd_sectors": sqlhelper.set_repr(config.default_fd_sectors),
                "measurements": sqlhelper.set_repr(measurements),
                "nipa_schema": usa.config.NIPA_SCHEMA,
                }
    
            stmt = db.prepare(
                """SELECT a.country, a.series, b.gdp,
                          a.series / b.gdp as intensity
                     FROM (SELECT country, sum(value) as series
                             FROM %(schema)s.env_%(year)d
                            WHERE industry = 'total'
                              AND measurement in %(measurements)s
                            GROUP BY country) a,
                          (SELECT aa.country, sum(value) * deflator as gdp
                             FROM %(schema)s.indbyind_%(year)d aa,
                                  (SELECT 100 / gdp as deflator
                                     FROM %(nipa_schema)s.implicit_price_deflators
                                    WHERE year = $1) bb
                            WHERE to_ind in %(fd_sectors)s
                            GROUP BY aa.country, deflator) b
                    WHERE a.country = b.country
                      AND a.series is not null
                    ORDER BY a.series / b.gdp""" % strings)
    
            for row in stmt(year):
                country = row[0]
                intensity = row[3]
                if country not in data:
                    data[country] = {}
                data[country][year] = intensity
    
        slopes = {}
        for (country, country_data) in data.items():
            n = len(country_data.keys())
    
            if n < 2:
                continue
    
            sum_y = sum(country_data.values())
            sum_x = sum(country_data.keys())
            slope = (n * sum([k * v for (k, v) in country_data.items()]) \
                     - sum_x * sum_y) / \
                    (n * sum([k * k for k in country_data.keys()]) - sum_x)
    
            slopes[country] = slope * 1000000
    
        years = "%d-%d" % (config.STUDY_YEARS[0], config.STUDY_YEARS[-1])
        i = 0
        binsize = 8
        plot = None
        for (country, slope) in sorted(slopes.items(), key=lambda x: x[1]):
            if i % binsize == 0:
                if plot is not None:
                    plot.write_tables()
                    plot.generate_plot()
    
                tier = i / binsize + 1
                plot = GNUPlot("tier%d" % tier, "",
                               #"%s intensity from %s, tier %d" \
                               #    % (name, years, tier),
                               "wiod-%s" % name.replace(" ", "-"))
    
                plot.legend("width -5")
    
            for year in config.STUDY_YEARS:
                if year in data[country]:
                    plot.set_value(
                        "%s (%.2f)" % (config.countries[country], slope),
                        year,
                        data[country][year])
    
            i += 1
    
        if plot is not None:
            plot.write_tables()
            plot.generate_plot()
Exemple #19
0
    
for year in config.STUDY_YEARS:
    print(year)
    iogen = cfgen.get_iogen()
    iogen.set_table("%s.ixi_%d" % (config.SCHEMA, year))

    exchange_rate = wiod.common.get_exchange_rate("CAN", year)
    if exchange_rate is None:
        exchange_rate = exrate.get_rate("ca", year)

    iogen.set_exchange_rate(exchange_rate)
    
    envgen = cfgen.get_envgen()
    envgen.set_universal_conditions([
            "year = %d" % year,
            "industry not in %s" % sqlhelper.set_repr(config.env_blacklist),
            ])

    io_harmonizer = matrixutils.generate_selector_matrix(
        "%s.sector_map" % config.SCHEMA,
        iogen.get_sectors(), "io_code", "harmonized",
        ["io_code is not null"])

    env_harmonizer = matrixutils.generate_selector_matrix(
        "%s.sector_map" % config.SCHEMA,
        envgen.get_sectors(), "env_code", "harmonized",
        ["env_code is not null"])

    series = ["1"]
    
    cfgen.prepare(year, series, io_harmonizer, env_harmonizer)
Exemple #20
0
def create_views():
    va_sectors = set(config.va_sectors.values())
    fd_sectors = set(config.fd_sectors.values())

    for year in config.STUDY_YEARS:
        strings = {
            "test_schema": common.config.TEST_SCHEMA,
            "schema": config.WIOD_SCHEMA,
            "extra_schema": "wiod_plus",
            "year": year,
            "fd_sectors": sqlhelper.set_repr(fd_sectors),
            "va_sectors": sqlhelper.set_repr(va_sectors),
            "margins": sqlhelper.set_repr(config.margin_sectors)
        }

        ### indbyind tables ignoring imports
        db.execute("""CREATE OR REPLACE VIEW %(schema)s.indbyind_%(year)d AS
               SELECT country, from_ind, to_ind, value
                 FROM %(schema)s.niot_%(year)d
                WHERE NOT is_import
             UNION
               SELECT country, from_ind, 'IMP', sum(value)
                 FROM %(schema)s.niot_%(year)d
                WHERE is_import
                GROUP BY country, from_ind""" % strings)

        #continue

        # co2 intensity views
        # put in test since we're just checking results
        sql = """CREATE OR REPLACE VIEW %(test_schema)s.co2_intensity_%(year)d AS
            SELECT a.country, CAST(a.gdp as int) gdp,
                   CAST(b.emissions as int) emissions,
                   b.emissions / a.gdp AS intensity
              FROM (SELECT country, sum(value) AS gdp
                      FROM %(schema)s.indbyind_%(year)d
                     WHERE from_ind not in %(va_sectors)s
                       AND to_ind in %(fd_sectors)s
                     GROUP BY country) a,
                   (SELECT country, value AS emissions
                      FROM %(schema)s.env_%(year)d where industry = 'total'
                       AND measurement = 'CO2') b
             WHERE a.country = b.country
             ORDER BY country""" % strings
        db.execute(sql)

        # commodity output proportions tables for all countries
        sql = """CREATE OR REPLACE VIEW %(schema)s.comshare_%(year)d AS
            SELECT make.country, make.commodity, make.industry,
                   make.value / totals.value AS use_share
              FROM (SELECT country, commodity, industry, value
                      FROM wiod.int_make_%(year)d
                     WHERE commodity not in %(va_sectors)s
                       AND industry not in %(margins)s) make,
                   (SELECT country, commodity, sum(value) as value
                      FROM wiod.int_make_%(year)d
                     WHERE commodity not in %(va_sectors)s
                       AND industry not in %(margins)s
                     GROUP BY country, commodity) totals
             WHERE make.country = totals.country
               AND make.commodity = totals.commodity""" % strings
        db.execute(sql)

        for country in config.countries:
            strings["country"] = country.lower()
            table = "%(extra_schema)s.%(country)s_io_import_%(year)d" % strings
            strings["io_import_table"] = table

            sql = "DROP TABLE IF EXISTS %(io_import_table)s" % strings
            db.execute(sql)

            sql = """SELECT comshare.country,
                            comshare.industry AS from_sector,
                            use.industry AS to_sector,
                            sum(use.value * comshare.use_share) AS value
                       INTO %(io_import_table)s
                       FROM %(schema)s.comshare_%(year)d comshare,
                            (SELECT from_country, industry, commodity, value
                               FROM %(schema)s.int_use_%(year)d
                              WHERE to_country = $1
                                AND from_country <> $1) use
                      WHERE comshare.country = use.from_country
                        AND comshare.commodity = use.commodity
                      GROUP BY comshare.country, comshare.industry,
                            use.industry""" % strings

            print(sql)
            stmt = db.prepare(sql)
            stmt(country)
Exemple #21
0
def parse_io():
    io_files = {
        1996: "410281134571.xls",
        1999: "4102715414971.xls",
        2001: "4122111363671.xls",
        2004: "611239581071.xls",
        2006: "9121414285971.xls",
        2007: "1139203871.xls",
        2008: "1139204871.xls",
        2009: "11229101502.xls",
        2010: "1122910141371.xls",
    }

    for (year, filename) in io_files.items():
        tablename = "%s.io_%d" % (config.SCHEMA, year)

        # millions are in NTD
        table = SQLTable(tablename, ["from_sector", "to_sector", "millions"],
                         ["varchar(255)", "varchar(255)", "float"])
        table.create()
        table.truncate()

        path = fileutils.getcache(filename, "tw/%d" % year)
        wb = xlrd.open_workbook(path)
        sheet = wb.sheets()[0]
        to_codes = sheet.row_values(0)
        to_names = sheet.row_values(1)
        for rowindex in range(2, sheet.nrows):
            row = sheet.row_values(rowindex)
            from_code = row[0].strip()
            from_name = row[1].strip()
            for i in range(2, len(to_names)):
                to_name = to_names[i].strip()
                value = row[i]
                table.insert([from_name, to_name, value])

        if year == 2010:
            strings = {
                "viewname": "%s.io_view_%d" % (config.SCHEMA, year),
                "tablename": tablename,
                "maptable": "%s.sector_map_%d" % (config.SCHEMA, year),
                "to_blacklist": sqlhelper.set_repr(config.to_blacklists[year]),
                "from_blacklist":
                sqlhelper.set_repr(config.from_blacklists[year]),
            }

            sql = """CREATE OR REPLACE VIEW %(viewname)s AS
                SELECT from_map.io_sector AS from_sector,
                       to_map.io_sector as to_sector,
                       sum(millions) as millions
                  FROM %(tablename)s io,
                       (SELECT DISTINCT io_sector, io_commod
                          FROM %(maptable)s) from_map,
                       (SELECT DISTINCT io_sector, io_ind
                          FROM %(maptable)s) to_map
                 WHERE io.to_sector NOT IN %(to_blacklist)s
                   AND io.from_sector NOT IN %(from_blacklist)s
                   AND from_map.io_commod = io.from_sector
                   AND to_map.io_ind = io.to_sector
                 GROUP BY from_map.io_sector, to_map.io_sector""" % strings

            print(sql)
            db.execute(sql)
Exemple #22
0
def create_views():
    for year in config.STUDY_YEARS:
        strings = {
            "year": year,
            "make_table": "%s.io_make_detail" % config.SCHEMA,
            "use_table": "%s.io_use_detail" % config.SCHEMA,
            "fd_table": "%s.io_fd_detail" % config.SCHEMA,

            "indshare_table": "%s.indshares_%d" % (config.SCHEMA, year),
            "cxctable": "%s.cxc_%d" % (config.SCHEMA, year),

            "comshare_table": "%s.comshares_%d" % (config.SCHEMA, year),
            "ixitable": "%s.ixi_%d" % (config.SCHEMA, year),
            "va_sectors": sqlhelper.set_repr(config.value_added),
           }

        # commodity output proportions
        runsql("""CREATE OR REPLACE VIEW %(comshare_table)s AS
                   SELECT make.industry, make.commodity,
                          cast(make.value as float) / comtotal.value AS output_share
                     FROM (SELECT industry, commodity, sum(value) as value
                             FROM %(make_table)s
                            WHERE year = %(year)d
                            GROUP BY industry, commodity) make,
                          (SELECT commodity, cast(sum(value) as float) AS value
                             FROM %(make_table)s
                            WHERE year = %(year)d
                            GROUP BY commodity) comtotal
                    WHERE make.value > 0
                      AND make.commodity = comtotal.commodity""" % strings)

        # intermediate output section of transactions table
        runsql("DROP TABLE IF EXISTS %(ixitable)s" % strings)

        runsql("""
           SELECT comshare.industry AS from_sector,
                  use.industry AS to_sector,
                  cast(use.value as float) * comshare.output_share as value
             INTO %(ixitable)s
             FROM (SELECT industry, commodity, sum(value) as value
                     FROM %(use_table)s
                    WHERE year = %(year)d
                    GROUP BY industry, commodity) use,
                  %(comshare_table)s comshare
            WHERE comshare.commodity = use.commodity""" % strings)

        # final demand section of transactions table
        runsql("""INSERT INTO %(ixitable)s
          SELECT comshare.industry as from_sector,
                 --split_part(fd.industry, ',', 1) AS to_sector,
                 fd.industry as to_sector,
                 cast(fd.value as float) * comshare.output_share AS value
            FROM (SELECT split_part(industry, ',', 1) as industry,
                         commodity, sum(value) as value
                    FROM %(fd_table)s
                   WHERE year = %(year)d
                   GROUP BY split_part(industry, ',', 1), commodity) fd,
                 %(comshare_table)s comshare
           WHERE comshare.commodity = fd.commodity""" % strings)

        # value added section of transactions table
        runsql("""INSERT INTO %(ixitable)s
           SELECT commodity AS from_sector,
                  industry AS to_sector,
                  cast(sum(value) as float)
             FROM %(use_table)s use
            WHERE year = %(year)d
              AND commodity IN %(va_sectors)s
            GROUP BY industry, commodity""" % strings)

        runsql("DROP VIEW %(comshare_table)s" % strings)
Exemple #23
0
            btu = expenditure * intensity
            energy_data[btu] = row

    print(year, total_expenditure, meat_expenditure / total_expenditure)

    btu_values = sorted(energy_data.keys(), reverse=True)
    for btu in btu_values[:10]:
        code = energy_data[btu]
        sector = io_codes[code]
        #print(code, sector, btu)

    #print(year, sum(energy_data.keys()))

    strings = {"tablename": "%s.transact_view_%d" % (config.IO_SCHEMA, year)}
    if year < 1997:
        strings["meat_codes"] = sqlhelper.set_repr(old_meat_codes)
    else:
        strings["meat_codes"] = sqlhelper.set_repr(new_meat_codes)

    stmt = db.prepare("""select from_sector, sum(fob)
                          from %(tablename)s
                         where to_sector in %(meat_codes)s
                         group by from_sector
                         order by sum(fob) desc""" % strings)
    result = stmt()
    for row in result[:10]:
        code = row[0]
        expenditure = row[1]
        #print(code, io_codes[code], expenditure)
Exemple #24
0
    print(year, total_expenditure, meat_expenditure / total_expenditure)

    btu_values = sorted(energy_data.keys(), reverse=True)
    for btu in btu_values[:10]:
        code = energy_data[btu]
        sector = io_codes[code]
        #print(code, sector, btu)

    #print(year, sum(energy_data.keys()))

    strings = {
        "tablename": "%s.transact_view_%d" % (config.IO_SCHEMA, year)
        }
    if year < 1997:
        strings["meat_codes"] = sqlhelper.set_repr(old_meat_codes)
    else:
        strings["meat_codes"] = sqlhelper.set_repr(new_meat_codes)

    stmt = db.prepare("""select from_sector, sum(fob)
                          from %(tablename)s
                         where to_sector in %(meat_codes)s
                         group by from_sector
                         order by sum(fob) desc""" % strings)
    result = stmt()
    for row in result[:10]:
        code = row[0]
        expenditure = row[1]
        #print(code, io_codes[code], expenditure)

Exemple #25
0
for year in config.STUDY_YEARS:
    print(year)
    iogen = cfgen.get_iogen()
    iogen.set_table("%s.ixi_%d" % (config.SCHEMA, year))

    exchange_rate = wiod.common.get_exchange_rate("CAN", year)
    if exchange_rate is None:
        exchange_rate = exrate.get_rate("ca", year)

    iogen.set_exchange_rate(exchange_rate)

    envgen = cfgen.get_envgen()
    envgen.set_universal_conditions([
        "year = %d" % year,
        "industry not in %s" % sqlhelper.set_repr(config.env_blacklist),
    ])

    io_harmonizer = matrixutils.generate_selector_matrix(
        "%s.sector_map" % config.SCHEMA, iogen.get_sectors(), "io_code",
        "harmonized", ["io_code is not null"])

    env_harmonizer = matrixutils.generate_selector_matrix(
        "%s.sector_map" % config.SCHEMA, envgen.get_sectors(), "env_code",
        "harmonized", ["env_code is not null"])

    series = ["1"]

    cfgen.prepare(year, series, io_harmonizer, env_harmonizer)

sector_titles = {}
Exemple #26
0
        iogen.set_table(iotable)
        iogen.set_fd_sectors(config.final_demand[year])
        iogen.blacklist_from_sectors(config.from_blacklists[year])
        iogen.blacklist_to_sectors(config.to_blacklists[year])
        iogen.set_harmonized_rows(config.io_harmonized_sectors[year])

        exchange_rate = wiod.common.get_exchange_rate("TWN", year)
        # tons / (million ntd * exchange_rate) = tons / M usd
        iogen.set_exchange_rate(exchange_rate)
        iogen.set_pce_col(config.pce_sector[year])
        iogen.set_export_col(config.export_sector[year])
    
        envtable = "%s.env_%d" % (config.SCHEMA, year)
        envgen = cfgen.get_envgen()
        envgen.set_table(envtable)
        env_blacklist = sqlhelper.set_repr(config.env_blacklist[year])
        env_condition = "sector NOT IN " + env_blacklist
        envgen.set_universal_conditions([env_condition])
    
        map_table = "%s.sector_map_%d" % (config.SCHEMA, year)
        env_harmonizer = matrixutils.generate_selector_matrix(
            map_table, envgen.get_sectors(), "env_sector", "harmonized_env")
        io_harmonizer = matrixutils.generate_selector_matrix(
            map_table, iogen.get_sectors(), "io_sector", "harmonized_env")
    
        series = config.env_series_for_code(series_code, year)
        cfgen.prepare(year, series, io_harmonizer, env_harmonizer)
    
    cfgen.describe()
    cfgen.describe(True)
    cfgen.counterfact(1999, "tw")