<commit_before># python script to build sqlite for visualization
# coding=utf8
import subprocess
import sqlite3
import os
import json
import itertools
from csv_unicode import UnicodeReader
from csv_unicode import UnicodeWriter

try :
	conf=json.load(open("config.json","r"))
except :
	print "couldn't load config.json database"
	exit(1)

# sqlite_viz=os.path.join("out_data",conf["sqlite_viz"])
conn=sqlite3.connect("out_data/RICardo_visualisation.sqlite")
c=conn.cursor()

################################################################################
##			Create table flow_joined
################################################################################

print "Create table flow_joined"
print "-------------------------------------------------------------------------"

c.execute("""DROP TABLE IF EXISTS flow_joined;""")
c.execute("""CREATE TABLE IF NOT EXISTS flow_joined AS
	 SELECT f.id, f.source, f.flow, f.year,
	 	f.unit as unit,
		eisg.modified_export_import as expimp,
		eisg.modified_special_general as spegen,
		rate.rate_to_pounds as rate,
		c.modified_currency as currency,
		r1.RICname as reporting,
		r2.slug as reporting_slug,
		CASE
			WHEN p2.RICname="World" and world_trade_type="total_estimated" THEN "Worldestimated"
			WHEN p2.RICname="World" and world_trade_type="total_reporting1" THEN "Worldasreported"
			WHEN p2.RICname="World" and world_trade_type="total_reporting2" THEN "Worldasreported2"
			WHEN p2.RICname="World" and world_trade_type is null THEN "Worldundefined"
			ELSE p2.slug
		END as partner_slug,
		CASE
			WHEN p2.RICname="World" and world_trade_type="total_estimated" THEN "World estimated"
			WHEN p2.RICname="World" and world_trade_type="total_reporting1" THEN "World as reported"
			WHEN p2.RICname="World" and world_trade_type="total_reporting2" THEN "World as reported2"
			WHEN p2.RICname="World" and world_trade_type is null THEN "World undefined"
			ELSE p2.RICname
		END as partner,
		r2.type as reporting_type,
		r2.continent as reporting_continent,
		p2.type as partner_type,
		p2.continent as partner_continent,
		transport_type,
		notes,
		species_bullions
		from flows as f
		LEFT OUTER JOIN currencies as c
			ON f.currency=c.currency
			    AND f.year=c.year
			    AND f.reporting = c.reporting
		LEFT OUTER JOIN exchange_rates as rate
			ON c.modified_currency=rate.modified_currency
			    AND c.year=rate.year
		LEFT OUTER JOIN entity_names as r1
			 	ON r1.original_name=f.reporting COLLATE NOCASE
		LEFT OUTER JOIN entity_names as p1
			 	ON p1.original_name=f.partner COLLATE NOCASE
		LEFT OUTER JOIN RICentities as p2
			 	ON p2.RICname=p1.RICname COLLATE NOCASE
		LEFT OUTER JOIN RICentities as r2
			 	ON r2.RICname=r1.RICname COLLATE NOCASE
		LEFT OUTER JOIN expimp_spegen as eisg
			 	USING (export_import, special_general)
		WHERE expimp != "Re-exp"
			and partner is not null
			and partner_sum is null
	""")

print "flow_joined created"
print "-------------------------------------------------------------------------"

# taking care of Total_type flag to define the world partner
# and ((`Total Trade Estimation` is null and partner != "World" )or(`Total Trade Estimation`=1 and partner = "World"))

# c.execute("""Select count(*) from flow_joined """)
# print list(c), "lines in flow_joined"

c.execute("""INSERT INTO RICentities (`RICname`,`type`,`continent`)
	VALUES ("World estimated","geographical_area","World")""")

print "World estimated added to RICentities"

c.execute("""INSERT INTO RICentities (`RICname`,`type`,`continent`)
	VALUES ("World as reported","geographical_area","World")""")

print "World as reported added to RICentities"

c.execute("""INSERT INTO RICentities (`RICname`,`type`,`continent`)
	VALUES ("World as reported2","geographical_area","World")""")

print "World as reported2 added to RICentities"

c.execute("""INSERT INTO RICentities (`RICname`,`type`,`continent`)
	VALUES ("World undefined","geographical_area","World")""")
<commit_msg>update script to create flow joined table for the website<commit_after># python script to build sqlite for visualization
# coding=utf8
import subprocess
import sqlite3
import os
import json
import itertools
from csv_unicode import UnicodeReader
from csv_unicode import UnicodeWriter

try :
	conf=json.load(open("config.json","r"))
except :
	print "couldn't load config.json database"
	exit(1)

# sqlite_viz=os.path.join("out_data",conf["sqlite_viz"])
conn=sqlite3.connect("out_data/RICardo_visualisation.sqlite")
c=conn.cursor()

################################################################################
##			Create table flow_joined
################################################################################

print "Create table flow_joined"
print "-------------------------------------------------------------------------"

c.execute("""DROP TABLE IF EXISTS flow_joined;""")
c.execute("""CREATE TABLE IF NOT EXISTS flow_joined AS
	 SELECT f.id, f.source, s.type, f.flow, f.year,
	 	f.unit as unit,
		eisg.modified_export_import as expimp,
		eisg.modified_special_general as spegen,
		rate.rate_to_pounds as rate,
		c.modified_currency as currency,
		r1.RICname as reporting,
		r2.slug as reporting_slug,
		CASE
			WHEN p2.RICname="World" and world_trade_type="total_estimated" THEN "Worldestimated"
			WHEN p2.RICname="World" and world_trade_type="total_reporting1" THEN "Worldasreported"
			WHEN p2.RICname="World" and world_trade_type="total_reporting2" THEN "Worldasreported2"
			WHEN p2.RICname="World" and world_trade_type is null THEN "Worldundefined"
			ELSE p2.slug
		END as partner_slug,
		CASE
			WHEN p2.RICname="World" and world_trade_type="total_estimated" THEN "World estimated"
			WHEN p2.RICname="World" and world_trade_type="total_reporting1" THEN "World as reported"
			WHEN p2.RICname="World" and world_trade_type="total_reporting2" THEN "World as reported2"
			WHEN p2.RICname="World" and world_trade_type is null THEN "World undefined"
			ELSE p2.RICname
		END as partner,
		r2.type as reporting_type,
		r2.continent as reporting_continent,
		p2.type as partner_type,
		p2.continent as partner_continent,
		transport_type,
		f.notes,
		species_bullions
		from flows as f
		LEFT OUTER JOIN currencies as c
			ON f.currency=c.currency
			    AND f.year=c.year
			    AND f.reporting = c.reporting
		LEFT OUTER JOIN exchange_rates as rate
			ON c.modified_currency=rate.modified_currency
			    AND c.year=rate.year
		LEFT OUTER JOIN entity_names as r1
			 	ON r1.original_name=f.reporting COLLATE NOCASE
		LEFT OUTER JOIN entity_names as p1
			 	ON p1.original_name=f.partner COLLATE NOCASE
		LEFT OUTER JOIN RICentities as p2
			 	ON p2.RICname=p1.RICname COLLATE NOCASE
		LEFT OUTER JOIN RICentities as r2
			 	ON r2.RICname=r1.RICname COLLATE NOCASE
		LEFT OUTER JOIN expimp_spegen as eisg
			 	USING (export_import, special_general)
		LEFT OUTER JOIN sources as s
				ON s.slug=f.source
		WHERE expimp != "Re-exp"
			and partner is not null
			and partner_sum is null
			and s.type != "OUPS"
	""")

print "flow_joined created"
print "-------------------------------------------------------------------------"

# taking care of Total_type flag to define the world partner
# and ((`Total Trade Estimation` is null and partner != "World" )or(`Total Trade Estimation`=1 and partner = "World"))

# c.execute("""Select count(*) from flow_joined """)
# print list(c), "lines in flow_joined"

c.execute("""INSERT INTO RICentities (`RICname`,`type`,`continent`)
	VALUES ("World estimated","geographical_area","World")""")

print "World estimated added to RICentities"

c.execute("""INSERT INTO RICentities (`RICname`,`type`,`continent`)
	VALUES ("World as reported","geographical_area","World")""")

print "World as reported added to RICentities"

c.execute("""INSERT INTO RICentities (`RICname`,`type`,`continent`)
	VALUES ("World as reported2","geographical_area","World")""")

print "World as reported2 added to RICentities"

c.execute("""INSERT INTO RICentities (`RICname`,`type`,`continent`)
	VALUES ("World undefined","geographical_area","World")""")

{'fraction_non_alphanumeric': 0.17151741293532338, 'fraction_numerical': 0.009701492537313432, 'mean_word_length': 4.293614219881501, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3003354', 'n_tokens_mistral': 2830, 'n_tokens_neox': 2548, 'n_words': 713}