Skip to main content

Example: Combining Federal Reserve dataset with other data

This example shows how to create a REST API that uses data from open datasets.

info

If you are not familiar with RAW we recommend checking out our Getting started guide first. To use RAW, you need an account which you can create and use for free here.

The Federal Reserve (FRED) dataset​

The St. Louis Federal Reserve have a superb resource, called FRED, which you can read more about here or watch their video.

info

If you want to try this example, you can deploy the following endpoint:

Using free datasets from St. Louis Federal Reserve.
Using free datasets from St. Louis Federal Reserve and joining with other public data.

Usage:

/examples/fred

This is a fabulous resource of very broad and useful data for all types of use case. And, yes that’s not a typo – there are 816,000 datasets from 108 sources. See the main categories. It’s not all financial data, and not all US-based, so something for everyone.

Accessing FRED data​

So let’s read a dataset from the St. Louis Federal Reserve.

First thing is to create an account because you need to use an API Key to access the FRED API.

After registering with FRED, go to API Keys page and create an API key.

Refer to T&C’s too – should also say at this point: “This product uses the FRED® API but is not endorsed or certified by the Federal Reserve Bank of St. Louis.“

Using FRED data​

Now that we have an API key, we can read a dataset like this:

fredDataset(series_id: string) =
  let
      observationType = type record(
          count: int,
          offset: int,
          limit: int,
          observations: collection(record(date: date, value: string))
      )
  in
      Json.Read(
          Http.Get(
              "https://api.stlouisfed.org/fred/series/observations",
              args = [
                  {"series_id", series_id},
                  {"api_key",Environment.Secret("FRED-api-key")},
                  {"file_type", "json"}
              ]
          ),
          observationType
      )

Our call passes three URL arguments: series_id with the series identifier, api_key with the API key that we created before and file_type because we want to process JSON in this example.

Note the POPTOTUSA647NWDB dataset is the total population of the United States a list of available datasets can be found here. Also we have chosen to only show a minimal set of parameters, for the sake of brevity, but you can read the full FRED API documentation here.

Now we can do more interesting things using Snapi. Here we are joining two FRED datasets (US house price index, vs. Interest Rates, trimester):

let
    housePriceIndex = fredDataset("USSTHPI"),
    interestRates = fredDataset("INTDSRUSM193N")
in
    Collection.EquiJoin(
        Collection.Transform(housePriceIndex.observations, (x) -> {priceIndex: x.value, date: x.date}),
        Collection.Transform(interestRates.observations, (x) -> {interestRate: x.value, date: x.date}),
        (x) -> x.date,
        (y) -> y.date
    )

This is the output of the query:

[
  {
    "priceIndex": "60.04",
    "date": "1975-01-01",
    "interestRate": "7.4"
  },
  {
    "priceIndex": "60.99",
    "date": "1975-04-01",
    "interestRate": "6.25"
  },
  {
    "priceIndex": "61.17",
    "date": "1975-07-01",
    "interestRate": "6.0"
  },

Combining FRED data with other datasets​

We can also combine this FRED data to other sources of data too. The example below uses CO2 Emissions data from another great free data provider, OurWorldInData.Org (OWID).

Let's use a dataset from there to calculate the global CO2 usage per year. For this, we read a CSV file and use Collection.GroupBy and Collection.Sum to aggregate the data.

owidCo2PerYear() =
    let
        co2Data = Csv.InferAndRead(
            "https://raw.githubusercontent.com/owid/co2-data/master/owid-co2-data.csv",
            hasHeader = true
        ),
        co2PerYear = Collection.Transform(
            Collection.GroupBy(co2Data, (x) -> x.year),
            (x) -> {year: x.key, globalCo2: Collection.Sum(x.group.co2)}
        )
    in
        co2PerYear

This is the output of the function:

[
  {
    "year": 1750,
    "globalCo2": 56.106
  },
  {
    "year": 1751,
    "globalCo2": 56.106
  },
  {
    "year": 1752,
    "globalCo2": 56.123999999999995
  },
  {
    "year": 1753,
    "globalCo2": 56.123999999999995
  },

Putting it all together​

Finally, the query combining both datasets looks like this:

let
    usEmissions = fredDataset("EMISSCO2TOTVTTTOUSA").observations,    
    co2PerYear = owidCo2PerYear() ,
    joined = Collection.EquiJoin(
        usEmissions,
        co2PerYear,
        (x) -> Date.Year(x.date),
        (y) -> y.year
    )
in
    Collection.Transform(
        joined,
        (x) ->
            {
                year: x.year,
                usValue: Double.From(x.value),
                globalValue: x.globalCo2,
                usPercentage: 100 * Double.From(x.value) / x.globalCo2
            }
    )

This is the corresponding output, showing the global CO2 usage per year.

[
  {
    "year": 1970,
    "usValue": 4252.051087,
    "globalValue": 105312.40899999996,
    "usPercentage": 4.037559417143331
  },
  {
    "year": 1971,
    "usValue": 4297.212948,
    "globalValue": 109586.43699999992,
    "usPercentage": 3.921299994450959
  },
  {
    "year": 1972,
    "usValue": 4516.651775,
    "globalValue": 114517.28700000001,
    "usPercentage": 3.9440785695525604
  },
  {
    "year": 1973,
    "usValue": 4713.242489,
    "globalValue": 120503.95099999994,
    "usPercentage": 3.9112763107659454
  },

Ready to try it out?

Register for free and start building today!

Otherwise, if you have questions/comments, join us in our Community!