10.人口分析案例(1).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 需求：\n",
    "    - 导入文件，查看原始数据\n",
    "    - 将人口数据和各州简称数据进行合并\n",
    "    - 将合并的数据中重复的abbreviation列进行删除\n",
    "    - 查看存在缺失数据的列\n",
    "    - 找到有哪些state/region使得state的值为NaN，进行去重操作\n",
    "    - 为找到的这些state/region的state项补上正确的值，从而去除掉state这一列的所有NaN\n",
    "    - 合并各州面积数据areas\n",
    "    - 我们会发现area(sq.mi)这一列有缺失数据，找出是哪些行\n",
    "    - 去除含有缺失数据的行\n",
    "    - 找出2010年的全民人口数据\n",
    "    - 计算各州的人口密度\n",
    "    - 排序，并找出人口密度最高的州"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state abbreviation\n",
       "0  Alabama           AL\n",
       "1   Alaska           AK\n",
       "2  Arizona           AZ"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "abb = pd.read_csv('./data/state-abbrevs.csv')\n",
    "abb.head(3)#州的全称和简称"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>state/region</th>\n",
       "      <th>ages</th>\n",
       "      <th>year</th>\n",
       "      <th>population</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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  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"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop = pd.read_csv('./data/state-population.csv')\n",
    "pop.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>state</th>\n",
       "      <th>area (sq. mi)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>52423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>656425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>114006</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state  area (sq. mi)\n",
       "0  Alabama          52423\n",
       "1   Alaska         656425\n",
       "2  Arizona         114006"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "area = pd.read_csv('./data/state-areas.csv')\n",
    "area.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>state</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>state/region</th>\n",
       "      <th>ages</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2012</td>\n",
       "      <td>1117489.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2012</td>\n",
       "      <td>4817528.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2010</td>\n",
       "      <td>1130966.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>4785570.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2011</td>\n",
       "      <td>1125763.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>309326295.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2540</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>under18</td>\n",
       "      <td>2011</td>\n",
       "      <td>73902222.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2541</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>total</td>\n",
       "      <td>2011</td>\n",
       "      <td>311582564.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2542</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>under18</td>\n",
       "      <td>2012</td>\n",
       "      <td>73708179.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2543</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>total</td>\n",
       "      <td>2012</td>\n",
       "      <td>313873685.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2544 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        state abbreviation state/region     ages  year   population\n",
       "0     Alabama           AL           AL  under18  2012    1117489.0\n",
       "1     Alabama           AL           AL    total  2012    4817528.0\n",
       "2     Alabama           AL           AL  under18  2010    1130966.0\n",
       "3     Alabama           AL           AL    total  2010    4785570.0\n",
       "4     Alabama           AL           AL  under18  2011    1125763.0\n",
       "...       ...          ...          ...      ...   ...          ...\n",
       "2539      NaN          NaN          USA    total  2010  309326295.0\n",
       "2540      NaN          NaN          USA  under18  2011   73902222.0\n",
       "2541      NaN          NaN          USA    total  2011  311582564.0\n",
       "2542      NaN          NaN          USA  under18  2012   73708179.0\n",
       "2543      NaN          NaN          USA    total  2012  313873685.0\n",
       "\n",
       "[2544 rows x 6 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#将人口数据和各州简称数据进行合并\n",
    "abb_pop = pd.merge(abb,pop,left_on='abbreviation',right_on='state/region',how='outer')\n",
    "abb_pop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "abb_pop.drop(labels='abbreviation',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state            True\n",
       "state/region    False\n",
       "ages            False\n",
       "year            False\n",
       "population       True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看存在缺失数据的列\n",
    "abb_pop.isnull().any(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>state</th>\n",
       "      <th>state/region</th>\n",
       "      <th>ages</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2448</th>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>under18</td>\n",
       "      <td>1990</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2449</th>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>total</td>\n",
       "      <td>1990</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2450</th>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>total</td>\n",
       "      <td>1991</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2451</th>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>under18</td>\n",
       "      <td>1991</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2452</th>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>total</td>\n",
       "      <td>1993</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>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>309326295.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2540</th>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>under18</td>\n",
       "      <td>2011</td>\n",
       "      <td>73902222.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2541</th>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>total</td>\n",
       "      <td>2011</td>\n",
       "      <td>311582564.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2542</th>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>under18</td>\n",
       "      <td>2012</td>\n",
       "      <td>73708179.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2543</th>\n",
       "      <td>NaN</td>\n",
       "      <td>USA</td>\n",
       "      <td>total</td>\n",
       "      <td>2012</td>\n",
       "      <td>313873685.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>96 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     state state/region     ages  year   population\n",
       "2448   NaN           PR  under18  1990          NaN\n",
       "2449   NaN           PR    total  1990          NaN\n",
       "2450   NaN           PR    total  1991          NaN\n",
       "2451   NaN           PR  under18  1991          NaN\n",
       "2452   NaN           PR    total  1993          NaN\n",
       "...    ...          ...      ...   ...          ...\n",
       "2539   NaN          USA    total  2010  309326295.0\n",
       "2540   NaN          USA  under18  2011   73902222.0\n",
       "2541   NaN          USA    total  2011  311582564.0\n",
       "2542   NaN          USA  under18  2012   73708179.0\n",
       "2543   NaN          USA    total  2012  313873685.0\n",
       "\n",
       "[96 rows x 5 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#找到有哪些state/region使得state的值为NaN，进行去重操作\n",
    "abb_pop.loc[abb_pop['state'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['PR', 'USA'], dtype=object)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "abb_pop.loc[abb_pop['state'].isnull()]['state/region'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#给state中存在的空值补上正确的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#给PR简称对应的全称补上正确的值\n",
    "abb_pop['state/region'] == 'PR'\n",
    "abb_pop.loc[abb_pop['state/region'] == 'PR'] #将PR的行数据取出\n",
    "PR_row_index = abb_pop.loc[abb_pop['state/region'] == 'PR'].index\n",
    "abb_pop.loc[PR_row_index,'state'] = 'PPPRRR' #批量填充"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#给USA简称对应的全称补上正确的值\n",
    "abb_pop['state/region'] == 'USA'\n",
    "abb_pop.loc[abb_pop['state/region'] == 'USA'] #取出的是USA对应的行数据\n",
    "USA_row_index = abb_pop.loc[abb_pop['state/region'] == 'USA'].index\n",
    "\n",
    "abb_pop.loc[USA_row_index,'state'] = 'United States'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>state</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",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>1117489.0</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>4817528.0</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>1130966.0</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>4785570.0</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1125763.0</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state state/region     ages    year  population  area (sq. mi)\n",
       "0  Alabama           AL  under18  2012.0   1117489.0        52423.0\n",
       "1  Alabama           AL    total  2012.0   4817528.0        52423.0\n",
       "2  Alabama           AL  under18  2010.0   1130966.0        52423.0\n",
       "3  Alabama           AL    total  2010.0   4785570.0        52423.0\n",
       "4  Alabama           AL  under18  2011.0   1125763.0        52423.0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "abb_pop_area = pd.merge(abb_pop,area,how='outer')\n",
    "abb_pop_area.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#我们会发现area(sq.mi)这一列有缺失数据，找出是哪些行\n",
    "abb_pop_area['area (sq. mi)'].isnull()\n",
    "abb_pop_area.loc[abb_pop_area['area (sq. mi)'].isnull()]\n",
    "drop_index = abb_pop_area.loc[abb_pop_area['area (sq. mi)'].isnull()].index "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "abb_pop_area.drop(labels=drop_index,axis=0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state            False\n",
       "state/region      True\n",
       "ages              True\n",
       "year              True\n",
       "population        True\n",
       "area (sq. mi)    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#检测目前哪些列中存在缺失值\n",
    "abb_pop_area.isnull().any(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in abb_pop_area.columns:\n",
    "    if abb_pop_area[col].isnull().sum() > 0:\n",
    "        drop_index = abb_pop_area.loc[abb_pop_area[col].isnull()].index\n",
    "        abb_pop_area.drop(labels=drop_index,axis=0,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state            False\n",
       "state/region     False\n",
       "ages             False\n",
       "year             False\n",
       "population       False\n",
       "area (sq. mi)    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "abb_pop_area.isnull().any(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": true
   },
   "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>state</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",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>4785570.0</td>\n",
       "      <td>52423.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>713868.0</td>\n",
       "      <td>656425.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>6408790.0</td>\n",
       "      <td>114006.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2922280.0</td>\n",
       "      <td>53182.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>37333601.0</td>\n",
       "      <td>163707.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>5048196.0</td>\n",
       "      <td>104100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>293</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>CT</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>3579210.0</td>\n",
       "      <td>5544.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>379</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>DE</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>899711.0</td>\n",
       "      <td>1954.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>DC</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>605125.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>Florida</td>\n",
       "      <td>FL</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>18846054.0</td>\n",
       "      <td>65758.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>485</th>\n",
       "      <td>Georgia</td>\n",
       "      <td>GA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>9713248.0</td>\n",
       "      <td>59441.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>570</th>\n",
       "      <td>Hawaii</td>\n",
       "      <td>HI</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>1363731.0</td>\n",
       "      <td>10932.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>581</th>\n",
       "      <td>Idaho</td>\n",
       "      <td>ID</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>1570718.0</td>\n",
       "      <td>83574.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>666</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>IL</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>12839695.0</td>\n",
       "      <td>57918.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>677</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>IN</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>6489965.0</td>\n",
       "      <td>36420.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>762</th>\n",
       "      <td>Iowa</td>\n",
       "      <td>IA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>3050314.0</td>\n",
       "      <td>56276.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>773</th>\n",
       "      <td>Kansas</td>\n",
       "      <td>KS</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2858910.0</td>\n",
       "      <td>82282.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>858</th>\n",
       "      <td>Kentucky</td>\n",
       "      <td>KY</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>4347698.0</td>\n",
       "      <td>40411.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>869</th>\n",
       "      <td>Louisiana</td>\n",
       "      <td>LA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>4545392.0</td>\n",
       "      <td>51843.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>954</th>\n",
       "      <td>Maine</td>\n",
       "      <td>ME</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>1327366.0</td>\n",
       "      <td>35387.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>965</th>\n",
       "      <td>Montana</td>\n",
       "      <td>MT</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>990527.0</td>\n",
       "      <td>147046.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1050</th>\n",
       "      <td>Nebraska</td>\n",
       "      <td>NE</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>1829838.0</td>\n",
       "      <td>77358.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1061</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>NV</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2703230.0</td>\n",
       "      <td>110567.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1146</th>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>NH</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>1316614.0</td>\n",
       "      <td>9351.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1157</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>NJ</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>8802707.0</td>\n",
       "      <td>8722.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1242</th>\n",
       "      <td>New Mexico</td>\n",
       "      <td>NM</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2064982.0</td>\n",
       "      <td>121593.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1253</th>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>19398228.0</td>\n",
       "      <td>54475.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1338</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>NC</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>9559533.0</td>\n",
       "      <td>53821.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1349</th>\n",
       "      <td>North Dakota</td>\n",
       "      <td>ND</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>674344.0</td>\n",
       "      <td>70704.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1434</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>OH</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>11545435.0</td>\n",
       "      <td>44828.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1445</th>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>OK</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>3759263.0</td>\n",
       "      <td>69903.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1530</th>\n",
       "      <td>Oregon</td>\n",
       "      <td>OR</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>3837208.0</td>\n",
       "      <td>98386.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1541</th>\n",
       "      <td>Maryland</td>\n",
       "      <td>MD</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>5787193.0</td>\n",
       "      <td>12407.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1626</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>MA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>6563263.0</td>\n",
       "      <td>10555.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1637</th>\n",
       "      <td>Michigan</td>\n",
       "      <td>MI</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>9876149.0</td>\n",
       "      <td>96810.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1722</th>\n",
       "      <td>Minnesota</td>\n",
       "      <td>MN</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>5310337.0</td>\n",
       "      <td>86943.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1733</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>MS</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2970047.0</td>\n",
       "      <td>48434.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1818</th>\n",
       "      <td>Missouri</td>\n",
       "      <td>MO</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>5996063.0</td>\n",
       "      <td>69709.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1829</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>PA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>12710472.0</td>\n",
       "      <td>46058.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1914</th>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>RI</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>1052669.0</td>\n",
       "      <td>1545.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1925</th>\n",
       "      <td>South Carolina</td>\n",
       "      <td>SC</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>4636361.0</td>\n",
       "      <td>32007.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>South Dakota</td>\n",
       "      <td>SD</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>816211.0</td>\n",
       "      <td>77121.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>TN</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>6356683.0</td>\n",
       "      <td>42146.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2106</th>\n",
       "      <td>Texas</td>\n",
       "      <td>TX</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>25245178.0</td>\n",
       "      <td>268601.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2117</th>\n",
       "      <td>Utah</td>\n",
       "      <td>UT</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>2774424.0</td>\n",
       "      <td>84904.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2202</th>\n",
       "      <td>Vermont</td>\n",
       "      <td>VT</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>625793.0</td>\n",
       "      <td>9615.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2213</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>VA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>8024417.0</td>\n",
       "      <td>42769.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2298</th>\n",
       "      <td>Washington</td>\n",
       "      <td>WA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>6742256.0</td>\n",
       "      <td>71303.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2309</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>WV</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>1854146.0</td>\n",
       "      <td>24231.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2394</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>WI</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>5689060.0</td>\n",
       "      <td>65503.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2405</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>564222.0</td>\n",
       "      <td>97818.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     state state/region   ages    year  population  \\\n",
       "3                  Alabama           AL  total  2010.0   4785570.0   \n",
       "91                  Alaska           AK  total  2010.0    713868.0   \n",
       "101                Arizona           AZ  total  2010.0   6408790.0   \n",
       "189               Arkansas           AR  total  2010.0   2922280.0   \n",
       "197             California           CA  total  2010.0  37333601.0   \n",
       "283               Colorado           CO  total  2010.0   5048196.0   \n",
       "293            Connecticut           CT  total  2010.0   3579210.0   \n",
       "379               Delaware           DE  total  2010.0    899711.0   \n",
       "389   District of Columbia           DC  total  2010.0    605125.0   \n",
       "475                Florida           FL  total  2010.0  18846054.0   \n",
       "485                Georgia           GA  total  2010.0   9713248.0   \n",
       "570                 Hawaii           HI  total  2010.0   1363731.0   \n",
       "581                  Idaho           ID  total  2010.0   1570718.0   \n",
       "666               Illinois           IL  total  2010.0  12839695.0   \n",
       "677                Indiana           IN  total  2010.0   6489965.0   \n",
       "762                   Iowa           IA  total  2010.0   3050314.0   \n",
       "773                 Kansas           KS  total  2010.0   2858910.0   \n",
       "858               Kentucky           KY  total  2010.0   4347698.0   \n",
       "869              Louisiana           LA  total  2010.0   4545392.0   \n",
       "954                  Maine           ME  total  2010.0   1327366.0   \n",
       "965                Montana           MT  total  2010.0    990527.0   \n",
       "1050              Nebraska           NE  total  2010.0   1829838.0   \n",
       "1061                Nevada           NV  total  2010.0   2703230.0   \n",
       "1146         New Hampshire           NH  total  2010.0   1316614.0   \n",
       "1157            New Jersey           NJ  total  2010.0   8802707.0   \n",
       "1242            New Mexico           NM  total  2010.0   2064982.0   \n",
       "1253              New York           NY  total  2010.0  19398228.0   \n",
       "1338        North Carolina           NC  total  2010.0   9559533.0   \n",
       "1349          North Dakota           ND  total  2010.0    674344.0   \n",
       "1434                  Ohio           OH  total  2010.0  11545435.0   \n",
       "1445              Oklahoma           OK  total  2010.0   3759263.0   \n",
       "1530                Oregon           OR  total  2010.0   3837208.0   \n",
       "1541              Maryland           MD  total  2010.0   5787193.0   \n",
       "1626         Massachusetts           MA  total  2010.0   6563263.0   \n",
       "1637              Michigan           MI  total  2010.0   9876149.0   \n",
       "1722             Minnesota           MN  total  2010.0   5310337.0   \n",
       "1733           Mississippi           MS  total  2010.0   2970047.0   \n",
       "1818              Missouri           MO  total  2010.0   5996063.0   \n",
       "1829          Pennsylvania           PA  total  2010.0  12710472.0   \n",
       "1914          Rhode Island           RI  total  2010.0   1052669.0   \n",
       "1925        South Carolina           SC  total  2010.0   4636361.0   \n",
       "2010          South Dakota           SD  total  2010.0    816211.0   \n",
       "2021             Tennessee           TN  total  2010.0   6356683.0   \n",
       "2106                 Texas           TX  total  2010.0  25245178.0   \n",
       "2117                  Utah           UT  total  2010.0   2774424.0   \n",
       "2202               Vermont           VT  total  2010.0    625793.0   \n",
       "2213              Virginia           VA  total  2010.0   8024417.0   \n",
       "2298            Washington           WA  total  2010.0   6742256.0   \n",
       "2309         West Virginia           WV  total  2010.0   1854146.0   \n",
       "2394             Wisconsin           WI  total  2010.0   5689060.0   \n",
       "2405               Wyoming           WY  total  2010.0    564222.0   \n",
       "\n",
       "      area (sq. mi)  \n",
       "3           52423.0  \n",
       "91         656425.0  \n",
       "101        114006.0  \n",
       "189         53182.0  \n",
       "197        163707.0  \n",
       "283        104100.0  \n",
       "293          5544.0  \n",
       "379          1954.0  \n",
       "389            68.0  \n",
       "475         65758.0  \n",
       "485         59441.0  \n",
       "570         10932.0  \n",
       "581         83574.0  \n",
       "666         57918.0  \n",
       "677         36420.0  \n",
       "762         56276.0  \n",
       "773         82282.0  \n",
       "858         40411.0  \n",
       "869         51843.0  \n",
       "954         35387.0  \n",
       "965        147046.0  \n",
       "1050        77358.0  \n",
       "1061       110567.0  \n",
       "1146         9351.0  \n",
       "1157         8722.0  \n",
       "1242       121593.0  \n",
       "1253        54475.0  \n",
       "1338        53821.0  \n",
       "1349        70704.0  \n",
       "1434        44828.0  \n",
       "1445        69903.0  \n",
       "1530        98386.0  \n",
       "1541        12407.0  \n",
       "1626        10555.0  \n",
       "1637        96810.0  \n",
       "1722        86943.0  \n",
       "1733        48434.0  \n",
       "1818        69709.0  \n",
       "1829        46058.0  \n",
       "1914         1545.0  \n",
       "1925        32007.0  \n",
       "2010        77121.0  \n",
       "2021        42146.0  \n",
       "2106       268601.0  \n",
       "2117        84904.0  \n",
       "2202         9615.0  \n",
       "2213        42769.0  \n",
       "2298        71303.0  \n",
       "2309        24231.0  \n",
       "2394        65503.0  \n",
       "2405        97818.0  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#找出2010年的全民人口数据\n",
    "abb_pop_area.query('year==2010 & ages==\"total\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>state</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",
       "      <th>midu</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>1117489.0</td>\n",
       "      <td>52423.0</td>\n",
       "      <td>21.316769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>4817528.0</td>\n",
       "      <td>52423.0</td>\n",
       "      <td>91.897221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>1130966.0</td>\n",
       "      <td>52423.0</td>\n",
       "      <td>21.573851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>4785570.0</td>\n",
       "      <td>52423.0</td>\n",
       "      <td>91.287603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>1125763.0</td>\n",
       "      <td>52423.0</td>\n",
       "      <td>21.474601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2443</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>under18</td>\n",
       "      <td>1993.0</td>\n",
       "      <td>137458.0</td>\n",
       "      <td>97818.0</td>\n",
       "      <td>1.405242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2444</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>total</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>459260.0</td>\n",
       "      <td>97818.0</td>\n",
       "      <td>4.695046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2445</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>under18</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>136720.0</td>\n",
       "      <td>97818.0</td>\n",
       "      <td>1.397698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2446</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>under18</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>136078.0</td>\n",
       "      <td>97818.0</td>\n",
       "      <td>1.391135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2447</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>total</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>453690.0</td>\n",
       "      <td>97818.0</td>\n",
       "      <td>4.638103</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2448 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        state state/region     ages    year  population  area (sq. mi)  \\\n",
       "0     Alabama           AL  under18  2012.0   1117489.0        52423.0   \n",
       "1     Alabama           AL    total  2012.0   4817528.0        52423.0   \n",
       "2     Alabama           AL  under18  2010.0   1130966.0        52423.0   \n",
       "3     Alabama           AL    total  2010.0   4785570.0        52423.0   \n",
       "4     Alabama           AL  under18  2011.0   1125763.0        52423.0   \n",
       "...       ...          ...      ...     ...         ...            ...   \n",
       "2443  Wyoming           WY  under18  1993.0    137458.0        97818.0   \n",
       "2444  Wyoming           WY    total  1991.0    459260.0        97818.0   \n",
       "2445  Wyoming           WY  under18  1991.0    136720.0        97818.0   \n",
       "2446  Wyoming           WY  under18  1990.0    136078.0        97818.0   \n",
       "2447  Wyoming           WY    total  1990.0    453690.0        97818.0   \n",
       "\n",
       "           midu  \n",
       "0     21.316769  \n",
       "1     91.897221  \n",
       "2     21.573851  \n",
       "3     91.287603  \n",
       "4     21.474601  \n",
       "...         ...  \n",
       "2443   1.405242  \n",
       "2444   4.695046  \n",
       "2445   1.397698  \n",
       "2446   1.391135  \n",
       "2447   4.638103  \n",
       "\n",
       "[2448 rows x 7 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#计算各州的人口密度\n",
    "abb_pop_area['midu'] = abb_pop_area['population']/abb_pop_area['area (sq. mi)']\n",
    "abb_pop_area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>state</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",
       "      <th>midu</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>391</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>DC</td>\n",
       "      <td>total</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>646449.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>9506.602941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>DC</td>\n",
       "      <td>total</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>633427.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>9315.102941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>387</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>DC</td>\n",
       "      <td>total</td>\n",
       "      <td>2011.0</td>\n",
       "      <td>619624.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>9112.117647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>431</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>DC</td>\n",
       "      <td>total</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>605321.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>8901.779412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>DC</td>\n",
       "      <td>total</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>605125.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>8898.897059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>under18</td>\n",
       "      <td>1992.0</td>\n",
       "      <td>184878.0</td>\n",
       "      <td>656425.0</td>\n",
       "      <td>0.281644</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>under18</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>184344.0</td>\n",
       "      <td>656425.0</td>\n",
       "      <td>0.280830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>under18</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>183124.0</td>\n",
       "      <td>656425.0</td>\n",
       "      <td>0.278972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>under18</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>182180.0</td>\n",
       "      <td>656425.0</td>\n",
       "      <td>0.277534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>under18</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>177502.0</td>\n",
       "      <td>656425.0</td>\n",
       "      <td>0.270407</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2448 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    state state/region     ages    year  population  \\\n",
       "391  District of Columbia           DC    total  2013.0    646449.0   \n",
       "385  District of Columbia           DC    total  2012.0    633427.0   \n",
       "387  District of Columbia           DC    total  2011.0    619624.0   \n",
       "431  District of Columbia           DC    total  1990.0    605321.0   \n",
       "389  District of Columbia           DC    total  2010.0    605125.0   \n",
       "..                    ...          ...      ...     ...         ...   \n",
       "52                 Alaska           AK  under18  1992.0    184878.0   \n",
       "83                 Alaska           AK  under18  2007.0    184344.0   \n",
       "85                 Alaska           AK  under18  2008.0    183124.0   \n",
       "51                 Alaska           AK  under18  1991.0    182180.0   \n",
       "49                 Alaska           AK  under18  1990.0    177502.0   \n",
       "\n",
       "     area (sq. mi)         midu  \n",
       "391           68.0  9506.602941  \n",
       "385           68.0  9315.102941  \n",
       "387           68.0  9112.117647  \n",
       "431           68.0  8901.779412  \n",
       "389           68.0  8898.897059  \n",
       "..             ...          ...  \n",
       "52        656425.0     0.281644  \n",
       "83        656425.0     0.280830  \n",
       "85        656425.0     0.278972  \n",
       "51        656425.0     0.277534  \n",
       "49        656425.0     0.270407  \n",
       "\n",
       "[2448 rows x 7 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#根据密度排序\n",
    "abb_pop_area.sort_values(by='midu',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}