TABLE_NAME = "Commodity_Raw_Data"
#Countries to merge with country one being the country to stay in the database
country_one = 185
country_two = 228
country_name = "Russian_Federation" #table_name
#Query to merge the rows of the two countries
query = """
SELECT country_id, item_id, element_id, unit_id, source_id, %s
FROM %%s
WHERE country_id=%s OR country_id=%s
GROUP BY item_id||element_id||source_id
"""%(",".join("SUM(yr%s) AS yr%s"%(x, x) for x in xrange(1961, 2011)), country_one, country_two)
#Run query through sqlite_io file, creating a temporary table and then dropping when complete
xs = sqlite_io.fromsqlite(DB, query%TABLE_NAME, "tmp")
print xs[xs['item_id']==1012]
exit()
#Extract out merged data for country remaining in the database
xs_merged = xs[xs['country_id']==country_one]
#Create a new table in the database for this new merged country
count = 0
foreign_keys = {'country_id':'Country', 'element_id':'Element',
'unit_id':'Unit', 'source_id':'Source'}
index = ['source_id', 'element_id', 'item_id', 'country_id'] #index in order
sqlite_io.tosqlite(xs_merged, count, DB, country_name, autoid=True,
foreign_keys=foreign_keys, index=index)
# Get data from master database for copying
xs = np.ma.array(cursor.execute("SELECT %s FROM %s"%(",".join(names), table)).fetchall(), ndtype)
# Mask all None values and create primary keys
autoid = [False, True][is_autoid] # assign primary keys
primary_key = ["%s_id"%table.lower(), False][is_autoid] # primary key
xs = mask_none_values(xs) # mask none values
sqlite_io.tosqlite(xs, 0, NEW_DB, table, autoid=autoid,
create=False, primary_key=primary_key)
# Format value tables with -1 values for missing values
for table in TABLES:
(names, typestr) = zip(*(_[1:3] for _ in connection.execute("PRAGMA TABLE_INFO(%s)"%table).fetchall()))
names = ",".join([name.strip() for name in names if name.strip()!='id'])
xs = sqlite_io.fromsqlite(DB, "SELECT %s FROM %s"%(names, table), "tmp_table")
ndtype = xs.dtype
xs = xs.view(float).reshape((-1, len(names.split(","))))
xs = np.ma.masked_less_equal(xs, 0) # mask any value less than or equal to 0
# Remove Commodity rows that have less than 5 values
if table == 'Commodity':
id_field_idx = 5 # number of columns that split the data b/w foreign keys and values
id_fields = xs[:,:id_field_idx] # foreign key fields
value_fields = xs[:,id_field_idx:] # data value fields
keep_rows, = np.where((np.size(value_fields, 1) - np.ma.count_masked(value_fields, axis=1)) > REMOVE_IF_LESS_THAN)
xs = np.ma.hstack((id_fields[keep_rows,:], value_fields[keep_rows,:]))
# Fill masked values with a -1 value
xs = np.ma.filled(xs, -1)