<jupyter_start><jupyter_text>Dataset Marlon Soybean, CBOT Soybean Futures + ( Global Historical Climatology Network (GHCN) filtered by USDA-NASS-soybeans-production_bushels-2015) Soybean, CBOT Soybean Futures- https://blog.quandl.com/api-for-commodity-data- http://www.quandl.com/api/v3/datasets/CHRIS/CME_S1/ Global Historical Climatology Network (GHCN)- https://www.ncdc.noaa.gov/data-access/land-based-station-data/land-based-datasets/global-historical-climatology-network-ghcn- FTP: ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/by_year/ USDA-NASS-soybeans-production_bushels-2015- https://usda-reports.nautilytics.com/?crop=soybeans&statistic=production_dollars&year=2007- https://www.nass.usda.gov/Data_Visualization/index.phphttps://github.com/aaronpenne/get_noaa_ghcn_data.git  Imports<jupyter_code>%matplotlib inline
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import pandas as pd
import numpy as np
import os
from six.moves import urllib
from ftplib import FTP
from io import StringIO
from IPython.display import clear_output
from functools import reduce
import tarfile
import subprocess
#subprocess.run(["ls", "-l"])
import zipfile
import shutil # move files
import psutil


# Load the Drive helper and mount
from google.colab import drive
drive.mount('/content/drive')<jupyter_output>Drive already mounted at /content/drive; to attempt to forcibly remount, call drive.mount("/content/drive", force_remount=True).<jupyter_text>Defines<jupyter_code>ROOT_PATH     = "drive/My Drive/TCC/"
DATASETS_PATH = ROOT_PATH + "datasets/"
SOYBEAN_PATH  = DATASETS_PATH + "CBOTSoybeanFutures/"
WEATHER_PATH  = DATASETS_PATH + "GHCN_Data/"
SOYBEAN_URL   = "http://www.quandl.com/api/v3/datasets/CHRIS/CME_S1/data.csv"
USDA_PATH     = "datasets/USDA-NASS-soybeans-production_bushels-2015/"

WEATHER_PATH_DRIVE_ZIP = WEATHER_PATH + "data/zip/"
WEATHER_PATH_DRIVE_CSV = WEATHER_PATH + "data/csv/"
FIXED_STATE_FILE       = WEATHER_PATH + "fixed_states.txt"
CALCULATED_STATE_FILE  = WEATHER_PATH + "calculated_states.txt"

DOWNLOADED_STATIONS_FILE      = WEATHER_PATH + "downloaded_stations.txt"
DOWNLOADED_STATIONS_FILE_TEMP = DOWNLOADED_STATIONS_FILE


plt.rcParams["figure.figsize"] = [19,15]
plt.rcParams.update({'font.size': 27})

# Create directories
# and initial files

if not os.path.exists(SOYBEAN_PATH):
  os.makedirs(SOYBEAN_PATH)

if not os.path.exists(WEATHER_PATH_DRIVE_ZIP):
  os.makedirs(WEATHER_PATH_DRIVE_ZIP)

if not os.path.exists(WEATHER_PATH_DRIVE_CSV):
  os.makedirs(WEATHER_PATH_DRIVE_CSV)

if not os.path.exists(DOWNLOADED_STATIONS_FILE):
  open(DOWNLOADED_STATIONS_FILE,'a').close()

if not os.path.exists(DOWNLOADED_STATIONS_FILE_TEMP):
  open(DOWNLOADED_STATIONS_FILE_TEMP,'a').close()

if not os.path.exists(FIXED_STATE_FILE):
  open(FIXED_STATE_FILE,'a').close()

if not os.path.exists(CALCULATED_STATE_FILE):
  open(CALCULATED_STATE_FILE,'a').close()<jupyter_output><empty_output><jupyter_text>https://github.com/aaronpenne/get_noaa_ghcn_data.git  https://github.com/aaronpenne/get_noaa_ghcn_data/blob/master/get_station_id.py<jupyter_code># -*- coding: utf-8 -*-
"""
Searches list of stations via user input to find the station ID.
Author: <NAME>
------------------------------
Variable   Columns   Type
------------------------------
ID            1-11   Character
LATITUDE     13-20   Real
LONGITUDE    22-30   Real
ELEVATION    32-37   Real
STATE        39-40   Character
NAME         42-71   Character
GSN FLAG     73-75   Character
HCN/CRN FLAG 77-79   Character
WMO ID       81-85   Character
------------------------------
"""

import sys
import pandas as pd
from ftplib import FTP
import os

output_dir = os.path.relpath('output')
if not os.path.isdir(output_dir):
    os.mkdir(output_dir)

ftp_path_dly = '/pub/data/ghcn/daily/'
ftp_path_dly_all = '/pub/data/ghcn/daily/all/'
ftp_filename = 'ghcnd-stations.txt'

def connect_to_ftp():
    ftp_path_root = 'ftp.ncdc.noaa.gov'

    # Access NOAA FTP server
    ftp = FTP(ftp_path_root)
    message = ftp.login()  # No credentials needed
    print(message)
    return ftp

def get_station_id(ftp, search_term):
    '''
    Get stations file
    '''
    ftp_full_path = os.path.join(ftp_path_dly, ftp_filename)
    local_full_path = os.path.join(output_dir, ftp_filename)
    if not os.path.isfile(local_full_path):
        with open(local_full_path, 'wb+') as f:
            ftp.retrbinary('RETR ' + ftp_full_path, f.write)

    '''
    Get user search term
    '''
    query = search_term
    query = query.upper()
    print("> Query: '"+query+"'")

    '''
    Read stations text file using fixed-width-file reader built into pandas
    '''
    # http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_fwf.html
    dtype = {'STATION_ID': str,
             'LATITUDE': str,
             'LONGITUDE': str,
             'ELEVATION': str,
             'STATE': str,
             'STATION_NAME': str,
             'GSN_FLAG': str,
             'HCN_CRN_FLAG': str,
             'WMO_ID': str}
    names = ['STATION_ID', 'LATITUDE', 'LONGITUDE', 'ELEVATION', 'STATE', 'STATION_NAME', 'GSN_FLAG', 'HCN_CRN_FLAG', 'WMO_ID']
    widths = [11,  # Station ID
              9,   # Latitude (decimal degrees)
              10,  # Longitude (decimal degrees)
              7,   # Elevation (meters)
              3,   # State (USA stations only)
              31,  # Station Name
              4,   # GSN Flag
              4,   # HCN/CRN Flag
              6]   # WMO ID
    df = pd.read_fwf(local_full_path, widths=widths, names=names, dtype=dtype, header=None)

    '''
    Replace missing values (nan, -999.9)
    '''
    df['STATE'] = df['STATE'].replace('nan', '--')
    df['GSN_FLAG'] = df['GSN_FLAG'].replace('nan', '---')
    df['HCN_CRN_FLAG'] = df['GSN_FLAG'].replace('nan', '---')
    df = df.replace(-999.9, float('nan'))

    try:
        '''
        Get query results, but only the columns we care about
        '''
        print('Searching records...')
        matches = df['STATION_ID'].str.contains(query)
        df = df.loc[matches, ['STATION_ID', 'LATITUDE', 'LONGITUDE', 'ELEVATION', 'STATE', 'STATION_NAME']]
        df.reset_index(drop=True, inplace=True)

        '''
        Get file sizes of each station's records to augment results
        '''
        #print('Getting file sizes...', end='')
        #print(df.index)
        #ftp.voidcmd('TYPE I')  # Needed to avoid FTP error with ftp.size()
        #count=0
        #last = ''
        #for i in list(df.index):
        #    count = count + 1
        #    print('.', end='')
        #    ftp_dly_file = ftp_path_dly + 'all/' + df.loc[i, 'STATION_ID'] + '.dly'
        #    #print(df.loc[i, 'STATION_ID'], end='')
        #    df.loc[i, 'SIZE'] = round(ftp.size(ftp_dly_file)/1000)  # Kilobytes
        #    #print('size: %d KB' %round(ftp.size(ftp_dly_file)/1000))
        #    actual = " %.1f%% " % ((count/df.index.size)*100)
        #    if (actual != last):
        #      clear_output()
        #      last = actual
        #      #print("%.2f%% " %((count/df.index.size)*100), end='')
        #      print('Getting file sizes...')
        #      print(str(actual) + ' ['+ str(count) + ' of ' + str(df.index.size) + ']')
           
        print()
        print()

        '''
        Sort by size then by rounded lat/long values to group geographic areas and show stations with most data
        '''
        df_sort = df.round(0)
        #df_sort.sort_values(['LATITUDE', 'LONGITUDE', 'SIZE'], ascending=False, inplace=True)
        df_sort.sort_values(['LATITUDE', 'LONGITUDE'], ascending=False, inplace=True)
        df = df.loc[df_sort.index]
        df.reset_index(drop=True, inplace=True)
        
    except:
        print('Station not found')
        traceback.print_exc(file=sys.stdout)
        ftp.quit()
        sys.exit()
    
    '''
    Print headers and values to facilitate reading
    '''
    #selection = 'Index'
    #station_id = 'Station_ID '
    #lat = 'Latitude'
    #lon = 'Longitude'
    #state = 'State'
    #name = 'Station_Name                '
    #size = ' File_Size'
    # Format output to be pretty, hopefully there is a prettier way to do this.
    #print('{: <6}{: <31}{: <6}({: >8},{: >10}){: >13}'.format(selection, name, state, lat, lon, size))
    #print('-'*5 + ' ' + '-'*30 + ' ' + '-'*5 + ' ' + '-'*21 + ' ' + '-'*12)
    #for i in list(df.index):
    #    print('{: 4}: {: <31}{: <6}({: >8},{: >10}){: >10} Kb'.format(i,
    #                                                                      df.loc[i,'STATION_NAME'],
    #                                                                      df.loc[i,'STATE'],
    #                                                                      df.loc[i,'LATITUDE'],
    #                                                                      df.loc[i,'LONGITUDE'],
    #                                                                      df.loc[i,'SIZE']))

#    '''
#    Get user selection
#    '''
#    try:
#        query = input('Enter selection (ex. 001, 42): ')
#        query = int(query)
#    except:
#        print('Please enter valid selection (ex. 001, 42)')
#        ftp.quit()
#        sys.exit()

    #station_id = df.loc[query, 'STATION_ID']
    station_id = df
    return station_id


def get_station(ftp=None, search_term='US'):
  close_after = False
  if ftp==None:
    ftp = connect_to_ftp()
    close_after = True
  
  station_id = get_station_id(ftp,search_term)
  #print(station_id)
  
  if close_after:
    ftp.quit()
  
  return (station_id)<jupyter_output><empty_output><jupyter_text>https://github.com/aaronpenne/get_noaa_ghcn_data/blob/master/get_dly.py<jupyter_code>"""
Grabs .dly file from the NOAA GHCN FTP server, parses, and reshapes to have one
day per row and element values in the columns. Writes output as CSV.
Author: <NAME>
.dly Format In (roughly):                     .csv Format Out (roughly):
-------------------------                     --------------------------
Month1  PRCP  Day1  Day2 ... Day31            Day1  PRCP  SNOW
Month1  SNOW  Day1  Day2 ... Day31            Day2  PRCP  SNOW
Month2  PRCP  Day1  Day2 ... Day31            Day3  PRCP  SNOW
Month2  SNOW  Day1  Day2 ... Day31            Day4  PRCP  SNOW
Starting with 5 core elements (per README)
    PRCP = Precipitation (tenths of mm)
    SNOW = Snowfall (mm)
    SNWD = Snow depth (mm)
    TMAX = Maximum temperature (tenths of degrees C)
    TMIN = Minimum temperature (tenths of degrees C)
ICD:
    ------------------------------
    Variable   Columns   Type
    ------------------------------
    ID            1-11   Character
    YEAR         12-15   Integer
    MONTH        16-17   Integer
    ELEMENT      18-21   Character
    VALUE1       22-26   Integer
    MFLAG1       27-27   Character
    QFLAG1       28-28   Character
    SFLAG1       29-29   Character
    VALUE2       30-34   Integer
    MFLAG2       35-35   Character
    QFLAG2       36-36   Character
    SFLAG2       37-37   Character
      .           .          .
      .           .          .
      .           .          .
    VALUE31    262-266   Integer
    MFLAG31    267-267   Character
    QFLAG31    268-268   Character
    SFLAG31    269-269   Character
    ------------------------------
"""

import pandas as pd
from ftplib import FTP
from io import StringIO
import os

ftp_path_dly_all = '/pub/data/ghcn/daily/all/'

def connect_to_ftp():
    """
    Get FTP server and file details
    """
    ftp_path_root = 'ftp.ncdc.noaa.gov'
    # Access NOAA FTP server
    ftp = FTP(ftp_path_root)
    message = ftp.login()  # No credentials needed
    #print(message)
    return ftp

# <NAME>
def disconnect_to_ftp(ftp_connection):
    return ftp_connection.quit()

def get_flags(s):
    """
    Get flags, replacing empty flags with '_' for clarity (' S ' becomes '_S_')
    """
    m_flag = s.read(1)
    m_flag = m_flag if m_flag.strip() else '_'
    q_flag = s.read(1)
    q_flag = q_flag if q_flag.strip() else '_'
    s_flag = s.read(1)
    s_flag = s_flag if s_flag.strip() else '_'
    return [m_flag + q_flag + s_flag]

def create_dataframe(element, dict_element):
    """
    Make dataframes out of the dicts, make the indices date strings (YYYY-MM-DD)
    """
    element = element.upper()
    df_element = pd.DataFrame(dict_element)
    # Add dates (YYYY-MM-DD) as index on df. Pad days with zeros to two places
    df_element.index = df_element['YEAR'] + '-' + df_element['MONTH'] + '-' + df_element['DAY'].str.zfill(2)
    df_element.index.name = 'DATE'
    # Arrange columns so ID, YEAR, MONTH, DAY are at front. Leaving them in for plotting later - https://stackoverflow.com/a/31396042
    for col in ['DAY', 'MONTH', 'YEAR', 'ID']:
        df_element = move_col_to_front(col, df_element)
    # Convert numerical values to float
    df_element.loc[:,element] = df_element.loc[:,element].astype(float)
    return df_element

def move_col_to_front(element, df):
    element = element.upper()
    cols = df.columns.tolist()
    cols.insert(0, cols.pop(cols.index(element)))
    df = df.reindex(columns=cols)
    return df

def dly_to_csv(ftp, station_id, output_dir, save_dly):
    #output_dir = os.path.relpath('output')
    if not os.path.isdir(output_dir):
        os.makedirs(output_dir)
    ftp_filename = station_id + '.dly'

    # Write .dly file to stream using StringIO using FTP command 'RETR'
    s = StringIO()
    ftp.retrlines('RETR ' + ftp_path_dly_all + ftp_filename, s.write)
    s.seek(0)

    # Write .dly file to dir to preserve original # FIXME make optional?
    if (save_dly):
      with open(os.path.join(output_dir, ftp_filename), 'wb+') as f:
        ftp.retrbinary('RETR ' + ftp_path_dly_all + ftp_filename, f.write)

    # Move to first char in file
    s.seek(0)

    # File params
    num_chars_line = 269
    num_chars_metadata = 21

    element_list = ['PRCP', 'SNOW', 'SNWD', 'TMAX', 'TMIN']

    '''
    Read through entire StringIO stream (the .dly file) and collect the data
    '''
    all_dicts = {}
    element_flag = {}
    prev_year = '0000'
    i = 0
    while True:
        i += 1

        '''
        Read metadata for each line (one month of data for a particular element per line)
        '''
        id_station = s.read(11)
        year = s.read(4)
        month = s.read(2)
        day = 0
        element = s.read(4)

        # If this is blank then we've reached EOF and should exit loop
        if not element:
            break

        '''
        Print status
        '''
        if year != prev_year:
            #print('Year {} | Line {}'.format(year, i))
            prev_year = year

        '''
        Loop through each day in rest of row, break if current position is end of row
        '''
        while s.tell() % num_chars_line != 0:
            day += 1
            # Fill in contents of each dict depending on element type in current row
            if day == 1:
                try:
                    first_hit = element_flag[element]
                except:
                    element_flag[element] = 1
                    all_dicts[element] = {}
                    all_dicts[element]['ID'] = []
                    all_dicts[element]['YEAR'] = []
                    all_dicts[element]['MONTH'] = []
                    all_dicts[element]['DAY'] = []
                    all_dicts[element][element.upper()] = []
                    all_dicts[element][element.upper() + '_FLAGS'] = []

            value = s.read(5)
            flags = get_flags(s)
            if value == '-9999':
                continue
            all_dicts[element]['ID'] += [station_id]
            all_dicts[element]['YEAR'] += [year]
            all_dicts[element]['MONTH'] += [month]
            all_dicts[element]['DAY'] += [str(day)]
            all_dicts[element][element.upper()] += [value]
            all_dicts[element][element.upper() + '_FLAGS'] += flags

    '''
    Create dataframes from dict
    '''
    all_dfs = {}
    for element in list(all_dicts.keys()):
        all_dfs[element] = create_dataframe(element, all_dicts[element])

    '''
    Combine all element dataframes into one dataframe, indexed on date.
    '''
    # pd.concat automagically aligns values to matching indices, therefore the data is date aligned!
    list_dfs = []
    for df in list(all_dfs.keys()):
        list_dfs += [all_dfs[df]]
    df_all = pd.concat(list_dfs, axis=1, sort=False)
    df_all.index.name = 'MM/DD/YYYY'

    '''
    Remove duplicated/broken columns and rows
    '''
    # https://stackoverflow.com/a/40435354
    df_all = df_all.loc[:,~df_all.columns.duplicated()]
    df_all = df_all.loc[df_all['ID'].notnull(), :]

    '''
    Output to CSV, convert everything to strings first
    '''
    # NOTE: To open the CSV in Excel, go through the CSV import wizard, otherwise it will come out broken
    df_out = df_all.astype(str)
    df_out.to_csv(os.path.join(output_dir, station_id + '.csv'))
    #print('\nOutput CSV saved to: {}'.format(os.path.join(output_dir, station_id + '.csv')))

def get_weather_data(ftp=None, station_id='USR0000CCHC',output_dir=WEATHER_PATH, save_dly=False):
    close_after = False
    if ftp==None:
      ftp = connect_to_ftp()
      close_after = True
    
    dly_to_csv(ftp, station_id,output_dir, save_dly)
    if close_after:
      ftp.quit()<jupyter_output><empty_output><jupyter_text>Fetch Data<jupyter_code>def fetch_soybean_data(soybean_url=SOYBEAN_URL, soybean_path=SOYBEAN_PATH):
    if not os.path.isdir(soybean_path):
        os.makedirs(soybean_path)
    csv_path = os.path.join(soybean_path, "soybeans.csv")
    urllib.request.urlretrieve(soybean_url, csv_path)

def fetch_weather_data(contains='US', weather_path=WEATHER_PATH_DRIVE_CSV, save_dly=False, how_much=100):
    conn = connect_to_ftp()
    weather = get_station(conn,search_term=contains) # List all stations from USA
    downloaded_stations = ""
    with open(DOWNLOADED_STATIONS_FILE_TEMP,"r+") as f:
      downloaded_stations = f.read()
    count = 0
    count2 = 0
    total = weather['STATION_ID'].size
    amount_of_data = total * how_much/100
    last = ''
    for station in weather['STATION_ID']:
      print('.',end='')
      count += 1
      actual = "%.2f%% " %((count/total)*100)
      actual_partial = "%.2f%% " %((count2/amount_of_data)*100)
      if (station+'.csv' not in downloaded_stations):
        if (count2 > amount_of_data):
          print('download completed: ['+str(count2)+' of '+str(amount_of_data)+'], total = '+str(total))
          return True
        count2 += 1
        print('get ', end='')
        get_weather_data(conn, station, weather_path, save_dly)
        print('done')
        downloaded_stations += station+'.csv\r\n'
        with open(DOWNLOADED_STATIONS_FILE_TEMP,"a+") as f:
          f.write(station+'.csv\r\n')
      else:
        print(',',end='')
      if (actual != last):
        clear_output()
        last = actual
        print('Getting '+str(how_much)+'% of weather data from GHCN ftp containing \''+contains+'\' in STATION_ID...')
        print('PARTIAL: '+str(actual_partial) + ' ['+ str(count2) + ' of ' + str(amount_of_data) + ']')
        print('TOTAL: '+str(actual) + ' ['+ str(count) + ' of ' + str(total) + ']')

    disconnect_to_ftp(conn)
    print('Final: download completed: ['+str(count2)+' of '+str(amount_of_data)+'], total = '+str(total))
    return True

# Update the local temp control file
#!echo "$DOWNLOADED_STATIONS_FILE" > "$DOWNLOADED_STATIONS_FILE_TEMP" .

#fetch_weather_data(how_much=0.54) #0.54% of total amount of data
fetch_weather_data()

# Update the control file
!echo "$DOWNLOADED_STATIONS_FILE_TEMP" > "$DOWNLOADED_STATIONS_FILE" .<jupyter_output><empty_output><jupyter_text>Check the number of downloaded station's csv file<jupyter_code>weather = get_station(search_term='US') # List all stations from USA
    
print("# of stations in GHCN FTP: ", end="")
print(str(weather['STATION_ID'].size))

print("# of downloaded csv files: ", end="")
!find "$WEATHER_PATH_DRIVE_CSV" -type f | wc -l

print("# of downloaded stations in control file: ", end="")
with open(DOWNLOADED_STATIONS_FILE) as f:
  num_lines = sum(1 for _ in f.readlines())
  print(str(num_lines))

def force_update_control_file():
  directory = os.path.join(WEATHER_PATH_DRIVE_CSV)
  with open(DOWNLOADED_STATIONS_FILE,"w") as f:
    for root,dirs,files in os.walk(directory):
      for file in files:
        print('.',end='')
        if file.endswith(".csv"):
          f.write(file+'\r\n')

force_update_control_file()<jupyter_output>........................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................[...]<jupyter_text>Get 'US' Stations<jupyter_code>newfile = ''
with open(PROJECT_PATH+'ghcnd-stations-us.txt', 'r') as f: 
  for line in f.readlines():
      line_list = line.split(' ')
      station = line_list[0]
      newfile += station
      for word in line_list:
        if (len(word) > 1):
          if (word[0].isalpha() and word!=station):
            state = word
            newfile += ','+state+'\n'
            break
            
print(newfile)

with open(PROJECT_PATH+'ghcnd-stations-us.csv', 'w+') as f: 
  f.write(newfile)<jupyter_output><empty_output><jupyter_text>Organize Stations by State<jupyter_code>def organize_stations_by_state():
  f1='' #stations_not_dowloaded
  csv_path = WEATHER_PATH_DRIVE_CSV
  with open(WEATHER_PATH+'ghcnd-stations-us.csv', 'r') as f:
    for line in f:
      station = line.split(',')[0]
      state = line.split(',')[1].rstrip()
      # Create target Directory if don't exist
      if not os.path.exists(csv_path+state):
        os.mkdir(csv_path+state)
        print("Directory " , csv_path+state ,  " Created ")
      #else:
      #	print("Directory " , "csv/"+state ,  " already exists")
      if not os.path.exists(csv_path+station+".csv"):
        print(".", end='')
        f1+=station+"\n"
      else:
        os.rename(csv_path+station+".csv", csv_path+state+"/"+station+".csv")
  with open(WEATHER_PATH+'stations_not_dowloaded.csv', 'w+') as f: 
    f.write(f1)
!ls drive/My\ Drive/TCC/
sLength = len(df1['TMAX'])
 df1['e'] = p.Series(np.random.randn(sLength), index=df1.index)<jupyter_output><empty_output><jupyter_text>Fix columns<jupyter_code>def fix_columns(df):
  for column in df:
    if column in ('ID','TMAX','TMIN','TAVG','PRCP'):
      pass
    else:
      #print(' deleting ',column, end='')
      del(df[column])
  if 'TMAX' not in df:
    #print(' creating TMAX... ', end='')
    #sLength = sizes['TMAX']
    df['TMAX'] = pd.Series(0, index=df.index)
  if 'TMIN' not in df:
    #print(' creating TMIN... ', end='')
    #sLength = sizes['TMIN']
    df['TMIN'] = pd.Series(0, index=df.index)
  if 'TAVG' not in df:
    #print(' creating TAVG... ', end='')
    #sLength = sizes['TAVG']
    df['TAVG'] = pd.Series(0, index=df.index)
  if 'PRCP' not in df:
    #print(' creating PRCP... ')
    #sLength = sizes['PRCP']
    df['PRCP'] = pd.Series(0, index=df.index)
  df=df.fillna(method='ffill')
  
df_ref = load_single_csv(CSV_PATH+'WA/USS0017B04S.csv')
sizes = {'TMAX':len(df_ref['TMAX']),'TMIN':len(df_ref['TMIN']),'TAVG':len(df_ref['TAVG']),'PRCP':len(df_ref['PRCP'])}
 
def fix_dataframes(folder=''):
  root_path = CSV_PATH+folder+'/'
  print(root_path)
  count=0
  count2=10
  total=0
  for root, dirs, files in os.walk(root_path):
    total=len(files)
    for file in files:
      if '.csv' in file:
        station=file.strip('.csv')
        #print(station)
        path = os.path.join(root, file)
        df = load_single_csv(path)
        fix_columns(df)
        new_path =  os.path.join(PROJECT_PATH+'new/'+folder+'/', file)
        # Create target Directory if don't exist
        if not os.path.exists(PROJECT_PATH+'new/'+folder+'/'):
          os.mkdir(PROJECT_PATH+'new/'+folder+'/')
          print("Directory " , PROJECT_PATH+'new/'+folder+'/' ,  " Created ")
        df.to_csv(new_path)
        if count2 == 70:
          count2=0
          actual = "%.2f%% " %((count/total)*100)
          clear_output()
          print('Fixing ',folder,' stations... ',actual,' (',str(count),' of ',str(total),')')
        count+=1
        count2+=1
  print('Done: %.2f%% ' %((count/total)*100))
  return True
fixed_states = ""
with open(FIXED_STATE_FILE, "r+") as f:
  fixed_states = f.read()

print('Already fixed:',fixed_states)  
  
for root, dirs, files in os.walk(CSV_PATH):
    total=len(dirs)
    for state in dirs:
      if (state not in fixed_states):
        if(fix_dataframes(state)):
          fixed_states+= state
          with open(FIXED_STATE_FILE,"a") as f:
            f.write(state+'\r\n')
fix_dataframes('CA')
df1 = load_single_csv('drive/My Drive/TCC/datasets/GHCN_Data/data/csv/TX/US1TXAC0002.csv')
df2 = load_single_csv('drive/My Drive/TCC/datasets/GHCN_Data/data/new/TX/US1TXAC0002.csv')
df1.tail()
df2.tail()<jupyter_output><empty_output><jupyter_text>Load Data<jupyter_code>def load_soybean_data(soybean_path=SOYBEAN_PATH):
    csv_path = os.path.join(soybean_path, "soybeans.csv")
    print(csv_path)
    return pd.read_csv(csv_path)

def load_single_csv(csv_path):
  #print(csv_path)
  df = pd.read_csv(csv_path,low_memory=False)
  df.set_index(['MM/DD/YYYY','YEAR','MONTH','DAY'], inplace=True)
  return df

def extract_zip(dir_name=WEATHER_PATH_DRIVE_ZIP,destination_dir=WEATHER_PATH_DRIVE_CSV):
  for item in os.listdir(dir_name): # loop through items in dir
    if item.endswith(".zip"): # check for ".zip" extension
        print("Extracting "+str(item), end="")
        #file_name = os.path.abspath(item) # get full path of files
        file_name = dir_name+item # get full path of files
        zip_ref = zipfile.ZipFile(file_name) # create zipfile object
        zip_ref.extractall(destination_dir) # extract file to dir
        zip_ref.close() # close file
        print("... OK!")
        #os.remove(file_name) # delete zipped file
  print("Extraction complete!")

def load_weather_data(weather_path=WEATHER_PATH_DRIVE_CSV,from_zip=False):
  if from_zip:
    extract_zip()
  data_frames=[]
  #first=True
  directory = os.path.join(weather_path,"")
  print(directory)
  for root,dirs,files in os.walk(directory):
    print(directory+".")
    for file in files:
      print(".")
      if file.endswith(".csv"):
        csv_path = os.path.join(weather_path, file)
        df = load_single_csv(csv_path)
        #Rename Columns
        #df=df.drop(columns=['ID'])
        #station = file.replace('.csv','')
        #for column in df.columns:
        #  if(column not in ['MM/DD/YYYY','YEAR','MONTH','DAY']):
        #    df.rename(columns={column: station +'-'+ column}, inplace=True)
            #print(station +'-'+ column)
        #Append to list
        data_frames.append(df)
        #if (first):
        #  data_frames = df
        #  first=False
        #else:
        #  data_frames = pd.merge(data_frames, df, on=['MM/DD/YYYY','YEAR','MONTH','DAY'], how='left')
  return data_frames
  #return pd.concat(data_frames, axis=1)
    
def load_usda_data(usda_path=USDA_PATH):
    csv_path = os.path.join(usda_path, "data.csv")
    print(csv_path)
    return pd.read_csv(csv_path, thousands=',')<jupyter_output><empty_output><jupyter_text>Calculate Mean and Standard Deviation for each state<jupyter_code>def save_csv(df,name,path):
  #print('Saving DataFrame in ',path)
  # Create target Directory if don't exist
  if not os.path.exists(path):
    os.mkdir(path)
    print("Directory " , path ,  " Created ")
  df.to_csv(path+name)
  
def read_log(file_path):
  files_processed = ""
  if not os.path.exists(file_path):
     with open(file_path,'w+') as f:
      files_processed = f.read()
  else :
    with open(file_path,'r+') as f:
      files_processed = f.read()
  return files_processed

def write_log(file_path,content):
  with open(file_path,'w+') as f:
    f.write(content)

def calculate(daf):
  print('TMAX',end='')
  daf['TMAX_mean'] = daf[[col for col in daf.columns if 'TMAX' in col ]].mean(1)
  print(' Ok mean')
  daf['TMAX_std'] = daf[[col for col in daf.columns if 'TMAX' in col ]].std(1)
  print(' Ok std')
  #daf = daf.drop(columns=['TMAX'])

  print(' OK\nTMIN', end='')
  daf['TMIN_mean'] = daf[[col for col in daf.columns if 'TMIN' in col ]].mean(1)
  print(' Ok mean')
  daf['TMIN_std'] = daf[[col for col in daf.columns if 'TMIN' in col ]].std(1)
  print(' Ok std')
  #daf = daf.drop(columns=['TMIN'])

  print(' OK\nTAVG', end='')
  daf['TAVG_mean'] = daf[[col for col in daf.columns if 'TAVG' in col ]].mean(1)
  print(' Ok mean')
  daf['TAVG_std'] = daf[[col for col in daf.columns if 'TAVG' in col ]].std(1)
  print(' Ok std')
  #daf = daf.drop(columns=['TAVG'])

  print(' OK\nPRCP', end='')
  daf['PRCP_mean'] = daf[[col for col in daf.columns if 'PRCP' in col ]].mean(1)
  print(' Ok mean')
  daf['PRCP_std'] = daf[[col for col in daf.columns if 'PRCP' in col ]].std(1)
  print(' Ok std')
  #daf = daf.drop(columns=['PRCP'])
  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']])
  print(' OK')
  daf=daf.fillna(0)
  return daf
def calculate_mean(folder=''):
  root_path = WEATHER_PATH_DRIVE_CSV+folder+'/'
  new_path =  os.path.join(WEATHER_PATH+'new/'+folder+'/','')
  file_path = new_path+folder+'.txt'
  if not os.path.exists(new_path):
    os.mkdir(new_path)
    print("Directory " , new_path ,  " Created ")
  files_processed = read_log(file_path)
  print(root_path)
  n=0
  count=0
  count2=70
  count_to_save=0
  count_to_reset=0
  total=0
  already_readed=False
  for root, dirs, files in os.walk(root_path):
    total=len(files)
    for file in files:
      if '.csv' in file:
        station=file.strip('.csv')
        if (station not in files_processed):
          path = os.path.join(root, file)
          df = load_single_csv(path)
          df = df.drop(columns=['ID'])
          if not already_readed:
            try:
              daf = load_single_csv(new_path+folder+'_tmp.csv')
            except:
              daf = df
            already_readed=True
          daf = pd.concat([daf,df], axis=1)
          if count_to_save == 100:
            count_to_save=0
            print('saving')
            save_csv(daf,folder+'_tmp',new_path)
            write_log(file_path,files_processed)
            print('saved')
          count_to_save+=1
          files_processed+=station+'\r\n'
          del df
        if count2 == 70:
          count2=0
          actual = "%.2f%% " %((count/total)*100)
          clear_output()
          process = psutil.Process(os.getpid())
          print('RAM usage: %.2f GB' %((process.memory_info().rss) / 1e9))
          print('Loading ',folder,' stations in DataFrames... ',actual,' (',str(count),' of ',str(total),')')
        count+=1
        count2+=1
  save_csv(daf,folder+'_tmp',new_path)
  write_log(file_path,files_processed)
  print('Load done: %.2f%% ' %((count/total)*100))
  if("Done" not in files_processed):
    daf = load_single_csv(new_path+folder+'_tmp.csv')
    daf = calculate(daf)
    new_file_name = state+str(n)+'.csv'
    print('Saving ', new_file_name)
    save_csv(daf,new_file_name,new_path)
    print('Done saving ',new_file_name)
    write_log(file_path,files_processed+"Done\r\n")
    print('Done!')
    os.remove(new_path+folder+'_tmp.csv')
  else :
    print('Already processed.')
  return True
  
calculate_mean('FL')

calculated_states = ""
with open(CALCULATED_STATE_FILE, "r+") as f:
  calculated_states = f.read()
print('Already calculated:\n[\n',calculated_states,']\n') 

for root, dirs, files in os.walk(WEATHER_PATH_DRIVE_CSV):
  total=len(dirs)
  for state in dirs:
    if (state not in calculated_states):
      if(calculate_mean(state)):
        calculated_states+= state
        with open(CALCULATED_STATE_FILE,"a") as f:
          f.write(state+'\r\n')<jupyter_output><empty_output><jupyter_text>Make the Date column as index<jupyter_code>soybeans.Date = pd.to_datetime(soybeans.Date)
soybeans.set_index('Date', inplace=True)
soybeans.head()

soybeans.tail()
plt.plot(soybeans.index, soybeans.Settle)
plt.title('CBOT Soybean Futures',fontsize=27)
plt.ylabel('Price (0.01 $USD)',fontsize=27)
plt.gca().yaxis.set_major_formatter(mticker.FormatStrFormatter('%d'))
plt.show()
usda = load_usda_data()<jupyter_output><empty_output><jupyter_text>Filter soybeans by the year 2015:<jupyter_code>mask = (soybeans['Date'] > '2015-01-01') & (soybeans['Date'] <= '2015-12-31')
soybeans = soybeans.loc[mask]
mask = (soybeans['Date'] > '2014-01-01')
soybeans = soybeans.loc[mask]<jupyter_output><empty_output><jupyter_text>Filter weather by the most productive states<jupyter_code>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')")<jupyter_output><empty_output><jupyter_text>Plot map data<jupyter_code>stations = pd.read_csv('US_stations.csv')
#stations.set_index(['LATITUDE','LONGITUDE'], inplace=True)
stations.index.names
#stations.drop_duplicates(subset=['LATITUDE','LONGITUDE'])
stations.plot(kind="scatter", x="LONGITUDE", y="LATITUDE",fontsize=27,figsize=(20,15))
plt.title("Meteorological stations in the USA's most soy producing regions", fontsize=27)
plt.gca().yaxis.set_major_locator(plt.NullLocator())
plt.gca().xaxis.set_major_formatter(plt.NullFormatter())
plt.axis('off')
plt.show()
weather.drop_duplicates(subset=['latitude','longitude']).plot(kind="scatter", x="longitude", y="latitude",fontsize=27,figsize=(20,15))
plt.title("Meteorological stations in the USA's most soy producing regions", fontsize=27)
plt.gca().yaxis.set_major_locator(plt.NullLocator())
plt.gca().xaxis.set_major_formatter(plt.NullFormatter())
plt.axis('off')
plt.show()<jupyter_output><empty_output><jupyter_text>Group data by date (daily)Média das medidas horárias para o avgtemp, mintemp e maxtemp<jupyter_code>weather = weather.groupby(['date'], as_index=False)['date','mintemp','maxtemp','avgtemp'].mean()
weather.head()
weather.date = pd.to_datetime(weather.date)
weather.set_index('date', inplace=True)<jupyter_output><empty_output><jupyter_text>Join datasets (soybeans + weather)<jupyter_code>dtMarlon = soybeans.join(weather)
dtMarlon.head()<jupyter_output><empty_output><jupyter_text>Histograms<jupyter_code>soybeans.hist(bins=50, figsize=(20,15))
plt.show()
weather.hist(bins=50, figsize=(20,15))
plt.show()
dtMarlon.hist(bins=50, figsize=(20,15))
plt.show()<jupyter_output><empty_output><jupyter_text>Time Series<jupyter_code>plt.plot(soybeans.index, soybeans.Settle)
plt.title('CBOT Soybean Futures',fontsize=27)
plt.ylabel('Price (0.01 $USD)',fontsize=27)
plt.gca().yaxis.set_major_formatter(mticker.FormatStrFormatter('%d'))
plt.show()
plt.plot(weather.index, weather.avgtemp)
plt.title('2015 USA Weather Avg, Max, Min')
plt.ylabel('Avg. Temp. (°F)');
plt.show()
fig = plt.figure()
ax1 = fig.add_subplot(111)
ax1.plot(dtMarlon.index, dtMarlon.avgtemp, 'g-')
ax1.set_ylabel('Avg. Temp. (°F)')

ax2 = ax1.twinx()
ax2.plot(dtMarlon.index, dtMarlon.Settle, 'b-')
ax2.set_ylabel('Price per bushel (0.01 $USD)')
ax2.yaxis.set_major_formatter(mticker.FormatStrFormatter('%0.01d'))
plt.title('2015 USA Weather Avg and CBOT Soybean Futures')
plt.show()<jupyter_output><empty_output><jupyter_text>Missing values for weather in days where we have soy quotes<jupyter_code>dtMarlon.query('avgtemp.isnull()')
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'")
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'")<jupyter_output><empty_output><jupyter_text>Filling missing values with method 'ffil'This propagate non-null values forward<jupyter_code>dtMarlon = dtMarlon.fillna(method='ffill')<jupyter_output><empty_output><jupyter_text>Correlation<jupyter_code>dtMarlon.corr()
dtMarlon.diff().corr()
pd.plotting.autocorrelation_plot(dtMarlon)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1765501935204482, 'fraction_numerical': 0.016645465125720627, 'mean_word_length': 2.7182248163429605, 'pattern_counts': {'":': 0, '<': 96, '<?xml version=': 0, '>': 103, 'https://': 10, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 25, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '668015', 'license': 'Apache-2.0', 'n_tokens_mistral': 13417, 'n_tokens_neox': 12775, 'n_words': 2563, 'path': 'Create_Dataset_MarlonFranco.ipynb', 'repo_name': 'marlonrcfranco/DatasetMarlon', 'size': '36964'}