def do_zip(url):
print url
fh = dl.grab(url)
mt, = list(messytables.zip.ZIPTableSet(fh).tables)
fh = None
xy = xypath.Table.from_messy(mt)
mt = None
print "...got"
headers = xy.filter(lambda c: c.y == 0)
country = headers.filter("Country").assert_one()
items = headers.filter("Item").assert_one().fill(xypath.DOWN).filter("Grand Total + (Total)")
units = headers.filter("Unit").assert_one().fill(xypath.DOWN).filter("kcal/capita/day")
filtered_items = items.select_other(lambda a, b: a.y == b.y, units)
years = country.fill(xypath.RIGHT).filter(re.compile("Y\d\d\d\d$"))
assert items
assert units
assert filtered_items
assert years
for i in filtered_items:
values = dict(valuetemplate)
values['source'] = url
countrycodecell, = i.junction(country)
values['region'] = countrycodecell[2].value
year_junction = i.junction(years)
for _, period, value in year_junction:
values['period'] = period.value.replace("Y", "")
values['value'] = value.value
orm.Value(**values).save()
print values
orm.session.commit()
def parsesheet(url):
rowset = messytables.excel.XLSTableSet(dl.grab(url)).tables[0]
for i, row in enumerate(rowset):
if i == 0:
headers = [x.value for x in row]
continue
yield OrderedDict(list(zip(headers, [x.value for x in row])))
def getstats(url, country="PLACEHOLDER"):
handle = dl.grab(url)
mts = messytables.any.any_tableset(handle)
saves = 0
for mt in mts.tables:
table = xypath.Table.from_messy(mt)
inds = table.filter(lambda b: b.x == 0 and "EPI" in b.value)
if not inds:
continue
assert len(inds) == 1
top, = table.filter(lambda b: 'to the top' in b.value)
value, = inds.junction(top)
for ind in inds:
split = split_ind(ind.value)
values_tosave = dict(value_template)
values_tosave['source'] = url
values_tosave['region'] = country
values_tosave['value'] = value[2].value
indicator = {'indID': split['indID'],
'name': split['indID'],
'units': split['units']}
orm.Indicator(**indicator).save()
values_tosave['indID'] = split['indID']
orm.Value(**values_tosave).save()
saves = saves + 1
if saves != 1:
print "huh, %d saves for %r" % (saves, url)
def do_indicator(ind="566"):
baseurl="http://mdgs.un.org/unsd/mdg/Handlers/ExportHandler.ashx?Type=Csv&Series=%s"
url = baseurl % ind
value_template['source'] = url
handle = dl.grab(url)
mt, = messytables.any.any_tableset(handle).tables
table = xypath.Table.from_messy(mt)
country_anchor = table.filter("Country").assert_one()
years = country_anchor.fill(xypath.RIGHT).filter(re.compile("\d\d\d\d"))
countries = country_anchor.fill(xypath.DOWN)
indicator = table.filter("Series").shift(xypath.DOWN).value
SEPARATOR = ', '
if SEPARATOR in indicator:
i_name = SEPARATOR.join(indicator.split(SEPARATOR)[:-1])
i_unit = indicator.split(SEPARATOR)[-1]
else:
i_name = indicator
i_unit = ''
value_template['indID'] = indicator
assert i_name
indicator = {'indID': indicator,
'name': i_name,
'units': i_unit}
orm.Indicator(**indicator).save()
# countries also gets some rubbish, but junction will ignore it.
for c_cell, y_cell, v_cell in countries.junction(years):
value = dict(value_template)
value['region'] = c_cell.value
value['period'] = y_cell.value
value['value'] = v_cell.value
orm.Value(**value).save()
def parsesheet(url):
rowset = messytables.excel.XLSTableSet(dl.grab(url)).tables[0]
for i, row in enumerate(rowset):
if i == 0:
headers = [x.value for x in row]
continue
yield OrderedDict(list(zip(headers, [x.value for x in row])))
def getstats(url, country="PLACEHOLDER"):
handle = dl.grab(url)
mts = messytables.any.any_tableset(handle)
saves = 0
for mt in mts.tables:
table = xypath.Table.from_messy(mt)
inds = table.filter(lambda b: b.x == 0 and "EPI" in b.value)
if not inds:
continue
assert len(inds) == 1
top, = table.filter(lambda b: 'to the top' in b.value)
value, = inds.junction(top)
for ind in inds:
split = split_ind(ind.value)
values_tosave = dict(value_template)
values_tosave['source'] = url
values_tosave['region'] = country
values_tosave['value'] = value[2].value
indicator = {
'indID': split['indID'],
'name': split['indID'],
'units': split['units']
}
orm.Indicator(**indicator).save()
values_tosave['indID'] = split['indID']
orm.Value(**values_tosave).save()
saves = saves + 1
if saves != 1:
print "huh, %d saves for %r" % (saves, url)
def do_zip(url):
print url
fh = dl.grab(url)
mt, = list(messytables.zip.ZIPTableSet(fh).tables)
fh = None
xy = xypath.Table.from_messy(mt)
mt = None
print "...got"
headers = xy.filter(lambda c: c.y == 0)
country = headers.filter("Country").assert_one()
items = headers.filter("Item").assert_one().fill(xypath.DOWN).filter("Grand Total + (Total)")
elements = headers.filter("Element").assert_one().fill(xypath.DOWN).filter("Food supply (kcal/capita/day)")
filtered_items = items.select_other(lambda a, b: a.y == b.y, elements)
years = country.fill(xypath.RIGHT).filter(re.compile("Y\d\d\d\d$"))
for i in filtered_items:
values = dict(valuetemplate)
values['source'] = url
countrycodecell, = i.junction(country)
values['region'] = countrycodecell[2].value
year_junction = i.junction(years)
for _, period, value in year_junction:
values['period'] = period.value.replace("Y", "")
values['value'] = value.value
orm.Value(**values).save()
orm.session.commit()
def do_indicator(ind="566"):
baseurl = "http://mdgs.un.org/unsd/mdg/Handlers/ExportHandler.ashx?Type=Csv&Series=%s"
url = baseurl % ind
value_template['source'] = url
handle = dl.grab(url)
mt, = messytables.any.any_tableset(handle).tables
table = xypath.Table.from_messy(mt)
country_anchor = table.filter("Country").assert_one()
years = country_anchor.fill(xypath.RIGHT).filter(re.compile("\d\d\d\d"))
countries = country_anchor.fill(xypath.DOWN)
indicator = table.filter("Series").shift(xypath.DOWN).value
SEPARATOR = ', '
if SEPARATOR in indicator:
i_name = SEPARATOR.join(indicator.split(SEPARATOR)[:-1])
i_unit = indicator.split(SEPARATOR)[-1]
else:
i_name = indicator
i_unit = ''
value_template['indID'] = indicator
assert i_name
indicator = {'indID': indicator, 'name': i_name, 'units': i_unit}
orm.Indicator(**indicator).save()
# countries also gets some rubbish, but junction will ignore it.
for c_cell, y_cell, v_cell in countries.junction(years):
value = dict(value_template)
value['region'] = c_cell.value
value['period'] = y_cell.value
value['value'] = v_cell.value
orm.Value(**value).save()
def main():
for sheet in spreadsheets:
shortname = sheet.split('/')[-1].split('.')[0]
dsID = 'esa-unpd-' + shortname.replace('_', '-').split('-')[0]
year_text, = re.findall('\d{4}', dsID)
dataset = {"dsID": dsID,
"last_updated": year_text,
"last_scraped": orm.now(),
"name": "esa-unpd"}
orm.DataSet(**dataset).save()
indicator = {"indID": shortname,
"name": shortname,
"units": ''
}
# we replace the indicator name, so not saving now.
# orm.Indicator(**indicator).save()
value_template = {"dsID": dsID,
"is_number": True,
"source": sheet}
raw = dl.grab(sheet)
mtables = messytables.any.any_tableset(raw)
names = [x.name for x in mtables.tables]
if 'ESTIMATES' in names:
mt = mtables['ESTIMATES']
else:
mt = mtables['PROPORTION-URBAN']
table = xypath.Table.from_messy(mt)
filestring = table.filter(re.compile("File[^:]*:.*")).assert_one().value
indicator['name'], indicator['units'] = parse_file_string(filestring)
print indicator['name']
orm.Indicator(**indicator).save()
region_header = table.filter(re.compile("Major area, region, country or area.*")).assert_one()
ccode_header = table.filter(re.compile("Country.code")).assert_one()
regions = region_header.fill(xypath.DOWN)
years = ccode_header.fill(xypath.RIGHT)
for region_cell, year_cell, value_cell in regions.junction(years):
value = dict(value_template)
value['indID'] = indicator['indID']
value['region'] = region_cell.value
year_value = year_cell.value
if isinstance(year_value, basestring) and '-' in year_value:
year1, _, year2 = year_value.partition('-')
year_count = int(year2) - int(year1)
assert year_count == 5
year_value = "%s/P%dY" % (year1, year_count)
value['period'] = year_value
value['value'] = value_cell.value
orm.Value(**value).save()
#print value
orm.session.commit()
def main():
for sheet in spreadsheets:
print sheet
shortname = sheet.split('/')[-1].split('.')[0]
dsID = 'esa-unpd-' + shortname.replace('_', '-').split('-')[0]
year_text, = re.findall('\d{4}', dsID)
dataset = {
"dsID": dsID,
"last_updated": year_text,
"last_scraped": orm.now(),
"name": "esa-unpd"
}
orm.DataSet(**dataset).save()
indicator = {"indID": shortname, "name": shortname, "units": ''}
# we replace the indicator name, so not saving now.
# orm.Indicator(**indicator).save()
value_template = {"dsID": dsID, "is_number": True, "source": sheet}
raw = dl.grab(sheet)
mtables = messytables.any.any_tableset(raw)
names = [x.name for x in mtables.tables]
if 'ESTIMATES' in names:
mt = mtables['ESTIMATES']
else:
mt = mtables['PROPORTION-URBAN']
table = xypath.Table.from_messy(mt)
filestring = table.filter(
re.compile("File[^:]*:.*")).assert_one().value
indicator['name'], indicator['units'] = parse_file_string(filestring)
print indicator['name']
orm.Indicator(**indicator).save()
region_header = table.filter(
re.compile("Major area, region, country or area.*")).assert_one()
ccode_header = table.filter(re.compile("Country.code")).assert_one()
regions = region_header.fill(xypath.DOWN)
years = ccode_header.fill(xypath.RIGHT)
for region_cell, year_cell, value_cell in regions.junction(years):
value = dict(value_template)
value['indID'] = indicator['indID']
value['region'] = region_cell.value
year_value = year_cell.value
if isinstance(year_value, basestring) and '-' in year_value:
year1, _, year2 = year_value.partition('-')
year_count = int(year2) - int(year1)
assert year_count == 5
year_value = "%s/P%dY" % (year1, year_count)
value['period'] = year_value
value['value'] = value_cell.value
orm.Value(**value).save()
#print value
orm.session.commit()
def do_indicator(ind):
print "indicator:", ind
fh = dl.grab(baseurl % ind)
mt, = messytables.commas.CSVTableSet(fh).tables
mt_list = list(mt)
try:
headers = mt_list[0]
except IndexError:
headers = []
if len(headers) == 0:
print "Error getting headers from ", ind
raise RuntimeError("No header in {}".format(ind))
logging.warn("headers {!r}".format(headers))
rest = mt_list[1:]
for row in rest:
if len(row) == 0:
continue # skip empty row
rowdict = {x[0].value: x[1].value for x in zip(headers, row)}
try:
name, unit = units(rowdict['GHO (DISPLAY)'])
except Exception:
fh.seek(0)
print fh.read()
raise
indID = rowdict['GHO (CODE)']
for lookup in [
"SEX", "RESIDENCEAREATYPE", "EDUCATIONLEVEL", "WEALTHQUINTILE"
]:
lookup_code = lookup + " (CODE)"
lookup_name = lookup + " (DISPLAY)"
if lookup_code in rowdict: # header = "SEX (CODE)"
if rowdict[lookup_code]: # value != ""
indID = indID + "({}={})".format(lookup,
rowdict[lookup_code])
name = name + " - " + rowdict[lookup_name]
value_dict = {
"value": rowdict['Display Value'],
"period": rowdict['YEAR (DISPLAY)'],
"indID": indID,
"region": rowdict["COUNTRY (CODE)"],
"dsID": "athena-api",
"source": baseurl % ind,
"is_number": True
}
indicator_dict = {'indID': indID, 'name': name, 'units': unit}
orm.Indicator(**indicator_dict).save()
orm.Value(**value_dict).save()
def do_file(url="http://bit.ly/14FRxGV"):
fh = dl.grab(url)
mts = messytables.excel.XLSTableSet(fh)
xy = discover_table(mts)
print "...got"
home = xy.filter("(home)")
years = home.fill(xypath.RIGHT)
countries = home.fill(xypath.DOWN)
for country, year, value in countries.junction(years):
values = dict(valuetemplate)
values['source'] = url
values['region'] = country.value
values['period'] = niceyear(year.value) # TODO
values['value'] = value.value
orm.Value(**values).save()
orm.session.commit()
def do_file(url="http://bit.ly/14FRxGV"):
fh = dl.grab(url)
mts = messytables.excel.XLSTableSet(fh)
xy = discover_table(mts)
print "...got"
home = xy.filter("(home)")
years = home.fill(xypath.RIGHT)
countries = home.fill(xypath.DOWN)
for country, year, value in countries.junction(years):
values = dict(valuetemplate)
values['source'] = url
values['region'] = country.value
values['period'] = niceyear(year.value) # TODO
values['value'] = value.value
orm.Value(**values).save()
orm.session.commit()
def getcountry(threeletter="PAK"):
print threeletter
baseurl = "http://api.worldbank.org/v2/en/country/{}?downloadformat=excel"
value = {'dsID': 'World Bank',
'region': threeletter,
'source': baseurl.format(threeletter.lower()),
'is_number': True}
while True:
fh = dl.grab(baseurl.format(threeletter), [404])
if not fh:
return
try:
messy = messytables.excel.XLSTableSet(fh)
break # success!
except messytables.error.ReadError, e:
print e
return
def getstats():
url = 'http://hdr.undp.org/en/content/combined-gross-enrolment-education-both-sexes'
handle = dl.grab(url)
mts = messytables.any.any_tableset(handle)
saves = 0
mt = mts.tables[0]
table = xypath.Table.from_messy(mt)
pivot, = table.filter(lambda c: 'Country' in c.value)
years = pivot.fill(xypath.RIGHT)
countries = pivot.fill(xypath.DOWN)
for year, country, value in years.junction(countries):
output = dict(output_template)
output['source'] = url
output['region'] = country.value.strip()
output['value'] = value.value.strip()
orm.Value(**output).save()
saves = saves + 1
assert saves
def getstats():
url = 'http://hdr.undp.org/en/content/combined-gross-enrolment-education-both-sexes'
handle = dl.grab(url)
mts = messytables.any.any_tableset(handle)
saves = 0
mt = mts.tables[0]
table = xypath.Table.from_messy(mt)
pivot, = table.filter(lambda c: 'Country' in c.value)
years = pivot.fill(xypath.RIGHT)
countries = pivot.fill(xypath.DOWN)
for year, country, value in years.junction(countries):
output = dict(output_template)
output['source'] = url
output['region'] = country.value.strip()
output['value'] = value.value.strip()
orm.Value(**output).save()
saves = saves + 1
assert saves
def getcountry(threeletter="PAK"):
print threeletter
baseurl = "http://api.worldbank.org/datafiles/%s_Country_MetaData_en_EXCEL.xls"
value = {'dsID': 'World Bank',
'region': threeletter,
'source': baseurl % threeletter,
'is_number': True}
fh = dl.grab(baseurl % threeletter, [404])
if not fh:
return
messy = messytables.excel.XLSTableSet(fh)
table = xypath.Table.from_messy(list(messy.tables)[0])
indicators = table.filter(is_in(indicator_list))
indname = indicators.shift(x=-1)
if not len(indname) == len(indicator_list):
print "missing indicators", [x.value for x in indname]
code = table.filter(equal_to('Indicator Code'))
years = code.fill(xypath.RIGHT)
junction = indname.junction(years)
for ind_cell, year_cell, value_cell in junction:
vdict = dict(value)
vdict['indID'] = ind_cell.value
vdict['period'] = year_cell.value
vdict['value'] = value_cell.value
indicator = {'indID': vdict['indID']}
nameunits = re.search('(.*)\((.*)\)', vdict['indID'])
if nameunits:
(indicator['name'], indicator['units']) = nameunits.groups()
else:
indicator['name'] = vdict['indID']
indicator['units'] = 'uno'
Indicator(**indicator).save()
v = Value(**vdict)
if not v.is_blank():
v.save()
print len(session.query(Value).filter(Value.dsID == 'World Bank').all())
session.commit()
def do_indicator(ind):
print "indicator:", ind
fh = dl.grab(baseurl % ind)
mt, = messytables.commas.CSVTableSet(fh).tables
mt_list = list(mt)
headers = mt_list[0]
if len(headers) == 0:
print ind
exit()
logging.warn("headers {!r}".format(headers))
rest = mt_list[1:]
for row in rest:
if len(row) == 0:
continue # skip empty row
rowdict = {x[0].value: x[1].value for x in zip(headers, row)}
name, unit = units(rowdict['GHO (DISPLAY)'])
indID = rowdict['GHO (CODE)']
for lookup in ["SEX", "RESIDENCEAREATYPE", "EDUCATIONLEVEL", "WEALTHQUINTILE"]:
lookup_code = lookup+" (CODE)"
lookup_name = lookup+" (DISPLAY)"
if lookup_code in rowdict: # header = "SEX (CODE)"
if rowdict[lookup_code]: # value != ""
indID = indID + "({}={})" .format(lookup, rowdict[lookup_code])
name = name + " - " + rowdict[lookup_name]
value_dict = {"value": rowdict['Display Value'],
"period": rowdict['YEAR (DISPLAY)'],
"indID": indID,
"region": rowdict["COUNTRY (CODE)"],
"dsID": "athena-api",
"source": baseurl % ind,
"is_number": True}
indicator_dict = {'indID': indID,
'name': name,
'units': unit}
orm.Indicator(**indicator_dict).save()
orm.Value(**value_dict).save()
def doit():
# country_cells: we used to assert_one(), but sometimes there's two!
dataset = {
'dsID': 'emdat',
'last_updated': None,
'last_scraped': orm.now(),
'name': 'EM-DAT'
}
orm.DataSet(**dataset).save()
for i, t in enumerate(targets):
indicator = {'indID': "emdat:%s" % t, 'name': names[i], 'units': 'uno'}
if t == 'total_damage':
indicator['units'] = ",000$ USD"
orm.Indicator(**indicator).save()
for country in country_list(): # TODO country_list
print country
raw = dl.grab(url.format(country))
m_tables = messytables.any.any_tableset(raw)
mt, = m_tables.tables
table = xypath.Table.from_messy(mt)
yr = table.filter('year').assert_one()
years = yr.fill(xypath.DOWN)
cats = yr.fill(xypath.RIGHT)
for year, cat, value in years.junction(cats):
value = {
'dsID': 'emdat',
'region': country,
'indID': 'emdat:{}'.format(cat.value),
'period': '{}/P1Y'.format(year.value),
'value': value.value,
'source': url,
'is_number': True
}
orm.Value(**value).save()
orm.session.commit()
def do_indicator(ind):
fh = dl.grab(baseurl % ind)
mt, = messytables.commas.CSVTableSet(fh).tables
mt_list = list(mt)
headers = mt_list[0]
if len(headers) == 0:
print ind
exit()
rest = mt_list[1:]
for row in rest:
if len(row) == 0:
continue # skip empty row
rowdict = {x[0].value: x[1].value for x in zip(headers, row)}
name, unit = units(rowdict['GHO (DISPLAY)'])
if 'SEX (CODE)' in rowdict:
indID = rowdict['GHO (CODE)'] + "-" + rowdict['SEX (CODE)']
name = name + " - " + rowdict['SEX (DISPLAY)']
else:
indID = rowdict['GHO (CODE)']
value_dict = {"value": rowdict['Display Value'],
"period": rowdict['YEAR (DISPLAY)'],
"indID": indID,
"region": rowdict["COUNTRY (CODE)"],
"dsID": "athena-api",
"source": baseurl % ind,
"is_number": True}
indicator_dict = {'indID': indID,
'name': name,
'units': unit}
orm.Indicator(**indicator_dict).save()
orm.Value(**value_dict).save()
print value_dict
def doit():
# country_cells: we used to assert_one(), but sometimes there's two!
dataset = {'dsID': 'emdat',
'last_updated': None,
'last_scraped': orm.now(),
'name': 'EM-DAT'}
orm.DataSet(**dataset).save()
for i, t in enumerate(targets):
indicator = {'indID': "emdat:%s" % t,
'name': names[i],
'units': 'uno'}
if t == 'total_damage':
indicator['units'] = ",000$ USD"
orm.Indicator(**indicator).save()
for country in country_list(): # TODO country_list
print country
raw = dl.grab(url.format(country))
m_tables = messytables.any.any_tableset(raw)
mt, = m_tables.tables
table = xypath.Table.from_messy(mt)
yr = table.filter('year').assert_one()
years = yr.fill(xypath.DOWN)
cats = yr.fill(xypath.RIGHT)
for year, cat, value in years.junction(cats):
value = {'dsID': 'emdat',
'region': country,
'indID': 'emdat:{}'.format(cat.value),
'period': '{}/P1Y'.format(year.value),
'value': value.value,
'source': url,
'is_number': True}
orm.Value(**value).save()
orm.session.commit()
def main():
save_dataset()
save_indicator()
raw = dl.grab(SHEET_URL)
mtables = messytables.any.any_tableset(raw)
table = xypath.Table.from_messy(mtables.tables[0])
table.filter(IND_NAME).assert_one() # we have the right table
region_header = table.filter(REGION_HEADER_VALUE).assert_one()
regions = region_header.fill(xypath.DOWN)
years = region_header.fill(xypath.RIGHT,
stop_before=lambda c: c.value == '')
assert len(years) < 15 # left side.
for region_cell, year_cell, value_cell in regions.junction(years):
value = {
"dsID": DSID,
'region': region_cell.value,
'indID': INDID,
'source': SHEET_URL,
'is_number': True,
'period': year_cell.value,
'value': value_cell.value
}
orm.Value(**value).save()
orm.session.commit()
def do_indicator(ind):
fh = dl.grab(baseurl % ind)
mt, = messytables.commas.CSVTableSet(fh).tables
mt_list = list(mt)
headers = mt_list[0]
if len(headers) == 0:
print ind
exit()
rest = mt_list[1:]
for row in rest:
if len(row) == 0:
continue # skip empty row
rowdict = {x[0].value: x[1].value for x in zip(headers, row)}
name, unit = units(rowdict['GHO (DISPLAY)'])
if 'SEX (CODE)' in rowdict:
indID = rowdict['GHO (CODE)'] + "-" + rowdict['SEX (CODE)']
name = name + " - " + rowdict['SEX (DISPLAY)']
else:
indID = rowdict['GHO (CODE)']
value_dict = {
"value": rowdict['Display Value'],
"period": rowdict['YEAR (DISPLAY)'],
"indID": indID,
"region": rowdict["COUNTRY (CODE)"],
"dsID": "athena-api",
"source": baseurl % ind,
"is_number": True
}
indicator_dict = {'indID': indID, 'name': name, 'units': unit}
orm.Indicator(**indicator_dict).save()
orm.Value(**value_dict).save()
print value_dict
indicators = [{"indID": "gna-vi",
"name": "GNA Vulnerability Index",
"units": "index"},
{"indID": "gna-ci",
"name": "GNA Crisis Index",
"units": "index"}]
for indicator in indicators:
orm.Indicator(**indicator).save()
value_template = {"dsID": "echo",
"period": 2012,
"source": baseurl,
"is_number": True}
xls_raw = dl.grab(baseurl)
mt = messytables.excel.XLSTableSet(xls_raw).tables[0]
assert mt.name == "GNA Final Index (rank)"
xy = xypath.Table.from_messy(mt)
countries = xy.filter("ISO3").assert_one().fill(xypath.DOWN)
vuln_h = xy.filter("GNA Vulnerability Index (VI)").assert_one()
crisis_h = xy.filter("GNA Crisis Index (CI)").assert_one()
headerheader(xy.filter("ISO3").assert_one(), xypath.DOWN, xypath.RIGHT)
big = {'region': headerheader(xy.filter("ISO3").assert_one(), xypath.DOWN, xypath.RIGHT),
'indID': {'gna-vi': vuln_h.fill(xypath.DOWN),
'gna-ci': crisis_h.fill(xypath.DOWN)}}
for olap_row in xypath.xyzzy.xyzzy(xy, big, valuename="value"):
print olap_row
import datetime
import itertools
import dl
import xypath
import messytables
import orm
"""Value: dsID, region, indID, period, value, source, is_number
DataSet: dsID, last_updated, last_scraped, name
Indicator: indID, name, units
"""
url = 'http://cred01.epid.ucl.ac.be:5317/?after=&before=&agg1=iso&agg2=year&dl=true'
raw = dl.grab(url)
m_tables = messytables.any.any_tableset(raw)
mt, = m_tables.tables
table = xypath.Table.from_messy(mt)
def doit(targets, names, year):
# country_cells: we used to assert_one(), but sometimes there's two!
country_cells = table.filter('iso').fill(xypath.DOWN)
country_cells = country_cells - country_cells.filter('iso') # remove other
if not country_cells: print "no countries"
country_year_filter = country_cells.filter(lambda b: b.shift(xypath.RIGHT).value == year)
if not country_year_filter: print "no countries for ", year
target_cells = table.filter(lambda b: b.value in targets)
if not target_cells: print "didn't find ", targets
value = {'dsID': 'emdat',
'period': "%s/P1Y" % (year),
'source': url,
import datetime
import itertools
import dl
import xypath
import messytables
import orm
"""Value: dsID, region, indID, period, value, source, is_number
DataSet: dsID, last_updated, last_scraped, name
Indicator: indID, name, units
"""
url = 'http://cred01.epid.ucl.ac.be:5317/?after=&before=&agg1=iso&agg2=year&dl=true'
raw = dl.grab(url)
m_tables = messytables.any.any_tableset(raw)
mt, = m_tables.tables
table = xypath.Table.from_messy(mt)
def doit(targets, names, year):
# country_cells: we used to assert_one(), but sometimes there's two!
country_cells = table.filter('iso').fill(xypath.DOWN)
country_cells = country_cells - country_cells.filter('iso') # remove other
if not country_cells: print "no countries"
country_year_filter = country_cells.filter(
lambda b: b.shift(xypath.RIGHT).value == year)
if not country_year_filter: print "no countries for ", year
target_cells = table.filter(lambda b: b.value in targets)
if not target_cells: print "didn't find ", targets
value = {
'dsID': 'emdat',
"indID": "gna-ci",
"name": "GNA Crisis Index",
"units": "index"
}]
for indicator in indicators:
orm.Indicator(**indicator).save()
value_template = {
"dsID": "echo",
"period": 2012,
"source": baseurl,
"is_number": True
}
xls_raw = dl.grab(baseurl)
mt = messytables.excel.XLSTableSet(xls_raw).tables[0]
assert mt.name == "GNA Final Index (rank)"
xy = xypath.Table.from_messy(mt)
countries = xy.filter("ISO3").assert_one().fill(xypath.DOWN)
vuln_h = xy.filter("GNA Vulnerability Index (VI)").assert_one()
crisis_h = xy.filter("GNA Crisis Index (CI)").assert_one()
headerheader(xy.filter("ISO3").assert_one(), xypath.DOWN, xypath.RIGHT)
big = {
'region':
headerheader(xy.filter("ISO3").assert_one(), xypath.DOWN, xypath.RIGHT),
'indID': {
'gna-vi': vuln_h.fill(xypath.DOWN),
'gna-ci': crisis_h.fill(xypath.DOWN)