Connect Masironi (1970), https://www.ncbi.nlm.nih.gov/pmc/articles/pmid/5309508/, with WHO mortality data (assume MySQL database set up according to my mortchartgen repo).

Masironi70.csv

Masironi70.jl

using MySQL, DataFrames

masiframe = readtable("Masironi70.csv")

function sqlq(qstr, partypes = [], pars = [])
	con = mysql_connect("localhost", "whomuser", "whomort", "Morticd";
		opts=Dict(MYSQL_SET_CHARSET_NAME => "utf8"))
	mysql_stmt_prepare(con, qstr)
	df = mysql_execute(con, partypes, pars)
	mysql_disconnect(con)
	return df
end

function popframe(sex, year = 1965)
	qstr = """SELECT * FROM Pop WHERE Sex = ?
		AND Year = ? AND (Admin1 = "" OR Country = 3150)"""
	partypes = [MYSQL_TYPE_SHORT, MYSQL_TYPE_SHORT]
	pars = [sex, year]
	return sqlq(qstr, partypes, pars)
end

function dthsframe(sex, caexpr, year = 1965, list = "07A")
	dcolnames = join(map((x)->"Deaths$x", collect(1:26)), ",")
	qstr = """SELECT Country, $dcolnames FROM Deaths WHERE Sex = ?
		AND Cause REGEXP ? AND Year = ? AND (Admin1 = "" OR Country = 3150)
		AND List = ?"""
	partypes = [MYSQL_TYPE_SHORT, MYSQL_TYPE_VARCHAR, MYSQL_TYPE_SHORT, MYSQL_TYPE_VARCHAR]
	pars = [sex, caexpr, year, list]
	return aggregate(sqlq(qstr, partypes, pars), :Country, sum)
end

function popdthsframe(popframe, dthsframe)
	return join(popframe, dthsframe, on = :Country)
end

function cumrate(popdthsframe, startcol, endcol)
	colist = collect(startcol:endcol)
	dcols = map((x)->popdthsframe[symbol("Deaths$(x)_sum")], colist)
	popcols = map((x)->popdthsframe[symbol("Pop$x")], colist)
	cumrate = foldr(.+, map(./, dcols, popcols))
	return DataFrame(country = popdthsframe[:Country], cumrate = cumrate)
end

function ratemasi(popframe, dthsframe, startcol, endcol)
	popdths = popdthsframe(popframe, dthsframe)
	rateall = join(cumrate(popdths, startcol, endcol), masiframe, on = :country)
	ratesuc = rateall[!isna(rateall[:suc]), :]
	return Dict(:all => rateall, :suc => ratesuc)
end