Выгрузка_txt+excel.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Работа с текстовыми файлами"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd # Импорт библиотеки pandas: при последовательном выполнении всех примеров ниже импорт библиотеки pandas выполняется один раз.\n",
    "countries_data = pd.read_csv('data/countries.csv', sep=';') # Загружаем данные из файла в переменную, создавая объект DataFrame.\n",
    "countries_data.to_csv('data/countries.txt', index=False, sep=' ') # Выгружаем данные из DataFrame в CSV-файл и сохраняем файл в папке data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>population</th>\n",
       "      <th>square</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Англия</th>\n",
       "      <td>56.29</td>\n",
       "      <td>133396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Канада</th>\n",
       "      <td>38.05</td>\n",
       "      <td>9984670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>США</th>\n",
       "      <td>322.28</td>\n",
       "      <td>9826630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Россия</th>\n",
       "      <td>146.24</td>\n",
       "      <td>17125191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Украина</th>\n",
       "      <td>45.50</td>\n",
       "      <td>603628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Беларусь</th>\n",
       "      <td>9.50</td>\n",
       "      <td>207600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Казахстан</th>\n",
       "      <td>17.04</td>\n",
       "      <td>2724902</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           population    square\n",
       "country                        \n",
       "Англия          56.29    133396\n",
       "Канада          38.05   9984670\n",
       "США            322.28   9826630\n",
       "Россия         146.24  17125191\n",
       "Украина         45.50    603628\n",
       "Беларусь         9.50    207600\n",
       "Казахстан       17.04   2724902"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "txt_df = pd.read_table('data/countries.txt', sep=' ', index_col=['country'])# Загружаем данные из файла в переменную, создавая объект DataFrame.\n",
    "display(txt_df) # Выводим содержимое DataFrame на экран"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### РЕШАЕМ ПРОБЛЕМУ С КОДИРОВКОЙ ИСХОДНЫХ ДАННЫХ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>User_943</td>\n",
       "      <td>Accumanst@gmail.com</td>\n",
       "      <td>������</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>User_908</td>\n",
       "      <td>Advismowr@mail.ru</td>\n",
       "      <td>������</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>User_962</td>\n",
       "      <td>Anachso@ukr.net</td>\n",
       "      <td>���������</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>User_973</td>\n",
       "      <td>Antecia@inbox.ru</td>\n",
       "      <td>�����</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>User_902</td>\n",
       "      <td>Balliaryva@ukr.net</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>User_959</td>\n",
       "      <td>UpdatesCurious@yahoo.com</td>\n",
       "      <td>������</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>User_901</td>\n",
       "      <td>V2artierso@mail.ru</td>\n",
       "      <td>�����������</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>User_970</td>\n",
       "      <td>Vashoterlo@bk.ru</td>\n",
       "      <td>�������</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>User_965</td>\n",
       "      <td>Visuareda@yahoo.com</td>\n",
       "      <td>�������</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>User_921</td>\n",
       "      <td>Aavast@ya.ru</td>\n",
       "      <td>������</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            0                           1             2\n",
       "0   User_943         Accumanst@gmail.com         ������\n",
       "1   User_908           Advismowr@mail.ru         ������\n",
       "2   User_962             Anachso@ukr.net      ���������\n",
       "3   User_973            Antecia@inbox.ru          �����\n",
       "4   User_902          Balliaryva@ukr.net               \n",
       "..        ...                         ...           ...\n",
       "95  User_959    UpdatesCurious@yahoo.com         ������\n",
       "96  User_901          V2artierso@mail.ru    �����������\n",
       "97  User_970            Vashoterlo@bk.ru        �������\n",
       "98  User_965         Visuareda@yahoo.com        �������\n",
       "99  User_921                Aavast@ya.ru         ������\n",
       "\n",
       "[100 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data=pd.read_csv('data/ErrorEnCoding.csv.xls', header=None, encoding_errors='replace') # Считываем данные из файла с неизвестной кодировкой в переменную, создавая объект DataFrame.\n",
    "display(data) # Выводим содержимое DataFrame на экран"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Приведённый ниже код поможет нам определить используемую кодировку в файле, степень достоверности, используемый язык."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pip install chardet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import chardet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'encoding': 'KOI8-R', 'confidence': 0.8773902118791048, 'language': 'Russian'}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from chardet.universaldetector import UniversalDetector # Импортируем субмодуль chardet.universaldetector\n",
    "detector = UniversalDetector()\n",
    "with open('data/ErrorEnCoding.csv.xls', 'rb') as fh:\n",
    "    for line in fh:\n",
    "        detector.feed(line)\n",
    "        if detector.done:\n",
    "            break\n",
    "detector.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>User_943</td>\n",
       "      <td>Accumanst@gmail.com</td>\n",
       "      <td>Ижевск</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>User_908</td>\n",
       "      <td>Advismowr@mail.ru</td>\n",
       "      <td>Ижевск</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>User_962</td>\n",
       "      <td>Anachso@ukr.net</td>\n",
       "      <td>Краснодар</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>User_973</td>\n",
       "      <td>Antecia@inbox.ru</td>\n",
       "      <td>Пермь</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>User_902</td>\n",
       "      <td>Balliaryva@ukr.net</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>User_959</td>\n",
       "      <td>UpdatesCurious@yahoo.com</td>\n",
       "      <td>Тюмень</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>User_901</td>\n",
       "      <td>V2artierso@mail.ru</td>\n",
       "      <td>Арзангелтск</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>User_970</td>\n",
       "      <td>Vashoterlo@bk.ru</td>\n",
       "      <td>Воронеж</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>User_965</td>\n",
       "      <td>Visuareda@yahoo.com</td>\n",
       "      <td>Воронеж</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>User_921</td>\n",
       "      <td>Aavast@ya.ru</td>\n",
       "      <td>Ижевск</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            0                           1             2\n",
       "0   User_943         Accumanst@gmail.com         Ижевск\n",
       "1   User_908           Advismowr@mail.ru         Ижевск\n",
       "2   User_962             Anachso@ukr.net      Краснодар\n",
       "3   User_973            Antecia@inbox.ru          Пермь\n",
       "4   User_902          Balliaryva@ukr.net               \n",
       "..        ...                         ...           ...\n",
       "95  User_959    UpdatesCurious@yahoo.com         Тюмень\n",
       "96  User_901          V2artierso@mail.ru    Арзангелтск\n",
       "97  User_970            Vashoterlo@bk.ru        Воронеж\n",
       "98  User_965         Visuareda@yahoo.com        Воронеж\n",
       "99  User_921                Aavast@ya.ru         Ижевск\n",
       "\n",
       "[100 rows x 3 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Создаём DataFrame из файла, явно указав кодировку символов, и выводим его содержимое на экран.\n",
    "data=pd.read_csv('data/ErrorEnCoding.csv.xls', encoding='koi8-r', header=None )\n",
    "display(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ЧТЕНИЕ ФАЙЛА ПО ССЫЛКЕ, ИСПОЛЬЗУЯ ФУНКЦИЮ READ_TABLE()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ssl\n",
    "\n",
    "try:\n",
    "    _create_unverified_https_context = ssl._create_unverified_context\n",
    "except AttributeError:\n",
    "    # Legacy Python that doesn't verify HTTPS certificates by default\n",
    "    pass\n",
    "else:\n",
    "    # Handle target environment that doesn't support HTTPS verification\n",
    "    ssl._create_default_https_context = _create_unverified_https_context"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Suburb</th>\n",
       "      <th>Address</th>\n",
       "      <th>Rooms</th>\n",
       "      <th>Type</th>\n",
       "      <th>Price</th>\n",
       "      <th>Method</th>\n",
       "      <th>SellerG</th>\n",
       "      <th>Date</th>\n",
       "      <th>Distance</th>\n",
       "      <th>...</th>\n",
       "      <th>Bathroom</th>\n",
       "      <th>Car</th>\n",
       "      <th>Landsize</th>\n",
       "      <th>BuildingArea</th>\n",
       "      <th>YearBuilt</th>\n",
       "      <th>CouncilArea</th>\n",
       "      <th>Lattitude</th>\n",
       "      <th>Longtitude</th>\n",
       "      <th>Regionname</th>\n",
       "      <th>Propertycount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Abbotsford</td>\n",
       "      <td>85 Turner St</td>\n",
       "      <td>2</td>\n",
       "      <td>h</td>\n",
       "      <td>1480000.0</td>\n",
       "      <td>S</td>\n",
       "      <td>Biggin</td>\n",
       "      <td>3/12/2016</td>\n",
       "      <td>2.5</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>202.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yarra</td>\n",
       "      <td>-37.79960</td>\n",
       "      <td>144.99840</td>\n",
       "      <td>Northern Metropolitan</td>\n",
       "      <td>4019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Abbotsford</td>\n",
       "      <td>25 Bloomburg St</td>\n",
       "      <td>2</td>\n",
       "      <td>h</td>\n",
       "      <td>1035000.0</td>\n",
       "      <td>S</td>\n",
       "      <td>Biggin</td>\n",
       "      <td>4/02/2016</td>\n",
       "      <td>2.5</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>Yarra</td>\n",
       "      <td>-37.80790</td>\n",
       "      <td>144.99340</td>\n",
       "      <td>Northern Metropolitan</td>\n",
       "      <td>4019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Abbotsford</td>\n",
       "      <td>5 Charles St</td>\n",
       "      <td>3</td>\n",
       "      <td>h</td>\n",
       "      <td>1465000.0</td>\n",
       "      <td>SP</td>\n",
       "      <td>Biggin</td>\n",
       "      <td>4/03/2017</td>\n",
       "      <td>2.5</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>134.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>Yarra</td>\n",
       "      <td>-37.80930</td>\n",
       "      <td>144.99440</td>\n",
       "      <td>Northern Metropolitan</td>\n",
       "      <td>4019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Abbotsford</td>\n",
       "      <td>40 Federation La</td>\n",
       "      <td>3</td>\n",
       "      <td>h</td>\n",
       "      <td>850000.0</td>\n",
       "      <td>PI</td>\n",
       "      <td>Biggin</td>\n",
       "      <td>4/03/2017</td>\n",
       "      <td>2.5</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yarra</td>\n",
       "      <td>-37.79690</td>\n",
       "      <td>144.99690</td>\n",
       "      <td>Northern Metropolitan</td>\n",
       "      <td>4019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>Abbotsford</td>\n",
       "      <td>55a Park St</td>\n",
       "      <td>4</td>\n",
       "      <td>h</td>\n",
       "      <td>1600000.0</td>\n",
       "      <td>VB</td>\n",
       "      <td>Nelson</td>\n",
       "      <td>4/06/2016</td>\n",
       "      <td>2.5</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>Yarra</td>\n",
       "      <td>-37.80720</td>\n",
       "      <td>144.99410</td>\n",
       "      <td>Northern Metropolitan</td>\n",
       "      <td>4019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18391</th>\n",
       "      <td>23540</td>\n",
       "      <td>Williamstown</td>\n",
       "      <td>8/2 Thompson St</td>\n",
       "      <td>2</td>\n",
       "      <td>t</td>\n",
       "      <td>622500.0</td>\n",
       "      <td>SP</td>\n",
       "      <td>Greg</td>\n",
       "      <td>26/08/2017</td>\n",
       "      <td>6.8</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>89.0</td>\n",
       "      <td>2010.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-37.86393</td>\n",
       "      <td>144.90484</td>\n",
       "      <td>Western Metropolitan</td>\n",
       "      <td>6380.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18392</th>\n",
       "      <td>23541</td>\n",
       "      <td>Williamstown</td>\n",
       "      <td>96 Verdon St</td>\n",
       "      <td>4</td>\n",
       "      <td>h</td>\n",
       "      <td>2500000.0</td>\n",
       "      <td>PI</td>\n",
       "      <td>Sweeney</td>\n",
       "      <td>26/08/2017</td>\n",
       "      <td>6.8</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>866.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>1920.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-37.85908</td>\n",
       "      <td>144.89299</td>\n",
       "      <td>Western Metropolitan</td>\n",
       "      <td>6380.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18393</th>\n",
       "      <td>23544</td>\n",
       "      <td>Yallambie</td>\n",
       "      <td>17 Amaroo Wy</td>\n",
       "      <td>4</td>\n",
       "      <td>h</td>\n",
       "      <td>1100000.0</td>\n",
       "      <td>S</td>\n",
       "      <td>Buckingham</td>\n",
       "      <td>26/08/2017</td>\n",
       "      <td>12.7</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-37.72006</td>\n",
       "      <td>145.10547</td>\n",
       "      <td>Northern Metropolitan</td>\n",
       "      <td>1369.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18394</th>\n",
       "      <td>23545</td>\n",
       "      <td>Yarraville</td>\n",
       "      <td>6 Agnes St</td>\n",
       "      <td>4</td>\n",
       "      <td>h</td>\n",
       "      <td>1285000.0</td>\n",
       "      <td>SP</td>\n",
       "      <td>Village</td>\n",
       "      <td>26/08/2017</td>\n",
       "      <td>6.3</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>362.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>1920.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-37.81188</td>\n",
       "      <td>144.88449</td>\n",
       "      <td>Western Metropolitan</td>\n",
       "      <td>6543.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18395</th>\n",
       "      <td>23546</td>\n",
       "      <td>Yarraville</td>\n",
       "      <td>33 Freeman St</td>\n",
       "      <td>4</td>\n",
       "      <td>h</td>\n",
       "      <td>1050000.0</td>\n",
       "      <td>VB</td>\n",
       "      <td>Village</td>\n",
       "      <td>26/08/2017</td>\n",
       "      <td>6.3</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>139.0</td>\n",
       "      <td>1950.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-37.81829</td>\n",
       "      <td>144.87404</td>\n",
       "      <td>Western Metropolitan</td>\n",
       "      <td>6543.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18396 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0        Suburb           Address  Rooms Type      Price  \\\n",
       "0               1    Abbotsford      85 Turner St      2    h  1480000.0   \n",
       "1               2    Abbotsford   25 Bloomburg St      2    h  1035000.0   \n",
       "2               4    Abbotsford      5 Charles St      3    h  1465000.0   \n",
       "3               5    Abbotsford  40 Federation La      3    h   850000.0   \n",
       "4               6    Abbotsford       55a Park St      4    h  1600000.0   \n",
       "...           ...           ...               ...    ...  ...        ...   \n",
       "18391       23540  Williamstown   8/2 Thompson St      2    t   622500.0   \n",
       "18392       23541  Williamstown      96 Verdon St      4    h  2500000.0   \n",
       "18393       23544     Yallambie      17 Amaroo Wy      4    h  1100000.0   \n",
       "18394       23545    Yarraville        6 Agnes St      4    h  1285000.0   \n",
       "18395       23546    Yarraville     33 Freeman St      4    h  1050000.0   \n",
       "\n",
       "      Method     SellerG        Date  Distance  ...  Bathroom  Car  Landsize  \\\n",
       "0          S      Biggin   3/12/2016       2.5  ...       1.0  1.0     202.0   \n",
       "1          S      Biggin   4/02/2016       2.5  ...       1.0  0.0     156.0   \n",
       "2         SP      Biggin   4/03/2017       2.5  ...       2.0  0.0     134.0   \n",
       "3         PI      Biggin   4/03/2017       2.5  ...       2.0  1.0      94.0   \n",
       "4         VB      Nelson   4/06/2016       2.5  ...       1.0  2.0     120.0   \n",
       "...      ...         ...         ...       ...  ...       ...  ...       ...   \n",
       "18391     SP        Greg  26/08/2017       6.8  ...       2.0  1.0       NaN   \n",
       "18392     PI     Sweeney  26/08/2017       6.8  ...       1.0  5.0     866.0   \n",
       "18393      S  Buckingham  26/08/2017      12.7  ...       3.0  2.0       NaN   \n",
       "18394     SP     Village  26/08/2017       6.3  ...       1.0  1.0     362.0   \n",
       "18395     VB     Village  26/08/2017       6.3  ...       2.0  2.0       NaN   \n",
       "\n",
       "       BuildingArea  YearBuilt  CouncilArea  Lattitude Longtitude  \\\n",
       "0               NaN        NaN        Yarra  -37.79960  144.99840   \n",
       "1              79.0     1900.0        Yarra  -37.80790  144.99340   \n",
       "2             150.0     1900.0        Yarra  -37.80930  144.99440   \n",
       "3               NaN        NaN        Yarra  -37.79690  144.99690   \n",
       "4             142.0     2014.0        Yarra  -37.80720  144.99410   \n",
       "...             ...        ...          ...        ...        ...   \n",
       "18391          89.0     2010.0          NaN  -37.86393  144.90484   \n",
       "18392         157.0     1920.0          NaN  -37.85908  144.89299   \n",
       "18393           NaN        NaN          NaN  -37.72006  145.10547   \n",
       "18394         112.0     1920.0          NaN  -37.81188  144.88449   \n",
       "18395         139.0     1950.0          NaN  -37.81829  144.87404   \n",
       "\n",
       "                  Regionname  Propertycount  \n",
       "0      Northern Metropolitan         4019.0  \n",
       "1      Northern Metropolitan         4019.0  \n",
       "2      Northern Metropolitan         4019.0  \n",
       "3      Northern Metropolitan         4019.0  \n",
       "4      Northern Metropolitan         4019.0  \n",
       "...                      ...            ...  \n",
       "18391   Western Metropolitan         6380.0  \n",
       "18392   Western Metropolitan         6380.0  \n",
       "18393  Northern Metropolitan         1369.0  \n",
       "18394   Western Metropolitan         6543.0  \n",
       "18395   Western Metropolitan         6543.0  \n",
       "\n",
       "[18396 rows x 22 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data = pd.read_table('https://raw.githubusercontent.com/esabunor/MLWorkspace/master/melb_data.csv', sep=',')\n",
    "display(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ЧТЕНИЕ/ЗАПИСЬ АРХИВИРОВАННЫХ CSV-ФАЙЛОВ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gender</th>\n",
       "      <th>race/ethnicity</th>\n",
       "      <th>parental level of education</th>\n",
       "      <th>lunch</th>\n",
       "      <th>test preparation course</th>\n",
       "      <th>math score</th>\n",
       "      <th>reading score</th>\n",
       "      <th>writing score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>bachelor's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>72</td>\n",
       "      <td>72</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>female</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>completed</td>\n",
       "      <td>69</td>\n",
       "      <td>90</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>female</td>\n",
       "      <td>group B</td>\n",
       "      <td>master's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>90</td>\n",
       "      <td>95</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>male</td>\n",
       "      <td>group A</td>\n",
       "      <td>associate's degree</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>none</td>\n",
       "      <td>47</td>\n",
       "      <td>57</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>male</td>\n",
       "      <td>group C</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>none</td>\n",
       "      <td>76</td>\n",
       "      <td>78</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>female</td>\n",
       "      <td>group E</td>\n",
       "      <td>master's degree</td>\n",
       "      <td>standard</td>\n",
       "      <td>completed</td>\n",
       "      <td>88</td>\n",
       "      <td>99</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>male</td>\n",
       "      <td>group C</td>\n",
       "      <td>high school</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>none</td>\n",
       "      <td>62</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>female</td>\n",
       "      <td>group C</td>\n",
       "      <td>high school</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>completed</td>\n",
       "      <td>59</td>\n",
       "      <td>71</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>female</td>\n",
       "      <td>group D</td>\n",
       "      <td>some college</td>\n",
       "      <td>standard</td>\n",
       "      <td>completed</td>\n",
       "      <td>68</td>\n",
       "      <td>78</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>female</td>\n",
       "      <td>group D</td>\n",
       "      <td>some college</td>\n",
       "      <td>free/reduced</td>\n",
       "      <td>none</td>\n",
       "      <td>77</td>\n",
       "      <td>86</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     gender race/ethnicity parental level of education         lunch  \\\n",
       "0    female        group B           bachelor's degree      standard   \n",
       "1    female        group C                some college      standard   \n",
       "2    female        group B             master's degree      standard   \n",
       "3      male        group A          associate's degree  free/reduced   \n",
       "4      male        group C                some college      standard   \n",
       "..      ...            ...                         ...           ...   \n",
       "995  female        group E             master's degree      standard   \n",
       "996    male        group C                 high school  free/reduced   \n",
       "997  female        group C                 high school  free/reduced   \n",
       "998  female        group D                some college      standard   \n",
       "999  female        group D                some college  free/reduced   \n",
       "\n",
       "    test preparation course  math score  reading score  writing score  \n",
       "0                      none          72             72             74  \n",
       "1                 completed          69             90             88  \n",
       "2                      none          90             95             93  \n",
       "3                      none          47             57             44  \n",
       "4                      none          76             78             75  \n",
       "..                      ...         ...            ...            ...  \n",
       "995               completed          88             99             95  \n",
       "996                    none          62             55             55  \n",
       "997               completed          59             71             65  \n",
       "998               completed          68             78             77  \n",
       "999                    none          77             86             86  \n",
       "\n",
       "[1000 rows x 8 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data = pd.read_csv('data/students_performance.csv')\n",
    "display(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Данные из DataFrame data запишем в CSV-файл, упакуем полученный файл в zip-архив «на лету» и сохраним полученный архив в папке data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "compression_opts = dict(method='zip', archive_name='out.csv') # Определяем параметры архивирования: метод сжатия, имя файла в архиве.\n",
    "data.to_csv('data/out.zip', index=False, compression=compression_opts)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Работа с файлами Excel"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### СЧИТЫВАНИЕ ДАННЫХ ИЗ ФАЙЛА EXCEL\n",
    "\n",
    "##### Основные параметры метода read_excel()\n",
    "- io — первый параметр, в который мы передаём адрес файла\n",
    "- sheet_name —  ссылка на лист в Excel-файле (возможные значения данного параметра: 0 — значение по умолчанию, загружается первый лист; 'Sheet1' — можно передать название листа; [0, 1, 'Sheet3'] — список, содержащий номера или названия листов; в таком случае Pandas вернёт словарь, в котором ключами будут номера или названия листов, а значениями — их содержимое в виде DataFrame; None — если передать такое значение, то pandas прочитает все листы и вернёт их в виде словаря, как в предыдущем пункте).\n",
    "- na_values — список значений, которые будут считаться пропусками ( ‘’, ‘#N/A’, ‘ N/A’, ‘#NA’, ‘-1.#IND’, ‘-1.#QNAN’, ‘-NaN’, ‘-nan’, ‘1.#IND’, ‘1.#QNAN’, ‘NA’, ‘NULL’, ‘NaN’, ‘n/a’, ‘nan’, ‘null’)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pip install openpyxl --upgrade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Student ID</th>\n",
       "      <th>Student name</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Аня</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Катя</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Маша</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Миша</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Женя</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Student ID Student name  Grade\n",
       "0           1          Аня      8\n",
       "1           2         Катя      9\n",
       "2           3         Маша      7\n",
       "3           4         Миша      4\n",
       "4           5         Женя      8"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "grades = pd.read_excel('data/grades.xlsx')\n",
    "display(grades.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### СЧИТЫВАНИЕ ДАННЫХ ИЗ ФАЙЛА EXCEL ПО ССЫЛКЕ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>January 2020 Sales</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Global Sales Report</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Location</td>\n",
       "      <td>Oranges</td>\n",
       "      <td>Apples</td>\n",
       "      <td>Bananas</td>\n",
       "      <td>Blueberries</td>\n",
       "      <td>Total</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Toronto</td>\n",
       "      <td>7651</td>\n",
       "      <td>4422</td>\n",
       "      <td>8580</td>\n",
       "      <td>3679</td>\n",
       "      <td>24332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>273</td>\n",
       "      <td>2998</td>\n",
       "      <td>9890</td>\n",
       "      <td>7293</td>\n",
       "      <td>20454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Atlanta</td>\n",
       "      <td>3758</td>\n",
       "      <td>6752</td>\n",
       "      <td>4599</td>\n",
       "      <td>4149</td>\n",
       "      <td>19258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>New York</td>\n",
       "      <td>4019</td>\n",
       "      <td>8796</td>\n",
       "      <td>8486</td>\n",
       "      <td>9188</td>\n",
       "      <td>30489</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    January 2020 Sales Unnamed: 1 Unnamed: 2 Unnamed: 3   Unnamed: 4  \\\n",
       "0  Global Sales Report        NaN        NaN        NaN          NaN   \n",
       "1                  NaN        NaN        NaN        NaN          NaN   \n",
       "2             Location    Oranges     Apples    Bananas  Blueberries   \n",
       "3              Toronto       7651       4422       8580         3679   \n",
       "4          Los Angeles        273       2998       9890         7293   \n",
       "5              Atlanta       3758       6752       4599         4149   \n",
       "6             New York       4019       8796       8486         9188   \n",
       "\n",
       "  Unnamed: 5  \n",
       "0        NaN  \n",
       "1        NaN  \n",
       "2      Total  \n",
       "3      24332  \n",
       "4      20454  \n",
       "5      19258  \n",
       "6      30489  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "data = pd.read_excel('https://github.com/asaydn/test/raw/master/january.xlsx')\n",
    "display(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Student ID</th>\n",
       "      <th>Student name</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Аня</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Катя</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Маша</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Миша</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Женя</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Student ID Student name  Grade\n",
       "0           1          Аня      7\n",
       "1           2         Катя      5\n",
       "2           3         Маша      9\n",
       "3           4         Миша      8\n",
       "4           5         Женя      9"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "grades = pd.read_excel('data/grades.xlsx', sheet_name='ML')\n",
    "display(grades.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Student ID</th>\n",
       "      <th>Student name</th>\n",
       "      <th>Grade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Аня</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Катя</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Маша</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Миша</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Женя</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Student ID Student name  Grade\n",
       "0           1          Аня      8\n",
       "1           2         Катя      9\n",
       "2           3         Маша      7\n",
       "3           4         Миша      4\n",
       "4           5         Женя      8"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "grades = pd.read_excel('data/grades.xlsx', sheet_name='Maths')\n",
    "display(grades.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ВЫГРУЗКА ДАННЫХ ИЗ DATAFRAME В EXCEL-ФАЙЛ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "grades.to_excel('data/grades_new.xlsx') # Сохраняем данные из DataFrame grades в файл grades_new.xlsx в папке data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "grades.to_excel('data/grades_new.xlsx', sheet_name='Example', index=False) # Сохраняем данные из DataFrame grades в файл grades_new.xlsx (на листе 'Example') в папке data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Задания "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "Worksheet named 'ratings' not found",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[19], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m ratings \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39;49mread_excel(\u001b[39m'\u001b[39;49m\u001b[39mdata/ratings_movies.xlsx\u001b[39;49m\u001b[39m'\u001b[39;49m, sheet_name\u001b[39m=\u001b[39;49m\u001b[39m'\u001b[39;49m\u001b[39mratings\u001b[39;49m\u001b[39m'\u001b[39;49m)\n\u001b[1;32m      2\u001b[0m display(ratings\u001b[39m.\u001b[39mhead())\n",
      "File \u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/excel/_base.py:486\u001b[0m, in \u001b[0;36mread_excel\u001b[0;34m(io, sheet_name, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, date_format, thousands, decimal, comment, skipfooter, storage_options, dtype_backend)\u001b[0m\n\u001b[1;32m    480\u001b[0m     \u001b[39mraise\u001b[39;00m \u001b[39mValueError\u001b[39;00m(\n\u001b[1;32m    481\u001b[0m         \u001b[39m\"\u001b[39m\u001b[39mEngine should not be specified when passing \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m    482\u001b[0m         \u001b[39m\"\u001b[39m\u001b[39man ExcelFile - ExcelFile already has the engine set\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m    483\u001b[0m     )\n\u001b[1;32m    485\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m--> 486\u001b[0m     data \u001b[39m=\u001b[39m io\u001b[39m.\u001b[39;49mparse(\n\u001b[1;32m    487\u001b[0m         sheet_name\u001b[39m=\u001b[39;49msheet_name,\n\u001b[1;32m    488\u001b[0m         header\u001b[39m=\u001b[39;49mheader,\n\u001b[1;32m    489\u001b[0m         names\u001b[39m=\u001b[39;49mnames,\n\u001b[1;32m    490\u001b[0m         index_col\u001b[39m=\u001b[39;49mindex_col,\n\u001b[1;32m    491\u001b[0m         usecols\u001b[39m=\u001b[39;49musecols,\n\u001b[1;32m    492\u001b[0m         dtype\u001b[39m=\u001b[39;49mdtype,\n\u001b[1;32m    493\u001b[0m         converters\u001b[39m=\u001b[39;49mconverters,\n\u001b[1;32m    494\u001b[0m         true_values\u001b[39m=\u001b[39;49mtrue_values,\n\u001b[1;32m    495\u001b[0m         false_values\u001b[39m=\u001b[39;49mfalse_values,\n\u001b[1;32m    496\u001b[0m         skiprows\u001b[39m=\u001b[39;49mskiprows,\n\u001b[1;32m    497\u001b[0m         nrows\u001b[39m=\u001b[39;49mnrows,\n\u001b[1;32m    498\u001b[0m         na_values\u001b[39m=\u001b[39;49mna_values,\n\u001b[1;32m    499\u001b[0m         keep_default_na\u001b[39m=\u001b[39;49mkeep_default_na,\n\u001b[1;32m    500\u001b[0m         na_filter\u001b[39m=\u001b[39;49mna_filter,\n\u001b[1;32m    501\u001b[0m         verbose\u001b[39m=\u001b[39;49mverbose,\n\u001b[1;32m    502\u001b[0m         parse_dates\u001b[39m=\u001b[39;49mparse_dates,\n\u001b[1;32m    503\u001b[0m         date_parser\u001b[39m=\u001b[39;49mdate_parser,\n\u001b[1;32m    504\u001b[0m         date_format\u001b[39m=\u001b[39;49mdate_format,\n\u001b[1;32m    505\u001b[0m         thousands\u001b[39m=\u001b[39;49mthousands,\n\u001b[1;32m    506\u001b[0m         decimal\u001b[39m=\u001b[39;49mdecimal,\n\u001b[1;32m    507\u001b[0m         comment\u001b[39m=\u001b[39;49mcomment,\n\u001b[1;32m    508\u001b[0m         skipfooter\u001b[39m=\u001b[39;49mskipfooter,\n\u001b[1;32m    509\u001b[0m         dtype_backend\u001b[39m=\u001b[39;49mdtype_backend,\n\u001b[1;32m    510\u001b[0m     )\n\u001b[1;32m    511\u001b[0m \u001b[39mfinally\u001b[39;00m:\n\u001b[1;32m    512\u001b[0m     \u001b[39m# make sure to close opened file handles\u001b[39;00m\n\u001b[1;32m    513\u001b[0m     \u001b[39mif\u001b[39;00m should_close:\n",
      "File \u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/excel/_base.py:1551\u001b[0m, in \u001b[0;36mExcelFile.parse\u001b[0;34m(self, sheet_name, header, names, index_col, usecols, converters, true_values, false_values, skiprows, nrows, na_values, parse_dates, date_parser, date_format, thousands, comment, skipfooter, dtype_backend, **kwds)\u001b[0m\n\u001b[1;32m   1518\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39mparse\u001b[39m(\n\u001b[1;32m   1519\u001b[0m     \u001b[39mself\u001b[39m,\n\u001b[1;32m   1520\u001b[0m     sheet_name: \u001b[39mstr\u001b[39m \u001b[39m|\u001b[39m \u001b[39mint\u001b[39m \u001b[39m|\u001b[39m \u001b[39mlist\u001b[39m[\u001b[39mint\u001b[39m] \u001b[39m|\u001b[39m \u001b[39mlist\u001b[39m[\u001b[39mstr\u001b[39m] \u001b[39m|\u001b[39m \u001b[39mNone\u001b[39;00m \u001b[39m=\u001b[39m \u001b[39m0\u001b[39m,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1538\u001b[0m     \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mkwds,\n\u001b[1;32m   1539\u001b[0m ) \u001b[39m-\u001b[39m\u001b[39m>\u001b[39m DataFrame \u001b[39m|\u001b[39m \u001b[39mdict\u001b[39m[\u001b[39mstr\u001b[39m, DataFrame] \u001b[39m|\u001b[39m \u001b[39mdict\u001b[39m[\u001b[39mint\u001b[39m, DataFrame]:\n\u001b[1;32m   1540\u001b[0m \u001b[39m    \u001b[39m\u001b[39m\"\"\"\u001b[39;00m\n\u001b[1;32m   1541\u001b[0m \u001b[39m    Parse specified sheet(s) into a DataFrame.\u001b[39;00m\n\u001b[1;32m   1542\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   1549\u001b[0m \u001b[39m        DataFrame from the passed in Excel file.\u001b[39;00m\n\u001b[1;32m   1550\u001b[0m \u001b[39m    \"\"\"\u001b[39;00m\n\u001b[0;32m-> 1551\u001b[0m     \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_reader\u001b[39m.\u001b[39;49mparse(\n\u001b[1;32m   1552\u001b[0m         sheet_name\u001b[39m=\u001b[39;49msheet_name,\n\u001b[1;32m   1553\u001b[0m         header\u001b[39m=\u001b[39;49mheader,\n\u001b[1;32m   1554\u001b[0m         names\u001b[39m=\u001b[39;49mnames,\n\u001b[1;32m   1555\u001b[0m         index_col\u001b[39m=\u001b[39;49mindex_col,\n\u001b[1;32m   1556\u001b[0m         usecols\u001b[39m=\u001b[39;49musecols,\n\u001b[1;32m   1557\u001b[0m         converters\u001b[39m=\u001b[39;49mconverters,\n\u001b[1;32m   1558\u001b[0m         true_values\u001b[39m=\u001b[39;49mtrue_values,\n\u001b[1;32m   1559\u001b[0m         false_values\u001b[39m=\u001b[39;49mfalse_values,\n\u001b[1;32m   1560\u001b[0m         skiprows\u001b[39m=\u001b[39;49mskiprows,\n\u001b[1;32m   1561\u001b[0m         nrows\u001b[39m=\u001b[39;49mnrows,\n\u001b[1;32m   1562\u001b[0m         na_values\u001b[39m=\u001b[39;49mna_values,\n\u001b[1;32m   1563\u001b[0m         parse_dates\u001b[39m=\u001b[39;49mparse_dates,\n\u001b[1;32m   1564\u001b[0m         date_parser\u001b[39m=\u001b[39;49mdate_parser,\n\u001b[1;32m   1565\u001b[0m         date_format\u001b[39m=\u001b[39;49mdate_format,\n\u001b[1;32m   1566\u001b[0m         thousands\u001b[39m=\u001b[39;49mthousands,\n\u001b[1;32m   1567\u001b[0m         comment\u001b[39m=\u001b[39;49mcomment,\n\u001b[1;32m   1568\u001b[0m         skipfooter\u001b[39m=\u001b[39;49mskipfooter,\n\u001b[1;32m   1569\u001b[0m         dtype_backend\u001b[39m=\u001b[39;49mdtype_backend,\n\u001b[1;32m   1570\u001b[0m         \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49mkwds,\n\u001b[1;32m   1571\u001b[0m     )\n",
      "File \u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/excel/_base.py:746\u001b[0m, in \u001b[0;36mBaseExcelReader.parse\u001b[0;34m(self, sheet_name, header, names, index_col, usecols, dtype, true_values, false_values, skiprows, nrows, na_values, verbose, parse_dates, date_parser, date_format, thousands, decimal, comment, skipfooter, dtype_backend, **kwds)\u001b[0m\n\u001b[1;32m    743\u001b[0m     \u001b[39mprint\u001b[39m(\u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39mReading sheet \u001b[39m\u001b[39m{\u001b[39;00masheetname\u001b[39m}\u001b[39;00m\u001b[39m\"\u001b[39m)\n\u001b[1;32m    745\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39misinstance\u001b[39m(asheetname, \u001b[39mstr\u001b[39m):\n\u001b[0;32m--> 746\u001b[0m     sheet \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mget_sheet_by_name(asheetname)\n\u001b[1;32m    747\u001b[0m \u001b[39melse\u001b[39;00m:  \u001b[39m# assume an integer if not a string\u001b[39;00m\n\u001b[1;32m    748\u001b[0m     sheet \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mget_sheet_by_index(asheetname)\n",
      "File \u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/excel/_openpyxl.py:569\u001b[0m, in \u001b[0;36mOpenpyxlReader.get_sheet_by_name\u001b[0;34m(self, name)\u001b[0m\n\u001b[1;32m    568\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39mget_sheet_by_name\u001b[39m(\u001b[39mself\u001b[39m, name: \u001b[39mstr\u001b[39m):\n\u001b[0;32m--> 569\u001b[0m     \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mraise_if_bad_sheet_by_name(name)\n\u001b[1;32m    570\u001b[0m     \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mbook[name]\n",
      "File \u001b[0;32m/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/excel/_base.py:597\u001b[0m, in \u001b[0;36mBaseExcelReader.raise_if_bad_sheet_by_name\u001b[0;34m(self, name)\u001b[0m\n\u001b[1;32m    595\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39mraise_if_bad_sheet_by_name\u001b[39m(\u001b[39mself\u001b[39m, name: \u001b[39mstr\u001b[39m) \u001b[39m-\u001b[39m\u001b[39m>\u001b[39m \u001b[39mNone\u001b[39;00m:\n\u001b[1;32m    596\u001b[0m     \u001b[39mif\u001b[39;00m name \u001b[39mnot\u001b[39;00m \u001b[39min\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39msheet_names:\n\u001b[0;32m--> 597\u001b[0m         \u001b[39mraise\u001b[39;00m \u001b[39mValueError\u001b[39;00m(\u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39mWorksheet named \u001b[39m\u001b[39m'\u001b[39m\u001b[39m{\u001b[39;00mname\u001b[39m}\u001b[39;00m\u001b[39m'\u001b[39m\u001b[39m not found\u001b[39m\u001b[39m\"\u001b[39m)\n",
      "\u001b[0;31mValueError\u001b[0m: Worksheet named 'ratings' not found"
     ]
    }
   ],
   "source": [
    "ratings = pd.read_excel('data/ratings_movies.xlsx', sheet_name='ratings')\n",
    "display(ratings.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Adventure|Animation|Children|Comedy|Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji (1995)</td>\n",
       "      <td>Adventure|Children|Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Grumpier Old Men (1995)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Waiting to Exhale (1995)</td>\n",
       "      <td>Comedy|Drama|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Father of the Bride Part II (1995)</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   movieId                               title  \\\n",
       "0        1                    Toy Story (1995)   \n",
       "1        2                      Jumanji (1995)   \n",
       "2        3             Grumpier Old Men (1995)   \n",
       "3        4            Waiting to Exhale (1995)   \n",
       "4        5  Father of the Bride Part II (1995)   \n",
       "\n",
       "                                        genres  \n",
       "0  Adventure|Animation|Children|Comedy|Fantasy  \n",
       "1                   Adventure|Children|Fantasy  \n",
       "2                               Comedy|Romance  \n",
       "3                         Comedy|Drama|Romance  \n",
       "4                                       Comedy  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "movies = pd.read_excel('data/ratings_movies.xlsx', sheet_name='movies')\n",
    "display(movies.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratings_movies = ratings.merge(movies, on='movieId', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratings_movies.to_excel('data/ratings_movies.xlsx', sheet_name='ratings_movies', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>userId</th>\n",
       "      <th>movieId</th>\n",
       "      <th>rating</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964982703</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Adventure|Animation|Children|Comedy|Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964981247</td>\n",
       "      <td>Grumpier Old Men (1995)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964982224</td>\n",
       "      <td>Heat (1995)</td>\n",
       "      <td>Action|Crime|Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>47</td>\n",
       "      <td>5.0</td>\n",
       "      <td>964983815</td>\n",
       "      <td>Seven (a.k.a. Se7en) (1995)</td>\n",
       "      <td>Mystery|Thriller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>5.0</td>\n",
       "      <td>964982931</td>\n",
       "      <td>Usual Suspects, The (1995)</td>\n",
       "      <td>Crime|Mystery|Thriller</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userId  movieId  rating  timestamp                        title  \\\n",
       "0       1        1     4.0  964982703             Toy Story (1995)   \n",
       "1       1        3     4.0  964981247      Grumpier Old Men (1995)   \n",
       "2       1        6     4.0  964982224                  Heat (1995)   \n",
       "3       1       47     5.0  964983815  Seven (a.k.a. Se7en) (1995)   \n",
       "4       1       50     5.0  964982931   Usual Suspects, The (1995)   \n",
       "\n",
       "                                        genres  \n",
       "0  Adventure|Animation|Children|Comedy|Fantasy  \n",
       "1                               Comedy|Romance  \n",
       "2                        Action|Crime|Thriller  \n",
       "3                             Mystery|Thriller  \n",
       "4                       Crime|Mystery|Thriller  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ratings_movies = pd.read_excel('data/ratings_movies.xlsx')\n",
    "display(ratings_movies.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 100836 entries, 0 to 100835\n",
      "Data columns (total 6 columns):\n",
      " #   Column     Non-Null Count   Dtype  \n",
      "---  ------     --------------   -----  \n",
      " 0   userId     100836 non-null  int64  \n",
      " 1   movieId    100836 non-null  int64  \n",
      " 2   rating     100836 non-null  float64\n",
      " 3   timestamp  100836 non-null  int64  \n",
      " 4   title      100836 non-null  object \n",
      " 5   genres     100836 non-null  object \n",
      "dtypes: float64(1), int64(3), object(2)\n",
      "memory usage: 4.6+ MB\n"
     ]
    }
   ],
   "source": [
    "ratings_movies.info()"
   ]
  }
 ],
 "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.9.10"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}