parsers/Archive_parser_horoscopes_fin.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4ec38f96",
   "metadata": {},
   "source": [
    "# Парсинг гороскопов в архивах сайтов\n",
    "\n",
    "## 1. Сайт https://sibmama.ru\n",
    "\n",
    "На этом сайте есть архив гороскопов на каждый день недели для всех занков зодиак за 2022-2016 годы. Попробуем собрать все данные и с привязкой к знаку Зодиака.\n",
    "Напишем функцию для сбора url по неделям и для получения данных за каждый день"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1b754be2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import fake_useragent\n",
    "from datetime import date\n",
    "from csv import writer\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "ua = fake_useragent.UserAgent()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "78e3653e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_url(url):\n",
    "    #url = f'https://sibmama.ru/index.htm?r=406'\n",
    "    response = requests.get(url, headers = {'user-agent': ua.random})\n",
    "    soup = BeautifulSoup(response.content, 'lxml')\n",
    "    data = soup.find_all(class_ = 'pgs')\n",
    "    for item in data:\n",
    "        ur = 'https://sibmama.ru/' + item.find('a').attrs['href']\n",
    "        yield ur\n",
    "\n",
    "def get_item(url):\n",
    "    horos = []\n",
    "    for url in get_url(url):\n",
    "        #print(url)\n",
    "        response = requests.get(url, headers = {'user-agent': ua.random})\n",
    "        soup = BeautifulSoup(response.content, 'lxml')\n",
    "        #data = soup.find('div')\n",
    "\n",
    "        days = soup.find_all('h3')\n",
    "        for day in days:\n",
    "            \n",
    "            zods = day.find_previous_siblings()\n",
    "            try:\n",
    "                for zod in zods:\n",
    "                    if (zod.name == 'h2') and (zod.text != '') and (day.text == 'Понедельник'):\n",
    "                        zodiac = zod.text\n",
    "                        break\n",
    "            except:\n",
    "                zodiac = 'Mistake'\n",
    "            hors = day.find_next_sibling()\n",
    "            \n",
    "            try:\n",
    "                horos.append(zodiac + '*' + hors.text)\n",
    "            except:\n",
    "                horos.append('Mistake')\n",
    "                    \n",
    "    yield horos"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d310b38",
   "metadata": {},
   "source": [
    "Для каждого года на сайте предусмотрен отдельный адрес, переберем их в цикле и запишем все списки в общий для последующей обработки."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fe30b5aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Log: working over https://sibmama.ru/index.htm?r=406 ...\n",
      "Log: working over https://sibmama.ru/index.htm?r=392 ...\n",
      "Log: working over https://sibmama.ru/index.htm?r=382 ...\n",
      "Log: working over https://sibmama.ru/index.htm?r=368 ...\n",
      "Log: working over https://sibmama.ru/index.htm?r=357 ...\n",
      "Log: working over https://sibmama.ru/index.htm?r=305 ...\n",
      "Log: working over https://sibmama.ru/index.htm?r=283 ...\n",
      "Log: working over https://sibmama.ru/index.htm?r=235 ...\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "8"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url_list = ['https://sibmama.ru/index.htm?r=406',\n",
    "            'https://sibmama.ru/index.htm?r=392',\n",
    "            'https://sibmama.ru/index.htm?r=382',\n",
    "            'https://sibmama.ru/index.htm?r=368',\n",
    "            'https://sibmama.ru/index.htm?r=357',\n",
    "            'https://sibmama.ru/index.htm?r=305',\n",
    "            'https://sibmama.ru/index.htm?r=283',\n",
    "            'https://sibmama.ru/index.htm?r=235'\n",
    "           ]\n",
    "\n",
    "total = []\n",
    "for url in url_list:\n",
    "    print(f'Log: working over {url} ...')\n",
    "    total.extend(get_item(url))\n",
    "len(total) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "3e902474",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>zodiac</th>\n",
       "      <th>horo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Овен*Последний понедельник уходящего года запо...</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Последний понедельник уходящего года запомнитс...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Овен*Во вторник звёзды рекомендуют вам… выключ...</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Во вторник звёзды рекомендуют вам… выключить м...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Овен*Сегодня у представителей огненной стихии ...</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Сегодня у представителей огненной стихии возмо...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Овен*В этот день представителям огненной стихи...</td>\n",
       "      <td>Овен</td>\n",
       "      <td>В этот день представителям огненной стихии мож...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Овен*Сегодня Овнам вдруг захочется позвонить с...</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Сегодня Овнам вдруг захочется позвонить старым...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Овен*Все вокруг носятся, стараются, готовятся ...</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Все вокруг носятся, стараются, готовятся к пра...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Овен*Вся страна лежит и лениво потягивается, п...</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Вся страна лежит и лениво потягивается, погляд...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Овен*</td>\n",
       "      <td>Овен</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Телец*Сегодня Тельцу захочется сделать столько...</td>\n",
       "      <td>Телец</td>\n",
       "      <td>Сегодня Тельцу захочется сделать столько, будт...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Телец*День новостей, известий и перемен. В как...</td>\n",
       "      <td>Телец</td>\n",
       "      <td>День новостей, известий и перемен. В какой-то ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0 zodiac  \\\n",
       "0  Овен*Последний понедельник уходящего года запо...   Овен   \n",
       "1  Овен*Во вторник звёзды рекомендуют вам… выключ...   Овен   \n",
       "2  Овен*Сегодня у представителей огненной стихии ...   Овен   \n",
       "3  Овен*В этот день представителям огненной стихи...   Овен   \n",
       "4  Овен*Сегодня Овнам вдруг захочется позвонить с...   Овен   \n",
       "5  Овен*Все вокруг носятся, стараются, готовятся ...   Овен   \n",
       "6  Овен*Вся страна лежит и лениво потягивается, п...   Овен   \n",
       "7                                             Овен*    Овен   \n",
       "8  Телец*Сегодня Тельцу захочется сделать столько...  Телец   \n",
       "9  Телец*День новостей, известий и перемен. В как...  Телец   \n",
       "\n",
       "                                                horo  \n",
       "0  Последний понедельник уходящего года запомнитс...  \n",
       "1  Во вторник звёзды рекомендуют вам… выключить м...  \n",
       "2  Сегодня у представителей огненной стихии возмо...  \n",
       "3  В этот день представителям огненной стихии мож...  \n",
       "4  Сегодня Овнам вдруг захочется позвонить старым...  \n",
       "5  Все вокруг носятся, стараются, готовятся к пра...  \n",
       "6  Вся страна лежит и лениво потягивается, погляд...  \n",
       "7                                                     \n",
       "8  Сегодня Тельцу захочется сделать столько, будт...  \n",
       "9  День новостей, известий и перемен. В какой-то ...  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flat_list = [item for sublist in total for item in sublist]\n",
    "df = pd.DataFrame(flat_list)\n",
    "df['zodiac'] = df[0].apply(lambda x: str(x).split(\"*\")[0])\n",
    "df['horo'] = df[0].apply(lambda x: str(x).split(\"*\")[1:])\n",
    "df['horo'] = df['horo'].apply(lambda x: ''.join(x))\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "f3d6c946",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['zodiac'] != 'Mistake']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "2af51d8a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace('\\xa0', ''))\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace('ОВЕН', 'Овен'))\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: 'Дева' if x == '' else x)\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace(' ', ''))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "b28188bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Лев         2137\n",
       "Рыбы        1989\n",
       "Козерог     1935\n",
       "Телец       1756\n",
       "Скорпион    1753\n",
       "Близнецы    1746\n",
       "Овен        1744\n",
       "Весы        1732\n",
       "Стрелец     1697\n",
       "Рак         1682\n",
       "Водолей     1554\n",
       "Дева        1288\n",
       "Name: zodiac, dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['zodiac'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "c9e800a0",
   "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>date</th>\n",
       "      <th>zodiac</th>\n",
       "      <th>horo</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-08-10</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Последний понедельник уходящего года запомнитс...</td>\n",
       "      <td>https://sibmama.ru/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-08-10</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Во вторник звёзды рекомендуют вам… выключить м...</td>\n",
       "      <td>https://sibmama.ru/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-08-10</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Сегодня у представителей огненной стихии возмо...</td>\n",
       "      <td>https://sibmama.ru/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-08-10</td>\n",
       "      <td>Овен</td>\n",
       "      <td>В этот день представителям огненной стихии мож...</td>\n",
       "      <td>https://sibmama.ru/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-08-10</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Сегодня Овнам вдруг захочется позвонить старым...</td>\n",
       "      <td>https://sibmama.ru/</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date zodiac                                               horo  \\\n",
       "0  2023-08-10   Овен  Последний понедельник уходящего года запомнитс...   \n",
       "1  2023-08-10   Овен  Во вторник звёзды рекомендуют вам… выключить м...   \n",
       "2  2023-08-10   Овен  Сегодня у представителей огненной стихии возмо...   \n",
       "3  2023-08-10   Овен  В этот день представителям огненной стихии мож...   \n",
       "4  2023-08-10   Овен  Сегодня Овнам вдруг захочется позвонить старым...   \n",
       "\n",
       "                source  \n",
       "0  https://sibmama.ru/  \n",
       "1  https://sibmama.ru/  \n",
       "2  https://sibmama.ru/  \n",
       "3  https://sibmama.ru/  \n",
       "4  https://sibmama.ru/  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from datetime import date\n",
    "df['date'] = date.today()\n",
    "df['source'] = 'https://sibmama.ru/'\n",
    "df = df[['date','zodiac', 'horo', 'source']]\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6b9fc352",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 21013 entries, 0 to 21411\n",
      "Data columns (total 4 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   date    21013 non-null  object\n",
      " 1   zodiac  21013 non-null  object\n",
      " 2   horo    21013 non-null  object\n",
      " 3   source  21013 non-null  object\n",
      "dtypes: object(4)\n",
      "memory usage: 820.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7205440",
   "metadata": {},
   "source": [
    "Информация обработана, приведена в форму для анализа. Добавим ее в общий файл, horo1.csv, куда собираются все данные"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "410f8c33",
   "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>date</th>\n",
       "      <th>zodiac</th>\n",
       "      <th>horo</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Намерения ваши прекрасны, но время для их осущ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Телец</td>\n",
       "      <td>Ваше сострадание и понимание бесконечны. Вы по...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Близнецы</td>\n",
       "      <td>Небольшое неудобство, которое вам придется пре...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Рак</td>\n",
       "      <td>Привычные глазу лица и предметы вряд ли смогут...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Лев</td>\n",
       "      <td>Сегодня вы будете равным среди великих. У вас ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date    zodiac                                               horo  \\\n",
       "0  2023-08-06      Овен  Намерения ваши прекрасны, но время для их осущ...   \n",
       "1  2023-08-06     Телец  Ваше сострадание и понимание бесконечны. Вы по...   \n",
       "2  2023-08-06  Близнецы  Небольшое неудобство, которое вам придется пре...   \n",
       "3  2023-08-06       Рак  Привычные глазу лица и предметы вряд ли смогут...   \n",
       "4  2023-08-06       Лев  Сегодня вы будете равным среди великих. У вас ...   \n",
       "\n",
       "                           source  \n",
       "0  https://74.ru/horoscope/daily/  \n",
       "1  https://74.ru/horoscope/daily/  \n",
       "2  https://74.ru/horoscope/daily/  \n",
       "3  https://74.ru/horoscope/daily/  \n",
       "4  https://74.ru/horoscope/daily/  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv('C:/Users/Kate/Desktop/Docker/sqlite/horo1.csv')\n",
    "df1.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "0bcf91c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "new = pd.concat([df1, df])\n",
    "new.to_csv('C:/Users/Kate/Desktop/Docker/sqlite/horo1.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21184a5d",
   "metadata": {},
   "source": [
    "##  2. Сайт https://sakhalife.ru/category/goroskop/\n",
    "\n",
    "На данном сайте также есть архив гороскопов на 67 страницах, попробуем их спарсить с учетом знака зодиака"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "5da159e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_url():\n",
    "    for page in range(1,tot_pages+1):\n",
    "        \n",
    "        if page % log_frequency == 0:\n",
    "            print(f'Log: working over page {page}...')\n",
    "        url = f'https://sakhalife.ru/category/goroskop/page/{page}/'\n",
    "        response = requests.get(url, headers = {'user-agent': ua.random})\n",
    "        soup = BeautifulSoup(response.content, 'lxml')\n",
    "        data = soup.find_all(class_ = {'cat-top-rows'})\n",
    "        data2 = soup.find_all(class_ = {'cat-row3 cat-row'})\n",
    "        data3 = soup.find_all(class_ = {'cat-row4 cat-row'})\n",
    "        data_fin = data+data2+data3\n",
    "        for item in data_fin:\n",
    "            url = item.find_all('a', href = True)\n",
    "            for item in url:\n",
    "                if item.attrs['href'].find('https://sakhalife.ru/ezhednevnyj-goroskop') != -1:\n",
    "                    url = item.attrs['href']\n",
    "                    yield url\n",
    "        \n",
    "                \n",
    "def get_item():\n",
    "    for url in get_url():\n",
    "        response = requests.get(url, headers = {'user-agent': ua.random})\n",
    "        soup = BeautifulSoup(response.content, 'lxml')\n",
    "        data = soup.find_all(class_ = {'post-content-inner'})\n",
    "        for item in data:\n",
    "            try:\n",
    "                title = item.find_all('h2')\n",
    "                title = title[1:-1]\n",
    "                title_lst = []\n",
    "                for x in title:\n",
    "                    zodiac = x.text.split(' ')[0]\n",
    "                    title_lst.append(zodiac)\n",
    "            except:\n",
    "                zodiac = ''\n",
    "                title_lst.append(zodiac)\n",
    "            try:\n",
    "                test = item.find_all('p')\n",
    "                test = test[2:-1]\n",
    "                horo_lst = []\n",
    "                for item in test:\n",
    "                    horo_lst.append(item.text.replace('\\xa0', ' '))\n",
    "            except:\n",
    "                horo_lst.append('')\n",
    "                \n",
    "            fin = list(zip(title_lst, horo_lst))\n",
    "            #print(url)\n",
    "            \n",
    "            yield fin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "9a83fc8c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100 pages saved in csv..\n",
      "Log: working over page 10...\n",
      "200 pages saved in csv..\n",
      "Log: working over page 20...\n",
      "300 pages saved in csv..\n",
      "Log: working over page 30...\n",
      "400 pages saved in csv..\n",
      "500 pages saved in csv..\n",
      "Log: working over page 40...\n",
      "600 pages saved in csv..\n",
      "Log: working over page 50...\n",
      "700 pages saved in csv..\n",
      "800 pages saved in csv..\n",
      "Log: working over page 60...\n",
      "900 pages saved in csv..\n",
      "\n",
      "Total pages saved: 933\n"
     ]
    }
   ],
   "source": [
    "tot_pages = 67\n",
    "log_frequency = 10\n",
    "save_frequency = 100\n",
    "result = []\n",
    "counter = 0\n",
    "\n",
    "for item in get_item():\n",
    "    result.extend(item)\n",
    "    counter += 1\n",
    "    if counter % save_frequency == 0:\n",
    "        df = pd.DataFrame(result)\n",
    "        df.to_csv('horo_temp.csv')\n",
    "        print(f'{counter} pages saved in csv..')\n",
    "df = pd.DataFrame(result)\n",
    "df.to_csv('horo_fin.csv')\n",
    "print(f'\\nTotal pages saved: {counter}') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "95f3a052",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Телец       620\n",
       "Близнецы    620\n",
       "Рак         620\n",
       "Лев         620\n",
       "Дева        620\n",
       "Весы        620\n",
       "Скорпион    620\n",
       "Стрелец     620\n",
       "Козерог     620\n",
       "Водолей     620\n",
       "Овен        616\n",
       "Рыбы        605\n",
       "Name: 0, dtype: int64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[0].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "f9b735f5",
   "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>date</th>\n",
       "      <th>zodiac</th>\n",
       "      <th>horo</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-08-10</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Хотя сегодня вам не терпится поговорить с друг...</td>\n",
       "      <td>https://sakhalife.ru/category/goroskop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-08-10</td>\n",
       "      <td>Телец</td>\n",
       "      <td>Сегодня хороший день, если вы можете избежать ...</td>\n",
       "      <td>https://sakhalife.ru/category/goroskop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-08-10</td>\n",
       "      <td>Близнецы</td>\n",
       "      <td>Сегодня Луна находится в вашем знаке, что дает...</td>\n",
       "      <td>https://sakhalife.ru/category/goroskop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-08-10</td>\n",
       "      <td>Рак</td>\n",
       "      <td>Это тот день, когда то жарко, то холодно. Обсу...</td>\n",
       "      <td>https://sakhalife.ru/category/goroskop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-08-10</td>\n",
       "      <td>Лев</td>\n",
       "      <td>В принципе, отношения с друзьями и членами гру...</td>\n",
       "      <td>https://sakhalife.ru/category/goroskop</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date    zodiac                                               horo  \\\n",
       "0  2023-08-10      Овен  Хотя сегодня вам не терпится поговорить с друг...   \n",
       "1  2023-08-10     Телец  Сегодня хороший день, если вы можете избежать ...   \n",
       "2  2023-08-10  Близнецы  Сегодня Луна находится в вашем знаке, что дает...   \n",
       "3  2023-08-10       Рак  Это тот день, когда то жарко, то холодно. Обсу...   \n",
       "4  2023-08-10       Лев  В принципе, отношения с друзьями и членами гру...   \n",
       "\n",
       "                                   source  \n",
       "0  https://sakhalife.ru/category/goroskop  \n",
       "1  https://sakhalife.ru/category/goroskop  \n",
       "2  https://sakhalife.ru/category/goroskop  \n",
       "3  https://sakhalife.ru/category/goroskop  \n",
       "4  https://sakhalife.ru/category/goroskop  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['source'] = 'https://sakhalife.ru/category/goroskop'\n",
    "df = df.rename(columns={0:'zodiac', 1:'horo'})\n",
    "df['date'] = date.today()\n",
    "df = df[['date','zodiac', 'horo', 'source']]\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "25649f28",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 7421 entries, 0 to 7420\n",
      "Data columns (total 4 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   date    7421 non-null   object\n",
      " 1   zodiac  7421 non-null   object\n",
      " 2   horo    7421 non-null   object\n",
      " 3   source  7421 non-null   object\n",
      "dtypes: object(4)\n",
      "memory usage: 232.0+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "241b5bba",
   "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>date</th>\n",
       "      <th>zodiac</th>\n",
       "      <th>horo</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Намерения ваши прекрасны, но время для их осущ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Телец</td>\n",
       "      <td>Ваше сострадание и понимание бесконечны. Вы по...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Близнецы</td>\n",
       "      <td>Небольшое неудобство, которое вам придется пре...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Рак</td>\n",
       "      <td>Привычные глазу лица и предметы вряд ли смогут...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Лев</td>\n",
       "      <td>Сегодня вы будете равным среди великих. У вас ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date    zodiac                                               horo  \\\n",
       "0  2023-08-06      Овен  Намерения ваши прекрасны, но время для их осущ...   \n",
       "1  2023-08-06     Телец  Ваше сострадание и понимание бесконечны. Вы по...   \n",
       "2  2023-08-06  Близнецы  Небольшое неудобство, которое вам придется пре...   \n",
       "3  2023-08-06       Рак  Привычные глазу лица и предметы вряд ли смогут...   \n",
       "4  2023-08-06       Лев  Сегодня вы будете равным среди великих. У вас ...   \n",
       "\n",
       "                           source  \n",
       "0  https://74.ru/horoscope/daily/  \n",
       "1  https://74.ru/horoscope/daily/  \n",
       "2  https://74.ru/horoscope/daily/  \n",
       "3  https://74.ru/horoscope/daily/  \n",
       "4  https://74.ru/horoscope/daily/  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv('C:/Users/Kate/Desktop/Docker/sqlite/horo1.csv')\n",
    "df1.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "efd20416",
   "metadata": {},
   "outputs": [],
   "source": [
    "new = pd.concat([df1, df])\n",
    "new.to_csv('C:/Users/Kate/Desktop/Docker/sqlite/horo1.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "efba9bcd",
   "metadata": {},
   "source": [
    "## 3. Сайт http://vuslon.ru/news/rubric/list/goroskop-na-kazhdyy-den\n",
    "\n",
    "Есть небольшой архив ежедневных гороскопов до октября 2022 года. Попробуем скачать и его"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c8cc23cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_url():\n",
    "    for page in range(1, tot_pages+1):\n",
    "        if page % log_frequency == 0:\n",
    "            print(f'Log: working over page {page}...')\n",
    "        url = f'http://vuslon.ru/news/rubric/list/goroskop-na-kazhdyy-den/page/{page}'\n",
    "        response = requests.get(url, headers = {'user-agent': ua.random})\n",
    "        soup = BeautifulSoup(response.content, 'lxml')\n",
    "        data = soup.find_all(class_ = 'all-news__list all-news__list_image')\n",
    "        for item in data:\n",
    "            item_url = item.find('a').attrs['href']\n",
    "            yield item_url\n",
    "                \n",
    "def get_horo():\n",
    "    for url in get_url():\n",
    "        zod_lst = []\n",
    "        horo_lst = []\n",
    "        response = requests.get(url, headers = {'user-agent': ua.random})\n",
    "        soup = BeautifulSoup(response.content, 'lxml')\n",
    "        data = soup.find(class_ = 'news-main-container__paragraph')\n",
    "        \n",
    "        desc= data.find_all('p')\n",
    "        for item in desc:\n",
    "            try: \n",
    "                horo = item.text\n",
    "                horo = horo.replace('\\r', '  ')\n",
    "                lst = horo.split('  ')\n",
    "                for h in lst:\n",
    "                    h = h.replace('\\n', '')\n",
    "                    h = h.replace('\\xa0', ' ')\n",
    "                    \n",
    "                    if (h == 'Реклама') or (h == '') or (h == 'Источник https://sovainfo.ru/') or (h == 'Источник \"Гадалкин дом\"'):\n",
    "                        pass\n",
    "                    else:\n",
    "                        if h.lower().strip() in zodiac_lst:\n",
    "                            zodiac = h.strip()\n",
    "                            zod_lst.append(zodiac)\n",
    "                            \n",
    "                        else:\n",
    "                            horo = h.strip()\n",
    "                            horo_lst.append(horo)\n",
    "                        \n",
    "            except:\n",
    "                zodiac = ''\n",
    "                horo = ''\n",
    "\n",
    "        \n",
    "        yield zod_lst, horo_lst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "61248b98",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Log: working over page 5...\n",
      "Log: working over page 10...\n",
      "Log: working over page 15...\n",
      "Log: working over page 20...\n",
      "300\n",
      "300\n"
     ]
    }
   ],
   "source": [
    "zodiac_lst = ['близнецы', 'весы', 'водолеи', 'водолей', 'дева', 'девы', 'козерог', 'козероги', 'лев', 'львы', 'овен',\n",
    "             'овны', 'рак', 'раки', 'рыбы', 'скорпион', 'скорпионы', 'стрелец', 'стрельцы', 'телец', 'тельцы']\n",
    "\n",
    "log_frequency = 5\n",
    "tot_pages = 20\n",
    "\n",
    "total_zod = []\n",
    "total_horo = []\n",
    "for zod, horo in get_horo():\n",
    "    total_zod.append(zod)\n",
    "    total_horo.append(horo)\n",
    "\n",
    "print(len(total_zod))\n",
    "print(len(total_horo))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "11aa4b53",
   "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>zodiac</th>\n",
       "      <th>horo</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ОВНЫ</td>\n",
       "      <td>Наступили дни, когда пора затянуть ремни потуж...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ТЕЛЬЦЫ</td>\n",
       "      <td>Сегодня ждите полезную информацию для вашего б...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>БЛИЗНЕЦЫ</td>\n",
       "      <td>На сегодняшний день сложилось впечатление неув...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>РАКИ</td>\n",
       "      <td>Погодные изменения скажутся на самочувствии. В...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ЛЬВЫ</td>\n",
       "      <td>В этот день захочется сменить место работы. Пр...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>СКОРПИОНЫ</td>\n",
       "      <td>Скорпионы могут оказаться перед каким-то непро...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>СТРЕЛЬЦЫ</td>\n",
       "      <td>11 октября Стрельцы могут что-то забыть или пе...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>КОЗЕРОГИ</td>\n",
       "      <td>11 октября Козерогам может понадобиться поддер...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ВОДОЛЕИ</td>\n",
       "      <td>11 октября у Водолеев может появиться неожидан...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>РЫБЫ</td>\n",
       "      <td>11 октября Рыбам нужно действовать смело и нет...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3576 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       zodiac                                               horo\n",
       "0        ОВНЫ  Наступили дни, когда пора затянуть ремни потуж...\n",
       "1      ТЕЛЬЦЫ  Сегодня ждите полезную информацию для вашего б...\n",
       "2    БЛИЗНЕЦЫ  На сегодняшний день сложилось впечатление неув...\n",
       "3        РАКИ  Погодные изменения скажутся на самочувствии. В...\n",
       "4        ЛЬВЫ  В этот день захочется сменить место работы. Пр...\n",
       "..        ...                                                ...\n",
       "7   СКОРПИОНЫ  Скорпионы могут оказаться перед каким-то непро...\n",
       "8    СТРЕЛЬЦЫ  11 октября Стрельцы могут что-то забыть или пе...\n",
       "9    КОЗЕРОГИ  11 октября Козерогам может понадобиться поддер...\n",
       "10    ВОДОЛЕИ  11 октября у Водолеев может появиться неожидан...\n",
       "11       РЫБЫ  11 октября Рыбам нужно действовать смело и нет...\n",
       "\n",
       "[3576 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(columns = ['zodiac', 'horo'])\n",
    "for i in range(len(total_zod)):\n",
    "    temp = pd.DataFrame(list(zip(total_zod[i], total_horo[i])), columns = ['zodiac', 'horo'])\n",
    "    df = pd.concat([df, temp])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bc932210",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop_duplicates(subset='horo', keep = 'first', inplace = False)\n",
    "df = df[df['horo'] != '']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "e8c8276f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.lower())\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace('водолеи', 'Водолей'))\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace('девы', 'Дева'))\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace('козероги', 'Козерог'))\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace('львы', 'Лев'))\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace('раки', 'Рак'))\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace('скорпионы', 'Скорпион'))\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace('стрельцы', 'Стрелец'))\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace('тельцы', 'Телец'))\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.replace('овны', 'Овен'))\n",
    "df['zodiac'] = df['zodiac'].apply(lambda x: x.capitalize())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "6913ca54",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Овен        298\n",
       "Весы        298\n",
       "Стрелец     298\n",
       "Водолей     298\n",
       "Лев         297\n",
       "Близнецы    296\n",
       "Скорпион    296\n",
       "Телец       295\n",
       "Козерог     295\n",
       "Рак         294\n",
       "Рыбы        294\n",
       "Дева        293\n",
       "Name: zodiac, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['zodiac'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d6e20e94",
   "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>date</th>\n",
       "      <th>zodiac</th>\n",
       "      <th>horo</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-08-11</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Наступили дни, когда пора затянуть ремни потуж...</td>\n",
       "      <td>http://vuslon.ru/news/rubric/list/goroskop-na-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-08-11</td>\n",
       "      <td>Телец</td>\n",
       "      <td>Сегодня ждите полезную информацию для вашего б...</td>\n",
       "      <td>http://vuslon.ru/news/rubric/list/goroskop-na-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-08-11</td>\n",
       "      <td>Близнецы</td>\n",
       "      <td>На сегодняшний день сложилось впечатление неув...</td>\n",
       "      <td>http://vuslon.ru/news/rubric/list/goroskop-na-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-08-11</td>\n",
       "      <td>Рак</td>\n",
       "      <td>Погодные изменения скажутся на самочувствии. В...</td>\n",
       "      <td>http://vuslon.ru/news/rubric/list/goroskop-na-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-08-11</td>\n",
       "      <td>Лев</td>\n",
       "      <td>В этот день захочется сменить место работы. Пр...</td>\n",
       "      <td>http://vuslon.ru/news/rubric/list/goroskop-na-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date    zodiac                                               horo  \\\n",
       "0  2023-08-11      Овен  Наступили дни, когда пора затянуть ремни потуж...   \n",
       "1  2023-08-11     Телец  Сегодня ждите полезную информацию для вашего б...   \n",
       "2  2023-08-11  Близнецы  На сегодняшний день сложилось впечатление неув...   \n",
       "3  2023-08-11       Рак  Погодные изменения скажутся на самочувствии. В...   \n",
       "4  2023-08-11       Лев  В этот день захочется сменить место работы. Пр...   \n",
       "\n",
       "                                              source  \n",
       "0  http://vuslon.ru/news/rubric/list/goroskop-na-...  \n",
       "1  http://vuslon.ru/news/rubric/list/goroskop-na-...  \n",
       "2  http://vuslon.ru/news/rubric/list/goroskop-na-...  \n",
       "3  http://vuslon.ru/news/rubric/list/goroskop-na-...  \n",
       "4  http://vuslon.ru/news/rubric/list/goroskop-na-...  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['source'] = 'http://vuslon.ru/news/rubric/list/goroskop-na-kazhdyy-den'\n",
    "df['date'] = date.today()\n",
    "df = df[['date','zodiac', 'horo', 'source']]\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "7b0348d3",
   "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>date</th>\n",
       "      <th>zodiac</th>\n",
       "      <th>horo</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Намерения ваши прекрасны, но время для их осущ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Телец</td>\n",
       "      <td>Ваше сострадание и понимание бесконечны. Вы по...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Близнецы</td>\n",
       "      <td>Небольшое неудобство, которое вам придется пре...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Рак</td>\n",
       "      <td>Привычные глазу лица и предметы вряд ли смогут...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Лев</td>\n",
       "      <td>Сегодня вы будете равным среди великих. У вас ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date    zodiac                                               horo  \\\n",
       "0  2023-08-06      Овен  Намерения ваши прекрасны, но время для их осущ...   \n",
       "1  2023-08-06     Телец  Ваше сострадание и понимание бесконечны. Вы по...   \n",
       "2  2023-08-06  Близнецы  Небольшое неудобство, которое вам придется пре...   \n",
       "3  2023-08-06       Рак  Привычные глазу лица и предметы вряд ли смогут...   \n",
       "4  2023-08-06       Лев  Сегодня вы будете равным среди великих. У вас ...   \n",
       "\n",
       "                           source  \n",
       "0  https://74.ru/horoscope/daily/  \n",
       "1  https://74.ru/horoscope/daily/  \n",
       "2  https://74.ru/horoscope/daily/  \n",
       "3  https://74.ru/horoscope/daily/  \n",
       "4  https://74.ru/horoscope/daily/  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv('C:/Users/Kate/Desktop/Docker/sqlite/horo1.csv')\n",
    "df1.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f3a3e84e",
   "metadata": {},
   "outputs": [],
   "source": [
    "new = pd.concat([df1, df])\n",
    "new.to_csv('C:/Users/Kate/Desktop/Docker/sqlite/horo1.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "0b857ada",
   "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>date</th>\n",
       "      <th>zodiac</th>\n",
       "      <th>horo</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Намерения ваши прекрасны, но время для их осущ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Телец</td>\n",
       "      <td>Ваше сострадание и понимание бесконечны. Вы по...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Близнецы</td>\n",
       "      <td>Небольшое неудобство, которое вам придется пре...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Рак</td>\n",
       "      <td>Привычные глазу лица и предметы вряд ли смогут...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Лев</td>\n",
       "      <td>Сегодня вы будете равным среди великих. У вас ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32679</th>\n",
       "      <td>2023-08-11</td>\n",
       "      <td>Скорпион</td>\n",
       "      <td>Скорпионы могут оказаться перед каким-то непро...</td>\n",
       "      <td>http://vuslon.ru/news/rubric/list/goroskop-na-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32680</th>\n",
       "      <td>2023-08-11</td>\n",
       "      <td>Стрелец</td>\n",
       "      <td>11 октября Стрельцы могут что-то забыть или пе...</td>\n",
       "      <td>http://vuslon.ru/news/rubric/list/goroskop-na-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32681</th>\n",
       "      <td>2023-08-11</td>\n",
       "      <td>Козерог</td>\n",
       "      <td>11 октября Козерогам может понадобиться поддер...</td>\n",
       "      <td>http://vuslon.ru/news/rubric/list/goroskop-na-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32682</th>\n",
       "      <td>2023-08-11</td>\n",
       "      <td>Водолей</td>\n",
       "      <td>11 октября у Водолеев может появиться неожидан...</td>\n",
       "      <td>http://vuslon.ru/news/rubric/list/goroskop-na-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32683</th>\n",
       "      <td>2023-08-11</td>\n",
       "      <td>Рыбы</td>\n",
       "      <td>11 октября Рыбам нужно действовать смело и нет...</td>\n",
       "      <td>http://vuslon.ru/news/rubric/list/goroskop-na-...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>32684 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             date    zodiac  \\\n",
       "0      2023-08-06      Овен   \n",
       "1      2023-08-06     Телец   \n",
       "2      2023-08-06  Близнецы   \n",
       "3      2023-08-06       Рак   \n",
       "4      2023-08-06       Лев   \n",
       "...           ...       ...   \n",
       "32679  2023-08-11  Скорпион   \n",
       "32680  2023-08-11   Стрелец   \n",
       "32681  2023-08-11   Козерог   \n",
       "32682  2023-08-11   Водолей   \n",
       "32683  2023-08-11      Рыбы   \n",
       "\n",
       "                                                    horo  \\\n",
       "0      Намерения ваши прекрасны, но время для их осущ...   \n",
       "1      Ваше сострадание и понимание бесконечны. Вы по...   \n",
       "2      Небольшое неудобство, которое вам придется пре...   \n",
       "3      Привычные глазу лица и предметы вряд ли смогут...   \n",
       "4      Сегодня вы будете равным среди великих. У вас ...   \n",
       "...                                                  ...   \n",
       "32679  Скорпионы могут оказаться перед каким-то непро...   \n",
       "32680  11 октября Стрельцы могут что-то забыть или пе...   \n",
       "32681  11 октября Козерогам может понадобиться поддер...   \n",
       "32682  11 октября у Водолеев может появиться неожидан...   \n",
       "32683  11 октября Рыбам нужно действовать смело и нет...   \n",
       "\n",
       "                                                  source  \n",
       "0                         https://74.ru/horoscope/daily/  \n",
       "1                         https://74.ru/horoscope/daily/  \n",
       "2                         https://74.ru/horoscope/daily/  \n",
       "3                         https://74.ru/horoscope/daily/  \n",
       "4                         https://74.ru/horoscope/daily/  \n",
       "...                                                  ...  \n",
       "32679  http://vuslon.ru/news/rubric/list/goroskop-na-...  \n",
       "32680  http://vuslon.ru/news/rubric/list/goroskop-na-...  \n",
       "32681  http://vuslon.ru/news/rubric/list/goroskop-na-...  \n",
       "32682  http://vuslon.ru/news/rubric/list/goroskop-na-...  \n",
       "32683  http://vuslon.ru/news/rubric/list/goroskop-na-...  \n",
       "\n",
       "[32684 rows x 4 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = pd.read_csv('C:/Users/Kate/Desktop/Docker/sqlite/horo1.csv')\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3bd4baee",
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "def get_url():\n",
    "    for page in range(1200, tot_pages+1):\n",
    "        if page % log_frequency == 0:\n",
    "            print(f'Log: working over page {page}...')\n",
    "        url = f'https://mygazeta.com/category/%D0%B3%D0%BE%D1%80%D0%BE%D1%81%D0%BA%D0%BE%D0%BF/page/{page}?ysclid=llgqbm765s403390480'\n",
    "        response = requests.get(url, headers = {'user-agent': ua.random})\n",
    "        soup = BeautifulSoup(response.content, 'lxml')\n",
    "        data = soup.find_all('a', rel=\"bookmark\")\n",
    "        for item in data:\n",
    "            if bool(re.search(pattern = re.escape('Полный текст'), string = item.text)):\n",
    "                item_url = item['href']\n",
    "                yield item_url\n",
    "                \n",
    "def get_horo():\n",
    "    for url_item in get_url():\n",
    "        response = requests.get(url_item, headers = {'user-agent': ua.random})\n",
    "        soup = BeautifulSoup(response.content, 'lxml')\n",
    "        data= soup.find_all('h3')\n",
    "        for item in data:\n",
    "            if bool(re.search(pattern = re.escape('['), string = item.text)):\n",
    "                zodiac = item.text\n",
    "                zodiac = re.sub(re.compile('[^А-Яа-я ]'), '', zodiac).strip()\n",
    "                zodiac = zodiac.split(' ')[-1]\n",
    "                horo = item.findNext('p').text.strip()\n",
    "                yield zodiac, horo\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "db435926",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_columns = ['zodiac', 'horo']\n",
    "df = pd.DataFrame(columns = df_columns)\n",
    "save_frequency = 2000\n",
    "log_frequency = 300\n",
    "tot_pages = 4582"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "44496a45",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Log: working over page 1200...\n",
      "2000 items saved in csv..\n",
      "4000 items saved in csv..\n",
      "6000 items saved in csv..\n",
      "8000 items saved in csv..\n",
      "10000 items saved in csv..\n",
      "12000 items saved in csv..\n",
      "14000 items saved in csv..\n",
      "16000 items saved in csv..\n",
      "18000 items saved in csv..\n",
      "20000 items saved in csv..\n",
      "22000 items saved in csv..\n",
      "24000 items saved in csv..\n",
      "Log: working over page 1500...\n",
      "26000 items saved in csv..\n",
      "28000 items saved in csv..\n",
      "30000 items saved in csv..\n",
      "32000 items saved in csv..\n",
      "34000 items saved in csv..\n",
      "36000 items saved in csv..\n",
      "38000 items saved in csv..\n",
      "40000 items saved in csv..\n",
      "42000 items saved in csv..\n",
      "Log: working over page 1800...\n",
      "44000 items saved in csv..\n",
      "46000 items saved in csv..\n",
      "48000 items saved in csv..\n",
      "50000 items saved in csv..\n",
      "52000 items saved in csv..\n",
      "54000 items saved in csv..\n",
      "56000 items saved in csv..\n",
      "58000 items saved in csv..\n",
      "60000 items saved in csv..\n",
      "62000 items saved in csv..\n",
      "64000 items saved in csv..\n",
      "66000 items saved in csv..\n",
      "Log: working over page 2100...\n",
      "68000 items saved in csv..\n",
      "70000 items saved in csv..\n",
      "72000 items saved in csv..\n",
      "74000 items saved in csv..\n",
      "76000 items saved in csv..\n",
      "78000 items saved in csv..\n",
      "80000 items saved in csv..\n",
      "82000 items saved in csv..\n",
      "84000 items saved in csv..\n",
      "86000 items saved in csv..\n",
      "88000 items saved in csv..\n",
      "90000 items saved in csv..\n",
      "Log: working over page 2400...\n",
      "92000 items saved in csv..\n",
      "94000 items saved in csv..\n",
      "96000 items saved in csv..\n",
      "98000 items saved in csv..\n",
      "100000 items saved in csv..\n",
      "102000 items saved in csv..\n",
      "104000 items saved in csv..\n",
      "106000 items saved in csv..\n",
      "108000 items saved in csv..\n",
      "110000 items saved in csv..\n",
      "112000 items saved in csv..\n",
      "114000 items saved in csv..\n",
      "Log: working over page 2700...\n",
      "116000 items saved in csv..\n",
      "118000 items saved in csv..\n",
      "120000 items saved in csv..\n",
      "122000 items saved in csv..\n",
      "124000 items saved in csv..\n",
      "126000 items saved in csv..\n",
      "128000 items saved in csv..\n",
      "130000 items saved in csv..\n",
      "132000 items saved in csv..\n",
      "134000 items saved in csv..\n",
      "136000 items saved in csv..\n",
      "138000 items saved in csv..\n",
      "Log: working over page 3000...\n",
      "140000 items saved in csv..\n",
      "142000 items saved in csv..\n",
      "144000 items saved in csv..\n",
      "146000 items saved in csv..\n",
      "148000 items saved in csv..\n",
      "150000 items saved in csv..\n",
      "152000 items saved in csv..\n",
      "154000 items saved in csv..\n",
      "156000 items saved in csv..\n",
      "158000 items saved in csv..\n",
      "160000 items saved in csv..\n",
      "162000 items saved in csv..\n",
      "Log: working over page 3300...\n",
      "164000 items saved in csv..\n",
      "166000 items saved in csv..\n",
      "168000 items saved in csv..\n",
      "170000 items saved in csv..\n",
      "172000 items saved in csv..\n",
      "174000 items saved in csv..\n",
      "176000 items saved in csv..\n",
      "178000 items saved in csv..\n",
      "180000 items saved in csv..\n",
      "182000 items saved in csv..\n",
      "184000 items saved in csv..\n",
      "186000 items saved in csv..\n",
      "188000 items saved in csv..\n",
      "Log: working over page 3600...\n",
      "190000 items saved in csv..\n",
      "192000 items saved in csv..\n",
      "194000 items saved in csv..\n",
      "196000 items saved in csv..\n",
      "198000 items saved in csv..\n",
      "200000 items saved in csv..\n",
      "202000 items saved in csv..\n",
      "204000 items saved in csv..\n",
      "206000 items saved in csv..\n",
      "208000 items saved in csv..\n",
      "210000 items saved in csv..\n",
      "212000 items saved in csv..\n",
      "Log: working over page 3900...\n",
      "214000 items saved in csv..\n",
      "216000 items saved in csv..\n",
      "218000 items saved in csv..\n",
      "220000 items saved in csv..\n",
      "222000 items saved in csv..\n",
      "224000 items saved in csv..\n",
      "226000 items saved in csv..\n",
      "228000 items saved in csv..\n",
      "230000 items saved in csv..\n",
      "232000 items saved in csv..\n",
      "234000 items saved in csv..\n",
      "236000 items saved in csv..\n",
      "238000 items saved in csv..\n",
      "Log: working over page 4200...\n",
      "240000 items saved in csv..\n",
      "242000 items saved in csv..\n",
      "244000 items saved in csv..\n",
      "246000 items saved in csv..\n",
      "248000 items saved in csv..\n",
      "250000 items saved in csv..\n",
      "252000 items saved in csv..\n",
      "254000 items saved in csv..\n",
      "256000 items saved in csv..\n",
      "258000 items saved in csv..\n",
      "260000 items saved in csv..\n",
      "262000 items saved in csv..\n",
      "264000 items saved in csv..\n",
      "266000 items saved in csv..\n",
      "268000 items saved in csv..\n",
      "Log: working over page 4500...\n",
      "270000 items saved in csv..\n",
      "272000 items saved in csv..\n",
      "274000 items saved in csv..\n",
      "276000 items saved in csv..\n",
      "\n",
      "Total items saved: 276615\n"
     ]
    }
   ],
   "source": [
    "def to_csv():\n",
    "    counter = 0  \n",
    "\n",
    "    for item in get_horo():\n",
    "            data = []\n",
    "            for i in range(len(df_columns)):\n",
    "                data.append(item[i])\n",
    "            df.loc[len(df.index)] = data\n",
    "            #time.sleep(0.05)\n",
    "\n",
    "            counter += 1\n",
    "            if counter % save_frequency == 0:\n",
    "                df.to_csv('mygazeta.csv')\n",
    "                print(f'{counter} items saved in csv..')\n",
    "    df.to_csv('mygazeta.csv')\n",
    "\n",
    "    print(f'\\nTotal items saved: {counter}') \n",
    "\n",
    "to_csv()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cbf23fb7",
   "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>date</th>\n",
       "      <th>zodiac</th>\n",
       "      <th>horo</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-08-20</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Плодотворный день, подходящий для многих дел. ...</td>\n",
       "      <td>https://mygazeta.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-08-20</td>\n",
       "      <td>Овен</td>\n",
       "      <td>День завтра благоприятен для новых знакомств. ...</td>\n",
       "      <td>https://mygazeta.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-08-20</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Вы будете более открыто, чем обычно, вести себ...</td>\n",
       "      <td>https://mygazeta.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-08-20</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Хотя ваше финансовое положение не вызывает опа...</td>\n",
       "      <td>https://mygazeta.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-08-20</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Овнам необходимо экономить жизненную энергию, ...</td>\n",
       "      <td>https://mygazeta.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date zodiac                                               horo  \\\n",
       "0  2023-08-20   Овен  Плодотворный день, подходящий для многих дел. ...   \n",
       "1  2023-08-20   Овен  День завтра благоприятен для новых знакомств. ...   \n",
       "2  2023-08-20   Овен  Вы будете более открыто, чем обычно, вести себ...   \n",
       "3  2023-08-20   Овен  Хотя ваше финансовое положение не вызывает опа...   \n",
       "4  2023-08-20   Овен  Овнам необходимо экономить жизненную энергию, ...   \n",
       "\n",
       "                 source  \n",
       "0  https://mygazeta.com  \n",
       "1  https://mygazeta.com  \n",
       "2  https://mygazeta.com  \n",
       "3  https://mygazeta.com  \n",
       "4  https://mygazeta.com  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('mygazeta.csv', index_col=0)\n",
    "new = df.drop_duplicates(subset='horo', keep = 'first', inplace = False)\n",
    "new['source'] = 'https://mygazeta.com'\n",
    "new['date'] = date.today()\n",
    "new = new[['date','zodiac', 'horo', 'source']]\n",
    "new.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bc0b9852",
   "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>date</th>\n",
       "      <th>zodiac</th>\n",
       "      <th>horo</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Намерения ваши прекрасны, но время для их осущ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Телец</td>\n",
       "      <td>Ваше сострадание и понимание бесконечны. Вы по...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Близнецы</td>\n",
       "      <td>Небольшое неудобство, которое вам придется пре...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Рак</td>\n",
       "      <td>Привычные глазу лица и предметы вряд ли смогут...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Лев</td>\n",
       "      <td>Сегодня вы будете равным среди великих. У вас ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date    zodiac                                               horo  \\\n",
       "0  2023-08-06      Овен  Намерения ваши прекрасны, но время для их осущ...   \n",
       "1  2023-08-06     Телец  Ваше сострадание и понимание бесконечны. Вы по...   \n",
       "2  2023-08-06  Близнецы  Небольшое неудобство, которое вам придется пре...   \n",
       "3  2023-08-06       Рак  Привычные глазу лица и предметы вряд ли смогут...   \n",
       "4  2023-08-06       Лев  Сегодня вы будете равным среди великих. У вас ...   \n",
       "\n",
       "                           source  \n",
       "0  https://74.ru/horoscope/daily/  \n",
       "1  https://74.ru/horoscope/daily/  \n",
       "2  https://74.ru/horoscope/daily/  \n",
       "3  https://74.ru/horoscope/daily/  \n",
       "4  https://74.ru/horoscope/daily/  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv('C:/Users/Kate/Desktop/Docker/sqlite/horo1.csv')\n",
    "df1.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f3fa161a",
   "metadata": {},
   "outputs": [],
   "source": [
    "upd = pd.concat([df1, new])\n",
    "upd.to_csv('C:/Users/Kate/Desktop/Docker/sqlite/horo1.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "93f7999d",
   "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>date</th>\n",
       "      <th>zodiac</th>\n",
       "      <th>horo</th>\n",
       "      <th>source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Овен</td>\n",
       "      <td>Намерения ваши прекрасны, но время для их осущ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Телец</td>\n",
       "      <td>Ваше сострадание и понимание бесконечны. Вы по...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Близнецы</td>\n",
       "      <td>Небольшое неудобство, которое вам придется пре...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Рак</td>\n",
       "      <td>Привычные глазу лица и предметы вряд ли смогут...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-08-06</td>\n",
       "      <td>Лев</td>\n",
       "      <td>Сегодня вы будете равным среди великих. У вас ...</td>\n",
       "      <td>https://74.ru/horoscope/daily/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255044</th>\n",
       "      <td>2023-08-20</td>\n",
       "      <td>Рыбы</td>\n",
       "      <td>Ваше красноречие, образованность, широта взгля...</td>\n",
       "      <td>https://mygazeta.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255045</th>\n",
       "      <td>2023-08-20</td>\n",
       "      <td>Рыбы</td>\n",
       "      <td>Рыбам сегодня будет отлично удаваться имитация...</td>\n",
       "      <td>https://mygazeta.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255046</th>\n",
       "      <td>2023-08-20</td>\n",
       "      <td>Рыбы</td>\n",
       "      <td>Сейчас стоит заняться поисками нового занятия ...</td>\n",
       "      <td>https://mygazeta.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255047</th>\n",
       "      <td>2023-08-20</td>\n",
       "      <td>Водолей</td>\n",
       "      <td>Несмотря на то, что вы способы искусно и ловко...</td>\n",
       "      <td>https://mygazeta.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255048</th>\n",
       "      <td>2023-08-20</td>\n",
       "      <td>Водолей</td>\n",
       "      <td>Ситуация резко изменилась со вчерашнего дня, и...</td>\n",
       "      <td>https://mygazeta.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>255049 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              date    zodiac  \\\n",
       "0       2023-08-06      Овен   \n",
       "1       2023-08-06     Телец   \n",
       "2       2023-08-06  Близнецы   \n",
       "3       2023-08-06       Рак   \n",
       "4       2023-08-06       Лев   \n",
       "...            ...       ...   \n",
       "255044  2023-08-20      Рыбы   \n",
       "255045  2023-08-20      Рыбы   \n",
       "255046  2023-08-20      Рыбы   \n",
       "255047  2023-08-20   Водолей   \n",
       "255048  2023-08-20   Водолей   \n",
       "\n",
       "                                                     horo  \\\n",
       "0       Намерения ваши прекрасны, но время для их осущ...   \n",
       "1       Ваше сострадание и понимание бесконечны. Вы по...   \n",
       "2       Небольшое неудобство, которое вам придется пре...   \n",
       "3       Привычные глазу лица и предметы вряд ли смогут...   \n",
       "4       Сегодня вы будете равным среди великих. У вас ...   \n",
       "...                                                   ...   \n",
       "255044  Ваше красноречие, образованность, широта взгля...   \n",
       "255045  Рыбам сегодня будет отлично удаваться имитация...   \n",
       "255046  Сейчас стоит заняться поисками нового занятия ...   \n",
       "255047  Несмотря на то, что вы способы искусно и ловко...   \n",
       "255048  Ситуация резко изменилась со вчерашнего дня, и...   \n",
       "\n",
       "                                source  \n",
       "0       https://74.ru/horoscope/daily/  \n",
       "1       https://74.ru/horoscope/daily/  \n",
       "2       https://74.ru/horoscope/daily/  \n",
       "3       https://74.ru/horoscope/daily/  \n",
       "4       https://74.ru/horoscope/daily/  \n",
       "...                                ...  \n",
       "255044            https://mygazeta.com  \n",
       "255045            https://mygazeta.com  \n",
       "255046            https://mygazeta.com  \n",
       "255047            https://mygazeta.com  \n",
       "255048            https://mygazeta.com  \n",
       "\n",
       "[255049 rows x 4 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = pd.read_csv('C:/Users/Kate/Desktop/Docker/sqlite/horo1.csv')\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ebb19e10",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "https://mygazeta.com                                         147574\n",
       "https://github.com/dkagramanyan/astro                         73477\n",
       "https://sibmama.ru/                                           21013\n",
       "https://sakhalife.ru/category/goroskop                         7421\n",
       "http://vuslon.ru/news/rubric/list/goroskop-na-kazhdyy-den      3552\n",
       "https://www.newsler.ru/horoscope                               1248\n",
       "https://retrofm.ru/goroskop                                     208\n",
       "https://horoscopes.rambler.ru                                   208\n",
       "https://74.ru/horoscope/daily/                                  204\n",
       "https://astroscope.ru/horoskop/ejednevniy_goroskop/             144\n",
       "Name: source, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['source'].value_counts()"
   ]
  }
 ],
 "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}