aws-dynamodb-import-census.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AWS DynamoDB Import Census Data\n",
    "\n",
    "This is a companion notebook for the new [Data Science Solutions](https://strtupsci.com) book. The code is explained in the book.\n",
    "\n",
    "**IMPORTANT.** The code will only run if you have a local DynamoDB server installed and running on localhost port 8000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((32561, 15), (16281, 15))"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data analysis and wrangling\n",
    "import pandas as pd\n",
    "import boto3\n",
    "import numpy as np\n",
    "\n",
    "already_imported = True\n",
    "\n",
    "resource = boto3.resource(\n",
    "    'dynamodb', endpoint_url='http://localhost:8000')\n",
    "\n",
    "client = boto3.client(\n",
    "    'dynamodb', endpoint_url='http://localhost:8000')\n",
    "\n",
    "census_table = resource.Table('Census')\n",
    "census_test_table = resource.Table('CensusTest')\n",
    "\n",
    "column_names = [\n",
    "    'age', 'workclass', 'fnlwgt', \n",
    "    'education', 'education-num', 'marital-status', \n",
    "    'occupation', 'relationship', 'race', 'sex', \n",
    "    'capital-gain', 'capital-loss', 'hours-per-week', \n",
    "    'native-country', 'salary']\n",
    "\n",
    "train_df = pd.read_csv(\n",
    "    'data/aws/census/adult.data', \n",
    "    header=None, names=column_names, \n",
    "    sep=', ', engine='python')\n",
    "\n",
    "test_df = pd.read_csv(\n",
    "    'data/aws/census/adult.test', \n",
    "    header=None, names=column_names, \n",
    "    sep=', ', engine='python', skiprows=1)\n",
    "\n",
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>workclass</th>\n",
       "      <th>fnlwgt</th>\n",
       "      <th>education</th>\n",
       "      <th>education-num</th>\n",
       "      <th>marital-status</th>\n",
       "      <th>occupation</th>\n",
       "      <th>relationship</th>\n",
       "      <th>race</th>\n",
       "      <th>sex</th>\n",
       "      <th>capital-gain</th>\n",
       "      <th>capital-loss</th>\n",
       "      <th>hours-per-week</th>\n",
       "      <th>native-country</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39</td>\n",
       "      <td>State-gov</td>\n",
       "      <td>77516</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>13</td>\n",
       "      <td>Never-married</td>\n",
       "      <td>Adm-clerical</td>\n",
       "      <td>Not-in-family</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>2174</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>United-States</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>Self-emp-not-inc</td>\n",
       "      <td>83311</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>13</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Exec-managerial</td>\n",
       "      <td>Husband</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>United-States</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38</td>\n",
       "      <td>Private</td>\n",
       "      <td>215646</td>\n",
       "      <td>HS-grad</td>\n",
       "      <td>9</td>\n",
       "      <td>Divorced</td>\n",
       "      <td>Handlers-cleaners</td>\n",
       "      <td>Not-in-family</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>United-States</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>53</td>\n",
       "      <td>Private</td>\n",
       "      <td>234721</td>\n",
       "      <td>11th</td>\n",
       "      <td>7</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Handlers-cleaners</td>\n",
       "      <td>Husband</td>\n",
       "      <td>Black</td>\n",
       "      <td>Male</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>United-States</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>Private</td>\n",
       "      <td>338409</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>13</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Prof-specialty</td>\n",
       "      <td>Wife</td>\n",
       "      <td>Black</td>\n",
       "      <td>Female</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>Cuba</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   age         workclass  fnlwgt  education  education-num  \\\n",
       "0   39         State-gov   77516  Bachelors             13   \n",
       "1   50  Self-emp-not-inc   83311  Bachelors             13   \n",
       "2   38           Private  215646    HS-grad              9   \n",
       "3   53           Private  234721       11th              7   \n",
       "4   28           Private  338409  Bachelors             13   \n",
       "\n",
       "       marital-status         occupation   relationship   race     sex  \\\n",
       "0       Never-married       Adm-clerical  Not-in-family  White    Male   \n",
       "1  Married-civ-spouse    Exec-managerial        Husband  White    Male   \n",
       "2            Divorced  Handlers-cleaners  Not-in-family  White    Male   \n",
       "3  Married-civ-spouse  Handlers-cleaners        Husband  Black    Male   \n",
       "4  Married-civ-spouse     Prof-specialty           Wife  Black  Female   \n",
       "\n",
       "   capital-gain  capital-loss  hours-per-week native-country salary  \n",
       "0          2174             0              40  United-States  <=50K  \n",
       "1             0             0              13  United-States  <=50K  \n",
       "2             0             0              40  United-States  <=50K  \n",
       "3             0             0              40  United-States  <=50K  \n",
       "4             0             0              40           Cuba  <=50K  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 32561 entries, 0 to 32560\n",
      "Data columns (total 15 columns):\n",
      "age               32561 non-null int64\n",
      "workclass         32561 non-null object\n",
      "fnlwgt            32561 non-null int64\n",
      "education         32561 non-null object\n",
      "education-num     32561 non-null int64\n",
      "marital-status    32561 non-null object\n",
      "occupation        32561 non-null object\n",
      "relationship      32561 non-null object\n",
      "race              32561 non-null object\n",
      "sex               32561 non-null object\n",
      "capital-gain      32561 non-null int64\n",
      "capital-loss      32561 non-null int64\n",
      "hours-per-week    32561 non-null int64\n",
      "native-country    32561 non-null object\n",
      "salary            32561 non-null object\n",
      "dtypes: int64(6), object(9)\n",
      "memory usage: 3.7+ MB\n"
     ]
    }
   ],
   "source": [
    "train_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.25 KB\n"
     ]
    }
   ],
   "source": [
    "item_data_size = train_df[:1].squeeze().nbytes\n",
    "attribute_names_size = sum([len(i) for i in column_names])\n",
    "item_size = item_data_size + attribute_names_size\n",
    "\n",
    "print(round(item_size/1024,2), \"KB\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>workclass</th>\n",
       "      <th>fnlwgt</th>\n",
       "      <th>education</th>\n",
       "      <th>education-num</th>\n",
       "      <th>marital-status</th>\n",
       "      <th>occupation</th>\n",
       "      <th>relationship</th>\n",
       "      <th>race</th>\n",
       "      <th>sex</th>\n",
       "      <th>capital-gain</th>\n",
       "      <th>capital-loss</th>\n",
       "      <th>hours-per-week</th>\n",
       "      <th>native-country</th>\n",
       "      <th>salary</th>\n",
       "      <th>uid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39</td>\n",
       "      <td>State-gov</td>\n",
       "      <td>77516</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>13</td>\n",
       "      <td>Never-married</td>\n",
       "      <td>Adm-clerical</td>\n",
       "      <td>Not-in-family</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>2174</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>United-States</td>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>Self-emp-not-inc</td>\n",
       "      <td>83311</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>13</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Exec-managerial</td>\n",
       "      <td>Husband</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>United-States</td>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38</td>\n",
       "      <td>Private</td>\n",
       "      <td>215646</td>\n",
       "      <td>HS-grad</td>\n",
       "      <td>9</td>\n",
       "      <td>Divorced</td>\n",
       "      <td>Handlers-cleaners</td>\n",
       "      <td>Not-in-family</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>United-States</td>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>53</td>\n",
       "      <td>Private</td>\n",
       "      <td>234721</td>\n",
       "      <td>11th</td>\n",
       "      <td>7</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Handlers-cleaners</td>\n",
       "      <td>Husband</td>\n",
       "      <td>Black</td>\n",
       "      <td>Male</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>United-States</td>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>Private</td>\n",
       "      <td>338409</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>13</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Prof-specialty</td>\n",
       "      <td>Wife</td>\n",
       "      <td>Black</td>\n",
       "      <td>Female</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>Cuba</td>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   age         workclass  fnlwgt  education  education-num  \\\n",
       "0   39         State-gov   77516  Bachelors             13   \n",
       "1   50  Self-emp-not-inc   83311  Bachelors             13   \n",
       "2   38           Private  215646    HS-grad              9   \n",
       "3   53           Private  234721       11th              7   \n",
       "4   28           Private  338409  Bachelors             13   \n",
       "\n",
       "       marital-status         occupation   relationship   race     sex  \\\n",
       "0       Never-married       Adm-clerical  Not-in-family  White    Male   \n",
       "1  Married-civ-spouse    Exec-managerial        Husband  White    Male   \n",
       "2            Divorced  Handlers-cleaners  Not-in-family  White    Male   \n",
       "3  Married-civ-spouse  Handlers-cleaners        Husband  Black    Male   \n",
       "4  Married-civ-spouse     Prof-specialty           Wife  Black  Female   \n",
       "\n",
       "   capital-gain  capital-loss  hours-per-week native-country salary  uid  \n",
       "0          2174             0              40  United-States  <=50K    0  \n",
       "1             0             0              13  United-States  <=50K    1  \n",
       "2             0             0              40  United-States  <=50K    2  \n",
       "3             0             0              40  United-States  <=50K    3  \n",
       "4             0             0              40           Cuba  <=50K    4  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df['uid'] = train_df.index\n",
    "test_df['uid'] = test_df.index\n",
    "\n",
    "train_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Already imported data\n"
     ]
    }
   ],
   "source": [
    "if already_imported:\n",
    "    print(\"Already imported data\")\n",
    "else:\n",
    "    for index, row in train_df.iterrows():\n",
    "        sample = row.squeeze().to_dict()\n",
    "\n",
    "        for key, value in sample.items():\n",
    "            if type(value) is np.int64:\n",
    "                sample[key] = int(sample[key])\n",
    "\n",
    "        census_table.put_item(Item=sample)\n",
    "        print('Put {}'.format(index))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Already imported data\n"
     ]
    }
   ],
   "source": [
    "if already_imported:\n",
    "    print(\"Already imported data\")\n",
    "else:\n",
    "    for index, row in test_df.iterrows():\n",
    "        sample = row.squeeze().to_dict()\n",
    "\n",
    "        for key, value in sample.items():\n",
    "            if type(value) is np.int64:\n",
    "                sample[key] = int(sample[key])\n",
    "\n",
    "        census_test_table.put_item(Item=sample)\n",
    "        print('Put {}'.format(index))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Table': {'AttributeDefinitions': [{'AttributeName': 'uid', 'AttributeType': 'N'}], 'TableName': 'Census', 'KeySchema': [{'AttributeName': 'uid', 'KeyType': 'HASH'}], 'TableStatus': 'ACTIVE', 'CreationDateTime': datetime.datetime(2017, 2, 6, 14, 48, 9, 91000, tzinfo=tzlocal()), 'ProvisionedThroughput': {'LastIncreaseDateTime': datetime.datetime(2017, 2, 6, 16, 1, 28, 553000, tzinfo=tzlocal()), 'LastDecreaseDateTime': datetime.datetime(2017, 2, 6, 17, 6, 17, 966000, tzinfo=tzlocal()), 'NumberOfDecreasesToday': 3, 'ReadCapacityUnits': 3, 'WriteCapacityUnits': 3}, 'TableSizeBytes': 0, 'ItemCount': 0, 'TableArn': 'arn:aws:dynamodb:eu-west-1:107349881872:table/Census'}, 'ResponseMetadata': {'RequestId': 'KC445SRH35CTED4S9J54KBALRBVV4KQNSO5AEMVJF66Q9ASUAAJG', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': 'KC445SRH35CTED4S9J54KBALRBVV4KQNSO5AEMVJF66Q9ASUAAJG', 'x-amz-crc32': '2918135267', 'content-type': 'application/x-amz-json-1.0', 'content-length': '492', 'date': 'Mon, 06 Feb 2017 12:03:23 GMT'}, 'RetryAttempts': 0}}\n",
      "{'Table': {'AttributeDefinitions': [{'AttributeName': 'uid', 'AttributeType': 'N'}], 'TableName': 'CensusTest', 'KeySchema': [{'AttributeName': 'uid', 'KeyType': 'HASH'}], 'TableStatus': 'ACTIVE', 'CreationDateTime': datetime.datetime(2017, 2, 6, 14, 48, 42, 128000, tzinfo=tzlocal()), 'ProvisionedThroughput': {'LastIncreaseDateTime': datetime.datetime(2017, 2, 6, 17, 1, 7, 773000, tzinfo=tzlocal()), 'LastDecreaseDateTime': datetime.datetime(2017, 2, 6, 17, 31, 24, 470000, tzinfo=tzlocal()), 'NumberOfDecreasesToday': 2, 'ReadCapacityUnits': 3, 'WriteCapacityUnits': 3}, 'TableSizeBytes': 0, 'ItemCount': 0, 'TableArn': 'arn:aws:dynamodb:eu-west-1:107349881872:table/CensusTest'}, 'ResponseMetadata': {'RequestId': 'QHDO2H7LMUICMK80GRT63HAIN7VV4KQNSO5AEMVJF66Q9ASUAAJG', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': 'QHDO2H7LMUICMK80GRT63HAIN7VV4KQNSO5AEMVJF66Q9ASUAAJG', 'x-amz-crc32': '2178789439', 'content-type': 'application/x-amz-json-1.0', 'content-length': '499', 'date': 'Mon, 06 Feb 2017 12:03:23 GMT'}, 'RetryAttempts': 0}}\n"
     ]
    }
   ],
   "source": [
    "table_description = client.describe_table(TableName='Census')\n",
    "print(table_description)\n",
    "table_description = client.describe_table(TableName='CensusTest')\n",
    "print(table_description)"
   ]
  }
 ],
 "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.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}