Lesson7/07_excel_Куртаев_Дамир.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Работа с Excel"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Материалы:\n",
    "* Макрушин С.В. Лекция 7: Работа с Excel\n",
    "* https://docs.xlwings.org/en/stable/quickstart.html\n",
    "* https://nbviewer.jupyter.org/github/pybokeh/jupyter_notebooks/blob/master/xlwings/Excel_Formatting.ipynb#search_text\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Задачи для совместного разбора"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "outputs": [],
   "source": [
    "# import numpy as np\n",
    "# import xlwings as xw\n",
    "# import pandas as pd\n",
    "# from pprint import pprint as pp\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "1. На листе \"Рецептура\" файла `себестоимостьА_в1.xlsx` для области \"Пшеничный хлеб\" рассчитать себестоимость всех видов продукции."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "outputs": [],
   "source": [
    "# wb = xw.Book('./data/data/себестоимостьА_в1.xlsx')"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "outputs": [],
   "source": [
    "# sheet = wb.sheets['Рецептура']"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "outputs": [],
   "source": [
    "# consumption = sheet.range('G7:O10').options(np.array).value\n",
    "# consumption"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "outputs": [],
   "source": [
    "# unit_price = sheet.range('G14:O14').options(np.array).value\n",
    "# unit_price"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "outputs": [],
   "source": [
    "# res = np.nan_to_num(consumption * unit_price).sum(axis=1)\n",
    "# res"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "2. Результаты расчетов 1.1 сохранить в отдельном столбце области \"Пшеничный хлеб\""
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "outputs": [],
   "source": [
    "# sheet.range('T7:T10').options(transpose=True).value = res"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "3. Приблизить форматирование столбца, добавленного в задаче 2 к оформлению всей области."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "outputs": [],
   "source": [
    "# sheet.range('T6').value = 'Себестоимость'\n",
    "# sheet.range('T4:T6').api.Merge()\n",
    "# sheet.range('T4:T6').color = (255, 0, 255)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "4. Выполнить 3 с помощью \"протягиваемых\" формул."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "outputs": [],
   "source": [
    "# from xlwings.constants import AutoFillType"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "outputs": [],
   "source": [
    "# sheet.range('V7').formula = '=SUMPRODUCT(G7:O7, $G$14:$O$14)'\n",
    "# sheet.range('V7').api.AutoFill(sheet.range('V7:V10').api,\n",
    "#                                AutoFillType.xlFillDefault)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Лабораторная работа 7"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import xlwings as xw\n",
    "import pandas as pd\n",
    "from pprint import pprint as pp\n",
    "from xlwings.constants import AutoFillType"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "1. Загрузите данные из файлов `reviews_sample.csv` (__ЛР2__) и `recipes_sample_with_tags_ingredients.csv` (__ЛР5__) в виде `pd.DataFrame`. Обратите внимание на корректное считывание столбца(ов) с индексами. Оставьте в таблице с рецептами следующие столбцы: `id`, `name`, `minutes`, `submitted`, `description`, `n_ingredients`"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "outputs": [],
   "source": [
    "reviews_sample = pd.read_csv(\"./data/data/reviews_sample.csv\", index_col=0, parse_dates=[\"date\"])\n",
    "recipes_sample_with_tags_ingredients = \\\n",
    "    pd.read_csv(\"./data/data/recipes_sample_with_tags_ingredients.csv\", parse_dates=[\"submitted\"])\\\n",
    "        .drop(columns=[\"contributor_id\", \"n_steps\", \"tags\", \"n_tags\", \"recipe_id\", \"ingredients\"])"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "outputs": [
    {
     "data": {
      "text/plain": "           user_id  recipe_id       date  rating  \\\n370476       21752      57993 2003-05-01       5   \n624300      431813     142201 2007-09-16       5   \n187037      400708     252013 2008-01-10       4   \n706134  2001852463     404716 2017-12-11       5   \n312179       95810     129396 2008-03-14       5   \n\n                                                   review  \n370476  Last week whole sides of frozen salmon fillet ...  \n624300  So simple and so tasty!  I used a yellow capsi...  \n187037  Very nice breakfast HH, easy to make and yummy...  \n706134  These are a favorite for the holidays and so e...  \n312179  Excellent soup!  The tomato flavor is just gre...  ",
      "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>user_id</th>\n      <th>recipe_id</th>\n      <th>date</th>\n      <th>rating</th>\n      <th>review</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>370476</th>\n      <td>21752</td>\n      <td>57993</td>\n      <td>2003-05-01</td>\n      <td>5</td>\n      <td>Last week whole sides of frozen salmon fillet ...</td>\n    </tr>\n    <tr>\n      <th>624300</th>\n      <td>431813</td>\n      <td>142201</td>\n      <td>2007-09-16</td>\n      <td>5</td>\n      <td>So simple and so tasty!  I used a yellow capsi...</td>\n    </tr>\n    <tr>\n      <th>187037</th>\n      <td>400708</td>\n      <td>252013</td>\n      <td>2008-01-10</td>\n      <td>4</td>\n      <td>Very nice breakfast HH, easy to make and yummy...</td>\n    </tr>\n    <tr>\n      <th>706134</th>\n      <td>2001852463</td>\n      <td>404716</td>\n      <td>2017-12-11</td>\n      <td>5</td>\n      <td>These are a favorite for the holidays and so e...</td>\n    </tr>\n    <tr>\n      <th>312179</th>\n      <td>95810</td>\n      <td>129396</td>\n      <td>2008-03-14</td>\n      <td>5</td>\n      <td>Excellent soup!  The tomato flavor is just gre...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reviews_sample.head()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "outputs": [
    {
     "data": {
      "text/plain": "                                       name     id  minutes  submitted  \\\n0     george s at the cove  black bean soup  44123       90 2002-10-25   \n1        healthy for them  yogurt popsicles  67664       10 2003-07-26   \n2              i can t believe it s spinach  38798       30 2002-08-29   \n3                      italian  gut busters  35173       45 2002-07-27   \n4  love is in the air  beef fondue   sauces  84797       25 2004-02-23   \n\n                                         description  n_ingredients  \n0  an original recipe created by chef scott meska...             18  \n1  my children and their friends ask for my homem...              3  \n2            these were so go, it surprised even me.              8  \n3  my sister-in-law made these for us at a family...              9  \n4  i think a fondue is a very romantic casual din...             12  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>name</th>\n      <th>id</th>\n      <th>minutes</th>\n      <th>submitted</th>\n      <th>description</th>\n      <th>n_ingredients</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>george s at the cove  black bean soup</td>\n      <td>44123</td>\n      <td>90</td>\n      <td>2002-10-25</td>\n      <td>an original recipe created by chef scott meska...</td>\n      <td>18</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>healthy for them  yogurt popsicles</td>\n      <td>67664</td>\n      <td>10</td>\n      <td>2003-07-26</td>\n      <td>my children and their friends ask for my homem...</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>i can t believe it s spinach</td>\n      <td>38798</td>\n      <td>30</td>\n      <td>2002-08-29</td>\n      <td>these were so go, it surprised even me.</td>\n      <td>8</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>italian  gut busters</td>\n      <td>35173</td>\n      <td>45</td>\n      <td>2002-07-27</td>\n      <td>my sister-in-law made these for us at a family...</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>love is in the air  beef fondue   sauces</td>\n      <td>84797</td>\n      <td>25</td>\n      <td>2004-02-23</td>\n      <td>i think a fondue is a very romantic casual din...</td>\n      <td>12</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "recipes_sample_with_tags_ingredients.head()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "2. Случайным образом выберите 5% строк из каждой таблицы и сохраните две таблицы на разные листы в один файл `recipes.xlsx`. Дайте листам названия \"Рецепты\" и \"Отзывы\", соответствующие содержанию таблиц."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "writer = pd.ExcelWriter(\"./data/data/recipes.xlsx\")\n",
    "reviews_sample.sample(frac=0.05).to_excel(writer, sheet_name = 'Отзывы')\n",
    "recipes_sample_with_tags_ingredients.sample(frac=0.05).to_excel(writer, sheet_name = 'Рецепты')\n",
    "writer.save()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "3. Используя `xlwings`, добавьте на лист `Рецепты` столбец `seconds_assign`, показывающий время выполнения рецепта в секундах. Выполните задание при помощи присваивания массива значений диапазону ячеек."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "outputs": [],
   "source": [
    "wb = xw.Book('./data/data/recipes.xlsx')\n",
    "wb_recipes_sheet = wb.sheets['Рецепты']\n",
    "wb_reviews_sheet = wb.sheets['Отзывы']\n",
    "# wb.save()\n",
    "# wb.close()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "outputs": [],
   "source": [
    "wb_recipes_sheet.range(\"H1\").value = \"seconds_assign\"\n",
    "wb_recipes_sheet.range(\"H2:H1501\").options(transpose=True).value = np.array(wb_recipes_sheet.range(\"D2:D1501\").value) * 60"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "4. Используя `xlwings`, добавьте на лист `Рецепты` столбец `seconds_formula`, показывающий время выполнения рецепта в секундах. Выполните задание при помощи формул Excel."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "outputs": [
    {
     "data": {
      "text/plain": "True"
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb_recipes_sheet.range(\"I1\").value = \"seconds_formula\"\n",
    "wb_recipes_sheet.range(\"I2\").formula = \"=D2*60\"\n",
    "wb_recipes_sheet.range('I2').api.AutoFill(wb_recipes_sheet.range('I2:I1501').api,\n",
    "                               AutoFillType.xlFillDefault)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "5. Добавьте на лист `Рецепты`  столбец `n_reviews`, содержащий кол-во отзывов для этого рецепта. Выполните задание при помощи формул Excel."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "outputs": [
    {
     "data": {
      "text/plain": "True"
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb_recipes_sheet.range(\"J1\").value = \"n_reviews\"\n",
    "wb_recipes_sheet.range(\"J2\").formula = '=COUNTIF(Отзывы!C:C, \"=\"&C2)'\n",
    "wb_recipes_sheet.range('J2').api.AutoFill(wb_recipes_sheet.range('J2:J1501').api,\n",
    "                               AutoFillType.xlFillDefault)\n",
    "# wb.close()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "6. Сделайте названия всех добавленных столбцов полужирными и выровняйте по центру ячейки."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "outputs": [],
   "source": [
    "wb_recipes_sheet['H1:J1'].api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter\n",
    "wb_recipes_sheet['H1:J1'].font.bold = True"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "7. Раскрасьте ячейки столбца `minutes` в соответствии со следующим правилом: если рецепт выполняется быстрее 5 минут, то цвет - зеленый; от 5 до 10 минут - жёлтый; и больше 10 - красный."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "outputs": [],
   "source": [
    "# for i in wb_recipes_sheet.range(\"D2:D1501\"):\n",
    "#     match i.value:\n",
    "#         case 5:\n",
    "#             i.color = (0, 255, 0)\n",
    "#         case range(5, 10):\n",
    "#             i.color = (255, 255, 0)\n",
    "#         case 5:\n",
    "#             i.color = (255, 0, 0)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "outputs": [],
   "source": [
    "for i in wb_recipes_sheet.range(\"D2:D1501\"):\n",
    "    if i.value < 5:\n",
    "        i.color = (0, 255, 0)\n",
    "    elif 5 <= i.value <= 10:\n",
    "        i.color = (255, 255, 0)\n",
    "    elif i.value > 10:\n",
    "        i.color = (255, 0, 0)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "8. Напишите функцию `validate()`, которая проверяет соответствие всех строк из листа `Отзывы` следующим правилам:\n",
    "    * Рейтинг - это число от 0 до 5 включительно\n",
    "    * Соответствующий рецепт имеется на листе `Рецепты`\n",
    "\n",
    "В случае несоответствия этим правилам, выделите строку красным цветом\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "outputs": [],
   "source": [
    "def validate():\n",
    "    wb = xw.Book('./data/data/recipes.xlsx')\n",
    "    wb_recipes_sheet = wb.sheets['Рецепты']\n",
    "    wb_reviews_sheet = wb.sheets['Отзывы']\n",
    "    recipe_ids = np.array(wb_recipes_sheet.range(\"C2:C1501\").value).astype(int)\n",
    "\n",
    "    for i in range(2, 3638):\n",
    "        if (wb_reviews_sheet[f\"E{i}\"].value > 5 or wb_reviews_sheet[f\"E{i}\"].value < 0) and (wb_reviews_sheet[f\"C{i}\"].value not in recipe_ids):\n",
    "            wb_reviews_sheet.range(f\"A{i}:F{i}\").color = (255, 0, 0)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "outputs": [],
   "source": [
    "validate()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "outputs": [],
   "source": [
    "wb.save(\"./data/data/recipes_v2.xlsx\")\n",
    "wb.close()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}