graphics.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "afa0b1cd-d2ea-4a6e-bd9a-8aea4cfcbf2b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-19T20:21:56.973354Z",
     "iopub.status.busy": "2023-05-19T20:21:56.973354Z",
     "iopub.status.idle": "2023-05-19T20:22:02.788435Z",
     "shell.execute_reply": "2023-05-19T20:22:02.787442Z",
     "shell.execute_reply.started": "2023-05-19T20:21:56.973354Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ready: 2023-05-19 23:22:02\n"
     ]
    }
   ],
   "source": [
    "%run functions.ipynb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "ec6ecb74-0e84-43a4-b7dd-84117b9d6cf2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-19T20:22:02.790431Z",
     "iopub.status.busy": "2023-05-19T20:22:02.790431Z",
     "iopub.status.idle": "2023-05-19T20:22:08.486571Z",
     "shell.execute_reply": "2023-05-19T20:22:08.485659Z",
     "shell.execute_reply.started": "2023-05-19T20:22:02.790431Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%run functions.ipynb\n",
    "findspark.init()\n",
    "from pyspark.sql import SparkSession\n",
    "try:\n",
    "    spark.stop()\n",
    "except:\n",
    "    pass\n",
    "spark = SparkSession.builder\\\n",
    "        .master(\"local[*]\")\\\n",
    "        .appName('PySpark_Tutorial')\\\n",
    "        .getOrCreate()\n",
    "\n",
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0b9564d2-12ec-4178-93e9-adc5dbc15266",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-19T20:22:08.488571Z",
     "iopub.status.busy": "2023-05-19T20:22:08.487569Z",
     "iopub.status.idle": "2023-05-19T20:22:09.654438Z",
     "shell.execute_reply": "2023-05-19T20:22:09.653440Z",
     "shell.execute_reply.started": "2023-05-19T20:22:08.488571Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - in-memory</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://host.docker.internal:4040\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.3.1</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>PySpark_Tutorial</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x1e4b2f2e070>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46221c84-dcc5-4485-935c-01100bf70207",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Новосибирск"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "0a4f9d75-c7ec-433b-b1fa-94a64135f140",
   "metadata": {},
   "outputs": [],
   "source": [
    "rubrics = pd.read_excel(r\"C:\\Users\\Mamed\\YandexDisk\\_Documents\\Проекты\\2ГИС 2023\\Backups\\_Final\\Мастер_таблица_2ГИС_Новосибирск_230322_2345.xlsx\")[['Рубрика']].iloc[:70]\n",
    "name = 'novosib'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33f26382-f74b-457d-b3e2-591bb1ca22a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "make_dates_file(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58e006b0-2138-406c-b003-4b1fd119e42c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "make_linear_plots('files/novosib_dates.xlsx', rubrics, cnt=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "e62fd54e-a209-4ef1-92ce-a4ea8f097123",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "files/novosib_dates_report_230402_2213.docx\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████████| 100/100 [00:33<00:00,  2.97it/s]\n"
     ]
    }
   ],
   "source": [
    "copy_images_texts('files/novosib_dates.xlsx', r\"C:\\Users\\Mamed\\YandexDisk\\_Documents\\Проекты\\2ГИС 2023\\Новосибирск\\Статья_Новосибирский распорядок_230401_1430.docx\", True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa594726-e45e-4f93-85c3-9c209f0f185b",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Москва"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7dc40bc4-a9ee-4cc3-98e1-669923b3cab5",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T19:08:53.835218Z",
     "iopub.status.busy": "2023-04-09T19:08:53.834213Z",
     "iopub.status.idle": "2023-04-09T19:08:55.265514Z",
     "shell.execute_reply": "2023-04-09T19:08:55.264501Z",
     "shell.execute_reply.started": "2023-04-09T19:08:53.835218Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "rubrics = pd.read_excel(r\"C:\\Users\\Mamed\\YandexDisk\\_Documents\\Проекты\\2ГИС 2023\\Москва\\Мастер_таблица_2ГИС_Москва_230320_0245.xlsx\")[['Рубрика']].iloc[:70]\n",
    "name = 'moscow'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8d4787c6-6319-4c53-8044-43500e67809a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T19:16:00.824752Z",
     "iopub.status.busy": "2023-04-09T19:16:00.824752Z",
     "iopub.status.idle": "2023-04-09T19:20:50.207006Z",
     "shell.execute_reply": "2023-04-09T19:20:50.206001Z",
     "shell.execute_reply.started": "2023-04-09T19:16:00.824752Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "make_dates_file(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77621212-018e-4e31-932e-5930377d2ecc",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T19:20:50.211005Z",
     "iopub.status.busy": "2023-04-09T19:20:50.211005Z",
     "iopub.status.idle": "2023-04-09T19:24:56.128967Z",
     "shell.execute_reply": "2023-04-09T19:24:56.128004Z",
     "shell.execute_reply.started": "2023-04-09T19:20:50.211005Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "make_linear_plots(f'files/df_dates_{name}.xlsx', rubrics, cnt=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "11db6194-b231-4215-a827-364836ca1996",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T15:45:42.759990Z",
     "iopub.status.busy": "2023-04-09T15:45:42.758986Z",
     "iopub.status.idle": "2023-04-09T15:45:42.778551Z",
     "shell.execute_reply": "2023-04-09T15:45:42.777575Z",
     "shell.execute_reply.started": "2023-04-09T15:45:42.759990Z"
    }
   },
   "outputs": [],
   "source": [
    "#files/df_dates_moscow_report_230409_1840.docx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fadbffe6-7056-4e09-b1ab-511daa8e4b9f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T20:29:04.375598Z",
     "iopub.status.busy": "2023-04-09T20:29:04.374603Z",
     "iopub.status.idle": "2023-04-09T20:29:05.154503Z",
     "shell.execute_reply": "2023-04-09T20:29:05.153585Z",
     "shell.execute_reply.started": "2023-04-09T20:29:04.375598Z"
    }
   },
   "outputs": [],
   "source": [
    "spark.stop()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f62378d-8c63-47f4-8cbc-2364862b02ea",
   "metadata": {},
   "source": [
    "# Алматы"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7459668c-a34c-4a2f-adba-abc6bea0d70c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T15:58:18.242721Z",
     "iopub.status.busy": "2023-04-09T15:58:18.241722Z",
     "iopub.status.idle": "2023-04-09T15:58:22.913118Z",
     "shell.execute_reply": "2023-04-09T15:58:22.912131Z",
     "shell.execute_reply.started": "2023-04-09T15:58:18.242721Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "rubrics = pd.read_excel(r\"C:\\Users\\Mamed\\YandexDisk\\_Documents\\Проекты\\2ГИС 2023\\Алматы\\Мастер_таблица_2ГИС_Алматы_230326_0030.xlsx\")[['Рубрика']].iloc[:70]\n",
    "name = 'almati'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c76d47ec-9a52-4935-ae47-50aa82181352",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T15:58:41.313621Z",
     "iopub.status.busy": "2023-04-09T15:58:41.312622Z",
     "iopub.status.idle": "2023-04-09T16:10:32.138452Z",
     "shell.execute_reply": "2023-04-09T16:10:32.136939Z",
     "shell.execute_reply.started": "2023-04-09T15:58:41.313621Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "make_dates_file(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e22b8687-880c-4cf1-8097-7a51c2b4fdc1",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T16:10:32.140158Z",
     "iopub.status.busy": "2023-04-09T16:10:32.140158Z",
     "iopub.status.idle": "2023-04-09T16:17:32.665093Z",
     "shell.execute_reply": "2023-04-09T16:17:32.663581Z",
     "shell.execute_reply.started": "2023-04-09T16:10:32.140158Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "make_linear_plots(f'files/df_dates_{name}.xlsx', rubrics, cnt=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ccac4614-db83-480a-a396-7af91e142fb0",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T16:20:57.824010Z",
     "iopub.status.busy": "2023-04-09T16:20:57.824010Z",
     "iopub.status.idle": "2023-04-09T16:20:57.843128Z",
     "shell.execute_reply": "2023-04-09T16:20:57.842127Z",
     "shell.execute_reply.started": "2023-04-09T16:20:57.824010Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#files/df_dates_almati_report_230409_1910.docx"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd58abab-0957-408b-9e2a-d932ebe133d0",
   "metadata": {},
   "source": [
    "# Астана"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d015561a-02a9-470b-9315-c6fc536fdd21",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T16:31:06.335342Z",
     "iopub.status.busy": "2023-04-09T16:31:06.335342Z",
     "iopub.status.idle": "2023-04-09T16:31:08.329906Z",
     "shell.execute_reply": "2023-04-09T16:31:08.328907Z",
     "shell.execute_reply.started": "2023-04-09T16:31:06.335342Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "rubrics = pd.read_excel(r\"C:\\Users\\Mamed\\YandexDisk\\_Documents\\Проекты\\2ГИС 2023\\Астана\\Мастер_таблица_2ГИС_Астана_230409_1645.xlsx\").sort_values('Запросы, итого', ascending=False)[['Рубрика']].iloc[:70]\n",
    "name = 'astana'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6125c37a-1498-4ea3-b9f2-7a4b6941ab5b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T16:31:20.947919Z",
     "iopub.status.busy": "2023-04-09T16:31:20.947919Z",
     "iopub.status.idle": "2023-04-09T16:37:40.169810Z",
     "shell.execute_reply": "2023-04-09T16:37:40.168886Z",
     "shell.execute_reply.started": "2023-04-09T16:31:20.947919Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "make_dates_file(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9661a23-bfcb-47c6-88d8-0a422099f802",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T16:37:40.171897Z",
     "iopub.status.busy": "2023-04-09T16:37:40.171897Z",
     "iopub.status.idle": "2023-04-09T16:45:18.641976Z",
     "shell.execute_reply": "2023-04-09T16:45:18.640974Z",
     "shell.execute_reply.started": "2023-04-09T16:37:40.171897Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "make_linear_plots(f'files/df_dates_{name}.xlsx', rubrics, cnt=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3ae50517-7940-42d3-be28-b09034927e72",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-09T16:20:57.824010Z",
     "iopub.status.busy": "2023-04-09T16:20:57.824010Z",
     "iopub.status.idle": "2023-04-09T16:20:57.843128Z",
     "shell.execute_reply": "2023-04-09T16:20:57.842127Z",
     "shell.execute_reply.started": "2023-04-09T16:20:57.824010Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#files/df_dates_astana_report_230409_1937.docx"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e670a76-187d-49a2-ac18-ed9b797859ba",
   "metadata": {},
   "source": [
    "# Калининград"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "47aef582-5b2b-4b76-8f3c-1b397c47bd13",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-15T07:50:25.158340Z",
     "iopub.status.busy": "2023-04-15T07:50:25.158340Z",
     "iopub.status.idle": "2023-04-15T07:50:28.034519Z",
     "shell.execute_reply": "2023-04-15T07:50:28.034519Z",
     "shell.execute_reply.started": "2023-04-15T07:50:25.158340Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "name = 'kaliningrad'\n",
    "rubrics = pd.read_excel(r\"C:\\Users\\Mamed\\YandexDisk\\_Documents\\Проекты\\2ГИС 2023\\Калининград\\Мастер_таблица_2ГИС_Калининград_230412_2345.xlsx\").sort_values('Запросы, итого',ascending=False)[['Рубрика']].iloc[:70]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3a3cf472-3925-4102-a986-b4326e320eec",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-15T07:50:29.409602Z",
     "iopub.status.busy": "2023-04-15T07:50:29.409602Z",
     "iopub.status.idle": "2023-04-15T07:52:36.617978Z",
     "shell.execute_reply": "2023-04-15T07:52:36.617978Z",
     "shell.execute_reply.started": "2023-04-15T07:50:29.409602Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "make_dates_file(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd3d75e6-ae75-41c1-8121-b5037aafc2e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#files/df_dates_kaliningrad_report_230415_1052.docx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "267cf607-60f5-4f5e-ac59-c4175b70387f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-15T08:11:51.205011Z",
     "iopub.status.busy": "2023-04-15T08:11:51.205011Z",
     "iopub.status.idle": "2023-04-15T08:17:55.288648Z",
     "shell.execute_reply": "2023-04-15T08:17:55.288648Z",
     "shell.execute_reply.started": "2023-04-15T08:11:51.205011Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "make_linear_plots(f'files/df_dates_{name}.xlsx', rubrics, cnt=25)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7bea63a3-ad88-4385-b43f-492ef093aafc",
   "metadata": {
    "tags": []
   },
   "source": [
    "# СПБ"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "7708a377-f18b-40d6-ba10-edf5e95ad58d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-01T20:40:38.398737Z",
     "iopub.status.busy": "2023-05-01T20:40:38.398737Z",
     "iopub.status.idle": "2023-05-01T20:40:38.562070Z",
     "shell.execute_reply": "2023-05-01T20:40:38.561060Z",
     "shell.execute_reply.started": "2023-05-01T20:40:38.398737Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "rubrics = pd.read_excel(r\"C:\\Users\\Mamed\\_Notebooks\\2GIS_2023\\files\\spb\\Мастер_таблица_2ГИС_СПБ_230322_1245.xlsx\")[['Рубрика']].iloc[:25]\n",
    "name = 'spb'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "61d4d211-4302-4b13-8870-684777bf4208",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-01T21:00:49.535823Z",
     "iopub.status.busy": "2023-05-01T21:00:49.535823Z",
     "iopub.status.idle": "2023-05-01T21:05:35.305214Z",
     "shell.execute_reply": "2023-05-01T21:05:35.304198Z",
     "shell.execute_reply.started": "2023-05-01T21:00:49.535823Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[########################################] | 100% Completed | 282.43 s\n"
     ]
    }
   ],
   "source": [
    "df1 = dd.read_csv(r\"D:\\файлы_2гис_2023\\СПБ\\СПБ_2020_974783_repacked.csv\\СПБ_2020_974783_repacked.csv.csv\", dtype={'fl': 'object'}, on_bad_lines='skip')[['rubricName','time', 'date','tx']]\n",
    "df2 = dd.read_csv(r\"D:\\файлы_2гис_2023\\СПБ\\СПБ_2021_974784_repacked.csv\\СПБ_2021_974784_repacked.csv.csv\", dtype={'fl': 'object'}, on_bad_lines='skip')[['rubricName','time', 'date','tx']]\n",
    "df3 = dd.read_csv(r\"D:\\файлы_2гис_2023\\СПБ\\СПБ_2022_974785_repacked.csv\\СПБ_2022_974785_repacked.csv.csv\", dtype={'fl': 'object'}, on_bad_lines='skip')[['rubricName','time', 'date','tx']]\n",
    "df4 = dd.read_csv(r\"D:\\файлы_2гис_2023\\СПБ\\СПБ_2023_974790_repacked.csv\\СПБ_2023_974790_repacked.csv.csv\", dtype={'fl': 'object'}, on_bad_lines='skip')[['rubricName','time', 'date','tx']]\n",
    "\n",
    "df_all = dd.concat([df1, df2, df3, df4])\n",
    "df_filtered = df_all.merge(rubrics, left_on='rubricName', right_on='Рубрика')\n",
    "df_filtered.to_parquet(f'files/df_filtered_{name}')\n",
    "df_filtered = dd.read_parquet(f'files/df_filtered_{name}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8a084fc2-fc08-489d-ad0b-b12006f161e8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-01T21:05:35.307215Z",
     "iopub.status.busy": "2023-05-01T21:05:35.307215Z",
     "iopub.status.idle": "2023-05-01T21:09:02.510121Z",
     "shell.execute_reply": "2023-05-01T21:09:02.509107Z",
     "shell.execute_reply.started": "2023-05-01T21:05:35.307215Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "make_dates_file(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "87e493a3-5b08-45a2-a1ca-8798b58082b9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-01T21:18:07.015017Z",
     "iopub.status.busy": "2023-05-01T21:18:07.015017Z",
     "iopub.status.idle": "2023-05-01T21:22:11.762644Z",
     "shell.execute_reply": "2023-05-01T21:22:11.761644Z",
     "shell.execute_reply.started": "2023-05-01T21:18:07.015017Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "files/df_dates_spb_report_230502_0018.docx\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████████████| 25/25 [04:04<00:00,  9.78s/it]\n"
     ]
    }
   ],
   "source": [
    "make_linear_plots(f'files/df_dates_{name}.xlsx', rubrics, cnt=25)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "298e983e-56ab-4446-b2b4-9e495c4a3ef5",
   "metadata": {},
   "source": [
    "# Баку"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "62e88542-c259-4200-b997-f29abbcd7411",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-19T20:22:12.132591Z",
     "iopub.status.busy": "2023-05-19T20:22:12.131592Z",
     "iopub.status.idle": "2023-05-19T20:22:12.543731Z",
     "shell.execute_reply": "2023-05-19T20:22:12.541925Z",
     "shell.execute_reply.started": "2023-05-19T20:22:12.132591Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "rubrics = pd.read_excel(r\"C:\\Users\\Mamed\\_Notebooks\\2GIS_2023\\files\\baku\\Мастер_таблица_2ГИС_Баку_230424_2100.xlsx\")[['Рубрика']].iloc[:25]\n",
    "name = 'baku'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e3e71aad-a748-46ba-932a-7c43d627f524",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-01T22:53:30.645868Z",
     "iopub.status.busy": "2023-05-01T22:53:30.645868Z",
     "iopub.status.idle": "2023-05-01T22:53:35.238883Z",
     "shell.execute_reply": "2023-05-01T22:53:35.238009Z",
     "shell.execute_reply.started": "2023-05-01T22:53:30.645868Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[########################################] | 100% Completed | 3.01 ss\n"
     ]
    }
   ],
   "source": [
    "df1 = dd.read_csv(r\"D:\\файлы_2гис_2023\\Баку\\Баку_2020_975740.csv\", dtype={'fl': 'object'}, on_bad_lines='skip')[['rubricName','time', 'date','tx']]\n",
    "df2 = dd.read_csv(r\"D:\\файлы_2гис_2023\\Баку\\Баку_2021_975746.csv\", dtype={'fl': 'object'}, on_bad_lines='skip')[['rubricName','time', 'date','tx']]\n",
    "df3 = dd.read_csv(r\"D:\\файлы_2гис_2023\\Баку\\Баку_2022_975747.csv\", dtype={'fl': 'object'}, on_bad_lines='skip')[['rubricName','time', 'date','tx']]\n",
    "df4 = dd.read_csv(r\"D:\\файлы_2гис_2023\\Баку\\Баку_2023_975748.csv\", dtype={'fl': 'object'}, on_bad_lines='skip')[['rubricName','time', 'date','tx']]\n",
    "\n",
    "df_all = dd.concat([df1, df2, df3, df4])\n",
    "df_filtered = df_all.merge(rubrics, left_on='rubricName', right_on='Рубрика')\n",
    "df_filtered.to_parquet(f'files/df_filtered_{name}')\n",
    "df_filtered = dd.read_parquet(f'files/df_filtered_{name}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5977c45c-71ec-4e23-874d-cec1710bd2d3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-19T20:22:17.486520Z",
     "iopub.status.busy": "2023-05-19T20:22:17.486520Z",
     "iopub.status.idle": "2023-05-19T20:23:00.505561Z",
     "shell.execute_reply": "2023-05-19T20:23:00.504856Z",
     "shell.execute_reply.started": "2023-05-19T20:22:17.486520Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "make_dates_file(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c42818c4-b1f1-4037-adff-77bf42616ebe",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-19T20:23:37.231224Z",
     "iopub.status.busy": "2023-05-19T20:23:37.231224Z",
     "iopub.status.idle": "2023-05-19T20:27:54.050860Z",
     "shell.execute_reply": "2023-05-19T20:27:54.048866Z",
     "shell.execute_reply.started": "2023-05-19T20:23:37.231224Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "files/df_dates_baku_report_230519_2323.docx\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "072b9a80ff2947578fe31c651d7de037",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/25 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "make_linear_plots(f'files/df_dates_{name}.xlsx', rubrics, cnt=25)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0da6fd5-3e8a-4e67-b392-c1f6edbf461c",
   "metadata": {},
   "source": [
    "# Владивосток"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "80451a1c-3876-4da4-a253-c2992b9529eb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-09T21:56:40.820618Z",
     "iopub.status.busy": "2023-05-09T21:56:40.820618Z",
     "iopub.status.idle": "2023-05-09T21:58:18.353745Z",
     "shell.execute_reply": "2023-05-09T21:58:18.352721Z",
     "shell.execute_reply.started": "2023-05-09T21:56:40.820618Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "name = 'vladivostok'\n",
    "rubrics = pd.read_excel('files/vladivostok/master_file_vladivostok.xlsx').sort_values('Запросы, итого',ascending=False)[['Рубрика']].iloc[:70]\n",
    "make_dates_file(name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "de320687-db3f-4612-9fd6-1e33dde3cdb2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-05-09T21:58:18.355743Z",
     "iopub.status.busy": "2023-05-09T21:58:18.354743Z",
     "iopub.status.idle": "2023-05-09T22:02:31.054784Z",
     "shell.execute_reply": "2023-05-09T22:02:31.052751Z",
     "shell.execute_reply.started": "2023-05-09T21:58:18.355743Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "files/df_dates_vladivostok_report_230510_0058.docx\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "af3f0d9bf77346ccad5c5b188ed22c6a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/25 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "make_linear_plots(f'files/df_dates_{name}.xlsx', rubrics, cnt=25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "402fd093-ea69-4166-9642-0916b975657a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd57850f-fed2-4a5a-bf93-13bca3810a9c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}