Stepik решение практических задач.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Первое задание"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('trekking1.xlsx', index_col = 0)"
   ]
  },
  {
   "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>ККал на 100</th>\n",
       "      <th>Б на 100</th>\n",
       "      <th>Ж на 100</th>\n",
       "      <th>У на 100</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Сухофрукты</td>\n",
       "      <td>548.0</td>\n",
       "      <td>12.8</td>\n",
       "      <td>40.8</td>\n",
       "      <td>32.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Каша Быстров</td>\n",
       "      <td>334.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>4.8</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Балык \"Королевский\"</td>\n",
       "      <td>170.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Колбаса с/к \"Еврейская полусухая\"</td>\n",
       "      <td>481.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Суп гороховый с копченостями</td>\n",
       "      <td>390.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   ККал на 100  Б на 100  Ж на 100  У на 100\n",
       "Сухофрукты                               548.0      12.8      40.8      32.4\n",
       "Каша Быстров                             334.0       8.6       4.8      64.0\n",
       "Балык \"Королевский\"                      170.0      25.0       8.0       NaN\n",
       "Колбаса с/к \"Еврейская полусухая\"        481.0      19.0      45.0       NaN\n",
       "Суп гороховый с копченостями             390.0      16.0      11.0      52.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_list = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range (len(df.index)):\n",
    "    new_food = (df.index[i], df.iloc[i][0])\n",
    "    new_list.append(new_food)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "right_order = sorted(new_list, key = lambda item: (-item[1], item[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Шоколад молочный какао', 601.0),\n",
       " ('Шоколад молочный миндаль', 574.0),\n",
       " ('Сухофрукты', 548.0),\n",
       " ('Омлет с копченостями галагала', 544.0),\n",
       " ('Свинина сублимрованная', 543.0),\n",
       " ('Шоколад ремесленный', 540.0),\n",
       " ('Козинак подсолнечный', 530.0),\n",
       " ('Омлет с грибами галагала', 526.0),\n",
       " ('Козинак арахисовый', 520.0),\n",
       " ('Хлебцы льняные \"Тайские\"', 507.0),\n",
       " ('Колбаса с/к \"Еврейская полусухая\"', 481.0),\n",
       " ('Говядина сублимрованная', 474.0),\n",
       " ('Сухарики ржаные с семечками', 451.0),\n",
       " ('Хлебцы льняные Морские', 436.5),\n",
       " ('Суп говяжий с картофелем галагала', 390.0),\n",
       " ('Суп гороховый с копченостями', 390.0),\n",
       " ('Суп гороховый с копченостями галагала', 390.0),\n",
       " ('Суп куриный с картофелем галагала', 390.0),\n",
       " ('Суп-гуляш галагала', 382.0),\n",
       " ('Суп-харчо \"Острый\"', 370.0),\n",
       " ('Сыр Маасдам', 370.0),\n",
       " ('Сыр Чеддер', 369.0),\n",
       " ('Борщ Московский Галагала', 360.0),\n",
       " ('Крупа гречневая', 360.0),\n",
       " ('Рис пропаренный Увелка', 350.0),\n",
       " ('Свинина Кронидов', 349.0),\n",
       " ('Сухарики ржаные Finn Crisp', 347.0),\n",
       " ('Капеллини Макфа', 344.0),\n",
       " ('Сыр Гауда', 338.0),\n",
       " ('Кус Кус', 336.0),\n",
       " ('Каша Быстров', 334.0),\n",
       " ('Картофель сушеный', 333.0),\n",
       " ('Говядина Кронидов', 213.0),\n",
       " ('Хлебцы с чесноком и луком', 212.0),\n",
       " ('Суп томатный с рисом', 205.5),\n",
       " ('Балык \"Королевский\"', 170.0),\n",
       " ('Паутина', 12.0)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "right_order"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Второе задание"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_raskladka = pd.read_excel('trekking2.xlsx', sheet_name=\"Раскладка\", index_col = 0)\n",
    "df_list = pd.read_excel('trekking2.xlsx', index_col = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "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>Вес в граммах</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Продукт</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Омлет с грибами галагала</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Сыр Маасдам</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Хлебцы льняные Морские</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Вес в граммах\n",
       "Продукт                                \n",
       "Омлет с грибами галагала             60\n",
       "Сыр Маасдам                          80\n",
       "Хлебцы льняные Морские               55"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_raskladka.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = df_list.fillna(0)\n",
    "additional_list = [str(i) for i in df_raskladka.index]\n",
    "df_raskladka = df_raskladka[~df_raskladka.index.duplicated()] #drop_duplicates убирает все дубликаты почкму-то???"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "catalog = {}\n",
    "for i in range (len(df_list.index)):\n",
    "    info = {df_list.index[i]: [df_list.iloc[i][0],df_list.iloc[i][1],df_list.iloc[i][2],\n",
    "                               df_list.iloc[i][3]]}\n",
    "    catalog.update(info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "value_of_products = [0,0,0,0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in additional_list:\n",
    "    a = catalog[i]\n",
    "    for b in range(4):\n",
    "        value_of_products[b] += (a[b]/100) * df_raskladka.loc[i]['Вес в граммах']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[4963.849999999999, 203.16999999999996, 307.4, 302.15000000000003]"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "value_of_products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[4963, 203, 307, 302]\n"
     ]
    }
   ],
   "source": [
    "final = [int(i) for i in value_of_products]\n",
    "print(final)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Третье задание "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_raskladka = pd.read_excel('trekking3.xlsx', sheet_name=\"Раскладка\", index_col = 1)\n",
    "df_list = pd.read_excel('trekking3.xlsx', index_col = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = df_list.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "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>ККал на 100</th>\n",
       "      <th>Б на 100</th>\n",
       "      <th>Ж на 100</th>\n",
       "      <th>У на 100</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Сухофрукты</td>\n",
       "      <td>548.0</td>\n",
       "      <td>12.8</td>\n",
       "      <td>40.8</td>\n",
       "      <td>32.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Каша Быстров</td>\n",
       "      <td>334.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>4.8</td>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Балык \"Королевский\"</td>\n",
       "      <td>170.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     ККал на 100  Б на 100  Ж на 100  У на 100\n",
       "Сухофрукты                 548.0      12.8      40.8      32.4\n",
       "Каша Быстров               334.0       8.6       4.8      64.0\n",
       "Балык \"Королевский\"        170.0      25.0       8.0       0.0"
      ]
     },
     "execution_count": 215,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_list.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "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>День</th>\n",
       "      <th>Вес в граммах</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Продукт</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Рис пропаренный Увелка</td>\n",
       "      <td>1</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Свинина Кронидов</td>\n",
       "      <td>1</td>\n",
       "      <td>250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Хлебцы льняные \"Тайские\"</td>\n",
       "      <td>1</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          День  Вес в граммах\n",
       "Продукт                                      \n",
       "Рис пропаренный Увелка       1            160\n",
       "Свинина Кронидов             1            250\n",
       "Хлебцы льняные \"Тайские\"     1            110"
      ]
     },
     "execution_count": 216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_raskladka.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {},
   "outputs": [],
   "source": [
    "catalog = {}\n",
    "for i in range (len(df_list.index)):\n",
    "    info = {df_list.index[i]: [df_list.iloc[i][0],df_list.iloc[i][1],df_list.iloc[i][2],\n",
    "                               df_list.iloc[i][3]]}\n",
    "    catalog.update(info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [],
   "source": [
    "values_by_days = []\n",
    "for i in range (1,10):\n",
    "    new_df = df_raskladka.query(f' День == {i}')\n",
    "    new_df = new_df.groupby('Продукт').aggregate({'Вес в граммах': 'sum'})\n",
    "    value_of_products = [0,0,0,0]\n",
    "    for j in new_df.index:\n",
    "        a = catalog[j]\n",
    "        for b in range(4):\n",
    "            value_of_products[b] += (a[b]/100) * new_df.loc[j][0]\n",
    "    values_by_days.append(value_of_products)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {},
   "outputs": [],
   "source": [
    "rounds = [[int(i) for i in b] for b in values_by_days]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2405 82 154 172\n",
      "4963 203 307 302\n",
      "5219 178 267 502\n",
      "4873 241 204 537\n",
      "5278 243 314 367\n",
      "4451 190 188 497\n",
      "4806 205 265 386\n",
      "5738 264 195 720\n",
      "1766 66 90 171\n"
     ]
    }
   ],
   "source": [
    "for i in rounds:\n",
    "    print(i[0],i[1],i[2],i[3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Четвертое задание"
   ]
  },
  {
   "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>Расчетный лист</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>ФИО</td>\n",
       "      <td>Сидоров Михаил Александрович</td>\n",
       "      <td>Начислено</td>\n",
       "      <td>49259.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Организация</td>\n",
       "      <td>ООО \"Рога и копыта\"</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Расчетный лист                    Unnamed: 1 Unnamed: 2  Unnamed: 3\n",
       "0            ФИО  Сидоров Михаил Александрович  Начислено     49259.0\n",
       "1    Организация           ООО \"Рога и копыта\"        NaN         NaN"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sal = pd.read_excel(f'salaries/1.xlsx')\n",
    "df_sal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_salaries = []\n",
    "for i in range(1,1001):\n",
    "    df_sal = pd.read_excel(f'salaries/{i}.xlsx')\n",
    "    all_salaries.append((df_sal.loc[0][1],int(df_sal.loc[0][3])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "right_order = sorted(all_salaries, key=lambda x: x[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Александров Александр Александрович', 19279),\n",
       " ('Александров Александр Алексеевич', 111877),\n",
       " ('Александров Александр Васильевич', 121425),\n",
       " ('Александров Александр Дмитриевич', 47073),\n",
       " ('Александров Александр Иванович', 98888)]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "right_order[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('salaries.txt', 'w', encoding = 'UTF-8') as f:\n",
    "    for i in right_order:\n",
    "        f.write(f'{i[0]} {i[1]}\\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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}