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)
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"
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)
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
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)
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)
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)
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" ])
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
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
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()
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)
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)
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)
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 )
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))
def set_fd_sectors(self, sectors): self.strings["fd_sectors"] = sqlhelper.set_repr(sectors) self.fd_sectors = sectors
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()
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)
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)
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)
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)
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)
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)
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 = {}
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")