usda.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests, zipfile, io\n",
    "\n",
    "response = requests.get('https://apps.fas.usda.gov/psdonline/downloads/psd_coffee_csv.zip')\n",
    "zf = zipfile.ZipFile(io.BytesIO(response.content))\n",
    "usda = pd.read_csv(zf.open('psd_coffee.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Commodity_Code</th>\n",
       "      <th>Commodity_Description</th>\n",
       "      <th>Country_Code</th>\n",
       "      <th>Country_Name</th>\n",
       "      <th>Market_Year</th>\n",
       "      <th>Calendar_Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Attribute_ID</th>\n",
       "      <th>Attribute_Description</th>\n",
       "      <th>Unit_ID</th>\n",
       "      <th>Unit_Description</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>711100</td>\n",
       "      <td>Coffee, Green</td>\n",
       "      <td>AG</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>2003</td>\n",
       "      <td>2012</td>\n",
       "      <td>6</td>\n",
       "      <td>29</td>\n",
       "      <td>Arabica Production</td>\n",
       "      <td>2</td>\n",
       "      <td>(1000 60 KG BAGS)</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>711100</td>\n",
       "      <td>Coffee, Green</td>\n",
       "      <td>AG</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>2003</td>\n",
       "      <td>2012</td>\n",
       "      <td>6</td>\n",
       "      <td>90</td>\n",
       "      <td>Bean Exports</td>\n",
       "      <td>2</td>\n",
       "      <td>(1000 60 KG BAGS)</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>711100</td>\n",
       "      <td>Coffee, Green</td>\n",
       "      <td>AG</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>2003</td>\n",
       "      <td>2012</td>\n",
       "      <td>6</td>\n",
       "      <td>58</td>\n",
       "      <td>Bean Imports</td>\n",
       "      <td>2</td>\n",
       "      <td>(1000 60 KG BAGS)</td>\n",
       "      <td>1800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>711100</td>\n",
       "      <td>Coffee, Green</td>\n",
       "      <td>AG</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>2003</td>\n",
       "      <td>2012</td>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "      <td>Beginning Stocks</td>\n",
       "      <td>2</td>\n",
       "      <td>(1000 60 KG BAGS)</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>711100</td>\n",
       "      <td>Coffee, Green</td>\n",
       "      <td>AG</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>2003</td>\n",
       "      <td>2012</td>\n",
       "      <td>6</td>\n",
       "      <td>125</td>\n",
       "      <td>Domestic Consumption</td>\n",
       "      <td>2</td>\n",
       "      <td>(1000 60 KG BAGS)</td>\n",
       "      <td>1805.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Commodity_Code Commodity_Description Country_Code Country_Name  \\\n",
       "0          711100         Coffee, Green           AG      Algeria   \n",
       "1          711100         Coffee, Green           AG      Algeria   \n",
       "2          711100         Coffee, Green           AG      Algeria   \n",
       "3          711100         Coffee, Green           AG      Algeria   \n",
       "4          711100         Coffee, Green           AG      Algeria   \n",
       "\n",
       "   Market_Year  Calendar_Year  Month  Attribute_ID Attribute_Description  \\\n",
       "0         2003           2012      6            29    Arabica Production   \n",
       "1         2003           2012      6            90          Bean Exports   \n",
       "2         2003           2012      6            58          Bean Imports   \n",
       "3         2003           2012      6            20      Beginning Stocks   \n",
       "4         2003           2012      6           125  Domestic Consumption   \n",
       "\n",
       "   Unit_ID   Unit_Description   Value  \n",
       "0        2  (1000 60 KG BAGS)     0.0  \n",
       "1        2  (1000 60 KG BAGS)     0.0  \n",
       "2        2  (1000 60 KG BAGS)  1800.0  \n",
       "3        2  (1000 60 KG BAGS)     0.0  \n",
       "4        2  (1000 60 KG BAGS)  1805.0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "usda.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Market_Year</th>\n",
       "      <th>1961</th>\n",
       "      <th>1962</th>\n",
       "      <th>1963</th>\n",
       "      <th>1964</th>\n",
       "      <th>1965</th>\n",
       "      <th>1966</th>\n",
       "      <th>1967</th>\n",
       "      <th>1968</th>\n",
       "      <th>1969</th>\n",
       "      <th>1970</th>\n",
       "      <th>...</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Attribute_Description</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>Arabica Production</th>\n",
       "      <td>1500.0</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>1929.0</td>\n",
       "      <td>1712.0</td>\n",
       "      <td>1660.0</td>\n",
       "      <td>2095.0</td>\n",
       "      <td>1726.0</td>\n",
       "      <td>1792.0</td>\n",
       "      <td>1716.0</td>\n",
       "      <td>1941.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>3950.0</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>3500.0</td>\n",
       "      <td>3125.0</td>\n",
       "      <td>3125.0</td>\n",
       "      <td>3400.0</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>3700.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Market_Year              1961    1962    1963    1964    1965    1966    1967  \\\n",
       "Attribute_Description                                                           \n",
       "Arabica Production     1500.0  1700.0  1929.0  1712.0  1660.0  2095.0  1726.0   \n",
       "\n",
       "Market_Year              1968    1969    1970   ...      2010    2011    2012  \\\n",
       "Attribute_Description                           ...                             \n",
       "Arabica Production     1792.0  1716.0  1941.0   ...    4000.0  3950.0  4400.0   \n",
       "\n",
       "Market_Year              2013    2014    2015    2016    2017    2018    2019  \n",
       "Attribute_Description                                                          \n",
       "Arabica Production     4000.0  3500.0  3125.0  3125.0  3400.0  3600.0  3700.0  \n",
       "\n",
       "[1 rows x 59 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def country_query(country, year_type='Market_Year', attribute=''):\n",
    "    df = usda[usda['Country_Name'] == country]\n",
    "    if attribute:\n",
    "        df = df[df['Attribute_Description'] == attribute]\n",
    "    return df.pivot(\n",
    "        index='Attribute_Description', \n",
    "        columns=year_type, \n",
    "        values='Value'\n",
    "    )\n",
    "\n",
    "country_query('Guatemala', attribute='Arabica Production')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Market_Year</th>\n",
       "      <th>1961</th>\n",
       "      <th>1962</th>\n",
       "      <th>1963</th>\n",
       "      <th>1964</th>\n",
       "      <th>1965</th>\n",
       "      <th>1966</th>\n",
       "      <th>1967</th>\n",
       "      <th>1968</th>\n",
       "      <th>1969</th>\n",
       "      <th>1970</th>\n",
       "      <th>...</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>2019</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country_Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>Guatemala</th>\n",
       "      <td>1500.0</td>\n",
       "      <td>1700.0</td>\n",
       "      <td>1929.0</td>\n",
       "      <td>1712.0</td>\n",
       "      <td>1660.0</td>\n",
       "      <td>2095.0</td>\n",
       "      <td>1726.0</td>\n",
       "      <td>1792.0</td>\n",
       "      <td>1716.0</td>\n",
       "      <td>1941.0</td>\n",
       "      <td>...</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>3950.0</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>3500.0</td>\n",
       "      <td>3125.0</td>\n",
       "      <td>3125.0</td>\n",
       "      <td>3400.0</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>3700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Honduras</th>\n",
       "      <td>291.0</td>\n",
       "      <td>315.0</td>\n",
       "      <td>405.0</td>\n",
       "      <td>390.0</td>\n",
       "      <td>479.0</td>\n",
       "      <td>461.0</td>\n",
       "      <td>431.0</td>\n",
       "      <td>495.0</td>\n",
       "      <td>514.0</td>\n",
       "      <td>443.0</td>\n",
       "      <td>...</td>\n",
       "      <td>3550.0</td>\n",
       "      <td>3975.0</td>\n",
       "      <td>5600.0</td>\n",
       "      <td>4725.0</td>\n",
       "      <td>4400.0</td>\n",
       "      <td>5100.0</td>\n",
       "      <td>5300.0</td>\n",
       "      <td>7510.0</td>\n",
       "      <td>7600.0</td>\n",
       "      <td>7600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nicaragua</th>\n",
       "      <td>437.0</td>\n",
       "      <td>432.0</td>\n",
       "      <td>535.0</td>\n",
       "      <td>533.0</td>\n",
       "      <td>610.0</td>\n",
       "      <td>513.0</td>\n",
       "      <td>421.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>504.0</td>\n",
       "      <td>563.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1925.0</td>\n",
       "      <td>1740.0</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>1925.0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>2600.0</td>\n",
       "      <td>2700.0</td>\n",
       "      <td>2500.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Market_Year     1961    1962    1963    1964    1965    1966    1967    1968  \\\n",
       "Country_Name                                                                   \n",
       "Guatemala     1500.0  1700.0  1929.0  1712.0  1660.0  2095.0  1726.0  1792.0   \n",
       "Honduras       291.0   315.0   405.0   390.0   479.0   461.0   431.0   495.0   \n",
       "Nicaragua      437.0   432.0   535.0   533.0   610.0   513.0   421.0   556.0   \n",
       "\n",
       "Market_Year     1969    1970   ...      2010    2011    2012    2013    2014  \\\n",
       "Country_Name                   ...                                             \n",
       "Guatemala     1716.0  1941.0   ...    4000.0  3950.0  4400.0  4000.0  3500.0   \n",
       "Honduras       514.0   443.0   ...    3550.0  3975.0  5600.0  4725.0  4400.0   \n",
       "Nicaragua      504.0   563.0   ...    1925.0  1740.0  2100.0  1925.0  2000.0   \n",
       "\n",
       "Market_Year     2015    2016    2017    2018    2019  \n",
       "Country_Name                                          \n",
       "Guatemala     3125.0  3125.0  3400.0  3600.0  3700.0  \n",
       "Honduras      5100.0  5300.0  7510.0  7600.0  7600.0  \n",
       "Nicaragua     2100.0  2100.0  2600.0  2700.0  2500.0  \n",
       "\n",
       "[3 rows x 59 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def attribute_query(attribute, year_type='Market_Year', countries=[]):\n",
    "    df = usda[usda['Attribute_Description'] == attribute]\n",
    "    if countries:\n",
    "        df = df[df['Country_Name'].isin(countries)]\n",
    "    return df.pivot(\n",
    "        index='Country_Name', \n",
    "        columns=year_type, \n",
    "        values='Value'\n",
    "    )\n",
    "\n",
    "attribute_query('Arabica Production', countries=['Guatemala', 'Honduras', 'Nicaragua'])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}