Create a gist now

Instantly share code, notes, and snippets.

@klpn /Masironi70.csv
Last active Sep 24, 2016

What would you like to do?
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).
ctryname country totfat satfat suc m55mort f55mort
Jordan 3170 18.5 3.7 11 49 19
El Salvador 2190 19.5 5.5 51 28
Taiwan 3070 15.3 4.8 4.2 58 53
Philippines 3300 9.6 3.7 5.5 89 40
Guatemala 2250 15.1 4.1 115 84
Mexico 2310 24.8 7.9 122 65
Greece 4140 26.5 5.4 4.9 162 61
Japan 3160 14.5 3.4 7.3 165 92
Panama 2350 22.6 6.8 8.7 170 76
Spain 4280 27.9 7.5 7 171 71
France 4080 37 14.5 10.4 206 59
Colombia 2130 21.6 8.1 8 218 130
Costa Rica 2140 18.6 6.4 13.1 224 150
Romania 4270 21.3 4.4 4.3 255 152
Portugal 4240 23.7 6.2 8 259 114
Yugoslavia 4340 22.3 3.2 5.2 267 184
Poland 4230 27.2 4.6 281 114
Chile 2120 19.4 7.5 321 184
Mauritius 1300 18.9 3.6 17.6 322 98
Venezuela 2470 22.6 6.1 15.5 342 230
Italy 4180 26.4 7.7 8.9 387 152
Switzerland 4300 36.4 13.4 14 395 68
Belgium 4020 41.1 12.8 11 419 124
Hungary 4150 29.4 13.7 453 234
Austria 4010 33.7 9.7 13 478 150
Uruguay 2460 36.6 16.4 485 173
Sweden 4290 40.6 14.5 14.6 490 152
Netherlands 4210 39.5 11.5 15.9 503 136
Federal Republic of Germany 4090 38.8 14.6 10.8 549 175
Norway 4220 39.2 12.4 16.7 583 138
Denmark 4050 41.5 16.1 15.5 586 172
Israel 3150 28.9 6.9 12 626 306
United Kingdom 4308 39.2 15.1 15.9 743 208
Canada 2090 40.8 16.3 15.5 832 253
New Zealand 5150 40.4 19.2 12.6 889 309
USA 2450 41.5 14.3 14.5 933 301
Australia 5020 38 16.3 14 942 303
Finland 4070 33 16.2 13.6 1037 252
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment