preprocessing/prepare_json.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>en_curid</th>\n",
       "      <th>name</th>\n",
       "      <th>numlangs</th>\n",
       "      <th>birthcity</th>\n",
       "      <th>birthstate</th>\n",
       "      <th>countryName</th>\n",
       "      <th>countryCode</th>\n",
       "      <th>countryCode3</th>\n",
       "      <th>LAT</th>\n",
       "      <th>LON</th>\n",
       "      <th>...</th>\n",
       "      <th>occupation</th>\n",
       "      <th>industry</th>\n",
       "      <th>domain</th>\n",
       "      <th>TotalPageViews</th>\n",
       "      <th>L_star</th>\n",
       "      <th>StdDevPageViews</th>\n",
       "      <th>PageViewsEnglish</th>\n",
       "      <th>PageViewsNonEnglish</th>\n",
       "      <th>AverageViews</th>\n",
       "      <th>HPI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>307</td>\n",
       "      <td>Abraham Lincoln</td>\n",
       "      <td>131</td>\n",
       "      <td>Hodgenville</td>\n",
       "      <td>KY</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>US</td>\n",
       "      <td>USA</td>\n",
       "      <td>37.571111</td>\n",
       "      <td>-85.738611</td>\n",
       "      <td>...</td>\n",
       "      <td>POLITICIAN</td>\n",
       "      <td>GOVERNMENT</td>\n",
       "      <td>INSTITUTIONS</td>\n",
       "      <td>66145211</td>\n",
       "      <td>5.801387</td>\n",
       "      <td>586914.72200</td>\n",
       "      <td>41477236</td>\n",
       "      <td>24667975</td>\n",
       "      <td>504925.2748</td>\n",
       "      <td>27.938585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>308</td>\n",
       "      <td>Aristotle</td>\n",
       "      <td>152</td>\n",
       "      <td>Stageira</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Greece</td>\n",
       "      <td>GR</td>\n",
       "      <td>GRC</td>\n",
       "      <td>40.333333</td>\n",
       "      <td>23.500000</td>\n",
       "      <td>...</td>\n",
       "      <td>PHILOSOPHER</td>\n",
       "      <td>PHILOSOPHY</td>\n",
       "      <td>HUMANITIES</td>\n",
       "      <td>56355172</td>\n",
       "      <td>11.914597</td>\n",
       "      <td>201067.46070</td>\n",
       "      <td>15745351</td>\n",
       "      <td>40609821</td>\n",
       "      <td>370757.7105</td>\n",
       "      <td>31.993795</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>339</td>\n",
       "      <td>Ayn Rand</td>\n",
       "      <td>55</td>\n",
       "      <td>Saint Petersburg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Russia</td>\n",
       "      <td>RU</td>\n",
       "      <td>RUS</td>\n",
       "      <td>59.950000</td>\n",
       "      <td>30.300000</td>\n",
       "      <td>...</td>\n",
       "      <td>WRITER</td>\n",
       "      <td>LANGUAGE</td>\n",
       "      <td>HUMANITIES</td>\n",
       "      <td>14208218</td>\n",
       "      <td>3.175685</td>\n",
       "      <td>87632.49020</td>\n",
       "      <td>11023490</td>\n",
       "      <td>3184728</td>\n",
       "      <td>258331.2364</td>\n",
       "      <td>24.325936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>595</td>\n",
       "      <td>Andre Agassi</td>\n",
       "      <td>69</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>NV</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>US</td>\n",
       "      <td>USA</td>\n",
       "      <td>36.121514</td>\n",
       "      <td>-115.173851</td>\n",
       "      <td>...</td>\n",
       "      <td>TENNIS PLAYER</td>\n",
       "      <td>INDIVIDUAL SPORTS</td>\n",
       "      <td>SPORTS</td>\n",
       "      <td>11244030</td>\n",
       "      <td>6.242525</td>\n",
       "      <td>85553.31810</td>\n",
       "      <td>6353888</td>\n",
       "      <td>4890142</td>\n",
       "      <td>162956.9565</td>\n",
       "      <td>20.925999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>628</td>\n",
       "      <td>Aldous Huxley</td>\n",
       "      <td>62</td>\n",
       "      <td>Godalming</td>\n",
       "      <td>NaN</td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td>GB</td>\n",
       "      <td>GBR</td>\n",
       "      <td>51.185000</td>\n",
       "      <td>-0.610000</td>\n",
       "      <td>...</td>\n",
       "      <td>WRITER</td>\n",
       "      <td>LANGUAGE</td>\n",
       "      <td>HUMANITIES</td>\n",
       "      <td>9268920</td>\n",
       "      <td>6.219842</td>\n",
       "      <td>33037.03209</td>\n",
       "      <td>5137256</td>\n",
       "      <td>4131664</td>\n",
       "      <td>149498.7097</td>\n",
       "      <td>25.996605</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   en_curid             name  numlangs         birthcity birthstate  \\\n",
       "0       307  Abraham Lincoln       131       Hodgenville         KY   \n",
       "1       308        Aristotle       152          Stageira        NaN   \n",
       "2       339         Ayn Rand        55  Saint Petersburg        NaN   \n",
       "3       595     Andre Agassi        69         Las Vegas         NV   \n",
       "4       628    Aldous Huxley        62         Godalming        NaN   \n",
       "\n",
       "      countryName countryCode countryCode3        LAT         LON    ...      \\\n",
       "0   UNITED STATES          US          USA  37.571111  -85.738611    ...       \n",
       "1          Greece          GR          GRC  40.333333   23.500000    ...       \n",
       "2          Russia          RU          RUS  59.950000   30.300000    ...       \n",
       "3   UNITED STATES          US          USA  36.121514 -115.173851    ...       \n",
       "4  UNITED KINGDOM          GB          GBR  51.185000   -0.610000    ...       \n",
       "\n",
       "      occupation           industry        domain TotalPageViews     L_star  \\\n",
       "0     POLITICIAN         GOVERNMENT  INSTITUTIONS       66145211   5.801387   \n",
       "1    PHILOSOPHER         PHILOSOPHY    HUMANITIES       56355172  11.914597   \n",
       "2         WRITER           LANGUAGE    HUMANITIES       14208218   3.175685   \n",
       "3  TENNIS PLAYER  INDIVIDUAL SPORTS        SPORTS       11244030   6.242525   \n",
       "4         WRITER           LANGUAGE    HUMANITIES        9268920   6.219842   \n",
       "\n",
       "  StdDevPageViews  PageViewsEnglish  PageViewsNonEnglish  AverageViews  \\\n",
       "0    586914.72200          41477236             24667975   504925.2748   \n",
       "1    201067.46070          15745351             40609821   370757.7105   \n",
       "2     87632.49020          11023490              3184728   258331.2364   \n",
       "3     85553.31810           6353888              4890142   162956.9565   \n",
       "4     33037.03209           5137256              4131664   149498.7097   \n",
       "\n",
       "         HPI  \n",
       "0  27.938585  \n",
       "1  31.993795  \n",
       "2  24.325936  \n",
       "3  20.925999  \n",
       "4  25.996605  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_table('pantheon.tsv', sep='\\t')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "variables = ['en_curid', 'name', 'countryName', 'occupation']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>en_curid</th>\n",
       "      <th>name</th>\n",
       "      <th>countryName</th>\n",
       "      <th>occupation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>307</td>\n",
       "      <td>Abraham Lincoln</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>POLITICIAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>308</td>\n",
       "      <td>Aristotle</td>\n",
       "      <td>Greece</td>\n",
       "      <td>PHILOSOPHER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>339</td>\n",
       "      <td>Ayn Rand</td>\n",
       "      <td>Russia</td>\n",
       "      <td>WRITER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>595</td>\n",
       "      <td>Andre Agassi</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>TENNIS PLAYER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>628</td>\n",
       "      <td>Aldous Huxley</td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td>WRITER</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   en_curid             name     countryName     occupation\n",
       "0       307  Abraham Lincoln   UNITED STATES     POLITICIAN\n",
       "1       308        Aristotle          Greece    PHILOSOPHER\n",
       "2       339         Ayn Rand          Russia         WRITER\n",
       "3       595     Andre Agassi   UNITED STATES  TENNIS PLAYER\n",
       "4       628    Aldous Huxley  UNITED KINGDOM         WRITER"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[variables].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "G:\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:3781: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  return super(DataFrame, self).rename(**kwargs)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>description</th>\n",
       "      <th>category_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>307</td>\n",
       "      <td>Abraham Lincoln</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>POLITICIAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>308</td>\n",
       "      <td>Aristotle</td>\n",
       "      <td>Greece</td>\n",
       "      <td>PHILOSOPHER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>339</td>\n",
       "      <td>Ayn Rand</td>\n",
       "      <td>Russia</td>\n",
       "      <td>WRITER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>595</td>\n",
       "      <td>Andre Agassi</td>\n",
       "      <td>UNITED STATES</td>\n",
       "      <td>TENNIS PLAYER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>628</td>\n",
       "      <td>Aldous Huxley</td>\n",
       "      <td>UNITED KINGDOM</td>\n",
       "      <td>WRITER</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id             name     description  category_type\n",
       "0  307  Abraham Lincoln   UNITED STATES     POLITICIAN\n",
       "1  308        Aristotle          Greece    PHILOSOPHER\n",
       "2  339         Ayn Rand          Russia         WRITER\n",
       "3  595     Andre Agassi   UNITED STATES  TENNIS PLAYER\n",
       "4  628    Aldous Huxley  UNITED KINGDOM         WRITER"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = df[variables]\n",
    "x.rename(columns = {'en_curid': 'id', 'countryName': 'description', 'occupation': 'category_type'}, inplace=True)\n",
    "x.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = []\n",
    "# open a json file and save json data\n",
    "with open('data.json', 'w') as outfile:\n",
    "    for i in x.index:\n",
    "        data.append(json.loads(x.loc[i].to_json()))\n",
    "    json.dump(data, outfile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}