Table Concordance¶
[1]:
import pandas as pd
import numpy as np
import beaapi
from dotenv import dotenv_values
beakey = dotenv_values()["beakey"]
[2]:
year = 2024
extra_details = [("State GDP State detail",
[('A', {'datasetname':'GDPbyIndustry', 'TableId':1, 'year':year, "Frequency":"A", "Industry":"ALL"}, {'datasetname':'Regional', 'TableName':'SAGDP2', 'year':year, 'LineCode':'ALL', 'GeoFips':"00000"}),
('Q', {'datasetname':'GDPbyIndustry'}, {'datasetname':'Regional', 'TableId':'SQGDP2'})])]
[4]:
df = beaapi.get_parameter_values(beakey, "GDPbyIndustry", "TableID")
display(df)
| Key | Desc | |
|---|---|---|
| 0 | 1 | Value Added by Industry (A) (Q) |
| 1 | 5 | Value added by Industry as a Percentage of Gro... |
| 2 | 6 | Components of Value Added by Industry (A) |
| 3 | 7 | Components of Value Added by Industry as a Per... |
| 4 | 8 | Chain-Type Quantity Indexes for Value Added by... |
| 5 | 9 | Percent Changes in Chain-Type Quantity Indexes... |
| 6 | 10 | Real Value Added by Industry (A) (Q) |
| 7 | 11 | Chain-Type Price Indexes for Value Added by In... |
| 8 | 12 | Percent Changes in Chain-Type Price Indexes fo... |
| 9 | 13 | Contributions to Percent Change in Real Gross ... |
| 10 | 14 | Contributions to Percent Change in the Chain-T... |
| 11 | 15 | Gross Output by Industry (A) (Q) |
| 12 | 16 | Chain-Type Quantity Indexes for Gross Output b... |
| 13 | 17 | Percent Changes in Chain-Type Quantity Indexes... |
| 14 | 18 | Chain-Type Price Indexes for Gross Output by I... |
| 15 | 19 | Percent Changes in Chain-Type Price Indexes fo... |
| 16 | 20 | Intermediate Inputs by Industry (A) (Q) |
| 17 | 21 | Chain-Type Quantity Indexes for Intermediate I... |
| 18 | 22 | Percent Changes in Chain-Type Quantity Indexes... |
| 19 | 23 | Chain-Type Price Indexes for Intermediate Inpu... |
| 20 | 24 | Percent Changes in Chain-Type Price Indexes fo... |
| 21 | 25 | Composition of Gross Output by Industry (A) |
| 22 | 26 | Shares of Gross Output by Industry (A) |
| 23 | 29 | Contributions to Percent Changes in Chain-Type... |
| 24 | 30 | Contributions to Percent Changes in Chain-Type... |
| 25 | 31 | Chain-Type Quantity Indexes for Energy Inputs ... |
| 26 | 32 | Contributions to Percent Change by Industry in... |
| 27 | 33 | Chain-Type Price Indexes for Energy Inputs by ... |
| 28 | 34 | Contributions to Percent Change by Industry in... |
| 29 | 35 | Chain-Type Quantity Indexes for Materials Inpu... |
| 30 | 36 | Contributions to Percent Change by Industry in... |
| 31 | 37 | Chain-Type Price Indexes for Materials Inputs ... |
| 32 | 38 | Contributions to Percent Change by Industry in... |
| 33 | 39 | Chain-Type Quantity Indexes for Purchased Serv... |
| 34 | 40 | Contributions to Percent Change by Industry in... |
| 35 | 41 | Chain-Type Price Indexes for Purchased Service... |
| 36 | 42 | Contributions to Percent Change by Industry in... |
| 37 | 208 | Real Gross Output by Industry (A) (Q) |
| 38 | 209 | Real Intermediate Inputs by Industry (A) (Q) |
[3]:
detail_i = 0
detail_description = extra_details[detail_i][0]
freq_i = 0
freq, agg_tbl_args, detail_tbl_args = extra_details[detail_i][1][freq_i]
agg_tbl0 = beaapi.get_data(beakey, **agg_tbl_args)
detail_tbl0 = beaapi.get_data(beakey, **detail_tbl_args)
display(agg_tbl0)
display(detail_tbl0)
| TableID | Frequency | Year | Quarter | Industry | IndustrYDescription | DataValue | NoteRef | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1 | A | 2024 | 2024 | 11 | Agriculture, forestry, fishing, and hunting | 248.4 | 1 |
| 1 | 1 | A | 2024 | 2024 | 111CA | Farms | 199.1 | 1 |
| 2 | 1 | A | 2024 | 2024 | 113FF | Forestry, fishing, and related activities | 49.3 | 1 |
| 3 | 1 | A | 2024 | 2024 | 21 | Mining | 393.7 | 1 |
| 4 | 1 | A | 2024 | 2024 | 211 | Oil and gas extraction | 241.5 | 1 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 94 | 1 | A | 2024 | 2024 | ORE | Other real estate | 837.0 | 1 |
| 95 | 1 | A | 2024 | 2024 | PGOOD | Private goods-producing industries<sup>1</sup> | 4867.5 | 1;1.1.A |
| 96 | 1 | A | 2024 | 2024 | PROF | Professional and business services | 3847.4 | 1 |
| 97 | 1 | A | 2024 | 2024 | PSERV | Private services-producing industries<sup>2</sup> | 21023.7 | 1;1.2.A |
| 98 | 1 | A | 2024 | 2024 | PVT | Private industries | 25891.2 | 1 |
99 rows × 8 columns
| Code | GeoFips | GeoName | TimePeriod | Description | CL_UNIT | UNIT_MULT | DataValue | NoteRef | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | SAGDP2-1 | 00000 | United States | 2024 | All industry total | Millions of current dollars | 6 | 29184890.0 | * |
| 1 | SAGDP2-2 | 00000 | United States | 2024 | Private industries | Millions of current dollars | 6 | 25891209.0 | * |
| 2 | SAGDP2-3 | 00000 | United States | 2024 | Agriculture, forestry, fishing and hunting | Millions of current dollars | 6 | 248390.0 | * |
| 3 | SAGDP2-4 | 00000 | United States | 2024 | Farms | Millions of current dollars | 6 | 0.0 | (NA) * |
| 4 | SAGDP2-5 | 00000 | United States | 2024 | Forestry, fishing, and related activities | Millions of current dollars | 6 | 0.0 | (NA) * |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 91 | SAGDP2-92 | 00000 | United States | 2024 | Private services-providing industries 3/ | Millions of current dollars | 6 | 21023662.0 | 3 * |
| 92 | SAGDP2-100 | 00000 | United States | 2024 | All industry total, overseas activity | Millions of current dollars | 6 | 158867.8 | * |
| 93 | SAGDP2-101 | 00000 | United States | 2024 | Government and government enterprises, oversea... | Millions of current dollars | 6 | 158867.8 | * |
| 94 | SAGDP2-102 | 00000 | United States | 2024 | Federal civilian, overseas activity | Millions of current dollars | 6 | 0.0 | (NA) * |
| 95 | SAGDP2-103 | 00000 | United States | 2024 | Military, overseas activity | Millions of current dollars | 6 | 0.0 | (NA) * |
96 rows × 9 columns
[ ]:
agg_tbl = agg_tbl0.drop(columns=['Frequency', 'NoteRef', 'TableID']).rename(columns={'DataValue':'DataValue_Agg'})
detail_tbl = detail_tbl0.copy()
detail_tbl['Quarter'] = '2024'
#detail_tbl['LineCode'] = detail_tbl['Code'].str.slice(7)
detail_tbl['Description'] = detail_tbl['Description'].str.strip()
xw = pd.read_csv("../../bea_data_transformations/REA/metadata/ind_rea_va_xw.csv")
detail_tbl = detail_tbl.rename(columns={'Description':'REA Description'}).merge(xw, on=['REA Description'], how='left')
detail_tbl = detail_tbl.drop(columns=['UNIT_MULT', 'NoteRef', 'Code']).rename(columns={'TimePeriod':'Year'}).rename(columns={'DataValue':'DataValue_Detail'})
merge = agg_tbl.merge(detail_tbl.rename(columns={'VA Description': 'IndustrYDescription'}), on=['Year', 'Quarter', 'IndustrYDescription'], how='left')
merge = merge[['Year', 'Quarter']+['Industry', 'IndustrYDescription', 'DataValue_Agg', 'DataValue_Detail', 'CL_UNIT', 'REA Description', 'REA LineCode']]
display(merge)
| Year | Quarter | Industry | IndustrYDescription | DataValue_Agg | DataValue_Detail | CL_UNIT | REA Description | REA LineCode | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024 | 2024 | 11 | Agriculture, forestry, fishing, and hunting | 248.4 | 248390.0 | Millions of current dollars | Agriculture, forestry, fishing and hunting | 3.0 |
| 1 | 2024 | 2024 | 111CA | Farms | 199.1 | NaN | <NA> | NaN | NaN |
| 2 | 2024 | 2024 | 113FF | Forestry, fishing, and related activities | 49.3 | NaN | <NA> | NaN | NaN |
| 3 | 2024 | 2024 | 21 | Mining | 393.7 | 393725.0 | Millions of current dollars | Mining, quarrying, and oil and gas extraction | 6.0 |
| 4 | 2024 | 2024 | 211 | Oil and gas extraction | 241.5 | NaN | <NA> | NaN | NaN |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 94 | 2024 | 2024 | ORE | Other real estate | 837.0 | NaN | <NA> | NaN | NaN |
| 95 | 2024 | 2024 | PGOOD | Private goods-producing industries<sup>1</sup> | 4867.5 | NaN | <NA> | NaN | NaN |
| 96 | 2024 | 2024 | PROF | Professional and business services | 3847.4 | 3847411.0 | Millions of current dollars | Professional and business services | 59.0 |
| 97 | 2024 | 2024 | PSERV | Private services-producing industries<sup>2</sup> | 21023.7 | NaN | <NA> | NaN | NaN |
| 98 | 2024 | 2024 | PVT | Private industries | 25891.2 | 25891209.0 | Millions of current dollars | Private industries | 2.0 |
99 rows × 9 columns
[ ]: