vander_plus_3_Pandas_07.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "95e47c72",
   "metadata": {},
   "source": [
    "## Объединение наборов данных: слияние и соединение"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6977c472",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1fca1ae",
   "metadata": {},
   "source": [
    "    Реляционная алгебра (relation algebra) - формальный набор правил манипулирования реляционными данными, формирующий теоретические основания для имеющихся в большинстве баз данных операций.\n",
    "    Основной интерфейс для них - ф-ия pd.merge()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbfefa7a",
   "metadata": {},
   "source": [
    "### Виды соединений\n",
    "    pd.merge() реализует множество типов соединений:\n",
    "    \"один-к-одному\"\n",
    "    \"многие-к-одному\"\n",
    "    \"многие-ко-многим\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4941827b",
   "metadata": {},
   "source": [
    "#### Соединения \"один-к-одному\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9169edf9",
   "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>employee</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  employee        group\n",
       "0      Bob   Accounting\n",
       "1     Jake  Engineering\n",
       "2     Lisa  Engineering\n",
       "3      Sue           HR"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame(\n",
    "    {'employee': ['Bob', 'Jake', 'Lisa', 'Sue'],\n",
    "     'group': ['Accounting', 'Engineering', 'Engineering', 'HR']}\n",
    ")\n",
    "df2 = pd.DataFrame(\n",
    "    {'employee': ['Lisa', 'Bob', 'Jake', 'Sue'],\n",
    "     'hire_date': [2004, 2008, 2012, 2014]}\n",
    ")\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b9123b35",
   "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>employee</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jake</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  employee  hire_date\n",
       "0     Lisa       2004\n",
       "1      Bob       2008\n",
       "2     Jake       2012\n",
       "3      Sue       2014"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "955bf590",
   "metadata": {},
   "outputs": [],
   "source": [
    "# теперь объединими эту инфу\n",
    "df3 = pd.merge(df1, df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "0f4e4e1d",
   "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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  employee        group  hire_date\n",
       "0      Bob   Accounting       2008\n",
       "1     Jake  Engineering       2012\n",
       "2     Lisa  Engineering       2004\n",
       "3      Sue           HR       2014"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3\n",
    "# ф-ия megrge() распознает, что в обоих DataFrame существует столбец employee и автоматически использует его в качестве ключа"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "758f9801",
   "metadata": {},
   "source": [
    "#### Соединения \"многие-к-одному\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fa91f27c",
   "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>group</th>\n",
       "      <th>supervisor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>Carly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>Guido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>HR</td>\n",
       "      <td>Steve</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         group supervisor\n",
       "0   Accounting      Carly\n",
       "1  Engineering      Guido\n",
       "2           HR      Steve"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4 = pd.DataFrame(\n",
    "    {'group': ['Accounting', 'Engineering', 'HR'],\n",
    "     'supervisor': ['Carly', 'Guido', 'Steve']}\n",
    ")\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "30325890",
   "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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>hire_date</th>\n",
       "      <th>supervisor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>2008</td>\n",
       "      <td>Carly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2012</td>\n",
       "      <td>Guido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2004</td>\n",
       "      <td>Guido</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>2014</td>\n",
       "      <td>Steve</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  employee        group  hire_date supervisor\n",
       "0      Bob   Accounting       2008      Carly\n",
       "1     Jake  Engineering       2012      Guido\n",
       "2     Lisa  Engineering       2004      Guido\n",
       "3      Sue           HR       2014      Steve"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df3, df4)\n",
    "# соединение прошло по group"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a85ad3a",
   "metadata": {},
   "source": [
    "#### Соединения \"многие-ко-многим\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4e1c4513",
   "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>group</th>\n",
       "      <th>skills</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>spreadsheets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>coding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>linux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HR</td>\n",
       "      <td>spreadsheets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>HR</td>\n",
       "      <td>organization</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         group        skills\n",
       "0   Accounting          math\n",
       "1   Accounting  spreadsheets\n",
       "2  Engineering        coding\n",
       "3  Engineering         linux\n",
       "4           HR  spreadsheets\n",
       "5           HR  organization"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5 = pd.DataFrame({'group': ['Accounting', 'Accounting', 'Engineering', 'Engineering', 'HR', 'HR'],\n",
    "                    'skills': ['math', 'spreadsheets', 'coding', 'linux', 'spreadsheets', 'organization']})\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "93fcff1d",
   "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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>skills</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>math</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>spreadsheets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>coding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>linux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>coding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>linux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>spreadsheets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>organization</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  employee        group        skills\n",
       "0      Bob   Accounting          math\n",
       "1      Bob   Accounting  spreadsheets\n",
       "2     Jake  Engineering        coding\n",
       "3     Jake  Engineering         linux\n",
       "4     Lisa  Engineering        coding\n",
       "5     Lisa  Engineering         linux\n",
       "6      Sue           HR  spreadsheets\n",
       "7      Sue           HR  organization"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47c32116",
   "metadata": {},
   "source": [
    "### Задание ключа слияния\n",
    "    Часто имена столбцоа не совпадают"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28a5444e",
   "metadata": {},
   "source": [
    "#### Ключевое слово - on\n",
    "    Проще всего указать название ключевого столбца используя параметр on, передав ему название или список.\n",
    "    on - работает только в случае, когда в обоих DataFrame имеется указанное название столбца."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e7c5815a",
   "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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  employee        group  hire_date\n",
       "0      Bob   Accounting       2008\n",
       "1     Jake  Engineering       2012\n",
       "2     Lisa  Engineering       2004\n",
       "3      Sue           HR       2014"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on='employee')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "464841fb",
   "metadata": {},
   "source": [
    "#### Ключевые слова left_on и right_on\n",
    "    Для выполнения слияний наборов с разными именами столбцов"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9103d6c4",
   "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>name</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name  salary\n",
       "0   Bob   70000\n",
       "1  Jake   80000\n",
       "2  Lisa  120000\n",
       "3   Sue   90000"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.DataFrame(\n",
    "    {'name': ['Bob', 'Jake', 'Lisa', 'Sue'],\n",
    "     'salary': [70000, 80000, 120000, 90000]}\n",
    ")\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "4e47e4f3",
   "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>employee</th>\n",
       "      <th>group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  employee        group\n",
       "0      Bob   Accounting\n",
       "1     Jake  Engineering\n",
       "2     Lisa  Engineering\n",
       "3      Sue           HR"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "516e7f93",
   "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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>name</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>Bob</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>Jake</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>Lisa</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>Sue</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  employee        group  name  salary\n",
       "0      Bob   Accounting   Bob   70000\n",
       "1     Jake  Engineering  Jake   80000\n",
       "2     Lisa  Engineering  Lisa  120000\n",
       "3      Sue           HR   Sue   90000"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df3, left_on='employee', right_on='name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "e4c2d794",
   "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>employee</th>\n",
       "      <th>group</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>Accounting</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>Engineering</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>HR</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  employee        group  salary\n",
       "0      Bob   Accounting   70000\n",
       "1     Jake  Engineering   80000\n",
       "2     Lisa  Engineering  120000\n",
       "3      Sue           HR   90000"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# избыточный столбец можно удалить при помощи метода объекта DataFrame .drop()\n",
    "pd.merge(df1, df3, left_on='employee', right_on='name').drop('name', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "425929ba",
   "metadata": {},
   "source": [
    "#### Ключевые слова left_index и right_index\n",
    "    Иногда удобнее вместо слияния по столбцу выполнить слияние по индексу"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "b2c46894",
   "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>group</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                group\n",
       "employee             \n",
       "Bob        Accounting\n",
       "Jake      Engineering\n",
       "Lisa      Engineering\n",
       "Sue                HR"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1a = df1.set_index('employee')\n",
    "df1a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "12075b04",
   "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>hire_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          hire_date\n",
       "employee           \n",
       "Lisa           2004\n",
       "Bob            2008\n",
       "Jake           2012\n",
       "Sue            2014"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2a = df2.set_index('employee')\n",
    "df2a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a315c946",
   "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>group</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>HR</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                group  hire_date\n",
       "employee                        \n",
       "Bob        Accounting       2008\n",
       "Jake      Engineering       2012\n",
       "Lisa      Engineering       2004\n",
       "Sue                HR       2014"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1a, df2a, left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "16d9bae4",
   "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>group</th>\n",
       "      <th>hire_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>HR</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                group  hire_date\n",
       "employee                        \n",
       "Bob        Accounting       2008\n",
       "Jake      Engineering       2012\n",
       "Lisa      Engineering       2004\n",
       "Sue                HR       2014"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# для удобства в DataFrame реализован метод .join(), выполняющий слияние по индексам по умолчанию\n",
    "df1a.join(df2a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "f42b141d",
   "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>group</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>employee</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bob</th>\n",
       "      <td>Accounting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jake</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lisa</th>\n",
       "      <td>Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sue</th>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                group\n",
       "employee             \n",
       "Bob        Accounting\n",
       "Jake      Engineering\n",
       "Lisa      Engineering\n",
       "Sue                HR"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# возможна комбинация слияния по столбцам и индексам:\n",
    "df1a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "1960ad41",
   "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>name</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name  salary\n",
       "0   Bob   70000\n",
       "1  Jake   80000\n",
       "2  Lisa  120000\n",
       "3   Sue   90000"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "80115783",
   "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>group</th>\n",
       "      <th>name</th>\n",
       "      <th>salary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>Bob</td>\n",
       "      <td>70000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>Jake</td>\n",
       "      <td>80000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Engineering</td>\n",
       "      <td>Lisa</td>\n",
       "      <td>120000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HR</td>\n",
       "      <td>Sue</td>\n",
       "      <td>90000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         group  name  salary\n",
       "0   Accounting   Bob   70000\n",
       "1  Engineering  Jake   80000\n",
       "2  Engineering  Lisa  120000\n",
       "3           HR   Sue   90000"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1a, df3, left_index=True, right_on='name')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2db3a225",
   "metadata": {},
   "source": [
    "### Задание операций над множествами для соединений\n",
    "    Случаи когда не все ключи совпадают"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "8a752fbd",
   "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>name</th>\n",
       "      <th>food</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Peter</td>\n",
       "      <td>fish</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Paul</td>\n",
       "      <td>beans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name   food\n",
       "0  Peter   fish\n",
       "1   Paul  beans\n",
       "2   Mary  bread"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df6 = pd.DataFrame({'name': ['Peter', 'Paul', 'Mary'],\n",
    "                    'food': ['fish', 'beans', 'bread']},\n",
    "                  columns=['name', 'food'])\n",
    "df6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "500591df",
   "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>name</th>\n",
       "      <th>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mary</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Joseph</td>\n",
       "      <td>beer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name drink\n",
       "0    Mary  wine\n",
       "1  Joseph  beer"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df7 = pd.DataFrame({'name': ['Mary', 'Joseph'],\n",
    "                    'drink': ['wine', 'beer']},\n",
    "                  columns=['name', 'drink'])\n",
    "df7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "eb59a902",
   "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>name</th>\n",
       "      <th>food</th>\n",
       "      <th>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name   food drink\n",
       "0  Mary  bread  wine"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df6, df7)\n",
    "# по умолчанию результат содержит только пересечение 2х вхоных множеств (inner join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "f63574a7",
   "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>name</th>\n",
       "      <th>food</th>\n",
       "      <th>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name   food drink\n",
       "0  Mary  bread  wine"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# можно задать явно\n",
    "pd.merge(df6, df7, how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af58c2dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# how = 'outer', 'left', 'right', 'inner'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "45fc66f4",
   "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>name</th>\n",
       "      <th>food</th>\n",
       "      <th>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Peter</td>\n",
       "      <td>fish</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Paul</td>\n",
       "      <td>beans</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Joseph</td>\n",
       "      <td>NaN</td>\n",
       "      <td>beer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name   food drink\n",
       "0   Peter   fish   NaN\n",
       "1    Paul  beans   NaN\n",
       "2    Mary  bread  wine\n",
       "3  Joseph    NaN  beer"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df6, df7, how='outer')\n",
    "# внешнее соединение - объединение всех входящих столбцов"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "47e4a9be",
   "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>name</th>\n",
       "      <th>food</th>\n",
       "      <th>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Peter</td>\n",
       "      <td>fish</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Paul</td>\n",
       "      <td>beans</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name   food drink\n",
       "0  Peter   fish   NaN\n",
       "1   Paul  beans   NaN\n",
       "2   Mary  bread  wine"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# по левой табличке left\n",
    "pd.merge(df6, df7, how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "8dde0175",
   "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>name</th>\n",
       "      <th>food</th>\n",
       "      <th>drink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mary</td>\n",
       "      <td>bread</td>\n",
       "      <td>wine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Joseph</td>\n",
       "      <td>NaN</td>\n",
       "      <td>beer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     name   food drink\n",
       "0    Mary  bread  wine\n",
       "1  Joseph    NaN  beer"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# по правой табличке right\n",
    "pd.merge(df6, df7, how='right')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2accde12",
   "metadata": {},
   "source": [
    "### Пересекающиеся названия столбцов:\n",
    "### Ключевое слово suffixes\n",
    "    Случай, когда в двух входных объектах присутствуют конфликтующие названия столбцов."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2a3ba358",
   "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>name</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name  rank\n",
       "0   Bob     1\n",
       "1  Jake     2\n",
       "2  Lisa     3\n",
       "3   Sue     4"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df8 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],\n",
    "                    'rank': [1, 2, 3, 4]})\n",
    "df9 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],\n",
    "                    'rank': [3, 1, 4, 2]})\n",
    "df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "f646850a",
   "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>name</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name  rank\n",
       "0   Bob     3\n",
       "1  Jake     1\n",
       "2  Lisa     4\n",
       "3   Sue     2"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "c9517afc",
   "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>name</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [name, rank]\n",
       "Index: []"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df8, df9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "64ef1391",
   "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>name</th>\n",
       "      <th>rank_x</th>\n",
       "      <th>rank_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name  rank_x  rank_y\n",
       "0   Bob       1       3\n",
       "1  Jake       2       1\n",
       "2  Lisa       3       4\n",
       "3   Sue       4       2"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df8, df9, on='name')\n",
    "# ф-ия слияния автоматически добавила в названия суффиксы _x _y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "1e5e405f",
   "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>name</th>\n",
       "      <th>rank_Low</th>\n",
       "      <th>rank_Uppers_lalala</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bob</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jake</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lisa</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Sue</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   name  rank_Low  rank_Uppers_lalala\n",
       "0   Bob         1                   3\n",
       "1  Jake         2                   1\n",
       "2  Lisa         3                   4\n",
       "3   Sue         4                   2"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# если мы хотим свои суффиксы\n",
    "pd.merge(df8, df9, on='name', suffixes=['_Low', '_Uppers_lalala'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb3cdccf",
   "metadata": {},
   "source": [
    "### Пример: Данные по штатам США"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "3615893b",
   "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",
       "    <tr>\n",
       "      <th>3</th>\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>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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2544 rows × 4 columns</p>\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\n",
       "3              AL    total  2010    4785570.0\n",
       "4              AL  under18  2011    1125763.0\n",
       "...           ...      ...   ...          ...\n",
       "2539          USA    total  2010  309326295.0\n",
       "2540          USA  under18  2011   73902222.0\n",
       "2541          USA    total  2011  311582564.0\n",
       "2542          USA  under18  2012   73708179.0\n",
       "2543          USA    total  2012  313873685.0\n",
       "\n",
       "[2544 rows x 4 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop = pd.read_csv('data/state-population.csv')\n",
    "pop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "9f602c8d",
   "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",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>53182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>163707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>104100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>5544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>1954</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\n",
       "3     Arkansas          53182\n",
       "4   California         163707\n",
       "5     Colorado         104100\n",
       "6  Connecticut           5544\n",
       "7     Delaware           1954"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "areas = pd.read_csv('data/state-areas.csv')\n",
    "areas.head(8) # выводит первые 5 строк по defoult"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "7894edc0",
   "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",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>CA</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\n",
       "3    Arkansas           AR\n",
       "4  California           CA"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "abbrevs = pd.read_csv('data/state-abbrevs.csv')\n",
    "abbrevs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "8c1735d8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "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         state  area (sq. mi)\n",
      "0     Alabama          52423\n",
      "1      Alaska         656425\n",
      "2     Arizona         114006\n",
      "3    Arkansas          53182\n",
      "4  California         163707         state abbreviation\n",
      "0     Alabama           AL\n",
      "1      Alaska           AK\n",
      "2     Arizona           AZ\n",
      "3    Arkansas           AR\n",
      "4  California           CA\n"
     ]
    }
   ],
   "source": [
    "print(pop.head(), areas.head(), abbrevs.head())  # криво"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "630decfb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "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",
      "        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",
      "        state abbreviation\n",
      "0     Alabama           AL\n",
      "1      Alaska           AK\n",
      "2     Arizona           AZ\n",
      "3    Arkansas           AR\n",
      "4  California           CA\n"
     ]
    }
   ],
   "source": [
    "print(pop.head()); print(areas.head()); print(abbrevs.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "e1c94701",
   "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",
       "      <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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2011</td>\n",
       "      <td>4801627.0</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AL</td>\n",
       "      <td>total</td>\n",
       "      <td>2009</td>\n",
       "      <td>4757938.0</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2009</td>\n",
       "      <td>1134192.0</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AL</td>\n",
       "      <td>under18</td>\n",
       "      <td>2013</td>\n",
       "      <td>1111481.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\n",
       "5           AL    total  2011   4801627.0  Alabama\n",
       "6           AL    total  2009   4757938.0  Alabama\n",
       "7           AL  under18  2009   1134192.0  Alabama\n",
       "8           AL  under18  2013   1111481.0  Alabama"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged = pd.merge(pop, abbrevs, how='outer',\n",
    "                  left_on='state/region', right_on='abbreviation').drop('abbreviation', axis=1)\n",
    "merged.head(9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "a2e684a3",
   "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",
       "      <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",
       "    <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>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2540</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2541</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2542</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2543</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2544 rows × 5 columns</p>\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\n",
       "...            ...    ...    ...         ...    ...\n",
       "2539         False  False  False       False   True\n",
       "2540         False  False  False       False   True\n",
       "2541         False  False  False       False   True\n",
       "2542         False  False  False       False   True\n",
       "2543         False  False  False       False   True\n",
       "\n",
       "[2544 rows x 5 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# проверка есть ли несовпадения\n",
    "merged.isnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "68747999",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state/region    False\n",
       "ages            False\n",
       "year            False\n",
       "population       True\n",
       "state            True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.isnull().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "9c2f95cb",
   "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",
       "      <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"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# выведем строки в котрых были пропуски по столбцу population\n",
    "merged[merged['population'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "41aa59a0",
   "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",
       "      <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]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.loc[merged['state'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "baa9834a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['PR', 'USA'], dtype=object)"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.loc[merged['state'].isnull(), 'state/region'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "742dfe68",
   "metadata": {},
   "outputs": [],
   "source": [
    "# исправим эти записи столбец state для записей в которых merged['state/region'] == 'PR'\n",
    "merged.loc[merged['state/region'] == 'PR', 'state'] = 'Puerto Rico'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "db5e7fa5",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged.loc[merged['state/region'] == 'USA', 'state'] = 'Unites States'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "0583a99c",
   "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",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [state/region, ages, year, population, state]\n",
       "Index: []"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.loc[merged['state'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "20679665",
   "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",
       "      <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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</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>Puerto Rico</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     state/region     ages  year  population        state\n",
       "2448           PR  under18  1990         NaN  Puerto Rico\n",
       "2449           PR    total  1990         NaN  Puerto Rico\n",
       "2450           PR    total  1991         NaN  Puerto Rico\n",
       "2451           PR  under18  1991         NaN  Puerto Rico\n",
       "2452           PR    total  1993         NaN  Puerto Rico\n",
       "2453           PR  under18  1993         NaN  Puerto Rico\n",
       "2454           PR  under18  1992         NaN  Puerto Rico\n",
       "2455           PR    total  1992         NaN  Puerto Rico\n",
       "2456           PR  under18  1994         NaN  Puerto Rico\n",
       "2457           PR    total  1994         NaN  Puerto Rico\n",
       "2458           PR    total  1995         NaN  Puerto Rico\n",
       "2459           PR  under18  1995         NaN  Puerto Rico\n",
       "2460           PR  under18  1996         NaN  Puerto Rico\n",
       "2461           PR    total  1996         NaN  Puerto Rico\n",
       "2462           PR  under18  1998         NaN  Puerto Rico\n",
       "2463           PR    total  1998         NaN  Puerto Rico\n",
       "2464           PR    total  1997         NaN  Puerto Rico\n",
       "2465           PR  under18  1997         NaN  Puerto Rico\n",
       "2466           PR    total  1999         NaN  Puerto Rico\n",
       "2467           PR  under18  1999         NaN  Puerto Rico"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged[merged['population'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "736f7221",
   "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",
       "      <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"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "f2b11765",
   "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",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>53182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>163707</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\n",
       "3    Arkansas          53182\n",
       "4  California         163707"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "areas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "ddc71bbb",
   "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",
       "      <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",
       "    <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>USA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>309326295.0</td>\n",
       "      <td>Unites States</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>Unites States</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>Unites States</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>Unites States</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>Unites States</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2544 rows × 6 columns</p>\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\n",
       "...           ...      ...   ...          ...            ...            ...\n",
       "2539          USA    total  2010  309326295.0  Unites States            NaN\n",
       "2540          USA  under18  2011   73902222.0  Unites States            NaN\n",
       "2541          USA    total  2011  311582564.0  Unites States            NaN\n",
       "2542          USA  under18  2012   73708179.0  Unites States            NaN\n",
       "2543          USA    total  2012  313873685.0  Unites States            NaN\n",
       "\n",
       "[2544 rows x 6 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(merged, areas, how='outer', left_on='state', right_on='state')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "327c3446",
   "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",
       "      <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",
       "    <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>USA</td>\n",
       "      <td>total</td>\n",
       "      <td>2010</td>\n",
       "      <td>309326295.0</td>\n",
       "      <td>Unites States</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>Unites States</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>Unites States</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>Unites States</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>Unites States</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2544 rows × 6 columns</p>\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\n",
       "...           ...      ...   ...          ...            ...            ...\n",
       "2539          USA    total  2010  309326295.0  Unites States            NaN\n",
       "2540          USA  under18  2011   73902222.0  Unites States            NaN\n",
       "2541          USA    total  2011  311582564.0  Unites States            NaN\n",
       "2542          USA  under18  2012   73708179.0  Unites States            NaN\n",
       "2543          USA    total  2012  313873685.0  Unites States            NaN\n",
       "\n",
       "[2544 rows x 6 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final = pd.merge(merged, areas, how='left', on='state')\n",
    "final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "d5a7d54d",
   "metadata": {},
   "outputs": [
    {
     "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"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.isnull().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "472a16fa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Unites States'], dtype=object)"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# посмотрим какие территории не были учтены\n",
    "final['state'][final['area (sq. mi)'].isnull()].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "c5089089",
   "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",
       "      <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",
       "    <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>2491</th>\n",
       "      <td>PR</td>\n",
       "      <td>under18</td>\n",
       "      <td>2010</td>\n",
       "      <td>896945.0</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>3515.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2492</th>\n",
       "      <td>PR</td>\n",
       "      <td>under18</td>\n",
       "      <td>2011</td>\n",
       "      <td>869327.0</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>3515.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2493</th>\n",
       "      <td>PR</td>\n",
       "      <td>total</td>\n",
       "      <td>2011</td>\n",
       "      <td>3686580.0</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>3515.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2494</th>\n",
       "      <td>PR</td>\n",
       "      <td>under18</td>\n",
       "      <td>2012</td>\n",
       "      <td>841740.0</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>3515.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2495</th>\n",
       "      <td>PR</td>\n",
       "      <td>total</td>\n",
       "      <td>2012</td>\n",
       "      <td>3651545.0</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>3515.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2476 rows × 6 columns</p>\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\n",
       "...           ...      ...   ...         ...          ...            ...\n",
       "2491           PR  under18  2010    896945.0  Puerto Rico         3515.0\n",
       "2492           PR  under18  2011    869327.0  Puerto Rico         3515.0\n",
       "2493           PR    total  2011   3686580.0  Puerto Rico         3515.0\n",
       "2494           PR  under18  2012    841740.0  Puerto Rico         3515.0\n",
       "2495           PR    total  2012   3651545.0  Puerto Rico         3515.0\n",
       "\n",
       "[2476 rows x 6 columns]"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Удалим NaN\n",
    "final.dropna(inplace=True)\n",
    "final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "8415eed9",
   "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",
       "      <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"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2010 = final.query(\"year == 2010 & ages == 'total'\")\n",
    "data2010.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "ae9103ef",
   "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",
       "      <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"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# выполним переиндексацию\n",
    "data2010.set_index('state', inplace=True)\n",
    "data2010.head()\n",
    "# после выполнения переиндексации, эту ячейку второй раз не выполнить, т.к. в буфере окажется уже переиндексированный DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "81ff9c8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# вычислим плотность населения и выведем данные в соответствующем порядке\n",
    "density = data2010['population'] / data2010['area (sq. mi)']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "140d4e85",
   "metadata": {},
   "outputs": [
    {
     "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"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "density.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "471bf5e0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state\n",
       "District of Columbia    8898.897059\n",
       "Puerto Rico             1058.665149\n",
       "New Jersey              1009.253268\n",
       "Rhode Island             681.339159\n",
       "Connecticut              645.600649\n",
       "dtype: float64"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "density.sort_values(ascending=False, inplace=True)\n",
    "density.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "4fef326b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state\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"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# хвост списка .tail()\n",
    "density.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "70c408f0",
   "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": 5
}