01-bq-data-prep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f0636aec-1d0f-4d15-adb2-2824738853b6",
   "metadata": {},
   "source": [
    "# TODOs\n",
    "* clean up notebook\n",
    "* parameterize\n",
    "* offer large and small options for producing dataset (create optionals)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf58742b-aabf-44e0-8610-f1778467c9f9",
   "metadata": {},
   "source": [
    "## Load env config"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "81503918-e4a9-437b-8711-6aed710b9276",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PREFIX = ndr-v1\n"
     ]
    }
   ],
   "source": [
    "# naming convention for all cloud resources\n",
    "VERSION        = \"v1\"                  # TODO\n",
    "PREFIX         = f'ndr-{VERSION}'      # TODO\n",
    "\n",
    "print(f\"PREFIX = {PREFIX}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b303bb1e-18f1-4940-924d-2686778aa891",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "PROJECT_ID               = \"hybrid-vertex\"\n",
      "PROJECT_NUM              = \"934903580331\"\n",
      "LOCATION                 = \"us-central1\"\n",
      "\n",
      "REGION                   = \"us-central1\"\n",
      "BQ_LOCATION              = \"US\"\n",
      "VPC_NETWORK_NAME         = \"ucaip-haystack-vpc-network\"\n",
      "\n",
      "VERTEX_SA                = \"934903580331-compute@developer.gserviceaccount.com\"\n",
      "\n",
      "PREFIX                   = \"ndr-v1\"\n",
      "VERSION                  = \"v1\"\n",
      "\n",
      "APP                      = \"sp\"\n",
      "MODEL_TYPE               = \"2tower\"\n",
      "FRAMEWORK                = \"tfrs\"\n",
      "DATA_VERSION             = \"v1\"\n",
      "TRACK_HISTORY            = \"5\"\n",
      "\n",
      "BUCKET_NAME              = \"ndr-v1-hybrid-vertex-bucket\"\n",
      "BUCKET_URI               = \"gs://ndr-v1-hybrid-vertex-bucket\"\n",
      "SOURCE_BUCKET            = \"spotify-million-playlist-dataset\"\n",
      "\n",
      "DATA_GCS_PREFIX          = \"data\"\n",
      "DATA_PATH                = \"gs://ndr-v1-hybrid-vertex-bucket/data\"\n",
      "VOCAB_SUBDIR             = \"vocabs\"\n",
      "VOCAB_FILENAME           = \"vocab_dict.pkl\"\n",
      "\n",
      "CANDIDATE_PREFIX         = \"candidates\"\n",
      "TRAIN_DIR_PREFIX         = \"train\"\n",
      "VALID_DIR_PREFIX         = \"valid\"\n",
      "\n",
      "VPC_NETWORK_FULL         = \"projects/934903580331/global/networks/ucaip-haystack-vpc-network\"\n",
      "\n",
      "BQ_DATASET               = \"spotify_e2e_test\"\n",
      "BQ_TABLE_TRAIN           = \"train_flatten_last_5\"\n",
      "BQ_TABLE_VALID           = \"train_flatten_valid_last_5\"\n",
      "BQ_TABLE_CANDIDATES      = \"candidates\"\n",
      "\n",
      "REPO_SRC                 = \"src\"\n",
      "PIPELINES_SUB_DIR        = \"feature_pipes\"\n",
      "\n",
      "REPOSITORY               = \"ndr-v1-spotify\"\n",
      "IMAGE_NAME               = \"train-v1\"\n",
      "REMOTE_IMAGE_NAME        = \"us-central1-docker.pkg.dev/hybrid-vertex/ndr-v1-spotify/train-v1\"\n",
      "DOCKERNAME               = \"tfrs\"\n",
      "\n",
      "SERVING_IMAGE_URI_CPU    = \"us-docker.pkg.dev/vertex-ai/prediction/tf2-cpu.2-11:latest\"\n",
      "SERVING_IMAGE_URI_GPU    = \"us-docker.pkg.dev/vertex-ai/prediction/tf2-gpu.2-11:latest\"\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# staging GCS\n",
    "GCP_PROJECTS             = !gcloud config get-value project\n",
    "PROJECT_ID               = GCP_PROJECTS[0]\n",
    "\n",
    "# GCS bucket and paths\n",
    "BUCKET_NAME              = f'{PREFIX}-{PROJECT_ID}-bucket'\n",
    "BUCKET_URI               = f'gs://{BUCKET_NAME}'\n",
    "\n",
    "config = !gsutil cat {BUCKET_URI}/config/notebook_env.py\n",
    "print(config.n)\n",
    "exec(config.n)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24f0eb21",
   "metadata": {},
   "source": [
    "#### Step 0: Dependencies\n",
    "\n",
    "Run this one time when starting, then restart the kernel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5cbb59bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !pip install pandas pandas-gbq==0.12.0 --user"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68da0ab1",
   "metadata": {},
   "source": [
    "# Data prep\n",
    "\n",
    "## In this notebook we will load the songs from the zip file, and perform transformations to prepare the data for two-tower training\n",
    "Steps\n",
    "1. Extract from the zip file\n",
    "2. Upload to BQ\n",
    "3. Enrich features for the playlist songs\n",
    "4. Cross-join songs with features (expected rows = n_songs x n_playlists)\n",
    "5. Remove after-the-fact (later position songs) from the newly generated samples\n",
    "6. Create a clean train table, and flatten structs or use arrays"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e04cd08",
   "metadata": {},
   "source": [
    "#### Unzip the file and upload to BQ\n",
    "Source of data if you want to download zip: gs://spotify-million-playlist-dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "77d08efb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Set your variables for your project, region, and dataset name\n",
    "# SOURCE_BUCKET = 'spotify-million-playlist-dataset'\n",
    "# PROJECT_ID = 'hybrid-vertex'\n",
    "# REGION = 'us-central1'\n",
    "# BQ_DATASET = 'spotify_e2e_test'\n",
    "\n",
    "import time\n",
    "from google.cloud import bigquery\n",
    "\n",
    "bigquery_client = bigquery.Client(project=PROJECT_ID, location=BQ_LOCATION)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb54db8c-22b2-4ba1-94cb-502f2a0ab198",
   "metadata": {},
   "source": [
    "### Specify BQ Dataset created in `00-env-setup.ipynb`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "92c0e0b6-0bd4-42bd-bd42-b722c0d871d5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Dataset(DatasetReference('`hybrid-vertex', 'spotify_e2e_test`'))"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # Create a bigquery dataset (one time operation)\n",
    "# # Construct a full Dataset object to send to the API.\n",
    "dataset = bigquery.Dataset(f\"`{PROJECT_ID}.{BQ_DATASET}`\")\n",
    "dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "409cabe2-16c1-4131-b298-82c7078b53ba",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Next create unique artist and song tables\n",
    "These tables contain features obtained via the public Spotify API. Features such as track and artist popularity are in this data. For more detail on loading json data to Bigquery, [see here](https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-json)\n",
    "\n",
    "![](img/unique-songs.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e866e7b6-0b6c-4d1e-9d7f-73c99c3a3a0b",
   "metadata": {},
   "source": [
    "### Unique artists\n",
    "\n",
    "![](img/unique-artists.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5550756a-7a9e-4ce7-ada5-6f109ac09839",
   "metadata": {},
   "source": [
    "##### The data is now in BQ"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f0bc92a-b54b-4600-8bf9-a289e098a6b9",
   "metadata": {},
   "source": [
    "## The tables are set for feature enrichment\n",
    "We will visit these tables later, now let's load the Million Playlist dataset locally and push bq using `pandas-gbq` (see requirements installation at the top)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8fd3725a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# !gsutil cp gs://{SOURCE_BUCKET}spotify_million_playlist_dataset.zip .\n",
    "# !unzip spotify_million_playlist_dataset.zip"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "420750c4",
   "metadata": {},
   "source": [
    "#### This step can take up to 30 minutes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0888f988",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "import os\n",
    "import json\n",
    "import pandas as pd\n",
    "data_files = os.listdir('data')\n",
    "\n",
    "#make sure there is not already existing data in the playlists table\n",
    "#loops over json files - converts to pandas then upload/appends\n",
    "for filename in data_files:\n",
    "    with open(f'data/{filename}') as f:\n",
    "        json_dict = json.load(f)\n",
    "        df = pd.DataFrame(json_dict['playlists'])\n",
    "        df.to_gbq(\n",
    "        destination_table=f'{BQ_DATASET}.playlists', \n",
    "        project_id=PROJECT_ID, # TODO: param\n",
    "        location=REGION, \n",
    "        progress_bar=False, \n",
    "        reauth=True, \n",
    "        if_exists='append'\n",
    "        ) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74a96366-cab1-408f-af19-4539ba1a890e",
   "metadata": {},
   "source": [
    "Now the data is loaded but the playlists are nested as one large string that needs to be parsed - we will use json compatible functionality with BigQuery to address\n",
    "\n",
    "![](img/tracks-string.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "338b113f",
   "metadata": {},
   "source": [
    "### Import bigquery and run parameterized queries to shape the data\n",
    "\n",
    "This query formats the json strings to be read as Bigquery structs, to be manipulated in subsequent queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53e9b7e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "JSON_EXTRACT_QUERY = f\"\"\"\n",
    "  CREATE OR REPLACE TABLE `{PROJECT_ID}.{BQ_DATASET}.playlists_nested` as (\n",
    "    WITH json_parsed as (\n",
    "      SELECT * except(tracks), JSON_EXTRACT_ARRAY(tracks) as json_data \n",
    "    FROM `{PROJECT_ID}.{BQ_DATASET}.playlists` \n",
    "    )\n",
    "\n",
    "    SELECT json_parsed.* except(json_data),\n",
    "      ARRAY(\n",
    "         SELECT AS STRUCT\n",
    "          JSON_EXTRACT_SCALAR(json_data, \"$.pos\") as pos, \n",
    "          JSON_EXTRACT_SCALAR(json_data, \"$.artist_name\") as artist_name,\n",
    "          JSON_EXTRACT_SCALAR(json_data, \"$.track_uri\") as track_uri,\n",
    "          JSON_EXTRACT_SCALAR(json_data, \"$.artist_uri\") as artist_uri,\n",
    "          JSON_EXTRACT_SCALAR(json_data, \"$.track_name\") as track_name,\n",
    "          JSON_EXTRACT_SCALAR(json_data, \"$.album_uri\") as album_uri,\n",
    "          JSON_EXTRACT_SCALAR(json_data, \"$.duration_ms\") as duration_ms,\n",
    "          JSON_EXTRACT_SCALAR(json_data, \"$.album_name\") as album_name\n",
    "        FROM json_parsed.json_data\n",
    "      ) as tracks,\n",
    "    FROM json_parsed\n",
    "    ) \n",
    "\"\"\"\n",
    "\n",
    "# print(JSON_EXTRACT_QUERY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1928193-1113-4857-817e-a224cb79b349",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "bigquery_client.query(JSON_EXTRACT_QUERY).result()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f3f27fb-b56d-4759-ab10-e28c0d2c186f",
   "metadata": {},
   "source": [
    "Now `playlists_nested` has parsed the string data to a struct with arrays that will allow us to process the data much more easily\n",
    "\n",
    "![](img/playlists-nested.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7543e5fe-d7d4-447c-9430-5ffa1575cb7f",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Next we get the unique track features to put in a BQ table\n",
    "\n",
    "This table will then be used to call the Spotify API and enrich with additional data about each track and artist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a9761d5-3554-4a92-a05a-b167dec4f5c0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 12.7 ms, sys: 757 µs, total: 13.4 ms\n",
      "Wall time: 10.7 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<google.cloud.bigquery.table._EmptyRowIterator at 0x7fa5568438d0>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "UNIQUE_TRACKS_QUERY = f\"\"\"\n",
    "CREATE OR REPLACE TABLE `{PROJECT_ID}.{BQ_DATASET}.tracks_unique` as (\n",
    "  SELECT distinct \n",
    "    track.track_uri,\n",
    "    track.album_uri,\n",
    "    track.artist_uri, \n",
    "  FROM `{PROJECT_ID}.{BQ_DATASET}.playlists_nested`, UNNEST(tracks) as track\n",
    ")\n",
    "\"\"\"\n",
    "\n",
    "# print(UNIQUE_TRACKS_QUERY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76c6a937-03c2-46b4-a353-9e7919d5953f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "bigquery_client.query(UNIQUE_TRACKS_QUERY).result()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2890a40",
   "metadata": {},
   "source": [
    "## Now enrich the playlist songs with the new features\n",
    "\n",
    "> Note: these tables are not included in the Spotify MPD. See `00-load-core-data-to-bq.ipynb` \n",
    "\n",
    "**New tables**\n",
    "* `audio_features` - created from prior notebook via Spotify API\n",
    "* `artist_features` - created from prior notebook via Spotify API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "b3066d45",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 22.1 ms, sys: 0 ns, total: 22.1 ms\n",
      "Wall time: 33.8 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<google.cloud.bigquery.table._EmptyRowIterator at 0x7f84e9bfa910>"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ENRICH_QUERY = f\"\"\"\n",
    "CREATE OR REPLACE TABLE\n",
    "  `{PROJECT_ID}.{BQ_DATASET}.enriched_data` AS (\n",
    "  WITH tf as (SELECT distinct * from `{PROJECT_ID}.{BQ_DATASET}.audio_features`),\n",
    "       af as (SELECT distinct * from `{PROJECT_ID}.{BQ_DATASET}.artist_features`) \n",
    "\n",
    "    SELECT\n",
    "    a.* except(tracks),\n",
    "      ARRAY(\n",
    "    SELECT\n",
    "      AS STRUCT CAST(track.pos AS int64) AS pos_can,\n",
    "      case when track.artist_name = '' then 'NONE' else track.artist_name end AS artist_name_can,\n",
    "      case when track.track_uri = '' then 'NONE' else track.track_uri  end AS track_uri_can,\n",
    "      case when track.album_uri = '' then 'NONE' else track.album_uri  end AS album_uri_can,\n",
    "      case when track.artist_uri = '' then 'NONE' else track.artist_uri  end AS artist_uri_can,\n",
    "      case when track.track_name = '' then 'NONE' else track.track_name end AS track_name_can,\n",
    "      CAST(track.duration_ms AS float64) / 1.0 AS duration_ms_can,\n",
    "      case when track.album_name = '' then 'NONE' else track.album_name end AS album_name_can,\n",
    "      CAST(IFNULL(tf.track_pop, 0.0) as float64) / 1.0 AS track_pop_can,\n",
    "      CAST(IFNULL(af.artist_pop, 0.0) as float64) / 1.0  AS artist_pop_can,\n",
    "      case when \n",
    "        ARRAY(SELECT * FROM UNNEST(SPLIT(SUBSTR(genres, 2 , LENGTH(genres) - 2))))[OFFSET(0)] = '' \n",
    "      then \n",
    "        ['NONE'] else ARRAY(SELECT * FROM UNNEST(SPLIT(SUBSTR(genres, 2 , LENGTH(genres) - 2)))) end AS artist_genres_can,\n",
    "      CAST(IFNULL(af.followers, 0.0) as float64) / 1.0 AS artist_followers_can\n",
    "    FROM\n",
    "      UNNEST(tracks) as track\n",
    "    INNER JOIN\n",
    "     tf --track features\n",
    "    ON\n",
    "      (track.track_uri = tf.track_uri)\n",
    "    INNER JOIN\n",
    "      af\n",
    "      ON\n",
    "      (track.artist_uri = af.artist_uri)\n",
    "      ) AS tracks\n",
    "  FROM \n",
    "  `{PROJECT_ID}.{BQ_DATASET}.playlists_nested` as a\n",
    "  )\n",
    "\"\"\"\n",
    "\n",
    "# print(ENRICH_QUERY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea4de97c-824f-478a-bc6e-ef34d6a674a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "bigquery_client.query(ENRICH_QUERY).result()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d933a3b9",
   "metadata": {},
   "source": [
    "## Cross join + get rid of after-the-fact `pos` data in playlist\n",
    "\n",
    "cross_join_songxplaylist_struct_query\n",
    "\n",
    "`hybrid-vertex.spotify_train_3.ordered_position_training`\n",
    "\n",
    "We create a data structure that creates unique song-playlist combos (every possible via cross-join). There is also a portion of pulling the last song in the playlist as the \"seed track\"\n",
    "________\n",
    "### Note on the approach\n",
    "\n",
    "Semantic matching requires pairs, triplets (tuples generally) of co-occurrences between pairs. This is a very broad definition, and with this newer approach many new use cases are being explored. A simple example are finding pairs of user queries and purchases. The training example pair are: (the features we know from the user query, the features we know on the product they ultimately purchased).\n",
    "\n",
    "There are other approaches where triples are considered, and there are advanced techniques on negative sampling, finding “bad” examples of query, product pairs, which we will not cover here.\n",
    "\n",
    "Note there are other sampling techniques we highlight below (different artist/album)\n",
    "\n",
    "The chosen task was predicting the next song on a playlist, given the playlist existing order. The approach taken was to create pairs for all children songs and their parent playlists. We did leveraging BigQuery’s `UNNEST` and `CROSS JOIN`. \n",
    "\n",
    "We also had rich features for playlists, albums and songs in another table that was later used to enrich post `CROSS JOIN`. This was done to optimize the computation since the cross-joining is expensive and it was subsequently much quicker to enrich after this step.\n",
    "\n",
    "Now that we completed this step, we had all combinations of child song, playlist pairs. The song was the candidate label but the playlist still contained the candidate label and all songs after. Additional criteria was added to remove the candidate song and all songs that occur after the candidate in the playlist. For the sake of performance we also only considered the last 5 played songs. Other sampling configurations are available in the example notebook as well (only predicting when there are album and artist switches).\n",
    "\n",
    "What this results in is a training dataset that has all possible child song candidates joined with the full playlist data, and the playlist data is properly censored as to only contain songs up to before the candidate song.\n",
    "\n",
    "![](img/semantic-pair.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e0e136cb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 37.7 ms, sys: 94 µs, total: 37.8 ms\n",
      "Wall time: 1min 46s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<google.cloud.bigquery.table._EmptyRowIterator at 0x7f84ea103f10>"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CROSS_JOIN_QUERY = f\"\"\"\n",
    "  CREATE OR REPLACE TABLE\n",
    "  `{PROJECT_ID}.{BQ_DATASET}.ordered_position_training` AS (\n",
    "  WITH\n",
    "    -- get every combination of song and its parent playlist\n",
    "    unnest_cross AS(\n",
    "    SELECT\n",
    "      b.*,\n",
    "      CONCAT(b.pid,\"-\",track.pos_can) AS pid_pos_id,\n",
    "      CAST(track.pos_can AS int64) AS pos_can,\n",
    "      IFNULL(track.artist_name_can, \"NONE\") as artist_name_can ,\n",
    "      track.track_uri_can ,\n",
    "      track.album_uri_can,\n",
    "      IFNULL(track.track_name_can, \"NONE\") as track_name_can ,\n",
    "      track.artist_uri_can ,\n",
    "      CAST(track.duration_ms_can AS float64) AS duration_ms_can,\n",
    "      track.album_name_can ,\n",
    "      track.track_pop_can ,\n",
    "      track.artist_pop_can,\n",
    "      ARRAY_TO_STRING(track.artist_genres_can, ',', 'MISSING') as artist_genres_can ,\n",
    "      track.artist_followers_can \n",
    "    FROM (\n",
    "      SELECT\n",
    "        * EXCEPT(duration_ms)\n",
    "      FROM\n",
    "        `{PROJECT_ID}.{BQ_DATASET}.enriched_data`) AS b\n",
    "    CROSS JOIN\n",
    "      UNNEST(tracks) AS track)\n",
    "  SELECT\n",
    "    a.* EXCEPT(tracks,\n",
    "      num_tracks,\n",
    "      num_artists,\n",
    "      num_albums,\n",
    "      num_followers,\n",
    "      num_edits),\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      AS STRUCT CAST(track.pos_can AS int64) AS pos_pl,\n",
    "      track.artist_name_can AS artist_name_pl,\n",
    "      track.track_uri_can AS track_uri_pl,\n",
    "      track.track_name_can AS track_name_pl,\n",
    "      track.album_uri_can AS album_uri_pl,\n",
    "      track.artist_uri_can AS artist_uri_pl,\n",
    "      CAST(track.duration_ms_can AS float64) AS duration_ms_pl,\n",
    "      track.album_name_can AS album_name_pl,\n",
    "      track.track_pop_can AS track_pop_pl,\n",
    "      track.artist_pop_can AS artist_pop_pl,\n",
    "      ARRAY_TO_STRING(track.artist_genres_can, ',', 'MISSING') AS artist_genres_pl,\n",
    "      track.artist_followers_can AS artist_followers_pl,\n",
    "    FROM\n",
    "      UNNEST(tracks) AS track\n",
    "    WHERE\n",
    "      CAST(track.pos_can AS int64) < a.pos_can ORDER BY CAST(track.pos_can AS int64)) AS seed_playlist_tracks\n",
    "  FROM\n",
    "    unnest_cross AS a -- with statement\n",
    "  )\n",
    "\"\"\"\n",
    "\n",
    "# print(CROSS_JOIN_QUERY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66afcf3e-e8cc-4194-aba2-dd3c8a9a289a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "bigquery_client.query(CROSS_JOIN_QUERY).result()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e48a1393",
   "metadata": {},
   "source": [
    "## Update the playlist metadata with the new samples created above\n",
    "\n",
    "Add audio features from the tracks\n",
    "\n",
    "Get new metadata for the tracks now that there are updated track counts, durations, etc...\n",
    "\n",
    "`hybrid-vertex.spotify_train_3.train` will be produced\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "e7985b2a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 38.8 ms, sys: 607 µs, total: 39.4 ms\n",
      "Wall time: 1min 54s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<google.cloud.bigquery.table._EmptyRowIterator at 0x7f84ea1c6850>"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "GET_NEW_METADATA_QUERY = f\"\"\"\n",
    "    CREATE OR REPLACE TABLE `{PROJECT_ID}.{BQ_DATASET}.train` as (\n",
    "    WITH\n",
    "      playlist_features_clean AS (\n",
    "      SELECT\n",
    "        pid_pos_id,\n",
    "        SUM(trx.duration_ms_pl) / 1.0 AS duration_ms_seed_pl,\n",
    "        COUNT(1) / 1.0 AS n_songs_pl,\n",
    "        COUNT(DISTINCT trx.artist_name_pl) / 1.0 AS num_artists_pl,\n",
    "        COUNT(DISTINCT trx.album_uri_pl) /1.0 AS num_albums_pl,\n",
    "      FROM\n",
    "        `{PROJECT_ID}.{BQ_DATASET}.ordered_position_training`,\n",
    "        UNNEST(seed_playlist_tracks) AS trx\n",
    "      GROUP BY\n",
    "        pid_pos_id\n",
    "        )\n",
    "    SELECT\n",
    "      a.* except(artist_genres_can, track_pop_can, artist_pop_can, artist_followers_can),\n",
    "      b.* except(pid_pos_id),\n",
    "      a.artist_genres_can,\n",
    "      IFNULL(a.track_pop_can, 0.0) / 1.0 as  track_pop_can, \n",
    "      IFNULL(a.artist_pop_can, 0.0) / 1.0 as artist_pop_can,\n",
    "      IFNULL(a.artist_followers_can, 0.0) / 1.0 as artist_followers_can,\n",
    "\n",
    "    FROM\n",
    "      `{PROJECT_ID}.{BQ_DATASET}.ordered_position_training` a\n",
    "    INNER JOIN\n",
    "      playlist_features_clean b\n",
    "    ON\n",
    "      a.pid_pos_id = b.pid_pos_id \n",
    "    )\n",
    "\"\"\"\n",
    "\n",
    "# print(GET_NEW_METADATA_QUERY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3be958a0-abd4-4128-8054-5dc5c58cece1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "bigquery_client.query(GET_NEW_METADATA_QUERY).result()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "19113617-7da4-4c9d-a18e-a54b414abe79",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 52.2 ms, sys: 161 µs, total: 52.4 ms\n",
      "Wall time: 15.7 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<google.cloud.bigquery.table._EmptyRowIterator at 0x7f84e9904710>"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Get candidates\n",
    "\n",
    "GET_UNIQUE_CANDIDATES_QUERY = f\"\"\"\n",
    "CREATE OR REPLACE TABLE `{PROJECT_ID}.{BQ_DATASET}.{BQ_TABLE_CANDIDATES}` AS (\n",
    "    WITH\n",
    "      af as (SELECT DISTINCT * FROM `{PROJECT_ID}.{BQ_DATASET}.audio_features`)\n",
    "\n",
    "    SELECT DISTINCT\n",
    "        track_uri_can,\n",
    "        track_name_can,\n",
    "        artist_uri_can,\n",
    "        artist_name_can,\n",
    "        album_uri_can,\n",
    "        album_name_can,\n",
    "        duration_ms_can,\n",
    "        track_pop_can,\n",
    "        artist_pop_can,\n",
    "        artist_genres_can,\n",
    "        artist_followers_can,\n",
    "\n",
    "        IFNULL(af.danceability, 0.) as track_danceability_can,\n",
    "        IFNULL(af.energy, 0.) as track_energy_can,\n",
    "        IFNULL(af.key, 0.) as track_key_can,\n",
    "        IFNULL(af.loudness, 0.) as track_loudness_can,\n",
    "        IFNULL(af.mode, 0) as track_mode_can,\n",
    "        IFNULL(af.speechiness, 0.) as track_speechiness_can,\n",
    "        IFNULL(af.acousticness, 0.) as track_acousticness_can,\n",
    "        IFNULL(af.instrumentalness, 0.) as track_instrumentalness_can,\n",
    "        IFNULL(af.liveness, 0.) as track_liveness_can,\n",
    "        IFNULL(af.valence, 0.) as track_valence_can,\n",
    "        IFNULL(af.tempo, 0.) as track_tempo_can,\n",
    "        IFNULL(af.time_signature, 0) as time_signature_can,\n",
    "    FROM\n",
    "      `{PROJECT_ID}.{BQ_DATASET}.train` a\n",
    "       inner join af on af.track_uri = a.track_uri_can\n",
    "       \n",
    "    )\n",
    "\"\"\"\n",
    "\n",
    "# print(GET_UNIQUE_CANDIDATES_QUERY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be59c72c-59cf-44e0-a46d-16635a3e1637",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "bigquery_client.query(GET_UNIQUE_CANDIDATES_QUERY).result()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4517477d",
   "metadata": {},
   "source": [
    "## For TFRecords\n",
    "Get rid of structs by creating new table with arrays from playlist_seed\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65bcaf70-5e96-435e-a888-ae832dc297aa",
   "metadata": {},
   "source": [
    "# Only selecting last 5 songs\n",
    "\n",
    "song_history is settable but it will impact `MAX_PLAYLIST_LENGTH` in `src/two_tower.py`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c1381148-0195-481e-a9a9-ae0188aee358",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "TRAIN_FLATTEN_QUERY_a = f\"\"\"\n",
    "CREATE OR REPLACE TABLE\n",
    "  `{PROJECT_ID}.{BQ_DATASET}.train_flatten_pre_split_a` AS (\n",
    "  WITH\n",
    "    audio AS (\n",
    "    SELECT\n",
    "      DISTINCT *\n",
    "    FROM\n",
    "      `{PROJECT_ID}.{BQ_DATASET}.audio_features`)\n",
    "  SELECT\n",
    "    pid,\n",
    "    IFNULL(a.name, \"\") AS pl_name_src,\n",
    "    collaborative AS pl_collaborative_src,\n",
    "    duration_ms_seed_pl AS pl_duration_ms_new,\n",
    "    n_songs_pl AS num_pl_songs_new,\n",
    "    num_artists_pl AS num_pl_artists_new,\n",
    "    num_albums_pl AS num_pl_albums_new,\n",
    "    track_uri_can,\n",
    "    track_name_can,\n",
    "    artist_uri_can,\n",
    "    artist_name_can,\n",
    "    album_uri_can,\n",
    "    album_name_can,\n",
    "    duration_ms_can,\n",
    "    track_pop_can,\n",
    "    artist_pop_can,\n",
    "    artist_genres_can,\n",
    "    artist_followers_can,\n",
    "    IFNULL(audio.danceability, 0.0) AS track_danceability_can,\n",
    "    IFNULL(audio.energy, 0.0) AS track_energy_can,\n",
    "    IFNULL(audio.key, 0.0) AS track_key_can,\n",
    "    IFNULL(audio.loudness, 0.0) AS track_loudness_can,\n",
    "    IFNULL(audio.mode, 0) AS track_mode_can,\n",
    "    IFNULL(audio.acousticness, 0.0) AS track_acousticness_can,\n",
    "    IFNULL(audio.instrumentalness, 0.0) AS track_instrumentalness_can,\n",
    "    IFNULL(audio.liveness, 0.0) AS track_liveness_can,\n",
    "    IFNULL(audio.speechiness, 0.0) AS track_speechiness_can,\n",
    "    IFNULL(audio.valence, 0.0) AS track_valence_can,\n",
    "    IFNULL(audio.tempo, 0.0) AS track_tempo_can,\n",
    "    IFNULL(audio.time_signature, 0) AS track_time_signature_can,\n",
    "    ARRAY(\n",
    "      SELECT CAST(pos_can - pos_pl) AS FLOAT64\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE \n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS candidate_rank,\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      t.artist_name_pl\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE\n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS artist_name_pl,\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      t.artist_uri_pl\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE\n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS artist_uri_pl,\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      t.track_uri_pl\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE\n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS track_uri_pl,\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      t.track_name_pl\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE\n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS track_name_pl,\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      t.duration_ms_pl\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE\n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS duration_ms_songs_pl,\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      t.album_name_pl\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE\n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS album_name_pl,\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      t.album_uri_pl\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE\n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS album_uri_pl,\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      CAST(t.artist_pop_pl AS FLOAT64)\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE\n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS artist_pop_pl,\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      t.artist_followers_pl\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE\n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS artists_followers_pl,\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      t.track_pop_pl\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE\n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS track_pop_pl,\n",
    "    ARRAY(\n",
    "    SELECT\n",
    "      t.artist_genres_pl\n",
    "    FROM\n",
    "      UNNEST(seed_playlist_tracks) t\n",
    "    WHERE\n",
    "      pos_pl >= pos_can - {TRACK_HISTORY}) AS artist_genres_pl\n",
    "  FROM\n",
    "    `{PROJECT_ID}.{BQ_DATASET}.train` a\n",
    "  INNER JOIN\n",
    "    audio\n",
    "  ON\n",
    "    audio.track_uri = a.track_uri_can \n",
    ")\n",
    "\"\"\"\n",
    "\n",
    "print(TRAIN_FLATTEN_QUERY_a)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5c2999a0-72e3-44b2-80a4-fa0d3ec718e8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 65 ms, sys: 6.87 ms, total: 71.9 ms\n",
      "Wall time: 58 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<google.cloud.bigquery.table._EmptyRowIterator at 0x7f0e1ee64d50>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "bigquery_client.query(TRAIN_FLATTEN_QUERY_a).result()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85f4d75b-48aa-42d9-abfd-7c8620d85a5e",
   "metadata": {},
   "source": [
    "#### Append the audio features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2b96beea-92fa-4c74-9a10-913185d54f2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "TRAIN_FLATTEN_QUERY_b = f\"\"\"\n",
    "CREATE OR REPLACE TABLE \n",
    "`{PROJECT_ID}.{BQ_DATASET}.train_flatten_pre_split_b` as (\n",
    "WITH \n",
    "  audio as (SELECT DISTINCT * FROM `{PROJECT_ID}.{BQ_DATASET}.audio_features`)\n",
    "SELECT \n",
    "    a.*,\n",
    "    ARRAY(select IFNULL(audio.danceability, 0.0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_danceability_pl,\n",
    "    ARRAY(select IFNULL(audio.energy, 0.0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_energy_pl,\n",
    "    ARRAY(select IFNULL(audio.key, 0.0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_key_pl,\n",
    "    ARRAY(select IFNULL(audio.loudness, 0.0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_loudness_pl,\n",
    "    ARRAY(select IFNULL(audio.mode, 0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_mode_pl,\n",
    "    ARRAY(select IFNULL(audio.acousticness, 0.0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_acousticness_pl,\n",
    "    ARRAY(select IFNULL(audio.instrumentalness, 0.0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_instrumentalness_pl,\n",
    "    ARRAY(select IFNULL(audio.liveness, 0.0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_liveness_pl,\n",
    "    ARRAY(select IFNULL(audio.valence, 0.0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_valence_pl,\n",
    "    ARRAY(select IFNULL(audio.tempo, 0.0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_tempo_pl,\n",
    "    ARRAY(select IFNULL(audio.time_signature, 0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_time_signature_pl,\n",
    "    ARRAY(select IFNULL(audio.speechiness, 0) from UNNEST(a.track_uri_pl) t, audio where audio.track_uri = t) as track_speechiness_pl\n",
    "    from `{PROJECT_ID}.{BQ_DATASET}.train_flatten_pre_split_a` a\n",
    "  WHERE\n",
    "     ARRAY_LENGTH(a.track_uri_pl) = {TRACK_HISTORY}\n",
    ") --limiting here for performance\n",
    "\"\"\"\n",
    "\n",
    "# print(TRAIN_FLATTEN_QUERY_b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e07c1792-f174-4a5b-9474-9eecf71b34f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "bigquery_client.query(TRAIN_FLATTEN_QUERY_b).result()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "793b5571-175c-44ac-b8bf-1df0ce8c5160",
   "metadata": {},
   "source": [
    "## Important for validation strategy\n",
    "Different playlist ids were selected for validation to prevent cross-contamination with the sampling approach."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "61815583-3efe-4073-9e6b-a9a95aeac793",
   "metadata": {},
   "outputs": [],
   "source": [
    "# VALIDATION_P = 0.01"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "0a08d4f1-ea60-46b5-b520-1d61ec0e38ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "VALIDATION_DATA_QUERY = f\"\"\"\n",
    "CREATE OR REPLACE TABLE\n",
    "  `{PROJECT_ID}.{BQ_DATASET}.{BQ_TABLE_VALID}` AS (\n",
    "    SELECT * \n",
    "  FROM\n",
    "    `{PROJECT_ID}.{BQ_DATASET}.train_flatten_pre_split_b` where MOD(pid, 100) = 0\n",
    "    AND ARRAY_LENGTH(track_uri_pl) = {TRACK_HISTORY})\"\"\" #complete examples only\n",
    "\n",
    "# print(VALIDATION_DATA_QUERY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "14c24f2a-0b37-481f-a782-0c6d38300a0e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 16.4 ms, sys: 2.24 ms, total: 18.6 ms\n",
      "Wall time: 26.2 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<google.cloud.bigquery.table._EmptyRowIterator at 0x7f0e1f334a90>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "bigquery_client.query(VALIDATION_DATA_QUERY).result()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c5db82a0-253c-4f84-bab3-d087888ab30b",
   "metadata": {},
   "outputs": [],
   "source": [
    "TRAIN_DATA_QUERY = f\"\"\"\n",
    "CREATE OR REPLACE TABLE\n",
    "  `{PROJECT_ID}.{BQ_DATASET}.{BQ_TABLE_TRAIN}` AS (\n",
    "    SELECT * \n",
    "  FROM\n",
    "    `{PROJECT_ID}.{BQ_DATASET}.train_flatten_pre_split_b` where MOD(pid, 100) != 0\n",
    "    AND ARRAY_LENGTH(track_uri_pl) = {TRACK_HISTORY})\"\"\"\n",
    "\n",
    "# print(TRAIN_DATA_QUERY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "fd8c01f9-73f7-440e-9066-0f7b7e26f63f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 34.1 ms, sys: 977 µs, total: 35.1 ms\n",
      "Wall time: 1min 29s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<google.cloud.bigquery.table._EmptyRowIterator at 0x7f0e1f33a610>"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "bigquery_client.query(TRAIN_DATA_QUERY).result()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac9bc3ad-35a2-4e14-b946-5faf7e14dca1",
   "metadata": {},
   "source": [
    "## Done - you can move on to the [next notebook](02-tfrecord-beam-pipeline.ipynb) \n",
    "\n",
    "Your data should look like this:\n",
    "    \n",
    "![](img/train-dataset-metadata.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c01cd3bc-3096-486c-bf7b-013490643fb9",
   "metadata": {},
   "source": [
    "**Finished**"
   ]
  }
 ],
 "metadata": {
  "environment": {
   "kernel": "python3",
   "name": "tf2-gpu.2-10.m103",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/tf2-gpu.2-10:m103"
  },
  "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.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}