050_spojeni.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3c49eaba-04cd-4c05-9b78-ca43179ff542",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import datetime\n",
    "pd.set_option('display.max_columns', 100)\n",
    "pd.set_option('display.max_rows', 500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e2c1f968-7977-4632-82f7-3d83447ea76d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame()\n",
    "for f in [x for x in os.listdir(\"data\") if x[2] == \"_\"]:\n",
    "    df = pd.concat([df, pd.read_parquet(os.path.join(\"data\",f))])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "98e440a4-ff6c-4378-af23-bc8206a7ebec",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['oscrapovano_minuty'] = df['oscrapovano'].apply(lambda x: str(x)[0:14])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "834a58d6-07a1-4ff3-b434-44a4d8a16b9f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      2024-11-07 18:\n",
       "1      2024-11-07 18:\n",
       "2      2024-11-07 18:\n",
       "3      2024-11-07 18:\n",
       "4      2024-11-07 18:\n",
       "            ...      \n",
       "510    2025-01-19 16:\n",
       "511    2025-01-19 16:\n",
       "512    2025-01-19 16:\n",
       "513    2025-01-19 22:\n",
       "514    2025-01-19 22:\n",
       "Name: oscrapovano_minuty, Length: 4224282, dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['oscrapovano_minuty']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b31b987f-afe9-4594-bd30-e512d08a3566",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop_duplicates(subset=['odkud','kam','odjezd','oscrapovano_minuty'], keep=\"last\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5418f80d-4c83-43ee-9eaa-e8292b9fd63c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1834042"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b383d7ac-5cb7-4d50-a416-546ae5d60ac8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "123775"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['odjezd','odkud','kam']].drop_duplicates().shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "de3420d1-e5c1-4720-b702-4e1840d74ae3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "prodejce  oscrapovano\n",
       "ARR       2024-11-07      2297\n",
       "          2024-11-08      7128\n",
       "          2024-11-09      3320\n",
       "          2024-11-10      3157\n",
       "          2024-11-11      3367\n",
       "          2024-11-12      3323\n",
       "          2024-11-13      3632\n",
       "          2024-11-14      3072\n",
       "          2024-11-15      3552\n",
       "          2024-11-16      3498\n",
       "          2024-11-17      3254\n",
       "          2024-11-18      3310\n",
       "          2024-11-19      3384\n",
       "          2024-11-20      3393\n",
       "          2024-11-21      3356\n",
       "          2024-11-22      3589\n",
       "          2024-11-23      3496\n",
       "          2024-11-24      3394\n",
       "          2024-11-25      3086\n",
       "          2024-11-26      2799\n",
       "          2024-11-27      2986\n",
       "          2024-11-28      3206\n",
       "          2024-11-29      3036\n",
       "          2024-11-30      3005\n",
       "          2024-12-01      3048\n",
       "          2024-12-02      1413\n",
       "          2024-12-03      1564\n",
       "          2024-12-04      1755\n",
       "          2024-12-05      1629\n",
       "          2024-12-06      1998\n",
       "          2024-12-07      2396\n",
       "          2024-12-08      1986\n",
       "          2024-12-09      1728\n",
       "          2024-12-10      2285\n",
       "          2024-12-11      1806\n",
       "          2024-12-12      2081\n",
       "          2024-12-13      1999\n",
       "          2024-12-14      2107\n",
       "          2024-12-15      2088\n",
       "          2024-12-16      2119\n",
       "          2024-12-17      1931\n",
       "          2024-12-18      2028\n",
       "          2024-12-20      2003\n",
       "          2024-12-21      2080\n",
       "          2024-12-22      1496\n",
       "          2024-12-23      1777\n",
       "          2024-12-24      1999\n",
       "          2024-12-25      2151\n",
       "          2024-12-26      2027\n",
       "          2024-12-27      2273\n",
       "          2024-12-28      1660\n",
       "          2024-12-29      2195\n",
       "          2024-12-30      2271\n",
       "          2024-12-31      2334\n",
       "          2025-01-01      1910\n",
       "          2025-01-02      2028\n",
       "          2025-01-03      1789\n",
       "          2025-01-04      1970\n",
       "          2025-01-05      2064\n",
       "          2025-01-06      2164\n",
       "          2025-01-07      1792\n",
       "          2025-01-08      2034\n",
       "          2025-01-09      1999\n",
       "          2025-01-10      2043\n",
       "          2025-01-11      1999\n",
       "          2025-01-12      2037\n",
       "          2025-01-13      1970\n",
       "          2025-01-14      1840\n",
       "          2025-01-15      2032\n",
       "          2025-01-16      1989\n",
       "          2025-01-17      1988\n",
       "          2025-01-18      1778\n",
       "          2025-01-19      1661\n",
       "LE        2024-11-07       332\n",
       "          2024-11-08      1573\n",
       "          2024-11-09      1873\n",
       "          2024-11-10      1755\n",
       "          2024-11-11      1697\n",
       "          2024-11-12      1889\n",
       "          2024-11-13      1886\n",
       "          2024-11-14      1981\n",
       "          2024-11-15      2063\n",
       "          2024-11-16      2002\n",
       "          2024-11-17      2004\n",
       "          2024-11-18      1988\n",
       "          2024-11-19      2050\n",
       "          2024-11-20      2042\n",
       "          2024-11-21      2123\n",
       "          2024-11-22      2017\n",
       "          2024-11-23      1951\n",
       "          2024-11-24      2085\n",
       "          2024-11-25      2308\n",
       "          2024-11-26      1965\n",
       "          2024-11-27      2015\n",
       "          2024-11-28      2029\n",
       "          2024-11-29      2062\n",
       "          2024-11-30      2028\n",
       "          2024-12-01      2016\n",
       "          2024-12-02      1139\n",
       "          2024-12-03      1204\n",
       "          2024-12-04      1181\n",
       "          2024-12-05      1037\n",
       "          2024-12-06      1123\n",
       "          2024-12-07      1276\n",
       "          2024-12-08      1295\n",
       "          2024-12-09      1300\n",
       "          2024-12-10      1238\n",
       "          2024-12-11      1294\n",
       "          2024-12-12      1342\n",
       "          2024-12-13      1395\n",
       "          2024-12-14      1438\n",
       "          2024-12-15      1427\n",
       "          2024-12-16      1584\n",
       "          2024-12-17      1567\n",
       "          2024-12-18      1524\n",
       "          2024-12-20      1618\n",
       "          2024-12-21      1491\n",
       "          2024-12-22      1252\n",
       "          2024-12-23      1460\n",
       "          2024-12-24      1352\n",
       "          2024-12-25      1457\n",
       "          2024-12-26      1507\n",
       "          2024-12-27      1569\n",
       "          2024-12-28      1554\n",
       "          2024-12-29      1457\n",
       "          2024-12-30      1373\n",
       "          2024-12-31      1414\n",
       "          2025-01-01      1415\n",
       "          2025-01-02      1346\n",
       "          2025-01-03      1379\n",
       "          2025-01-04      1390\n",
       "          2025-01-05      1240\n",
       "          2025-01-06      1230\n",
       "          2025-01-07      1347\n",
       "          2025-01-08      1244\n",
       "          2025-01-09      1191\n",
       "          2025-01-10      1269\n",
       "          2025-01-11      1186\n",
       "          2025-01-12      1215\n",
       "          2025-01-13      1202\n",
       "          2025-01-14      1260\n",
       "          2025-01-15      1246\n",
       "          2025-01-16      1257\n",
       "          2025-01-17      1156\n",
       "          2025-01-18      1160\n",
       "          2025-01-19      1176\n",
       "RJ        2024-11-06      3676\n",
       "          2024-11-07      1853\n",
       "          2024-11-08     11164\n",
       "          2024-11-09     14652\n",
       "          2024-11-10     12683\n",
       "          2024-11-11      7081\n",
       "          2024-11-12     13862\n",
       "          2024-11-13     15328\n",
       "          2024-11-14     11828\n",
       "          2024-11-15     11514\n",
       "          2024-11-16     12837\n",
       "          2024-11-17     13502\n",
       "          2024-11-18      7927\n",
       "          2024-11-19     14891\n",
       "          2024-11-20     14477\n",
       "          2024-11-21     10384\n",
       "          2024-11-22      7936\n",
       "          2024-11-23     13436\n",
       "          2024-11-24     10115\n",
       "          2024-11-25     10448\n",
       "          2024-11-26      9629\n",
       "          2024-11-27     10146\n",
       "          2024-11-28      9261\n",
       "          2024-11-29      4371\n",
       "          2024-11-30      1554\n",
       "          2024-12-01      1512\n",
       "          2024-12-02       586\n",
       "          2024-12-03       327\n",
       "          2024-12-04       456\n",
       "          2024-12-05       367\n",
       "          2024-12-06       482\n",
       "          2024-12-07       419\n",
       "          2024-12-08       417\n",
       "          2024-12-09       930\n",
       "          2024-12-10       512\n",
       "          2024-12-11      4245\n",
       "          2024-12-12      2049\n",
       "          2024-12-13       372\n",
       "          2024-12-14       519\n",
       "          2024-12-15       592\n",
       "          2024-12-16       366\n",
       "          2024-12-17       401\n",
       "          2024-12-18       536\n",
       "          2024-12-20       576\n",
       "          2024-12-21       466\n",
       "          2024-12-22       378\n",
       "          2024-12-23       550\n",
       "          2024-12-24       510\n",
       "          2024-12-25       421\n",
       "          2024-12-26       798\n",
       "          2024-12-27       889\n",
       "          2024-12-28      1003\n",
       "          2024-12-29       830\n",
       "          2024-12-30       516\n",
       "          2024-12-31       372\n",
       "          2025-01-01      5925\n",
       "          2025-01-02      3932\n",
       "          2025-01-03       453\n",
       "          2025-01-04       580\n",
       "          2025-01-05       474\n",
       "          2025-01-06       443\n",
       "          2025-01-07       628\n",
       "          2025-01-08       542\n",
       "          2025-01-09       480\n",
       "          2025-01-10       472\n",
       "          2025-01-11       566\n",
       "          2025-01-12       484\n",
       "          2025-01-13       446\n",
       "          2025-01-14       460\n",
       "          2025-01-15       593\n",
       "          2025-01-16       419\n",
       "          2025-01-17       546\n",
       "          2025-01-18       501\n",
       "          2025-01-19       509\n",
       "ČD        2024-10-31      1325\n",
       "          2024-11-01     13722\n",
       "          2024-11-02     19225\n",
       "          2024-11-03     15819\n",
       "          2024-11-04     20916\n",
       "          2024-11-05     21097\n",
       "          2024-11-06     21010\n",
       "          2024-11-07     20399\n",
       "          2024-11-08     21999\n",
       "          2024-11-09     23641\n",
       "          2024-11-10     21437\n",
       "          2024-11-11     21862\n",
       "          2024-11-12     22391\n",
       "          2024-11-13     21706\n",
       "          2024-11-14     21601\n",
       "          2024-11-15     21843\n",
       "          2024-11-16     22309\n",
       "          2024-11-17     21160\n",
       "          2024-11-18     21754\n",
       "          2024-11-19     22402\n",
       "          2024-11-20     21496\n",
       "          2024-11-21     22217\n",
       "          2024-11-22     21578\n",
       "          2024-11-23     21450\n",
       "          2024-11-24     21960\n",
       "          2024-11-25     20028\n",
       "          2024-11-26      2344\n",
       "          2024-11-27     22304\n",
       "          2024-11-28     21801\n",
       "          2024-11-29     22009\n",
       "          2024-11-30     21689\n",
       "          2024-12-01     22901\n",
       "          2024-12-02     12724\n",
       "          2024-12-03     12871\n",
       "          2024-12-04     12634\n",
       "          2024-12-05     10853\n",
       "          2024-12-06     13039\n",
       "          2024-12-07     12409\n",
       "          2024-12-08     13037\n",
       "          2024-12-09     11618\n",
       "          2024-12-10     13406\n",
       "          2024-12-11     13399\n",
       "          2024-12-12     13283\n",
       "          2024-12-13     12928\n",
       "          2024-12-14     13588\n",
       "          2024-12-15     12340\n",
       "          2024-12-16     11065\n",
       "          2024-12-17     12126\n",
       "          2024-12-18     12414\n",
       "          2024-12-20     12904\n",
       "          2024-12-21     11981\n",
       "          2024-12-22     11135\n",
       "          2024-12-23     13592\n",
       "          2024-12-24     12972\n",
       "          2024-12-25     11939\n",
       "          2024-12-26     12835\n",
       "          2024-12-27     12903\n",
       "          2024-12-28     13368\n",
       "          2024-12-29     12472\n",
       "          2024-12-30     13064\n",
       "          2024-12-31     13133\n",
       "          2025-01-01     13109\n",
       "          2025-01-02     12938\n",
       "          2025-01-03     12666\n",
       "          2025-01-04     12475\n",
       "          2025-01-05     13371\n",
       "          2025-01-06     12863\n",
       "          2025-01-07     12446\n",
       "          2025-01-08     12690\n",
       "          2025-01-09     12773\n",
       "          2025-01-10     13049\n",
       "          2025-01-11     13053\n",
       "          2025-01-12     13767\n",
       "          2025-01-13     12659\n",
       "          2025-01-14     12987\n",
       "          2025-01-15     13319\n",
       "          2025-01-16     12511\n",
       "          2025-01-17     12222\n",
       "          2025-01-18     13049\n",
       "          2025-01-19     10798\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['prodejce',pd.Grouper(key='oscrapovano',freq='D')]).size()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ec44be4-4b3e-49f7-9c30-bf4518f4e9ee",
   "metadata": {},
   "source": [
    "Výpadek ČD 2024-11-26 opraven, vyřešen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "13a6cb25-bc64-4cce-b00f-ab1a4ecf7734",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.sort_values(by=\"oscrapovano\").reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1e429647-b3cf-4a38-b662-82bf58f40e68",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.dropna(subset=['odkud','kam','odjezd','oscrapovano'],how='any')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "8516e283-1ec0-4fc6-92e0-5b0d9eda7db4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['prostredek'] != 'autobus']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "2451f714-e193-49ac-9439-dfaa169a3c4c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_42052\\103445691.py:1: UserWarning: This pattern is interpreted as a regular expression, and has match groups. To actually get the groups, use str.extract.\n",
      "  df = df[~df['kam'].str.contains('(tief)',na=False)]\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_42052\\103445691.py:2: UserWarning: This pattern is interpreted as a regular expression, and has match groups. To actually get the groups, use str.extract.\n",
      "  df = df[~df['odkud'].str.contains('(tief)',na=False)]\n"
     ]
    }
   ],
   "source": [
    "df = df[~df['kam'].str.contains('(tief)',na=False)]\n",
    "df = df[~df['odkud'].str.contains('(tief)',na=False)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c83a3e04-215a-4352-9caa-6fb0d20a75cb",
   "metadata": {},
   "source": [
    "Před 7. listopadem scrapování testovací a nesystematické = nevypovídající = filtrujeme."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "0c6bbbad-5707-4758-a97e-70e76b8ec28a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['oscrapovano'] >= '2024-11-08']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9a384ac6-c920-41cd-9134-3d04a084229e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "prodejce  oscrapovano\n",
       "ARR       2024-11-08      7128\n",
       "          2024-11-09      3320\n",
       "          2024-11-10      3157\n",
       "          2024-11-11      3367\n",
       "          2024-11-12      3323\n",
       "          2024-11-13      3632\n",
       "          2024-11-14      3072\n",
       "          2024-11-15      3552\n",
       "          2024-11-16      3498\n",
       "          2024-11-17      3254\n",
       "          2024-11-18      3310\n",
       "          2024-11-19      3384\n",
       "          2024-11-20      3393\n",
       "          2024-11-21      3356\n",
       "          2024-11-22      3589\n",
       "          2024-11-23      3496\n",
       "          2024-11-24      3394\n",
       "          2024-11-25      3086\n",
       "          2024-11-26      2799\n",
       "          2024-11-27      2986\n",
       "          2024-11-28      3206\n",
       "          2024-11-29      3036\n",
       "          2024-11-30      3005\n",
       "          2024-12-01      3048\n",
       "          2024-12-02      1413\n",
       "          2024-12-03      1564\n",
       "          2024-12-04      1755\n",
       "          2024-12-05      1629\n",
       "          2024-12-06      1998\n",
       "          2024-12-07      2396\n",
       "          2024-12-08      1986\n",
       "          2024-12-09      1728\n",
       "          2024-12-10      2285\n",
       "          2024-12-11      1806\n",
       "          2024-12-12      2081\n",
       "          2024-12-13      1999\n",
       "          2024-12-14      2107\n",
       "          2024-12-15      2088\n",
       "          2024-12-16      2119\n",
       "          2024-12-17      1931\n",
       "          2024-12-18      2028\n",
       "          2024-12-20      2003\n",
       "          2024-12-21      2080\n",
       "          2024-12-22      1496\n",
       "          2024-12-23      1777\n",
       "          2024-12-24      1999\n",
       "          2024-12-25      2151\n",
       "          2024-12-26      2027\n",
       "          2024-12-27      2273\n",
       "          2024-12-28      1660\n",
       "          2024-12-29      2195\n",
       "          2024-12-30      2271\n",
       "          2024-12-31      2334\n",
       "          2025-01-01      1910\n",
       "          2025-01-02      2028\n",
       "          2025-01-03      1789\n",
       "          2025-01-04      1970\n",
       "          2025-01-05      2064\n",
       "          2025-01-06      2164\n",
       "          2025-01-07      1792\n",
       "          2025-01-08      2034\n",
       "          2025-01-09      1999\n",
       "          2025-01-10      2043\n",
       "          2025-01-11      1999\n",
       "          2025-01-12      2037\n",
       "          2025-01-13      1970\n",
       "          2025-01-14      1840\n",
       "          2025-01-15      2032\n",
       "          2025-01-16      1989\n",
       "          2025-01-17      1988\n",
       "          2025-01-18      1778\n",
       "          2025-01-19      1661\n",
       "LE        2024-11-08      1573\n",
       "          2024-11-09      1873\n",
       "          2024-11-10      1755\n",
       "          2024-11-11      1697\n",
       "          2024-11-12      1889\n",
       "          2024-11-13      1886\n",
       "          2024-11-14      1981\n",
       "          2024-11-15      2063\n",
       "          2024-11-16      2002\n",
       "          2024-11-17      2004\n",
       "          2024-11-18      1988\n",
       "          2024-11-19      2050\n",
       "          2024-11-20      2042\n",
       "          2024-11-21      2123\n",
       "          2024-11-22      2017\n",
       "          2024-11-23      1951\n",
       "          2024-11-24      2085\n",
       "          2024-11-25      2308\n",
       "          2024-11-26      1965\n",
       "          2024-11-27      2015\n",
       "          2024-11-28      2029\n",
       "          2024-11-29      2062\n",
       "          2024-11-30      2028\n",
       "          2024-12-01      2016\n",
       "          2024-12-02      1139\n",
       "          2024-12-03      1204\n",
       "          2024-12-04      1181\n",
       "          2024-12-05      1037\n",
       "          2024-12-06      1123\n",
       "          2024-12-07      1276\n",
       "          2024-12-08      1295\n",
       "          2024-12-09      1300\n",
       "          2024-12-10      1238\n",
       "          2024-12-11      1294\n",
       "          2024-12-12      1342\n",
       "          2024-12-13      1395\n",
       "          2024-12-14      1438\n",
       "          2024-12-15      1427\n",
       "          2024-12-16      1584\n",
       "          2024-12-17      1567\n",
       "          2024-12-18      1524\n",
       "          2024-12-20      1618\n",
       "          2024-12-21      1491\n",
       "          2024-12-22      1252\n",
       "          2024-12-23      1460\n",
       "          2024-12-24      1352\n",
       "          2024-12-25      1457\n",
       "          2024-12-26      1507\n",
       "          2024-12-27      1569\n",
       "          2024-12-28      1554\n",
       "          2024-12-29      1457\n",
       "          2024-12-30      1373\n",
       "          2024-12-31      1414\n",
       "          2025-01-01      1415\n",
       "          2025-01-02      1346\n",
       "          2025-01-03      1379\n",
       "          2025-01-04      1390\n",
       "          2025-01-05      1240\n",
       "          2025-01-06      1230\n",
       "          2025-01-07      1347\n",
       "          2025-01-08      1244\n",
       "          2025-01-09      1191\n",
       "          2025-01-10      1269\n",
       "          2025-01-11      1186\n",
       "          2025-01-12      1215\n",
       "          2025-01-13      1202\n",
       "          2025-01-14      1260\n",
       "          2025-01-15      1246\n",
       "          2025-01-16      1257\n",
       "          2025-01-17      1156\n",
       "          2025-01-18      1160\n",
       "          2025-01-19      1176\n",
       "RJ        2024-11-08      5743\n",
       "          2024-11-09      7629\n",
       "          2024-11-10      7099\n",
       "          2024-11-11      3987\n",
       "          2024-11-12      7103\n",
       "          2024-11-13      7852\n",
       "          2024-11-14      6297\n",
       "          2024-11-15      5897\n",
       "          2024-11-16      7182\n",
       "          2024-11-17      7344\n",
       "          2024-11-18      4224\n",
       "          2024-11-19      8180\n",
       "          2024-11-20      7246\n",
       "          2024-11-21      5808\n",
       "          2024-11-22      4291\n",
       "          2024-11-23      6895\n",
       "          2024-11-24      5750\n",
       "          2024-11-25      5598\n",
       "          2024-11-26      5068\n",
       "          2024-11-27      5313\n",
       "          2024-11-28      5390\n",
       "          2024-11-29      2172\n",
       "          2024-11-30       849\n",
       "          2024-12-01       815\n",
       "          2024-12-02       317\n",
       "          2024-12-03       198\n",
       "          2024-12-04       253\n",
       "          2024-12-05       211\n",
       "          2024-12-06       256\n",
       "          2024-12-07       248\n",
       "          2024-12-08       240\n",
       "          2024-12-09       506\n",
       "          2024-12-10       272\n",
       "          2024-12-11      2337\n",
       "          2024-12-12      1060\n",
       "          2024-12-13       204\n",
       "          2024-12-14       281\n",
       "          2024-12-15       324\n",
       "          2024-12-16       175\n",
       "          2024-12-17       229\n",
       "          2024-12-18       263\n",
       "          2024-12-20       300\n",
       "          2024-12-21       266\n",
       "          2024-12-22       217\n",
       "          2024-12-23       297\n",
       "          2024-12-24       272\n",
       "          2024-12-25       240\n",
       "          2024-12-26       387\n",
       "          2024-12-27       486\n",
       "          2024-12-28       539\n",
       "          2024-12-29       490\n",
       "          2024-12-30       289\n",
       "          2024-12-31       242\n",
       "          2025-01-01      3401\n",
       "          2025-01-02      2185\n",
       "          2025-01-03       245\n",
       "          2025-01-04       294\n",
       "          2025-01-05       265\n",
       "          2025-01-06       240\n",
       "          2025-01-07       351\n",
       "          2025-01-08       273\n",
       "          2025-01-09       259\n",
       "          2025-01-10       287\n",
       "          2025-01-11       275\n",
       "          2025-01-12       276\n",
       "          2025-01-13       236\n",
       "          2025-01-14       251\n",
       "          2025-01-15       342\n",
       "          2025-01-16       224\n",
       "          2025-01-17       288\n",
       "          2025-01-18       262\n",
       "          2025-01-19       266\n",
       "ČD        2024-11-08     21175\n",
       "          2024-11-09     22068\n",
       "          2024-11-10     20263\n",
       "          2024-11-11     20752\n",
       "          2024-11-12     20805\n",
       "          2024-11-13     20623\n",
       "          2024-11-14     21091\n",
       "          2024-11-15     20754\n",
       "          2024-11-16     20674\n",
       "          2024-11-17     20080\n",
       "          2024-11-18     20400\n",
       "          2024-11-19     20281\n",
       "          2024-11-20     21028\n",
       "          2024-11-21     21502\n",
       "          2024-11-22     19793\n",
       "          2024-11-23     20456\n",
       "          2024-11-24     21168\n",
       "          2024-11-25     19234\n",
       "          2024-11-26      2019\n",
       "          2024-11-27     21754\n",
       "          2024-11-28     20385\n",
       "          2024-11-29     20532\n",
       "          2024-11-30     20493\n",
       "          2024-12-01     21364\n",
       "          2024-12-02     12079\n",
       "          2024-12-03     12056\n",
       "          2024-12-04     12044\n",
       "          2024-12-05      9449\n",
       "          2024-12-06     12503\n",
       "          2024-12-07     11553\n",
       "          2024-12-08     12272\n",
       "          2024-12-09     11503\n",
       "          2024-12-10     12587\n",
       "          2024-12-11     12840\n",
       "          2024-12-12     12462\n",
       "          2024-12-13     12482\n",
       "          2024-12-14     12904\n",
       "          2024-12-15     12087\n",
       "          2024-12-16     10638\n",
       "          2024-12-17     11690\n",
       "          2024-12-18     11758\n",
       "          2024-12-20     12517\n",
       "          2024-12-21     11692\n",
       "          2024-12-22     10832\n",
       "          2024-12-23     12725\n",
       "          2024-12-24     12207\n",
       "          2024-12-25     11314\n",
       "          2024-12-26     12066\n",
       "          2024-12-27     12012\n",
       "          2024-12-28     12445\n",
       "          2024-12-29     12072\n",
       "          2024-12-30     12479\n",
       "          2024-12-31     12304\n",
       "          2025-01-01     12228\n",
       "          2025-01-02     12265\n",
       "          2025-01-03     12389\n",
       "          2025-01-04     11074\n",
       "          2025-01-05     12790\n",
       "          2025-01-06     12029\n",
       "          2025-01-07     12100\n",
       "          2025-01-08     11634\n",
       "          2025-01-09     12744\n",
       "          2025-01-10     11798\n",
       "          2025-01-11     12713\n",
       "          2025-01-12     13187\n",
       "          2025-01-13     11815\n",
       "          2025-01-14     12385\n",
       "          2025-01-15     12803\n",
       "          2025-01-16     12218\n",
       "          2025-01-17     11923\n",
       "          2025-01-18     12162\n",
       "          2025-01-19     10525\n",
       "dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['prodejce',pd.Grouper(key='oscrapovano',freq='D')]).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e7b3f70a-4f04-488f-99a0-e534168cdf21",
   "metadata": {},
   "outputs": [],
   "source": [
    "days = {0: 'po', 1: 'út', 2: 'st', 3: 'čt', \n",
    "        4: 'pá', 5: 'so', 6: 'ne'}\n",
    "df['den'] = df['odjezd'].dt.dayofweek.map(days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "50764e8d-9cd1-450b-bd8a-34e85e3d22f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['predstih_d'] = df['predstih'].dt.days\n",
    "df['predstih_h'] = df['predstih'].dt.total_seconds() / 3600"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "94bc1ba3-932e-4df2-a7a5-4aedaeec9717",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['predstih_h'] > -3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5376546f-7995-48af-a8c1-dd8c41c0a591",
   "metadata": {},
   "outputs": [],
   "source": [
    "kategoricka_data = ['odkud','kam','prodejce','den']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "be5f17df-fb85-4139-9b07-98b3013433ed",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Before: 108492900\n",
      "After:  13513133\n",
      "Before: 108446561\n",
      "After:  13509181\n",
      "Before: 112990900\n",
      "After:  13501228\n",
      "Before: 99405480\n",
      "After:  13501548\n"
     ]
    }
   ],
   "source": [
    "for k in kategoricka_data:\n",
    "    print(\"Before:\", df[k].memory_usage(deep=True))\n",
    "    df[k] = df[k].astype('category')\n",
    "    print(\"After: \", df[k].memory_usage(deep=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "f5e504ea-d0cb-43db-960d-574b16d96b2f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df['cena'] = pd.to_numeric(df['cena'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "031a1930-cc15-4b30-b202-ffcb492534b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1500092"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "55046487-4a82-4bec-8870-cc400664db09",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "109111"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['odjezd','odkud','kam']].drop_duplicates().shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "852ca70e-3c1a-48cb-81fe-d1c51c2300f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "poradi = ['oscrapovano','prodejce','odkud','kam',\n",
    " 'odjezd',\n",
    " 'predstih',\n",
    " 'predstih_d',\n",
    " 'predstih_h',\n",
    "          'cena',\n",
    " 'prostredek',\n",
    "          'volnych_mist',\n",
    " 'obsazenost',\n",
    " 'jizdni_doba',\n",
    " 'vzdalenost',\n",
    " 'zpozdeni',\n",
    " 'cena_poznamka',\n",
    " 'den',\n",
    " 'prestupy',\n",
    "'vlaky',\n",
    " 'mistenka_zdarma',\n",
    " 'nahradni_bus',\n",
    " 'volna_mista_economy',\n",
    " 'volna_mista_economy_plus',\n",
    " 'volna_mista_economy_business',\n",
    " 'volna_mista_premium']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "16e60aa6-f5da-4435-8fd0-35341d542234",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[poradi].to_parquet(os.path.join(\"data\",\"jizdenky.parquet\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "66d06a65-575a-486b-aeff-1fb8a3735eb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "nejnovejsi = df['oscrapovano'].max()\n",
    "nejnovejsi\n",
    "df_tyden = df[df['oscrapovano'] > (nejnovejsi - datetime.timedelta(hours=168))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "50643981-0d07-4390-a1ab-e2a4b636b358",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tyden[poradi].to_csv(os.path.join(\"data\",\"jizdenky_tyden.csv\"))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}