{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "6dd35d48",
   "metadata": {},
   "source": [
    "# beaapi\n",
    "  beaapi: A Python library library to make it easier to retrieve and work with BEA data. \n",
    "\n",
    "For documentation online, see [here](https://us-bea.github.io/beaapi/).\n",
    "\n",
    "For development and building information, see [CONTRIBUTING.md](https://github.com/us-bea/beaapi/blobl/main/CONTRIBUTING.md)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34d41f9d",
   "metadata": {},
   "source": [
    "## To Get Started\n",
    "Requirements: You will need Python and the `pandas` package with at least version 1.5.6. \n",
    "\n",
    "Installation: You can download the `.whl` file for the latest [release](https://github.com/us-bea/beaapi/releases/) and then install using the following command (executed from the folder that has the downloaded file).\n",
    "```\n",
    "pip install beaapi-0.0.2-py3-none-any.whl\n",
    "```\n",
    "\n",
    "Alternatively, you can clone this repo and build it locally. \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77ad4035",
   "metadata": {},
   "source": [
    "Once you have installed the package, you can load it in your Python script as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "42a10181",
   "metadata": {},
   "outputs": [],
   "source": [
    "import beaapi"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91b833f0",
   "metadata": {},
   "source": [
    "To use the package, you must first  [register for an API key](https://apps.bea.gov/api/signup/) from BEA by providing your name and email address. The key will be emailed to you. \n",
    "\n",
    "Once you have received your BEA API key, you can save it to a variable to make it easier to use later: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "123f6d70",
   "metadata": {},
   "outputs": [],
   "source": [
    "beakey = 'YOUR 36-DIGIT API KEY'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b16b67f2",
   "metadata": {},
   "source": [
    "For scripts, an even better method is to create an unversioned text file called `.env` with the contents `beakey=Your36DigitAPiKey` and then use the package `python-dotenv` to load it automatically."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c72f4ce6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from dotenv import load_dotenv\n",
    "load_dotenv()\n",
    "beakey = os.environ.get(\"beakey\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bf1e59a",
   "metadata": {},
   "source": [
    "## Get metadata\n",
    "The BEA data is distributed across various datasets that each contain many tables. To get a list of the datasets available on the API, use `get_data_set_list()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bb62442b",
   "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>DatasetName</th>\n",
       "      <th>DatasetDescription</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NIPA</td>\n",
       "      <td>Standard NIPA tables</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NIUnderlyingDetail</td>\n",
       "      <td>Standard NI underlying detail tables</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MNE</td>\n",
       "      <td>Multinational Enterprises</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FixedAssets</td>\n",
       "      <td>Standard Fixed Assets tables</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ITA</td>\n",
       "      <td>International Transactions Accounts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>IIP</td>\n",
       "      <td>International Investment Position</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>InputOutput</td>\n",
       "      <td>Input-Output Data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>IntlServTrade</td>\n",
       "      <td>International Services Trade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>GDPbyIndustry</td>\n",
       "      <td>GDP by Industry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Regional</td>\n",
       "      <td>Regional data sets</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>UnderlyingGDPbyIndustry</td>\n",
       "      <td>Underlying GDP by Industry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>APIDatasetMetaData</td>\n",
       "      <td>Metadata about other API datasets</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                DatasetName                    DatasetDescription\n",
       "0                      NIPA                  Standard NIPA tables\n",
       "1        NIUnderlyingDetail  Standard NI underlying detail tables\n",
       "2                       MNE             Multinational Enterprises\n",
       "3               FixedAssets          Standard Fixed Assets tables\n",
       "4                       ITA   International Transactions Accounts\n",
       "5                       IIP     International Investment Position\n",
       "6               InputOutput                     Input-Output Data\n",
       "7             IntlServTrade          International Services Trade\n",
       "8             GDPbyIndustry                       GDP by Industry\n",
       "9                  Regional                    Regional data sets\n",
       "10  UnderlyingGDPbyIndustry            Underlying GDP by Industry\n",
       "11       APIDatasetMetaData     Metadata about other API datasets"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "list_of_sets = beaapi.get_data_set_list(beakey)\n",
    "display(list_of_sets)  # Note the last dataset is only for speeding up metadata queries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d6170ff",
   "metadata": {},
   "source": [
    "Queries to the different datasets take different parameters. To get a list of the paramaters for a given dataset, use `get_parameter_list()`; for example, to get a list of the parameters for the NIPA dataset, use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "11dd6a39",
   "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>ParameterName</th>\n",
       "      <th>ParameterDataType</th>\n",
       "      <th>ParameterDescription</th>\n",
       "      <th>ParameterIsRequiredFlag</th>\n",
       "      <th>ParameterDefaultValue</th>\n",
       "      <th>MultipleAcceptedFlag</th>\n",
       "      <th>AllValue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Frequency</td>\n",
       "      <td>string</td>\n",
       "      <td>A - Annual, Q-Quarterly, M-Monthly</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ShowMillions</td>\n",
       "      <td>string</td>\n",
       "      <td>A flag indicating that million-dollar data sho...</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TableID</td>\n",
       "      <td>integer</td>\n",
       "      <td>The standard NIPA table identifier</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TableName</td>\n",
       "      <td>string</td>\n",
       "      <td>The new NIPA table identifier</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Year</td>\n",
       "      <td>integer</td>\n",
       "      <td>List of year(s) of data to retrieve (X for All)</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>X</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ParameterName ParameterDataType  \\\n",
       "0     Frequency            string   \n",
       "1  ShowMillions            string   \n",
       "2       TableID           integer   \n",
       "3     TableName            string   \n",
       "4          Year           integer   \n",
       "\n",
       "                                ParameterDescription  ParameterIsRequiredFlag  \\\n",
       "0                 A - Annual, Q-Quarterly, M-Monthly                        1   \n",
       "1  A flag indicating that million-dollar data sho...                        0   \n",
       "2                 The standard NIPA table identifier                        0   \n",
       "3                      The new NIPA table identifier                        0   \n",
       "4    List of year(s) of data to retrieve (X for All)                        1   \n",
       "\n",
       "  ParameterDefaultValue  MultipleAcceptedFlag AllValue  \n",
       "0                                           1           \n",
       "1                     N                     0           \n",
       "2                  <NA>                     0           \n",
       "3                  <NA>                     0           \n",
       "4                                           1        X  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "list_of_params = beaapi.get_parameter_list(beakey, 'NIPA')\n",
    "display(list_of_params)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9fb1473d",
   "metadata": {},
   "source": [
    "To get a list of the values for a given parameter, use `get_parameter_values()`; for example, to get a list of the parameter values for the Frequency parameter of the NIPA dataset, use:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8577143d",
   "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>FrequencyID</th>\n",
       "      <th>Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>Annual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Q</td>\n",
       "      <td>Quarterly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M</td>\n",
       "      <td>Monthly</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  FrequencyID Description\n",
       "0           A      Annual\n",
       "1           Q   Quarterly\n",
       "2           M     Monthly"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "list_of_param_vals = beaapi.get_parameter_values(beakey, 'NIPA', 'Frequency')\n",
    "display(list_of_param_vals)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07fcbe70",
   "metadata": {},
   "source": [
    "The [API documentation](https://apps.bea.gov/API/docs/index.htm) is a good place to also understand allowed values.\n",
    "\n",
    "A few datasets (ITA, IIP, InputOutput, IntlServTrade, GDPbyIndustry, Regional, and UnderlyingGDPbyIndustry) allow you to filter the values based on what will be passed in for other parameters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "dfbfba34",
   "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>Key</th>\n",
       "      <th>Desc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BalCapAcct</td>\n",
       "      <td>Balance on capital account</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BalCurrAcct</td>\n",
       "      <td>Balance on current account</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BalGds</td>\n",
       "      <td>Balance on goods</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BalGdsServ</td>\n",
       "      <td>Balance on goods and services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BalPrimInc</td>\n",
       "      <td>Balance on primary income</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Key                           Desc\n",
       "0   BalCapAcct     Balance on capital account\n",
       "1  BalCurrAcct     Balance on current account\n",
       "2       BalGds               Balance on goods\n",
       "3   BalGdsServ  Balance on goods and services\n",
       "4   BalPrimInc      Balance on primary income"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "tbl = beaapi.get_parameter_values_filtered(beakey, 'ITA', targetparameter='Indicator', AreaOrCountry=\"China\",Frequency=\"A\", Year=\"2011\")\n",
    "display(tbl.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad1f628b",
   "metadata": {},
   "source": [
    "A few of the datasets publish metadata tables that can be queried for particular strings using `search_metadata()`. This method allows you to search for BEA data by keyword. For example, to find all datasets in which the term \"personal consumption\" appears, use the following:  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b0812784",
   "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>SeriesCode</th>\n",
       "      <th>RowNumber</th>\n",
       "      <th>LineDescription</th>\n",
       "      <th>LineNumber</th>\n",
       "      <th>ParentLineNumber</th>\n",
       "      <th>Tier</th>\n",
       "      <th>Path</th>\n",
       "      <th>TableId</th>\n",
       "      <th>Datasetname</th>\n",
       "      <th>TableName</th>\n",
       "      <th>ReleaseDate</th>\n",
       "      <th>NextReleaseDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A191RL</td>\n",
       "      <td>10</td>\n",
       "      <td>Gross domestic product</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>T10101</td>\n",
       "      <td>NIPA</td>\n",
       "      <td>Table 1.1.1. Percent Change From Preceding Per...</td>\n",
       "      <td>Feb 28 2019  8:30AM</td>\n",
       "      <td>Mar 28 2019  8:30AM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>A191RP</td>\n",
       "      <td>280</td>\n",
       "      <td>Gross domestic product, current dollars</td>\n",
       "      <td>27</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>T10101</td>\n",
       "      <td>NIPA</td>\n",
       "      <td>Table 1.1.1. Percent Change From Preceding Per...</td>\n",
       "      <td>Feb 28 2019  8:30AM</td>\n",
       "      <td>Mar 28 2019  8:30AM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SeriesCode RowNumber                          LineDescription LineNumber  \\\n",
       "0      A191RL        10                   Gross domestic product          1   \n",
       "26     A191RP       280  Gross domestic product, current dollars         27   \n",
       "\n",
       "   ParentLineNumber Tier Path TableId Datasetname  \\\n",
       "0                      0    1  T10101        NIPA   \n",
       "26                     0   27  T10101        NIPA   \n",
       "\n",
       "                                            TableName          ReleaseDate  \\\n",
       "0   Table 1.1.1. Percent Change From Preceding Per...  Feb 28 2019  8:30AM   \n",
       "26  Table 1.1.1. Percent Change From Preceding Per...  Feb 28 2019  8:30AM   \n",
       "\n",
       "        NextReleaseDate  \n",
       "0   Mar 28 2019  8:30AM  \n",
       "26  Mar 28 2019  8:30AM  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "search_data = beaapi.search_metadata('Gross domestic', beakey)\n",
    "search_data.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aae29d99",
   "metadata": {},
   "source": [
    "Please note that that search_metadata currently searches only national data.\n",
    "\n",
    "The contents of this function are automatically updated using a new metadata component of BEA's API; as such, we recommend that you use it with your API key, and the first use of this function requires that you use your key or it will be unable to extract the metadata.\n",
    "\n",
    "If you do not wish to automatically update the metadata (e.g., you have conducted a study using the search function), simply searching for the term without also passing your key to the function will do a search only using your locally stored version.\n",
    "\n",
    "\n",
    "## Get data \n",
    "\n",
    "\n",
    "Once you have identified the TableId number and other information, you can use `get_data()` to access the data. The following code, for example, returns the NIPA table with 2015 data for Table T20305. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f0f9cefc",
   "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>TableName</th>\n",
       "      <th>SeriesCode</th>\n",
       "      <th>LineNumber</th>\n",
       "      <th>LineDescription</th>\n",
       "      <th>TimePeriod</th>\n",
       "      <th>METRIC_NAME</th>\n",
       "      <th>CL_UNIT</th>\n",
       "      <th>UNIT_MULT</th>\n",
       "      <th>DataValue</th>\n",
       "      <th>NoteRef</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>T20305</td>\n",
       "      <td>DPCERC</td>\n",
       "      <td>1</td>\n",
       "      <td>Personal consumption expenditures (PCE)</td>\n",
       "      <td>2015Q1</td>\n",
       "      <td>Current Dollars</td>\n",
       "      <td>Level</td>\n",
       "      <td>6</td>\n",
       "      <td>12083904</td>\n",
       "      <td>T20305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>T20305</td>\n",
       "      <td>DPCERC</td>\n",
       "      <td>1</td>\n",
       "      <td>Personal consumption expenditures (PCE)</td>\n",
       "      <td>2015Q2</td>\n",
       "      <td>Current Dollars</td>\n",
       "      <td>Level</td>\n",
       "      <td>6</td>\n",
       "      <td>12224707</td>\n",
       "      <td>T20305</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  TableName SeriesCode  LineNumber                          LineDescription  \\\n",
       "0    T20305     DPCERC           1  Personal consumption expenditures (PCE)   \n",
       "1    T20305     DPCERC           1  Personal consumption expenditures (PCE)   \n",
       "\n",
       "  TimePeriod      METRIC_NAME CL_UNIT  UNIT_MULT  DataValue NoteRef  \n",
       "0     2015Q1  Current Dollars   Level          6   12083904  T20305  \n",
       "1     2015Q2  Current Dollars   Level          6   12224707  T20305  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "bea_tbl = beaapi.get_data(beakey, datasetname='NIPA', TableName='T20305', Frequency='Q', Year='2015')\n",
    "display(bea_tbl.head(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0de40ad2",
   "metadata": {},
   "source": [
    "We store in the meta-data the index columns so that you can create a unique index on the data-frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "946094d8",
   "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></th>\n",
       "      <th>TableName</th>\n",
       "      <th>SeriesCode</th>\n",
       "      <th>LineDescription</th>\n",
       "      <th>METRIC_NAME</th>\n",
       "      <th>CL_UNIT</th>\n",
       "      <th>UNIT_MULT</th>\n",
       "      <th>DataValue</th>\n",
       "      <th>NoteRef</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LineNumber</th>\n",
       "      <th>TimePeriod</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 rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>2015Q1</th>\n",
       "      <td>T20305</td>\n",
       "      <td>DPCERC</td>\n",
       "      <td>Personal consumption expenditures (PCE)</td>\n",
       "      <td>Current Dollars</td>\n",
       "      <td>Level</td>\n",
       "      <td>6</td>\n",
       "      <td>12083904</td>\n",
       "      <td>T20305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015Q2</th>\n",
       "      <td>T20305</td>\n",
       "      <td>DPCERC</td>\n",
       "      <td>Personal consumption expenditures (PCE)</td>\n",
       "      <td>Current Dollars</td>\n",
       "      <td>Level</td>\n",
       "      <td>6</td>\n",
       "      <td>12224707</td>\n",
       "      <td>T20305</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      TableName SeriesCode  \\\n",
       "LineNumber TimePeriod                        \n",
       "1          2015Q1        T20305     DPCERC   \n",
       "           2015Q2        T20305     DPCERC   \n",
       "\n",
       "                                               LineDescription  \\\n",
       "LineNumber TimePeriod                                            \n",
       "1          2015Q1      Personal consumption expenditures (PCE)   \n",
       "           2015Q2      Personal consumption expenditures (PCE)   \n",
       "\n",
       "                           METRIC_NAME CL_UNIT  UNIT_MULT  DataValue NoteRef  \n",
       "LineNumber TimePeriod                                                         \n",
       "1          2015Q1      Current Dollars   Level          6   12083904  T20305  \n",
       "           2015Q2      Current Dollars   Level          6   12224707  T20305  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(bea_tbl.set_index(bea_tbl.attrs['index_cols']).head(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7de743e8",
   "metadata": {},
   "source": [
    "Extra meta-data from the API is returned in a dictionary in the attributes called `detail` and can vary based on the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "29282d1d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Extra detail keys:dict_keys(['Statistic', 'UTCProductionTime', 'Dimensions', 'Notes'])\n",
      "Let's look at some interesting ones.\n",
      "Statistic: NIPA Table\n",
      "Notes corresponding to NoteRef:\n"
     ]
    },
    {
     "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>NoteRef</th>\n",
       "      <th>NoteText</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>T20305</td>\n",
       "      <td>Table 2.3.5. Personal Consumption Expenditures...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>T20305.1</td>\n",
       "      <td>1. Net expenses of NPISHs, defined as their gr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>T20305.2</td>\n",
       "      <td>2. Gross output is net of unrelated sales, sec...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>T20305.3</td>\n",
       "      <td>3. Excludes unrelated sales, secondary sales, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>T20305.4</td>\n",
       "      <td>4. Food consists of food and beverages purchas...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    NoteRef                                           NoteText\n",
       "0    T20305  Table 2.3.5. Personal Consumption Expenditures...\n",
       "1  T20305.1  1. Net expenses of NPISHs, defined as their gr...\n",
       "2  T20305.2  2. Gross output is net of unrelated sales, sec...\n",
       "3  T20305.3  3. Excludes unrelated sales, secondary sales, ...\n",
       "4  T20305.4  4. Food consists of food and beverages purchas..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print('Extra detail keys:' + str(bea_tbl.attrs['detail'].keys()))\n",
    "print(\"Let's look at some interesting ones.\")\n",
    "print('Statistic: ' + bea_tbl.attrs['detail']['Statistic'])\n",
    "print(\"Notes corresponding to NoteRef:\")\n",
    "display(bea_tbl.attrs['detail']['Notes'].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e3cca7d",
   "metadata": {},
   "source": [
    "To retrieve a limited selection of multiple years, list all the years you want to retrieve. For example, to retrieve data for 2011-2015, use `Year=\"2011,2012,2013,2014,2015\"`\n",
    "\n",
    "The  [API documentation](http://www.bea.gov/API/bea_web_service_api_user_guide.htm) includes information about the specific parameters required by `get_data()`. \n",
    "\n",
    "If you would like to format the data in a wide format so that different variables (in this case LineNumbers) are different rows, you can use `to_wide_vars_to_cols()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "522ce6f1",
   "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>LineNumber</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "      <th>25</th>\n",
       "      <th>26</th>\n",
       "      <th>27</th>\n",
       "      <th>28</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TimePeriod</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>2015Q1</th>\n",
       "      <td>12083904</td>\n",
       "      <td>3867908</td>\n",
       "      <td>1283823</td>\n",
       "      <td>465426</td>\n",
       "      <td>285887</td>\n",
       "      <td>332965</td>\n",
       "      <td>199546</td>\n",
       "      <td>2584085</td>\n",
       "      <td>917371</td>\n",
       "      <td>366171</td>\n",
       "      <td>...</td>\n",
       "      <td>814298</td>\n",
       "      <td>960760</td>\n",
       "      <td>992461</td>\n",
       "      <td>362114</td>\n",
       "      <td>1367850</td>\n",
       "      <td>1005736</td>\n",
       "      <td>10619844</td>\n",
       "      <td>546689</td>\n",
       "      <td>10570891</td>\n",
       "      <td>9107467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015Q2</th>\n",
       "      <td>12224707</td>\n",
       "      <td>3927357</td>\n",
       "      <td>1309627</td>\n",
       "      <td>481820</td>\n",
       "      <td>292631</td>\n",
       "      <td>333600</td>\n",
       "      <td>201577</td>\n",
       "      <td>2617730</td>\n",
       "      <td>916930</td>\n",
       "      <td>368315</td>\n",
       "      <td>...</td>\n",
       "      <td>828712</td>\n",
       "      <td>977198</td>\n",
       "      <td>1003757</td>\n",
       "      <td>363147</td>\n",
       "      <td>1374002</td>\n",
       "      <td>1010855</td>\n",
       "      <td>10759482</td>\n",
       "      <td>548295</td>\n",
       "      <td>10693235</td>\n",
       "      <td>9228648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015Q3</th>\n",
       "      <td>12347752</td>\n",
       "      <td>3960436</td>\n",
       "      <td>1318493</td>\n",
       "      <td>481620</td>\n",
       "      <td>297306</td>\n",
       "      <td>337083</td>\n",
       "      <td>202483</td>\n",
       "      <td>2641944</td>\n",
       "      <td>924494</td>\n",
       "      <td>370385</td>\n",
       "      <td>...</td>\n",
       "      <td>836130</td>\n",
       "      <td>978430</td>\n",
       "      <td>1012278</td>\n",
       "      <td>373825</td>\n",
       "      <td>1385653</td>\n",
       "      <td>1011828</td>\n",
       "      <td>10877903</td>\n",
       "      <td>545354</td>\n",
       "      <td>10802962</td>\n",
       "      <td>9333737</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "LineNumber        1        2        3       4       5       6       7   \\\n",
       "TimePeriod                                                               \n",
       "2015Q1      12083904  3867908  1283823  465426  285887  332965  199546   \n",
       "2015Q2      12224707  3927357  1309627  481820  292631  333600  201577   \n",
       "2015Q3      12347752  3960436  1318493  481620  297306  337083  202483   \n",
       "\n",
       "LineNumber       8       9       10  ...      19      20       21      22  \\\n",
       "TimePeriod                           ...                                    \n",
       "2015Q1      2584085  917371  366171  ...  814298  960760   992461  362114   \n",
       "2015Q2      2617730  916930  368315  ...  828712  977198  1003757  363147   \n",
       "2015Q3      2641944  924494  370385  ...  836130  978430  1012278  373825   \n",
       "\n",
       "LineNumber       23       24        25      26        27       28  \n",
       "TimePeriod                                                         \n",
       "2015Q1      1367850  1005736  10619844  546689  10570891  9107467  \n",
       "2015Q2      1374002  1010855  10759482  548295  10693235  9228648  \n",
       "2015Q3      1385653  1011828  10877903  545354  10802962  9333737  \n",
       "\n",
       "[3 rows x 28 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(beaapi.to_wide_vars_in_cols(bea_tbl).head(3))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28c160d2",
   "metadata": {},
   "source": [
    "## Advanced topics\n",
    "Throttling: The BEA api limits requests to a maximum of 100/minute and 100MB/minute (as well as 30 errors/minute). If the user exceeds this, they will be denied access for 1 hour. This package will automatically self-throttle, so in general, the user does not have to worry about that.\n",
    "\n",
    "See the docs for additional information on:\n",
    "- How to construct API queries to pull data from existing iTables."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d3b57cd",
   "metadata": {},
   "source": [
    "\n",
    "## Quick Links\n",
    "<!--\n",
    "* [CODE_OF_CONDUCT.md]\n",
    "* [SECURITY.md]\n",
    "-->\n",
    "* [LICENSE](https://github.com/us-bea/beaapi/blobl/main/LICENSE)\n",
    "* [CONTRIBUTING.md](https://github.com/us-bea/beaapi/blobl/main/CONTRIBUTING.md)\n",
    "* [SUPPORT.md](https://github.com/us-bea/beaapi/blobl/main/SUPPORT.md)\n",
    "* [CHANGELOG.md](https://github.com/us-bea/beaapi/blobl/main/CHANGELOG.md)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df2bf300",
   "metadata": {},
   "source": [
    "## Disclaimer\n",
    "The United States Department of Commerce (DOC) GitHub project code is provided on an ‘as is’ basis and the user assumes responsibility for its use. DOC has relinquished control of the information and no longer has responsibility to protect the integrity, confidentiality, or availability of the information. Any claims against the Department of Commerce stemming from the use of its GitHub project will be governed by all applicable Federal law. Any reference to specific commercial products, processes, or services by service mark, trademark, manufacturer, or otherwise, does not constitute or imply their endorsement, recommendation or favoring by the Department of Commerce. The Department of Commerce seal and logo, or the seal and logo of a DOC bureau, shall not be used in any manner to imply endorsement of any commercial product or activity by DOC or the United States Government.\n",
    "\n",
    "Use of this library may result in data being stored on users' local machines. Specifically, local copies of BEA API metadata may be stored and automatically updated in the `beaapi_data` directory (unless otherwise changed) in order to improve performance of `search_metadata()`.\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.16"
  },
  "vscode": {
   "interpreter": {
    "hash": "b39e4f58aaffe407935d2240021aabbab10b51fed98489a7bd1981bee29fc8b0"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
