{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# USDA Data Exploration\n", "USDA’s Foreign Agricultural Service data API portal provides users the ability to obtain programmatic access to publicly available agricultural commodity data from the Export Sales Report (ESR), Global Agricultural Trade System (GATS), and Production, Supply & Distribution (PSD) databases. This notebook explores ESR and PSD data. More information about these data sources [can be found here.](https://apps.fas.usda.gov/opendataweb/about)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "# general imports\n", "import cmath\n", "import numpy as np\n", "import pandas as pd\n", "import streamlit as st\n", "import requests\n", "import geopandas as gpd\n", "from datetime import date" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## USDA Export Sales Report (ESR) Data\n", "USDA's Export Sales Reporting Program monitors U.S. agricultural export sales on a daily and weekly basis. Export sales reporting provides a constant stream of up-to-date market information for 40 U.S. agricultural commodities sold abroad.\n", "\n", "A single statistic reveals the significance of the program: in a typical year, the program monitors more than 40 percent of total U.S. agricultural exports. The program also serves as an early alert on the possible impact foreign sales have on U.S. supplies and prices.\n", "\n", "The weekly U.S. Export Sales report is the most currently available source of U.S. export sales data. The data is used to analyze the overall level of export demand, determine where markets exist, and assess the relative position of U.S. commodities in foreign markets." ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "from requests.auth import HTTPBasicAuth\n", "# function that gets data from USDA endpoint and converts response to pandas dataframe\n", "def get_usda_data(endpoint_url):\n", " # USDA API Key\n", " key = {\"API_KEY\": \"aefd68b9-cfdc-4c9e-a800-b457ff5adade\"}\n", " request = requests.get(url=endpoint_url, headers=key)\n", " # convert response to pandas dataframe\n", " return (pd.read_json(request.text))\n" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "# Basic endpoints. See below for endpoints that return data commodity and country specific data\n", "regions_url = \"https://apps.fas.usda.gov/OpenData/api/esr/regions\"\n", "country_url = \"https://apps.fas.usda.gov/OpenData/api/esr/countries\"\n", "data_release_url = \"https://apps.fas.usda.gov/OpenData/api/esr/datareleasedates\"\n", "commodity_url = \"https://apps.fas.usda.gov/OpenData/api/esr/commodities\"\n", "units_url = \"https://apps.fas.usda.gov/OpenData/api/esr/unitsOfMeasure\"\n" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
regionIdregionName
01EUROPEAN UNION - 27
12OTHER EUROPE
23EASTERN EUROPE
34FORMER SOVIET UNION-12
45JAPAN
56TAIWAN
67CHINA
78INDIA
89OTHER ASIA AND OCEANIA
910AFRICA
1011WESTERN HEMISPHERE
1199UNKNOWN
\n", "
" ], "text/plain": [ " regionId regionName\n", "0 1 EUROPEAN UNION - 27 \n", "1 2 OTHER EUROPE \n", "2 3 EASTERN EUROPE \n", "3 4 FORMER SOVIET UNION-12 \n", "4 5 JAPAN \n", "5 6 TAIWAN \n", "6 7 CHINA \n", "7 8 INDIA \n", "8 9 OTHER ASIA AND OCEANIA \n", "9 10 AFRICA \n", "10 11 WESTERN HEMISPHERE \n", "11 99 UNKNOWN " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Regions with Region ID's\n", "get_usda_data(regions_url)" ] }, { "cell_type": "code", "execution_count": 60, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
countryCodecountryNamecountryDescriptionregionIdgencCode
01EUROPEANEUROPEAN UNION - 271None
12UNKNOWNUNKNOWN99AX1
21010GREENLDGREENLAND11GRL
31220CANADACANADA11CAN
41610MIGUELST. PIERRE AND MIQUELON11None
..................
2059350GUAMGUAM9GUM
2069410CAN EN ICANTON AND ENDERBURY ISLANDS9None
2079510AM SAMOAAMERICAN SAMOA9ASM
2089610NMARIANANORTHERN MARIANA ISLANDS9MNP
2099800US O ISUS MINOR OUTLYING ISLAND9None
\n", "

210 rows × 5 columns

\n", "
" ], "text/plain": [ " countryCode countryName countryDescription regionId \\\n", "0 1 EUROPEAN EUROPEAN UNION - 27 1 \n", "1 2 UNKNOWN UNKNOWN 99 \n", "2 1010 GREENLD GREENLAND 11 \n", "3 1220 CANADA CANADA 11 \n", "4 1610 MIGUEL ST. PIERRE AND MIQUELON 11 \n", ".. ... ... ... ... \n", "205 9350 GUAM GUAM 9 \n", "206 9410 CAN EN I CANTON AND ENDERBURY ISLANDS 9 \n", "207 9510 AM SAMOA AMERICAN SAMOA 9 \n", "208 9610 NMARIANA NORTHERN MARIANA ISLANDS 9 \n", "209 9800 US O IS US MINOR OUTLYING ISLAND 9 \n", "\n", " gencCode \n", "0 None \n", "1 AX1 \n", "2 GRL \n", "3 CAN \n", "4 None \n", ".. ... \n", "205 GUM \n", "206 None \n", "207 ASM \n", "208 MNP \n", "209 None \n", "\n", "[210 rows x 5 columns]" ] }, "execution_count": 60, "metadata": {}, "output_type": "execute_result" } ], "source": [ "get_usda_data(country_url)" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
commodityCodecommodityNameunitId
0101Wheat - HRW1
1102Wheat - SRW1
2103Wheat - HRS1
3104Wheat - White1
4105Wheat - Durum1
5106Wheat - Mixed1
6107All Wheat1
7201Wheat Products1
8301Barley1
9401Corn1
10501Rye1
11601Oats1
12701Sorghum1
13801Soybeans1
14901Soybean cake & meal1
15902Soybean Oil1
161001Flaxseed1
171101Linseed Oil1
181110Sunflowerseed Oil1
191201Cottonseed1
201202Cottonseed cake & meal1
211203Cottonseed Oil1
221301Cotton- Am Pima2
231401Cotton- Upland 1 1/16\" & over2
241402Cotton- Upland 1\"-1 1/16\" & over2
251403Cotton- Upland under 1\"2
261404All Upland Cotton2
271498Rice - LG Rough1
281499Rice- Med, Short,Other Rough1
291501Rice- LG Brown1
301502Rice- Med,Short, Other Brown1
311503Rice - Long Grain, Milled1
321504Rice- Med,Short,Other Milled1
331505All Rice1
341601Cattle Hides - Whole - Excluding Wet Blues3
351602Calf Skins - Whole - Excluding Wet Blues3
361603Kip Skins - Whole - Excluding Wet Blues3
371604Cattle Hides-Cut into Croupons, etc-excl Wet B...4
381605Cattle Hides and Skins-other-excluding Wet Blues5
391606Cattle Wet Blues-Unsplit (Whole or Sided)4
401607Cattle Wet Blues-Grain Splits (Whole or Sided)4
411608Cattle Wet Blues-Splits-Excluding Grain Splits5
421701Fresh, Chilled, or Frozen Muscle Cuts of Beef1
431702Fresh, Chilled, or Frozen Muscle Cuts of Pork1
\n", "
" ], "text/plain": [ " commodityCode commodityName unitId\n", "0 101 Wheat - HRW 1\n", "1 102 Wheat - SRW 1\n", "2 103 Wheat - HRS 1\n", "3 104 Wheat - White 1\n", "4 105 Wheat - Durum 1\n", "5 106 Wheat - Mixed 1\n", "6 107 All Wheat 1\n", "7 201 Wheat Products 1\n", "8 301 Barley 1\n", "9 401 Corn 1\n", "10 501 Rye 1\n", "11 601 Oats 1\n", "12 701 Sorghum 1\n", "13 801 Soybeans 1\n", "14 901 Soybean cake & meal 1\n", "15 902 Soybean Oil 1\n", "16 1001 Flaxseed 1\n", "17 1101 Linseed Oil 1\n", "18 1110 Sunflowerseed Oil 1\n", "19 1201 Cottonseed 1\n", "20 1202 Cottonseed cake & meal 1\n", "21 1203 Cottonseed Oil 1\n", "22 1301 Cotton- Am Pima 2\n", "23 1401 Cotton- Upland 1 1/16\" & over 2\n", "24 1402 Cotton- Upland 1\"-1 1/16\" & over 2\n", "25 1403 Cotton- Upland under 1\" 2\n", "26 1404 All Upland Cotton 2\n", "27 1498 Rice - LG Rough 1\n", "28 1499 Rice- Med, Short,Other Rough 1\n", "29 1501 Rice- LG Brown 1\n", "30 1502 Rice- Med,Short, Other Brown 1\n", "31 1503 Rice - Long Grain, Milled 1\n", "32 1504 Rice- Med,Short,Other Milled 1\n", "33 1505 All Rice 1\n", "34 1601 Cattle Hides - Whole - Excluding Wet Blues 3\n", "35 1602 Calf Skins - Whole - Excluding Wet Blues 3\n", "36 1603 Kip Skins - Whole - Excluding Wet Blues 3\n", "37 1604 Cattle Hides-Cut into Croupons, etc-excl Wet B... 4\n", "38 1605 Cattle Hides and Skins-other-excluding Wet Blues 5\n", "39 1606 Cattle Wet Blues-Unsplit (Whole or Sided) 4\n", "40 1607 Cattle Wet Blues-Grain Splits (Whole or Sided) 4\n", "41 1608 Cattle Wet Blues-Splits-Excluding Grain Splits 5\n", "42 1701 Fresh, Chilled, or Frozen Muscle Cuts of Beef 1\n", "43 1702 Fresh, Chilled, or Frozen Muscle Cuts of Pork 1" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "get_usda_data(commodity_url)" ] }, { "cell_type": "code", "execution_count": 75, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
commodityCodemarketYearStartmarketYearEndmarketYearreleaseTimeStamp
01012022-06-01T00:00:002023-05-31T00:00:0020232022-07-28T00:00:00
11022022-06-01T00:00:002023-05-31T00:00:0020232022-07-28T00:00:00
21032022-06-01T00:00:002023-05-31T00:00:0020232022-07-28T00:00:00
31042022-06-01T00:00:002023-05-31T00:00:0020232022-07-28T00:00:00
41052022-06-01T00:00:002023-05-31T00:00:0020232022-07-28T00:00:00
..................
99916021998-01-01T00:00:001998-12-31T00:00:0019981999-01-07T00:00:00
100016031998-01-01T00:00:001998-12-31T00:00:0019981999-01-07T00:00:00
100116061998-01-01T00:00:001998-12-31T00:00:0019981999-01-07T00:00:00
100216071998-01-01T00:00:001998-12-31T00:00:0019981999-01-07T00:00:00
100316081998-01-01T00:00:001998-12-31T00:00:0019981999-01-07T00:00:00
\n", "

1004 rows × 5 columns

\n", "
" ], "text/plain": [ " commodityCode marketYearStart marketYearEnd marketYear \\\n", "0 101 2022-06-01T00:00:00 2023-05-31T00:00:00 2023 \n", "1 102 2022-06-01T00:00:00 2023-05-31T00:00:00 2023 \n", "2 103 2022-06-01T00:00:00 2023-05-31T00:00:00 2023 \n", "3 104 2022-06-01T00:00:00 2023-05-31T00:00:00 2023 \n", "4 105 2022-06-01T00:00:00 2023-05-31T00:00:00 2023 \n", "... ... ... ... ... \n", "999 1602 1998-01-01T00:00:00 1998-12-31T00:00:00 1998 \n", "1000 1603 1998-01-01T00:00:00 1998-12-31T00:00:00 1998 \n", "1001 1606 1998-01-01T00:00:00 1998-12-31T00:00:00 1998 \n", "1002 1607 1998-01-01T00:00:00 1998-12-31T00:00:00 1998 \n", "1003 1608 1998-01-01T00:00:00 1998-12-31T00:00:00 1998 \n", "\n", " releaseTimeStamp \n", "0 2022-07-28T00:00:00 \n", "1 2022-07-28T00:00:00 \n", "2 2022-07-28T00:00:00 \n", "3 2022-07-28T00:00:00 \n", "4 2022-07-28T00:00:00 \n", "... ... \n", "999 1999-01-07T00:00:00 \n", "1000 1999-01-07T00:00:00 \n", "1001 1999-01-07T00:00:00 \n", "1002 1999-01-07T00:00:00 \n", "1003 1999-01-07T00:00:00 \n", "\n", "[1004 rows x 5 columns]" ] }, "execution_count": 75, "metadata": {}, "output_type": "execute_result" } ], "source": [ "get_usda_data(data_release_url)" ] }, { "cell_type": "code", "execution_count": 63, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
unitIdunitNames
01Metric Tons
12Running Bales
23Pieces
34NUMBER
45Pounds
\n", "
" ], "text/plain": [ " unitId unitNames\n", "0 1 Metric Tons\n", "1 2 Running Bales\n", "2 3 Pieces\n", "3 4 NUMBER\n", "4 5 Pounds" ] }, "execution_count": 63, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# Units of commodities referenced in chart above.\n", "get_usda_data(units_url)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Given Commodity Code (Ex: 107 for \"All Wheat\") and MarketYear (Ex: 2021) this API End point will return a list of US Export records of White Wheat to all applicable countries from USA for the given Market Year. See DataReleaseDates end point to get a list of all Commodities and the corresponding Market Year data." ] }, { "cell_type": "code", "execution_count": 70, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
commodityCodecountryCodeweeklyExportsaccumulatedExportsoutstandingSalesgrossNewSalescurrentMYNetSalescurrentMYTotalCommitmentnextMYOutstandingSalesnextMYNetSalesunitIdweekEndingDate
010712200092669266092660012020-06-04T00:00:00
110720101350613506352846224335253763663520012020-06-04T00:00:00
2107205000959999599995999959990012020-06-04T00:00:00
310720800040000040000012020-06-04T00:00:00
41072110001000000100000012020-06-04T00:00:00
\n", "
" ], "text/plain": [ " commodityCode countryCode weeklyExports accumulatedExports \\\n", "0 107 1220 0 0 \n", "1 107 2010 13506 13506 \n", "2 107 2050 0 0 \n", "3 107 2080 0 0 \n", "4 107 2110 0 0 \n", "\n", " outstandingSales grossNewSales currentMYNetSales \\\n", "0 9266 9266 0 \n", "1 352846 224335 25376 \n", "2 95999 95999 95999 \n", "3 4000 0 0 \n", "4 10000 0 0 \n", "\n", " currentMYTotalCommitment nextMYOutstandingSales nextMYNetSales unitId \\\n", "0 9266 0 0 1 \n", "1 366352 0 0 1 \n", "2 95999 0 0 1 \n", "3 4000 0 0 1 \n", "4 10000 0 0 1 \n", "\n", " weekEndingDate \n", "0 2020-06-04T00:00:00 \n", "1 2020-06-04T00:00:00 \n", "2 2020-06-04T00:00:00 \n", "3 2020-06-04T00:00:00 \n", "4 2020-06-04T00:00:00 " ] }, "execution_count": 70, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# return first 5 rows for \"All Wheat\" in 2021\n", "wheat_url = \"https://apps.fas.usda.gov/OpenData/api/esr/exports/commodityCode/107/allCountries/marketYear/2021\"\n", "get_usda_data(wheat_url).head(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Given Commodity Code (Ex: 107 for \"All Wheat\"), Country Code (Ex:1220 for Canada) and MarketYear (Ex: 2021) this API End point will return a list of US Export records of White Wheat to Canada from USA for the give Market Year. Please see DataReleaseDates end point to get a list of all Commodities and the corresponding Market Year data." ] }, { "cell_type": "code", "execution_count": 73, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
commodityCodecountryCodeweeklyExportsaccumulatedExportsoutstandingSalesgrossNewSalescurrentMYNetSalescurrentMYTotalCommitmentnextMYOutstandingSalesnextMYNetSalesunitIdweekEndingDate
010712200092669266092660012020-06-04T00:00:00
110720101350613506352846224335253763663520012020-06-04T00:00:00
2107205000959999599995999959990012020-06-04T00:00:00
310720800040000040000012020-06-04T00:00:00
41072110001000000100000012020-06-04T00:00:00
\n", "
" ], "text/plain": [ " commodityCode countryCode weeklyExports accumulatedExports \\\n", "0 107 1220 0 0 \n", "1 107 2010 13506 13506 \n", "2 107 2050 0 0 \n", "3 107 2080 0 0 \n", "4 107 2110 0 0 \n", "\n", " outstandingSales grossNewSales currentMYNetSales \\\n", "0 9266 9266 0 \n", "1 352846 224335 25376 \n", "2 95999 95999 95999 \n", "3 4000 0 0 \n", "4 10000 0 0 \n", "\n", " currentMYTotalCommitment nextMYOutstandingSales nextMYNetSales unitId \\\n", "0 9266 0 0 1 \n", "1 366352 0 0 1 \n", "2 95999 0 0 1 \n", "3 4000 0 0 1 \n", "4 10000 0 0 1 \n", "\n", " weekEndingDate \n", "0 2020-06-04T00:00:00 \n", "1 2020-06-04T00:00:00 \n", "2 2020-06-04T00:00:00 \n", "3 2020-06-04T00:00:00 \n", "4 2020-06-04T00:00:00 " ] }, "execution_count": 73, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# return first 5 rows for \"All Wheat\" in 2021\n", "canada_wheat_url = \"https://apps.fas.usda.gov/OpenData/api/esr/exports/commodityCode/107/countryCode/2021/marketYear/1220\"\n", "get_usda_data(wheat_url).head(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Production, Supply & Distribution (PSD) Data\n", "PSD Online is the public repository for USDA’s Official Production, Supply and Distribution forecast data, reports and circulars for key agricultural commodities.\n", "\n", "FAS's PSD Online data are reviewed and updated monthly by an interagency committee chaired by USDA's World Agricultural Outlook Board (WAOB),and consisting of: the Foreign Agricultural Service (FAS), the Economic Research Service (ERS),the Farm Service Agency (FSA), and the Agricultural Marketing Service (AMS).\n", "\n", "The international portion of the data is updated with input from agricultural attachés stationed at U.S. embassies around the world, FAS commodity analysts, and country and commodity analysts with ERS. The U.S. domestic component is updated with input from analysts in FAS, ERS, the National Agricultural Statistical Service, and FSA.\n", "\n", "*Note, country and commodity codes are differen that ESR data*" ] }, { "cell_type": "code", "execution_count": 105, "metadata": {}, "outputs": [], "source": [ "psd_com_attributes_url = \"https://apps.fas.usda.gov/OpenData/api/psd/commodityAttributes\"\n", "psd_units_url = \"https://apps.fas.usda.gov/OpenData/api/psd/unitsOfMeasure\"\n", "psd_com_url = \"https://apps.fas.usda.gov/OpenData/api/psd/commodities\"\n", "psd_countries_url = \"https://apps.fas.usda.gov/OpenData/api/psd/countries\"\n", "psd_regions_url = \"https://apps.fas.usda.gov/OpenData/api/psd/regions\"\n" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "ename": "NameError", "evalue": "name 'psd_com_attributes_url' is not defined", "output_type": "error", "traceback": [ "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[0;31mNameError\u001b[0m Traceback (most recent call last)", "\u001b[1;32m/Users/cameronkruse/development/github/food-security/usda-data-exploration.ipynb Cell 17\u001b[0m in \u001b[0;36m\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0m get_usda_data(psd_com_attributes_url)\n", "\u001b[0;31mNameError\u001b[0m: name 'psd_com_attributes_url' is not defined" ] } ], "source": [ "get_usda_data(psd_com_attributes_url)" ] }, { "cell_type": "code", "execution_count": 102, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
unitIdunitDescription
01(1000 BUSHES)
12(1000 60 KG BAGS)
23(1000 COLONIES)
34(1000 HA)
45(1000 HEAD)
56(1000 HL)
67(1000 MT CWE)
78(1000 MT)
89(1000 PCS)
910(1000 TREES)
1011(Dec. Fraction)
1112(HA)
1213(HECTARES)
1314(KG)
1415(MIL HEAD)
1516(MIL PCS)
1617(MILLION TREES)
1718(MT RAW EQ)
1819(MT RAW EW)
1920(MT RE)
2021(MT)
2122(MT, Net Weight)
2223(PERCENT)
2324(RATIO)
2425(1000 CUBIC METERS)
2526(MT/HA)
26271000 480 lb. Bales
2728(Bales/HA)
2829(KG/HA)
2930ACRES
3031BUSHELS
3132HUNDREDWEIGHT
3233MILLING RATE
3334BUSHELS/TON
3435IMPORT MILLING RATE
3536Bushels
3637SHORT TONS
3738MILLION LBS
3839BILLION LBS
3940(HEAD)
4041(PEOPLE)
\n", "
" ], "text/plain": [ " unitId unitDescription\n", "0 1 (1000 BUSHES) \n", "1 2 (1000 60 KG BAGS) \n", "2 3 (1000 COLONIES) \n", "3 4 (1000 HA) \n", "4 5 (1000 HEAD) \n", "5 6 (1000 HL) \n", "6 7 (1000 MT CWE) \n", "7 8 (1000 MT) \n", "8 9 (1000 PCS) \n", "9 10 (1000 TREES) \n", "10 11 (Dec. Fraction) \n", "11 12 (HA) \n", "12 13 (HECTARES) \n", "13 14 (KG) \n", "14 15 (MIL HEAD) \n", "15 16 (MIL PCS) \n", "16 17 (MILLION TREES) \n", "17 18 (MT RAW EQ) \n", "18 19 (MT RAW EW) \n", "19 20 (MT RE) \n", "20 21 (MT) \n", "21 22 (MT, Net Weight) \n", "22 23 (PERCENT) \n", "23 24 (RATIO) \n", "24 25 (1000 CUBIC METERS) \n", "25 26 (MT/HA) \n", "26 27 1000 480 lb. Bales \n", "27 28 (Bales/HA) \n", "28 29 (KG/HA) \n", "29 30 ACRES \n", "30 31 BUSHELS \n", "31 32 HUNDREDWEIGHT \n", "32 33 MILLING RATE \n", "33 34 BUSHELS/TON \n", "34 35 IMPORT MILLING RATE \n", "35 36 Bushels \n", "36 37 SHORT TONS \n", "37 38 MILLION LBS \n", "38 39 BILLION LBS \n", "39 40 (HEAD) \n", "40 41 (PEOPLE) " ] }, "execution_count": 102, "metadata": {}, "output_type": "execute_result" } ], "source": [ "get_usda_data(psd_units_url)" ] }, { "cell_type": "code", "execution_count": 104, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
commodityCodecommodityName
0577400Almonds, Shelled Basis
111000Animal Numbers, Cattle
213000Animal Numbers, Swine
3574000Apples, Fresh
4430000Barley
5579305Cherries (Sweet&Sour), Fresh
6711100Coffee, Green
7440000Corn
82631000Cotton
9230000Dairy, Butter
10240000Dairy, Cheese
11224400Dairy, Dry Whole Milk Powder
12223000Dairy, Milk, Fluid
13224200Dairy, Milk, Nonfat Dry
14572220Grapefruit, Fresh
15575100Grapes, Fresh Table
16572120Lemons/Limes, Fresh
17813700Meal, Copra
18813300Meal, Cottonseed
19814200Meal, Fish
20813800Meal, Palm Kernel
21813200Meal, Peanut
22813600Meal, Rapeseed
23813100Meal, Soybean
24813101Meal, Soybean (Local)
25813500Meal, Sunflowerseed
26111000Meat, Beef and Veal
27115000Meat, Chicken
28113000Meat, Swine
29459100Millet
30459900Mixed Grain
31452000Oats
324242000Oil, Coconut
334233000Oil, Cottonseed
344235000Oil, Olive
354243000Oil, Palm
364244000Oil, Palm Kernel
374234000Oil, Peanut
384239100Oil, Rapeseed
394232000Oil, Soybean
404232001Oil, Soybean (Local)
414236000Oil, Sunflowerseed
422231000Oilseed, Copra
432223000Oilseed, Cottonseed
442232000Oilseed, Palm Kernel
452221000Oilseed, Peanut
462226000Oilseed, Rapeseed
472222000Oilseed, Soybean
482222001Oilseed, Soybean (Local)
492224000Oilseed, Sunflowerseed
50585100Orange Juice
51571120Oranges, Fresh
52579309Peaches & Nectarines, Fresh
53579220Pears, Fresh
54577907Pistachios, Inshell Basis
55114200Poultry, Meat, Broiler
56575200Raisins
57422110Rice, Milled
58451000Rye
59459200Sorghum
60612000Sugar, Centrifugal
61571220Tangerines/Mandarins, Fresh
62577901Walnuts, Inshell Basis
63410000Wheat
\n", "
" ], "text/plain": [ " commodityCode commodityName\n", "0 577400 Almonds, Shelled Basis\n", "1 11000 Animal Numbers, Cattle\n", "2 13000 Animal Numbers, Swine\n", "3 574000 Apples, Fresh\n", "4 430000 Barley\n", "5 579305 Cherries (Sweet&Sour), Fresh\n", "6 711100 Coffee, Green\n", "7 440000 Corn\n", "8 2631000 Cotton\n", "9 230000 Dairy, Butter\n", "10 240000 Dairy, Cheese\n", "11 224400 Dairy, Dry Whole Milk Powder\n", "12 223000 Dairy, Milk, Fluid\n", "13 224200 Dairy, Milk, Nonfat Dry\n", "14 572220 Grapefruit, Fresh\n", "15 575100 Grapes, Fresh Table\n", "16 572120 Lemons/Limes, Fresh\n", "17 813700 Meal, Copra\n", "18 813300 Meal, Cottonseed\n", "19 814200 Meal, Fish\n", "20 813800 Meal, Palm Kernel\n", "21 813200 Meal, Peanut\n", "22 813600 Meal, Rapeseed\n", "23 813100 Meal, Soybean\n", "24 813101 Meal, Soybean (Local)\n", "25 813500 Meal, Sunflowerseed\n", "26 111000 Meat, Beef and Veal\n", "27 115000 Meat, Chicken\n", "28 113000 Meat, Swine\n", "29 459100 Millet\n", "30 459900 Mixed Grain\n", "31 452000 Oats\n", "32 4242000 Oil, Coconut\n", "33 4233000 Oil, Cottonseed\n", "34 4235000 Oil, Olive\n", "35 4243000 Oil, Palm\n", "36 4244000 Oil, Palm Kernel\n", "37 4234000 Oil, Peanut\n", "38 4239100 Oil, Rapeseed\n", "39 4232000 Oil, Soybean\n", "40 4232001 Oil, Soybean (Local)\n", "41 4236000 Oil, Sunflowerseed\n", "42 2231000 Oilseed, Copra\n", "43 2223000 Oilseed, Cottonseed\n", "44 2232000 Oilseed, Palm Kernel\n", "45 2221000 Oilseed, Peanut\n", "46 2226000 Oilseed, Rapeseed\n", "47 2222000 Oilseed, Soybean\n", "48 2222001 Oilseed, Soybean (Local)\n", "49 2224000 Oilseed, Sunflowerseed\n", "50 585100 Orange Juice\n", "51 571120 Oranges, Fresh\n", "52 579309 Peaches & Nectarines, Fresh\n", "53 579220 Pears, Fresh\n", "54 577907 Pistachios, Inshell Basis\n", "55 114200 Poultry, Meat, Broiler\n", "56 575200 Raisins\n", "57 422110 Rice, Milled\n", "58 451000 Rye\n", "59 459200 Sorghum\n", "60 612000 Sugar, Centrifugal\n", "61 571220 Tangerines/Mandarins, Fresh\n", "62 577901 Walnuts, Inshell Basis\n", "63 410000 Wheat" ] }, "execution_count": 104, "metadata": {}, "output_type": "execute_result" } ], "source": [ "get_usda_data(psd_com_url)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "get_usda_data(psd_regions_url)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "get_usda_data(psd_countries_url)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Returns a set of records with Forecast number for a given Commodity Code (Ex, 0440000 for Corn) and a given Market Year (Ex, 2017) for all applicable countries. PSD Database has data for most commodities since year 1960. Please see Commodities end point to get a list of all PSD Commodities with corresponding Commodity Code." ] }, { "cell_type": "code", "execution_count": 129, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
commodityCodecountryCodemarketYearcalendarYearmonthattributeIdunitIdvalue
0440000AF20212022544130.0000
1440000AF2021202252080.0000
2440000AF202120225288220.0000
3440000AF2021202255785.0000
4440000AF2021202258185.0000
...........................
1870440000ZA20212022719282100.0000
1871440000ZA20212022712582800.0000
1872440000ZA2021202271768971.0000
1873440000ZA20212022717884471.0000
1874440000ZA202120227184262.5674
\n", "

1875 rows × 8 columns

\n", "
" ], "text/plain": [ " commodityCode countryCode marketYear calendarYear month attributeId \\\n", "0 440000 AF 2021 2022 5 4 \n", "1 440000 AF 2021 2022 5 20 \n", "2 440000 AF 2021 2022 5 28 \n", "3 440000 AF 2021 2022 5 57 \n", "4 440000 AF 2021 2022 5 81 \n", "... ... ... ... ... ... ... \n", "1870 440000 ZA 2021 2022 7 192 \n", "1871 440000 ZA 2021 2022 7 125 \n", "1872 440000 ZA 2021 2022 7 176 \n", "1873 440000 ZA 2021 2022 7 178 \n", "1874 440000 ZA 2021 2022 7 184 \n", "\n", " unitId value \n", "0 4 130.0000 \n", "1 8 0.0000 \n", "2 8 220.0000 \n", "3 8 5.0000 \n", "4 8 5.0000 \n", "... ... ... \n", "1870 8 2100.0000 \n", "1871 8 2800.0000 \n", "1872 8 971.0000 \n", "1873 8 4471.0000 \n", "1874 26 2.5674 \n", "\n", "[1875 rows x 8 columns]" ] }, "execution_count": 129, "metadata": {}, "output_type": "execute_result" } ], "source": [ "psd_comyear_url = \"https://apps.fas.usda.gov/OpenData/api/psd/commodity/{commodityCode}/country/all/year/{marketYear}\"\n", "get_usda_data(psd_comyear_url.format(commodityCode = \"0440000\", marketYear = \"2021\"))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Returns a set of records with Forecast number for a given Commodity Code (Ex, 0440000 for Corn) and a given Market Year (Ex, 2017) for a given Country (Ex, BR for Brazil). PSD Database has data for most commodities since year 1960. Please see Commodities end point to get a list of all PSD Commodities with corresponding Commodity Code, and Countries end point for a list of all countries with corresponding Country Code" ] }, { "cell_type": "code", "execution_count": 128, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
commodityCodecountryCodemarketYearcalendarYearmonthattributeIdunitIdvalue
0440000BR2021202264421800.0000
1440000BR2021202262084153.0000
2440000BR202120226288116000.0000
3440000BR2021202265782000.0000
4440000BR2021202268182500.0000
5440000BR2021202268480.0000
6440000BR202120226868122153.0000
7440000BR20212022688844500.0000
8440000BR202120226113834000.0000
9440000BR202120226130862000.0000
10440000BR202120226192811000.0000
11440000BR202120226125873000.0000
12440000BR20212022617684653.0000
13440000BR2021202261788122153.0000
14440000BR202120226184265.3211
\n", "
" ], "text/plain": [ " commodityCode countryCode marketYear calendarYear month attributeId \\\n", "0 440000 BR 2021 2022 6 4 \n", "1 440000 BR 2021 2022 6 20 \n", "2 440000 BR 2021 2022 6 28 \n", "3 440000 BR 2021 2022 6 57 \n", "4 440000 BR 2021 2022 6 81 \n", "5 440000 BR 2021 2022 6 84 \n", "6 440000 BR 2021 2022 6 86 \n", "7 440000 BR 2021 2022 6 88 \n", "8 440000 BR 2021 2022 6 113 \n", "9 440000 BR 2021 2022 6 130 \n", "10 440000 BR 2021 2022 6 192 \n", "11 440000 BR 2021 2022 6 125 \n", "12 440000 BR 2021 2022 6 176 \n", "13 440000 BR 2021 2022 6 178 \n", "14 440000 BR 2021 2022 6 184 \n", "\n", " unitId value \n", "0 4 21800.0000 \n", "1 8 4153.0000 \n", "2 8 116000.0000 \n", "3 8 2000.0000 \n", "4 8 2500.0000 \n", "5 8 0.0000 \n", "6 8 122153.0000 \n", "7 8 44500.0000 \n", "8 8 34000.0000 \n", "9 8 62000.0000 \n", "10 8 11000.0000 \n", "11 8 73000.0000 \n", "12 8 4653.0000 \n", "13 8 122153.0000 \n", "14 26 5.3211 " ] }, "execution_count": 128, "metadata": {}, "output_type": "execute_result" } ], "source": [ "psd_comcountryyear_url = \"https://apps.fas.usda.gov/OpenData/api/psd/commodity/{commodityCode}/country/{countryCode}/year/{marketYear}\"\n", "get_usda_data(psd_comcountryyear_url.format(commodityCode = \"0440000\", countryCode = \"BR\", marketYear = \"2021\"))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Returns a set of records with Forecast number for a given Commodity Code (Ex, 0440000 for Corn) and a given Market Year (Ex, 2017) for the world.Data from all applicable countries are aggregated together for reporting it at the World Level. PSD Database has data for most commodities since year 1960. Please see Commodities end point to get a list of all PSD Commodities with corresponding Commodity Code." ] }, { "cell_type": "code", "execution_count": 127, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
commodityCodecountryCodemarketYearcalendarYearmonthattributeIdunitIdvalue
04400000202120227442.067390e+05
144000002021202272082.932910e+05
244000002021202272881.217866e+06
344000002021202275781.791450e+05
444000002021202278181.813980e+05
544000002021202278480.000000e+00
644000002021202278681.690302e+06
744000002021202278881.992330e+05
8440000020212022711381.899280e+05
9440000020212022713087.462700e+05
10440000020212022719284.325210e+05
11440000020212022712581.178791e+06
12440000020212022717683.122780e+05
13440000020212022717881.690302e+06
144400000202120227184265.890800e+00
\n", "
" ], "text/plain": [ " commodityCode countryCode marketYear calendarYear month attributeId \\\n", "0 440000 0 2021 2022 7 4 \n", "1 440000 0 2021 2022 7 20 \n", "2 440000 0 2021 2022 7 28 \n", "3 440000 0 2021 2022 7 57 \n", "4 440000 0 2021 2022 7 81 \n", "5 440000 0 2021 2022 7 84 \n", "6 440000 0 2021 2022 7 86 \n", "7 440000 0 2021 2022 7 88 \n", "8 440000 0 2021 2022 7 113 \n", "9 440000 0 2021 2022 7 130 \n", "10 440000 0 2021 2022 7 192 \n", "11 440000 0 2021 2022 7 125 \n", "12 440000 0 2021 2022 7 176 \n", "13 440000 0 2021 2022 7 178 \n", "14 440000 0 2021 2022 7 184 \n", "\n", " unitId value \n", "0 4 2.067390e+05 \n", "1 8 2.932910e+05 \n", "2 8 1.217866e+06 \n", "3 8 1.791450e+05 \n", "4 8 1.813980e+05 \n", "5 8 0.000000e+00 \n", "6 8 1.690302e+06 \n", "7 8 1.992330e+05 \n", "8 8 1.899280e+05 \n", "9 8 7.462700e+05 \n", "10 8 4.325210e+05 \n", "11 8 1.178791e+06 \n", "12 8 3.122780e+05 \n", "13 8 1.690302e+06 \n", "14 26 5.890800e+00 " ] }, "execution_count": 127, "metadata": {}, "output_type": "execute_result" } ], "source": [ "psd_comworldyear_url = \"https://apps.fas.usda.gov/OpenData/api/psd/commodity/{commodityCode}/world/year/{marketYear}\"\n", "get_usda_data(psd_comworldyear_url.format(commodityCode = \"0440000\", marketYear = \"2021\"))" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Production QuantityDomestic ConsumptionUSDA Stocks to Use RatioGro Implied Stocks to Use Ratio
2013/14265282517.391304NaN
2014/15473312515.104000NaN
2015/16779295010.949153NaN
2016/1751630009.900000NaN
2017/1846330509.508197NaN
2018/1970230007.120076NaN
2019/20726310016.903226NaN
2020/21751290014.939759NaN
2021/22600285010.508475NaN
2022/23700260010.51823411.529111
\n", "
" ], "text/plain": [ " Production Quantity Domestic Consumption USDA Stocks to Use Ratio \\\n", "2013/14 265 2825 17.391304 \n", "2014/15 473 3125 15.104000 \n", "2015/16 779 2950 10.949153 \n", "2016/17 516 3000 9.900000 \n", "2017/18 463 3050 9.508197 \n", "2018/19 702 3000 7.120076 \n", "2019/20 726 3100 16.903226 \n", "2020/21 751 2900 14.939759 \n", "2021/22 600 2850 10.508475 \n", "2022/23 700 2600 10.518234 \n", "\n", " Gro Implied Stocks to Use Ratio \n", "2013/14 NaN \n", "2014/15 NaN \n", "2015/16 NaN \n", "2016/17 NaN \n", "2017/18 NaN \n", "2018/19 NaN \n", "2019/20 NaN \n", "2020/21 NaN \n", "2021/22 NaN \n", "2022/23 11.529111 " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "gro_url = \"https://community.gro-intelligence.com/food-security-tracker-africa/data/Sudan_Wheat_supply_demand.json\"\n", "get_usda_data(gro_url)\n", "# See chart of data on website here: https://community.gro-intelligence.com/food-security-tracker-africa/details?country=Sudan&crop=Wheat" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "https://community.gro-intelligence.com/food-security-tracker-africa/details?country=Sudan&crop=Wheat\n", "\n", "/Users/cameronkruse/Desktop/Screen Shot 2022-07-31 at 6.57.33 PM.png\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3.10.4 ('food-security')", "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.10.4" }, "orig_nbformat": 4, "vscode": { "interpreter": { "hash": "80d14d1fb741077bd42a5b4187c7db14fd15f042564cfa5e7db6942dfa41f05d" } } }, "nbformat": 4, "nbformat_minor": 2 }