Bureau of Labor Statistics Data

How to query public data sets using BigQuery

BigQuery is a fully managed data warehouse and analytics platform. Public datasets are available for you to analyze using SQL queries. You can access BigQuery public data sets using the web UI the command-line tool, or by making calls to the BigQuery REST API using a variety of client libraries such as Java, .NET, or Python.

To get started using a BigQuery public dataset, create or select a project. The first terabyte of data processed per month is free, so you can start querying public datasets without enabling billing. If you intend to go beyond the free tier, you should also enable billing.

  1. Sign in to your Google account.

    If you don't already have one, sign up for a new account.

  2. Select or create a Cloud Platform project.

    Go to the Manage resources page

  3. Enable billing for your project.

    Enable billing

  4. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, Enable the BigQuery API.

    Enable the API

Dataset overview

This dataset includes economic statistics on inflation, prices, unemployment, and pay & benefits provided by the Bureau of Labor Statistics (BLS).

You can start exploring this data in the BigQuery console:

Go to Bureau of Labor Statistics Dataset

Sample queries

Here are some examples of SQL queries you can run on this data in BigQuery.

These samples use BigQuery’s support for standard SQL. Use the #standardSQL tag to let BigQuery know you want to use standard SQL. For more information about the #standardSQL prefix, see Setting a query prefix.

What is the average annual inflation across all US Cities?

This query shows the average inflation across all major cities in the U.S.A. based on the Consumer Price Index (CPI). The CPI can be used to measure how much consumer spending varies from one time period to the next.

For more meaningful results, compare similar time periods, such as one year to the next, or May 2015 to May 2016. You should also compare equivalent locations. For example, don’t compare San Francisco to New York City.

Web UI

Open the following query in the Web UI

#standardsql
SELECT *, ROUND((100*(value-prev_year)/value), 1) rate
FROM (
  SELECT
    year,
    LAG(value) OVER(ORDER BY year) prev_year,
    ROUND(value, 1) AS value,
    area_name
  FROM
    `bigquery-public-data.bls.cpi_u`
  WHERE
    period = "S03"
    AND item_code = "SA0"
    AND area_name = "U.S. city average"
)
ORDER BY year

Command-line

bq query '
#standardsql
SELECT *, ROUND((100*(value-prev_year)/value), 1) rate
FROM (
  SELECT
    year,
    LAG(value) OVER(ORDER BY year) prev_year,
    ROUND(value, 1) AS value,
    area_name
  FROM
    `bigquery-public-data.bls.cpi_u`
  WHERE
    period = "S03"
    AND item_code = "SA0"
    AND area_name = "U.S. city average"
)
ORDER BY year
'

The results are shown here:

+------+-----------+-------+-------------------+------+
| year | prev_year | value |     area_name     | rate |
+------+-----------+-------+-------------------+------+
| 1997 |      NULL | 160.5 | U.S. city average | NULL |
| 1998 |     160.5 | 163.0 | U.S. city average |  1.5 |
| 1999 |     163.0 | 166.6 | U.S. city average |  2.2 |
| 2000 |     166.6 | 172.2 | U.S. city average |  3.3 |
| 2001 |     172.2 | 177.1 | U.S. city average |  2.8 |
| 2002 |     177.1 | 179.9 | U.S. city average |  1.6 |
| 2003 |     179.9 | 184.0 | U.S. city average |  2.2 |
| 2004 |     184.0 | 188.9 | U.S. city average |  2.6 |
| 2005 |     188.9 | 195.3 | U.S. city average |  3.3 |
| 2006 |     195.3 | 201.6 | U.S. city average |  3.1 |
| 2007 |     201.6 | 207.3 | U.S. city average |  2.7 |
| 2008 |   207.342 | 215.3 | U.S. city average |  3.7 |
| 2009 |   215.303 | 214.5 | U.S. city average | -0.4 |
| 2010 |   214.537 | 218.1 | U.S. city average |  1.6 |
| 2011 |   218.056 | 224.9 | U.S. city average |  3.0 |
| 2012 |   224.939 | 229.6 | U.S. city average |  2.0 |
| 2013 |   229.594 | 233.0 | U.S. city average |  1.5 |
| 2014 |   232.957 | 236.7 | U.S. city average |  1.6 |
| 2015 |   236.736 | 237.0 | U.S. city average |  0.1 |
| 2016 |   237.017 | 240.0 | U.S. city average |  1.2 |
+------+-----------+-------+-------------------+------+

What was the monthly unemployment rate (U3) in 2016?

This query shows how the unemployment rate varies by month for the year 2016. U3 is the traditional unemployment metric reported in the media and by the government.

Web UI

Open the following query in the Web UI

#standardsql
SELECT
  year,
  date,
  period,
  value,
  series_title
FROM
  `bigquery-public-data.bls.unemployment_cps`
WHERE
  series_id = "LNS14000000"
  AND year = 2016
ORDER BY date

Command-line

bq query '
#standardsql
SELECT
  year,
  date,
  period,
  value,
  series_title
FROM
  `bigquery-public-data.bls.unemployment_cps`
WHERE
  series_id = "LNS14000000"
  AND year = 2016
ORDER BY date
'

The results are shown here:

+------+------------+--------+-------+--------------------------+
| year |    date    | period | value |       series_title       |
+------+------------+--------+-------+--------------------------+
| 2016 | 2016-01-01 | M01    |   4.9 | (Seas) Unemployment Rate |
| 2016 | 2016-02-01 | M02    |   4.9 | (Seas) Unemployment Rate |
| 2016 | 2016-03-01 | M03    |   5.0 | (Seas) Unemployment Rate |
| 2016 | 2016-04-01 | M04    |   5.0 | (Seas) Unemployment Rate |
| 2016 | 2016-05-01 | M05    |   4.7 | (Seas) Unemployment Rate |
| 2016 | 2016-06-01 | M06    |   4.9 | (Seas) Unemployment Rate |
| 2016 | 2016-07-01 | M07    |   4.9 | (Seas) Unemployment Rate |
| 2016 | 2016-08-01 | M08    |   4.9 | (Seas) Unemployment Rate |
| 2016 | 2016-09-01 | M09    |   4.9 | (Seas) Unemployment Rate |
| 2016 | 2016-10-01 | M10    |   4.8 | (Seas) Unemployment Rate |
| 2016 | 2016-11-01 | M11    |   4.6 | (Seas) Unemployment Rate |
| 2016 | 2016-12-01 | M12    |   4.7 | (Seas) Unemployment Rate |
+------+------------+--------+-------+--------------------------+

What are the top 10 hourly-waged types of work in Pittsburgh, PA for 2016?

Show the top ten types of work sorted by hourly wage, where ‘value’ in the table represents US dollars per hour.

Web UI

Open the following query in the Web UI

#standardsql
SELECT
  year,
  period,
  value,
  series_title
FROM
  `bigquery-public-data.bls.wm`
WHERE
  series_title LIKE '%Pittsburgh, PA%'
  AND year = 2016
ORDER BY
  value DESC
LIMIT
  10

Command-line

bq query '
#standardsql
SELECT
  year,
  period,
  value,
  series_title
FROM
  `bigquery-public-data.bls.wm`
WHERE
  series_title LIKE "%Pittsburgh, PA%"
  AND year = 2016
ORDER BY
  value DESC
LIMIT
  10
'

The results are shown here:

+------+--------+-------+-----------------------------------------------------------------------------------------------------------+
| year | period | value |                                               series_title                                                |
+------+--------+-------+-----------------------------------------------------------------------------------------------------------+
| 2016 | A01    | 81.27 | Hourly mean wage for financial managers, in Pittsburgh, PA, not able to level                             |
| 2016 | A01    | 79.09 | Hourly mean wage for education, training, and library occupations, full-time, in Pittsburgh, PA, Level 13 |
| 2016 | A01    | 79.07 | Hourly mean wage for education, training, and library occupations, in Pittsburgh, PA, Level 13            |
| 2016 | A01    |  68.2 | Hourly mean wage for management occupations, in Pittsburgh, PA, not able to level                         |
| 2016 | A01    | 66.97 | Hourly mean wage for management occupations, in Pittsburgh, PA, Level 12                                  |
| 2016 | A01    | 66.05 | Hourly mean wage for computer and mathematical occupations, in Pittsburgh, PA, Level 12                   |
| 2016 | A01    | 61.39 | Hourly mean wage for education, training, and library occupations, in Pittsburgh, PA, Level 12            |
| 2016 | A01    | 61.19 | Hourly mean wage for education, training, and library occupations, full-time, in Pittsburgh, PA, Level 12 |
| 2016 | A01    | 61.13 | Hourly mean wage for financial managers, in Pittsburgh, PA, Level 11                                      |
| 2016 | A01    | 59.86 | Hourly mean wage for business and financial operations occupations, in Pittsburgh, PA, Level 12           |
+------+--------+-------+-----------------------------------------------------------------------------------------------------------+

About the data

Dataset Source: http://www.bls.gov/data/

Category: Economic

Use: This dataset is publicly available for anyone to use under the following terms provided by the Dataset Source - http://www.data.gov/privacy-policy#data_policy - and is provided "AS IS" without any warranty, express or implied, from Google. Google disclaims all liability for any damages, direct or indirect, resulting from the use of the dataset.

Update Frequency: Monthly

View in BigQuery: Go to Bureau of Labor Statistics Dataset

Send feedback about...