<jupyter_start><jupyter_text>**United States State-Level Agricultural Data: Source and Preparation**<jupyter_code>from google.colab import files
import pandas as pd
import io
import csv
import numpy as np
import os<jupyter_output><empty_output><jupyter_text>The authority on United States agricultural data is the United State Department of Agriculture. It offers its data through many channels, and in many formats, but for downloading machine-readable state level data, as well as much else, its most conventient tool is 'Quick Stats' at the below link:https://quickstats.nass.usda.gov/ This document will describe the process for soybeans, but it is exactly the same for corn and wheat with the exception of the name entered in the drop down menu for 'Commodity', and the date selected for stocks: because crops have different seasons, a crop years is considered to start on 1st September for corn and soybeans and 1st June for wheat. Select the following options from the drop down menus in this order. Some menus will not appear until prior choices have been made.1. Program: Survey2. Sector: Crops3. Group: Field Crops4. Commodity: Soybeans5: Category: [Production, Yield, Stocks]6. Data Item: [Soybeans - Production, Measured in BU, Soybeans - Yield, Measured n BU / Acre, Soybeans - Stocks Measured in BU]7. Domain: Total8. Georgraphic Level: State9. State: _Select all_10. Year: _Select all years back to 1960_11. Period Type: [Annual, Point in Time]12. Period: [Year, First of Jun _for wheat_ / First of Sep _for corn and soybeans]The press the 'Get Data' button  the bottom of the page.A new page showing the data will appear. Press the option to download the data as a 'Spreadsheet' in the top right. This will in fact cause result in the download of a csv file.Save the downloaded file anywhere locally with any title, and then press the button below to locate and upload it:<jupyter_code>uploaded = files.upload()
source_file = list(uploaded.keys())[0]
print(source_file)
# Read and process the csv file dowloaded from Quick Stats

df = pd.read_csv(io.BytesIO(uploaded[source_file]))
df.head()<jupyter_output><empty_output><jupyter_text>Use the next button to locate and upload the 'political_entity.csv' file containing State names and their id codes.<jupyter_code>uploaded2 = files.upload()
print(uploaded2.keys())
# Read the political entity csv file into a datafrme

df2 = pd.read_csv(io.BytesIO(uploaded2['political_entity.csv']))
df2.head()
# Process the political entity data and create dictionary or names and pe_ids

peDict = {}

for i in range(1, df2.shape[0]):
  pe_name = df2['name'].iloc[i]
  pe_id = df2['id'].iloc[i]
  peDict[pe_name.upper()] = pe_id
# Dictionaries for translating the USDA's names into those used in the database

nameDict = {}
nameDict['SOYBEANS'] = 'Oilseed, Soybean'
nameDict['CORN'] = 'Corn'
nameDict['WHEAT'] = 'Wheat'
# Obtain the required information from the dataframe and write to a csv file for uploading to database

dataDict = {}
for i in range(1, len(df)):
  if df['State'].iloc[i] != 'OTHER STATES':
    inputName = df['Commodity'].iloc[i]
    commod = nameDict.get(inputName)
    stateStr = df['State'].iloc[i]
    pe_id = peDict[stateStr]
    year = df['Year'].iloc[i]
    a = 0
    if  'PRODUCTION, MEASURED IN BU' in df['Data Item'].iloc[i]:
      a = 1
    if  'YIELD, MEASURED IN BU / ACRE' in df['Data Item'].iloc[i]:
      a = 2
    if 'STOCKS, MEASURED IN BU' in df['Data Item'].iloc[i]:
      a = 3
    valueStr = df['Value'].iloc[i]
    valueStr = valueStr.replace(',', '')
    if valueStr ==  ' (D)':
      valueStr = 0
    p = 0
    y = 0
    s = 0
    value = float(valueStr)
    if a == 1:
      if commod == 'Corn':
        p = int((value / 39.37) / 1000)
      else:
        p = int((value / 36.74) / 1000)
    if a == 2:
      if commod == 'Corn':
        y = value * 2.47 / 39.37
      else:
        y = value * 2.47 / 36.74
    if a == 3:
      if commod == 'Corn':
        s = int((value / 39.37) / 1000)
      else:
        s = int((value / 36.74) / 1000)

    k = (pe_id * 10000) + year
    atts = [None, None, None, None]

    if commod in dataDict:
      if k in dataDict.get(commod):
        atts = dataDict.get(commod).get(k)
      if a == 1:
        atts[0] = p
      if a == 2:
        atts[1] = y
      if a == 3:
        atts[2] = s
      dataDict[commod][k] = atts
    else:
      if a == 1: atts[0] = p
      if a == 2: atts[1] = y
      if a == 3: atts[2] = s
      dataDict[commod] = {}
      dataDict[commod][k] = atts


# Sets ending stocks for each state equal to beginning stocks for the following year

for commodity in dataDict:
  for pe_idYear in dataDict.get(commodity):
    year = pe_idYear % 10000
    pe_id = int((pe_idYear - year) / 10000)
    if year != 2020:
      atts = dataDict.get(commodity).get(pe_idYear)
      nextK = pe_idYear + 1
      nextAtts = dataDict.get(commodity).get(nextK)
      if nextAtts:
        atts[3] = nextAtts[2]
# Puts the required data into database tuple format

output = []
txt = ""

for commodity in dataDict:
  for pe_idYear in dataDict.get(commodity):
    #print(pe_idYear, dataDict.get(commodity).get(pe_idYear))
    year = pe_idYear % 10000
    pe_id = int((pe_idYear - year) / 10000)
    atts = dataDict.get(commodity).get(pe_idYear)
    line = [commodity, pe_id, year, atts[0], atts[1], atts[2], atts[3]]
    txt += '"' + commodity + '"' + ',' + str(pe_id) + ',' + str(year) + ','
    if atts[0]:
      txt += str(atts[0]) + ','
    else:
      txt += 'NULL,'

    if atts[1]:
      txt +=  str(atts[1])[:4] + ','
    else:
      txt += 'NULL,'

    if atts[2]:
      txt += str(atts[2]) + ','
    else:
      txt += 'NULL,'

    if atts[3]:
      txt += str(atts[3])
    else:
      txt += 'NULL'
    txt += '\n'

f = open("ag data.csv", "w")
f.write(txt)
f.close()
files.download("ag data.csv")<jupyter_output><empty_output><jupyter_text>Upload the data from the csv file to the databse using instruction like as below, changing the path to the file according to the file's location.<jupyter_code>LOAD DATA LOCAL INFILE "~/ag data.csv"
INTO TABLE Commodity
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
(name, pe_id, year, production, yield, beginning_stocks, ending_stocks);<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11996191685179308, 'fraction_numerical': 0.023325928276737545, 'mean_word_length': 3.281929347826087, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '647583', 'license': 'MIT', 'n_tokens_mistral': 2272, 'n_tokens_neox': 2110, 'n_words': 739, 'path': 'notebooks/commodity_usda_state.ipynb', 'repo_name': 'iristyx/commodify', 'size': '6302'}