{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Начало лабораторной работы №1\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Набор данных \"Наблюдения НЛО в США\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Загрузка и сохранение данных"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>summary</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>date_time</th>\n",
       "      <th>shape</th>\n",
       "      <th>duration</th>\n",
       "      <th>stats</th>\n",
       "      <th>report_link</th>\n",
       "      <th>text</th>\n",
       "      <th>posted</th>\n",
       "      <th>city_latitude</th>\n",
       "      <th>city_longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Viewed some red lights in the sky appearing to...</td>\n",
       "      <td>Visalia</td>\n",
       "      <td>CA</td>\n",
       "      <td>2021-12-15T21:45:00</td>\n",
       "      <td>light</td>\n",
       "      <td>2 minutes</td>\n",
       "      <td>Occurred : 12/15/2021 21:45  (Entered as : 12/...</td>\n",
       "      <td>http://www.nuforc.org/webreports/165/S165881.html</td>\n",
       "      <td>Viewed some red lights in the sky appearing to...</td>\n",
       "      <td>2021-12-19T00:00:00</td>\n",
       "      <td>36.356650</td>\n",
       "      <td>-119.347937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Look like 1 or 3 crafts from North traveling s...</td>\n",
       "      <td>Cincinnati</td>\n",
       "      <td>OH</td>\n",
       "      <td>2021-12-16T09:45:00</td>\n",
       "      <td>triangle</td>\n",
       "      <td>14 seconds</td>\n",
       "      <td>Occurred : 12/16/2021 09:45  (Entered as : 12/...</td>\n",
       "      <td>http://www.nuforc.org/webreports/165/S165888.html</td>\n",
       "      <td>Look like 1 or 3 crafts from North traveling s...</td>\n",
       "      <td>2021-12-19T00:00:00</td>\n",
       "      <td>39.174503</td>\n",
       "      <td>-84.481363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>seen dark rectangle moving slowly thru the sky...</td>\n",
       "      <td>Tecopa</td>\n",
       "      <td>CA</td>\n",
       "      <td>2021-12-10T00:00:00</td>\n",
       "      <td>rectangle</td>\n",
       "      <td>Several minutes</td>\n",
       "      <td>Occurred : 12/10/2021 00:00  (Entered as : 12/...</td>\n",
       "      <td>http://www.nuforc.org/webreports/165/S165810.html</td>\n",
       "      <td>seen dark rectangle moving slowly thru the sky...</td>\n",
       "      <td>2021-12-19T00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>One red light moving switly west to east, beco...</td>\n",
       "      <td>Knoxville</td>\n",
       "      <td>TN</td>\n",
       "      <td>2021-12-10T19:30:00</td>\n",
       "      <td>triangle</td>\n",
       "      <td>20-30 seconds</td>\n",
       "      <td>Occurred : 12/10/2021 19:30  (Entered as : 12/...</td>\n",
       "      <td>http://www.nuforc.org/webreports/165/S165825.html</td>\n",
       "      <td>One red light moving switly west to east, beco...</td>\n",
       "      <td>2021-12-19T00:00:00</td>\n",
       "      <td>35.961561</td>\n",
       "      <td>-83.980115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bright, circular Fresnel-lens shaped light sev...</td>\n",
       "      <td>Alexandria</td>\n",
       "      <td>VA</td>\n",
       "      <td>2021-12-07T08:00:00</td>\n",
       "      <td>circle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Occurred : 12/7/2021 08:00  (Entered as : 12/0...</td>\n",
       "      <td>http://www.nuforc.org/webreports/165/S165754.html</td>\n",
       "      <td>Bright, circular Fresnel-lens shaped light sev...</td>\n",
       "      <td>2021-12-19T00:00:00</td>\n",
       "      <td>38.798958</td>\n",
       "      <td>-77.095133</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             summary        city state  \\\n",
       "0  Viewed some red lights in the sky appearing to...     Visalia    CA   \n",
       "1  Look like 1 or 3 crafts from North traveling s...  Cincinnati    OH   \n",
       "2  seen dark rectangle moving slowly thru the sky...      Tecopa    CA   \n",
       "3  One red light moving switly west to east, beco...   Knoxville    TN   \n",
       "4  Bright, circular Fresnel-lens shaped light sev...  Alexandria    VA   \n",
       "\n",
       "             date_time      shape         duration  \\\n",
       "0  2021-12-15T21:45:00      light        2 minutes   \n",
       "1  2021-12-16T09:45:00   triangle       14 seconds   \n",
       "2  2021-12-10T00:00:00  rectangle  Several minutes   \n",
       "3  2021-12-10T19:30:00   triangle    20-30 seconds   \n",
       "4  2021-12-07T08:00:00     circle              NaN   \n",
       "\n",
       "                                               stats  \\\n",
       "0  Occurred : 12/15/2021 21:45  (Entered as : 12/...   \n",
       "1  Occurred : 12/16/2021 09:45  (Entered as : 12/...   \n",
       "2  Occurred : 12/10/2021 00:00  (Entered as : 12/...   \n",
       "3  Occurred : 12/10/2021 19:30  (Entered as : 12/...   \n",
       "4  Occurred : 12/7/2021 08:00  (Entered as : 12/0...   \n",
       "\n",
       "                                         report_link  \\\n",
       "0  http://www.nuforc.org/webreports/165/S165881.html   \n",
       "1  http://www.nuforc.org/webreports/165/S165888.html   \n",
       "2  http://www.nuforc.org/webreports/165/S165810.html   \n",
       "3  http://www.nuforc.org/webreports/165/S165825.html   \n",
       "4  http://www.nuforc.org/webreports/165/S165754.html   \n",
       "\n",
       "                                                text               posted  \\\n",
       "0  Viewed some red lights in the sky appearing to...  2021-12-19T00:00:00   \n",
       "1  Look like 1 or 3 crafts from North traveling s...  2021-12-19T00:00:00   \n",
       "2  seen dark rectangle moving slowly thru the sky...  2021-12-19T00:00:00   \n",
       "3  One red light moving switly west to east, beco...  2021-12-19T00:00:00   \n",
       "4  Bright, circular Fresnel-lens shaped light sev...  2021-12-19T00:00:00   \n",
       "\n",
       "   city_latitude  city_longitude  \n",
       "0      36.356650     -119.347937  \n",
       "1      39.174503      -84.481363  \n",
       "2            NaN             NaN  \n",
       "3      35.961561      -83.980115  \n",
       "4      38.798958      -77.095133  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Чтобы загрузить данные из CSV файла:\n",
    "\n",
    "df = pd.read_csv('datasets/nuforc_reports.csv')\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Получение сведений о датафрейме с данными"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 136940 entries, 0 to 136939\n",
      "Data columns (total 12 columns):\n",
      " #   Column          Non-Null Count   Dtype  \n",
      "---  ------          --------------   -----  \n",
      " 0   summary         136866 non-null  object \n",
      " 1   city            136558 non-null  object \n",
      " 2   state           127595 non-null  object \n",
      " 3   date_time       134272 non-null  object \n",
      " 4   shape           131018 non-null  object \n",
      " 5   duration        130448 non-null  object \n",
      " 6   stats           136940 non-null  object \n",
      " 7   report_link     136940 non-null  object \n",
      " 8   text            136902 non-null  object \n",
      " 9   posted          134272 non-null  object \n",
      " 10  city_latitude   110136 non-null  float64\n",
      " 11  city_longitude  110136 non-null  float64\n",
      "dtypes: float64(2), object(10)\n",
      "memory usage: 12.5+ MB\n"
     ]
    },
    {
     "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>city_latitude</th>\n",
       "      <th>city_longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>110136.000000</td>\n",
       "      <td>110136.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>38.704608</td>\n",
       "      <td>-95.185792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5.752186</td>\n",
       "      <td>18.310088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-32.055500</td>\n",
       "      <td>-170.494000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>34.238375</td>\n",
       "      <td>-113.901810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>39.257500</td>\n",
       "      <td>-89.161450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>42.317739</td>\n",
       "      <td>-80.363444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>64.845276</td>\n",
       "      <td>130.850580</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       city_latitude  city_longitude\n",
       "count  110136.000000   110136.000000\n",
       "mean       38.704608      -95.185792\n",
       "std         5.752186       18.310088\n",
       "min       -32.055500     -170.494000\n",
       "25%        34.238375     -113.901810\n",
       "50%        39.257500      -89.161450\n",
       "75%        42.317739      -80.363444\n",
       "max        64.845276      130.850580"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Получить общую информацию о датафрейме можно с помощью:\n",
    "df.info()\n",
    "#Для получения статистического описания числовых колонок:\n",
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Получение сведений о колонках датафрейма"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['summary', 'city', 'state', 'date_time', 'shape', 'duration', 'stats',\n",
      "       'report_link', 'text', 'posted', 'city_latitude', 'city_longitude'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "#Вывести названия колонок:\n",
    "print(df.columns)\n",
    "\n",
    "#Получить уникальные значения в колонке:\n",
    "unique_values = df['city'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Вывод отельных строки и столбцов из датафрейма"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#Для вывода отдельных строк можно использовать iloc или loc:\n",
    "\n",
    "# Вывод первой строки\n",
    "first_row = df.iloc[0]\n",
    "\n",
    "# Вывод строк с 0 по 4\n",
    "first_five_rows = df.iloc[0:5]\n",
    "\n",
    "# Вывод по метке индекса\n",
    "row_by_label = df.loc[0]\n",
    "\n",
    "# Вывод определенного столбца\n",
    "specific_column = df['city']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Группировка и агрегация данных в датафрейме"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Для группировки данных можно использовать groupby:\n",
    "grouped = df.groupby('city').agg({'state': 'sum'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### сортировка данных в датафрейме"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Для сортировки данных по определенной колонке:\n",
    "sorted_df = df.sort_values(by='city', ascending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### удаление строк/столбцов"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Для удаления строк:\n",
    "\n",
    "# Удаление строки по индексу\n",
    "df = df.drop(24)\n",
    "\n",
    "# Удаление нескольких строк\n",
    "df = df.drop([1, 2, 3])\n",
    "\n",
    "\n",
    "#Для удаления столбцов:\n",
    "\n",
    "# Удаление столбца\n",
    "df = df.drop(\"summary\", axis=1)\n",
    "\n",
    "# Удаление нескольких столбцов\n",
    "df = df.drop(['shape', 'duration'], axis=1)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### создание новых столбцов на основе данных из существующих столбцов датафрейма"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Создание нового столбца на основе существующих:\n",
    "df['new_columnStateCity'] = df['state'] + df['city']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### удаление строк с пустыми значениями"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Для удаления строк с хотя бы одним пустым значением:\n",
    "df = df.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### заполнение пустых значений на основе существующих данных"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Заполнение средним значением\n",
    "df['city_latitude'] = df['city_latitude'].fillna(df['city_latitude'].mean())\n",
    "\n",
    "# Заполнение фиксированным значением\n",
    "df['state'] = df['state'].fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Линейная диаграмма (plot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 39128 characters]",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = pd.read_csv('datasets/nuforc_reports.csv')\n",
    "\n",
    "# Пример: Линейная диаграмма количества отчетов по годам\n",
    "df['year'] = pd.to_datetime(df['date_time']).dt.year\n",
    "yearly_counts = df['year'].value_counts().sort_index()\n",
    "\n",
    "plt.plot(yearly_counts.index, yearly_counts.values)\n",
    "plt.title('Количество UFO отчетов по годам')\n",
    "plt.xlabel('Год')\n",
    "plt.ylabel('Количество отчетов')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 34312 characters]",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Пример: Столбчатая диаграмма количества отчетов по штатам\n",
    "state_counts = df['state'].value_counts().head(15)\n",
    "\n",
    "state_counts.plot(kind='bar')\n",
    "plt.title('Топ-10 штатов по количеству UFO отчетов')\n",
    "plt.xlabel('Штат')\n",
    "plt.ylabel('Количество отчетов')\n",
    "plt.xticks(rotation=45)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 21136 characters]",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "\n",
    "# Гистограмма распределения географической широты\n",
    "plt.hist(df['city_latitude'].dropna(), bins=30, color='blue', alpha=0.7)\n",
    "plt.title('Гистограмма широты города')\n",
    "plt.xlabel('Широта')\n",
    "plt.ylabel('Частота')\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 36116 characters]",
      "text/plain": [
       "<Figure size 640x480 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Пример: Диаграмма рассеяния по координатам географической широты\n",
    "plt.scatter(df['city_longitude'].dropna(), df['city_latitude'].dropna(), alpha=0.5)\n",
    "plt.title('Диаграмма рассеяния UFO наблюдений по координатам')\n",
    "plt.xlabel('Долгота')\n",
    "plt.ylabel('Широта')\n",
    "plt.show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
