{
 "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', 60)\n",
    "\n",
    "# считываем данные в DataFrame, используя в качестве\n",
    "# индекса столбец Symbol и записывая только те \n",
    "# столбцы, которые имеют позиции 0, 2, 3, 7\n",
    "sp500 = pd.read_csv(\"C:/data/sp500.csv\", \n",
    "                    index_col='Symbol', \n",
    "                    usecols=[0, 2, 3, 7])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Важность применения индексов"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "        foo  key\n",
       "0  0.126970  100\n",
       "1  0.966718  101\n",
       "2  0.260476  102\n",
       "3  0.897237  103\n",
       "4  0.376750  104"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# создаем DataFame, состоящий из случайных чисел и столбца key\n",
    "np.random.seed(123456)\n",
    "df = pd.DataFrame({'foo':np.random.random(10000), 'key':range(100, 10100)})\n",
    "df[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "           foo    key\n",
       "9999  0.272283  10099"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# отбираем строку, в котором значение столбца key равно 10099\n",
    "df[df.key==10099]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1000 loops, best of 3: 572 µs per loop\n"
     ]
    }
   ],
   "source": [
    "# измеряем время выполнения операции отбора\n",
    "%timeit df[df.key==10099]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "          foo\n",
       "key          \n",
       "100  0.126970\n",
       "101  0.966718\n",
       "102  0.260476\n",
       "103  0.897237\n",
       "104  0.376750"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# превращаем столбец key в index\n",
    "df_with_index = df.set_index(['key'])\n",
    "df_with_index[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "foo    0.272283\n",
       "Name: 10099, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# теперь можно найти это значение\n",
    "# с помощью индекса\n",
    "df_with_index.loc[10099]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10000 loops, best of 3: 115 µs per loop\n"
     ]
    }
   ],
   "source": [
    "# и теперь операция выполняется намного быстрее\n",
    "%timeit df_with_index.loc[10099]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Основной тип Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "           City  Temperature\n",
       "0      Missoula           70\n",
       "1  Philadelphia           80"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# покажем, что столбцы фактически являются индексом\n",
    "temps = pd.DataFrame({ \"City\": [\"Missoula\", \"Philadelphia\"],\n",
    "                       \"Temperature\": [70, 80] })\n",
    "temps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['City', 'Temperature'], dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# мы видим, что столбцы - это индекс\n",
    "temps.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Индексы Int64Index и RangeIndex, в качестве меток используются целые числа"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "    0\n",
       "0  10\n",
       "1  11\n",
       "2  12\n",
       "3  13\n",
       "4  14"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# явно создаем Int64Index\n",
    "df_i64 = pd.DataFrame(np.arange(10, 20), index=np.arange(0, 10))\n",
    "df_i64[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([0, 1, 2, 3, 4, 5, 6, 7, 8, 9], dtype='int64')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# смотрим индекс\n",
    "df_i64.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "    0\n",
       "0  10\n",
       "1  11\n",
       "2  12\n",
       "3  13\n",
       "4  14"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# по умолчанию мы получаем RangeIndex\n",
    "df_range = pd.DataFrame(np.arange(10, 15))\n",
    "df_range[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RangeIndex(start=0, stop=5, step=1)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_range.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Индекс Float64Index, в качестве меток используются числа с плавающей точкой"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "      0\n",
       "0.0   0\n",
       "0.5   5\n",
       "1.0  10\n",
       "1.5  15\n",
       "2.0  20"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# индексы, использующие Float64Index\n",
    "df_f64 = pd.DataFrame(np.arange(0, 1000, 5), \n",
    "                      np.arange(0.0, 100.0, 0.5))\n",
    "df_f64.iloc[:5] # потребуется iloc для отбора первых пяти строк"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Float64Index([ 0.0,  0.5,  1.0,  1.5,  2.0,  2.5,  3.0,\n",
       "               3.5,  4.0,  4.5,\n",
       "              ...\n",
       "              95.0, 95.5, 96.0, 96.5, 97.0, 97.5, 98.0,\n",
       "              98.5, 99.0, 99.5],\n",
       "             dtype='float64', length=200)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_f64.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Представление дискретных интервалов с использованием IntervalIndex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "            A\n",
       "(0.0, 0.5]  1\n",
       "(0.5, 1.0]  2\n",
       "(1.0, 1.5]  3\n",
       "(1.5, 2.0]  4"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# датафрейм с IntervalIndex\n",
    "df_interval = pd.DataFrame({ \"A\": [1, 2, 3, 4]},\n",
    "                    index = pd.IntervalIndex.from_breaks(\n",
    "                        [0, 0.5, 1.0, 1.5, 2.0]))\n",
    "df_interval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "IntervalIndex([(0.0, 0.5], (0.5, 1.0], (1.0, 1.5], (1.5, 2.0]]\n",
       "              closed='right',\n",
       "              dtype='interval[float64]')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_interval.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Категории в качестве индекса – CategoricalIndex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   A  B\n",
       "0  0  a\n",
       "1  1  a\n",
       "2  2  b\n",
       "3  3  b\n",
       "4  4  c\n",
       "5  5  a"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# создаем датафрейм со столбцом, имеющим тип Categorical\n",
    "df_categorical = pd.DataFrame({'A': np.arange(6),\n",
    "                               'B': list('aabbca')})\n",
    "df_categorical['B'] = df_categorical['B'].astype('category', \n",
    "                                          categories=list('cab'))\n",
    "df_categorical"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CategoricalIndex(['a', 'a', 'b', 'b', 'c', 'a'], categories=['c', 'a', 'b'], ordered=False, name='B', dtype='category')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# превращаем категориальный столбец в индекс\n",
    "df_categorical = df_categorical.set_index('B')\n",
    "df_categorical.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   A\n",
       "B   \n",
       "a  0\n",
       "a  1\n",
       "a  5"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ищем значения в категории 'a'\n",
    "df_categorical.loc['a']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Индексирование по датам и времени с помощью DatetimeIndex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2017-05-01 00:00:00    1.239792\n",
       "2017-05-01 01:00:00   -0.400611\n",
       "2017-05-01 02:00:00    0.718247\n",
       "2017-05-01 03:00:00    0.430499\n",
       "2017-05-01 04:00:00    1.155432\n",
       "Freq: H, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# создаем DatetimeIndex на основе диапазона дат\n",
    "rng = pd.date_range('5/1/2017', periods=5, freq='H')\n",
    "ts = pd.Series(np.random.randn(len(rng)), index=rng)\n",
    "ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2017-05-01 00:00:00',\n",
       "               '2017-05-01 01:00:00',\n",
       "               '2017-05-01 02:00:00',\n",
       "               '2017-05-01 03:00:00',\n",
       "               '2017-05-01 04:00:00'],\n",
       "              dtype='datetime64[ns]', freq='H')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Индексирование периодов времени с помощью PeriodIndex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "PeriodIndex(['2017-01', '2017-02', '2017-03'], dtype='period[M]', freq='M')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# явно создаем PeriodIndex\n",
    "periods = pd.PeriodIndex(['2017-1', '2017-2', '2017-3'], freq='M')\n",
    "periods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2017-01   -0.449276\n",
       "2017-02    2.472977\n",
       "2017-03   -0.716023\n",
       "Freq: M, dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# используем индекс в серии\n",
    "period_series = pd.Series(np.random.randn(len(periods)), \n",
    "                          index=periods)\n",
    "period_series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Создание и использование индекса в объекте Series или объекте DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2017-05-01 00:00:00',\n",
       "               '2017-05-01 01:00:00',\n",
       "               '2017-05-01 02:00:00',\n",
       "               '2017-05-01 03:00:00',\n",
       "               '2017-05-01 04:00:00'],\n",
       "              dtype='datetime64[ns]', freq='H')"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# создаем DatetimeIndex\n",
    "date_times = pd.DatetimeIndex(pd.date_range('5/1/2017', \n",
    "                                            periods=5, \n",
    "                                            freq='H'))\n",
    "date_times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                     0\n",
       "2017-05-01 00:00:00  0\n",
       "2017-05-01 01:00:00  1\n",
       "2017-05-01 02:00:00  2\n",
       "2017-05-01 03:00:00  3\n",
       "2017-05-01 04:00:00  4"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# создаем объект DataFrame, используя индекс\n",
    "df_date_times = pd.DataFrame(np.arange(0, len(date_times)), \n",
    "                             index=date_times)\n",
    "df_date_times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                     0\n",
       "2017-06-01 00:00:00  0\n",
       "2017-06-01 01:00:00  1\n",
       "2017-06-01 02:00:00  2\n",
       "2017-06-01 03:00:00  3\n",
       "2017-06-01 04:00:00  4"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# задаем индекс датафрейма\n",
    "df_date_times.index = pd.DatetimeIndex(pd.date_range('6/1/2017', \n",
    "                                                     periods=5, \n",
    "                                                     freq='H'))\n",
    "df_date_times"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Отбор значений с помощью индекса"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    0\n",
       "b    1\n",
       "c    2\n",
       "d    3\n",
       "e    4\n",
       "dtype: int32"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# создаем серию\n",
    "s = pd.Series(np.arange(0, 5), index=list('abcde'))\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ищем по метке индекса\n",
    "s['b']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# явно ищем по метке индекса\n",
    "s.loc['b']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "    a   b\n",
       "v  10  11\n",
       "w  12  13"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# создаем датафрейм с двумя столбцами\n",
    "df = pd.DataFrame([ np.arange(10, 12), \n",
    "                    np.arange(12, 14)], \n",
    "                  columns=list('ab'), \n",
    "                  index=list('vw'))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "v    10\n",
       "w    12\n",
       "Name: a, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# эта строка возвращает столбец 'a'\n",
    "df['a']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    12\n",
       "b    13\n",
       "Name: w, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# возвращает строку 'w' по метке\n",
    "df.loc['w']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b    1\n",
       "c    2\n",
       "d    3\n",
       "dtype: int32"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# создаем срез серии от метки b до метки d\n",
    "s['b':'d']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b    1\n",
       "c    2\n",
       "d    3\n",
       "dtype: int32"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# эта строка явно создает срез от метки b до метки d\n",
    "s.loc['b':'d']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    0\n",
       "c    2\n",
       "e    4\n",
       "dtype: int32"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ищем строки по метке\n",
    "s.loc[['a', 'c', 'e']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Преобразование данных в индекс и получение данных из индекса "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                        Sector   Price  Book Value\n",
       "Symbol                                            \n",
       "MMM                Industrials  141.14      26.668\n",
       "ABT                Health Care   39.60      15.573\n",
       "ABBV               Health Care   53.95       2.954\n",
       "ACN     Information Technology   79.79       8.326\n",
       "ACE                 Financials  102.91      86.897"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# исследуем несколько строк датафрейма sp500\n",
    "sp500[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "  Symbol                  Sector   Price  Book Value\n",
       "0    MMM             Industrials  141.14      26.668\n",
       "1    ABT             Health Care   39.60      15.573\n",
       "2   ABBV             Health Care   53.95       2.954\n",
       "3    ACN  Information Technology   79.79       8.326\n",
       "4    ACE              Financials  102.91      86.897"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# сбрасываем индекс, помещая значения \n",
    "# индекса в столбец\n",
    "index_moved_to_col = sp500.reset_index()\n",
    "index_moved_to_col[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                       Symbol   Price  Book Value\n",
       "Sector                                           \n",
       "Industrials               MMM  141.14      26.668\n",
       "Health Care               ABT   39.60      15.573\n",
       "Health Care              ABBV   53.95       2.954\n",
       "Information Technology    ACN   79.79       8.326\n",
       "Financials                ACE  102.91      86.897"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# а теперь делаем столбец Sector индексом\n",
    "index_moved_to_col.set_index('Sector')[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "             Sector   Price  Book Value\n",
       "Symbol                                 \n",
       "MMM     Industrials  141.14      26.668\n",
       "ABBV    Health Care   53.95       2.954\n",
       "FOO             NaN     NaN         NaN"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# делаем переиндексацию, задав метки MMM, ABBV и FOO\n",
    "reindexed = sp500.reindex(index=['MMM', 'ABBV', 'FOO'])\n",
    "# обратите внимание, что ABT и ACN удалены, а FOO содержит значения NaN\n",
    "reindexed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "         Price  Book Value  NewCol\n",
       "Symbol                            \n",
       "MMM     141.14      26.668     NaN\n",
       "ABT      39.60      15.573     NaN\n",
       "ABBV     53.95       2.954     NaN\n",
       "ACN      79.79       8.326     NaN\n",
       "ACE     102.91      86.897     NaN"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# выполняем переиндексацию столбцов\n",
    "sp500.reindex(columns=['Price', \n",
    "                       'Book Value', \n",
    "                       'NewCol'])[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Иерархическая индексация"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                Price  Book Value\n",
       "Sector                 Symbol                    \n",
       "Industrials            MMM     141.14      26.668\n",
       "Health Care            ABT      39.60      15.573\n",
       "                       ABBV     53.95       2.954\n",
       "Information Technology ACN      79.79       8.326\n",
       "Financials             ACE     102.91      86.897"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# сначала помещаем символы в столбец\n",
    "reindexed = sp500.reset_index()\n",
    "# а теперь индексируем датафрейм sp500 по столбцам Sector и Symbol\n",
    "multi_fi = reindexed.set_index(['Sector', 'Symbol'])\n",
    "multi_fi[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.indexes.multi.MultiIndex"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# наш индекс - это MultiIndex\n",
    "type(multi_fi.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# он имеет два уровня\n",
    "len(multi_fi.index.levels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Consumer Discretionary', 'Consumer Discretionary ',\n",
       "       'Consumer Staples', 'Consumer Staples ', 'Energy',\n",
       "       'Financials', 'Health Care', 'Industrials',\n",
       "       'Industries', 'Information Technology', 'Materials',\n",
       "       'Telecommunications Services', 'Utilities'],\n",
       "      dtype='object', name='Sector')"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# каждый уровень индекса - это индекс\n",
    "multi_fi.index.levels[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['A', 'AA', 'AAPL', 'ABBV', 'ABC', 'ABT', 'ACE',\n",
       "       'ACN', 'ACT', 'ADBE',\n",
       "       ...\n",
       "       'XLNX', 'XOM', 'XRAY', 'XRX', 'XYL', 'YHOO', 'YUM',\n",
       "       'ZION', 'ZMH', 'ZTS'],\n",
       "      dtype='object', name='Symbol', length=500)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# каждый уровень индекса - это индекс\n",
    "multi_fi.index.levels[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Industrials', 'Health Care', 'Health Care',\n",
       "       'Information Technology', 'Financials',\n",
       "       'Health Care', 'Information Technology',\n",
       "       'Utilities', 'Health Care', 'Financials',\n",
       "       ...\n",
       "       'Utilities', 'Information Technology',\n",
       "       'Information Technology', 'Financials',\n",
       "       'Industrials', 'Information Technology',\n",
       "       'Consumer Discretionary', 'Health Care',\n",
       "       'Financials', 'Health Care'],\n",
       "      dtype='object', name='Sector', length=500)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# значения в уровне индекса 0\n",
    "multi_fi.index.get_level_values(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "         Price  Book Value\n",
       "Symbol                    \n",
       "MMM     141.14      26.668\n",
       "ALLE     52.46       0.000\n",
       "APH      95.71      18.315\n",
       "AVY      48.20      15.616\n",
       "BA      132.41      19.870"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# получаем все акции, которые имеют значение Industrials\n",
    "# обратите внимание, что в результатах \n",
    "# индекс уровня 0 не выводится \n",
    "multi_fi.xs('Industrials')[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "             Price  Book Value\n",
       "Sector                        \n",
       "Industrials  52.46         0.0"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# отбираем строки, в которых индекс уровня 1 \n",
    "# (Symbol) имеет значение ALLE\n",
    "# обратите внимание, что в результатах \n",
    "# индекс уровня 1 (Symbol) не выводится \n",
    "multi_fi.xs('ALLE', level=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                     Price  Book Value\n",
       "Sector      Symbol                    \n",
       "Industrials MMM     141.14      26.668\n",
       "            ALLE     52.46       0.000\n",
       "            APH      95.71      18.315\n",
       "            AVY      48.20      15.616\n",
       "            BA      132.41      19.870"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# отбираем строки, в которых индекс уровня 0 \n",
    "# (Sector) имеет значение Industrials, \n",
    "# без удаления уровней\n",
    "multi_fi.xs('Industrials', drop_level=False)[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Price         102.73\n",
       "Book Value      6.79\n",
       "Name: UPS, dtype: float64"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# скомбинируем уровни индексов\n",
    "multi_fi.xs('Industrials').xs('UPS')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Price         102.73\n",
       "Book Value      6.79\n",
       "Name: (Industrials, UPS), dtype: float64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# комбинируем уровни индексов, используя кортеж\n",
    "multi_fi.xs(('Industrials', 'UPS'))"
   ]
  }
 ],
 "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
}
