{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#szekelyland workbook - extrapolating from romanian values using mortality statistics from INSSE\n", "import pandas as pd, numpy as np" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": true }, "outputs": [], "source": [ "df=pd.read_csv('trexportPivot_POP206C.csv')\n", "de=pd.read_csv('trexportPivot_POP206E.csv')" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "Index([u'Sexe', u' Grupe de varsta ',\n", " u' Macroregiuni regiuni de dezvoltare si judete', u' Ani',\n", " u' UM: Numar persoane', u' Valoare'],\n", " dtype='object')" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "de.columns" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [], "source": [ "df=df.drop(u' UM: Numar persoane',axis=1).set_index([u' Ani',u'Clasificarea internationala a maladiilor - Revizia a X a 1994',u' Macroregiuni regiuni de dezvoltare si judete'])\n", "de=de.drop(u' UM: Numar persoane',axis=1).set_index([u' Ani',u'Sexe',u' Grupe de varsta ',u' Macroregiuni regiuni de dezvoltare si judete'])" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Valoare
AniSexeGrupe de varstaMacroregiuni regiuni de dezvoltare si judete
Anul 1990TotalTotalTOTAL247086
Anul 1991TotalTotalTOTAL251760
Anul 1992TotalTotalTOTAL263855
Anul 1993TotalTotalTOTAL263323
Anul 1994TotalTotalTOTAL266101
\n", "
" ], "text/plain": [ " Valoare\n", " Ani Sexe Grupe de varsta Macroregiuni regiuni de dezvoltare si judete \n", " Anul 1990 Total Total TOTAL 247086\n", " Anul 1991 Total Total TOTAL 251760\n", " Anul 1992 Total Total TOTAL 263855\n", " Anul 1993 Total Total TOTAL 263323\n", " Anul 1994 Total Total TOTAL 266101" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "de.head()" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Anul 1990\n", " Anul 1991\n", " Anul 1992\n", " Anul 1993\n", " Anul 1994\n", " Anul 1995\n", " Anul 1996\n", " Anul 1997\n", " Anul 1998\n", " Anul 1999\n", " Anul 2000\n", " Anul 2001\n", " Anul 2002\n", " Anul 2003\n", " Anul 2004\n", " Anul 2005\n", " Anul 2006\n", " Anul 2007\n", " Anul 2008\n", " Anul 2009\n", " Anul 2010\n", " Anul 2011\n", " Anul 2012\n", " Anul 2013\n" ] } ], "source": [ "for i in df.index.levels[0].unique():\n", " print i" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Alte cauze\n", "Boli ale aparatului circulator\n", "Boli ale aparatului digestiv\n", "Boli ale aparatului genito-urinar\n", "Boli ale aparatului respirator\n", "Boli ale sistemului nervos boli ale ochiului si anexele sale boli ale urechii si apofizei mastoide\n", "Boli endocrine de nutritie de metabolism ale sangelui si ale organelor hematopoetice\n", "Boli endocrine de nutritie si metabolism\n", "Boli infectioase si parazitare\n", "Leziuni traumatice otraviri si alte consecinte ale cauzelor externe\n", "Malformatii congenitale deformatii si anomalii cromozomiale\n", "Sarcina nastere si lauzie\n", "Total\n", "Tulburari mentale si de comportament\n", "Tulburari mintale boli ale sistemului nervos si ale organelor simturilor (fara bolile cerebrovasculare)\n", "Tumori\n", "Unele afectiuni a caror origine se situeaza in perioada perinatala\n", "din care: Boala ischemica a inimii\n", "din care: Boli cerebro-vasculare\n", "din care: Diabet zaharat\n", "din care: Tuberculoza\n" ] } ], "source": [ "for i in df.index.levels[1].unique():\n", " print i" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": true }, "outputs": [], "source": [ "d={\n", "\"Alte cauze\":1094,\n", "\"Boli ale aparatului circulator\":1064,\n", "\"Boli ale aparatului digestiv\":1078,\n", "\"Boli ale aparatului genito-urinar\":1084,\n", "\"Boli ale aparatului respirator\":1072,\n", "\"Boli ale sistemului nervos boli ale ochiului si anexele sale boli ale urechii si apofizei mastoide\":9,\n", "\"Boli endocrine de nutritie si metabolism\":1051,\n", "\"Boli infectioase si parazitare\":1001,\n", "\"Leziuni traumatice otraviri si alte consecinte ale cauzelor externe\":1095,\n", "\"Malformatii congenitale deformatii si anomalii cromozomiale\":1093,\n", "\"Sarcina nastere si lauzie\":1087,\n", "\"Total\":1000,\n", "\"Tulburari mentale si de comportament\":1055,\n", "\"Tumori\":1026,\n", "\"Unele afectiuni a caror origine se situeaza in perioada perinatala\":1092,\n", "\"din care: Boala ischemica a inimii\":0,\n", "\"din care: Boli cerebro-vasculare\":0,\n", "\"din care: Diabet zaharat\":0,\n", "\"din care: Tuberculoza\":0}\n", "n0=[1048,1082,1083]\n", "n9=[1058,1062,1063]" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#Transylvanian counties\n", "#['HARGHITA','MURES','COVASNA','CLUJ','BRASOV','ARAD','ALBA','SALAJ','TIMIS','BISTRITA-NASAUD','MARAMURES','BIHOR','HUNEDOARA','CARAS-SEVERIN','SATU MARE','SIBIU']" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "array(['Alte cauze', 'Boli ale aparatului circulator',\n", " 'Boli ale aparatului digestiv', 'Boli ale aparatului genito-urinar',\n", " 'Boli ale aparatului respirator',\n", " 'Boli ale sistemului nervos boli ale ochiului si anexele sale boli ale urechii si apofizei mastoide',\n", " 'Boli endocrine de nutritie de metabolism ale sangelui si ale organelor hematopoetice',\n", " 'Boli endocrine de nutritie si metabolism',\n", " 'Boli infectioase si parazitare',\n", " 'Leziuni traumatice otraviri si alte consecinte ale cauzelor externe',\n", " 'Malformatii congenitale deformatii si anomalii cromozomiale',\n", " 'Sarcina nastere si lauzie', 'Total',\n", " 'Tulburari mentale si de comportament',\n", " 'Tulburari mintale boli ale sistemului nervos si ale organelor simturilor (fara bolile cerebrovasculare)',\n", " 'Tumori',\n", " 'Unele afectiuni a caror origine se situeaza in perioada perinatala',\n", " 'din care: Boala ischemica a inimii',\n", " 'din care: Boli cerebro-vasculare', 'din care: Diabet zaharat',\n", " 'din care: Tuberculoza'], dtype=object)" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.index.levels[1].unique()" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false }, "outputs": [], "source": [ "#calculate county share in romania total\n", "pop={}\n", "for j in df.index.levels[0].unique():\n", " if j[6:] not in pop:pop[j[6:]]={}\n", " for k in df.index.levels[1].unique():\n", " try:\n", " if d[k]!=0: \n", " if d[k] not in pop[j[6:]]:pop[j[6:]][str(d[k])]={}\n", " pop[j[6:]][str(d[k])][\"ro\"]=df.loc[j].loc[k].loc[' TOTAL'][0]\n", " try: a=df.loc[j].loc[k].loc[' Regiunea CENTRU'][0]\n", " except: a=0\n", " try: b=df.loc[j].loc[k].loc[' Regiunea VEST'][0]\n", " except: b=0\n", " try: c=df.loc[j].loc[k].loc[' Regiunea NORD-VEST'][0]\n", " except: c=0\n", " pop[j[6:]][str(d[k])][\"szf\"]=(a+b+c)*1.0/df.loc[j].loc[k].loc[' TOTAL'][0]\n", " except: pass" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "collapsed": false }, "outputs": [], "source": [ "for y in pop:\n", " for c in n9:\n", " if str(c) not in pop[y]:pop[y][str(c)]={}\n", " for m in [\"szf\",\"ro\"]:\n", " try: pop[y][str(c)][m]=pop[y]['9'][m]/3.0\n", " except: pass\n", " pop[y].pop('9');\n", " for c in n0:\n", " if str(c) not in pop[y]:pop[y][str(c)]={}\n", " for m in [\"szf\",\"ro\"]:\n", " try: pop[y][str(c)][m]=pop[y]['1000'][m]\n", " except: pass" ] }, { "cell_type": "code", "execution_count": 134, "metadata": { "collapsed": false }, "outputs": [], "source": [ "#calculate age deviation from national average\n", "e={\"Feminin\":\"f\",\"Masculin\":\"m\",\"Total\":\"s\"}\n", "pop2={}\n", "pop3={}\n", "for j in de.index.levels[0].unique():\n", " if j[6:] not in pop2:pop2[j[6:]]={}\n", " if j[6:] not in pop3:pop3[j[6:]]={}\n", " for k in de.index.levels[1].unique():\n", " if e[k] not in pop2[j[6:]]:pop2[j[6:]][e[k]]={}\n", " if e[k] not in pop3[j[6:]]:pop3[j[6:]][e[k]]={}\n", " for l in de.index.levels[2].unique():\n", " age=l[:3].strip().strip('-')\n", " try: a=de.loc[j].loc[k].loc[l].loc[' Regiunea CENTRU'][0]\n", " except: a=0\n", " try: b=de.loc[j].loc[k].loc[l].loc[' Regiunea VEST'][0]\n", " except: b=0\n", " try: c=de.loc[j].loc[k].loc[l].loc[' Regiunea NORD-VEST'][0]\n", " except: c=0\n", " try: d=de.loc[j].loc[k].loc[l].loc[' TOTAL'][0]\n", " except: d=0\n", " if age!='To':\n", " if age!='0':\n", " if age!='85':\n", " pop2[j[6:]][e[k]][age]=(a+b+c)*1.0\n", " pop3[j[6:]][e[k]][age]=(d)*1.0\n", " else:\n", " pop2[j[6:]][e[k]]['90']=(a+b+c)*3.0/10\n", " pop2[j[6:]][e[k]]['95']=(a+b+c)*1.0/10\n", " pop2[j[6:]][e[k]]['85']=(a+b+c)*6.0/10\n", " pop3[j[6:]][e[k]]['90']=(d)*3.0/10\n", " pop3[j[6:]][e[k]]['95']=(d)*1.0/10\n", " pop3[j[6:]][e[k]]['85']=(d)*6.0/10\n", " \n", " else:\n", " pop2[j[6:]][e[k]]['0']=(a+b+c)*1.0/5\n", " pop2[j[6:]][e[k]]['1']=(a+b+c)*1.0/5\n", " pop2[j[6:]][e[k]]['2']=(a+b+c)*1.0/5\n", " pop2[j[6:]][e[k]]['3']=(a+b+c)*1.0/5\n", " pop2[j[6:]][e[k]]['4']=(a+b+c)*1.0/5\n", " pop3[j[6:]][e[k]]['0']=(d)*1.0/5\n", " pop3[j[6:]][e[k]]['1']=(d)*1.0/5\n", " pop3[j[6:]][e[k]]['2']=(d)*1.0/5\n", " pop3[j[6:]][e[k]]['3']=(d)*1.0/5\n", " pop3[j[6:]][e[k]]['4']=(d)*1.0/5" ] }, { "cell_type": "code", "execution_count": 135, "metadata": { "collapsed": false }, "outputs": [], "source": [ "for i in pop2:\n", " for j in [\"f\",\"m\"]:\n", " for a in pop2[i][j]:\n", " try:\n", " pop2[i][j][a]=pop2[i][j][a]*1.0/sum(pop2[i][\"s\"].values())\n", " pop3[i][j][a]=pop3[i][j][a]*1.0/sum(pop3[i][\"s\"].values())\n", " except:pass" ] }, { "cell_type": "code", "execution_count": 136, "metadata": { "collapsed": false }, "outputs": [], "source": [ "import zipfile,json\n", "#read RO data\n", "z = zipfile.ZipFile('db2/642.zip') \n", "ro = json.loads(z.open('data.json').read())" ] }, { "cell_type": "code", "execution_count": 137, "metadata": { "collapsed": false }, "outputs": [], "source": [ "h=json.loads(file('hierarchy2.json').read())" ] }, { "cell_type": "code", "execution_count": 138, "metadata": { "collapsed": false }, "outputs": [], "source": [ "szf=[]\n", "for i in ro:\n", " szf.append({\"a\":i[\"a\"],\n", " \"c\":i[\"c\"],\n", " \"g\":i[\"g\"],\n", " \"t\":i[\"t\"],\n", " \"s\":i[\"s\"]*1.0*float(pop[str(i[\"t\"])][h[i['c']][\"group\"]][\"szf\"])}) " ] }, { "cell_type": "code", "execution_count": 139, "metadata": { "collapsed": false }, "outputs": [], "source": [ "szf2=[]\n", "for i in ro:\n", " szf2.append({\"a\":i[\"a\"],\n", " \"c\":i[\"c\"],\n", " \"g\":i[\"g\"],\n", " \"t\":i[\"t\"],\n", " \"s\":i[\"s\"]*1.0*float(pop[str(i[\"t\"])][h[i['c']][\"group\"]][\"szf\"])\n", " *float(pop2[str(i[\"t\"])][i[\"g\"]][str(i[\"a\"])])\n", " /float(pop3[str(i[\"t\"])][i[\"g\"]][str(i[\"a\"])])\n", " })" ] }, { "cell_type": "code", "execution_count": 140, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#save files\n", "try:\n", " import zlib\n", " compression = zipfile.ZIP_DEFLATED\n", "except:\n", " compression = zipfile.ZIP_STORED\n", "\n", "file('db2/data.json','w').write(json.dumps(szf)) \n", "zf = zipfile.ZipFile('db2/9997.zip', mode='w')\n", "zf.write('db2/data.json','data.json',compress_type=compression)\n", "zf.close()\n", "file('db2/data.json','w').write(json.dumps(szf2)) \n", "zf = zipfile.ZipFile('db2/9996.zip', mode='w')\n", "zf.write('db2/data.json','data.json',compress_type=compression)\n", "zf.close()" ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#update dictionaries" ] }, { "cell_type": "code", "execution_count": 111, "metadata": { "collapsed": false }, "outputs": [], "source": [ "#run only once\n", "c=json.loads(file('countries.json').read())\n", "c=[u'9996']+c\n", "file('countries.json','w').write(json.dumps(c)) " ] }, { "cell_type": "code", "execution_count": 63, "metadata": { "collapsed": false }, "outputs": [], "source": [ "c=json.loads(file('cnames.json').read())\n", "c[u'9996']=u'Transylvania'\n", "file('cnames.json','w').write(json.dumps(c)) " ] }, { "cell_type": "code", "execution_count": 64, "metadata": { "collapsed": false }, "outputs": [], "source": [ "c=json.loads(file('hnames.json').read())\n", "c[u'Transylvania']=u'Erdély'\n", "file('hnames.json','w').write(json.dumps(c)) " ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#update population" ] }, { "cell_type": "code", "execution_count": 82, "metadata": { "collapsed": true }, "outputs": [], "source": [ "p=json.loads(file('pop.json').read())" ] }, { "cell_type": "code", "execution_count": 83, "metadata": { "collapsed": true }, "outputs": [], "source": [ "x=pd.read_csv('trexportPivot_POP107A.csv')" ] }, { "cell_type": "code", "execution_count": 84, "metadata": { "collapsed": false }, "outputs": [], "source": [ "x=x.drop([u' Medii de rezidenta',u' UM: Numar persoane'],axis=1).set_index([u'Varste si grupe de varsta',u' Sexe',u' Ani',u' Macroregiuni regiuni de dezvoltare si judete'])" ] }, { "cell_type": "code", "execution_count": 85, "metadata": { "collapsed": false }, "outputs": [], "source": [ "x=x.unstack(u' Macroregiuni regiuni de dezvoltare si judete')" ] }, { "cell_type": "code", "execution_count": 86, "metadata": { "collapsed": false }, "outputs": [], "source": [ "x=pd.DataFrame(x.T.sum())" ] }, { "cell_type": "code", "execution_count": 87, "metadata": { "collapsed": false }, "outputs": [], "source": [ "indice=[str(i)+' ani' for i in range(5)]+[str(i*5)+'-'+str((i+1)*5-1)+' ani' for i in range(1,17)]\n", "indice[5]='5- 9 ani'" ] }, { "cell_type": "code", "execution_count": 101, "metadata": { "collapsed": false }, "outputs": [], "source": [ "p['9997']={}\n", "p['9996']={}\n", "gg={\"f\":u\" Feminin\",\"m\":u\" Masculin\"}\n", "for y in range(1999,2013):\n", " if str(y) not in p['9997']:p['9997'][str(y)]={}\n", " for g in [\"f\",\"m\"]:\n", " if g not in p['9997'][str(y)]:p['9997'][str(y)][g]={}\n", " for i in indice:\n", " p['9997'][str(y)][g][str(i[:2].strip().strip('-'))]=str(float(x.loc[i].loc[gg[g]].loc[' Anul '+str(y)][0]))\n", " p['9997'][str(y)][g][str(85)]=str(float(x.loc['85 ani si peste'].loc[gg[g]].loc[' Anul '+str(y)][0])*6.0/10)\n", " p['9997'][str(y)][g][str(90)]=str(float(x.loc['85 ani si peste'].loc[gg[g]].loc[' Anul '+str(y)][0])*3.0/10)\n", " p['9997'][str(y)][g][str(95)]=str(float(x.loc['85 ani si peste'].loc[gg[g]].loc[' Anul '+str(y)][0])*1.0/10)" ] }, { "cell_type": "code", "execution_count": 102, "metadata": { "collapsed": false }, "outputs": [], "source": [ "p['9996']=p['9997']" ] }, { "cell_type": "code", "execution_count": 103, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#save updated population file\n", "file('pop.json','w').write(json.dumps(p)) " ] }, { "cell_type": "code", "execution_count": 107, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "{'0': '36715.0',\n", " '1': '36805.0',\n", " '10': '216536.0',\n", " '15': '290419.0',\n", " '2': '35922.0',\n", " '20': '283071.0',\n", " '25': '315829.0',\n", " '3': '36074.0',\n", " '30': '303458.0',\n", " '35': '295930.0',\n", " '4': '38130.0',\n", " '40': '230951.0',\n", " '45': '285143.0',\n", " '5': '186511.0',\n", " '50': '268423.0',\n", " '55': '216661.0',\n", " '60': '188840.0',\n", " '65': '196362.0',\n", " '70': '168310.0',\n", " '75': '127405.0',\n", " '80': '78904.0',\n", " '85': '18638.4',\n", " '90': '9319.2',\n", " '95': '3106.4'}" ] }, "execution_count": 107, "metadata": {}, "output_type": "execute_result" } ], "source": [ "p['9996']['2005']['f']" ] } ], "metadata": { "kernelspec": { "display_name": "Python 2", "language": "python", "name": "python2" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.11" } }, "nbformat": 4, "nbformat_minor": 0 }