def fd_sectors_for_year(year, is_hybrid): if is_hybrid: strings = { "code_table": "hybrid_codes_%d" % year, "x_table": "hybrid_transactions_%d" % year, } stmt = db.prepare(""" SELECT code FROM %(code_table)s WHERE (code IN (SELECT distinct from_sector from %(x_table)s) OR code IN (SELECT distinct to_sector from %(x_table)s)) AND sector_type = $1 ORDER BY code""" % strings) result = stmt(bea.FINAL_DEMAND) else: strings = { "code_table": "%s.codes_%d" % (config.IO_SCHEMA, year), #"x_table": "%s.transact_view_%d" % (config.IO_SCHEMA, year), "fd_criteria": bea.fd_sector_criteria[year], } stmt = db.prepare( "SELECT code FROM %(code_table)s WHERE %(fd_criteria)s" % strings) result = stmt() return [row[0] for row in result]
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 world_trade_stats(is_import): minyear = min(config.STUDY_YEARS) maxyear = max(config.STUDY_YEARS) clauses = [] for year in (minyear, maxyear): strings = { "year": year, "table": "%s.niot_%d" % (config.WIOD_SCHEMA, year), } if is_import: strings["where"] = "is_import IS true" else: strings["where"] = "to_ind = 'EXP'" clauses.append( """SELECT %(year)d, from_ind, sum(value), 100 * sum(value) / (SELECT sum(value) FROM %(table)s WHERE %(where)s) FROM %(table)s WHERE %(where)s GROUP BY from_ind""" % (strings)) stmt = db.prepare("\n UNION \n".join(clauses)) print() print_result(stmt(), minyear, maxyear)
def world_trade_stats(is_import): minyear = min(config.STUDY_YEARS) maxyear = max(config.STUDY_YEARS) clauses = [] for year in (minyear, maxyear): strings = { "year": year, "table": "%s.niot_%d" % (config.WIOD_SCHEMA, year), } if is_import: strings["where"] = "is_import IS true" else: strings["where"] = "to_ind = 'EXP'" clauses.append("""SELECT %(year)d, from_ind, sum(value), 100 * sum(value) / (SELECT sum(value) FROM %(table)s WHERE %(where)s) FROM %(table)s WHERE %(where)s GROUP BY from_ind""" % (strings)) stmt = db.prepare("\n UNION \n".join(clauses)) print() print_result(stmt(), minyear, maxyear)
def get_sector_names(include_sec=False): sector_names = {} if include_sec: compartor = "LIKE" else: compartor = "NOT LIKE" stmt = db.prepare( "SELECT code, description " + \ " FROM %s.industry_codes" % config.WIOD_SCHEMA + \ " WHERE code %s 'sec%%'" % compartor) for row in stmt(): if row[0] not in config.industry_blacklist and \ row[0] not in config.commodity_blacklist: sector_name = row[1] # replace long titles with custom titles we did for usa shortsector = row[0].replace("sec", "") if shortsector in wiod_code_map.codes: sector_name = wiod_code_map.codes[shortsector]["title"] sector_names[row[0]] = sector_name return sector_names
def generate_sector_stmt(self): strings = self.strings strings["conditions"] = self.generate_where() sql = """SELECT DISTINCT %(from_sector)s FROM %(iotable)s WHERE %(conditions)s""" % strings return db.prepare(sql)
def io_codes_for_year(year): codes = {} stmt = db.prepare("select * from %s.codes_%d" % (config.IO_SCHEMA, year)) result = stmt() for row in result: codes[row[0]] = row[1] return codes
def generate_Y_stmt(self): strings = self.strings strings["conditions"] = self.generate_where() sql = """SELECT %(from_sector)s, %(to_sector)s, %(value_column)s FROM %(iotable)s WHERE %(conditions)s AND %(to_sector)s IN %(fd_sectors)s""" % strings return db.prepare(sql)
def do_import_table(): minyear = min(config.STUDY_YEARS) maxyear = max(config.STUDY_YEARS) sector = 'CONS_h' fd = {} fd_imports = {} for year in (minyear, maxyear): strings = { "schema": config.WIOD_SCHEMA, "year": year, } stmt = db.prepare( """SELECT country, sum(value) FROM %(schema)s.niot_%(year)d WHERE to_ind = $1 AND is_import = $2 GROUP BY country""" % strings) fd[year] = {} fd_imports[year] = {} for (country, value) in stmt(sector, True): fd_imports[year][country] = value fd[year][country] = value for (country, value) in stmt(sector, False): fd[year][country] += value shares = {} for (country, total) in fd[maxyear].items(): share = fd_imports[maxyear][country] / total shares[share] = country sorted_shares = sorted(shares.keys(), reverse=True) midpoint = int(len(sorted_shares) / 2) for i in range(midpoint): values = [] for index in (i, i + midpoint): country = shares[sorted_shares[index]] minval = imfdata.convert_to_2005( fd_imports[minyear][country], country, minyear) maxval = imfdata.convert_to_2005( fd_imports[maxyear][country], country, maxyear) minshare = fd_imports[minyear][country] / fd[minyear][country] maxshare = fd_imports[maxyear][country] / fd[maxyear][country] values += [ config.countries[country], utils.add_commas(minval), utils.add_commas(maxval), "%.1f" % (minshare * 100), "%.1f" % (maxshare * 100), ""] # want blank space between two halves values.pop() # remove trailing empty string print(" & ".join(values) + " \\NN")
def pce_bridge_vector(year, pcegroup=None): view = "%s.nipa_pce_%d" % (config.IO_SCHEMA, year) iogen = iogen_for_year(year, True, True, True) # just to get sectors vector = NamedMatrix(False, rows=iogen.get_sectors(), cols=["pce"]) if pcegroup is None: # get total stmt = db.prepare( "SELECT commodity, sum(value) FROM %s GROUP BY commodity" % view) result = stmt() else: stmt = db.prepare( "SELECT commodity, value FROM %s WHERE pcegroup = $1" % view) result = stmt(pcegroup) for row in result: vector.set_element(row[0], "pce", row[1]) return vector
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 getall(self, conditions=[], args=[]): if len(conditions): whereclause = " AND ".join(conditions) sql = "SELECT * FROM %s WHERE %s" % (self.name, whereclause) else: sql = "SELECT * FROM %s" % self.name stmt = db.prepare(sql) if len(args): return stmt(*args) return stmt()
def __init__(self): if IMFData.__static: raise IMFData.__static self.rates = {} self.deflators = {} self.generic_stmt = db.prepare(""" SELECT value FROM %s.world_supplement WHERE country = $1 AND year = $2 AND measurement = $3""" % config.WIOD_SCHEMA) IMFData.__static = self
def generate_A_stmt(self): strings = self.strings strings["x_sql"] = self.sql_for_total_output() strings["conditions"] = self.generate_where("z") sql = """SELECT z.%(from_sector)s, z.%(to_sector)s, z.%(value_column)s / x.%(value_column)s as a FROM %(iotable)s z, (%(x_sql)s) x WHERE %(conditions)s AND z.%(to_sector)s NOT IN %(fd_sectors)s AND z.%(to_sector)s = x.__from_sector AND x.%(value_column)s <> 0""" % strings return db.prepare(sql)
def get_exchange_rate(self, country, year): if country not in self.rates: self.rates[country] = {} if year not in self.rates[country]: sql = """SELECT rate FROM %s.exchange_rates WHERE country = $1 AND year = $2""" % config.WIOD_SCHEMA stmt = db.prepare(sql) result = stmt(country, year) if len(result) and len(result[0]): self.rates[country][year] = result[0][0] elif common.config.DEBUG_MODE: print("warning: no exchange rate for %s, %d)" % (country, year)) return self.rates[country][year]
def get_wiod_trade_vector(from_country, to_country): yearstrings = [str(year) for year in config.STUDY_YEARS] vector = NamedMatrix(rows=yearstrings, cols=["value"]) strings = {"schema": config.WIOD_SCHEMA} for year in yearstrings: strings["year"] = year stmt = db.prepare("""SELECT sum(value) FROM %(schema)s.int_use_%(year)s WHERE from_country = $1 and to_country = $2""" % strings) result = stmt(from_country, to_country) if len(result) and len(result[0]): vector.set_element(year, "value", result[0][0]) return vector
def generate_map_stmt(self, maptable, env_col, other_col, conditions=[]): strings = { "maptable": maptable, "other_col": other_col, "self_col": env_col, } if len(conditions): strings["basic_condition"] = " AND ".join(conditions) else: strings["basic_condition"] = "TRUE" sql = """SELECT %(self_col)s, %(other_col)s FROM %(maptable)s WHERE %(basic_condition)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 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_industry_title(code): stmt = db.prepare( "select description from %s.industry_codes where code = $1" % config.WIOD_SCHEMA) result = stmt(code) return result[0][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)
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 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 trade_ratios(base_country, env_key): env_series = config.env_series_names[env_key] other_countries = sorted(config.countries.keys()) other_countries.remove(base_country) yearstrings = [str(y) for y in config.STUDY_YEARS] exported_y = NamedMatrix(rows=other_countries, cols=yearstrings) exported_E = NamedMatrix(rows=other_countries, cols=yearstrings) def describe_exporters(): print("Exports by country - " + env_key) print("dollars") for country in other_countries: formatted = [ ("%.0f" % exported_y.get_element(country, year)).rjust(6) for year in yearstrings ] print(country, " ".join(formatted)) print("emissions") for country in other_countries: formatted = [ ("%.0f" % exported_E.get_element(country, year)).rjust(6) for year in yearstrings ] print(country, " ".join(formatted)) print("intensities") intensities = exported_E.divide(exported_y) for country in other_countries: formatted = [ ("%.2f" % intensities.get_element(country, year)).rjust(6) for year in yearstrings ] print(country, " ".join(formatted)) ### prepare plots env_title = env_key.replace(" ", "-") plot_group = "import-%s-%s" % (env_title, base_country) plots = {} for sector in common.default_env_sectors: sector_title = common.get_industry_title(sector) plots[sector] = GNUPlot( "%s_%s" % (env_title, sector), "", #"Imports of %s - %s" % (sector_title, env_key), plot_group) plots["import"] = GNUPlot( "%s_import" % env_title, "", #"All imports - %s" % env_key, plot_group) plots["export"] = GNUPlot( "%s_export" % env_title, "", #"All imports - %s" % env_key, plot_group) def create_plots(): for (sector, plot) in plots.items(): plot.set_series_style( import_dollar_series, "pointtype 5 linetype rgb '#88BBFF'") # hollow circle plot.set_series_style( emission_series, "pointtype 7 linetype rgb '#1144FF'") # solid circle plot.set_series_style(self_emission_series, "pointtype 12 linetype rgb '#0000FF'") plot.set_series_style(export_dollar_series, "pointtype 4 linetype rgb '#CC9933'") plot.set_series_style(export_emissions_series, "pointtype 7 linetype rgb '#996600'") #plot.legend("width -8") plot.width = 480 plot.height = 300 plot.write_tables() plot.generate_plot() ### trade balance matrices export_balance = NamedMatrix(rows=common.default_env_sectors, cols=yearstrings) import_balance = NamedMatrix(rows=common.default_env_sectors, cols=yearstrings) all_E = NamedMatrix(rows=common.default_env_sectors, cols=yearstrings) def describe_balance(ratios=False): export_total = export_balance.sum(0) import_total = import_balance.sum(0) all_total = all_E.sum(0) balance = export_total.subtract(import_total) country_name = config.countries[base_country] oddyears = [ str(y) for y in filter(lambda x: x % 2 == 1, config.STUDY_YEARS) ] if ratios: balance_ratio = balance.divide(all_total) print( country_name.ljust(15) + " & " + " & ".join([("%.2f" % balance_ratio.get_element("sum", y)).rjust(6) for y in oddyears]) + " \\\\") else: print( country_name.ljust(15) + " & " + " & ".join([ utils.add_commas(balance.get_element("sum", y)).rjust(9) for y in oddyears ]) + " \\\\") def describe_balance_intensity(): export_total = export_balance.sum(0) import_total = import_balance.sum(0) all_total = all_E.sum(0) balance = export_total.subtract(import_total) balance_ratio = balance.divide(all_total) country_name = config.countries[base_country] years = [str(minyear), str(maxyear)] fields = [] for y in years: balance_val = balance.get_element("sum", y) ratio_val = balance_ratio.get_element("sum", y) (gdp_val, env_val, intensity) = \ common.get_efficiency(base_country, int(y)) if int(y) in balance_plots: plot = balance_plots[int(y)] # ratio = exports to imports plot.set_value("2 ratio", base_country, ratio_val) plot.set_value("1 intensity", base_country, intensity) if int(y) in worldmap: worldmap[int(y)].set_country_value(base_country, balance_val) fields.append(utils.add_commas(balance_val)) fields.append("%.2f" % ratio_val) fields.append("%.2f" % intensity) print(country_name.ljust(15) + " & " + " & ".join(fields) + " \\NN") ### iogen = common.iogen_for_year(config.STUDY_YEARS[0]) envgen = common.envgen_for_year(config.STUDY_YEARS[0]) strings = {"schema": config.WIOD_SCHEMA} for year in config.STUDY_YEARS: strings["year"] = year base_E = get_wiod_env_vector(base_country, year, env_series) all_E.set_column(str(year), base_E) base_E_sum = base_E.sum() iogen.set_table("%(schema)s.indbyind_%(year)d" % strings) iogen.set_condition_args(base_country) envgen.set_table("%(schema)s.env_%(year)d" % strings) envgen.set_condition_args(base_country) envgen.set_sectors(common.default_env_sectors) # prepare base country values y = iogen.get_Y() import_column = common.get_import_vector(iogen) base_imports = import_column.sum() base_gdp = y.sum() harmonizer = common.get_io_harmonizer(iogen) base_y = harmonizer.matrix_mult(y.get_total()) x = iogen.get_x() E = envgen.get_env_vector(env_series) L = iogen.get_L() J = E.divide(harmonizer.matrix_mult(x), ignore_zero_denom=True) base_JsL = J.square_matrix_from_diag()\ .matrix_mult(harmonizer).matrix_mult(L) exported = base_JsL.matrix_mult(y.get_exports()) export_balance.set_column(str(year), exported) plots["export"].set_value(export_emissions_series, year, exported.sum() / base_E_sum) plots["export"].set_value(export_dollar_series, year, y.get_exports().sum() / base_gdp) # prepare other country values stmt = db.prepare("""SELECT from_sector, sum(value) FROM wiod_plus.%s_io_import_%d WHERE country = $1 AND from_sector NOT IN ('ITM', 'IMP', 'Rex') GROUP BY from_sector""" % (base_country.lower(), year)) imported_E = None imported_y = None domestic_E = None # emissions under domestic technology assumption for country in other_countries: envgen.set_condition_args(country) envgen.set_sectors(common.default_env_sectors) iogen.set_condition_args(country) sectors = iogen.get_sectors( ) # number of sectors varies by country E = envgen.get_env_vector(env_series) if E.mat() is None: # this country has no data for env series continue imports = NamedMatrix(rows=sectors, cols=["imports"]) db_result = stmt(country) if not len(db_result): # we can't do any of the following with a blank import vector continue for row in db_result: imports.set_element(row[0], "imports", row[1]) sel = common.get_io_harmonizer(iogen) x = iogen.get_x() L = iogen.get_L() J = E.divide(sel.matrix_mult(x), ignore_zero_denom=True) JsL = J.square_matrix_from_diag().matrix_mult(sel).matrix_mult(L) current_E = JsL.matrix_mult(imports) current_y = sel.matrix_mult(imports) # temporary dumb way to deal with sector mismatch compat_imports = NamedMatrix(rows=base_JsL.get_columns(), cols=["imports"]) for col in base_JsL.get_columns(): if col in sectors: compat_imports.set_element(col, "imports", imports.get_element(col)) current_domestic_E = base_JsL.matrix_mult(compat_imports) imported_E = sum_if_not_none(imported_E, current_E) imported_y = sum_if_not_none(imported_y, current_y) domestic_E = sum_if_not_none(domestic_E, current_domestic_E) exported_y.set_element(country, str(year), imports.sum()) exported_E.set_element(country, str(year), current_E.sum()) # populate results table TradeResultsTable.insert_exports(year, base_country, exported) TradeResultsTable.insert_imports(year, base_country, imported_E) # generate import plots for sector in common.default_env_sectors: base_y_val = base_y.get_element(sector) if base_y_val > 0: plots[sector].set_value( import_dollar_series, year, imported_y.get_element(sector) / base_y_val) base_E_val = base_E.get_element(sector) if base_E_val > 0: plots[sector].set_value( emission_series, year, imported_E.get_element(sector) / base_E_val) plots[sector].set_value( self_emission_series, year, domestic_E.get_element(sector) / base_E_val) plots["import"].set_value(import_dollar_series, year, imported_y.sum() / base_y.sum()) plots["import"].set_value(emission_series, year, imported_E.sum() / base_E_sum) plots["import"].set_value(self_emission_series, year, domestic_E.sum() / base_E_sum) if year in dta_plots and base_country in dta_countries: dta_plots[year].set_value("DTA", config.countries[base_country], imported_E.sum() / base_E_sum) dta_plots[year].set_value("No DTA", config.countries[base_country], domestic_E.sum() / base_E_sum) # this is for DTA vs non-DTA table #print(base_country, year, # imported_E.sum(), # domestic_E.sum(), # imported_E.sum() / base_E_sum, # domestic_E.sum() / base_E_sum) plots["export"].set_value(emission_series, year, imported_E.sum() / base_E_sum) import_balance.set_column(str(year), imported_E) #describe_exporters() #create_plots() #describe_balance(True) describe_balance_intensity()
# k tons / (M pounds * exchange_rate) = k tons / million usd iogen.set_exchange_rate(exchange_rate) envgen = cfgen.get_envgen() envgen.set_table("%s.env_%d" % (config.SCHEMA, year)) # we need to keep building this up because # some years are missing sectors env_harmonizer = matrixutils.generate_selector_matrix( "%s.code_map" % config.SCHEMA, envgen.get_sectors(), "env_code", "harmonized", ["env_code is not null"]) series = env_series_code cfgen.prepare(year, series, io_harmonizer, env_harmonizer) sector_titles = {} stmt = db.prepare("select distinct harmonized, description" + " from %s.code_map order by harmonized" % config.SCHEMA) for row in stmt(): sector_titles[row[0]] = row[1] cfgen.set_sector_titles(sector_titles) cfgen.describe() cfgen.describe(True) cfgen.counterfact(1995, "uk")
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)
from common.dbconnect import db from common.ioutils import IOMatrixGenerator, EnvMatrixGenerator from common.counterfact import CounterfactGenerator iogen = IOMatrixGenerator(transaction_table=None, from_sector_name="from_sector", to_sector_name="to_sector", value_column_name="value") envgen = EnvMatrixGenerator(envtable=None, ind_col_name="sector", series_col_name="series", value_col_name="value") sector_titles = {} stmt = db.prepare("select distinct harmonized, description" + " from jp.io_map_1990 order by harmonized") for row in stmt(): sector_titles[row[0]] = row[1] cfgen = CounterfactGenerator(iogen, envgen) for series_code in config.env_series.keys(): cfgen.set_series_code(series_code) for year in config.STUDY_YEARS: iogen = cfgen.get_iogen() iogen.set_table("%s.ixi_%d" % (config.SCHEMA, year)) iogen.set_fd_sectors(config.fd_sectors[year]) iogen.blacklist_from_sectors(config.from_blacklists[year]) iogen.blacklist_to_sectors(config.to_blacklists[year]) iogen.set_pce_col(config.pce_sector[year])
gdp_value = Y_standard.get_total().sum() / 1000 # result is millions use_vector = L.matrix_mult(hybrid_vector) for i in range(len(energy_codes)): code = energy_codes[i] if eia.is_fossil_fuel(code): name = eia.name_for_naics(code) value = use_vector.get_element(rowname=code) # billion Btu # divide billion Btu by 1k for tBtu ff_plot.set_value(year, name, value / 1000) # div billion Btu by MM$ for kBtu/$ ff_iplot.set_value(year, name, value / gdp_value) # manually generate electricity figures stmt = db.prepare( "select source, use_btu from %s.seds_short_%d where sector = 'EI'" % (config.EIA_SCHEMA, year)) result = stmt() for row in result: source = row[0] btu = row[1] # billion Btu elec_plot.set_value(elec_sources[source], year, btu / 1000) # tBtu elec_iplot.set_value(elec_sources[source], year, btu / gdp_value) stmt = db.prepare( "select source, use_btu " + \ " from %s.seds_us_%d " % (config.EIA_SCHEMA, year) + " where (source = 'LO' and sector = 'TC') " + " or (source = 'TE' and sector = 'EI')") result = stmt() for row in result:
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 prepare(self): placeholders = ["$%d" % (i+1) for i in range(len(self.colnames))] self.stmt = db.prepare( "INSERT INTO %s VALUES ( %s )" % (self.name, ", ".join(placeholders)))
def generate_sector_stmt(self): sql = """SELECT DISTINCT %(ind_col)s FROM %(envtable)s WHERE %(basic_condition)s ORDER BY %(ind_col)s""" % self.strings return db.prepare(sql)
from common.counterfact import CounterfactGenerator iogen = IOMatrixGenerator( transaction_table=None, from_sector_name="from_sector", to_sector_name="to_sector", value_column_name="value") envgen = EnvMatrixGenerator( envtable=None, ind_col_name="sector", series_col_name="series", value_col_name="value") sector_titles = {} stmt = db.prepare("select distinct harmonized, description" + " from jp.io_map_1990 order by harmonized") for row in stmt(): sector_titles[row[0]] = row[1] cfgen = CounterfactGenerator(iogen, envgen) for series_code in config.env_series.keys(): cfgen.set_series_code(series_code) for year in config.STUDY_YEARS: iogen = cfgen.get_iogen() iogen.set_table("%s.ixi_%d" % (config.SCHEMA, year)) iogen.set_fd_sectors(config.fd_sectors[year]) iogen.blacklist_from_sectors(config.from_blacklists[year]) iogen.blacklist_to_sectors(config.to_blacklists[year]) iogen.set_pce_col(config.pce_sector[year])
def trade_ratios(base_country, env_key): env_series = config.env_series_names[env_key] other_countries = sorted(config.countries.keys()) other_countries.remove(base_country) yearstrings = [str(y) for y in config.STUDY_YEARS] exported_y = NamedMatrix(rows=other_countries, cols=yearstrings) exported_E = NamedMatrix(rows=other_countries, cols=yearstrings) def describe_exporters(): print("Exports by country - " + env_key) print("dollars") for country in other_countries: formatted = [("%.0f" % exported_y.get_element(country, year)).rjust(6) for year in yearstrings] print(country, " ".join(formatted)) print("emissions") for country in other_countries: formatted = [("%.0f" % exported_E.get_element(country, year)).rjust(6) for year in yearstrings] print(country, " ".join(formatted)) print("intensities") intensities = exported_E.divide(exported_y) for country in other_countries: formatted = [("%.2f" % intensities.get_element(country, year)).rjust(6) for year in yearstrings] print(country, " ".join(formatted)) ### prepare plots env_title = env_key.replace(" ", "-") plot_group = "import-%s-%s" % (env_title, base_country) plots = {} for sector in common.default_env_sectors: sector_title = common.get_industry_title(sector) plots[sector] = GNUPlot("%s_%s" % (env_title, sector), "", #"Imports of %s - %s" % (sector_title, env_key), plot_group) plots["import"] = GNUPlot("%s_import" % env_title, "", #"All imports - %s" % env_key, plot_group) plots["export"] = GNUPlot("%s_export" % env_title, "", #"All imports - %s" % env_key, plot_group) def create_plots(): for (sector, plot) in plots.items(): plot.set_series_style( import_dollar_series, "pointtype 5 linetype rgb '#88BBFF'") # hollow circle plot.set_series_style( emission_series, "pointtype 7 linetype rgb '#1144FF'") # solid circle plot.set_series_style( self_emission_series, "pointtype 12 linetype rgb '#0000FF'") plot.set_series_style( export_dollar_series, "pointtype 4 linetype rgb '#CC9933'") plot.set_series_style( export_emissions_series, "pointtype 7 linetype rgb '#996600'") #plot.legend("width -8") plot.width = 480 plot.height = 300 plot.write_tables() plot.generate_plot() ### trade balance matrices export_balance = NamedMatrix(rows=common.default_env_sectors, cols=yearstrings) import_balance = NamedMatrix(rows=common.default_env_sectors, cols=yearstrings) all_E = NamedMatrix(rows=common.default_env_sectors, cols=yearstrings) def describe_balance(ratios=False): export_total = export_balance.sum(0) import_total = import_balance.sum(0) all_total = all_E.sum(0) balance = export_total.subtract(import_total) country_name = config.countries[base_country] oddyears = [str(y) for y in filter(lambda x: x % 2 == 1, config.STUDY_YEARS)] if ratios: balance_ratio = balance.divide(all_total) print(country_name.ljust(15) + " & " + " & ".join( [("%.2f" % balance_ratio.get_element("sum", y)).rjust(6) for y in oddyears]) + " \\\\") else: print(country_name.ljust(15) + " & " + " & ".join( [utils.add_commas(balance.get_element("sum", y)).rjust(9) for y in oddyears]) + " \\\\") def describe_balance_intensity(): export_total = export_balance.sum(0) import_total = import_balance.sum(0) all_total = all_E.sum(0) balance = export_total.subtract(import_total) balance_ratio = balance.divide(all_total) country_name = config.countries[base_country] years = [str(minyear), str(maxyear)] fields = [] for y in years: balance_val = balance.get_element("sum", y) ratio_val = balance_ratio.get_element("sum", y) (gdp_val, env_val, intensity) = \ common.get_efficiency(base_country, int(y)) if int(y) in balance_plots: plot = balance_plots[int(y)] # ratio = exports to imports plot.set_value("2 ratio", base_country, ratio_val) plot.set_value("1 intensity", base_country, intensity) if int(y) in worldmap: worldmap[int(y)].set_country_value(base_country, balance_val) fields.append(utils.add_commas(balance_val)) fields.append("%.2f" % ratio_val) fields.append("%.2f" % intensity) print(country_name.ljust(15) + " & " + " & ".join(fields) + " \\NN") ### iogen = common.iogen_for_year(config.STUDY_YEARS[0]) envgen = common.envgen_for_year(config.STUDY_YEARS[0]) strings = {"schema": config.WIOD_SCHEMA} for year in config.STUDY_YEARS: strings["year"] = year base_E = get_wiod_env_vector(base_country, year, env_series) all_E.set_column(str(year), base_E) base_E_sum = base_E.sum() iogen.set_table("%(schema)s.indbyind_%(year)d" % strings) iogen.set_condition_args(base_country) envgen.set_table("%(schema)s.env_%(year)d" % strings) envgen.set_condition_args(base_country) envgen.set_sectors(common.default_env_sectors) # prepare base country values y = iogen.get_Y() import_column = common.get_import_vector(iogen) base_imports = import_column.sum() base_gdp = y.sum() harmonizer = common.get_io_harmonizer(iogen) base_y = harmonizer.matrix_mult(y.get_total()) x = iogen.get_x() E = envgen.get_env_vector(env_series) L = iogen.get_L() J = E.divide(harmonizer.matrix_mult(x), ignore_zero_denom=True) base_JsL = J.square_matrix_from_diag()\ .matrix_mult(harmonizer).matrix_mult(L) exported = base_JsL.matrix_mult(y.get_exports()) export_balance.set_column(str(year), exported) plots["export"].set_value( export_emissions_series, year, exported.sum() / base_E_sum) plots["export"].set_value( export_dollar_series, year, y.get_exports().sum() / base_gdp) # prepare other country values stmt = db.prepare("""SELECT from_sector, sum(value) FROM wiod_plus.%s_io_import_%d WHERE country = $1 AND from_sector NOT IN ('ITM', 'IMP', 'Rex') GROUP BY from_sector""" % (base_country.lower(), year)) imported_E = None imported_y = None domestic_E = None # emissions under domestic technology assumption for country in other_countries: envgen.set_condition_args(country) envgen.set_sectors(common.default_env_sectors) iogen.set_condition_args(country) sectors = iogen.get_sectors() # number of sectors varies by country E = envgen.get_env_vector(env_series) if E.mat() is None: # this country has no data for env series continue imports = NamedMatrix(rows=sectors, cols=["imports"]) db_result = stmt(country) if not len(db_result): # we can't do any of the following with a blank import vector continue for row in db_result: imports.set_element(row[0], "imports", row[1]) sel = common.get_io_harmonizer(iogen) x = iogen.get_x() L = iogen.get_L() J = E.divide(sel.matrix_mult(x), ignore_zero_denom=True) JsL = J.square_matrix_from_diag().matrix_mult(sel).matrix_mult(L) current_E = JsL.matrix_mult(imports) current_y = sel.matrix_mult(imports) # temporary dumb way to deal with sector mismatch compat_imports = NamedMatrix(rows=base_JsL.get_columns(), cols=["imports"]) for col in base_JsL.get_columns(): if col in sectors: compat_imports.set_element( col, "imports", imports.get_element(col)) current_domestic_E = base_JsL.matrix_mult(compat_imports) imported_E = sum_if_not_none(imported_E, current_E) imported_y = sum_if_not_none(imported_y, current_y) domestic_E = sum_if_not_none(domestic_E, current_domestic_E) exported_y.set_element(country, str(year), imports.sum()) exported_E.set_element(country, str(year), current_E.sum()) # populate results table TradeResultsTable.insert_exports(year, base_country, exported) TradeResultsTable.insert_imports(year, base_country, imported_E) # generate import plots for sector in common.default_env_sectors: base_y_val = base_y.get_element(sector) if base_y_val > 0: plots[sector].set_value( import_dollar_series, year, imported_y.get_element(sector) / base_y_val) base_E_val = base_E.get_element(sector) if base_E_val > 0: plots[sector].set_value( emission_series, year, imported_E.get_element(sector) / base_E_val) plots[sector].set_value( self_emission_series, year, domestic_E.get_element(sector) / base_E_val) plots["import"].set_value(import_dollar_series, year, imported_y.sum() / base_y.sum()) plots["import"].set_value(emission_series, year, imported_E.sum() / base_E_sum) plots["import"].set_value(self_emission_series, year, domestic_E.sum() / base_E_sum) if year in dta_plots and base_country in dta_countries: dta_plots[year].set_value("DTA", config.countries[base_country], imported_E.sum() / base_E_sum) dta_plots[year].set_value("No DTA", config.countries[base_country], domestic_E.sum() / base_E_sum) # this is for DTA vs non-DTA table #print(base_country, year, # imported_E.sum(), # domestic_E.sum(), # imported_E.sum() / base_E_sum, # domestic_E.sum() / base_E_sum) plots["export"].set_value(emission_series, year, imported_E.sum() / base_E_sum) import_balance.set_column(str(year), imported_E) #describe_exporters() #create_plots() #describe_balance(True) describe_balance_intensity()
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 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)
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 = {} stmt = db.prepare("select distinct code, description" + " from %s.ind_codes order by code" % config.SCHEMA) for row in stmt(): sector_titles[row[0]] = row[1] cfgen.set_sector_titles(sector_titles) cfgen.describe() cfgen.describe(True) cfgen.counterfact(1997, "ca")
def generate_x_stmt(self): return db.prepare(self.sql_for_total_output())
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 = {} stmt = db.prepare("select distinct code, description" + " from %s.ind_codes order by code" % config.SCHEMA) for row in stmt(): sector_titles[row[0]] = row[1] cfgen.set_sector_titles(sector_titles) cfgen.describe() cfgen.describe(True) cfgen.counterfact(1997, "ca")
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()