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 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 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 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 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 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 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_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])
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 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()
