{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Работаем с данными бейсбольных игр"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_game</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>v_name</th>\n",
       "      <th>v_league</th>\n",
       "      <th>v_game_number</th>\n",
       "      <th>h_name</th>\n",
       "      <th>h_league</th>\n",
       "      <th>h_game_number</th>\n",
       "      <th>v_score</th>\n",
       "      <th>...</th>\n",
       "      <th>h_player_7_name</th>\n",
       "      <th>h_player_7_def_pos</th>\n",
       "      <th>h_player_8_id</th>\n",
       "      <th>h_player_8_name</th>\n",
       "      <th>h_player_8_def_pos</th>\n",
       "      <th>h_player_9_id</th>\n",
       "      <th>h_player_9_name</th>\n",
       "      <th>h_player_9_def_pos</th>\n",
       "      <th>additional_info</th>\n",
       "      <th>acquisition_info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18710504</td>\n",
       "      <td>0</td>\n",
       "      <td>Thu</td>\n",
       "      <td>CL1</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>FW1</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Ed Mincher</td>\n",
       "      <td>7.0</td>\n",
       "      <td>mcdej101</td>\n",
       "      <td>James McDermott</td>\n",
       "      <td>8.0</td>\n",
       "      <td>kellb105</td>\n",
       "      <td>Bill Kelly</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18710505</td>\n",
       "      <td>0</td>\n",
       "      <td>Fri</td>\n",
       "      <td>BS1</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>WS3</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>Asa Brainard</td>\n",
       "      <td>1.0</td>\n",
       "      <td>burrh101</td>\n",
       "      <td>Henry Burroughs</td>\n",
       "      <td>9.0</td>\n",
       "      <td>berth101</td>\n",
       "      <td>Henry Berthrong</td>\n",
       "      <td>8.0</td>\n",
       "      <td>HTBF</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18710506</td>\n",
       "      <td>0</td>\n",
       "      <td>Sat</td>\n",
       "      <td>CL1</td>\n",
       "      <td>na</td>\n",
       "      <td>2</td>\n",
       "      <td>RC1</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>Pony Sager</td>\n",
       "      <td>6.0</td>\n",
       "      <td>birdg101</td>\n",
       "      <td>George Bird</td>\n",
       "      <td>7.0</td>\n",
       "      <td>stirg101</td>\n",
       "      <td>Gat Stires</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18710508</td>\n",
       "      <td>0</td>\n",
       "      <td>Mon</td>\n",
       "      <td>CL1</td>\n",
       "      <td>na</td>\n",
       "      <td>3</td>\n",
       "      <td>CH1</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>Ed Duffy</td>\n",
       "      <td>6.0</td>\n",
       "      <td>pinke101</td>\n",
       "      <td>Ed Pinkham</td>\n",
       "      <td>5.0</td>\n",
       "      <td>zettg101</td>\n",
       "      <td>George Zettlein</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18710509</td>\n",
       "      <td>0</td>\n",
       "      <td>Tue</td>\n",
       "      <td>BS1</td>\n",
       "      <td>na</td>\n",
       "      <td>2</td>\n",
       "      <td>TRO</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>Steve Bellan</td>\n",
       "      <td>5.0</td>\n",
       "      <td>pikel101</td>\n",
       "      <td>Lip Pike</td>\n",
       "      <td>3.0</td>\n",
       "      <td>cravb101</td>\n",
       "      <td>Bill Craver</td>\n",
       "      <td>6.0</td>\n",
       "      <td>HTBF</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 161 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       date  number_of_game day_of_week v_name v_league  v_game_number h_name  \\\n",
       "0  18710504               0         Thu    CL1       na              1    FW1   \n",
       "1  18710505               0         Fri    BS1       na              1    WS3   \n",
       "2  18710506               0         Sat    CL1       na              2    RC1   \n",
       "3  18710508               0         Mon    CL1       na              3    CH1   \n",
       "4  18710509               0         Tue    BS1       na              2    TRO   \n",
       "\n",
       "  h_league  h_game_number  v_score       ...         h_player_7_name  \\\n",
       "0       na              1        0       ...              Ed Mincher   \n",
       "1       na              1       20       ...            Asa Brainard   \n",
       "2       na              1       12       ...              Pony Sager   \n",
       "3       na              1       12       ...                Ed Duffy   \n",
       "4       na              1        9       ...            Steve Bellan   \n",
       "\n",
       "   h_player_7_def_pos h_player_8_id  h_player_8_name h_player_8_def_pos  \\\n",
       "0                 7.0      mcdej101  James McDermott                8.0   \n",
       "1                 1.0      burrh101  Henry Burroughs                9.0   \n",
       "2                 6.0      birdg101      George Bird                7.0   \n",
       "3                 6.0      pinke101       Ed Pinkham                5.0   \n",
       "4                 5.0      pikel101         Lip Pike                3.0   \n",
       "\n",
       "  h_player_9_id  h_player_9_name  h_player_9_def_pos  additional_info  \\\n",
       "0      kellb105       Bill Kelly                 9.0              NaN   \n",
       "1      berth101  Henry Berthrong                 8.0             HTBF   \n",
       "2      stirg101       Gat Stires                 9.0              NaN   \n",
       "3      zettg101  George Zettlein                 1.0              NaN   \n",
       "4      cravb101      Bill Craver                 6.0             HTBF   \n",
       "\n",
       "  acquisition_info  \n",
       "0                Y  \n",
       "1                Y  \n",
       "2                Y  \n",
       "3                Y  \n",
       "4                Y  \n",
       "\n",
       "[5 rows x 161 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# отключаем предупреждения Anaconda\n",
    "import warnings\n",
    "warnings.simplefilter('ignore')\n",
    "\n",
    "# импортируем библиотеку pandas\n",
    "import pandas as pd\n",
    "\n",
    "# cчитываем данные и выводим первые 5 наблюдений\n",
    "gl = pd.read_csv('C:/data/game_logs.csv')\n",
    "gl.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 171907 entries, 0 to 171906\n",
      "Columns: 161 entries, date to acquisition_info\n",
      "dtypes: float64(77), int64(6), object(78)\n",
      "memory usage: 860.5 MB\n"
     ]
    }
   ],
   "source": [
    "# выводим точную информацию об использовании памяти\n",
    "gl.info(memory_usage='deep')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Внутреннее представление датафрейма"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Использование памяти в среднем для float столбцов: 1.29 MB\n",
      "Использование памяти в среднем для int столбцов: 0.00 MB\n",
      "Использование памяти в среднем для object столбцов: 9.51 MB\n"
     ]
    }
   ],
   "source": [
    "# посмотрим, сколько памяти в среднем используют \n",
    "# столбцы определенного типа\n",
    "for dtype in ['float','int','object']:\n",
    "    selected_dtype = gl.select_dtypes(include=[dtype])\n",
    "    mean_usage_b = selected_dtype.memory_usage(deep=True).mean()\n",
    "    mean_usage_mb = mean_usage_b / 1024 ** 2\n",
    "    print(\"Использование памяти в среднем для {} столбцов: {:03.2f} MB\".\n",
    "          format(dtype,mean_usage_mb))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Подтипы"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Machine parameters for uint8\n",
      "---------------------------------------------------------------\n",
      "min = 0\n",
      "max = 255\n",
      "---------------------------------------------------------------\n",
      "\n",
      "Machine parameters for int8\n",
      "---------------------------------------------------------------\n",
      "min = -128\n",
      "max = 127\n",
      "---------------------------------------------------------------\n",
      "\n",
      "Machine parameters for int16\n",
      "---------------------------------------------------------------\n",
      "min = -32768\n",
      "max = 32767\n",
      "---------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# импортируем библиотеку numpy\n",
    "import numpy as np\n",
    "\n",
    "# посмотрим минимальное и максимальное значения для\n",
    "# каждого целочисленного типа\n",
    "int_types = [\"uint8\", \"int8\", \"int16\"]\n",
    "for it in int_types:\n",
    "    print(np.iinfo(it))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Оптимизация числовых столбцов с помощью понижающего преобразования"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.00 MB\n",
      "0.00 MB\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>before</th>\n",
       "      <th>after</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [before, after]\n",
       "Index: []"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# мы будем довольно часто подсчитывать использование памяти,\n",
    "# поэтому напишем функцию, которая сэкономит нам немного времени\n",
    "\n",
    "def mem_usage(pandas_obj):\n",
    "    if isinstance(pandas_obj,pd.DataFrame):\n",
    "        usage_b = pandas_obj.memory_usage(deep=True).sum()\n",
    "    else: # предположим, что если это не датафрейм, то серия\n",
    "        usage_b = pandas_obj.memory_usage(deep=True)\n",
    "    usage_mb = usage_b / 1024 ** 2 # преобразуем байты в мегабайты\n",
    "    return \"{:03.2f} MB\".format(usage_mb)\n",
    "\n",
    "# выполняем понижающее преобразование \n",
    "# для столбцов типа int\n",
    "gl_int = gl.select_dtypes(include=['int'])\n",
    "converted_int = gl_int.apply(pd.to_numeric,downcast='unsigned')\n",
    "\n",
    "print(mem_usage(gl_int))\n",
    "print(mem_usage(converted_int))\n",
    "\n",
    "compare_ints = pd.concat([gl_int.dtypes,converted_int.dtypes],axis=1)\n",
    "compare_ints.columns = ['before','after']\n",
    "compare_ints.apply(pd.Series.value_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100.99 MB\n",
      "50.49 MB\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>before</th>\n",
       "      <th>after</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>float32</th>\n",
       "      <td>NaN</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>float64</th>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         before  after\n",
       "float32     NaN   77.0\n",
       "float64    77.0    NaN"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# выполняем понижающее преобразование \n",
    "# для столбцов типа float\n",
    "gl_float = gl.select_dtypes(include=['float'])\n",
    "converted_float = gl_float.apply(pd.to_numeric,downcast='float')\n",
    "\n",
    "print(mem_usage(gl_float))\n",
    "print(mem_usage(converted_float))\n",
    "\n",
    "compare_floats = pd.concat([gl_float.dtypes,converted_float.dtypes],axis=1)\n",
    "compare_floats.columns = ['before','after']\n",
    "compare_floats.apply(pd.Series.value_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "860.50 MB\n",
      "810.01 MB\n"
     ]
    }
   ],
   "source": [
    "# создаем копию исходного датафрейма\n",
    "optimized_gl = gl.copy()\n",
    "\n",
    "# заменяем исходные числовые столбцы\n",
    "# оптимизированными\n",
    "optimized_gl[converted_int.columns] = converted_int\n",
    "optimized_gl[converted_float.columns] = converted_float\n",
    "\n",
    "# смотрим использование памяти\n",
    "print(mem_usage(gl))\n",
    "print(mem_usage(optimized_gl))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Сравнение способов хранения числовых и строковых значений"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "60\n",
      "65\n",
      "74\n",
      "74\n"
     ]
    }
   ],
   "source": [
    "# смотрим размеры отдельно\n",
    "# хранящихся строк\n",
    "from sys import getsizeof\n",
    "\n",
    "s1 = 'working out'\n",
    "s2 = 'memory usage for'\n",
    "s3 = 'strings in python is fun!'\n",
    "s4 = 'strings in python is fun!'\n",
    "\n",
    "for s in [s1, s2, s3, s4]:\n",
    "    print(getsizeof(s))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    60\n",
       "1    65\n",
       "2    74\n",
       "3    74\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# смотрим размеры строк, являющихся\n",
    "# элементами серии\n",
    "obj_series = pd.Series(['working out',\n",
    "                          'memory usage for',\n",
    "                          'strings in python is fun!',\n",
    "                          'strings in python is fun!'])\n",
    "obj_series.apply(getsizeof)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>v_name</th>\n",
       "      <th>v_league</th>\n",
       "      <th>h_name</th>\n",
       "      <th>h_league</th>\n",
       "      <th>day_night</th>\n",
       "      <th>completion</th>\n",
       "      <th>forefeit</th>\n",
       "      <th>protest</th>\n",
       "      <th>park_id</th>\n",
       "      <th>...</th>\n",
       "      <th>h_player_6_id</th>\n",
       "      <th>h_player_6_name</th>\n",
       "      <th>h_player_7_id</th>\n",
       "      <th>h_player_7_name</th>\n",
       "      <th>h_player_8_id</th>\n",
       "      <th>h_player_8_name</th>\n",
       "      <th>h_player_9_id</th>\n",
       "      <th>h_player_9_name</th>\n",
       "      <th>additional_info</th>\n",
       "      <th>acquisition_info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>171907</td>\n",
       "      <td>171907</td>\n",
       "      <td>171907</td>\n",
       "      <td>171907</td>\n",
       "      <td>171907</td>\n",
       "      <td>140150</td>\n",
       "      <td>116</td>\n",
       "      <td>145</td>\n",
       "      <td>180</td>\n",
       "      <td>171907</td>\n",
       "      <td>...</td>\n",
       "      <td>140838</td>\n",
       "      <td>140838</td>\n",
       "      <td>140838</td>\n",
       "      <td>140838</td>\n",
       "      <td>140838</td>\n",
       "      <td>140838</td>\n",
       "      <td>140838</td>\n",
       "      <td>140838</td>\n",
       "      <td>1456</td>\n",
       "      <td>140841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>7</td>\n",
       "      <td>148</td>\n",
       "      <td>7</td>\n",
       "      <td>148</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>116</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>245</td>\n",
       "      <td>...</td>\n",
       "      <td>4774</td>\n",
       "      <td>4720</td>\n",
       "      <td>5253</td>\n",
       "      <td>5197</td>\n",
       "      <td>4760</td>\n",
       "      <td>4710</td>\n",
       "      <td>5193</td>\n",
       "      <td>5142</td>\n",
       "      <td>332</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>Sat</td>\n",
       "      <td>CHN</td>\n",
       "      <td>NL</td>\n",
       "      <td>CHN</td>\n",
       "      <td>NL</td>\n",
       "      <td>D</td>\n",
       "      <td>19450528,,5,10,43</td>\n",
       "      <td>H</td>\n",
       "      <td>V</td>\n",
       "      <td>STL07</td>\n",
       "      <td>...</td>\n",
       "      <td>grimc101</td>\n",
       "      <td>Charlie Grimm</td>\n",
       "      <td>grimc101</td>\n",
       "      <td>Charlie Grimm</td>\n",
       "      <td>lopea102</td>\n",
       "      <td>Al Lopez</td>\n",
       "      <td>spahw101</td>\n",
       "      <td>Warren Spahn</td>\n",
       "      <td>HTBF</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>28891</td>\n",
       "      <td>8870</td>\n",
       "      <td>88866</td>\n",
       "      <td>9024</td>\n",
       "      <td>88867</td>\n",
       "      <td>82724</td>\n",
       "      <td>1</td>\n",
       "      <td>69</td>\n",
       "      <td>90</td>\n",
       "      <td>7022</td>\n",
       "      <td>...</td>\n",
       "      <td>427</td>\n",
       "      <td>427</td>\n",
       "      <td>491</td>\n",
       "      <td>491</td>\n",
       "      <td>676</td>\n",
       "      <td>676</td>\n",
       "      <td>339</td>\n",
       "      <td>339</td>\n",
       "      <td>1112</td>\n",
       "      <td>140841</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4 rows × 78 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       day_of_week  v_name v_league  h_name h_league day_night  \\\n",
       "count       171907  171907   171907  171907   171907    140150   \n",
       "unique           7     148        7     148        7         2   \n",
       "top            Sat     CHN       NL     CHN       NL         D   \n",
       "freq         28891    8870    88866    9024    88867     82724   \n",
       "\n",
       "               completion forefeit protest park_id       ...         \\\n",
       "count                 116      145     180  171907       ...          \n",
       "unique                116        3       5     245       ...          \n",
       "top     19450528,,5,10,43        H       V   STL07       ...          \n",
       "freq                    1       69      90    7022       ...          \n",
       "\n",
       "       h_player_6_id h_player_6_name h_player_7_id h_player_7_name  \\\n",
       "count         140838          140838        140838          140838   \n",
       "unique          4774            4720          5253            5197   \n",
       "top         grimc101   Charlie Grimm      grimc101   Charlie Grimm   \n",
       "freq             427             427           491             491   \n",
       "\n",
       "       h_player_8_id h_player_8_name h_player_9_id h_player_9_name  \\\n",
       "count         140838          140838        140838          140838   \n",
       "unique          4760            4710          5193            5142   \n",
       "top         lopea102        Al Lopez      spahw101    Warren Spahn   \n",
       "freq             676             676           339             339   \n",
       "\n",
       "       additional_info acquisition_info  \n",
       "count             1456           140841  \n",
       "unique             332                1  \n",
       "top               HTBF                Y  \n",
       "freq              1112           140841  \n",
       "\n",
       "[4 rows x 78 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# смотрим количество уникальных значений\n",
    "# по каждому столбцу типа object\n",
    "gl_obj = gl.select_dtypes(include=['object']).copy()\n",
    "gl_obj.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    Thu\n",
      "1    Fri\n",
      "2    Sat\n",
      "3    Mon\n",
      "4    Tue\n",
      "Name: day_of_week, dtype: object\n",
      "0    Thu\n",
      "1    Fri\n",
      "2    Sat\n",
      "3    Mon\n",
      "4    Tue\n",
      "Name: day_of_week, dtype: category\n",
      "Categories (7, object): [Fri, Mon, Sat, Sun, Thu, Tue, Wed]\n"
     ]
    }
   ],
   "source": [
    "# запишем столбец day_of_week как dow\n",
    "dow = gl_obj.day_of_week\n",
    "print(dow.head())\n",
    "\n",
    "# присваиваем столбцу dow тип category\n",
    "dow_cat = dow.astype('category')\n",
    "print(dow_cat.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    4\n",
       "1    0\n",
       "2    2\n",
       "3    1\n",
       "4    5\n",
       "dtype: int8"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# выводим целочисленные значения,\n",
    "# соответствующие исходным значениям\n",
    "dow_cat.head().cat.codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9.84 MB\n",
      "0.16 MB\n"
     ]
    }
   ],
   "source": [
    "# смотрим, сколько памяти использует столбец dow\n",
    "# до и после преобразования в тип category\n",
    "print(mem_usage(dow))\n",
    "print(mem_usage(dow_cat))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "converted_obj = pd.DataFrame()\n",
    "\n",
    "# пишем цикл, которой перебирает каждый столбец object, \n",
    "# проверяет его на соответствие заданному порогу \n",
    "# (количество уникальных значений должно быть меньше 50% \n",
    "# от общего количества значений), и если столбец \n",
    "# удовлетворяет порогу, преобразовывает его в тип category\n",
    "for col in gl_obj.columns:\n",
    "    num_unique_values = len(gl_obj[col].unique())\n",
    "    num_total_values = len(gl_obj[col])\n",
    "    if num_unique_values / num_total_values < 0.5:\n",
    "        converted_obj.loc[:,col] = gl_obj[col].astype('category')\n",
    "    else:\n",
    "        converted_obj.loc[:,col] = gl_obj[col]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "751.64 MB\n",
      "51.67 MB\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Exception ignored in: 'pandas._libs.lib.array_equivalent_object'\n",
      "TypeError: data type not understood\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>before</th>\n",
       "      <th>after</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>object</th>\n",
       "      <td>78.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category</th>\n",
       "      <td>NaN</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          before  after\n",
       "object      78.0    NaN\n",
       "category     NaN   78.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# снова применяем функцию mem_usage, смотрим,\n",
    "# сколько памяти занимают все столбцы типа object\n",
    "# до и после преобразования в тип category\n",
    "print(mem_usage(gl_obj))\n",
    "print(mem_usage(converted_obj))\n",
    "\n",
    "compare_obj = pd.concat([gl_obj.dtypes,converted_obj.dtypes],axis=1)\n",
    "compare_obj.columns = ['before','after']\n",
    "compare_obj.apply(pd.Series.value_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'110.03 MB'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# смотрим, сколько памяти использует датафрейм\n",
    "# после оптимизации типов\n",
    "optimized_gl[converted_obj.columns] = converted_obj\n",
    "mem_usage(optimized_gl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.31 MB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    18710504\n",
       "1    18710505\n",
       "2    18710506\n",
       "3    18710508\n",
       "4    18710509\n",
       "Name: date, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# смотрим, сколько памяти использует\n",
    "# столбец date\n",
    "date = optimized_gl.date\n",
    "print(mem_usage(date))\n",
    "date.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Задаем типы во время считывания данных"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "110.03 MB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0   1871-05-04\n",
       "1   1871-05-05\n",
       "2   1871-05-06\n",
       "3   1871-05-08\n",
       "4   1871-05-09\n",
       "Name: date, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# преобразуем столбец \n",
    "# date в тип datetime \n",
    "optimized_gl['date'] = pd.to_datetime(date,format='%Y%m%d')\n",
    "\n",
    "# смотрим, сколько памяти использует\n",
    "# столбец date\n",
    "print(mem_usage(optimized_gl))\n",
    "optimized_gl.date.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{   'day_of_week': 'category',\n",
      "    'h_game_number': 'int64',\n",
      "    'h_league': 'category',\n",
      "    'h_name': 'category',\n",
      "    'h_score': 'int64',\n",
      "    'number_of_game': 'int64',\n",
      "    'v_game_number': 'int64',\n",
      "    'v_league': 'category',\n",
      "    'v_name': 'category',\n",
      "    'v_score': 'int64'}\n"
     ]
    }
   ],
   "source": [
    "# создаем словарь, в котором ключами будут имена\n",
    "# столбцов, а значениями - типы столбцов\n",
    "dtypes = optimized_gl.drop('date',axis=1).dtypes\n",
    "\n",
    "dtypes_col = dtypes.index\n",
    "dtypes_type = [i.name for i in dtypes.values]\n",
    "\n",
    "column_types = dict(zip(dtypes_col, dtypes_type))\n",
    "\n",
    "# вместо того, чтобы печатать 161 столбец,\n",
    "# выберем 10 пар \"ключ-значение\" из словаря\n",
    "# и красиво распечатаем их, используя prettyprint\n",
    "\n",
    "preview = first2pairs = {key:value for key,value in list(column_types.items())[:10]}\n",
    "import pprint\n",
    "pp = pp = pprint.PrettyPrinter(indent=4)\n",
    "pp.pprint(preview)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "110.02 MB\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_game</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>v_name</th>\n",
       "      <th>v_league</th>\n",
       "      <th>v_game_number</th>\n",
       "      <th>h_name</th>\n",
       "      <th>h_league</th>\n",
       "      <th>h_game_number</th>\n",
       "      <th>v_score</th>\n",
       "      <th>...</th>\n",
       "      <th>h_player_7_name</th>\n",
       "      <th>h_player_7_def_pos</th>\n",
       "      <th>h_player_8_id</th>\n",
       "      <th>h_player_8_name</th>\n",
       "      <th>h_player_8_def_pos</th>\n",
       "      <th>h_player_9_id</th>\n",
       "      <th>h_player_9_name</th>\n",
       "      <th>h_player_9_def_pos</th>\n",
       "      <th>additional_info</th>\n",
       "      <th>acquisition_info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1871-05-04</td>\n",
       "      <td>0</td>\n",
       "      <td>Thu</td>\n",
       "      <td>CL1</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>FW1</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>Ed Mincher</td>\n",
       "      <td>7.0</td>\n",
       "      <td>mcdej101</td>\n",
       "      <td>James McDermott</td>\n",
       "      <td>8.0</td>\n",
       "      <td>kellb105</td>\n",
       "      <td>Bill Kelly</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1871-05-05</td>\n",
       "      <td>0</td>\n",
       "      <td>Fri</td>\n",
       "      <td>BS1</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>WS3</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>Asa Brainard</td>\n",
       "      <td>1.0</td>\n",
       "      <td>burrh101</td>\n",
       "      <td>Henry Burroughs</td>\n",
       "      <td>9.0</td>\n",
       "      <td>berth101</td>\n",
       "      <td>Henry Berthrong</td>\n",
       "      <td>8.0</td>\n",
       "      <td>HTBF</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1871-05-06</td>\n",
       "      <td>0</td>\n",
       "      <td>Sat</td>\n",
       "      <td>CL1</td>\n",
       "      <td>na</td>\n",
       "      <td>2</td>\n",
       "      <td>RC1</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>Pony Sager</td>\n",
       "      <td>6.0</td>\n",
       "      <td>birdg101</td>\n",
       "      <td>George Bird</td>\n",
       "      <td>7.0</td>\n",
       "      <td>stirg101</td>\n",
       "      <td>Gat Stires</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1871-05-08</td>\n",
       "      <td>0</td>\n",
       "      <td>Mon</td>\n",
       "      <td>CL1</td>\n",
       "      <td>na</td>\n",
       "      <td>3</td>\n",
       "      <td>CH1</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>Ed Duffy</td>\n",
       "      <td>6.0</td>\n",
       "      <td>pinke101</td>\n",
       "      <td>Ed Pinkham</td>\n",
       "      <td>5.0</td>\n",
       "      <td>zettg101</td>\n",
       "      <td>George Zettlein</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1871-05-09</td>\n",
       "      <td>0</td>\n",
       "      <td>Tue</td>\n",
       "      <td>BS1</td>\n",
       "      <td>na</td>\n",
       "      <td>2</td>\n",
       "      <td>TRO</td>\n",
       "      <td>na</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>Steve Bellan</td>\n",
       "      <td>5.0</td>\n",
       "      <td>pikel101</td>\n",
       "      <td>Lip Pike</td>\n",
       "      <td>3.0</td>\n",
       "      <td>cravb101</td>\n",
       "      <td>Bill Craver</td>\n",
       "      <td>6.0</td>\n",
       "      <td>HTBF</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 161 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  number_of_game day_of_week v_name v_league  v_game_number  \\\n",
       "0 1871-05-04               0         Thu    CL1       na              1   \n",
       "1 1871-05-05               0         Fri    BS1       na              1   \n",
       "2 1871-05-06               0         Sat    CL1       na              2   \n",
       "3 1871-05-08               0         Mon    CL1       na              3   \n",
       "4 1871-05-09               0         Tue    BS1       na              2   \n",
       "\n",
       "  h_name h_league  h_game_number  v_score       ...         h_player_7_name  \\\n",
       "0    FW1       na              1        0       ...              Ed Mincher   \n",
       "1    WS3       na              1       20       ...            Asa Brainard   \n",
       "2    RC1       na              1       12       ...              Pony Sager   \n",
       "3    CH1       na              1       12       ...                Ed Duffy   \n",
       "4    TRO       na              1        9       ...            Steve Bellan   \n",
       "\n",
       "   h_player_7_def_pos h_player_8_id  h_player_8_name h_player_8_def_pos  \\\n",
       "0                 7.0      mcdej101  James McDermott                8.0   \n",
       "1                 1.0      burrh101  Henry Burroughs                9.0   \n",
       "2                 6.0      birdg101      George Bird                7.0   \n",
       "3                 6.0      pinke101       Ed Pinkham                5.0   \n",
       "4                 5.0      pikel101         Lip Pike                3.0   \n",
       "\n",
       "  h_player_9_id  h_player_9_name  h_player_9_def_pos  additional_info  \\\n",
       "0      kellb105       Bill Kelly                 9.0              NaN   \n",
       "1      berth101  Henry Berthrong                 8.0             HTBF   \n",
       "2      stirg101       Gat Stires                 9.0              NaN   \n",
       "3      zettg101  George Zettlein                 1.0              NaN   \n",
       "4      cravb101      Bill Craver                 6.0             HTBF   \n",
       "\n",
       "  acquisition_info  \n",
       "0                Y  \n",
       "1                Y  \n",
       "2                Y  \n",
       "3                Y  \n",
       "4                Y  \n",
       "\n",
       "[5 rows x 161 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# считываем данные с нужными нам типами\n",
    "read_and_optimized = pd.read_csv('C:/data/game_logs.csv',\n",
    "                                 dtype=column_types,\n",
    "                                 parse_dates=['date'],\n",
    "                                 infer_datetime_format=True)\n",
    "# смотрим использование памяти\n",
    "print(mem_usage(read_and_optimized))\n",
    "read_and_optimized.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
