{
"cells": [
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"import numpy as np, requests, pandas as pd, zipfile, StringIO"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Anaconda\\lib\\site-packages\\pandas\\io\\html.py:841: FutureWarning: infer_types will have no effect in 0.14\n",
" warnings.warn(\"infer_types will have no effect in 0.14\", FutureWarning)\n"
]
},
{
"data": {
"text/html": [
"
\n",
"
\n",
" \n",
" \n",
" | \n",
" ISO2 | \n",
" ISO3 | \n",
" ISONUM | \n",
" FIPS | \n",
" Capital | \n",
" Area | \n",
" Population | \n",
" Continent | \n",
"
\n",
" \n",
" Country | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" Andorra | \n",
" AD | \n",
" AND | \n",
" 20 | \n",
" AN | \n",
" Andorra la Vella | \n",
" 468.0 | \n",
" 84000 | \n",
" EU | \n",
"
\n",
" \n",
" United Arab Emirates | \n",
" AE | \n",
" ARE | \n",
" 784 | \n",
" AE | \n",
" Abu Dhabi | \n",
" 82880.0 | \n",
" 4975593 | \n",
" AS | \n",
"
\n",
" \n",
" Afghanistan | \n",
" AF | \n",
" AFG | \n",
" 4 | \n",
" AF | \n",
" Kabul | \n",
" 647500.0 | \n",
" 29121286 | \n",
" AS | \n",
"
\n",
" \n",
" Antigua and Barbuda | \n",
" AG | \n",
" ATG | \n",
" 28 | \n",
" AC | \n",
" St. John's | \n",
" 443.0 | \n",
" 86754 | \n",
" nan | \n",
"
\n",
" \n",
" Anguilla | \n",
" AI | \n",
" AIA | \n",
" 660 | \n",
" AV | \n",
" The Valley | \n",
" 102.0 | \n",
" 13254 | \n",
" nan | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" ISO2 ISO3 ISONUM FIPS Capital Area \\\n",
"Country \n",
"Andorra AD AND 20 AN Andorra la Vella 468.0 \n",
"United Arab Emirates AE ARE 784 AE Abu Dhabi 82880.0 \n",
"Afghanistan AF AFG 4 AF Kabul 647500.0 \n",
"Antigua and Barbuda AG ATG 28 AC St. John's 443.0 \n",
"Anguilla AI AIA 660 AV The Valley 102.0 \n",
"\n",
" Population Continent \n",
"Country \n",
"Andorra 84000 EU \n",
"United Arab Emirates 4975593 AS \n",
"Afghanistan 29121286 AS \n",
"Antigua and Barbuda 86754 nan \n",
"Anguilla 13254 nan "
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"countries=pd.read_html('http://www.geonames.org/countries/',header=0,infer_types=False)[1]\n",
"countries.columns=['ISO2','ISO3','ISONUM','FIPS','Country','Capital','Area','Population','Continent']\n",
"countries.set_index('Country',drop=True,inplace=True)\n",
"countries.head(5)"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso3 | \n",
" lex2010 | \n",
" lex2011 | \n",
" lex2012 | \n",
" lex2013 | \n",
" edux2010 | \n",
" edux2011 | \n",
" edux2012 | \n",
" edux2013 | \n",
" gnix2010 | \n",
" gnix2011 | \n",
" gnix2012 | \n",
" gnix2013 | \n",
"
\n",
" \n",
" country | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" Afghanistan | \n",
" AFG | \n",
" 0.609 | \n",
" 0.617 | \n",
" 0.623 | \n",
" 0.630 | \n",
" 0.357000 | \n",
" 0.365333 | \n",
" 0.365333 | \n",
" 0.365333 | \n",
" 0.425934 | \n",
" 0.427753 | \n",
" 0.443368 | \n",
" 0.445065 | \n",
"
\n",
" \n",
" Albania | \n",
" ALB | \n",
" 0.874 | \n",
" 0.877 | \n",
" 0.880 | \n",
" 0.883 | \n",
" 0.601675 | \n",
" 0.608519 | \n",
" 0.608519 | \n",
" 0.608519 | \n",
" 0.674701 | \n",
" 0.680974 | \n",
" 0.680882 | \n",
" 0.683453 | \n",
"
\n",
" \n",
" Algeria | \n",
" DZA | \n",
" 0.779 | \n",
" 0.781 | \n",
" 0.783 | \n",
" 0.785 | \n",
" 0.631478 | \n",
" 0.642589 | \n",
" 0.642589 | \n",
" 0.642589 | \n",
" 0.725495 | \n",
" 0.729289 | \n",
" 0.727595 | \n",
" 0.730002 | \n",
"
\n",
" \n",
" Andorra | \n",
" AND | \n",
" 0.936 | \n",
" 0.937 | \n",
" 0.939 | \n",
" 0.941 | \n",
" 0.670287 | \n",
" 0.670287 | \n",
" 0.670287 | \n",
" 0.670287 | \n",
" 0.918319 | \n",
" 0.912079 | \n",
" 0.908631 | \n",
" 0.907283 | \n",
"
\n",
" \n",
" Angola | \n",
" AGO | \n",
" 0.472 | \n",
" 0.478 | \n",
" 0.485 | \n",
" 0.491 | \n",
" 0.440879 | \n",
" 0.474212 | \n",
" 0.474212 | \n",
" 0.474212 | \n",
" 0.614749 | \n",
" 0.625145 | \n",
" 0.624848 | \n",
" 0.626393 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" iso3 lex2010 lex2011 lex2012 lex2013 edux2010 edux2011 \\\n",
"country \n",
"Afghanistan AFG 0.609 0.617 0.623 0.630 0.357000 0.365333 \n",
"Albania ALB 0.874 0.877 0.880 0.883 0.601675 0.608519 \n",
"Algeria DZA 0.779 0.781 0.783 0.785 0.631478 0.642589 \n",
"Andorra AND 0.936 0.937 0.939 0.941 0.670287 0.670287 \n",
"Angola AGO 0.472 0.478 0.485 0.491 0.440879 0.474212 \n",
"\n",
" edux2012 edux2013 gnix2010 gnix2011 gnix2012 gnix2013 \n",
"country \n",
"Afghanistan 0.365333 0.365333 0.425934 0.427753 0.443368 0.445065 \n",
"Albania 0.608519 0.608519 0.674701 0.680974 0.680882 0.683453 \n",
"Algeria 0.642589 0.642589 0.725495 0.729289 0.727595 0.730002 \n",
"Andorra 0.670287 0.670287 0.918319 0.912079 0.908631 0.907283 \n",
"Angola 0.474212 0.474212 0.614749 0.625145 0.624848 0.626393 "
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"hdi=pd.read_excel('ihdi_series_cartagena.xlsx',sheetname=u'LEx, EDUx, INCx',header=1).dropna(axis=1, how='all').set_index('country')\n",
"hdi.head()"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso3 | \n",
" ilex2010 | \n",
" ilex2011 | \n",
" ilex2012 | \n",
" ilex2013 | \n",
" iedux2010 | \n",
" iedux2011 | \n",
" iedux2012 | \n",
" iedux2013 | \n",
" ignix2010 | \n",
" ignix2011 | \n",
" ignix2012 | \n",
" ignix2013 | \n",
"
\n",
" \n",
" country | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" Afghanistan | \n",
" AFG | \n",
" 0.250908 | \n",
" 0.302947 | \n",
" 0.305893 | \n",
" 0.413910 | \n",
" 0.216699 | \n",
" 0.221757 | \n",
" 0.221757 | \n",
" 0.200933 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" 0.396998 | \n",
"
\n",
" \n",
" Albania | \n",
" ALB | \n",
" 0.778734 | \n",
" 0.778776 | \n",
" 0.781440 | \n",
" 0.795583 | \n",
" 0.525262 | \n",
" 0.536106 | \n",
" 0.536106 | \n",
" 0.536106 | \n",
" 0.577544 | \n",
" 0.556356 | \n",
" 0.556281 | \n",
" 0.558381 | \n",
"
\n",
" \n",
" Algeria | \n",
" DZA | \n",
" 0.639559 | \n",
" 0.667755 | \n",
" 0.669465 | \n",
" 0.653905 | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" Andorra | \n",
" AND | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" Angola | \n",
" AGO | \n",
" 0.218536 | \n",
" 0.257642 | \n",
" 0.261415 | \n",
" 0.264158 | \n",
" 0.325369 | \n",
" NaN | \n",
" 0.310135 | \n",
" 0.310135 | \n",
" 0.390980 | \n",
" 0.312572 | \n",
" 0.312424 | \n",
" 0.313197 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" iso3 ilex2010 ilex2011 ilex2012 ilex2013 iedux2010 \\\n",
"country \n",
"Afghanistan AFG 0.250908 0.302947 0.305893 0.413910 0.216699 \n",
"Albania ALB 0.778734 0.778776 0.781440 0.795583 0.525262 \n",
"Algeria DZA 0.639559 0.667755 0.669465 0.653905 NaN \n",
"Andorra AND NaN NaN NaN NaN NaN \n",
"Angola AGO 0.218536 0.257642 0.261415 0.264158 0.325369 \n",
"\n",
" iedux2011 iedux2012 iedux2013 ignix2010 ignix2011 ignix2012 \\\n",
"country \n",
"Afghanistan 0.221757 0.221757 0.200933 NaN NaN NaN \n",
"Albania 0.536106 0.536106 0.536106 0.577544 0.556356 0.556281 \n",
"Algeria NaN NaN NaN NaN NaN NaN \n",
"Andorra NaN NaN NaN NaN NaN NaN \n",
"Angola NaN 0.310135 0.310135 0.390980 0.312572 0.312424 \n",
"\n",
" ignix2013 \n",
"country \n",
"Afghanistan 0.396998 \n",
"Albania 0.558381 \n",
"Algeria NaN \n",
"Andorra NaN \n",
"Angola 0.313197 "
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"ihdi=pd.read_excel('ihdi_series_cartagena.xlsx',sheetname=u'iLEx, iEDUx,iINCx',header=1).dropna(axis=1, how='all').set_index('country')\n",
"ihdi.head()"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso3 | \n",
" GII1995 | \n",
" GII2000 | \n",
" GII2005 | \n",
" GII2010 | \n",
" GII2011 | \n",
" GII2012 | \n",
" GII2013 | \n",
"
\n",
" \n",
" Country | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" Afghanistan | \n",
" AFG | \n",
" NaN | \n",
" NaN | \n",
" 0.743191 | \n",
" 0.720173 | \n",
" 0.706641 | \n",
" 0.712844 | \n",
" 0.705302 | \n",
"
\n",
" \n",
" Albania | \n",
" ALB | \n",
" 0.661140 | \n",
" NaN | \n",
" 0.366596 | \n",
" 0.268824 | \n",
" 0.271414 | \n",
" 0.251003 | \n",
" 0.245376 | \n",
"
\n",
" \n",
" Algeria | \n",
" DZA | \n",
" 0.594158 | \n",
" 0.621349 | \n",
" 0.551894 | \n",
" 0.505261 | \n",
" 0.412363 | \n",
" 0.471756 | \n",
" 0.425258 | \n",
"
\n",
" \n",
" Andorra | \n",
" AND | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
" Angola | \n",
" AGO | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" iso3 GII1995 GII2000 GII2005 GII2010 GII2011 GII2012 \\\n",
"Country \n",
"Afghanistan AFG NaN NaN 0.743191 0.720173 0.706641 0.712844 \n",
"Albania ALB 0.661140 NaN 0.366596 0.268824 0.271414 0.251003 \n",
"Algeria DZA 0.594158 0.621349 0.551894 0.505261 0.412363 0.471756 \n",
"Andorra AND NaN NaN NaN NaN NaN NaN \n",
"Angola AGO NaN NaN NaN NaN NaN NaN \n",
"\n",
" GII2013 \n",
"Country \n",
"Afghanistan 0.705302 \n",
"Albania 0.245376 \n",
"Algeria 0.425258 \n",
"Andorra NaN \n",
"Angola NaN "
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"gii=pd.read_excel('gii_series_cartagena.xlsx',sheetname=u'GII',header=2).dropna(axis=1, how='all').set_index('Country')\n",
"gii.head()"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"cc={\n",
"'Bolivia (Plurinational State of)':'Bolivia',\n",
"'Brunei Darussalam':'Brunei',\n",
"'Congo':'Republic of the Congo',\n",
"'Congo (Democratic Republic of the)':'Democratic Republic of the Congo',\n",
"\"Cote d'Ivoire\":'Ivory Coast',\n",
"'Hong Kong, China (SAR)':'Hong Kong',\n",
"'Iran (Islamic Republic of)':'Iran',\n",
"\"Korea (Democratic People's Republic of)\":'North Korea',\n",
"'Korea (Republic of)':'South Korea',\n",
"\"Lao People's Democratic Republic\":'Laos',\n",
"'Libyan Arab Jamahiriya':'Libya',\n",
"'Micronesia (Federated States of)':'Micronesia',\n",
"'Moldova (Republic of)':'Moldova',\n",
"'Myanmar':'Myanmar [Burma]',\n",
"'Palestine (State of)':'Palestine',\n",
"'Russian Federation':'Russia',\n",
"'Sao Tome and Principe':u'São Tomé and PrÃncipe',\n",
"'Syrian Arab Republic':'Syria',\n",
"'Tanzania (United Republic of)':'Tanzania',\n",
"'The former Yugoslav Republic of Macedonia':'Macedonia',\n",
"'Timor-Leste':'East Timor',\n",
"'Venezuela (Bolivarian Republic of)':'Venezuela'\n",
"}\n",
"cc2={\n",
"\"Cote d'Ivoire\":u\"Côte d'Ivoire\",\n",
"\"Korea (Democratic People's Republic of)\":\"Korea (Democratic People's Rep. of)\",\n",
"'Palestine (State of)':'Palestine, State of',\n",
"'Vietnam':'Viet Nam',\n",
"'Libyan Arab Jamahiriya':'Libya'\n",
"}\n",
"cc3={\n",
"'Libyan Arab Jamahiriya':'Libya'\n",
"}\n",
"cc4={\n",
"\"Korea (Democratic People's Republic of)\":\"Korea (Democratic People's Rep. of)\",\n",
"'Libyan Arab Jamahiriya':'Libya'\n",
"}\n",
"\n",
"continent_converter={\n",
"'EU':'Europe',\n",
"'AS':'Asia',\n",
"'SA':'South America',\n",
"'nan':'North America',\n",
"'AN':'Antarctica',\n",
"'AF':'Africa',\n",
"'OC':'Oceania'\n",
"}\n",
"\n",
"def country_name_converter(country):\n",
" if country in cc: return cc[country]\n",
" else: return country\n",
"def country_name_converter2(country):\n",
" if country in cc2: return cc2[country]\n",
" else: return country\n",
"def country_name_converter3(country):\n",
" if country in cc3: return cc3[country]\n",
" else: return country\n",
"def country_name_converter4(country):\n",
" if country in cc4: return cc4[country]\n",
" else: return country"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"data={}\n",
"for country in ihdi.index: \n",
" if country_name_converter(country) in countries.index:\n",
" icountry=country_name_converter(country)\n",
" \n",
" data[countries.loc[icountry]['ISONUM']]={}\n",
" data[countries.loc[icountry]['ISONUM']]['name']=icountry\n",
" data[countries.loc[icountry]['ISONUM']]['code']=countries.loc[icountry]['ISO3']\n",
" data[countries.loc[icountry]['ISONUM']]['population']=countries.loc[icountry]['Population']\n",
" data[countries.loc[icountry]['ISONUM']]['area']=countries.loc[icountry]['Area']\n",
" data[countries.loc[icountry]['ISONUM']]['continent']=continent_converter[countries.loc[icountry]['Continent']]\n",
" data[countries.loc[icountry]['ISONUM']]['hdi']={}\n",
" data[countries.loc[icountry]['ISONUM']]['ihdi']={}\n",
" data[countries.loc[icountry]['ISONUM']]['gii']={}\n",
" for year in range(2010,2014):\n",
" try:\n",
" data[countries.loc[icountry]['ISONUM']]['hdi'][year]={}\n",
" if np.isnan(hdi.loc[country]['edux'+repr(year)]):\n",
" data[countries.loc[icountry]['ISONUM']]['hdi'][year]['edu']=\"NaN\"\n",
" else: data[countries.loc[icountry]['ISONUM']]['hdi'][year]['edu']=round(hdi.loc[country]['edux'+repr(year)],3)\n",
" if np.isnan(hdi.loc[country]['lex'+repr(year)]):\n",
" data[countries.loc[icountry]['ISONUM']]['hdi'][year]['le']=\"NaN\"\n",
" else: data[countries.loc[icountry]['ISONUM']]['hdi'][year]['le']=round(hdi.loc[country]['lex'+repr(year)],3)\n",
" if np.isnan(hdi.loc[country]['gnix'+repr(year)]):\n",
" data[countries.loc[icountry]['ISONUM']]['hdi'][year]['gni']=\"NaN\"\n",
" else: data[countries.loc[icountry]['ISONUM']]['hdi'][year]['gni']=round(hdi.loc[country]['gnix'+repr(year)],3)\n",
" except: print country,'hdi'\n",
" try:\n",
" data[countries.loc[icountry]['ISONUM']]['ihdi'][year]={}\n",
" if np.isnan(ihdi.loc[country]['iedux'+repr(year)]):\n",
" data[countries.loc[icountry]['ISONUM']]['ihdi'][year]['edu']=\"NaN\"\n",
" else: data[countries.loc[icountry]['ISONUM']]['ihdi'][year]['edu']=round(ihdi.loc[country]['iedux'+repr(year)],3)\n",
" if np.isnan(ihdi.loc[country]['ilex'+repr(year)]):\n",
" data[countries.loc[icountry]['ISONUM']]['ihdi'][year]['le']=\"NaN\"\n",
" else: data[countries.loc[icountry]['ISONUM']]['ihdi'][year]['le']=round(ihdi.loc[country]['ilex'+repr(year)],3)\n",
" if np.isnan(ihdi.loc[country]['ignix'+repr(year)]):\n",
" data[countries.loc[icountry]['ISONUM']]['ihdi'][year]['gni']=\"NaN\"\n",
" else: data[countries.loc[icountry]['ISONUM']]['ihdi'][year]['gni']=round(ihdi.loc[country]['ignix'+repr(year)],3)\n",
" except:\n",
" print country,'ihdi'\n",
" try:\n",
" gcountry=country_name_converter2(country)\n",
" if np.isnan(gii.loc[gcountry]['GII'+repr(year)]):\n",
" data[countries.loc[icountry]['ISONUM']]['gii'][year]=\"NaN\"\n",
" else: data[countries.loc[icountry]['ISONUM']]['gii'][year]=round(gii.loc[gcountry]['GII'+repr(year)],3)\n",
" except:\n",
" print country,'gii'"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"import json\n",
"file('data.json','w').write(json.dumps(data))"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso3 | \n",
" GNI1980 | \n",
" GNI1981 | \n",
" GNI1982 | \n",
" GNI1983 | \n",
" GNI1984 | \n",
" GNI1985 | \n",
" GNI1986 | \n",
" GNI1987 | \n",
" GNI1988 | \n",
" ... | \n",
" GNI2004 | \n",
" GNI2005 | \n",
" GNI2006 | \n",
" GNI2007 | \n",
" GNI2008 | \n",
" GNI2009 | \n",
" GNI2010 | \n",
" GNI2011 | \n",
" GNI2012 | \n",
" GNI2013 | \n",
"
\n",
" \n",
" Country | \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",
" Afghanistan | \n",
" AFG | \n",
" 2548.254832 | \n",
" 2466.626434 | \n",
" 2562.737937 | \n",
" 2626.621422 | \n",
" 2844.072554 | \n",
" 2990.928165 | \n",
" 3083.504539 | \n",
" 3250.936824 | \n",
" 2969.797438 | \n",
" ... | \n",
" 1125.869194 | \n",
" 1125.857560 | \n",
" 1202.399608 | \n",
" 1263.661214 | \n",
" 1432.486514 | \n",
" 1454.914569 | \n",
" 1677.201601 | \n",
" 1697.523824 | \n",
" 1882.395509 | \n",
" 1903.656673 | \n",
"
\n",
" \n",
" Albania | \n",
" ALB | \n",
" 3206.926814 | \n",
" 3232.740139 | \n",
" 3339.181470 | \n",
" 3359.854185 | \n",
" 3319.652177 | \n",
" 3205.344390 | \n",
" 3178.175640 | \n",
" 3264.309099 | \n",
" 3150.099437 | \n",
" ... | \n",
" 6553.656875 | \n",
" 6920.969736 | \n",
" 7373.477016 | \n",
" 7867.466696 | \n",
" 8350.808162 | \n",
" 8400.746220 | \n",
" 8705.765308 | \n",
" 9074.926805 | \n",
" 9069.389993 | \n",
" 9225.050846 | \n",
"
\n",
" \n",
" Algeria | \n",
" DZA | \n",
" 10215.030188 | \n",
" 10039.512129 | \n",
" 10014.561086 | \n",
" 10313.216799 | \n",
" 10560.014313 | \n",
" 10928.949084 | \n",
" 11025.723938 | \n",
" 10849.097215 | \n",
" 10724.071343 | \n",
" ... | \n",
" 10879.228521 | \n",
" 11133.509897 | \n",
" 11577.415445 | \n",
" 11650.490699 | \n",
" 12153.510881 | \n",
" 12227.579531 | \n",
" 12185.446764 | \n",
" 12495.444586 | \n",
" 12356.046736 | \n",
" 12554.571986 | \n",
"
\n",
" \n",
" Andorra | \n",
" AND | \n",
" 31812.140284 | \n",
" 31393.180239 | \n",
" 30148.593193 | \n",
" 29267.883014 | \n",
" 28510.312997 | \n",
" 27767.417852 | \n",
" 27206.021185 | \n",
" 26932.811055 | \n",
" 27286.989362 | \n",
" ... | \n",
" 33749.938605 | \n",
" 34385.304454 | \n",
" 35450.339111 | \n",
" 37555.137778 | \n",
" 38365.363439 | \n",
" 40870.160552 | \n",
" 43673.948833 | \n",
" 41906.643301 | \n",
" 40960.901628 | \n",
" 40597.124810 | \n",
"
\n",
" \n",
" Angola | \n",
" AGO | \n",
" 3259.908578 | \n",
" 3253.536712 | \n",
" 3010.757634 | \n",
" 2912.914398 | \n",
" 2897.972093 | \n",
" 2946.294587 | \n",
" 2902.630135 | \n",
" 2901.611617 | \n",
" 3104.058261 | \n",
" ... | \n",
" 3057.616822 | \n",
" 3878.839822 | \n",
" 5041.274640 | \n",
" 5662.865490 | \n",
" 6271.567560 | \n",
" 5246.697802 | \n",
" 5853.837614 | \n",
" 6270.903706 | \n",
" 6258.566318 | \n",
" 6322.938484 | \n",
"
\n",
" \n",
"
\n",
"
5 rows × 35 columns
\n",
"
"
],
"text/plain": [
" iso3 GNI1980 GNI1981 GNI1982 GNI1983 \\\n",
"Country \n",
"Afghanistan AFG 2548.254832 2466.626434 2562.737937 2626.621422 \n",
"Albania ALB 3206.926814 3232.740139 3339.181470 3359.854185 \n",
"Algeria DZA 10215.030188 10039.512129 10014.561086 10313.216799 \n",
"Andorra AND 31812.140284 31393.180239 30148.593193 29267.883014 \n",
"Angola AGO 3259.908578 3253.536712 3010.757634 2912.914398 \n",
"\n",
" GNI1984 GNI1985 GNI1986 GNI1987 \\\n",
"Country \n",
"Afghanistan 2844.072554 2990.928165 3083.504539 3250.936824 \n",
"Albania 3319.652177 3205.344390 3178.175640 3264.309099 \n",
"Algeria 10560.014313 10928.949084 11025.723938 10849.097215 \n",
"Andorra 28510.312997 27767.417852 27206.021185 26932.811055 \n",
"Angola 2897.972093 2946.294587 2902.630135 2901.611617 \n",
"\n",
" GNI1988 ... GNI2004 GNI2005 \\\n",
"Country ... \n",
"Afghanistan 2969.797438 ... 1125.869194 1125.857560 \n",
"Albania 3150.099437 ... 6553.656875 6920.969736 \n",
"Algeria 10724.071343 ... 10879.228521 11133.509897 \n",
"Andorra 27286.989362 ... 33749.938605 34385.304454 \n",
"Angola 3104.058261 ... 3057.616822 3878.839822 \n",
"\n",
" GNI2006 GNI2007 GNI2008 GNI2009 \\\n",
"Country \n",
"Afghanistan 1202.399608 1263.661214 1432.486514 1454.914569 \n",
"Albania 7373.477016 7867.466696 8350.808162 8400.746220 \n",
"Algeria 11577.415445 11650.490699 12153.510881 12227.579531 \n",
"Andorra 35450.339111 37555.137778 38365.363439 40870.160552 \n",
"Angola 5041.274640 5662.865490 6271.567560 5246.697802 \n",
"\n",
" GNI2010 GNI2011 GNI2012 GNI2013 \n",
"Country \n",
"Afghanistan 1677.201601 1697.523824 1882.395509 1903.656673 \n",
"Albania 8705.765308 9074.926805 9069.389993 9225.050846 \n",
"Algeria 12185.446764 12495.444586 12356.046736 12554.571986 \n",
"Andorra 43673.948833 41906.643301 40960.901628 40597.124810 \n",
"Angola 5853.837614 6270.903706 6258.566318 6322.938484 \n",
"\n",
"[5 rows x 35 columns]"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"GNI=pd.read_excel('hdi_series_cartagena.xlsx',sheetname=u'Gross National Income',header=2).set_index('Country')\n",
"GNI.head()"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso3 | \n",
" Life_Exp1980 | \n",
" Life_Exp1981 | \n",
" Life_Exp1982 | \n",
" Life_Exp1983 | \n",
" Life_Exp1984 | \n",
" Life_Exp1985 | \n",
" Life_Exp1986 | \n",
" Life_Exp1987 | \n",
" Life_Exp1988 | \n",
" ... | \n",
" Life_Exp2004 | \n",
" Life_Exp2005 | \n",
" Life_Exp2006 | \n",
" Life_Exp2007 | \n",
" Life_Exp2008 | \n",
" Life_Exp2009 | \n",
" Life_Exp2010 | \n",
" Life_Exp2011 | \n",
" Life_Exp2012 | \n",
" Life_Exp2013 | \n",
"
\n",
" \n",
" Country | \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",
" Afghanistan | \n",
" AFG | \n",
" 41.242 | \n",
" 41.770 | \n",
" 42.347 | \n",
" 42.977 | \n",
" 43.661 | \n",
" 44.400 | \n",
" 45.192 | \n",
" 46.024 | \n",
" 46.880 | \n",
" ... | \n",
" 56.583 | \n",
" 57.071 | \n",
" 57.582 | \n",
" 58.102 | \n",
" 58.618 | \n",
" 59.124 | \n",
" 59.612 | \n",
" 60.079 | \n",
" 60.524 | \n",
" 60.947 | \n",
"
\n",
" \n",
" Albania | \n",
" ALB | \n",
" 70.218 | \n",
" 70.426 | \n",
" 70.646 | \n",
" 70.886 | \n",
" 71.144 | \n",
" 71.398 | \n",
" 71.615 | \n",
" 71.770 | \n",
" 71.853 | \n",
" ... | \n",
" 75.725 | \n",
" 75.949 | \n",
" 76.124 | \n",
" 76.278 | \n",
" 76.433 | \n",
" 76.598 | \n",
" 76.780 | \n",
" 76.979 | \n",
" 77.185 | \n",
" 77.392 | \n",
"
\n",
" \n",
" Algeria | \n",
" DZA | \n",
" 58.198 | \n",
" 59.524 | \n",
" 60.826 | \n",
" 62.051 | \n",
" 63.160 | \n",
" 64.120 | \n",
" 64.911 | \n",
" 65.554 | \n",
" 66.072 | \n",
" ... | \n",
" 69.682 | \n",
" 69.854 | \n",
" 70.020 | \n",
" 70.180 | \n",
" 70.332 | \n",
" 70.477 | \n",
" 70.615 | \n",
" 70.747 | \n",
" 70.874 | \n",
" 71.000 | \n",
"
\n",
" \n",
" Andorra | \n",
" AND | \n",
" 74.856 | \n",
" 75.115 | \n",
" 75.344 | \n",
" 75.541 | \n",
" 75.707 | \n",
" 75.848 | \n",
" 75.973 | \n",
" 76.094 | \n",
" 76.222 | \n",
" ... | \n",
" 80.005 | \n",
" 80.192 | \n",
" 80.343 | \n",
" 80.471 | \n",
" 80.589 | \n",
" 80.703 | \n",
" 80.818 | \n",
" 80.935 | \n",
" 81.054 | \n",
" 81.173 | \n",
"
\n",
" \n",
" Angola | \n",
" AGO | \n",
" 40.182 | \n",
" 40.311 | \n",
" 40.429 | \n",
" 40.547 | \n",
" 40.671 | \n",
" 40.794 | \n",
" 40.902 | \n",
" 40.988 | \n",
" 41.050 | \n",
" ... | \n",
" 48.036 | \n",
" 48.572 | \n",
" 49.041 | \n",
" 49.471 | \n",
" 49.882 | \n",
" 50.286 | \n",
" 50.689 | \n",
" 51.094 | \n",
" 51.498 | \n",
" 51.899 | \n",
"
\n",
" \n",
"
\n",
"
5 rows × 35 columns
\n",
"
"
],
"text/plain": [
" iso3 Life_Exp1980 Life_Exp1981 Life_Exp1982 Life_Exp1983 \\\n",
"Country \n",
"Afghanistan AFG 41.242 41.770 42.347 42.977 \n",
"Albania ALB 70.218 70.426 70.646 70.886 \n",
"Algeria DZA 58.198 59.524 60.826 62.051 \n",
"Andorra AND 74.856 75.115 75.344 75.541 \n",
"Angola AGO 40.182 40.311 40.429 40.547 \n",
"\n",
" Life_Exp1984 Life_Exp1985 Life_Exp1986 Life_Exp1987 \\\n",
"Country \n",
"Afghanistan 43.661 44.400 45.192 46.024 \n",
"Albania 71.144 71.398 71.615 71.770 \n",
"Algeria 63.160 64.120 64.911 65.554 \n",
"Andorra 75.707 75.848 75.973 76.094 \n",
"Angola 40.671 40.794 40.902 40.988 \n",
"\n",
" Life_Exp1988 ... Life_Exp2004 Life_Exp2005 \\\n",
"Country ... \n",
"Afghanistan 46.880 ... 56.583 57.071 \n",
"Albania 71.853 ... 75.725 75.949 \n",
"Algeria 66.072 ... 69.682 69.854 \n",
"Andorra 76.222 ... 80.005 80.192 \n",
"Angola 41.050 ... 48.036 48.572 \n",
"\n",
" Life_Exp2006 Life_Exp2007 Life_Exp2008 Life_Exp2009 \\\n",
"Country \n",
"Afghanistan 57.582 58.102 58.618 59.124 \n",
"Albania 76.124 76.278 76.433 76.598 \n",
"Algeria 70.020 70.180 70.332 70.477 \n",
"Andorra 80.343 80.471 80.589 80.703 \n",
"Angola 49.041 49.471 49.882 50.286 \n",
"\n",
" Life_Exp2010 Life_Exp2011 Life_Exp2012 Life_Exp2013 \n",
"Country \n",
"Afghanistan 59.612 60.079 60.524 60.947 \n",
"Albania 76.780 76.979 77.185 77.392 \n",
"Algeria 70.615 70.747 70.874 71.000 \n",
"Andorra 80.818 80.935 81.054 81.173 \n",
"Angola 50.689 51.094 51.498 51.899 \n",
"\n",
"[5 rows x 35 columns]"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"LE=pd.read_excel('hdi_series_cartagena.xlsx',sheetname=u'Life Expectency',header=2).set_index('Country')\n",
"LE.head()"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" iso3 | \n",
" EYS1980 | \n",
" EYS1981 | \n",
" EYS1982 | \n",
" EYS1983 | \n",
" EYS1984 | \n",
" EYS1985 | \n",
" EYS1986 | \n",
" EYS1987 | \n",
" EYS1988 | \n",
" ... | \n",
" EYS2004 | \n",
" EYS2005 | \n",
" EYS2006 | \n",
" EYS2007 | \n",
" EYS2008 | \n",
" EYS2009 | \n",
" EYS2010 | \n",
" EYS2011 | \n",
" EYS2012 | \n",
" EYS2013 | \n",
"
\n",
" \n",
" Country | \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",
" Afghanistan | \n",
" AFG | \n",
" 1.80000 | \n",
" 1.80000 | \n",
" 1.80000 | \n",
" 1.97500 | \n",
" 2.15000 | \n",
" 2.32500 | \n",
" 2.50000 | \n",
" 2.52500 | \n",
" 2.55000 | \n",
" ... | \n",
" 7.50000 | \n",
" 7.74000 | \n",
" 7.98000 | \n",
" 8.22000 | \n",
" 8.46000 | \n",
" 8.70000 | \n",
" 9.00000 | \n",
" 9.30000 | \n",
" 9.30000 | \n",
" 9.30000 | \n",
"
\n",
" \n",
" Albania | \n",
" ALB | \n",
" 11.30000 | \n",
" 11.30000 | \n",
" 10.80000 | \n",
" 10.30000 | \n",
" 10.30000 | \n",
" 10.50000 | \n",
" 10.60000 | \n",
" 10.40000 | \n",
" 10.40000 | \n",
" ... | \n",
" 10.80000 | \n",
" 10.80000 | \n",
" 10.80000 | \n",
" 10.80000 | \n",
" 10.80000 | \n",
" 10.80000 | \n",
" 10.80000 | \n",
" 10.80000 | \n",
" 10.80000 | \n",
" 10.80000 | \n",
"
\n",
" \n",
" Algeria | \n",
" DZA | \n",
" 9.40000 | \n",
" 9.40000 | \n",
" 9.40000 | \n",
" 9.40000 | \n",
" 9.40000 | \n",
" 9.40000 | \n",
" 9.40000 | \n",
" 9.40000 | \n",
" 9.60000 | \n",
" ... | \n",
" 11.80000 | \n",
" 12.00000 | \n",
" 12.10000 | \n",
" 12.30000 | \n",
" 12.75000 | \n",
" 13.20000 | \n",
" 13.60000 | \n",
" 14.00000 | \n",
" 14.00000 | \n",
" 14.00000 | \n",
"
\n",
" \n",
" Andorra | \n",
" AND | \n",
" 10.79878 | \n",
" 10.79878 | \n",
" 10.79878 | \n",
" 10.79878 | \n",
" 10.79878 | \n",
" 10.79878 | \n",
" 10.79878 | \n",
" 10.79878 | \n",
" 10.79878 | \n",
" ... | \n",
" 10.79199 | \n",
" 10.82593 | \n",
" 11.18277 | \n",
" 11.18277 | \n",
" 11.67192 | \n",
" 11.67192 | \n",
" 11.67192 | \n",
" 11.67192 | \n",
" 11.67192 | \n",
" 11.67192 | \n",
"
\n",
" \n",
" Angola | \n",
" AGO | \n",
" 4.20000 | \n",
" 4.20000 | \n",
" 4.20000 | \n",
" 4.20000 | \n",
" 4.20000 | \n",
" 4.20000 | \n",
" 4.20000 | \n",
" 4.20000 | \n",
" 4.20000 | \n",
" ... | \n",
" 7.35000 | \n",
" 7.82500 | \n",
" 8.30000 | \n",
" 8.77500 | \n",
" 9.25000 | \n",
" 9.72500 | \n",
" 10.20000 | \n",
" 11.40000 | \n",
" 11.40000 | \n",
" 11.40000 | \n",
"
\n",
" \n",
"
\n",
"
5 rows × 35 columns
\n",
"
"
],
"text/plain": [
" iso3 EYS1980 EYS1981 EYS1982 EYS1983 EYS1984 EYS1985 \\\n",
"Country \n",
"Afghanistan AFG 1.80000 1.80000 1.80000 1.97500 2.15000 2.32500 \n",
"Albania ALB 11.30000 11.30000 10.80000 10.30000 10.30000 10.50000 \n",
"Algeria DZA 9.40000 9.40000 9.40000 9.40000 9.40000 9.40000 \n",
"Andorra AND 10.79878 10.79878 10.79878 10.79878 10.79878 10.79878 \n",
"Angola AGO 4.20000 4.20000 4.20000 4.20000 4.20000 4.20000 \n",
"\n",
" EYS1986 EYS1987 EYS1988 ... EYS2004 EYS2005 \\\n",
"Country ... \n",
"Afghanistan 2.50000 2.52500 2.55000 ... 7.50000 7.74000 \n",
"Albania 10.60000 10.40000 10.40000 ... 10.80000 10.80000 \n",
"Algeria 9.40000 9.40000 9.60000 ... 11.80000 12.00000 \n",
"Andorra 10.79878 10.79878 10.79878 ... 10.79199 10.82593 \n",
"Angola 4.20000 4.20000 4.20000 ... 7.35000 7.82500 \n",
"\n",
" EYS2006 EYS2007 EYS2008 EYS2009 EYS2010 EYS2011 \\\n",
"Country \n",
"Afghanistan 7.98000 8.22000 8.46000 8.70000 9.00000 9.30000 \n",
"Albania 10.80000 10.80000 10.80000 10.80000 10.80000 10.80000 \n",
"Algeria 12.10000 12.30000 12.75000 13.20000 13.60000 14.00000 \n",
"Andorra 11.18277 11.18277 11.67192 11.67192 11.67192 11.67192 \n",
"Angola 8.30000 8.77500 9.25000 9.72500 10.20000 11.40000 \n",
"\n",
" EYS2012 EYS2013 \n",
"Country \n",
"Afghanistan 9.30000 9.30000 \n",
"Albania 10.80000 10.80000 \n",
"Algeria 14.00000 14.00000 \n",
"Andorra 11.67192 11.67192 \n",
"Angola 11.40000 11.40000 \n",
"\n",
"[5 rows x 35 columns]"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"EYS=pd.read_excel('hdi_series_cartagena.xlsx',sheetname=u'Expected Years of Schooling',header=2).set_index('Country')\n",
"EYS.head()"
]
},
{
"cell_type": "code",
"execution_count": 63,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"MYS=pd.read_excel('hdi_series_cartagena.xlsx',sheetname=u'Mean Years of Schooling',header=2).set_index('Country')\n",
"MYS.head()\n",
"#fux db\n",
"MYS.loc[\"Vanuatu\",\"MYS1980\"]=np.NaN"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Vanuatu hdi 1980\n"
]
}
],
"source": [
"data2={}\n",
"for country in GNI.index: \n",
" if country_name_converter(country) in countries.index:\n",
" icountry=country_name_converter(country)\n",
" \n",
" data2[countries.loc[icountry]['ISONUM']]={}\n",
" data2[countries.loc[icountry]['ISONUM']]['name']=icountry\n",
" data2[countries.loc[icountry]['ISONUM']]['code']=countries.loc[icountry]['ISO3']\n",
" data2[countries.loc[icountry]['ISONUM']]['population']=countries.loc[icountry]['Population']\n",
" data2[countries.loc[icountry]['ISONUM']]['area']=countries.loc[icountry]['Area']\n",
" data2[countries.loc[icountry]['ISONUM']]['continent']=continent_converter[countries.loc[icountry]['Continent']]\n",
" data2[countries.loc[icountry]['ISONUM']]['hdi']={}\n",
" for year in range(1980,2014):\n",
" try:\n",
" data2[countries.loc[icountry]['ISONUM']]['hdi'][year]={}\n",
" data2[countries.loc[icountry]['ISONUM']]['hdi'][year]['edu']=\\\n",
" round((MYS.loc[country_name_converter3(country)]['MYS'+repr(year)]/15+\\\n",
" EYS.loc[country_name_converter3(country)]['EYS'+repr(year)]/18)/2,3)\n",
" data2[countries.loc[icountry]['ISONUM']]['hdi'][year]['le']=\\\n",
" round((LE.loc[country_name_converter4(country)]['Life_Exp'+repr(year)]-20)/(85-20),3)\n",
" data2[countries.loc[icountry]['ISONUM']]['hdi'][year]['gni']=\\\n",
" round((np.log(GNI.loc[country]['GNI'+repr(year)])-np.log(100))/(np.log(75000)-np.log(100)),3)\n",
" except: print country,'hdi',year"
]
},
{
"cell_type": "code",
"execution_count": 231,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"import json\n",
"file('data2.json','w').write(json.dumps(data2))"
]
},
{
"cell_type": "code",
"execution_count": 64,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"for country in GNI.index: \n",
" if country_name_converter(country) in countries.index:\n",
" icountry=country_name_converter(country)\n",
" for year in range(1980,2010):\n",
" try:\n",
" data[countries.loc[icountry]['ISONUM']]['hdi'][year]={}\n",
" data[countries.loc[icountry]['ISONUM']]['hdi'][year]['edu']=\\\n",
" round((MYS.loc[country_name_converter3(country)]['MYS'+repr(year)]/15+\\\n",
" EYS.loc[country_name_converter3(country)]['EYS'+repr(year)]/18)/2,3)\n",
" data[countries.loc[icountry]['ISONUM']]['hdi'][year]['le']=\\\n",
" round((LE.loc[country_name_converter4(country)]['Life_Exp'+repr(year)]-20)/(85-20),3)\n",
" data[countries.loc[icountry]['ISONUM']]['hdi'][year]['gni']=\\\n",
" round((np.log(GNI.loc[country]['GNI'+repr(year)])-np.log(100))/(np.log(75000)-np.log(100)),3)\n",
" if np.isnan(data[countries.loc[icountry]['ISONUM']]['hdi'][year]['edu']):\n",
" data[countries.loc[icountry]['ISONUM']]['hdi'][year]['edu']=\"NaN\"\n",
" if np.isnan(data[countries.loc[icountry]['ISONUM']]['hdi'][year]['le']):\n",
" data[countries.loc[icountry]['ISONUM']]['hdi'][year]['le']=\"NaN\"\n",
" if np.isnan(data[countries.loc[icountry]['ISONUM']]['hdi'][year]['gni']):\n",
" data[countries.loc[icountry]['ISONUM']]['hdi'][year]['gni']=\"NaN\"\n",
" \n",
" data[countries.loc[icountry]['ISONUM']]['ihdi'][year]={'edu':\"NaN\",'le':\"NaN\",'gni':\"NaN\"}\n",
" \n",
" data[countries.loc[icountry]['ISONUM']]['gii'][year]=\"NaN\"\n",
" if year in [1995,2000,2005]:\n",
" try:\n",
" gcountry=country_name_converter2(country)\n",
" if np.isnan(gii.loc[gcountry]['GII'+repr(year)]):\n",
" data[countries.loc[icountry]['ISONUM']]['gii'][year]=\"NaN\"\n",
" else: data[countries.loc[icountry]['ISONUM']]['gii'][year]=round(gii.loc[gcountry]['GII'+repr(year)],3)\n",
" except:\n",
" print country,'gii'\n",
" except: print country,'error',year"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"import json\n",
"file('data3.json','w').write(json.dumps(data))"
]
},
{
"cell_type": "code",
"execution_count": 65,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"pop=pd.read_excel('http://esa.un.org/unpd/wpp/Excel-Data/EXCEL_FILES/1_Population/WPP2012_POP_F01_1_TOTAL_POPULATION_BOTH_SEXES.XLS',sheet=\"ESTIMATES\",header=16)"
]
},
{
"cell_type": "code",
"execution_count": 66,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"pop=pop.set_index(['Country code'])"
]
},
{
"cell_type": "code",
"execution_count": 67,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"for i in data:\n",
" data[i]['population']={}\n",
" for year in range(1980,2011):\n",
" data[i]['population'][year]=\\\n",
" pop.loc[(int)(i)].values[::-1][2010-year]*1000 "
]
},
{
"cell_type": "code",
"execution_count": 68,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"pop2=pd.read_excel('http://esa.un.org/unpd/wpp/Excel-Data/EXCEL_FILES/1_Population/WPP2012_POP_F01_1_TOTAL_POPULATION_BOTH_SEXES.XLS',sheetname=\"NO CHANGE\",header=16)"
]
},
{
"cell_type": "code",
"execution_count": 69,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"pop2=pop2.set_index(['Country code'])"
]
},
{
"cell_type": "code",
"execution_count": 70,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"for i in data:\n",
" for year in range(2011,2014):\n",
" data[i]['population'][year]=\\\n",
" pop2.loc[(int)(i)].values[::-1][2100-year]*1000"
]
},
{
"cell_type": "code",
"execution_count": 71,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"import json\n",
"file('data3.json','w').write(json.dumps(data))"
]
}
],
"metadata": {
"anaconda-cloud": {},
"kernelspec": {
"display_name": "Python [default]",
"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.12"
}
},
"nbformat": 4,
"nbformat_minor": 0
}