I wanted to be able to work on a project that utilized data available from an API and working on formatting into a more end user friendly format. I started a playthrough of Pokémon Fire Red for the Gameboy Advance as my COVID downtime activity and thought a tool to compare Pokémon stats would be nice. It's been a minute since I last played a Pokémon game so it would be nice to have something I can use to remind me of the difference stat values between stats Pokémon .

I extracted data from the Pokémon API by sending GET requests for each of the 151 Pokémon available in the version of the game I am playing. I saved the jsons and got to work in AWS Glue and set up a workflow to crawl the json files and relationalize the data to query in AWS Athena. I queried the data from Athena and exported the CSV of the data so that Tableau could easily read and use the data for the dashboard. In the data prep session below, I'll go into detail how I performed the transformation to get the data into Tableau.

Immediately below this paragraph, you'll see the dashboard I created on Tableau. To use the tool, all you have to do is select the two Pokémon you want to compare. On the left next to the picture of Blastoise, select the first Pokémon you want to compare (default is set to Blastoise) and on the right next to Charizard, select the second Pokémon you want to compare. Note that the sprite pictures will update when you select different Pokémon. The center graphs will update with the distributions of their six key stats. A summary of the stats and descriptions of each Pokémon can be found below the selection filter and sprite picture.

Data Prep

I've saved the files in this github for reference purposes. Here's a quick rundown of the files you will find:

• /gluescript/pokemon_to_parquet.py - The AWS Glue pySpark script to take the json data and convert into parquet.
• /rawjson/* - The compressed json files from the Pokémon API for all 151 Pokémon
• pokemon.yaml - The CloudFormation template you can use to launch the stack with all AWS resources needed to store the data, authorize Glue and run Glue Workflows (including crawlers and jobs)

There's probably numerous ways to get Pokémon data from but I figured I would try and get data from the Pokémon API and sharpen my AWS and pySpark skills. I basically called the API for all the Pokémon and saved the results in a compressed text file.

If you want to use the data in the github repo, feel free to run these commands using the AWS CLI:

aws s3 cp rawjson/ s3://bucketname/rawjson/ --recursive
aws s3 cp gluescript/ s3://bucketname/gluescript/ --recursive

In the commands above, the bucketname is the bucket name you specified in the BucketName parameter in the CloudFormation template. This will copy the data to your bucket quickly. The second command is to load the gluescript where Glue can retrieve and assign it to the job you created when you launched the stack.

The stack will launch a Glue Workflow that follows basically three steps:

1. Crawl the location where the jsons are stored and create the table in the Data Catalog to use in the Glue Jobs.
2. Run the Glue Job to relationalize nested data within the json files and save data into parquet files.
3. Crawl the location where the parquet files to create the tables in the Data Catalog that you can use to query the data in AWS Athena.

The key to the Glue Job is the relationalize portion that takes the nested data and will create out tables so you can have several flat tables that you can join on ID fields similar to a familiar star schema. The jsons of the Pokémon data are full of nested arrays (see below).

After the workflow completes, I simply ran a query in AWS Athena to get the data and save the results to CSV and loaded into Tableau Public. I use Tableau Public for my personal analysis but with enterprise Tableau Solutions, you can automate these data pulls to take the manual exports out of it. Below is the query I ran. Note that mypokemondb was the database I created in my CloudFormation stack where the crawlers will associate the tables. You should fill this with the DBName parameter you specified when creating the stack. I mapped to pivot some of the fields but ended up pivoting the table back in Tableau itself:

WITH pokemon_stats as
(
  SELECT
    id,
    stat['special-attack'] as stat_special_attack,
    stat['defense'] as stat_defense,
    stat['attack'] as stat_attack,
    stat['hp'] as stat_hp,
    stat['special-defense'] as stat_special_defense,
    stat['speed'] as stat_speed
  FROM
    (
      SELECT
        id,
        map_agg("stats.val.stat.name", "stats.val.base_stat") stat
      FROM mypokemondb.pokemon_main_stats
      GROUP BY id
    )
  )
, pokemon_types as
(
  SELECT
    id,
    types[1] as type1,
    types[2] as type2
  FROM
    (
      SELECT
      id,
      map_agg("types.val.slot", "types.val.type.name") as types
      FROM mypokemondb.pokemon_main_types
      GROUP BY id
    )
 )


SELECT *
FROM mypokemondb.pokemon_main pkm
LEFT JOIN pokemon_stats pks ON pks.id = pkm.stats
LEFT JOIN pokemon_types pkt ON pkt.id = pkm.types
ORDER BY pkm.id

That's basically it! I hope you found this first post interesting. I'll be playing more with AWS data services and Tableau and posting anything I come up with here.