US_State_Data_.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 5,
  "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.8.8"
    },
    "colab": {
      "name": "US State Data .ipynb",
      "provenance": []
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZL3JsneNymMs"
      },
      "source": [
        "Importing libraries "
      ],
      "id": "ZL3JsneNymMs"
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ddxoq-MJyPs9"
      },
      "source": [
        "import numpy as np\n",
        "import pandas as pd"
      ],
      "id": "ddxoq-MJyPs9",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uft18aCeyDo4"
      },
      "source": [
        "##### Loading data"
      ],
      "id": "uft18aCeyDo4"
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "72bf1f48"
      },
      "source": [
        "population = pd.read_csv('/content/state-population.csv')\n",
        "areas = pd.read_csv('/content/state-areas.csv')\n",
        "abbrevs = pd.read_csv('/content/state-abbrevs.csv')"
      ],
      "id": "72bf1f48",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "f80a7663",
        "outputId": "88aa2991-e825-4686-e7f7-94fe4a036355"
      },
      "source": [
        "print(population.head()); print('-'*30); print(areas.head()); print('-'*30); print(abbrevs.head())"
      ],
      "id": "f80a7663",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "  state/region     ages  year  population\n",
            "0           AL  under18  2012   1117489.0\n",
            "1           AL    total  2012   4817528.0\n",
            "2           AL  under18  2010   1130966.0\n",
            "3           AL    total  2010   4785570.0\n",
            "4           AL  under18  2011   1125763.0\n",
            "------------------------------\n",
            "        state  area (sq. mi)\n",
            "0     Alabama          52423\n",
            "1      Alaska         656425\n",
            "2     Arizona         114006\n",
            "3    Arkansas          53182\n",
            "4  California         163707\n",
            "------------------------------\n",
            "        state abbreviation\n",
            "0     Alabama           AL\n",
            "1      Alaska           AK\n",
            "2     Arizona           AZ\n",
            "3    Arkansas           AR\n",
            "4  California           CA\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "p2WZBg3Dxb8f"
      },
      "source": [
        "Task: Rank the states by population density in 2010"
      ],
      "id": "p2WZBg3Dxb8f"
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "13e899c6"
      },
      "source": [
        "merged = pd.merge(population,abbrevs, how = 'outer', left_on = 'state/region',right_on = 'abbreviation').drop('abbreviation',axis=1)"
      ],
      "id": "13e899c6",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "7ea73231",
        "outputId": "0857e8e3-cba8-4e7d-cd44-f3b147c290a2"
      },
      "source": [
        "merged.head()"
      ],
      "id": "7ea73231",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>state/region</th>\n",
              "      <th>ages</th>\n",
              "      <th>year</th>\n",
              "      <th>population</th>\n",
              "      <th>state</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2012</td>\n",
              "      <td>1117489.0</td>\n",
              "      <td>Alabama</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2012</td>\n",
              "      <td>4817528.0</td>\n",
              "      <td>Alabama</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2010</td>\n",
              "      <td>1130966.0</td>\n",
              "      <td>Alabama</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>4785570.0</td>\n",
              "      <td>Alabama</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2011</td>\n",
              "      <td>1125763.0</td>\n",
              "      <td>Alabama</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  state/region     ages  year  population    state\n",
              "0           AL  under18  2012   1117489.0  Alabama\n",
              "1           AL    total  2012   4817528.0  Alabama\n",
              "2           AL  under18  2010   1130966.0  Alabama\n",
              "3           AL    total  2010   4785570.0  Alabama\n",
              "4           AL  under18  2011   1125763.0  Alabama"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "e4b0407d",
        "outputId": "4b3a15d8-366c-4c7b-ad02-eb95fc022189"
      },
      "source": [
        "merged.isnull().head() # returns boolean mask"
      ],
      "id": "e4b0407d",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>state/region</th>\n",
              "      <th>ages</th>\n",
              "      <th>year</th>\n",
              "      <th>population</th>\n",
              "      <th>state</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   state/region   ages   year  population  state\n",
              "0         False  False  False       False  False\n",
              "1         False  False  False       False  False\n",
              "2         False  False  False       False  False\n",
              "3         False  False  False       False  False\n",
              "4         False  False  False       False  False"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "e1c0d4f0",
        "outputId": "2f6429a4-79df-45d2-ffe4-e2fb5a1ec2b2"
      },
      "source": [
        "merged.isnull().any()"
      ],
      "id": "e1c0d4f0",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "state/region    False\n",
              "ages            False\n",
              "year            False\n",
              "population       True\n",
              "state            True\n",
              "dtype: bool"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 677
        },
        "id": "ef3a8e29",
        "outputId": "34dd560e-c841-4424-dc7f-be574ef45804"
      },
      "source": [
        " merged[merged['population'].isnull()]"
      ],
      "id": "ef3a8e29",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>state/region</th>\n",
              "      <th>ages</th>\n",
              "      <th>year</th>\n",
              "      <th>population</th>\n",
              "      <th>state</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2448</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1990</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2449</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1990</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2450</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1991</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2451</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1991</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2452</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1993</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2453</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1993</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2454</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1992</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2455</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1992</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2456</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1994</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2457</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1994</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2458</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1995</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2459</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1995</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2460</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1996</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2461</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1996</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2462</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1998</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2463</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1998</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2464</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1997</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2465</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1997</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2466</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1999</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2467</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1999</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     state/region     ages  year  population state\n",
              "2448           PR  under18  1990         NaN   NaN\n",
              "2449           PR    total  1990         NaN   NaN\n",
              "2450           PR    total  1991         NaN   NaN\n",
              "2451           PR  under18  1991         NaN   NaN\n",
              "2452           PR    total  1993         NaN   NaN\n",
              "2453           PR  under18  1993         NaN   NaN\n",
              "2454           PR  under18  1992         NaN   NaN\n",
              "2455           PR    total  1992         NaN   NaN\n",
              "2456           PR  under18  1994         NaN   NaN\n",
              "2457           PR    total  1994         NaN   NaN\n",
              "2458           PR    total  1995         NaN   NaN\n",
              "2459           PR  under18  1995         NaN   NaN\n",
              "2460           PR  under18  1996         NaN   NaN\n",
              "2461           PR    total  1996         NaN   NaN\n",
              "2462           PR  under18  1998         NaN   NaN\n",
              "2463           PR    total  1998         NaN   NaN\n",
              "2464           PR    total  1997         NaN   NaN\n",
              "2465           PR  under18  1997         NaN   NaN\n",
              "2466           PR    total  1999         NaN   NaN\n",
              "2467           PR  under18  1999         NaN   NaN"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        },
        "id": "1e3c24bc",
        "outputId": "0d69e35f-4757-4586-d059-af787075240e"
      },
      "source": [
        "merged[merged['state'].isnull()]"
      ],
      "id": "1e3c24bc",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>state/region</th>\n",
              "      <th>ages</th>\n",
              "      <th>year</th>\n",
              "      <th>population</th>\n",
              "      <th>state</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2448</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1990</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2449</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1990</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2450</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1991</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2451</th>\n",
              "      <td>PR</td>\n",
              "      <td>under18</td>\n",
              "      <td>1991</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2452</th>\n",
              "      <td>PR</td>\n",
              "      <td>total</td>\n",
              "      <td>1993</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2539</th>\n",
              "      <td>USA</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>309326295.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2540</th>\n",
              "      <td>USA</td>\n",
              "      <td>under18</td>\n",
              "      <td>2011</td>\n",
              "      <td>73902222.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2541</th>\n",
              "      <td>USA</td>\n",
              "      <td>total</td>\n",
              "      <td>2011</td>\n",
              "      <td>311582564.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2542</th>\n",
              "      <td>USA</td>\n",
              "      <td>under18</td>\n",
              "      <td>2012</td>\n",
              "      <td>73708179.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2543</th>\n",
              "      <td>USA</td>\n",
              "      <td>total</td>\n",
              "      <td>2012</td>\n",
              "      <td>313873685.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>96 rows × 5 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "     state/region     ages  year   population state\n",
              "2448           PR  under18  1990          NaN   NaN\n",
              "2449           PR    total  1990          NaN   NaN\n",
              "2450           PR    total  1991          NaN   NaN\n",
              "2451           PR  under18  1991          NaN   NaN\n",
              "2452           PR    total  1993          NaN   NaN\n",
              "...           ...      ...   ...          ...   ...\n",
              "2539          USA    total  2010  309326295.0   NaN\n",
              "2540          USA  under18  2011   73902222.0   NaN\n",
              "2541          USA    total  2011  311582564.0   NaN\n",
              "2542          USA  under18  2012   73708179.0   NaN\n",
              "2543          USA    total  2012  313873685.0   NaN\n",
              "\n",
              "[96 rows x 5 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "c125c0e7",
        "outputId": "7d9e6dd0-dd0d-4782-e06a-40fa7643c6c1"
      },
      "source": [
        "merged[merged['state'].isnull()]['state/region'].unique()"
      ],
      "id": "c125c0e7",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['PR', 'USA'], dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "3ac3cafa",
        "outputId": "35e63dba-821e-4f2c-9e5b-ccbb8d4f9f94"
      },
      "source": [
        "# There are no full statement for USA and PR\n",
        "merged.head()"
      ],
      "id": "3ac3cafa",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>state/region</th>\n",
              "      <th>ages</th>\n",
              "      <th>year</th>\n",
              "      <th>population</th>\n",
              "      <th>state</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2012</td>\n",
              "      <td>1117489.0</td>\n",
              "      <td>Alabama</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2012</td>\n",
              "      <td>4817528.0</td>\n",
              "      <td>Alabama</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2010</td>\n",
              "      <td>1130966.0</td>\n",
              "      <td>Alabama</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>4785570.0</td>\n",
              "      <td>Alabama</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2011</td>\n",
              "      <td>1125763.0</td>\n",
              "      <td>Alabama</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  state/region     ages  year  population    state\n",
              "0           AL  under18  2012   1117489.0  Alabama\n",
              "1           AL    total  2012   4817528.0  Alabama\n",
              "2           AL  under18  2010   1130966.0  Alabama\n",
              "3           AL    total  2010   4785570.0  Alabama\n",
              "4           AL  under18  2011   1125763.0  Alabama"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Hcu5bbV3x1om"
      },
      "source": [
        "Gives the null values of 'PR' states "
      ],
      "id": "Hcu5bbV3x1om"
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0ba8fade",
        "outputId": "e8548766-4013-4794-843a-1a9b0374a77b"
      },
      "source": [
        "merged[merged['state/region']=='PR']['state']"
      ],
      "id": "0ba8fade",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "2448    NaN\n",
              "2449    NaN\n",
              "2450    NaN\n",
              "2451    NaN\n",
              "2452    NaN\n",
              "2453    NaN\n",
              "2454    NaN\n",
              "2455    NaN\n",
              "2456    NaN\n",
              "2457    NaN\n",
              "2458    NaN\n",
              "2459    NaN\n",
              "2460    NaN\n",
              "2461    NaN\n",
              "2462    NaN\n",
              "2463    NaN\n",
              "2464    NaN\n",
              "2465    NaN\n",
              "2466    NaN\n",
              "2467    NaN\n",
              "2468    NaN\n",
              "2469    NaN\n",
              "2470    NaN\n",
              "2471    NaN\n",
              "2472    NaN\n",
              "2473    NaN\n",
              "2474    NaN\n",
              "2475    NaN\n",
              "2476    NaN\n",
              "2477    NaN\n",
              "2478    NaN\n",
              "2479    NaN\n",
              "2480    NaN\n",
              "2481    NaN\n",
              "2482    NaN\n",
              "2483    NaN\n",
              "2484    NaN\n",
              "2485    NaN\n",
              "2486    NaN\n",
              "2487    NaN\n",
              "2488    NaN\n",
              "2489    NaN\n",
              "2490    NaN\n",
              "2491    NaN\n",
              "2492    NaN\n",
              "2493    NaN\n",
              "2494    NaN\n",
              "2495    NaN\n",
              "Name: state, dtype: object"
            ]
          },
          "metadata": {},
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "61058f0d"
      },
      "source": [
        "# Replacing NaN with 'Peurto Rico' in 'PR' state column\n",
        "merged.loc[merged['state/region']=='PR','state']='Peurto Rico'\n",
        "# Replacing NaN with 'United States' in 'USA' state column\n",
        "merged.loc[merged['state/region']=='USA','state']='United States'"
      ],
      "id": "61058f0d",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "74d40a8e",
        "outputId": "4594cf19-1918-430e-f7bb-2794cbf0bd72"
      },
      "source": [
        "merged.isnull().any()"
      ],
      "id": "74d40a8e",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "state/region    False\n",
              "ages            False\n",
              "year            False\n",
              "population       True\n",
              "state           False\n",
              "dtype: bool"
            ]
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GEikFY2sSmq8"
      },
      "source": [
        "There is no null values in state collumn. So we can merge the result with the area data."
      ],
      "id": "GEikFY2sSmq8"
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "GYCOuPxPS0Qq",
        "outputId": "1c85ea25-522a-4014-f133-c4ef908fa565"
      },
      "source": [
        "final = pd.merge(merged, areas, on ='state', how = 'left')\n",
        "final.head()"
      ],
      "id": "GYCOuPxPS0Qq",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>state/region</th>\n",
              "      <th>ages</th>\n",
              "      <th>year</th>\n",
              "      <th>population</th>\n",
              "      <th>state</th>\n",
              "      <th>area (sq. mi)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2012</td>\n",
              "      <td>1117489.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2012</td>\n",
              "      <td>4817528.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2010</td>\n",
              "      <td>1130966.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>4785570.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2011</td>\n",
              "      <td>1125763.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  state/region     ages  year  population    state  area (sq. mi)\n",
              "0           AL  under18  2012   1117489.0  Alabama        52423.0\n",
              "1           AL    total  2012   4817528.0  Alabama        52423.0\n",
              "2           AL  under18  2010   1130966.0  Alabama        52423.0\n",
              "3           AL    total  2010   4785570.0  Alabama        52423.0\n",
              "4           AL  under18  2011   1125763.0  Alabama        52423.0"
            ]
          },
          "metadata": {},
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mQNYWQVhUtxN"
      },
      "source": [
        "Let's check for null to see if there is any mismatches."
      ],
      "id": "mQNYWQVhUtxN"
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yLyxZM2ZTEka",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "84f99998-4a72-4f0a-d083-53b70f52f4a3"
      },
      "source": [
        "final.isnull().any()"
      ],
      "id": "yLyxZM2ZTEka",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "state/region     False\n",
              "ages             False\n",
              "year             False\n",
              "population        True\n",
              "state            False\n",
              "area (sq. mi)     True\n",
              "dtype: bool"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "zKBXxskqkiBK"
      },
      "source": [
        "There are  null in a area columns. Let's see which regions they are."
      ],
      "id": "zKBXxskqkiBK"
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Mig3rcujkekl",
        "outputId": "b92c9588-8a3e-4cbf-cc94-fbdb63401a82"
      },
      "source": [
        "final[final['area (sq. mi)'].isnull()]['state'].unique()"
      ],
      "id": "Mig3rcujkekl",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['Peurto Rico', 'United States'], dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rKGD4ixMm61R"
      },
      "source": [
        "Let's drop those rows with NA's"
      ],
      "id": "rKGD4ixMm61R"
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-Yh71709lQJc",
        "outputId": "b102e5e9-d17f-46c1-d9f1-bb55d0f7249f"
      },
      "source": [
        "final.dropna(inplace=True)\n",
        "final.isnull().any()"
      ],
      "id": "-Yh71709lQJc",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "state/region     False\n",
              "ages             False\n",
              "year             False\n",
              "population       False\n",
              "state            False\n",
              "area (sq. mi)    False\n",
              "dtype: bool"
            ]
          },
          "metadata": {},
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uzKCbGFdohh9"
      },
      "source": [
        "There is no null value."
      ],
      "id": "uzKCbGFdohh9"
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "EyRitYERnjs3",
        "outputId": "75257229-ffea-40c4-8eda-59185a26af9f"
      },
      "source": [
        "final.shape "
      ],
      "id": "EyRitYERnjs3",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(2448, 6)"
            ]
          },
          "metadata": {},
          "execution_count": 19
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "UDkD6w5Fonf8",
        "outputId": "4eb78c86-3264-49e1-f0ee-724108740f42"
      },
      "source": [
        "final.head()"
      ],
      "id": "UDkD6w5Fonf8",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>state/region</th>\n",
              "      <th>ages</th>\n",
              "      <th>year</th>\n",
              "      <th>population</th>\n",
              "      <th>state</th>\n",
              "      <th>area (sq. mi)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2012</td>\n",
              "      <td>1117489.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2012</td>\n",
              "      <td>4817528.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2010</td>\n",
              "      <td>1130966.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>4785570.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2011</td>\n",
              "      <td>1125763.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  state/region     ages  year  population    state  area (sq. mi)\n",
              "0           AL  under18  2012   1117489.0  Alabama        52423.0\n",
              "1           AL    total  2012   4817528.0  Alabama        52423.0\n",
              "2           AL  under18  2010   1130966.0  Alabama        52423.0\n",
              "3           AL    total  2010   4785570.0  Alabama        52423.0\n",
              "4           AL  under18  2011   1125763.0  Alabama        52423.0"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "xoDBHLIgopkM",
        "outputId": "90c5af59-235e-4808-c232-e475f2df5545"
      },
      "source": [
        "final['state'].unique()"
      ],
      "id": "xoDBHLIgopkM",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array(['Alabama', 'Alaska', 'Arizona', 'Arkansas', 'California',\n",
              "       'Colorado', 'Connecticut', 'Delaware', 'District of Columbia',\n",
              "       'Florida', 'Georgia', 'Hawaii', 'Idaho', 'Illinois', 'Indiana',\n",
              "       'Iowa', 'Kansas', 'Kentucky', 'Louisiana', 'Maine', 'Maryland',\n",
              "       'Massachusetts', 'Michigan', 'Minnesota', 'Mississippi',\n",
              "       'Missouri', 'Montana', 'Nebraska', 'Nevada', 'New Hampshire',\n",
              "       'New Jersey', 'New Mexico', 'New York', 'North Carolina',\n",
              "       'North Dakota', 'Ohio', 'Oklahoma', 'Oregon', 'Pennsylvania',\n",
              "       'Rhode Island', 'South Carolina', 'South Dakota', 'Tennessee',\n",
              "       'Texas', 'Utah', 'Vermont', 'Virginia', 'Washington',\n",
              "       'West Virginia', 'Wisconsin', 'Wyoming'], dtype=object)"
            ]
          },
          "metadata": {},
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Nhcy1E74ouBM",
        "outputId": "eba870d0-b576-452c-86cc-decdc14c8449"
      },
      "source": [
        "len(final['state'].unique())"
      ],
      "id": "Nhcy1E74ouBM",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "51"
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LIMLe1OApF6m"
      },
      "source": [
        "We want to get the population density. Here, population column contain the population of Under 18 and total population. so creating new dataframe selecting total population."
      ],
      "id": "LIMLe1OApF6m"
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "5ID2z-Ylq3RZ",
        "outputId": "2d8e1bad-82dd-44c6-dccf-a3d96d8585ab"
      },
      "source": [
        "final.head()"
      ],
      "id": "5ID2z-Ylq3RZ",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>state/region</th>\n",
              "      <th>ages</th>\n",
              "      <th>year</th>\n",
              "      <th>population</th>\n",
              "      <th>state</th>\n",
              "      <th>area (sq. mi)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2012</td>\n",
              "      <td>1117489.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2012</td>\n",
              "      <td>4817528.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2010</td>\n",
              "      <td>1130966.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>4785570.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AL</td>\n",
              "      <td>under18</td>\n",
              "      <td>2011</td>\n",
              "      <td>1125763.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  state/region     ages  year  population    state  area (sq. mi)\n",
              "0           AL  under18  2012   1117489.0  Alabama        52423.0\n",
              "1           AL    total  2012   4817528.0  Alabama        52423.0\n",
              "2           AL  under18  2010   1130966.0  Alabama        52423.0\n",
              "3           AL    total  2010   4785570.0  Alabama        52423.0\n",
              "4           AL  under18  2011   1125763.0  Alabama        52423.0"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FckdwjimpB1d"
      },
      "source": [
        "data2010 = final.query(\"year == 2010 & ages == 'total'\") # selecting data with year 2010 and ages 'total' ."
      ],
      "id": "FckdwjimpB1d",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "MsCjQHNZrKDf",
        "outputId": "16294f8c-5dc6-42de-dc40-6f44d16eaf0b"
      },
      "source": [
        "data2010.head()"
      ],
      "id": "MsCjQHNZrKDf",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>state/region</th>\n",
              "      <th>ages</th>\n",
              "      <th>year</th>\n",
              "      <th>population</th>\n",
              "      <th>state</th>\n",
              "      <th>area (sq. mi)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>4785570.0</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>91</th>\n",
              "      <td>AK</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>713868.0</td>\n",
              "      <td>Alaska</td>\n",
              "      <td>656425.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>101</th>\n",
              "      <td>AZ</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>6408790.0</td>\n",
              "      <td>Arizona</td>\n",
              "      <td>114006.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>189</th>\n",
              "      <td>AR</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>2922280.0</td>\n",
              "      <td>Arkansas</td>\n",
              "      <td>53182.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>197</th>\n",
              "      <td>CA</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>37333601.0</td>\n",
              "      <td>California</td>\n",
              "      <td>163707.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    state/region   ages  year  population       state  area (sq. mi)\n",
              "3             AL  total  2010   4785570.0     Alabama        52423.0\n",
              "91            AK  total  2010    713868.0      Alaska       656425.0\n",
              "101           AZ  total  2010   6408790.0     Arizona       114006.0\n",
              "189           AR  total  2010   2922280.0    Arkansas        53182.0\n",
              "197           CA  total  2010  37333601.0  California       163707.0"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jJVjR19BstSW"
      },
      "source": [
        "data2010.set_index('state',inplace=True)"
      ],
      "id": "jJVjR19BstSW",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 237
        },
        "id": "XlX6j7xnuiex",
        "outputId": "1698caec-0576-42ae-d408-8a971b563f61"
      },
      "source": [
        "data2010.head()"
      ],
      "id": "XlX6j7xnuiex",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>state/region</th>\n",
              "      <th>ages</th>\n",
              "      <th>year</th>\n",
              "      <th>population</th>\n",
              "      <th>area (sq. mi)</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>state</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>Alabama</th>\n",
              "      <td>AL</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>4785570.0</td>\n",
              "      <td>52423.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Alaska</th>\n",
              "      <td>AK</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>713868.0</td>\n",
              "      <td>656425.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Arizona</th>\n",
              "      <td>AZ</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>6408790.0</td>\n",
              "      <td>114006.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>Arkansas</th>\n",
              "      <td>AR</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>2922280.0</td>\n",
              "      <td>53182.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>California</th>\n",
              "      <td>CA</td>\n",
              "      <td>total</td>\n",
              "      <td>2010</td>\n",
              "      <td>37333601.0</td>\n",
              "      <td>163707.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "           state/region   ages  year  population  area (sq. mi)\n",
              "state                                                          \n",
              "Alabama              AL  total  2010   4785570.0        52423.0\n",
              "Alaska               AK  total  2010    713868.0       656425.0\n",
              "Arizona              AZ  total  2010   6408790.0       114006.0\n",
              "Arkansas             AR  total  2010   2922280.0        53182.0\n",
              "California           CA  total  2010  37333601.0       163707.0"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Lvrwbw4pv9zd"
      },
      "source": [
        "density = data2010['population']/data2010['area (sq. mi)']"
      ],
      "id": "Lvrwbw4pv9zd",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "t0uSS93HwrOo",
        "outputId": "edcbd826-d632-4349-92f2-4941885e2b6a"
      },
      "source": [
        "type(density)"
      ],
      "id": "t0uSS93HwrOo",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "pandas.core.series.Series"
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qFZq19JHwNm1",
        "outputId": "88e1dd8b-7073-42ba-e278-566ec8d9adb1"
      },
      "source": [
        "density.head()"
      ],
      "id": "qFZq19JHwNm1",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "state\n",
              "Alabama        91.287603\n",
              "Alaska          1.087509\n",
              "Arizona        56.214497\n",
              "Arkansas       54.948667\n",
              "California    228.051342\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 30
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kDBmlPjYwT9I"
      },
      "source": [
        "Sorting the density for ranking from density in decending order."
      ],
      "id": "kDBmlPjYwT9I"
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FPax0I4DwSwm"
      },
      "source": [
        "density.sort_values(ascending=False, inplace = True)"
      ],
      "id": "FPax0I4DwSwm",
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "B0FjCttU5Wbj",
        "outputId": "3cd2f69b-c4bf-4756-d17b-0a80a6eb068b"
      },
      "source": [
        "density.head()"
      ],
      "id": "B0FjCttU5Wbj",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "state\n",
              "District of Columbia    8898.897059\n",
              "New Jersey              1009.253268\n",
              "Rhode Island             681.339159\n",
              "Connecticut              645.600649\n",
              "Massachusetts            621.815538\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 32
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AUZM9tJQxB8e",
        "outputId": "7c799273-e662-4a64-bf2b-a7872e3f13d3"
      },
      "source": [
        "density.head(10)"
      ],
      "id": "AUZM9tJQxB8e",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "state\n",
              "District of Columbia    8898.897059\n",
              "New Jersey              1009.253268\n",
              "Rhode Island             681.339159\n",
              "Connecticut              645.600649\n",
              "Massachusetts            621.815538\n",
              "Maryland                 466.445797\n",
              "Delaware                 460.445752\n",
              "New York                 356.094135\n",
              "Florida                  286.597129\n",
              "Pennsylvania             275.966651\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wLpALA2JxEbe",
        "outputId": "285233be-fb5a-4ff2-fe68-761745b441f3"
      },
      "source": [
        "density.tail(10)"
      ],
      "id": "wLpALA2JxEbe",
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "state\n",
              "Utah            32.677188\n",
              "Nevada          24.448796\n",
              "Nebraska        23.654153\n",
              "Idaho           18.794338\n",
              "New Mexico      16.982737\n",
              "South Dakota    10.583512\n",
              "North Dakota     9.537565\n",
              "Montana          6.736171\n",
              "Wyoming          5.768079\n",
              "Alaska           1.087509\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 34
        }
      ]
    }
  ]
}