Create_Dataset_MarlonFranco.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Create_Dataset_MarlonFranco.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "wE2_YNZwKd6-",
        "sY6SKOQNKd7E",
        "wHFfruEaKd7u",
        "oVHZ51CFKd75",
        "KN6ZEQUHKd8L",
        "NZnNyt1KKd8l",
        "UctrZfABKd86",
        "490UXoXEKd9B"
      ],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "accelerator": "TPU"
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/marlonrcfranco/soyforecast/blob/master/Create_Dataset_MarlonFranco.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LzMOF9JbKd55",
        "colab_type": "text"
      },
      "source": [
        "# Dataset Marlon\n",
        "## Soybean, CBOT Soybean Futures + ( Global Historical Climatology Network (GHCN) filtered by USDA-NASS-soybeans-production_bushels-2015)\n",
        "\n",
        "### Soybean, CBOT Soybean Futures\n",
        "- https://blog.quandl.com/api-for-commodity-data\n",
        "- http://www.quandl.com/api/v3/datasets/CHRIS/CME_S1/\n",
        "\n",
        "### Global Historical Climatology Network (GHCN)\n",
        "- https://www.ncdc.noaa.gov/data-access/land-based-station-data/land-based-datasets/global-historical-climatology-network-ghcn\n",
        "- FTP: ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/by_year/\n",
        "\n",
        "### USDA-NASS-soybeans-production_bushels-2015\n",
        "- https://usda-reports.nautilytics.com/?crop=soybeans&statistic=production_dollars&year=2007\n",
        "- https://www.nass.usda.gov/Data_Visualization/index.php\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "\n",
        "https://github.com/aaronpenne/get_noaa_ghcn_data.git\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qhwDKtUnKd58",
        "colab_type": "text"
      },
      "source": [
        "## Imports"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Y9fJI-b2Kd5-",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 54
        },
        "outputId": "ea769162-fdd6-42a6-a022-bfed94e66525"
      },
      "source": [
        "%matplotlib inline\n",
        "import matplotlib.pyplot as plt\n",
        "import matplotlib.ticker as mticker\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "import os\n",
        "from six.moves import urllib\n",
        "from ftplib import FTP\n",
        "from io import StringIO\n",
        "from IPython.display import clear_output\n",
        "from functools import reduce\n",
        "import tarfile\n",
        "import subprocess\n",
        "#subprocess.run([\"ls\", \"-l\"])\n",
        "import zipfile\n",
        "import shutil # move files\n",
        "import psutil\n",
        "\n",
        "\n",
        "# Load the Drive helper and mount\n",
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount(\"/content/drive\", force_remount=True).\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Z2fNl9zeKd6E",
        "colab_type": "text"
      },
      "source": [
        "## Defines"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2S38XHOuKd6G",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "ROOT_PATH     = \"drive/My Drive/TCC/\"\n",
        "DATASETS_PATH = ROOT_PATH + \"datasets/\"\n",
        "SOYBEAN_PATH  = DATASETS_PATH + \"CBOTSoybeanFutures/\"\n",
        "WEATHER_PATH  = DATASETS_PATH + \"GHCN_Data/\"\n",
        "SOYBEAN_URL   = \"http://www.quandl.com/api/v3/datasets/CHRIS/CME_S1/data.csv\"\n",
        "USDA_PATH     = \"datasets/USDA-NASS-soybeans-production_bushels-2015/\"\n",
        "\n",
        "WEATHER_PATH_DRIVE_ZIP = WEATHER_PATH + \"data/zip/\"\n",
        "WEATHER_PATH_DRIVE_CSV = WEATHER_PATH + \"data/csv/\"\n",
        "FIXED_STATE_FILE       = WEATHER_PATH + \"fixed_states.txt\"\n",
        "CALCULATED_STATE_FILE  = WEATHER_PATH + \"calculated_states.txt\"\n",
        "\n",
        "DOWNLOADED_STATIONS_FILE      = WEATHER_PATH + \"downloaded_stations.txt\"\n",
        "DOWNLOADED_STATIONS_FILE_TEMP = DOWNLOADED_STATIONS_FILE\n",
        "\n",
        "\n",
        "plt.rcParams[\"figure.figsize\"] = [19,15]\n",
        "plt.rcParams.update({'font.size': 27})\n"
      ],
      "execution_count": 20,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "V6kLRNs8z6VB",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Create directories\n",
        "# and initial files\n",
        "\n",
        "if not os.path.exists(SOYBEAN_PATH):\n",
        "  os.makedirs(SOYBEAN_PATH)\n",
        "\n",
        "if not os.path.exists(WEATHER_PATH_DRIVE_ZIP):\n",
        "  os.makedirs(WEATHER_PATH_DRIVE_ZIP)\n",
        "\n",
        "if not os.path.exists(WEATHER_PATH_DRIVE_CSV):\n",
        "  os.makedirs(WEATHER_PATH_DRIVE_CSV)\n",
        "\n",
        "if not os.path.exists(DOWNLOADED_STATIONS_FILE):\n",
        "  open(DOWNLOADED_STATIONS_FILE,'a').close()\n",
        "\n",
        "if not os.path.exists(DOWNLOADED_STATIONS_FILE_TEMP):\n",
        "  open(DOWNLOADED_STATIONS_FILE_TEMP,'a').close()\n",
        "\n",
        "if not os.path.exists(FIXED_STATE_FILE):\n",
        "  open(FIXED_STATE_FILE,'a').close()\n",
        "\n",
        "if not os.path.exists(CALCULATED_STATE_FILE):\n",
        "  open(CALCULATED_STATE_FILE,'a').close()\n"
      ],
      "execution_count": 30,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KrgF2iI-kedc",
        "colab_type": "text"
      },
      "source": [
        "#### https://github.com/aaronpenne/get_noaa_ghcn_data.git"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HqmMyA5EYGMT",
        "colab_type": "text"
      },
      "source": [
        "##### https://github.com/aaronpenne/get_noaa_ghcn_data/blob/master/get_station_id.py\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "seJlWHeOT9F1",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# -*- coding: utf-8 -*-\n",
        "\"\"\"\n",
        "Searches list of stations via user input to find the station ID.\n",
        "Author: Aaron Penne\n",
        "------------------------------\n",
        "Variable   Columns   Type\n",
        "------------------------------\n",
        "ID            1-11   Character\n",
        "LATITUDE     13-20   Real\n",
        "LONGITUDE    22-30   Real\n",
        "ELEVATION    32-37   Real\n",
        "STATE        39-40   Character\n",
        "NAME         42-71   Character\n",
        "GSN FLAG     73-75   Character\n",
        "HCN/CRN FLAG 77-79   Character\n",
        "WMO ID       81-85   Character\n",
        "------------------------------\n",
        "\"\"\"\n",
        "\n",
        "import sys\n",
        "import pandas as pd\n",
        "from ftplib import FTP\n",
        "import os\n",
        "\n",
        "output_dir = os.path.relpath('output')\n",
        "if not os.path.isdir(output_dir):\n",
        "    os.mkdir(output_dir)\n",
        "\n",
        "ftp_path_dly = '/pub/data/ghcn/daily/'\n",
        "ftp_path_dly_all = '/pub/data/ghcn/daily/all/'\n",
        "ftp_filename = 'ghcnd-stations.txt'\n",
        "\n",
        "def connect_to_ftp():\n",
        "    ftp_path_root = 'ftp.ncdc.noaa.gov'\n",
        "\n",
        "    # Access NOAA FTP server\n",
        "    ftp = FTP(ftp_path_root)\n",
        "    message = ftp.login()  # No credentials needed\n",
        "    print(message)\n",
        "    return ftp\n",
        "\n",
        "def get_station_id(ftp, search_term):\n",
        "    '''\n",
        "    Get stations file\n",
        "    '''\n",
        "    ftp_full_path = os.path.join(ftp_path_dly, ftp_filename)\n",
        "    local_full_path = os.path.join(output_dir, ftp_filename)\n",
        "    if not os.path.isfile(local_full_path):\n",
        "        with open(local_full_path, 'wb+') as f:\n",
        "            ftp.retrbinary('RETR ' + ftp_full_path, f.write)\n",
        "\n",
        "    '''\n",
        "    Get user search term\n",
        "    '''\n",
        "    query = search_term\n",
        "    query = query.upper()\n",
        "    print(\"> Query: '\"+query+\"'\")\n",
        "\n",
        "    '''\n",
        "    Read stations text file using fixed-width-file reader built into pandas\n",
        "    '''\n",
        "    # http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_fwf.html\n",
        "    dtype = {'STATION_ID': str,\n",
        "             'LATITUDE': str,\n",
        "             'LONGITUDE': str,\n",
        "             'ELEVATION': str,\n",
        "             'STATE': str,\n",
        "             'STATION_NAME': str,\n",
        "             'GSN_FLAG': str,\n",
        "             'HCN_CRN_FLAG': str,\n",
        "             'WMO_ID': str}\n",
        "    names = ['STATION_ID', 'LATITUDE', 'LONGITUDE', 'ELEVATION', 'STATE', 'STATION_NAME', 'GSN_FLAG', 'HCN_CRN_FLAG', 'WMO_ID']\n",
        "    widths = [11,  # Station ID\n",
        "              9,   # Latitude (decimal degrees)\n",
        "              10,  # Longitude (decimal degrees)\n",
        "              7,   # Elevation (meters)\n",
        "              3,   # State (USA stations only)\n",
        "              31,  # Station Name\n",
        "              4,   # GSN Flag\n",
        "              4,   # HCN/CRN Flag\n",
        "              6]   # WMO ID\n",
        "    df = pd.read_fwf(local_full_path, widths=widths, names=names, dtype=dtype, header=None)\n",
        "\n",
        "    '''\n",
        "    Replace missing values (nan, -999.9)\n",
        "    '''\n",
        "    df['STATE'] = df['STATE'].replace('nan', '--')\n",
        "    df['GSN_FLAG'] = df['GSN_FLAG'].replace('nan', '---')\n",
        "    df['HCN_CRN_FLAG'] = df['GSN_FLAG'].replace('nan', '---')\n",
        "    df = df.replace(-999.9, float('nan'))\n",
        "\n",
        "    try:\n",
        "        '''\n",
        "        Get query results, but only the columns we care about\n",
        "        '''\n",
        "        print('Searching records...')\n",
        "        matches = df['STATION_ID'].str.contains(query)\n",
        "        df = df.loc[matches, ['STATION_ID', 'LATITUDE', 'LONGITUDE', 'ELEVATION', 'STATE', 'STATION_NAME']]\n",
        "        df.reset_index(drop=True, inplace=True)\n",
        "\n",
        "        '''\n",
        "        Get file sizes of each station's records to augment results\n",
        "        '''\n",
        "        #print('Getting file sizes...', end='')\n",
        "        #print(df.index)\n",
        "        #ftp.voidcmd('TYPE I')  # Needed to avoid FTP error with ftp.size()\n",
        "        #count=0\n",
        "        #last = ''\n",
        "        #for i in list(df.index):\n",
        "        #    count = count + 1\n",
        "        #    print('.', end='')\n",
        "        #    ftp_dly_file = ftp_path_dly + 'all/' + df.loc[i, 'STATION_ID'] + '.dly'\n",
        "        #    #print(df.loc[i, 'STATION_ID'], end='')\n",
        "        #    df.loc[i, 'SIZE'] = round(ftp.size(ftp_dly_file)/1000)  # Kilobytes\n",
        "        #    #print('size: %d KB' %round(ftp.size(ftp_dly_file)/1000))\n",
        "        #    actual = \" %.1f%% \" % ((count/df.index.size)*100)\n",
        "        #    if (actual != last):\n",
        "        #      clear_output()\n",
        "        #      last = actual\n",
        "        #      #print(\"%.2f%% \" %((count/df.index.size)*100), end='')\n",
        "        #      print('Getting file sizes...')\n",
        "        #      print(str(actual) + ' ['+ str(count) + ' of ' + str(df.index.size) + ']')\n",
        "           \n",
        "        print()\n",
        "        print()\n",
        "\n",
        "        '''\n",
        "        Sort by size then by rounded lat/long values to group geographic areas and show stations with most data\n",
        "        '''\n",
        "        df_sort = df.round(0)\n",
        "        #df_sort.sort_values(['LATITUDE', 'LONGITUDE', 'SIZE'], ascending=False, inplace=True)\n",
        "        df_sort.sort_values(['LATITUDE', 'LONGITUDE'], ascending=False, inplace=True)\n",
        "        df = df.loc[df_sort.index]\n",
        "        df.reset_index(drop=True, inplace=True)\n",
        "        \n",
        "    except:\n",
        "        print('Station not found')\n",
        "        traceback.print_exc(file=sys.stdout)\n",
        "        ftp.quit()\n",
        "        sys.exit()\n",
        "    \n",
        "    '''\n",
        "    Print headers and values to facilitate reading\n",
        "    '''\n",
        "    #selection = 'Index'\n",
        "    #station_id = 'Station_ID '\n",
        "    #lat = 'Latitude'\n",
        "    #lon = 'Longitude'\n",
        "    #state = 'State'\n",
        "    #name = 'Station_Name                '\n",
        "    #size = ' File_Size'\n",
        "    # Format output to be pretty, hopefully there is a prettier way to do this.\n",
        "    #print('{: <6}{: <31}{: <6}({: >8},{: >10}){: >13}'.format(selection, name, state, lat, lon, size))\n",
        "    #print('-'*5 + ' ' + '-'*30 + ' ' + '-'*5 + ' ' + '-'*21 + ' ' + '-'*12)\n",
        "    #for i in list(df.index):\n",
        "    #    print('{: 4}: {: <31}{: <6}({: >8},{: >10}){: >10} Kb'.format(i,\n",
        "    #                                                                      df.loc[i,'STATION_NAME'],\n",
        "    #                                                                      df.loc[i,'STATE'],\n",
        "    #                                                                      df.loc[i,'LATITUDE'],\n",
        "    #                                                                      df.loc[i,'LONGITUDE'],\n",
        "    #                                                                      df.loc[i,'SIZE']))\n",
        "\n",
        "#    '''\n",
        "#    Get user selection\n",
        "#    '''\n",
        "#    try:\n",
        "#        query = input('Enter selection (ex. 001, 42): ')\n",
        "#        query = int(query)\n",
        "#    except:\n",
        "#        print('Please enter valid selection (ex. 001, 42)')\n",
        "#        ftp.quit()\n",
        "#        sys.exit()\n",
        "\n",
        "    #station_id = df.loc[query, 'STATION_ID']\n",
        "    station_id = df\n",
        "    return station_id\n",
        "\n",
        "\n",
        "def get_station(ftp=None, search_term='US'):\n",
        "  close_after = False\n",
        "  if ftp==None:\n",
        "    ftp = connect_to_ftp()\n",
        "    close_after = True\n",
        "  \n",
        "  station_id = get_station_id(ftp,search_term)\n",
        "  #print(station_id)\n",
        "  \n",
        "  if close_after:\n",
        "    ftp.quit()\n",
        "  \n",
        "  return (station_id)\n",
        "    "
      ],
      "execution_count": 22,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ekDgKsdXkMvj",
        "colab_type": "text"
      },
      "source": [
        "#####https://github.com/aaronpenne/get_noaa_ghcn_data/blob/master/get_dly.py\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YsfzRsuGPS4C",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "\n",
        "\"\"\"\n",
        "Grabs .dly file from the NOAA GHCN FTP server, parses, and reshapes to have one\n",
        "day per row and element values in the columns. Writes output as CSV.\n",
        "Author: Aaron Penne\n",
        ".dly Format In (roughly):                     .csv Format Out (roughly):\n",
        "-------------------------                     --------------------------\n",
        "Month1  PRCP  Day1  Day2 ... Day31            Day1  PRCP  SNOW\n",
        "Month1  SNOW  Day1  Day2 ... Day31            Day2  PRCP  SNOW\n",
        "Month2  PRCP  Day1  Day2 ... Day31            Day3  PRCP  SNOW\n",
        "Month2  SNOW  Day1  Day2 ... Day31            Day4  PRCP  SNOW\n",
        "Starting with 5 core elements (per README)\n",
        "    PRCP = Precipitation (tenths of mm)\n",
        "    SNOW = Snowfall (mm)\n",
        "    SNWD = Snow depth (mm)\n",
        "    TMAX = Maximum temperature (tenths of degrees C)\n",
        "    TMIN = Minimum temperature (tenths of degrees C)\n",
        "ICD:\n",
        "    ------------------------------\n",
        "    Variable   Columns   Type\n",
        "    ------------------------------\n",
        "    ID            1-11   Character\n",
        "    YEAR         12-15   Integer\n",
        "    MONTH        16-17   Integer\n",
        "    ELEMENT      18-21   Character\n",
        "    VALUE1       22-26   Integer\n",
        "    MFLAG1       27-27   Character\n",
        "    QFLAG1       28-28   Character\n",
        "    SFLAG1       29-29   Character\n",
        "    VALUE2       30-34   Integer\n",
        "    MFLAG2       35-35   Character\n",
        "    QFLAG2       36-36   Character\n",
        "    SFLAG2       37-37   Character\n",
        "      .           .          .\n",
        "      .           .          .\n",
        "      .           .          .\n",
        "    VALUE31    262-266   Integer\n",
        "    MFLAG31    267-267   Character\n",
        "    QFLAG31    268-268   Character\n",
        "    SFLAG31    269-269   Character\n",
        "    ------------------------------\n",
        "\"\"\"\n",
        "\n",
        "import pandas as pd\n",
        "from ftplib import FTP\n",
        "from io import StringIO\n",
        "import os\n",
        "\n",
        "ftp_path_dly_all = '/pub/data/ghcn/daily/all/'\n",
        "\n",
        "def connect_to_ftp():\n",
        "    \"\"\"\n",
        "    Get FTP server and file details\n",
        "    \"\"\"\n",
        "    ftp_path_root = 'ftp.ncdc.noaa.gov'\n",
        "    # Access NOAA FTP server\n",
        "    ftp = FTP(ftp_path_root)\n",
        "    message = ftp.login()  # No credentials needed\n",
        "    #print(message)\n",
        "    return ftp\n",
        "\n",
        "# Marlon Franco\n",
        "def disconnect_to_ftp(ftp_connection):\n",
        "    return ftp_connection.quit()\n",
        "\n",
        "def get_flags(s):\n",
        "    \"\"\"\n",
        "    Get flags, replacing empty flags with '_' for clarity (' S ' becomes '_S_')\n",
        "    \"\"\"\n",
        "    m_flag = s.read(1)\n",
        "    m_flag = m_flag if m_flag.strip() else '_'\n",
        "    q_flag = s.read(1)\n",
        "    q_flag = q_flag if q_flag.strip() else '_'\n",
        "    s_flag = s.read(1)\n",
        "    s_flag = s_flag if s_flag.strip() else '_'\n",
        "    return [m_flag + q_flag + s_flag]\n",
        "\n",
        "def create_dataframe(element, dict_element):\n",
        "    \"\"\"\n",
        "    Make dataframes out of the dicts, make the indices date strings (YYYY-MM-DD)\n",
        "    \"\"\"\n",
        "    element = element.upper()\n",
        "    df_element = pd.DataFrame(dict_element)\n",
        "    # Add dates (YYYY-MM-DD) as index on df. Pad days with zeros to two places\n",
        "    df_element.index = df_element['YEAR'] + '-' + df_element['MONTH'] + '-' + df_element['DAY'].str.zfill(2)\n",
        "    df_element.index.name = 'DATE'\n",
        "    # Arrange columns so ID, YEAR, MONTH, DAY are at front. Leaving them in for plotting later - https://stackoverflow.com/a/31396042\n",
        "    for col in ['DAY', 'MONTH', 'YEAR', 'ID']:\n",
        "        df_element = move_col_to_front(col, df_element)\n",
        "    # Convert numerical values to float\n",
        "    df_element.loc[:,element] = df_element.loc[:,element].astype(float)\n",
        "    return df_element\n",
        "\n",
        "def move_col_to_front(element, df):\n",
        "    element = element.upper()\n",
        "    cols = df.columns.tolist()\n",
        "    cols.insert(0, cols.pop(cols.index(element)))\n",
        "    df = df.reindex(columns=cols)\n",
        "    return df\n",
        "\n",
        "def dly_to_csv(ftp, station_id, output_dir, save_dly):\n",
        "    #output_dir = os.path.relpath('output')\n",
        "    if not os.path.isdir(output_dir):\n",
        "        os.makedirs(output_dir)\n",
        "    ftp_filename = station_id + '.dly'\n",
        "\n",
        "    # Write .dly file to stream using StringIO using FTP command 'RETR'\n",
        "    s = StringIO()\n",
        "    ftp.retrlines('RETR ' + ftp_path_dly_all + ftp_filename, s.write)\n",
        "    s.seek(0)\n",
        "\n",
        "    # Write .dly file to dir to preserve original # FIXME make optional?\n",
        "    if (save_dly):\n",
        "      with open(os.path.join(output_dir, ftp_filename), 'wb+') as f:\n",
        "        ftp.retrbinary('RETR ' + ftp_path_dly_all + ftp_filename, f.write)\n",
        "\n",
        "    # Move to first char in file\n",
        "    s.seek(0)\n",
        "\n",
        "    # File params\n",
        "    num_chars_line = 269\n",
        "    num_chars_metadata = 21\n",
        "\n",
        "    element_list = ['PRCP', 'SNOW', 'SNWD', 'TMAX', 'TMIN']\n",
        "\n",
        "    '''\n",
        "    Read through entire StringIO stream (the .dly file) and collect the data\n",
        "    '''\n",
        "    all_dicts = {}\n",
        "    element_flag = {}\n",
        "    prev_year = '0000'\n",
        "    i = 0\n",
        "    while True:\n",
        "        i += 1\n",
        "\n",
        "        '''\n",
        "        Read metadata for each line (one month of data for a particular element per line)\n",
        "        '''\n",
        "        id_station = s.read(11)\n",
        "        year = s.read(4)\n",
        "        month = s.read(2)\n",
        "        day = 0\n",
        "        element = s.read(4)\n",
        "\n",
        "        # If this is blank then we've reached EOF and should exit loop\n",
        "        if not element:\n",
        "            break\n",
        "\n",
        "        '''\n",
        "        Print status\n",
        "        '''\n",
        "        if year != prev_year:\n",
        "            #print('Year {} | Line {}'.format(year, i))\n",
        "            prev_year = year\n",
        "\n",
        "        '''\n",
        "        Loop through each day in rest of row, break if current position is end of row\n",
        "        '''\n",
        "        while s.tell() % num_chars_line != 0:\n",
        "            day += 1\n",
        "            # Fill in contents of each dict depending on element type in current row\n",
        "            if day == 1:\n",
        "                try:\n",
        "                    first_hit = element_flag[element]\n",
        "                except:\n",
        "                    element_flag[element] = 1\n",
        "                    all_dicts[element] = {}\n",
        "                    all_dicts[element]['ID'] = []\n",
        "                    all_dicts[element]['YEAR'] = []\n",
        "                    all_dicts[element]['MONTH'] = []\n",
        "                    all_dicts[element]['DAY'] = []\n",
        "                    all_dicts[element][element.upper()] = []\n",
        "                    all_dicts[element][element.upper() + '_FLAGS'] = []\n",
        "\n",
        "            value = s.read(5)\n",
        "            flags = get_flags(s)\n",
        "            if value == '-9999':\n",
        "                continue\n",
        "            all_dicts[element]['ID'] += [station_id]\n",
        "            all_dicts[element]['YEAR'] += [year]\n",
        "            all_dicts[element]['MONTH'] += [month]\n",
        "            all_dicts[element]['DAY'] += [str(day)]\n",
        "            all_dicts[element][element.upper()] += [value]\n",
        "            all_dicts[element][element.upper() + '_FLAGS'] += flags\n",
        "\n",
        "    '''\n",
        "    Create dataframes from dict\n",
        "    '''\n",
        "    all_dfs = {}\n",
        "    for element in list(all_dicts.keys()):\n",
        "        all_dfs[element] = create_dataframe(element, all_dicts[element])\n",
        "\n",
        "    '''\n",
        "    Combine all element dataframes into one dataframe, indexed on date.\n",
        "    '''\n",
        "    # pd.concat automagically aligns values to matching indices, therefore the data is date aligned!\n",
        "    list_dfs = []\n",
        "    for df in list(all_dfs.keys()):\n",
        "        list_dfs += [all_dfs[df]]\n",
        "    df_all = pd.concat(list_dfs, axis=1, sort=False)\n",
        "    df_all.index.name = 'MM/DD/YYYY'\n",
        "\n",
        "    '''\n",
        "    Remove duplicated/broken columns and rows\n",
        "    '''\n",
        "    # https://stackoverflow.com/a/40435354\n",
        "    df_all = df_all.loc[:,~df_all.columns.duplicated()]\n",
        "    df_all = df_all.loc[df_all['ID'].notnull(), :]\n",
        "\n",
        "    '''\n",
        "    Output to CSV, convert everything to strings first\n",
        "    '''\n",
        "    # NOTE: To open the CSV in Excel, go through the CSV import wizard, otherwise it will come out broken\n",
        "    df_out = df_all.astype(str)\n",
        "    df_out.to_csv(os.path.join(output_dir, station_id + '.csv'))\n",
        "    #print('\\nOutput CSV saved to: {}'.format(os.path.join(output_dir, station_id + '.csv')))\n",
        "\n",
        "def get_weather_data(ftp=None, station_id='USR0000CCHC',output_dir=WEATHER_PATH, save_dly=False):\n",
        "    close_after = False\n",
        "    if ftp==None:\n",
        "      ftp = connect_to_ftp()\n",
        "      close_after = True\n",
        "    \n",
        "    dly_to_csv(ftp, station_id,output_dir, save_dly)\n",
        "    if close_after:\n",
        "      ftp.quit()"
      ],
      "execution_count": 45,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "IpOTn6JzKd6L",
        "colab_type": "text"
      },
      "source": [
        "## Fetch Data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "JKh_V8Y1Kd6N",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def fetch_soybean_data(soybean_url=SOYBEAN_URL, soybean_path=SOYBEAN_PATH):\n",
        "    if not os.path.isdir(soybean_path):\n",
        "        os.makedirs(soybean_path)\n",
        "    csv_path = os.path.join(soybean_path, \"soybeans.csv\")\n",
        "    urllib.request.urlretrieve(soybean_url, csv_path)\n",
        "\n",
        "def fetch_weather_data(contains='US', weather_path=WEATHER_PATH_DRIVE_CSV, save_dly=False, how_much=100):\n",
        "    conn = connect_to_ftp()\n",
        "    weather = get_station(conn,search_term=contains) # List all stations from USA\n",
        "    downloaded_stations = \"\"\n",
        "    with open(DOWNLOADED_STATIONS_FILE_TEMP,\"r+\") as f:\n",
        "      downloaded_stations = f.read()\n",
        "    count = 0\n",
        "    count2 = 0\n",
        "    total = weather['STATION_ID'].size\n",
        "    amount_of_data = total * how_much/100\n",
        "    last = ''\n",
        "    for station in weather['STATION_ID']:\n",
        "      print('.',end='')\n",
        "      count += 1\n",
        "      actual = \"%.2f%% \" %((count/total)*100)\n",
        "      actual_partial = \"%.2f%% \" %((count2/amount_of_data)*100)\n",
        "      if (station+'.csv' not in downloaded_stations):\n",
        "        if (count2 > amount_of_data):\n",
        "          print('download completed: ['+str(count2)+' of '+str(amount_of_data)+'], total = '+str(total))\n",
        "          return True\n",
        "        count2 += 1\n",
        "        print('get ', end='')\n",
        "        get_weather_data(conn, station, weather_path, save_dly)\n",
        "        print('done')\n",
        "        downloaded_stations += station+'.csv\\r\\n'\n",
        "        with open(DOWNLOADED_STATIONS_FILE_TEMP,\"a+\") as f:\n",
        "          f.write(station+'.csv\\r\\n')\n",
        "      else:\n",
        "        print(',',end='')\n",
        "      if (actual != last):\n",
        "        clear_output()\n",
        "        last = actual\n",
        "        print('Getting '+str(how_much)+'% of weather data from GHCN ftp containing \\''+contains+'\\' in STATION_ID...')\n",
        "        print('PARTIAL: '+str(actual_partial) + ' ['+ str(count2) + ' of ' + str(amount_of_data) + ']')\n",
        "        print('TOTAL: '+str(actual) + ' ['+ str(count) + ' of ' + str(total) + ']')\n",
        "\n",
        "    disconnect_to_ftp(conn)\n",
        "    print('Final: download completed: ['+str(count2)+' of '+str(amount_of_data)+'], total = '+str(total))\n",
        "    return True\n"
      ],
      "execution_count": 46,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ERSO2ajR0Wpa",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Update the local temp control file\n",
        "#!echo \"$DOWNLOADED_STATIONS_FILE\" > \"$DOWNLOADED_STATIONS_FILE_TEMP\" .\n",
        "\n",
        "#fetch_weather_data(how_much=0.54) #0.54% of total amount of data\n",
        "fetch_weather_data()\n",
        "\n",
        "# Update the control file\n",
        "!echo \"$DOWNLOADED_STATIONS_FILE_TEMP\" > \"$DOWNLOADED_STATIONS_FILE\" ."
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_fUWwdUl8GlQ",
        "colab_type": "text"
      },
      "source": [
        "### Check the number of downloaded station's csv file\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8iYlQE8l06QV",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 136
        },
        "outputId": "b8f7d7e1-3f8d-4596-c42a-3771acb0a605"
      },
      "source": [
        "weather = get_station(search_term='US') # List all stations from USA\n",
        "    \n",
        "print(\"# of stations in GHCN FTP: \", end=\"\")\n",
        "print(str(weather['STATION_ID'].size))\n",
        "\n",
        "print(\"# of downloaded csv files: \", end=\"\")\n",
        "!find \"$WEATHER_PATH_DRIVE_CSV\" -type f | wc -l\n",
        "\n",
        "print(\"# of downloaded stations in control file: \", end=\"\")\n",
        "with open(DOWNLOADED_STATIONS_FILE) as f:\n",
        "  num_lines = sum(1 for _ in f.readlines())\n",
        "  print(str(num_lines))\n"
      ],
      "execution_count": 38,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "> Query: 'US'\n",
            "Searching records...\n",
            "\n",
            "\n",
            "# of stations in GHCN FTP: 0\n",
            "# of downloaded csv files: 13924\n",
            "# of downloaded stations in control file: 13924\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bMuNOlG_C-XX",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 54
        },
        "outputId": "ec53bce4-47a8-4466-b024-aeaecd03f2d4"
      },
      "source": [
        "def force_update_control_file():\n",
        "  directory = os.path.join(WEATHER_PATH_DRIVE_CSV)\n",
        "  with open(DOWNLOADED_STATIONS_FILE,\"w\") as f:\n",
        "    for root,dirs,files in os.walk(directory):\n",
        "      for file in files:\n",
        "        print('.',end='')\n",
        "        if file.endswith(\".csv\"):\n",
        "          f.write(file+'\\r\\n')\n",
        "\n",
        "force_update_control_file()"
      ],
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "...................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................."
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jrzlP92gysYB",
        "colab_type": "text"
      },
      "source": [
        "# Get 'US' Stations\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AdtpepUByz3u",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "newfile = ''\n",
        "with open(PROJECT_PATH+'ghcnd-stations-us.txt', 'r') as f: \n",
        "  for line in f.readlines():\n",
        "      line_list = line.split(' ')\n",
        "      station = line_list[0]\n",
        "      newfile += station\n",
        "      for word in line_list:\n",
        "        if (len(word) > 1):\n",
        "          if (word[0].isalpha() and word!=station):\n",
        "            state = word\n",
        "            newfile += ','+state+'\\n'\n",
        "            break\n",
        "            \n",
        "print(newfile)\n",
        "\n",
        "with open(PROJECT_PATH+'ghcnd-stations-us.csv', 'w+') as f: \n",
        "  f.write(newfile)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vB-KlZ2cyrbV",
        "colab_type": "text"
      },
      "source": [
        "# Organize Stations by State"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HpZLCb1SfU32",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def organize_stations_by_state():\n",
        "  f1='' #stations_not_dowloaded\n",
        "  csv_path = WEATHER_PATH_DRIVE_CSV\n",
        "  with open(WEATHER_PATH+'ghcnd-stations-us.csv', 'r') as f:\n",
        "    for line in f:\n",
        "      station = line.split(',')[0]\n",
        "      state = line.split(',')[1].rstrip()\n",
        "      # Create target Directory if don't exist\n",
        "      if not os.path.exists(csv_path+state):\n",
        "        os.mkdir(csv_path+state)\n",
        "        print(\"Directory \" , csv_path+state ,  \" Created \")\n",
        "      #else:\n",
        "      #\tprint(\"Directory \" , \"csv/\"+state ,  \" already exists\")\n",
        "      if not os.path.exists(csv_path+station+\".csv\"):\n",
        "        print(\".\", end='')\n",
        "        f1+=station+\"\\n\"\n",
        "      else:\n",
        "        os.rename(csv_path+station+\".csv\", csv_path+state+\"/\"+station+\".csv\")\n",
        "  with open(WEATHER_PATH+'stations_not_dowloaded.csv', 'w+') as f: \n",
        "    f.write(f1)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oW-DPvJAgtT_",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "524e997d-11f6-42cf-84bf-c77b6a7466f7"
      },
      "source": [
        "!ls drive/My\\ Drive/TCC/"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "drive  ghcnd-stations-us.csv  ghcnd-stations-us.txt  sample_data\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MDiOb_uCjwKx",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sLength = len(df1['TMAX'])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YXUq1kM1myO5",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        " df1['e'] = p.Series(np.random.randn(sLength), index=df1.index)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ryu9G0nCo2IP",
        "colab_type": "text"
      },
      "source": [
        "# Fix columns"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3RbUUEj3j9ds",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def fix_columns(df):\n",
        "  for column in df:\n",
        "    if column in ('ID','TMAX','TMIN','TAVG','PRCP'):\n",
        "      pass\n",
        "    else:\n",
        "      #print(' deleting ',column, end='')\n",
        "      del(df[column])\n",
        "  if 'TMAX' not in df:\n",
        "    #print(' creating TMAX... ', end='')\n",
        "    #sLength = sizes['TMAX']\n",
        "    df['TMAX'] = pd.Series(0, index=df.index)\n",
        "  if 'TMIN' not in df:\n",
        "    #print(' creating TMIN... ', end='')\n",
        "    #sLength = sizes['TMIN']\n",
        "    df['TMIN'] = pd.Series(0, index=df.index)\n",
        "  if 'TAVG' not in df:\n",
        "    #print(' creating TAVG... ', end='')\n",
        "    #sLength = sizes['TAVG']\n",
        "    df['TAVG'] = pd.Series(0, index=df.index)\n",
        "  if 'PRCP' not in df:\n",
        "    #print(' creating PRCP... ')\n",
        "    #sLength = sizes['PRCP']\n",
        "    df['PRCP'] = pd.Series(0, index=df.index)\n",
        "  df=df.fillna(method='ffill')\n",
        "  "
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2U36ulu1RTPb",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df_ref = load_single_csv(CSV_PATH+'WA/USS0017B04S.csv')\n",
        "sizes = {'TMAX':len(df_ref['TMAX']),'TMIN':len(df_ref['TMIN']),'TAVG':len(df_ref['TAVG']),'PRCP':len(df_ref['PRCP'])}\n",
        " "
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HYyWd7QcjrjF",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def fix_dataframes(folder=''):\n",
        "  root_path = CSV_PATH+folder+'/'\n",
        "  print(root_path)\n",
        "  count=0\n",
        "  count2=10\n",
        "  total=0\n",
        "  for root, dirs, files in os.walk(root_path):\n",
        "    total=len(files)\n",
        "    for file in files:\n",
        "      if '.csv' in file:\n",
        "        station=file.strip('.csv')\n",
        "        #print(station)\n",
        "        path = os.path.join(root, file)\n",
        "        df = load_single_csv(path)\n",
        "        fix_columns(df)\n",
        "        new_path =  os.path.join(PROJECT_PATH+'new/'+folder+'/', file)\n",
        "        # Create target Directory if don't exist\n",
        "        if not os.path.exists(PROJECT_PATH+'new/'+folder+'/'):\n",
        "          os.mkdir(PROJECT_PATH+'new/'+folder+'/')\n",
        "          print(\"Directory \" , PROJECT_PATH+'new/'+folder+'/' ,  \" Created \")\n",
        "        df.to_csv(new_path)\n",
        "        if count2 == 70:\n",
        "          count2=0\n",
        "          actual = \"%.2f%% \" %((count/total)*100)\n",
        "          clear_output()\n",
        "          print('Fixing ',folder,' stations... ',actual,' (',str(count),' of ',str(total),')')\n",
        "        count+=1\n",
        "        count2+=1\n",
        "  print('Done: %.2f%% ' %((count/total)*100))\n",
        "  return True"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BBas0LD_R7vG",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        },
        "outputId": "8b8cb90f-2035-4545-de19-bb10e22a3488"
      },
      "source": [
        "fixed_states = \"\"\n",
        "with open(FIXED_STATE_FILE, \"r+\") as f:\n",
        "  fixed_states = f.read()\n",
        "\n",
        "print('Already fixed:',fixed_states)  \n",
        "  \n",
        "for root, dirs, files in os.walk(CSV_PATH):\n",
        "    total=len(dirs)\n",
        "    for state in dirs:\n",
        "      if (state not in fixed_states):\n",
        "        if(fix_dataframes(state)):\n",
        "          fixed_states+= state\n",
        "          with open(FIXED_STATE_FILE,\"a\") as f:\n",
        "            f.write(state+'\\r\\n')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Fixing  DE  stations...  54.55%   ( 60  of  110 )\n",
            "Done: 100.00% \n",
            "drive/My Drive/TCC/datasets/GHCN_Data/data/csv/DC/\n",
            "Directory  drive/My Drive/TCC/datasets/GHCN_Data/data/new/DC/  Created \n",
            "Done: 100.00% \n",
            "drive/My Drive/TCC/datasets/GHCN_Data/data/csv/UM/\n",
            "Directory  drive/My Drive/TCC/datasets/GHCN_Data/data/new/UM/  Created \n",
            "Done: 100.00% \n",
            "drive/My Drive/TCC/datasets/GHCN_Data/data/csv/PI/\n",
            "Directory  drive/My Drive/TCC/datasets/GHCN_Data/data/new/PI/  Created \n",
            "Done: 100.00% \n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fNCzVmGhs5j-",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "fix_dataframes('CA')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gE-ZEWeoR8Qa",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df1 = load_single_csv('drive/My Drive/TCC/datasets/GHCN_Data/data/csv/TX/US1TXAC0002.csv')\n",
        "df2 = load_single_csv('drive/My Drive/TCC/datasets/GHCN_Data/data/new/TX/US1TXAC0002.csv')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Uv8htZIiSgfr",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        },
        "outputId": "09d2cdbb-9743-4462-9cfe-67f00783eef5"
      },
      "source": [
        "df1.tail()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th>ID</th>\n",
              "      <th>PRCP</th>\n",
              "      <th>PRCP_FLAGS</th>\n",
              "      <th>SNOW</th>\n",
              "      <th>SNOW_FLAGS</th>\n",
              "      <th>SNWD</th>\n",
              "      <th>SNWD_FLAGS</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MM/DD/YYYY</th>\n",
              "      <th>YEAR</th>\n",
              "      <th>MONTH</th>\n",
              "      <th>DAY</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2014-06-19</th>\n",
              "      <th>2014</th>\n",
              "      <th>6</th>\n",
              "      <th>19</th>\n",
              "      <td>US1TXAC0002</td>\n",
              "      <td>0.0</td>\n",
              "      <td>__N</td>\n",
              "      <td>0.0</td>\n",
              "      <td>__N</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2014-06-20</th>\n",
              "      <th>2014</th>\n",
              "      <th>6</th>\n",
              "      <th>20</th>\n",
              "      <td>US1TXAC0002</td>\n",
              "      <td>0.0</td>\n",
              "      <td>T_N</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2014-06-21</th>\n",
              "      <th>2014</th>\n",
              "      <th>6</th>\n",
              "      <th>21</th>\n",
              "      <td>US1TXAC0002</td>\n",
              "      <td>0.0</td>\n",
              "      <td>__N</td>\n",
              "      <td>0.0</td>\n",
              "      <td>__N</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2014-06-22</th>\n",
              "      <th>2014</th>\n",
              "      <th>6</th>\n",
              "      <th>22</th>\n",
              "      <td>US1TXAC0002</td>\n",
              "      <td>0.0</td>\n",
              "      <td>__N</td>\n",
              "      <td>0.0</td>\n",
              "      <td>__N</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2014-06-23</th>\n",
              "      <th>2014</th>\n",
              "      <th>6</th>\n",
              "      <th>23</th>\n",
              "      <td>US1TXAC0002</td>\n",
              "      <td>254.0</td>\n",
              "      <td>__N</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    ID   PRCP  ... SNWD  SNWD_FLAGS\n",
              "MM/DD/YYYY YEAR MONTH DAY                      ...                 \n",
              "2014-06-19 2014 6     19   US1TXAC0002    0.0  ...  NaN         NaN\n",
              "2014-06-20 2014 6     20   US1TXAC0002    0.0  ...  NaN         NaN\n",
              "2014-06-21 2014 6     21   US1TXAC0002    0.0  ...  NaN         NaN\n",
              "2014-06-22 2014 6     22   US1TXAC0002    0.0  ...  NaN         NaN\n",
              "2014-06-23 2014 6     23   US1TXAC0002  254.0  ...  NaN         NaN\n",
              "\n",
              "[5 rows x 7 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6tnlJLct9saV",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 235
        },
        "outputId": "741b7b9c-3d6c-4b69-ba99-bf78b9697bda"
      },
      "source": [
        "df2.tail()"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "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></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th>ID</th>\n",
              "      <th>PRCP</th>\n",
              "      <th>TMAX</th>\n",
              "      <th>TMIN</th>\n",
              "      <th>TAVG</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>MM/DD/YYYY</th>\n",
              "      <th>YEAR</th>\n",
              "      <th>MONTH</th>\n",
              "      <th>DAY</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2014-06-19</th>\n",
              "      <th>2014</th>\n",
              "      <th>6</th>\n",
              "      <th>19</th>\n",
              "      <td>US1TXAC0002</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2014-06-20</th>\n",
              "      <th>2014</th>\n",
              "      <th>6</th>\n",
              "      <th>20</th>\n",
              "      <td>US1TXAC0002</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2014-06-21</th>\n",
              "      <th>2014</th>\n",
              "      <th>6</th>\n",
              "      <th>21</th>\n",
              "      <td>US1TXAC0002</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2014-06-22</th>\n",
              "      <th>2014</th>\n",
              "      <th>6</th>\n",
              "      <th>22</th>\n",
              "      <td>US1TXAC0002</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2014-06-23</th>\n",
              "      <th>2014</th>\n",
              "      <th>6</th>\n",
              "      <th>23</th>\n",
              "      <td>US1TXAC0002</td>\n",
              "      <td>254.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                                    ID   PRCP  TMAX  TMIN  TAVG\n",
              "MM/DD/YYYY YEAR MONTH DAY                                      \n",
              "2014-06-19 2014 6     19   US1TXAC0002    0.0     0     0     0\n",
              "2014-06-20 2014 6     20   US1TXAC0002    0.0     0     0     0\n",
              "2014-06-21 2014 6     21   US1TXAC0002    0.0     0     0     0\n",
              "2014-06-22 2014 6     22   US1TXAC0002    0.0     0     0     0\n",
              "2014-06-23 2014 6     23   US1TXAC0002  254.0     0     0     0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6eEagynSKd6W",
        "colab_type": "text"
      },
      "source": [
        "# Load Data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WpWUg0U1Kd6Y",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def load_soybean_data(soybean_path=SOYBEAN_PATH):\n",
        "    csv_path = os.path.join(soybean_path, \"soybeans.csv\")\n",
        "    print(csv_path)\n",
        "    return pd.read_csv(csv_path)\n",
        "\n",
        "def load_single_csv(csv_path):\n",
        "  #print(csv_path)\n",
        "  df = pd.read_csv(csv_path,low_memory=False)\n",
        "  df.set_index(['MM/DD/YYYY','YEAR','MONTH','DAY'], inplace=True)\n",
        "  return df\n",
        "\n",
        "def extract_zip(dir_name=WEATHER_PATH_DRIVE_ZIP,destination_dir=WEATHER_PATH_DRIVE_CSV):\n",
        "  for item in os.listdir(dir_name): # loop through items in dir\n",
        "    if item.endswith(\".zip\"): # check for \".zip\" extension\n",
        "        print(\"Extracting \"+str(item), end=\"\")\n",
        "        #file_name = os.path.abspath(item) # get full path of files\n",
        "        file_name = dir_name+item # get full path of files\n",
        "        zip_ref = zipfile.ZipFile(file_name) # create zipfile object\n",
        "        zip_ref.extractall(destination_dir) # extract file to dir\n",
        "        zip_ref.close() # close file\n",
        "        print(\"... OK!\")\n",
        "        #os.remove(file_name) # delete zipped file\n",
        "  print(\"Extraction complete!\")\n",
        "\n",
        "def load_weather_data(weather_path=WEATHER_PATH_DRIVE_CSV,from_zip=False):\n",
        "  if from_zip:\n",
        "    extract_zip()\n",
        "  data_frames=[]\n",
        "  #first=True\n",
        "  directory = os.path.join(weather_path,\"\")\n",
        "  print(directory)\n",
        "  for root,dirs,files in os.walk(directory):\n",
        "    print(directory+\".\")\n",
        "    for file in files:\n",
        "      print(\".\")\n",
        "      if file.endswith(\".csv\"):\n",
        "        csv_path = os.path.join(weather_path, file)\n",
        "        df = load_single_csv(csv_path)\n",
        "        #Rename Columns\n",
        "        #df=df.drop(columns=['ID'])\n",
        "        #station = file.replace('.csv','')\n",
        "        #for column in df.columns:\n",
        "        #  if(column not in ['MM/DD/YYYY','YEAR','MONTH','DAY']):\n",
        "        #    df.rename(columns={column: station +'-'+ column}, inplace=True)\n",
        "            #print(station +'-'+ column)\n",
        "        #Append to list\n",
        "        data_frames.append(df)\n",
        "        #if (first):\n",
        "        #  data_frames = df\n",
        "        #  first=False\n",
        "        #else:\n",
        "        #  data_frames = pd.merge(data_frames, df, on=['MM/DD/YYYY','YEAR','MONTH','DAY'], how='left')\n",
        "  return data_frames\n",
        "  #return pd.concat(data_frames, axis=1)\n",
        "    \n",
        "def load_usda_data(usda_path=USDA_PATH):\n",
        "    csv_path = os.path.join(usda_path, \"data.csv\")\n",
        "    print(csv_path)\n",
        "    return pd.read_csv(csv_path, thousands=',')\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "X5XxW3-h6cSc",
        "colab_type": "text"
      },
      "source": [
        "# Calculate Mean and Standard Deviation for each state\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "h0crUCOQMN3F",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def save_csv(df,name,path):\n",
        "  #print('Saving DataFrame in ',path)\n",
        "  # Create target Directory if don't exist\n",
        "  if not os.path.exists(path):\n",
        "    os.mkdir(path)\n",
        "    print(\"Directory \" , path ,  \" Created \")\n",
        "  df.to_csv(path+name)\n",
        "  \n",
        "def read_log(file_path):\n",
        "  files_processed = \"\"\n",
        "  if not os.path.exists(file_path):\n",
        "     with open(file_path,'w+') as f:\n",
        "      files_processed = f.read()\n",
        "  else :\n",
        "    with open(file_path,'r+') as f:\n",
        "      files_processed = f.read()\n",
        "  return files_processed\n",
        "\n",
        "def write_log(file_path,content):\n",
        "  with open(file_path,'w+') as f:\n",
        "    f.write(content)\n",
        "\n",
        "def calculate(daf):\n",
        "  print('TMAX',end='')\n",
        "  daf['TMAX_mean'] = daf[[col for col in daf.columns if 'TMAX' in col ]].mean(1)\n",
        "  print(' Ok mean')\n",
        "  daf['TMAX_std'] = daf[[col for col in daf.columns if 'TMAX' in col ]].std(1)\n",
        "  print(' Ok std')\n",
        "  #daf = daf.drop(columns=['TMAX'])\n",
        "\n",
        "  print(' OK\\nTMIN', end='')\n",
        "  daf['TMIN_mean'] = daf[[col for col in daf.columns if 'TMIN' in col ]].mean(1)\n",
        "  print(' Ok mean')\n",
        "  daf['TMIN_std'] = daf[[col for col in daf.columns if 'TMIN' in col ]].std(1)\n",
        "  print(' Ok std')\n",
        "  #daf = daf.drop(columns=['TMIN'])\n",
        "\n",
        "  print(' OK\\nTAVG', end='')\n",
        "  daf['TAVG_mean'] = daf[[col for col in daf.columns if 'TAVG' in col ]].mean(1)\n",
        "  print(' Ok mean')\n",
        "  daf['TAVG_std'] = daf[[col for col in daf.columns if 'TAVG' in col ]].std(1)\n",
        "  print(' Ok std')\n",
        "  #daf = daf.drop(columns=['TAVG'])\n",
        "\n",
        "  print(' OK\\nPRCP', end='')\n",
        "  daf['PRCP_mean'] = daf[[col for col in daf.columns if 'PRCP' in col ]].mean(1)\n",
        "  print(' Ok mean')\n",
        "  daf['PRCP_std'] = daf[[col for col in daf.columns if 'PRCP' in col ]].std(1)\n",
        "  print(' Ok std')\n",
        "  #daf = daf.drop(columns=['PRCP'])\n",
        "  daf = daf.drop(columns=[col for col in daf.columns if col not in ['MM/DD/YYYY','YEAR','MONTH','DAY','TMAX_mean','TMAX_std','TMIN_mean','TMIN_std','TAVG_mean','TAVG_std','PRCP_mean','PRCP_std']])\n",
        "  print(' OK')\n",
        "  daf=daf.fillna(0)\n",
        "  return daf"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8JP4HSyINbSs",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def calculate_mean(folder=''):\n",
        "  root_path = WEATHER_PATH_DRIVE_CSV+folder+'/'\n",
        "  new_path =  os.path.join(WEATHER_PATH+'new/'+folder+'/','')\n",
        "  file_path = new_path+folder+'.txt'\n",
        "  if not os.path.exists(new_path):\n",
        "    os.mkdir(new_path)\n",
        "    print(\"Directory \" , new_path ,  \" Created \")\n",
        "  files_processed = read_log(file_path)\n",
        "  print(root_path)\n",
        "  n=0\n",
        "  count=0\n",
        "  count2=70\n",
        "  count_to_save=0\n",
        "  count_to_reset=0\n",
        "  total=0\n",
        "  already_readed=False\n",
        "  for root, dirs, files in os.walk(root_path):\n",
        "    total=len(files)\n",
        "    for file in files:\n",
        "      if '.csv' in file:\n",
        "        station=file.strip('.csv')\n",
        "        if (station not in files_processed):\n",
        "          path = os.path.join(root, file)\n",
        "          df = load_single_csv(path)\n",
        "          df = df.drop(columns=['ID'])\n",
        "          if not already_readed:\n",
        "            try:\n",
        "              daf = load_single_csv(new_path+folder+'_tmp.csv')\n",
        "            except:\n",
        "              daf = df\n",
        "            already_readed=True\n",
        "          daf = pd.concat([daf,df], axis=1)\n",
        "          if count_to_save == 100:\n",
        "            count_to_save=0\n",
        "            print('saving')\n",
        "            save_csv(daf,folder+'_tmp',new_path)\n",
        "            write_log(file_path,files_processed)\n",
        "            print('saved')\n",
        "          count_to_save+=1\n",
        "          files_processed+=station+'\\r\\n'\n",
        "          del df\n",
        "        if count2 == 70:\n",
        "          count2=0\n",
        "          actual = \"%.2f%% \" %((count/total)*100)\n",
        "          clear_output()\n",
        "          process = psutil.Process(os.getpid())\n",
        "          print('RAM usage: %.2f GB' %((process.memory_info().rss) / 1e9))\n",
        "          print('Loading ',folder,' stations in DataFrames... ',actual,' (',str(count),' of ',str(total),')')\n",
        "        count+=1\n",
        "        count2+=1\n",
        "  save_csv(daf,folder+'_tmp',new_path)\n",
        "  write_log(file_path,files_processed)\n",
        "  print('Load done: %.2f%% ' %((count/total)*100))\n",
        "  if(\"Done\" not in files_processed):\n",
        "    daf = load_single_csv(new_path+folder+'_tmp.csv')\n",
        "    daf = calculate(daf)\n",
        "    new_file_name = state+str(n)+'.csv'\n",
        "    print('Saving ', new_file_name)\n",
        "    save_csv(daf,new_file_name,new_path)\n",
        "    print('Done saving ',new_file_name)\n",
        "    write_log(file_path,files_processed+\"Done\\r\\n\")\n",
        "    print('Done!')\n",
        "    os.remove(new_path+folder+'_tmp.csv')\n",
        "  else :\n",
        "    print('Already processed.')\n",
        "  return True\n",
        "  "
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "blb-S7i16ZaN",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 306
        },
        "outputId": "8fd0e4f7-c028-486f-ff2d-18d18bf0ac9e"
      },
      "source": [
        "calculate_mean('FL')"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "RAM usage: 10.82 GB\n",
            "Loading  FL  stations in DataFrames...  99.35%   ( 1680  of  1691 )\n",
            "Load done: 100.00% \n",
            "TMAX Ok mean\n",
            " Ok std\n",
            " OK\n",
            "TMIN Ok mean\n",
            " Ok std\n",
            " OK\n",
            "TAVG Ok mean\n",
            " Ok std\n",
            " OK\n",
            "PRCP Ok mean\n",
            " Ok std\n",
            " OK\n",
            "Done!\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "True"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EhV4Km_l6UqX",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 411
        },
        "outputId": "f215f7d6-2369-4eef-ba21-69c4ea42d3b9"
      },
      "source": [
        "\n",
        "calculated_states = \"\"\n",
        "with open(CALCULATED_STATE_FILE, \"r+\") as f:\n",
        "  calculated_states = f.read()\n",
        "print('Already calculated:\\n[\\n',calculated_states,']\\n') \n",
        "\n",
        "for root, dirs, files in os.walk(WEATHER_PATH_DRIVE_CSV):\n",
        "  total=len(dirs)\n",
        "  for state in dirs:\n",
        "    if (state not in calculated_states):\n",
        "      if(calculate_mean(state)):\n",
        "        calculated_states+= state\n",
        "        with open(CALCULATED_STATE_FILE,\"a\") as f:\n",
        "          f.write(state+'\\r\\n')\n"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Loading  TX  stations in DataFrames...  3.53%   ( 174  of  4933 )\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "error",
          "ename": "KeyboardInterrupt",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-15-4eae93c1ae18>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      9\u001b[0m   \u001b[0;32mfor\u001b[0m \u001b[0mstate\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mdirs\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     10\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mstate\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcalculated_states\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 11\u001b[0;31m       \u001b[0;32mif\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcalculate_mean\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstate\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     12\u001b[0m         \u001b[0mcalculated_states\u001b[0m\u001b[0;34m+=\u001b[0m \u001b[0mstate\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     13\u001b[0m         \u001b[0;32mwith\u001b[0m \u001b[0mopen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mCALCULATED_STATE_FILE\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"a\"\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m<ipython-input-13-848f55820469>\u001b[0m in \u001b[0;36mcalculate_mean\u001b[0;34m(folder)\u001b[0m\n\u001b[1;32m     28\u001b[0m             \u001b[0mdaf\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mload_single_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnew_path\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0mfolder\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0;34m'_tmp.csv'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     29\u001b[0m             \u001b[0mdaf\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconcat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mdaf\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mdf\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 30\u001b[0;31m           \u001b[0msave_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdaf\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mfolder\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0;34m'_tmp'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mnew_path\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     31\u001b[0m           \u001b[0;32mdel\u001b[0m \u001b[0mdaf\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     32\u001b[0m           \u001b[0;32mdel\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m<ipython-input-12-6345718427f3>\u001b[0m in \u001b[0;36msave_csv\u001b[0;34m(df, name, path)\u001b[0m\n\u001b[1;32m      5\u001b[0m     \u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmkdir\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpath\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Directory \"\u001b[0m \u001b[0;34m,\u001b[0m \u001b[0mpath\u001b[0m \u001b[0;34m,\u001b[0m  \u001b[0;34m\" Created \"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m   \u001b[0mdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpath\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0;34m'.csv'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      8\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      9\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0mread_log\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfile_path\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36mto_csv\u001b[0;34m(self, path_or_buf, sep, na_rep, float_format, columns, header, index, index_label, mode, encoding, compression, quoting, quotechar, line_terminator, chunksize, tupleize_cols, date_format, doublequote, escapechar, decimal)\u001b[0m\n\u001b[1;32m   3018\u001b[0m                                  \u001b[0mdoublequote\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdoublequote\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3019\u001b[0m                                  escapechar=escapechar, decimal=decimal)\n\u001b[0;32m-> 3020\u001b[0;31m         \u001b[0mformatter\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msave\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3021\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3022\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mpath_or_buf\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/io/formats/csvs.py\u001b[0m in \u001b[0;36msave\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    170\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwriter\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mUnicodeWriter\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mwriter_kwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    171\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 172\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_save\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    173\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    174\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/io/formats/csvs.py\u001b[0m in \u001b[0;36m_save\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    286\u001b[0m                 \u001b[0;32mbreak\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    287\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 288\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_save_chunk\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstart_i\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mend_i\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    289\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    290\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_save_chunk\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstart_i\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mend_i\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/io/formats/csvs.py\u001b[0m in \u001b[0;36m_save_chunk\u001b[0;34m(self, start_i, end_i)\u001b[0m\n\u001b[1;32m    300\u001b[0m                                   \u001b[0mdecimal\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdecimal\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    301\u001b[0m                                   \u001b[0mdate_format\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdate_format\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 302\u001b[0;31m                                   quoting=self.quoting)\n\u001b[0m\u001b[1;32m    303\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    304\u001b[0m             \u001b[0;32mfor\u001b[0m \u001b[0mcol_loc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcol\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mzip\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mb\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmgr_locs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0md\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/pandas/core/internals/blocks.py\u001b[0m in \u001b[0;36mto_native_types\u001b[0;34m(self, slicer, na_rep, float_format, decimal, quoting, **kwargs)\u001b[0m\n\u001b[1;32m   1995\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1996\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mquoting\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1997\u001b[0;31m                 \u001b[0mvalues\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mvalues\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1998\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1999\u001b[0m                 \u001b[0mvalues\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0marray\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'object'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ki_c7jSVXwQR",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wE2_YNZwKd6-",
        "colab_type": "text"
      },
      "source": [
        "### Make the Date column as index"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ql74WMbDKd6_",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "soybeans.Date = pd.to_datetime(soybeans.Date)\n",
        "soybeans.set_index('Date', inplace=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kmBG39ppNBX-",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "soybeans.head()\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0XvrvJnyNr6f",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "soybeans.tail()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4miq-XtJPkaK",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "plt.plot(soybeans.index, soybeans.Settle)\n",
        "plt.title('CBOT Soybean Futures',fontsize=27)\n",
        "plt.ylabel('Price (0.01 $USD)',fontsize=27)\n",
        "plt.gca().yaxis.set_major_formatter(mticker.FormatStrFormatter('%d'))\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "912kkLLYKd6s",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "usda = load_usda_data()\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "j6B_2OemKd6z",
        "colab_type": "text"
      },
      "source": [
        "## Filter soybeans by the year 2015:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Xt6tZLksKd60",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "mask = (soybeans['Date'] > '2015-01-01') & (soybeans['Date'] <= '2015-12-31')\n",
        "soybeans = soybeans.loc[mask]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-PKCfxWJKd65",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "mask = (soybeans['Date'] > '2014-01-01')\n",
        "soybeans = soybeans.loc[mask]"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sY6SKOQNKd7E",
        "colab_type": "text"
      },
      "source": [
        "## Filter weather by the most productive states    \n",
        "   "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yihyKy4eKd7G",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "weather = weather.query(\"state in ('IA','IL','MN','NE','IN','OH','SD','ND','MO','AR','KS','MS','MI','WI','KY','TN','LA','NC','PA','VA','MD','AL','GA','NY','OK','SC','DE','NJ','TX','WV','FL')\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oj-ug9YYKd7L",
        "colab_type": "text"
      },
      "source": [
        "## Plot map data\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MSuKJdfgWsNk",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "stations = pd.read_csv('US_stations.csv')\n",
        "#stations.set_index(['LATITUDE','LONGITUDE'], inplace=True)\n",
        "stations.index.names"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KgDkPiacW5_5",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "#stations.drop_duplicates(subset=['LATITUDE','LONGITUDE'])\n",
        "stations.plot(kind=\"scatter\", x=\"LONGITUDE\", y=\"LATITUDE\",fontsize=27,figsize=(20,15))\n",
        "plt.title(\"Meteorological stations in the USA's most soy producing regions\", fontsize=27)\n",
        "plt.gca().yaxis.set_major_locator(plt.NullLocator())\n",
        "plt.gca().xaxis.set_major_formatter(plt.NullFormatter())\n",
        "plt.axis('off')\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LYUwuHfUKd7N",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "weather.drop_duplicates(subset=['latitude','longitude']).plot(kind=\"scatter\", x=\"longitude\", y=\"latitude\",fontsize=27,figsize=(20,15))\n",
        "plt.title(\"Meteorological stations in the USA's most soy producing regions\", fontsize=27)\n",
        "plt.gca().yaxis.set_major_locator(plt.NullLocator())\n",
        "plt.gca().xaxis.set_major_formatter(plt.NullFormatter())\n",
        "plt.axis('off')\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yI3-AbZDKd7U",
        "colab_type": "text"
      },
      "source": [
        "# Group data by date (daily)\n",
        "\n",
        "Média das medidas horárias para o avgtemp, mintemp e maxtemp"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iK7RklvDKd7a",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "weather = weather.groupby(['date'], as_index=False)['date','mintemp','maxtemp','avgtemp'].mean()\n",
        "weather.head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qear_vgfKd7m",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "weather.date = pd.to_datetime(weather.date)\n",
        "weather.set_index('date', inplace=True)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wHFfruEaKd7u",
        "colab_type": "text"
      },
      "source": [
        "## Join datasets (soybeans + weather)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GEqxHWziKd7v",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dtMarlon = soybeans.join(weather)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NvIMaF0dKd7z",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dtMarlon.head()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "oVHZ51CFKd75",
        "colab_type": "text"
      },
      "source": [
        "## Histograms"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kbltcJzfKd76",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "soybeans.hist(bins=50, figsize=(20,15))\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Y_BNWzsfKd8A",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "weather.hist(bins=50, figsize=(20,15))\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OBszYOLyKd8F",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dtMarlon.hist(bins=50, figsize=(20,15))\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KN6ZEQUHKd8L",
        "colab_type": "text"
      },
      "source": [
        "## Time Series"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "weXdy0gsKd8O",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "plt.plot(soybeans.index, soybeans.Settle)\n",
        "plt.title('CBOT Soybean Futures',fontsize=27)\n",
        "plt.ylabel('Price (0.01 $USD)',fontsize=27)\n",
        "plt.gca().yaxis.set_major_formatter(mticker.FormatStrFormatter('%d'))\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "t77yf2ncKd8V",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "plt.plot(weather.index, weather.avgtemp)\n",
        "plt.title('2015 USA Weather Avg, Max, Min')\n",
        "plt.ylabel('Avg. Temp. (°F)');\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "sKbq9MlIKd8d",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "fig = plt.figure()\n",
        "ax1 = fig.add_subplot(111)\n",
        "ax1.plot(dtMarlon.index, dtMarlon.avgtemp, 'g-')\n",
        "ax1.set_ylabel('Avg. Temp. (°F)')\n",
        "\n",
        "ax2 = ax1.twinx()\n",
        "ax2.plot(dtMarlon.index, dtMarlon.Settle, 'b-')\n",
        "ax2.set_ylabel('Price per bushel (0.01 $USD)')\n",
        "ax2.yaxis.set_major_formatter(mticker.FormatStrFormatter('%0.01d'))\n",
        "plt.title('2015 USA Weather Avg and CBOT Soybean Futures')\n",
        "plt.show()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NZnNyt1KKd8l",
        "colab_type": "text"
      },
      "source": [
        "## Missing values for weather in days where we have soy quotes"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "W6vxccbhKd8n",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dtMarlon.query('avgtemp.isnull()')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xRamS8KsKd8t",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "weather.query(\"date=='2015-06-05' or date=='2015-06-04' or date=='2015-06-03' or date=='2015-06-02' or date=='2015-06-01'\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "x3emJIlJKd80",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "soybeans.query(\"Date=='2015-06-05' or Date=='2015-06-04' or Date=='2015-06-03' or Date=='2015-06-02' or Date=='2015-06-01'\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UctrZfABKd86",
        "colab_type": "text"
      },
      "source": [
        "## Filling missing values with method 'ffil'\n",
        "This propagate non-null values forward"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "38jON8BIKd88",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dtMarlon = dtMarlon.fillna(method='ffill')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "490UXoXEKd9B",
        "colab_type": "text"
      },
      "source": [
        "## Correlation"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3FQM1m-FKd9C",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dtMarlon.corr()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DyUCbGNpKd9G",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dtMarlon.diff().corr()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jQawXcJZKd9K",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "pd.plotting.autocorrelation_plot(dtMarlon)"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}