09_Загрузка данных.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Настройка pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# импортируем библиотеки numpy и pandas\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# импортируем библиотеку datetime для работы с датами\n",
    "import datetime\n",
    "from datetime import datetime, date\n",
    "\n",
    "# задаем некоторые опции библиотеки pandas, которые\n",
    "# настраивают вывод\n",
    "pd.set_option('display.notebook_repr_html', False)\n",
    "pd.set_option('display.max_columns', 8)\n",
    "pd.set_option('display.max_rows', 10)\n",
    "pd.set_option('display.width', 90)\n",
    "\n",
    "# импортируем библиотеку matplotlib для построения графиков\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Date', 'Open', 'High', 'Low', 'Close', 'Volume']\n",
      "['7/21/2014', '83.46', '83.53', '81.81', '81.93', '2359300']\n",
      "['7/18/2014', '83.3', '83.4', '82.52', '83.35', '4020800']\n",
      "['7/17/2014', '84.35', '84.63', '83.33', '83.63', '1974000']\n",
      "['7/16/2014', '83.77', '84.91', '83.66', '84.91', '1755600']\n",
      "['7/15/2014', '84.3', '84.38', '83.2', '83.58', '1874700']\n"
     ]
    }
   ],
   "source": [
    "# с помощью модуля csv взглянем\n",
    "# на первые 5 строк CSV-файла\n",
    "import csv \n",
    "with open('C:/data/msft.csv') as file:\n",
    "    reader = csv.reader(file, delimiter=',')\n",
    "    for i,row in enumerate(reader):\n",
    "        print(row)\n",
    "        if(i >= 5):\n",
    "            break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Считывание CSV-файла в датафрейм"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "2  7/17/2014  84.35  84.63  83.33  83.63  1974000\n",
       "3  7/16/2014  83.77  84.91  83.66  84.91  1755600\n",
       "4  7/15/2014  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем msft.csv в датафрейм\n",
    "msft = pd.read_csv(\"C:/data/msft.csv\")\n",
    "msft[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Указание индекса столбца при считывании CSV-файла "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "            Open   High    Low  Close   Volume\n",
       "Date                                          \n",
       "7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "7/17/2014  84.35  84.63  83.33  83.63  1974000\n",
       "7/16/2014  83.77  84.91  83.66  84.91  1755600\n",
       "7/15/2014  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# используем столбец 0 в качестве индекса\n",
    "msft = pd.read_csv(\"C:/data/msft.csv\", index_col=0)\n",
    "msft[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Вывод и спецификация типа данных"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Open      float64\n",
       "High      float64\n",
       "Low       float64\n",
       "Close     float64\n",
       "Volume      int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# исследуем типы столбцов в этом датафрейме\n",
    "msft.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Date       object\n",
       "Open      float64\n",
       "High      float64\n",
       "Low       float64\n",
       "Close     float64\n",
       "Volume    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# указываем, что столбец Volume должен иметь тип float64\n",
    "msft = pd.read_csv(\"C:/data/msft.csv\", \n",
    "                   dtype = { 'Volume' : np.float64})\n",
    "msft.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Указание имен столбцов"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        date   open   high    low  close   volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "2  7/17/2014  84.35  84.63  83.33  83.63  1974000\n",
       "3  7/16/2014  83.77  84.91  83.66  84.91  1755600\n",
       "4  7/15/2014  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# задаем новый набор имен для столбцов\n",
    "# все имеют нижний регистр, \n",
    "# header=0 задает строку заголовков\n",
    "df = pd.read_csv(\"C:/data/msft.csv\", \n",
    "                 header=0,\n",
    "                 names=['date', 'open', 'high', 'low', \n",
    "                        'close', 'volume'])\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Указание конкретных столбцов для загрузки"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "           Close\n",
       "Date            \n",
       "7/21/2014  81.93\n",
       "7/18/2014  83.35\n",
       "7/17/2014  83.63\n",
       "7/16/2014  84.91\n",
       "7/15/2014  83.58"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем в данных только столбцы Date и Close\n",
    "# и индексируем по столбцу Date\n",
    "df2 = pd.read_csv(\"data/msft.csv\", \n",
    "                  usecols=['Date', 'Close'], \n",
    "                  index_col=['Date'])\n",
    "df2[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Сохранение датафрейма в CSV-файл"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# сохраняем датафрейм df2 в новый csv-файл\n",
    "# задаем имя индекса как date\n",
    "df2.to_csv(\"C:/data/msft_modified.csv\", index_label='date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['date', 'Close']\n",
      "['7/21/2014', '81.93']\n",
      "['7/18/2014', '83.35']\n",
      "['7/17/2014', '83.63']\n",
      "['7/16/2014', '84.91']\n",
      "['7/15/2014', '83.58']\n"
     ]
    }
   ],
   "source": [
    "# с помощью модуля csv взглянем\n",
    "# на первые 5 строк CSV-файла\n",
    "with open('C:/data/msft_modified.csv') as file:\n",
    "    reader = csv.reader(file, delimiter=',')\n",
    "    for i,row in enumerate(reader):\n",
    "        print(row)\n",
    "        if(i >= 5):\n",
    "            break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Работа с данными, в которых используются разделители полей"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "2  7/17/2014  84.35  84.63  83.33  83.63  1974000\n",
       "3  7/16/2014  83.77  84.91  83.66  84.91  1755600\n",
       "4  7/15/2014  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# используем функцию read_table с параметром sep=',' \n",
    "# чтобы прочитать CSV-файл\n",
    "df = pd.read_table(\"C:/data/msft.csv\", sep=',')\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['|Date|Open|High|Low|Close|Volume']\n",
      "['0|7/21/2014|83.46|83.53|81.81|81.93|2359300']\n",
      "['1|7/18/2014|83.3|83.4|82.52|83.35|4020800']\n",
      "['2|7/17/2014|84.35|84.63|83.33|83.63|1974000']\n",
      "['3|7/16/2014|83.77|84.91|83.66|84.91|1755600']\n",
      "['4|7/15/2014|84.3|84.38|83.2|83.58|1874700']\n"
     ]
    }
   ],
   "source": [
    "# сохраняем как данные, в которых разделителем\n",
    "# является вертикальная черта\n",
    "df.to_csv(\"C:/data/msft_piped.txt\", sep='|')\n",
    "# смотрим, как сработал программный код\n",
    "with open('C:/data/msft_piped.txt') as file:\n",
    "    reader = csv.reader(file, delimiter=',')\n",
    "    for i,row in enumerate(reader):\n",
    "        print(row)\n",
    "        if(i >= 5):\n",
    "            break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Обработка загрязненных данных, в которых используются разделители полей"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Данные начинаются не с первой строки', '', '', '', '', '']\n",
      "['Date', 'Open', 'High', 'Low', 'Close', 'Volume']\n",
      "['', '', '', '', '', '']\n",
      "['А тут пробел между строкой заголовков и данными', '', '', '', '', '']\n",
      "['7/21/2014', '83.46', '83.53', '81.81', '81.93', '2359300']\n",
      "['7/18/2014', '83.3', '83.4', '82.52', '83.35', '4020800']\n",
      "['7/17/2014', '84.35', '84.63', '83.33', '83.63', '1974000']\n"
     ]
    }
   ],
   "source": [
    "# смотрим первые 6 наблюдений файла msft2.csv\n",
    "with open('C:/data/msft2.csv') as file:\n",
    "    reader = csv.reader(file, delimiter=',')\n",
    "    for i,row in enumerate(reader):\n",
    "        print(row)\n",
    "        if(i >= 6):\n",
    "            break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "2  7/17/2014  84.35  84.63  83.33  83.63  1974000\n",
       "3  7/16/2014  83.77  84.91  83.66  84.91  1755600\n",
       "4  7/15/2014  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем данные, пропустив строки 0, 2 и 3\n",
    "df = pd.read_csv(\"C:/data/msft2.csv\", skiprows=[0, 2, 3])\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Date', 'Open', 'High', 'Low', 'Close', 'Volume']\n",
      "['7/21/2014', '83.46', '83.53', '81.81', '81.93', '2359300']\n",
      "['7/18/2014', '83.3', '83.4', '82.52', '83.35', '4020800']\n",
      "[]\n",
      "['Тут что-то есть в конце файла.']\n"
     ]
    }
   ],
   "source": [
    "# смотрим файл msft_with_footer.csv\n",
    "with open('C:/data/msft_with_footer.csv') as file:\n",
    "    reader = csv.reader(file, delimiter=',')\n",
    "    for row in reader:\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем, пропустив две строки в конце файла\n",
    "df = pd.read_csv(\"C:/data/msft_with_footer.csv\", \n",
    "                 skipfooter=2,\n",
    "                 engine = 'python')\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0  7/21/2014  83.46  83.53  81.81  81.93  2359300\n",
       "1  7/18/2014  83.30  83.40  82.52  83.35  4020800\n",
       "2  7/17/2014  84.35  84.63  83.33  83.63  1974000"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считаем только первые три строки\n",
    "pd.read_csv(\"data/msft.csv\", nrows=3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        date   open   high    low  close      vol\n",
       "0   3/3/2014  80.35  81.31  79.91  79.97  5004100\n",
       "1  2/28/2014  82.40  83.42  82.17  83.42  2853200\n",
       "2  2/27/2014  84.06  84.63  81.63  82.00  3676800\n",
       "3  2/26/2014  82.92  84.03  82.43  83.81  2623600\n",
       "4  2/25/2014  83.80  83.80  81.72  83.08  3579100"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# пропускаем 100 строк, а затем считываем следующие 5 строк\n",
    "pd.read_csv(\"C:/data/msft.csv\", skiprows=100, nrows=5, \n",
    "            header=0,\n",
    "            names=['date', 'open', 'high', 'low', \n",
    "                   'close', 'vol']) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Чтение и запись данных в формате Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close   Volume\n",
       "0 2014-07-21  83.46  83.53  81.81  81.93  2359300\n",
       "1 2014-07-18  83.30  83.40  82.52  83.35  4020800\n",
       "2 2014-07-17  84.35  84.63  83.33  83.63  1974000\n",
       "3 2014-07-16  83.77  84.91  83.66  84.91  1755600\n",
       "4 2014-07-15  84.30  84.38  83.20  83.58  1874700"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем файл Excel\n",
    "# считываем только данные первого рабочего листа\n",
    "# (msft в данном случае)\n",
    "df = pd.read_excel(\"C:/data/stocks.xlsx\")\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close    Volume\n",
       "0 2014-07-21  94.99  95.00  93.72  93.94  38887700\n",
       "1 2014-07-18  93.62  94.74  93.02  94.43  49898600\n",
       "2 2014-07-17  95.03  95.28  92.57  93.09  57152000\n",
       "3 2014-07-16  96.97  97.10  94.74  94.78  53396300\n",
       "4 2014-07-15  96.80  96.85  95.03  95.32  45477900"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем данные рабочего листа aapl\n",
    "aapl = pd.read_excel(\"C:/data/stocks.xlsx\", sheetname='aapl')\n",
    "aapl[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# сохраняем XLS-файл в рабочем листе 'Sheet1'\n",
    "df.to_excel(\"C:/data/stocks2.xls\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# записываем, задав имя рабочего листа MSFT\n",
    "df.to_excel(\"C:/data/stocks_msft.xls\", sheet_name='MSFT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# записываем несколько рабочих листов\n",
    "# требуется класс ExcelWriter\n",
    "from pandas import ExcelWriter\n",
    "with ExcelWriter(\"C:/data/all_stocks.xls\") as writer:\n",
    "    aapl.to_excel(writer, sheet_name='AAPL')\n",
    "    df.to_excel(writer, sheet_name='MSFT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# записываем в xlsx\n",
    "df.to_excel(\"C:/data/msft2.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Чтение и запись JSON-файлов"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'Close': {'0': 81.93, '1': 83.35, '2': 83.63, '3': 84.91, '4': 83.58},\n",
      " 'Date': {'0': 1405900800000,\n",
      "          '1': 1405641600000,\n",
      "          '2': 1405555200000,\n",
      "          '3': 1405468800000,\n",
      "          '4': 1405382400000},\n",
      " 'High': {'0': 83.53, '1': 83.4, '2': 84.63, '3': 84.91, '4': 84.38},\n",
      " 'Low': {'0': 81.81, '1': 82.52, '2': 83.33, '3': 83.66, '4': 83.2},\n",
      " 'Open': {'0': 83.46, '1': 83.3, '2': 84.35, '3': 83.77, '4': 84.3},\n",
      " 'Volume': {'0': 2359300,\n",
      "            '1': 4020800,\n",
      "            '2': 1974000,\n",
      "            '3': 1755600,\n",
      "            '4': 1874700}}\n"
     ]
    }
   ],
   "source": [
    "# записываем данные Excel в JSON-файл\n",
    "df[:5].to_json(\"C:/data/stocks.json\")\n",
    "# теперь взглянем на JSON-файл\n",
    "import json\n",
    "from pprint import pprint\n",
    "\n",
    "with open('C:/data/stocks.json') as data_file:    \n",
    "    data = json.load(data_file)\n",
    "\n",
    "pprint(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   Close       Date   High    Low   Open   Volume\n",
       "0  81.93 2014-07-21  83.53  81.81  83.46  2359300\n",
       "1  83.35 2014-07-18  83.40  82.52  83.30  4020800\n",
       "2  83.63 2014-07-17  84.63  83.33  84.35  1974000\n",
       "3  84.91 2014-07-16  84.91  83.66  83.77  1755600\n",
       "4  83.58 2014-07-15  84.38  83.20  84.30  1874700"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем данные в формате JSON\n",
    "df_from_json = pd.read_json(\"C:/data/stocks.json\")\n",
    "df_from_json[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Чтение HTML-файлов из Интернета"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# задаем URL-адрес HTML-файла\n",
    "url = \"http://www.fdic.gov/bank/individual/failed/banklist.html\"\n",
    "# читаем его\n",
    "banks = pd.read_html(url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                           Bank Name                City\n",
       "0                                Fayette County Bank          Saint Elmo\n",
       "1  Guaranty Bank, (d/b/a BestBank in Georgia & Mi...           Milwaukee\n",
       "2                                     First NBC Bank         New Orleans\n",
       "3                                      Proficio Bank  Cottonwood Heights\n",
       "4                      Seaway Bank and Trust Company             Chicago"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# проверяем, как была прочитана\n",
    "# часть первой таблицы\n",
    "banks[0][0:5].iloc[:,0:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем данные о котировках акций\n",
    "df = pd.read_excel(\"C:/data/stocks.xlsx\")\n",
    "# записываем первые две строки в HTML\n",
    "df.head(2).to_html(\"C:/data/stocks.html\")\n",
    "# смотрим HTML-файл в браузере\n",
    "import webbrowser\n",
    "webbrowser.open(\"C:/data/stocks.html\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Чтение и запись HDF5-файлов"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<class 'pandas.io.pytables.HDFStore'>\n",
       "File path: C:/data/store.h5\n",
       "/df            frame        (shape->[8,3])"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# задаем стартовое значение генератора случайных чисел\n",
    "# для получения воспроизводимых результатов\n",
    "np.random.seed(123456)\n",
    "# создаем датафрейм, состоящий из дат и случайных чисел,\n",
    "# записанных в трех столбцах \n",
    "df = pd.DataFrame(np.random.randn(8, 3), \n",
    "                  index=pd.date_range('1/1/2000', periods=8),\n",
    "                  columns=['A', 'B', 'C'])\n",
    "\n",
    "# создаем хранилище HDF5\n",
    "store = pd.HDFStore('C:/data/store.h5')\n",
    "store['df'] = df # сохранение произошло здесь\n",
    "store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                   A         B         C\n",
       "2000-01-01  0.469112 -0.282863 -1.509059\n",
       "2000-01-02 -1.135632  1.212112 -0.173215\n",
       "2000-01-03  0.119209 -1.044236 -0.861849\n",
       "2000-01-04 -2.104569 -0.494929  1.071804\n",
       "2000-01-05  0.721555 -0.706771 -1.039575"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем данные хранилища HDF5 \n",
    "store = pd.HDFStore(\"C:/data/store.h5\")\n",
    "df = store['df']\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                   A         B         C\n",
       "2000-01-01  1.000000 -0.282863 -1.509059\n",
       "2000-01-02 -1.135632  1.212112 -0.173215\n",
       "2000-01-03  0.119209 -1.044236 -0.861849\n",
       "2000-01-04 -2.104569 -0.494929  1.071804\n",
       "2000-01-05  0.721555 -0.706771 -1.039575"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# этот программный код меняет датафрейм, но изменения не сохраняются\n",
    "df.iloc[0].A = 1 \n",
    "# чтобы сохранить изменения, присваиваем объект DataFrame \n",
    "# объекту-хранилищу HDF5\n",
    "store['df'] = df\n",
    "# теперь изменения сохранены\n",
    "# следующий программный код загружает хранилище и \n",
    "# выводит первые две строки, демонстрируя,\n",
    "# что сохранение выполнено\n",
    "pd.HDFStore(\"C:/data/store.h5\")['df'][:5] # сейчас датафрейм в хранилище"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Загрузка данных из Интернета и облака"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        Date   Open   High    Low  Close    Volume\n",
       "0  13-Sep-17  74.93  75.23  74.55  75.21  13380802\n",
       "1  12-Sep-17  74.76  75.24  74.37  74.68  14394850\n",
       "2  11-Sep-17  74.31  74.94  74.31  74.76  17910383\n",
       "3   8-Sep-17  74.33  74.44  73.84  73.98  14703816\n",
       "4   7-Sep-17  73.68  74.60  73.60  74.34  17471200"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем csv непосредственно из Google Finance по URL-адресу\n",
    "msft_hist = pd.read_csv(\n",
    "    \"http://www.google.com/finance/historical?\" +\n",
    "    \"q=NASDAQ:MSFT&startdate=Apr+01%2C+2017&\" +\n",
    "    \"enddate=Apr+30%2C+2017&output=csv\")\n",
    "msft_hist[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Чтение из базы данных SQL и запись в базу данных SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# импортируем библиотеку SQLite\n",
    "import sqlite3\n",
    "\n",
    "# считываем данные о котировках акций из CSV-файла\n",
    "msft = pd.read_csv(\"C:/data/msft.csv\")\n",
    "msft[\"Symbol\"]=\"MSFT\"\n",
    "aapl = pd.read_csv(\"C:/data/aapl.csv\")\n",
    "aapl[\"Symbol\"]=\"AAPL\"\n",
    "\n",
    "# создаем подключение\n",
    "connection = sqlite3.connect(\"C:/data/stocks.sqlite\")\n",
    "# .to_sql() создаст базу SQL для хранения датафрейма\n",
    "# в указанной таблице.  if_exists задает\n",
    "# действие, которое нужно выполнить в том случае,\n",
    "# если таблица уже существует\n",
    "msft.to_sql(\"STOCK_DATA\", connection, if_exists=\"replace\")\n",
    "aapl.to_sql(\"STOCK_DATA\", connection, if_exists=\"append\")\n",
    "\n",
    "# подтверждаем отправку данных в базу и закрываем подключение\n",
    "connection.commit()\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "            Date   Open   High    Low  Close   Volume Symbol\n",
       "index                                                       \n",
       "0      7/21/2014  83.46  83.53  81.81  81.93  2359300   MSFT\n",
       "1      7/18/2014  83.30  83.40  82.52  83.35  4020800   MSFT\n",
       "2      7/17/2014  84.35  84.63  83.33  83.63  1974000   MSFT\n",
       "3      7/16/2014  83.77  84.91  83.66  84.91  1755600   MSFT\n",
       "4      7/15/2014  84.30  84.38  83.20  83.58  1874700   MSFT"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# подключаемся к файлу базы данных\n",
    "connection = sqlite3.connect(\"C:/data/stocks.sqlite\")\n",
    "\n",
    "# запрос всех записей в STOCK_DATA\n",
    "# возвращает датафрейм\n",
    "# index_col задает столбец, который нужно сделать\n",
    "# индексом датафрейма\n",
    "stocks = pd.io.sql.read_sql(\"SELECT * FROM STOCK_DATA;\", \n",
    "                             connection, index_col='index')\n",
    "\n",
    "# закрываем подключение\n",
    "connection.close()\n",
    "\n",
    "# выводим первые 5 наблюдений в извлеченных данных\n",
    "stocks[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "            Date   Open   High    Low  Close    Volume Symbol\n",
       "index                                                        \n",
       "1081   5/21/2010  42.22  42.35  40.99  42.00  33610800   MSFT\n",
       "1097   4/29/2010  46.80  46.95  44.65  45.92  47076200   MSFT\n",
       "1826   6/15/2007  89.80  92.10  89.55  92.04  30656400   MSFT\n",
       "3455   3/16/2001  47.00  47.80  46.10  45.33  40806400   MSFT\n",
       "3712   3/17/2000  49.50  50.00  48.29  50.00  50860500   MSFT"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# открываем подключение\n",
    "connection = sqlite3.connect(\"C:/data/stocks.sqlite\")\n",
    "# создаем строку-запрос\n",
    "query = \"SELECT * FROM STOCK_DATA WHERE \" + \\\n",
    "        \"Volume>29200100 AND Symbol='MSFT';\"\n",
    "# выполняем и закрываем подключение\n",
    "items = pd.io.sql.read_sql(query, connection, index_col='index')\n",
    "connection.close()\n",
    "# выводим результат запроса\n",
    "items"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Чтение данных о котировках акций с Google Finance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# импортируем пакет pandas_datareader\n",
    "import pandas_datareader as pdr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "             Open   High    Low  Close    Volume\n",
       "Date                                            \n",
       "2016-09-15  56.15  57.35  55.98  57.19  27062614\n",
       "2016-09-16  57.63  57.63  56.75  57.25  44606965\n",
       "2016-09-19  57.27  57.75  56.85  56.93  20937104\n",
       "2016-09-20  57.35  57.35  56.75  56.81  17383982\n",
       "2016-09-21  57.51  57.85  57.08  57.76  33707272"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем данные с Google и выводим первые 5 строк\n",
    "start = datetime(2017, 4, 1)\n",
    "end = datetime(2017, 4, 30)\n",
    "goog = pdr.data.DataReader(\"MSFT\", 'google', start, end)\n",
    "goog[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Получение данных об  опционах с веб-сервиса Google Finance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# считываем данные об обционах на акции MSFT\n",
    "options = pdr.data.Options('MSFT', 'google')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[datetime.date(2018, 1, 19)]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "options.expiry_dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                             Last    Bid    Ask\n",
       "Strike Expiry     Type Symbol                                  \n",
       "23.0   2018-01-19 call MSFT180119C00023000  50.58  51.90  52.25\n",
       "                  put  MSFT180119P00023000   0.01    NaN   0.02\n",
       "25.0   2018-01-19 call MSFT180119C00025000  48.30  50.15  50.40\n",
       "                  put  MSFT180119P00025000   0.01    NaN   0.02\n",
       "28.0   2018-01-19 call MSFT180119C00028000  45.70  47.15  47.40"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = options.get_options_data(expiry=options.expiry_dates[0])\n",
    "data.iloc[:5,:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Last  Bid   Ask\n",
       "Strike Expiry     Type Symbol                              \n",
       "30.0   2018-01-19 put  MSFT180119P00030000  0.02  NaN  0.02"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# извлекаем все пут-опционы по страйк-цене 30$ \n",
    "# (интересуют лишь первые четыре столбца)\n",
    "data.loc[(30, slice(None), 'put'), :].iloc[0:5, 0:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                            Last  Bid   Ask\n",
       "Strike Expiry     Type Symbol                              \n",
       "30.0   2018-01-19 put  MSFT180119P00030000  0.02  NaN  0.02"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# извлекаем пут-опционы по страйк-цене 80$ между 2017-06-01 и 2017-06-30\n",
    "data.loc[(30, slice('20180119','20180130'), 'put'), :] \\\n",
    "    .iloc[:, 0:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Получение Базы данных по экономической статистике Федерального резервного банка Сент-Луиса"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                GDP\n",
       "DATE               \n",
       "2012-01-01  15973.9\n",
       "2012-04-01  16121.9\n",
       "2012-07-01  16227.9\n",
       "2012-10-01  16297.3\n",
       "2013-01-01  16475.4"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем данные по GDP из FRED\n",
    "gdp = pdr.data.FredReader(\"GDP\",\n",
    "                     date(2012, 1, 1), \n",
    "                     date(2014, 1, 27))\n",
    "gdp.read()[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "            A576RC1A027NBEA\n",
       "DATE                       \n",
       "1929-01-01             50.5\n",
       "1930-01-01             46.2\n",
       "1931-01-01             39.2\n",
       "1932-01-01             30.5\n",
       "1933-01-01             29.0"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# получаем данные по показателю Compensation of employees: Wages and salaries\n",
    "pdr.data.FredReader(\"A576RC1A027NBEA\",\n",
    "                date(1929, 1, 1),\n",
    "                date(2013, 1, 1)).read()[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Получение данных Кеннета Френча"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "         Mkt-RF   SMB   HML   WML    RF\n",
       "Date                                   \n",
       "2010-01   -3.70  2.70 -0.29 -2.23  0.00\n",
       "2010-02    1.24  0.14  0.10  1.59  0.00\n",
       "2010-03    6.30 -0.26  3.18  4.26  0.01\n",
       "2010-04    0.44  3.78  0.77  1.60  0.01\n",
       "2010-05   -9.52  0.17 -2.54 -0.56  0.01"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем набор данных Global Factors из библиотеки Кеннета Френча\n",
    "factors = pdr.data.FamaFrenchReader(\"Global_Factors\").read()\n",
    "factors[0][:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Получение данных Всемирного банка "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                     id                                     name\n",
       "0    1.0.HCount.1.90usd          Poverty Headcount ($1.90 a day)\n",
       "1     1.0.HCount.2.5usd          Poverty Headcount ($2.50 a day)\n",
       "2  1.0.HCount.Mid10to50    Middle Class ($10-50 a day) Headcount\n",
       "3       1.0.HCount.Ofcl  Official Moderate Poverty Rate-National\n",
       "4   1.0.HCount.Poor4uds             Poverty Headcount ($4 a day)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandas_datareader import wb\n",
    "# извлекаем все индикаторы\n",
    "all_indicators = pdr.wb.get_indicators()\n",
    "# выводим первые 5 индикаторов\n",
    "all_indicators.iloc[:5,:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                      id                                               name\n",
       "8983         SE.SCH.LIFE  School life expectancy, primary to tertiary, b...\n",
       "10268  SP.DYN.LE00.FE.IN           Life expectancy at birth, female (years)\n",
       "10269     SP.DYN.LE00.IN            Life expectancy at birth, total (years)\n",
       "10270  SP.DYN.LE00.MA.IN             Life expectancy at birth, male (years)\n",
       "10271  SP.DYN.LE60.FE.IN                  Life expectancy at age 60, female"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# поиск индикаторов, связанных с продолжительностью жизни\n",
    "le_indicators = pdr.wb.search(\"life expectancy\")\n",
    "# выводим первые три строки и первые два столбца\n",
    "le_indicators.iloc[:5,:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          name       capitalCity iso2c\n",
       "0        Aruba        Oranjestad    AW\n",
       "1  Afghanistan             Kabul    AF\n",
       "2       Africa                      A9\n",
       "3       Angola            Luanda    AO\n",
       "4      Albania            Tirane    AL\n",
       "5      Andorra  Andorra la Vella    AD"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# получаем список стран, показываем код и название\n",
    "countries = pdr.wb.get_countries()\n",
    "# выводим фрагмент списка стран\n",
    "countries.loc[0:5,['name', 'capitalCity', 'iso2c']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                    SP.DYN.LE00.IN\n",
       "country       year                \n",
       "Canada        2014       81.956610\n",
       "              2013       81.765049\n",
       "              2012       81.562439\n",
       "              2011       81.448780\n",
       "              2010       81.197561\n",
       "...                            ...\n",
       "United States 1984       74.563415\n",
       "              1983       74.463415\n",
       "              1982       74.360976\n",
       "              1981       74.009756\n",
       "              1980       73.609756\n",
       "\n",
       "[105 rows x 1 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# получаем данные о продолжительности жизни \n",
    "# для всех стран с 1980 по 2014 годы\n",
    "le_data_all = pdr.wb.download(indicator=\"SP.DYN.LE00.IN\", \n",
    "                          start='1980', \n",
    "                          end='2014')\n",
    "le_data_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Canada', 'Mexico', 'United States'], dtype='object', name='country')"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# по умолчанию будут возвращены данные только для США, Канады и Мексики\n",
    "le_data_all.index.levels[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "               SP.DYN.LE00.IN\n",
       "country  year                \n",
       "Aruba    2012       75.205756\n",
       "         2011       75.081390\n",
       "         2010       74.953537\n",
       "         2009       74.818146\n",
       "         2008       74.675732\n",
       "...                       ...\n",
       "Zimbabwe 1984       61.583951\n",
       "         1983       61.148171\n",
       "         1982       60.605512\n",
       "         1981       60.004829\n",
       "         1980       59.388024\n",
       "\n",
       "[8679 rows x 1 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Отключаем предупреждения Anaconda\n",
    "import warnings\n",
    "warnings.simplefilter('ignore')\n",
    "# получаем данные о продолжительности жизни \n",
    "# для всех стран с 1980 по 2014 годы\n",
    "le_data_all = wb.download(indicator=\"SP.DYN.LE00.IN\", \n",
    "                          country = countries['iso2c'],\n",
    "                          start='1980', \n",
    "                          end='2012')\n",
    "le_data_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "               SP.DYN.LE00.IN                      \n",
       "year                     1980       1981       1982\n",
       "country                                            \n",
       "Afghanistan         41.867537  42.526927  43.230732\n",
       "Albania             70.235976  70.454463  70.685122\n",
       "Algeria             58.164024  59.486756  60.786341\n",
       "American Samoa            NaN        NaN        NaN\n",
       "Andorra                   NaN        NaN        NaN"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#le_data_all.pivot(index='country', columns='year')\n",
    "le_data = le_data_all.reset_index().pivot(index='country', \n",
    "                                          columns='year')\n",
    "# смотрим транспонированные данные\n",
    "le_data.iloc[:5,0:3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                year\n",
       "SP.DYN.LE00.IN  1980       Cambodia\n",
       "                1981       Cambodia\n",
       "                1982    Timor-Leste\n",
       "                1983    South Sudan\n",
       "                1984    South Sudan\n",
       "dtype: object"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# определяем для каждого года страну с\n",
    "# наименьшей продолжительностью жизни\n",
    "country_with_least_expectancy = le_data.idxmin(axis=0)\n",
    "country_with_least_expectancy[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                year\n",
       "SP.DYN.LE00.IN  1980    27.738976\n",
       "                1981    33.449927\n",
       "                1982    38.186220\n",
       "                1983    39.666488\n",
       "                1984    39.999537\n",
       "dtype: float64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# определяем для каждого года минимальное значение\n",
    "# продолжительности жизни\n",
    "expectancy_for_least_country = le_data.min(axis=0)\n",
    "expectancy_for_least_country[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          Country  Expectancy\n",
       "year                         \n",
       "1980     Cambodia   27.738976\n",
       "1981     Cambodia   33.449927\n",
       "1982  Timor-Leste   38.186220\n",
       "1983  South Sudan   39.666488\n",
       "1984  South Sudan   39.999537"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# этот программный код объединяет два датафрейма вместе и мы получаем\n",
    "# по каждому году страну с наименьшей продолжительностью жизни и\n",
    "# наименьшее значение продолжительности жизни\n",
    "least = pd.DataFrame(\n",
    "    data = {'Country': country_with_least_expectancy.values,\n",
    "            'Expectancy': expectancy_for_least_country.values},\n",
    "    index = country_with_least_expectancy.index.levels[1])\n",
    "least[:5]"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}