Read meteo data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Meteo data reading"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "896d2bed689126d8"
  },
  {
   "cell_type": "markdown",
   "source": [
    "Data source: https://datacatalog.regione.emilia-romagna.it/catalogCTA/dataset/meteo-dati-osservati2"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "7e1e69ed50db60a6"
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "outputs": [],
   "source": [
    "import pandas as pd, ast, os, plotly.express as px, numpy as np, geopandas as gpd, re\n",
    "from scipy import stats"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-17T08:02:54.636251100Z",
     "start_time": "2023-10-17T08:02:51.912898300Z"
    }
   },
   "id": "26365fe47b0e30e8"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Reading a single year"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "10cecca4219c3b20"
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2de47b55b2369525",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-10-05T14:16:09.190176700Z",
     "start_time": "2023-10-05T14:15:08.694572500Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: total: 23.4 s\n",
      "Wall time: 1min\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# leggo il file csv\n",
    "read_2021 = pd.read_csv('Storico meteo/unito_2021.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d72a7702d1720e4e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-10-05T14:16:09.209310600Z",
     "start_time": "2023-10-05T14:16:09.194838400Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "          Unnamed: 0  version network  ident      lon      lat  \\\n0                  0      0.1  agrmet    NaN  1095670  4470214   \n1                  1      0.1  agrmet    NaN  1095670  4470214   \n2                  2      0.1  agrmet    NaN  1095670  4470214   \n3                  3      0.1  agrmet    NaN  1095670  4470214   \n4                  4      0.1  agrmet    NaN  1095670  4470214   \n...              ...      ...     ...    ...      ...      ...   \n16922941    16922941      0.1  spdsra    NaN  1219660  4395814   \n16922942    16922942      0.1  spdsra    NaN  1219660  4395814   \n16922943    16922943      0.1  spdsra    NaN  1219660  4395814   \n16922944    16922944      0.1  spdsra    NaN  1219660  4395814   \n16922945    16922945      0.1  spdsra    NaN  1219660  4395814   \n\n                               date  \\\n0         2021-02-01 00:00:00+00:00   \n1         2021-02-01 00:15:00+00:00   \n2         2021-02-01 00:30:00+00:00   \n3         2021-02-01 00:45:00+00:00   \n4         2021-02-01 01:00:00+00:00   \n...                             ...   \n16922941  2021-01-31 18:00:00+00:00   \n16922942  2021-01-31 19:00:00+00:00   \n16922943  2021-01-31 20:00:00+00:00   \n16922944  2021-01-31 21:00:00+00:00   \n16922945  2021-01-31 23:00:00+00:00   \n\n                                                       data  \n0         [{'vars': {'B01019': {'v': 'Albareto'}, 'B0119...  \n1         [{'vars': {'B01019': {'v': 'Albareto'}, 'B0119...  \n2         [{'vars': {'B01019': {'v': 'Albareto'}, 'B0119...  \n3         [{'vars': {'B01019': {'v': 'Albareto'}, 'B0119...  \n4         [{'vars': {'B01019': {'v': 'Albareto'}, 'B0119...  \n...                                                     ...  \n16922941  [{'vars': {'B01019': {'v': 'Mercato Saraceno'}...  \n16922942  [{'vars': {'B01019': {'v': 'Mercato Saraceno'}...  \n16922943  [{'vars': {'B01019': {'v': 'Mercato Saraceno'}...  \n16922944  [{'vars': {'B01019': {'v': 'Mercato Saraceno'}...  \n16922945  [{'vars': {'B01019': {'v': 'Mercato Saraceno'}...  \n\n[16922946 rows x 8 columns]",
      "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>Unnamed: 0</th>\n      <th>version</th>\n      <th>network</th>\n      <th>ident</th>\n      <th>lon</th>\n      <th>lat</th>\n      <th>date</th>\n      <th>data</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>0.1</td>\n      <td>agrmet</td>\n      <td>NaN</td>\n      <td>1095670</td>\n      <td>4470214</td>\n      <td>2021-02-01 00:00:00+00:00</td>\n      <td>[{'vars': {'B01019': {'v': 'Albareto'}, 'B0119...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>0.1</td>\n      <td>agrmet</td>\n      <td>NaN</td>\n      <td>1095670</td>\n      <td>4470214</td>\n      <td>2021-02-01 00:15:00+00:00</td>\n      <td>[{'vars': {'B01019': {'v': 'Albareto'}, 'B0119...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>0.1</td>\n      <td>agrmet</td>\n      <td>NaN</td>\n      <td>1095670</td>\n      <td>4470214</td>\n      <td>2021-02-01 00:30:00+00:00</td>\n      <td>[{'vars': {'B01019': {'v': 'Albareto'}, 'B0119...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>0.1</td>\n      <td>agrmet</td>\n      <td>NaN</td>\n      <td>1095670</td>\n      <td>4470214</td>\n      <td>2021-02-01 00:45:00+00:00</td>\n      <td>[{'vars': {'B01019': {'v': 'Albareto'}, 'B0119...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>0.1</td>\n      <td>agrmet</td>\n      <td>NaN</td>\n      <td>1095670</td>\n      <td>4470214</td>\n      <td>2021-02-01 01:00:00+00:00</td>\n      <td>[{'vars': {'B01019': {'v': 'Albareto'}, 'B0119...</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>16922941</th>\n      <td>16922941</td>\n      <td>0.1</td>\n      <td>spdsra</td>\n      <td>NaN</td>\n      <td>1219660</td>\n      <td>4395814</td>\n      <td>2021-01-31 18:00:00+00:00</td>\n      <td>[{'vars': {'B01019': {'v': 'Mercato Saraceno'}...</td>\n    </tr>\n    <tr>\n      <th>16922942</th>\n      <td>16922942</td>\n      <td>0.1</td>\n      <td>spdsra</td>\n      <td>NaN</td>\n      <td>1219660</td>\n      <td>4395814</td>\n      <td>2021-01-31 19:00:00+00:00</td>\n      <td>[{'vars': {'B01019': {'v': 'Mercato Saraceno'}...</td>\n    </tr>\n    <tr>\n      <th>16922943</th>\n      <td>16922943</td>\n      <td>0.1</td>\n      <td>spdsra</td>\n      <td>NaN</td>\n      <td>1219660</td>\n      <td>4395814</td>\n      <td>2021-01-31 20:00:00+00:00</td>\n      <td>[{'vars': {'B01019': {'v': 'Mercato Saraceno'}...</td>\n    </tr>\n    <tr>\n      <th>16922944</th>\n      <td>16922944</td>\n      <td>0.1</td>\n      <td>spdsra</td>\n      <td>NaN</td>\n      <td>1219660</td>\n      <td>4395814</td>\n      <td>2021-01-31 21:00:00+00:00</td>\n      <td>[{'vars': {'B01019': {'v': 'Mercato Saraceno'}...</td>\n    </tr>\n    <tr>\n      <th>16922945</th>\n      <td>16922945</td>\n      <td>0.1</td>\n      <td>spdsra</td>\n      <td>NaN</td>\n      <td>1219660</td>\n      <td>4395814</td>\n      <td>2021-01-31 23:00:00+00:00</td>\n      <td>[{'vars': {'B01019': {'v': 'Mercato Saraceno'}...</td>\n    </tr>\n  </tbody>\n</table>\n<p>16922946 rows × 8 columns</p>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "read_2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: total: 26 s\n",
      "Wall time: 1min 10s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# Convert the 'data' column to a string\n",
    "read_2021['data'] = read_2021['data'].astype(str)\n",
    "\n",
    "# Combine all 'data' strings into one large string\n",
    "combined_data = ' '.join(read_2021['data'])\n",
    "\n",
    "# Use regular expressions to find all codes in the format 'B04006'\n",
    "found_codes = re.findall(r\"'B\\d{5}':\", combined_data)\n",
    "\n",
    "# Remove duplicates from the list of found codes\n",
    "unique_codes = list(set(found_codes))"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-05T14:17:20.139374300Z",
     "start_time": "2023-10-05T14:16:09.764445300Z"
    }
   },
   "id": "54473732da02d603"
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [],
   "source": [
    "# Remove unwanted characters from each code\n",
    "cleaned_codes = [code.replace(\"'\", \"\").replace(\",\", \"\").replace(\":\", \"\") for code in unique_codes]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-05T14:17:20.174340200Z",
     "start_time": "2023-10-05T14:17:20.164256800Z"
    }
   },
   "id": "8e1f979b77ca53b9"
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "31"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(cleaned_codes)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-05T14:17:20.221058500Z",
     "start_time": "2023-10-05T14:17:20.201371200Z"
    }
   },
   "id": "1b4dfb748e4af89a"
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "File 'Storico meteo/Processed_data/meteo_codes.csv' already exists.\n"
     ]
    }
   ],
   "source": [
    "# Check if the file \"Storico meteo/Processed_data/meteo_codes.csv\" exists\n",
    "if os.path.exists(\"Storico meteo/Processed_data/meteo_codes.csv\"):\n",
    "    print(\"File 'Storico meteo/Processed_data/meteo_codes.csv' already exists.\")\n",
    "else:\n",
    "    # Specify the URL from which you want to download the table\n",
    "    url = 'https://arpa-simc.github.io/dballe/general_ref/btable.html'  # Replace with your actual URL\n",
    "\n",
    "    # Use pd.read_html() to download the table from the web page\n",
    "    # This method will return a list of DataFrames, one for each table found on the page\n",
    "    tables = pd.read_html(url)\n",
    "\n",
    "    # Select the table you're interested in from the list (there may be more than one)\n",
    "    # Let's assume you want the first table, so select tables[0]\n",
    "    your_table = tables[0]\n",
    "    \n",
    "    your_table.to_csv(\"Storico meteo/Processed_data/meteo_codes.csv\", index=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-05T14:17:20.221058500Z",
     "start_time": "2023-10-05T14:17:20.206661100Z"
    }
   },
   "id": "a26b1f199ea8877f"
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [],
   "source": [
    "# Specify the file path\n",
    "file_path = \"Storico meteo/Processed_data/meteo_codes.csv\"\n",
    "\n",
    "# Read the CSV file into a DataFrame\n",
    "your_table = pd.read_csv(file_path)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-05T14:17:20.311412400Z",
     "start_time": "2023-10-05T14:17:20.249117Z"
    }
   },
   "id": "4b55f33db98800c"
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [],
   "source": [
    "# Filter rows based on the cleaned codes\n",
    "your_table_filtered = your_table[your_table['Code'].isin(cleaned_codes)].reset_index(drop=True)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-05T14:17:20.334479500Z",
     "start_time": "2023-10-05T14:17:20.308311200Z"
    }
   },
   "id": "db961e8a5f43f66b"
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [
    {
     "data": {
      "text/plain": "      Code                                        Description         Unit  \\\n0   B01001                                   WMO BLOCK NUMBER      Numeric   \n1   B01002                                 WMO STATION NUMBER      Numeric   \n2   B01019                          LONG STATION OR SITE NAME     CCITTIA5   \n3   B01194                                    Report mnemonic     CCITTIA5   \n4   B04001                                               YEAR         YEAR   \n5   B04002                                              MONTH        MONTH   \n6   B04003                                                DAY          DAY   \n7   B04004                                               HOUR         HOUR   \n8   B04005                                             MINUTE       MINUTE   \n9   B04006                                             SECOND       SECOND   \n10  B05001                           LATITUDE (HIGH ACCURACY)       DEGREE   \n11  B06001                          LONGITUDE (HIGH ACCURACY)       DEGREE   \n12  B07030  HEIGHT OF STATION GROUND ABOVE MEAN SEA LEVEL ...            M   \n13  B07031  HEIGHT OF BAROMETER ABOVE MEAN SEA LEVEL (SEE ...            M   \n14  B10004                                           PRESSURE           PA   \n15  B11001                                     WIND DIRECTION  DEGREE TRUE   \n16  B11002                                         WIND SPEED          M/S   \n17  B11041                            MAXIMUM WIND GUST SPEED          M/S   \n18  B11043                        MAXIMUM WIND GUST DIRECTION  DEGREE TRUE   \n19  B12101                   TEMPERATURE/DRY-BULB TEMPERATURE            K   \n20  B13003                                  RELATIVE HUMIDITY            %   \n21  B13011       TOTAL PRECIPITATION / TOTAL WATER EQUIVALENT      KG/M**2   \n22  B13013                                   TOTAL SNOW DEPTH            M   \n23  B13215                                        River level            M   \n24  B14198                   Global radiation flux (downward)       W/M**2   \n25  B22001                                 DIRECTION OF WAVES  DEGREE TRUE   \n26  B22043                              SEA/WATER TEMPERATURE            K   \n27  B22070                            SIGNIFICANT WAVE HEIGHT            M   \n28  B22071                          SPECTRAL PEAK WAVE PERIOD            S   \n29  B22074                                AVERAGE WAVE PERIOD            S   \n30  B33007                                PER CENT CONFIDENCE            %   \n\n       Format  \n0    3 digits  \n1    4 digits  \n2   32 digits  \n3   16 digits  \n4    4 digits  \n5    2 digits  \n6    2 digits  \n7    2 digits  \n8    2 digits  \n9    2 digits  \n10  ###.#####  \n11  ###.#####  \n12    #####.#  \n13    #####.#  \n14     #####0  \n15   3 digits  \n16      ###.#  \n17      ###.#  \n18   3 digits  \n19     ###.##  \n20   3 digits  \n21     ####.#  \n22   ####.###  \n23    ####.##  \n24   4 digits  \n25   3 digits  \n26     ###.##  \n27      ##.##  \n28       ##.#  \n29       ##.#  \n30   3 digits  ",
      "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>Code</th>\n      <th>Description</th>\n      <th>Unit</th>\n      <th>Format</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>B01001</td>\n      <td>WMO BLOCK NUMBER</td>\n      <td>Numeric</td>\n      <td>3 digits</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>B01002</td>\n      <td>WMO STATION NUMBER</td>\n      <td>Numeric</td>\n      <td>4 digits</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>B01019</td>\n      <td>LONG STATION OR SITE NAME</td>\n      <td>CCITTIA5</td>\n      <td>32 digits</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>B01194</td>\n      <td>Report mnemonic</td>\n      <td>CCITTIA5</td>\n      <td>16 digits</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>B04001</td>\n      <td>YEAR</td>\n      <td>YEAR</td>\n      <td>4 digits</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>B04002</td>\n      <td>MONTH</td>\n      <td>MONTH</td>\n      <td>2 digits</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>B04003</td>\n      <td>DAY</td>\n      <td>DAY</td>\n      <td>2 digits</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>B04004</td>\n      <td>HOUR</td>\n      <td>HOUR</td>\n      <td>2 digits</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>B04005</td>\n      <td>MINUTE</td>\n      <td>MINUTE</td>\n      <td>2 digits</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>B04006</td>\n      <td>SECOND</td>\n      <td>SECOND</td>\n      <td>2 digits</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>B05001</td>\n      <td>LATITUDE (HIGH ACCURACY)</td>\n      <td>DEGREE</td>\n      <td>###.#####</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>B06001</td>\n      <td>LONGITUDE (HIGH ACCURACY)</td>\n      <td>DEGREE</td>\n      <td>###.#####</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>B07030</td>\n      <td>HEIGHT OF STATION GROUND ABOVE MEAN SEA LEVEL ...</td>\n      <td>M</td>\n      <td>#####.#</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>B07031</td>\n      <td>HEIGHT OF BAROMETER ABOVE MEAN SEA LEVEL (SEE ...</td>\n      <td>M</td>\n      <td>#####.#</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>B10004</td>\n      <td>PRESSURE</td>\n      <td>PA</td>\n      <td>#####0</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>B11001</td>\n      <td>WIND DIRECTION</td>\n      <td>DEGREE TRUE</td>\n      <td>3 digits</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>B11002</td>\n      <td>WIND SPEED</td>\n      <td>M/S</td>\n      <td>###.#</td>\n    </tr>\n    <tr>\n      <th>17</th>\n      <td>B11041</td>\n      <td>MAXIMUM WIND GUST SPEED</td>\n      <td>M/S</td>\n      <td>###.#</td>\n    </tr>\n    <tr>\n      <th>18</th>\n      <td>B11043</td>\n      <td>MAXIMUM WIND GUST DIRECTION</td>\n      <td>DEGREE TRUE</td>\n      <td>3 digits</td>\n    </tr>\n    <tr>\n      <th>19</th>\n      <td>B12101</td>\n      <td>TEMPERATURE/DRY-BULB TEMPERATURE</td>\n      <td>K</td>\n      <td>###.##</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>B13003</td>\n      <td>RELATIVE HUMIDITY</td>\n      <td>%</td>\n      <td>3 digits</td>\n    </tr>\n    <tr>\n      <th>21</th>\n      <td>B13011</td>\n      <td>TOTAL PRECIPITATION / TOTAL WATER EQUIVALENT</td>\n      <td>KG/M**2</td>\n      <td>####.#</td>\n    </tr>\n    <tr>\n      <th>22</th>\n      <td>B13013</td>\n      <td>TOTAL SNOW DEPTH</td>\n      <td>M</td>\n      <td>####.###</td>\n    </tr>\n    <tr>\n      <th>23</th>\n      <td>B13215</td>\n      <td>River level</td>\n      <td>M</td>\n      <td>####.##</td>\n    </tr>\n    <tr>\n      <th>24</th>\n      <td>B14198</td>\n      <td>Global radiation flux (downward)</td>\n      <td>W/M**2</td>\n      <td>4 digits</td>\n    </tr>\n    <tr>\n      <th>25</th>\n      <td>B22001</td>\n      <td>DIRECTION OF WAVES</td>\n      <td>DEGREE TRUE</td>\n      <td>3 digits</td>\n    </tr>\n    <tr>\n      <th>26</th>\n      <td>B22043</td>\n      <td>SEA/WATER TEMPERATURE</td>\n      <td>K</td>\n      <td>###.##</td>\n    </tr>\n    <tr>\n      <th>27</th>\n      <td>B22070</td>\n      <td>SIGNIFICANT WAVE HEIGHT</td>\n      <td>M</td>\n      <td>##.##</td>\n    </tr>\n    <tr>\n      <th>28</th>\n      <td>B22071</td>\n      <td>SPECTRAL PEAK WAVE PERIOD</td>\n      <td>S</td>\n      <td>##.#</td>\n    </tr>\n    <tr>\n      <th>29</th>\n      <td>B22074</td>\n      <td>AVERAGE WAVE PERIOD</td>\n      <td>S</td>\n      <td>##.#</td>\n    </tr>\n    <tr>\n      <th>30</th>\n      <td>B33007</td>\n      <td>PER CENT CONFIDENCE</td>\n      <td>%</td>\n      <td>3 digits</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "your_table_filtered"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-05T14:17:20.375058300Z",
     "start_time": "2023-10-05T14:17:20.334479500Z"
    }
   },
   "id": "e6a4eb466599a17f"
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [],
   "source": [
    "# Specify the file path\n",
    "file_path = \"Storico meteo/Processed_data/meteo_codes_filtered.csv\"\n",
    "\n",
    "your_table_filtered.to_csv(file_path, index=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-05T14:17:22.944053200Z",
     "start_time": "2023-10-05T14:17:20.374250900Z"
    }
   },
   "id": "9b32f6f43cb9c777"
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [],
   "source": [
    "def extract_data_and_create_dataframe(df, variables_of_interest):\n",
    "    # Filter the DataFrame based on the first variable of interest\n",
    "    mask = df['data'].str.contains('|'.join(variables_of_interest))\n",
    "\n",
    "    # Filtrare il DataFrame utilizzando la maschera booleana\n",
    "    df_filtered = df[mask].reset_index()\n",
    "\n",
    "    # Inizializza una lista vuota per memorizzare i timerange_key unici\n",
    "    timerange_keys_list = []\n",
    "\n",
    "    # Itera attraverso il DataFrame df\n",
    "    for index, row in df_filtered.iterrows():\n",
    "        # Estrai il valore dalla colonna 'data'\n",
    "        data_str = row['data']\n",
    "\n",
    "        # Analizza la stringa JSON\n",
    "        json_data = ast.literal_eval(data_str)\n",
    "\n",
    "        # Inizializza timerange a None\n",
    "        timerange = None\n",
    "\n",
    "        # Itera attraverso gli oggetti JSON\n",
    "        for obj in json_data[1:]:\n",
    "            obj_str = str(obj)\n",
    "            if 'timerange' in obj_str and 'B13011' in obj_str:\n",
    "                # Crea il timerange_key\n",
    "                timerange = obj['timerange']\n",
    "                timerange_key = f'B13011_{timerange[0]}_{timerange[-1]}'\n",
    "\n",
    "                # Aggiungi il timerange_key alla lista se non è già presente\n",
    "                if timerange_key not in timerange_keys_list:\n",
    "                    timerange_keys_list.append(timerange_key)\n",
    "\n",
    "    name_list = []\n",
    "\n",
    "    for i in range(len(df_filtered)):\n",
    "        date = df_filtered['date'][i]\n",
    "        get_name = df_filtered['data'][i]\n",
    "        json_data = ast.literal_eval(get_name)\n",
    "\n",
    "        values_dict = {}  # Inizializza un dizionario per memorizzare i valori per B13011\n",
    "        values_dict_timerange = {}\n",
    "\n",
    "        for i in range(0, len(json_data)):\n",
    "            for j, variable in enumerate(variables_of_interest):\n",
    "                try:\n",
    "                    if variable == 'B13011':\n",
    "                        timerange = json_data[i].get('timerange')\n",
    "                        if timerange is not None:\n",
    "                            timerange_key = f'{variable}_{timerange[0]}_{timerange[-1]}'  # Crea una colonna separata per l'ultimo timerange\n",
    "                            value = json_data[i]['vars'][variable]['v']\n",
    "                            values_dict_timerange[timerange_key] = value\n",
    "\n",
    "                    else:\n",
    "                        timerange = json_data[i].get('timerange')\n",
    "                        if timerange is not None:\n",
    "                            if str(timerange[0]) == '254':\n",
    "                                value = json_data[i]['vars'][variable]['v']\n",
    "                                if variable in variables_of_interest:\n",
    "                                    values_dict[variable] = value\n",
    "                        else:\n",
    "                            continue\n",
    "\n",
    "                except KeyError:\n",
    "                    continue\n",
    "\n",
    "        # Aggiungi i valori del dizionario alle colonne della lista dei valori\n",
    "        values = [values_dict.get(variable, np.nan) for variable in variables_of_interest if variable != 'B13011']\n",
    "        values_keys = [values_dict_timerange.get(variable, np.nan) for variable in timerange_keys_list]\n",
    "\n",
    "        name_list.append([json_data[0]['vars']['B01019']['v'], json_data[0]['vars']['B05001']['v'], json_data[0]['vars']['B06001']['v'],\n",
    "                          json_data[0]['vars']['B07030']['v'], json_data[0]['vars'].get('B07031', {}).get('v', np.nan)] + values + values_keys + [date])\n",
    "\n",
    "\n",
    "    total_columns = [variable for variable in variables_of_interest if variable != 'B13011'] + timerange_keys_list\n",
    "\n",
    "    df_station = pd.DataFrame(name_list)\n",
    "    df_station.columns = ['name', 'lon', 'lat', 'h_sea', 'h_bar'] + total_columns + ['date']\n",
    "\n",
    "    # Elimina le righe in cui 'Colonna1' e 'Colonna2' contengono tutti NaN\n",
    "    df_station = df_station.dropna(subset=total_columns, how='all').reset_index(drop=True)\n",
    "\n",
    "    return df_station"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-11T12:08:36.449023400Z",
     "start_time": "2023-10-11T12:08:36.421867300Z"
    }
   },
   "id": "4c41cfe75deaf940"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16a0ba6bde373582",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-10-05T14:17:25.665228900Z",
     "start_time": "2023-10-05T14:17:25.665228900Z"
    }
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "\n",
    "# Utilizzo della funzione con un DataFrame generico\n",
    "variables_of_interest = ['B11001','B11002', 'B11041', 'B11043', 'B12101', 'B13003', 'B13011']\n",
    "variable_to_mode = ['B11001', 'B11041', 'B11043', 'B12101', 'B13003', 'B13011']\n",
    "df_station_2021 = extract_data_and_create_dataframe(read_2021, variables_of_interest, variable_to_mode)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df_station_2021['name'].unique()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2023-10-05T14:17:25.674281Z",
     "start_time": "2023-10-05T14:17:25.674281Z"
    }
   },
   "id": "b5cfd5fffa5772d6"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "single_station = df_station_2021[df_station_2021['name'] == 'Cesenatico porto']\n",
    "single_station = single_station.sort_values(by=['date'])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-10-05T14:17:25.676444600Z"
    }
   },
   "id": "960bfab885437069"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "single_station"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-10-05T14:17:25.676444600Z"
    }
   },
   "id": "9a55e1559ab13b86"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "df_station_2021.to_csv(\"Storico meteo/Processed_data/all_variables_2021_modify.csv\", index=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2023-10-05T14:17:25.676444600Z"
    }
   },
   "id": "19489ce13097242f"
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Reading all the years available"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "e06ecb531d03757f"
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fdec0cb765e35508",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-10-11T13:11:51.655759300Z",
     "start_time": "2023-10-11T12:09:01.116713500Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing file: unito_2012.csv\n",
      "Processing file: unito_2013.csv\n",
      "Processing file: unito_2014.csv\n",
      "Processing file: unito_2015.csv\n",
      "Processing file: unito_2016.csv\n",
      "Processing file: unito_2017.csv\n",
      "Processing file: unito_2018.csv\n",
      "Processing file: unito_2019.csv\n",
      "Processing file: unito_2020.csv\n",
      "Processing file: unito_2021.csv\n",
      "CPU times: total: 42min 47s\n",
      "Wall time: 1h 2min 50s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# Lista per memorizzare i DataFrame da ciascun file CSV\n",
    "dfs = []\n",
    "dir_path = 'Storico meteo'\n",
    "variables_of_interest = ['B11001','B11002']\n",
    "variable_to_mode = ['B11001']\n",
    "\n",
    "# Elencare tutti i file CSV nella cartella\n",
    "for filename in os.listdir('Storico meteo'):\n",
    "    if filename.endswith(\".csv\"):\n",
    "        # Print the filename being processed\n",
    "        print(\"Processing file:\", filename)\n",
    "        file_path = os.path.join(dir_path, filename)\n",
    "        df = pd.read_csv(file_path)\n",
    "        df_station = extract_data_and_create_dataframe(df, variables_of_interest, variable_to_mode)\n",
    "        dfs.append(df_station)\n",
    "\n",
    "# Concatenare tutti i DataFrame in uno solo\n",
    "final_df = pd.concat(dfs, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "83847fbf",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-10-11T13:12:54.802385300Z",
     "start_time": "2023-10-11T13:11:51.689714900Z"
    }
   },
   "outputs": [],
   "source": [
    "final_df.to_csv(\"Storico meteo/Processed_data/wind_variables_old_code.csv\", index=False)"
   ]
  }
 ],
 "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
}