itunes_navidrome_syncup.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "016bcdf3-c102-44de-a3b6-483b266adc44",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from libpytunes import Library\n",
    "import mutagen\n",
    "import os\n",
    "import sqlalchemy\n",
    "import uuid\n",
    "from datetime import datetime\n",
    "import shutil"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cdc8f0ff-9425-4af1-9497-f0e162f8bff4",
   "metadata": {},
   "outputs": [],
   "source": [
    "ITUNES_LIBRARY_LOCATION = r\"C:/Users/rdoge/Music/iTunes/iTunes Music Library.xml\"\n",
    "OLD_NAVIDROME_LIBRARY_LOCATION = \"navidrome.db\"\n",
    "NEW_NAVIDROME_LIBRARY_LOCATION = \"navidrome_new.db\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e4d1853-3ad7-42ba-92aa-9e8ca68fd9a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "itunes = Library(ITUNES_LIBRARY_LOCATION)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "57d9e5e2-90f6-408e-94d0-066c5469c433",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# for song_id, song in itunes.songs.items():\n",
    "#     print(f\"{song_id}: {song.artist} - {song.name}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ad800a4c-e90d-4790-8576-7ec610553be6",
   "metadata": {},
   "outputs": [],
   "source": [
    "song = itunes.songs[21500]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "48eed309-3469-4833-a6dd-39e1239cebed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'True Faith'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "song.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7f8c8fa2-fa87-454a-8725-e9322468cc0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "song.rating"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b3722f1a-8f4b-4d88-acb9-6ec41f77b598",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name: True Faith\n",
      "work: None\n",
      "movement_number: None\n",
      "movement_count: None\n",
      "movement_name: None\n",
      "track_id: 21500\n",
      "artist: New Order\n",
      "album_artist: New Order\n",
      "composer: None\n",
      "album: Substance 1987\n",
      "genre: New Wave\n",
      "kind: AAC audio file\n",
      "size: 8698980\n",
      "total_time: 355093\n",
      "track_number: 12\n",
      "track_count: 12\n",
      "disc_number: 1\n",
      "disc_count: 2\n",
      "year: 1987\n",
      "date_modified: time.struct_time(tm_year=2022, tm_mon=2, tm_mday=12, tm_hour=6, tm_min=53, tm_sec=0, tm_wday=5, tm_yday=43, tm_isdst=-1)\n",
      "date_added: time.struct_time(tm_year=2022, tm_mon=2, tm_mday=12, tm_hour=6, tm_min=53, tm_sec=28, tm_wday=5, tm_yday=43, tm_isdst=-1)\n",
      "bit_rate: 193\n",
      "sample_rate: 44100\n",
      "comments: None\n",
      "rating: None\n",
      "rating_computed: False\n",
      "play_count: None\n",
      "album_rating: None\n",
      "album_rating_computed: False\n",
      "persistent_id: 9D61999A0F23D49A\n",
      "location_escaped: file://localhost/F:/music/iTunes%20AAC%20Transcodes/Songs/New%20Order%20-%20True%20Faith.m4a\n",
      "location: F:/music/iTunes AAC Transcodes/Songs/New Order - True Faith.m4a\n",
      "compilation: False\n",
      "lastplayed: None\n",
      "skip_count: None\n",
      "skip_date: None\n",
      "length: 355093\n",
      "track_type: File\n",
      "grouping: None\n",
      "podcast: False\n",
      "movie: False\n",
      "has_video: False\n",
      "loved: False\n",
      "album_loved: False\n",
      "playlist_only: False\n",
      "apple_music: False\n",
      "protected: False\n"
     ]
    }
   ],
   "source": [
    "for prop, value in vars(song).items():\n",
    "    print(f\"{prop}: {value}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "0811c68b-de19-40ef-9cc4-836a86c3fe05",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'New Order - True Faith | Substance 1987 (1987) 5:55 '"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f\"{song.artist} - {song.name} | {song.album} ({song.year}) {song.total_time // (1000*60)}:{(song.total_time//1000)%60:02} {'♥ ' if song.loved else ''}{('☆' if song.rating_computed else '★') * (song.rating // 20 if song.rating else 0)}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "19eefacd-44a5-42dd-908f-4cd36c454e8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "song_file_metadata = mutagen.File(song.location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "232e520e-038a-4110-9c0a-4a584fae24b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# song_file_metadata[\"TXXX:MusicBrainz Release Track Id\"][0]\n",
    "# bytes(song_file_metadata[\"----:com.apple.iTunes:MusicBrainz Track Id\"][0]).decode(\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e717a3be-fb37-42d9-a68b-5568b01d873c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Song 1000/7919\n",
      "Song 2000/7919\n",
      "Song 3000/7919\n",
      "Song 4000/7919\n",
      "Song 5000/7919\n",
      "Song 6000/7919\n",
      "Song 7000/7919\n"
     ]
    }
   ],
   "source": [
    "itunes_songs = {}\n",
    "counter = 0\n",
    "for song_id, song in itunes.songs.items():\n",
    "    counter += 1\n",
    "    if counter % 1000 == 0:\n",
    "        print(f\"Song {counter}/{len(itunes.songs)}\")\n",
    "    if any([song.podcast, song.protected, song.movie, song.playlist_only, song.apple_music]):\n",
    "        continue\n",
    "    musicbrainz_track_id = None\n",
    "    in_itunes_music = None\n",
    "    if song.location:\n",
    "        if os.path.exists(song.location):\n",
    "            song_file_metadata = mutagen.File(song.location)\n",
    "            if song_file_metadata.get(\"----:com.apple.iTunes:MusicBrainz Track Id\"):\n",
    "                    musicbrainz_track_id = bytes(song_file_metadata[\"----:com.apple.iTunes:MusicBrainz Track Id\"][0]).decode(\"utf-8\")\n",
    "            elif song_file_metadata.get(\"TXXX:MusicBrainz Release Track Id\"):\n",
    "                    musicbrainz_track_id = song_file_metadata[\"TXXX:MusicBrainz Release Track Id\"][0]\n",
    "        in_itunes_music = song.location.startswith(\"C:/Users/rdoge/Music/iTunes/iTunes Music/\")\n",
    "    lastplayed = None\n",
    "    if song.lastplayed:\n",
    "        lastplayed = datetime(*song.lastplayed[:6])\n",
    "    itunes_songs[song_id] = {\"name\": song.name, \"artist\": song.artist, \"rating_itunes\": song.rating, \"rating_computed\": song.rating_computed, \"play_count\": song.play_count, \"location\": song.location, \"in_itunes_music\": in_itunes_music, \"musicbrainz_track_id\": musicbrainz_track_id, \"play_count\": song.play_count, \"lastplayed\": lastplayed}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7191a0a2-95f7-430c-820c-cb1a54681f13",
   "metadata": {},
   "outputs": [],
   "source": [
    "itunes_df = pd.DataFrame.from_dict(itunes_songs, orient='index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "946403e5-b8df-410b-afbc-038b0009716b",
   "metadata": {},
   "outputs": [],
   "source": [
    "itunes_df[\"rating_in_stars\"] = pd.NA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "91ff70da-51b1-45a6-985d-cbac0ed56de1",
   "metadata": {},
   "outputs": [],
   "source": [
    "itunes_df[\"rating_in_stars\"] = itunes_df[(itunes_df[\"rating_itunes\"].notnull()) & (~itunes_df[\"rating_computed\"])][\"rating_itunes\"] // 20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8f931f2-71d8-4b80-b414-a8e75300bdd0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "824438a8-a818-4825-a1ef-8c157287f7c9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>artist</th>\n",
       "      <th>rating_itunes</th>\n",
       "      <th>rating_computed</th>\n",
       "      <th>play_count</th>\n",
       "      <th>location</th>\n",
       "      <th>in_itunes_music</th>\n",
       "      <th>musicbrainz_track_id</th>\n",
       "      <th>lastplayed</th>\n",
       "      <th>rating_in_stars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5752</th>\n",
       "      <td>Come Together</td>\n",
       "      <td>The Beatles</td>\n",
       "      <td>60.0</td>\n",
       "      <td>False</td>\n",
       "      <td>21.0</td>\n",
       "      <td>C:/Users/rdoge/Music/iTunes/iTunes Music/The B...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "      <td>2012-05-03 03:37:51</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5754</th>\n",
       "      <td>Something</td>\n",
       "      <td>The Beatles</td>\n",
       "      <td>60.0</td>\n",
       "      <td>False</td>\n",
       "      <td>32.0</td>\n",
       "      <td>C:/Users/rdoge/Music/iTunes/iTunes Music/The B...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "      <td>2022-02-25 17:30:45</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5756</th>\n",
       "      <td>Maxwell's Silver Hammer</td>\n",
       "      <td>The Beatles</td>\n",
       "      <td>60.0</td>\n",
       "      <td>True</td>\n",
       "      <td>25.0</td>\n",
       "      <td>C:/Users/rdoge/Music/iTunes/iTunes Music/The B...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-06-26 18:12:12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5758</th>\n",
       "      <td>Oh! Darling</td>\n",
       "      <td>The Beatles</td>\n",
       "      <td>60.0</td>\n",
       "      <td>True</td>\n",
       "      <td>23.0</td>\n",
       "      <td>C:/Users/rdoge/Music/iTunes/iTunes Music/The B...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "      <td>2012-05-31 15:18:08</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5760</th>\n",
       "      <td>Octopus's Garden</td>\n",
       "      <td>The Beatles</td>\n",
       "      <td>60.0</td>\n",
       "      <td>False</td>\n",
       "      <td>16.0</td>\n",
       "      <td>C:/Users/rdoge/Music/iTunes/iTunes Music/The B...</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "      <td>2015-01-09 05:28:48</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21562</th>\n",
       "      <td>Shizam</td>\n",
       "      <td>Zed Bias, Stylo G &amp; Scrufizzer</td>\n",
       "      <td>60.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F:/music/songs/Anything remotely electronic/Ze...</td>\n",
       "      <td>False</td>\n",
       "      <td>c79707e6-ab22-4921-95d2-cf299e8ae009</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21564</th>\n",
       "      <td>Under</td>\n",
       "      <td>Pleasure P</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F:/music/songs/R&amp;B - Newer Soul/Pleasure P - U...</td>\n",
       "      <td>False</td>\n",
       "      <td>28347420-2696-37ee-80e6-b32b61fd9998</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21566</th>\n",
       "      <td>Same Shit Different Day</td>\n",
       "      <td>Havoc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1.0</td>\n",
       "      <td>F:/music/songs/Anything remotely electronic/Ha...</td>\n",
       "      <td>False</td>\n",
       "      <td>fb3704db-4384-453e-9c86-bf1795e9e0ea</td>\n",
       "      <td>2022-03-06 14:41:41</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21570</th>\n",
       "      <td>Sofa (Nonchalant)</td>\n",
       "      <td>Chapeaumelon</td>\n",
       "      <td>80.0</td>\n",
       "      <td>False</td>\n",
       "      <td>3.0</td>\n",
       "      <td>F:/music/songs/Rock/Chapeaumelon - Sofa (Nonch...</td>\n",
       "      <td>False</td>\n",
       "      <td>e3025af6-7ffb-4b46-8e7f-00c210d2bbf9</td>\n",
       "      <td>2022-03-06 03:21:58</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21600</th>\n",
       "      <td>Life's a Bitch (Blutch Remix)</td>\n",
       "      <td>Blutch</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F:/music/songs/Rap/Nas - Life's a Bitch (feat....</td>\n",
       "      <td>False</td>\n",
       "      <td>bd510882-a886-4d41-8d7c-9c1c6966e7d2</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7292 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                name                          artist  \\\n",
       "5752                   Come Together                     The Beatles   \n",
       "5754                       Something                     The Beatles   \n",
       "5756         Maxwell's Silver Hammer                     The Beatles   \n",
       "5758                     Oh! Darling                     The Beatles   \n",
       "5760                Octopus's Garden                     The Beatles   \n",
       "...                              ...                             ...   \n",
       "21562                         Shizam  Zed Bias, Stylo G & Scrufizzer   \n",
       "21564                          Under                      Pleasure P   \n",
       "21566        Same Shit Different Day                           Havoc   \n",
       "21570              Sofa (Nonchalant)                    Chapeaumelon   \n",
       "21600  Life's a Bitch (Blutch Remix)                          Blutch   \n",
       "\n",
       "       rating_itunes  rating_computed  play_count  \\\n",
       "5752            60.0            False        21.0   \n",
       "5754            60.0            False        32.0   \n",
       "5756            60.0             True        25.0   \n",
       "5758            60.0             True        23.0   \n",
       "5760            60.0            False        16.0   \n",
       "...              ...              ...         ...   \n",
       "21562           60.0            False         NaN   \n",
       "21564            NaN            False         NaN   \n",
       "21566            NaN            False         1.0   \n",
       "21570           80.0            False         3.0   \n",
       "21600            NaN            False         NaN   \n",
       "\n",
       "                                                location in_itunes_music  \\\n",
       "5752   C:/Users/rdoge/Music/iTunes/iTunes Music/The B...            True   \n",
       "5754   C:/Users/rdoge/Music/iTunes/iTunes Music/The B...            True   \n",
       "5756   C:/Users/rdoge/Music/iTunes/iTunes Music/The B...            True   \n",
       "5758   C:/Users/rdoge/Music/iTunes/iTunes Music/The B...            True   \n",
       "5760   C:/Users/rdoge/Music/iTunes/iTunes Music/The B...            True   \n",
       "...                                                  ...             ...   \n",
       "21562  F:/music/songs/Anything remotely electronic/Ze...           False   \n",
       "21564  F:/music/songs/R&B - Newer Soul/Pleasure P - U...           False   \n",
       "21566  F:/music/songs/Anything remotely electronic/Ha...           False   \n",
       "21570  F:/music/songs/Rock/Chapeaumelon - Sofa (Nonch...           False   \n",
       "21600  F:/music/songs/Rap/Nas - Life's a Bitch (feat....           False   \n",
       "\n",
       "                       musicbrainz_track_id          lastplayed  \\\n",
       "5752                                   None 2012-05-03 03:37:51   \n",
       "5754                                   None 2022-02-25 17:30:45   \n",
       "5756                                   None 2013-06-26 18:12:12   \n",
       "5758                                   None 2012-05-31 15:18:08   \n",
       "5760                                   None 2015-01-09 05:28:48   \n",
       "...                                     ...                 ...   \n",
       "21562  c79707e6-ab22-4921-95d2-cf299e8ae009                 NaT   \n",
       "21564  28347420-2696-37ee-80e6-b32b61fd9998                 NaT   \n",
       "21566  fb3704db-4384-453e-9c86-bf1795e9e0ea 2022-03-06 14:41:41   \n",
       "21570  e3025af6-7ffb-4b46-8e7f-00c210d2bbf9 2022-03-06 03:21:58   \n",
       "21600  bd510882-a886-4d41-8d7c-9c1c6966e7d2                 NaT   \n",
       "\n",
       "       rating_in_stars  \n",
       "5752               3.0  \n",
       "5754               3.0  \n",
       "5756               NaN  \n",
       "5758               NaN  \n",
       "5760               3.0  \n",
       "...                ...  \n",
       "21562              3.0  \n",
       "21564              NaN  \n",
       "21566              NaN  \n",
       "21570              4.0  \n",
       "21600              NaN  \n",
       "\n",
       "[7292 rows x 10 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "itunes_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "228c9cde-de65-4c62-9714-0c8a3e215a32",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 7292 entries, 5752 to 21600\n",
      "Data columns (total 10 columns):\n",
      " #   Column                Non-Null Count  Dtype         \n",
      "---  ------                --------------  -----         \n",
      " 0   name                  7292 non-null   object        \n",
      " 1   artist                7260 non-null   object        \n",
      " 2   rating_itunes         3676 non-null   float64       \n",
      " 3   rating_computed       7292 non-null   bool          \n",
      " 4   play_count            5427 non-null   float64       \n",
      " 5   location              7198 non-null   object        \n",
      " 6   in_itunes_music       7197 non-null   object        \n",
      " 7   musicbrainz_track_id  4891 non-null   object        \n",
      " 8   lastplayed            5417 non-null   datetime64[ns]\n",
      " 9   rating_in_stars       1571 non-null   float64       \n",
      "dtypes: bool(1), datetime64[ns](1), float64(3), object(5)\n",
      "memory usage: 576.8+ KB\n"
     ]
    }
   ],
   "source": [
    "itunes_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "53eccc4a-5234-4e36-a34b-180dc6cfdae2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# itunes_df.to_parquet(\"itunes_songs.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "16192496-148a-4e09-a3fd-76904a041a4e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'navidrome_new.db'"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "shutil.copy2(OLD_NAVIDROME_LIBRARY_LOCATION, NEW_NAVIDROME_LIBRARY_LOCATION)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "4c7762b1-dd73-4d26-ae79-489cca2eaab9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create your connection.\n",
    "engine = sqlalchemy.create_engine(\"sqlite:///\" + OLD_NAVIDROME_LIBRARY_LOCATION)\n",
    "\n",
    "with engine.connect() as conn:\n",
    "    navidrome_song_df = pd.read_sql_query(\"SELECT * FROM media_file WHERE mbz_track_id IS NOT NULL\", conn)\n",
    "    annotation_df = pd.read_sql_table(\"annotation\", conn)\n",
    "\n",
    "engine.dispose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "eb116806-890f-401a-9b1e-b1f4685ffb4b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>path</th>\n",
       "      <th>title</th>\n",
       "      <th>album</th>\n",
       "      <th>artist</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>album_artist</th>\n",
       "      <th>album_id</th>\n",
       "      <th>has_cover_art</th>\n",
       "      <th>track_number</th>\n",
       "      <th>...</th>\n",
       "      <th>mbz_artist_id</th>\n",
       "      <th>mbz_album_artist_id</th>\n",
       "      <th>mbz_album_type</th>\n",
       "      <th>mbz_album_comment</th>\n",
       "      <th>catalog_num</th>\n",
       "      <th>comment</th>\n",
       "      <th>lyrics</th>\n",
       "      <th>bpm</th>\n",
       "      <th>channels</th>\n",
       "      <th>order_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37862d0706fd9a47f239b247549afc7a</td>\n",
       "      <td>/music/albums/2562 - Aerial/009-2562-kameleon.mp3</td>\n",
       "      <td>Kameleon</td>\n",
       "      <td>Aerial</td>\n",
       "      <td>2562</td>\n",
       "      <td>dbab2adc8f9d078009ee3fa810bea142</td>\n",
       "      <td>2562</td>\n",
       "      <td>826dbbca8298a8d82024cc465d50a9b4</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>5a557973-2e65-4429-a60d-a9886bfbac6a</td>\n",
       "      <td>5a557973-2e65-4429-a60d-a9886bfbac6a</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>TECCD004</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Kameleon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>e55643332f0db7b2bd909f077feb8b7e</td>\n",
       "      <td>/music/albums/2562 - Aerial/001-2562-redux.mp3</td>\n",
       "      <td>Redux</td>\n",
       "      <td>Aerial</td>\n",
       "      <td>2562</td>\n",
       "      <td>dbab2adc8f9d078009ee3fa810bea142</td>\n",
       "      <td>2562</td>\n",
       "      <td>826dbbca8298a8d82024cc465d50a9b4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>5a557973-2e65-4429-a60d-a9886bfbac6a</td>\n",
       "      <td>5a557973-2e65-4429-a60d-a9886bfbac6a</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>TECCD004</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Redux</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3c68cda5f18522b66f3d011bd59d4871</td>\n",
       "      <td>/music/albums/2562 - Aerial/002-2562-morvern.mp3</td>\n",
       "      <td>Morvern</td>\n",
       "      <td>Aerial</td>\n",
       "      <td>2562</td>\n",
       "      <td>dbab2adc8f9d078009ee3fa810bea142</td>\n",
       "      <td>2562</td>\n",
       "      <td>826dbbca8298a8d82024cc465d50a9b4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>5a557973-2e65-4429-a60d-a9886bfbac6a</td>\n",
       "      <td>5a557973-2e65-4429-a60d-a9886bfbac6a</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>TECCD004</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Morvern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4a72a9da42608586bf431afc526593e8</td>\n",
       "      <td>/music/albums/2562 - Aerial/004-2562-channel_t...</td>\n",
       "      <td>Channel Two</td>\n",
       "      <td>Aerial</td>\n",
       "      <td>2562</td>\n",
       "      <td>dbab2adc8f9d078009ee3fa810bea142</td>\n",
       "      <td>2562</td>\n",
       "      <td>826dbbca8298a8d82024cc465d50a9b4</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>5a557973-2e65-4429-a60d-a9886bfbac6a</td>\n",
       "      <td>5a557973-2e65-4429-a60d-a9886bfbac6a</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>TECCD004</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Channel Two</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50050f743195440153cf293df40e5d06</td>\n",
       "      <td>/music/albums/2562 - Aerial/003-2562-moog_dub.mp3</td>\n",
       "      <td>Moog Dub</td>\n",
       "      <td>Aerial</td>\n",
       "      <td>2562</td>\n",
       "      <td>dbab2adc8f9d078009ee3fa810bea142</td>\n",
       "      <td>2562</td>\n",
       "      <td>826dbbca8298a8d82024cc465d50a9b4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>5a557973-2e65-4429-a60d-a9886bfbac6a</td>\n",
       "      <td>5a557973-2e65-4429-a60d-a9886bfbac6a</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>TECCD004</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Moog Dub</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7618</th>\n",
       "      <td>1c4af6f080857ef66bcdfed87a9affc3</td>\n",
       "      <td>/music/songs/Rock/Chapeaumelon - Sofa (Nonchal...</td>\n",
       "      <td>Sofa (Nonchalant)</td>\n",
       "      <td>Chapeaumelon</td>\n",
       "      <td>Chapeaumelon</td>\n",
       "      <td>07c2a76a8dda4cfb3c7864ab4bc54943</td>\n",
       "      <td>Chapeaumelon</td>\n",
       "      <td>e3051b1d3be26922228aabb20ac764e4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>5d98f67f-3562-42ce-a779-f8b5d0ba75b1</td>\n",
       "      <td>5d98f67f-3562-42ce-a779-f8b5d0ba75b1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Sofa (Nonchalant)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7619</th>\n",
       "      <td>11e3d6deb715308ce6e4253731475bca</td>\n",
       "      <td>/music/songs/Rock/Gregorian - Ave Satani (The ...</td>\n",
       "      <td>Ave Satani (The Omen)</td>\n",
       "      <td>The Dark Side</td>\n",
       "      <td>Gregorian</td>\n",
       "      <td>a56c75f6ac705510397b519cbfe3d39d</td>\n",
       "      <td>Gregorian</td>\n",
       "      <td>e677758f69eeacc8510af7a63ff783e3</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>18a0d72d-0be0-4196-a340-035163bfae1c</td>\n",
       "      <td>18a0d72d-0be0-4196-a340-035163bfae1c</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0158082ERE</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Ave Satani (The Omen)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7620</th>\n",
       "      <td>09ff19f4d93403596288affe538d1bb4</td>\n",
       "      <td>/music/songs/Anything remotely electronic/Eric...</td>\n",
       "      <td>Call On Me (radio edit)</td>\n",
       "      <td>Call On Me</td>\n",
       "      <td>Eric Prydz</td>\n",
       "      <td>570934c95f1d141104627e6e47f94ebc</td>\n",
       "      <td>Eric Prydz</td>\n",
       "      <td>e8c1e6680e0012c225a21c75d00b8305</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>35dac7d2-0b1f-470f-9a5a-c53c8821f6d6</td>\n",
       "      <td>35dac7d2-0b1f-470f-9a5a-c53c8821f6d6</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>DATA68CDS</td>\n",
       "      <td></td>\n",
       "      <td>Call on me, call on me\\nCall on me, call on me...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Call On Me (radio edit)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7621</th>\n",
       "      <td>293d01200d2b2b775b0568f21acf591b</td>\n",
       "      <td>/music/songs/Anything remotely electronic/Wago...</td>\n",
       "      <td>Musipal</td>\n",
       "      <td>Musipal</td>\n",
       "      <td>Wagon Christ</td>\n",
       "      <td>57123dde9844ac8d811d998426dce99b</td>\n",
       "      <td>Wagon Christ</td>\n",
       "      <td>9c8e04a0e6efdc32539e5114f0958ad9</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Musipal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7622</th>\n",
       "      <td>bfb56b4dcb5a5dc7d3aa273746020e2a</td>\n",
       "      <td>/music/songs/Rap/Nas - Life's a Bitch (feat. A...</td>\n",
       "      <td>Life's a Bitch (Blutch Remix)</td>\n",
       "      <td>Gratis</td>\n",
       "      <td>Blutch</td>\n",
       "      <td>aa1ff24070dbfcf636d1c72781f06aef</td>\n",
       "      <td>Blutch</td>\n",
       "      <td>51589b9d2d0af6265ac1fdd2daa854ff</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>8a00f1b8-eae9-4bad-9bb6-20a40e341ccd</td>\n",
       "      <td>8a00f1b8-eae9-4bad-9bb6-20a40e341ccd</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Life's a Bitch (Blutch Remix)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>7623 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    id  \\\n",
       "0     37862d0706fd9a47f239b247549afc7a   \n",
       "1     e55643332f0db7b2bd909f077feb8b7e   \n",
       "2     3c68cda5f18522b66f3d011bd59d4871   \n",
       "3     4a72a9da42608586bf431afc526593e8   \n",
       "4     50050f743195440153cf293df40e5d06   \n",
       "...                                ...   \n",
       "7618  1c4af6f080857ef66bcdfed87a9affc3   \n",
       "7619  11e3d6deb715308ce6e4253731475bca   \n",
       "7620  09ff19f4d93403596288affe538d1bb4   \n",
       "7621  293d01200d2b2b775b0568f21acf591b   \n",
       "7622  bfb56b4dcb5a5dc7d3aa273746020e2a   \n",
       "\n",
       "                                                   path  \\\n",
       "0     /music/albums/2562 - Aerial/009-2562-kameleon.mp3   \n",
       "1        /music/albums/2562 - Aerial/001-2562-redux.mp3   \n",
       "2      /music/albums/2562 - Aerial/002-2562-morvern.mp3   \n",
       "3     /music/albums/2562 - Aerial/004-2562-channel_t...   \n",
       "4     /music/albums/2562 - Aerial/003-2562-moog_dub.mp3   \n",
       "...                                                 ...   \n",
       "7618  /music/songs/Rock/Chapeaumelon - Sofa (Nonchal...   \n",
       "7619  /music/songs/Rock/Gregorian - Ave Satani (The ...   \n",
       "7620  /music/songs/Anything remotely electronic/Eric...   \n",
       "7621  /music/songs/Anything remotely electronic/Wago...   \n",
       "7622  /music/songs/Rap/Nas - Life's a Bitch (feat. A...   \n",
       "\n",
       "                              title          album        artist  \\\n",
       "0                          Kameleon         Aerial          2562   \n",
       "1                             Redux         Aerial          2562   \n",
       "2                           Morvern         Aerial          2562   \n",
       "3                       Channel Two         Aerial          2562   \n",
       "4                          Moog Dub         Aerial          2562   \n",
       "...                             ...            ...           ...   \n",
       "7618              Sofa (Nonchalant)   Chapeaumelon  Chapeaumelon   \n",
       "7619          Ave Satani (The Omen)  The Dark Side     Gregorian   \n",
       "7620        Call On Me (radio edit)     Call On Me    Eric Prydz   \n",
       "7621                        Musipal        Musipal  Wagon Christ   \n",
       "7622  Life's a Bitch (Blutch Remix)         Gratis        Blutch   \n",
       "\n",
       "                             artist_id  album_artist  \\\n",
       "0     dbab2adc8f9d078009ee3fa810bea142          2562   \n",
       "1     dbab2adc8f9d078009ee3fa810bea142          2562   \n",
       "2     dbab2adc8f9d078009ee3fa810bea142          2562   \n",
       "3     dbab2adc8f9d078009ee3fa810bea142          2562   \n",
       "4     dbab2adc8f9d078009ee3fa810bea142          2562   \n",
       "...                                ...           ...   \n",
       "7618  07c2a76a8dda4cfb3c7864ab4bc54943  Chapeaumelon   \n",
       "7619  a56c75f6ac705510397b519cbfe3d39d     Gregorian   \n",
       "7620  570934c95f1d141104627e6e47f94ebc    Eric Prydz   \n",
       "7621  57123dde9844ac8d811d998426dce99b  Wagon Christ   \n",
       "7622  aa1ff24070dbfcf636d1c72781f06aef        Blutch   \n",
       "\n",
       "                              album_id  has_cover_art  track_number  ...  \\\n",
       "0     826dbbca8298a8d82024cc465d50a9b4              1             9  ...   \n",
       "1     826dbbca8298a8d82024cc465d50a9b4              1             1  ...   \n",
       "2     826dbbca8298a8d82024cc465d50a9b4              1             2  ...   \n",
       "3     826dbbca8298a8d82024cc465d50a9b4              1             4  ...   \n",
       "4     826dbbca8298a8d82024cc465d50a9b4              1             3  ...   \n",
       "...                                ...            ...           ...  ...   \n",
       "7618  e3051b1d3be26922228aabb20ac764e4              1             2  ...   \n",
       "7619  e677758f69eeacc8510af7a63ff783e3              1            12  ...   \n",
       "7620  e8c1e6680e0012c225a21c75d00b8305              1             1  ...   \n",
       "7621  9c8e04a0e6efdc32539e5114f0958ad9              0             2  ...   \n",
       "7622  51589b9d2d0af6265ac1fdd2daa854ff              1             3  ...   \n",
       "\n",
       "                             mbz_artist_id  \\\n",
       "0     5a557973-2e65-4429-a60d-a9886bfbac6a   \n",
       "1     5a557973-2e65-4429-a60d-a9886bfbac6a   \n",
       "2     5a557973-2e65-4429-a60d-a9886bfbac6a   \n",
       "3     5a557973-2e65-4429-a60d-a9886bfbac6a   \n",
       "4     5a557973-2e65-4429-a60d-a9886bfbac6a   \n",
       "...                                    ...   \n",
       "7618  5d98f67f-3562-42ce-a779-f8b5d0ba75b1   \n",
       "7619  18a0d72d-0be0-4196-a340-035163bfae1c   \n",
       "7620  35dac7d2-0b1f-470f-9a5a-c53c8821f6d6   \n",
       "7621                                         \n",
       "7622  8a00f1b8-eae9-4bad-9bb6-20a40e341ccd   \n",
       "\n",
       "                       mbz_album_artist_id  mbz_album_type mbz_album_comment  \\\n",
       "0     5a557973-2e65-4429-a60d-a9886bfbac6a                                     \n",
       "1     5a557973-2e65-4429-a60d-a9886bfbac6a                                     \n",
       "2     5a557973-2e65-4429-a60d-a9886bfbac6a                                     \n",
       "3     5a557973-2e65-4429-a60d-a9886bfbac6a                                     \n",
       "4     5a557973-2e65-4429-a60d-a9886bfbac6a                                     \n",
       "...                                    ...             ...               ...   \n",
       "7618  5d98f67f-3562-42ce-a779-f8b5d0ba75b1                                     \n",
       "7619  18a0d72d-0be0-4196-a340-035163bfae1c                                     \n",
       "7620  35dac7d2-0b1f-470f-9a5a-c53c8821f6d6                                     \n",
       "7621                                                                           \n",
       "7622  8a00f1b8-eae9-4bad-9bb6-20a40e341ccd                                     \n",
       "\n",
       "      catalog_num  comment                                             lyrics  \\\n",
       "0        TECCD004                                                               \n",
       "1        TECCD004                                                               \n",
       "2        TECCD004                                                               \n",
       "3        TECCD004                                                               \n",
       "4        TECCD004                                                               \n",
       "...           ...      ...                                                ...   \n",
       "7618                                                                            \n",
       "7619   0158082ERE                                                               \n",
       "7620    DATA68CDS           Call on me, call on me\\nCall on me, call on me...   \n",
       "7621                                                                            \n",
       "7622                                                                            \n",
       "\n",
       "      bpm channels                    order_title  \n",
       "0       0        2                       Kameleon  \n",
       "1       0        2                          Redux  \n",
       "2       0        2                        Morvern  \n",
       "3       0        2                    Channel Two  \n",
       "4       0        2                       Moog Dub  \n",
       "...   ...      ...                            ...  \n",
       "7618    0        2              Sofa (Nonchalant)  \n",
       "7619    0        2          Ave Satani (The Omen)  \n",
       "7620    0        2        Call On Me (radio edit)  \n",
       "7621    0        2                        Musipal  \n",
       "7622    0        2  Life's a Bitch (Blutch Remix)  \n",
       "\n",
       "[7623 rows x 42 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "navidrome_song_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "df41e441-9b30-43be-bc64-c6913a8469dd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'path', 'title', 'album', 'artist', 'artist_id', 'album_artist',\n",
       "       'album_id', 'has_cover_art', 'track_number', 'disc_number', 'year',\n",
       "       'size', 'suffix', 'duration', 'bit_rate', 'genre', 'compilation',\n",
       "       'created_at', 'updated_at', 'full_text', 'album_artist_id',\n",
       "       'order_album_name', 'order_album_artist_name', 'order_artist_name',\n",
       "       'sort_album_name', 'sort_artist_name', 'sort_album_artist_name',\n",
       "       'sort_title', 'disc_subtitle', 'mbz_track_id', 'mbz_album_id',\n",
       "       'mbz_artist_id', 'mbz_album_artist_id', 'mbz_album_type',\n",
       "       'mbz_album_comment', 'catalog_num', 'comment', 'lyrics', 'bpm',\n",
       "       'channels', 'order_title'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "navidrome_song_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d83d364a-81bb-43db-a623-726aba6b8f82",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ann_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>item_type</th>\n",
       "      <th>play_count</th>\n",
       "      <th>play_date</th>\n",
       "      <th>rating</th>\n",
       "      <th>starred</th>\n",
       "      <th>starred_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>c4234b4b-ef97-4719-b9a3-b045ba287a76</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>f12cb91a7885f1e027e0e0a65a52ddbf</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-11-30 01:59:43</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01442847-631d-410d-b063-19c7de57f4c9</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>01754fd08306328a5c786d986d56f2d1</td>\n",
       "      <td>album</td>\n",
       "      <td>2</td>\n",
       "      <td>2021-11-30 21:29:44</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6eaabbf8-6b01-4380-8d13-ff3d1c4578ec</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>83cba0493179ae3d5198cfadbbebf603</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-11-30 02:11:51</td>\n",
       "      <td>3</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-11-30 02:11:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a0a363f1-d752-4cf0-8ce7-fe570b7c4579</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>b40558e18a88b22be1709b473f46771f</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5dc610c3-5f30-4cf9-89d9-eeea80fc28a2</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>d1358cfce5e18b1adef106f37428153e</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>287</th>\n",
       "      <td>56304a6f-8340-47b2-93c7-6186df0ebe72</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>e20cc302bad8a36bca21aeab6d15c052</td>\n",
       "      <td>album</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-12 15:34:10</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>288</th>\n",
       "      <td>ecb7c430-145b-4961-b951-591d81442d4c</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>282804bf069cacf4844e7e853b3c9e14</td>\n",
       "      <td>artist</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-12 15:34:10</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>289</th>\n",
       "      <td>3dbccf76-17b9-4827-b15c-5eb9c7eb5f62</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>11e8d7685077eaf7ecaf65daca09ae25</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-12 15:39:54</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290</th>\n",
       "      <td>915656cb-37e2-42f1-87a0-65573c126a63</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>7019c3f09e780f9e91e21246c4afc753</td>\n",
       "      <td>album</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-12 15:39:54</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291</th>\n",
       "      <td>85db1467-03d0-4822-b1d4-f58d00f657c8</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>ae85c391f2ea8f0212ee4b08738f1b00</td>\n",
       "      <td>artist</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-12 15:39:54</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>292 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   ann_id  \\\n",
       "0    c4234b4b-ef97-4719-b9a3-b045ba287a76   \n",
       "1    01442847-631d-410d-b063-19c7de57f4c9   \n",
       "2    6eaabbf8-6b01-4380-8d13-ff3d1c4578ec   \n",
       "3    a0a363f1-d752-4cf0-8ce7-fe570b7c4579   \n",
       "4    5dc610c3-5f30-4cf9-89d9-eeea80fc28a2   \n",
       "..                                    ...   \n",
       "287  56304a6f-8340-47b2-93c7-6186df0ebe72   \n",
       "288  ecb7c430-145b-4961-b951-591d81442d4c   \n",
       "289  3dbccf76-17b9-4827-b15c-5eb9c7eb5f62   \n",
       "290  915656cb-37e2-42f1-87a0-65573c126a63   \n",
       "291  85db1467-03d0-4822-b1d4-f58d00f657c8   \n",
       "\n",
       "                                  user_id                           item_id  \\\n",
       "0    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  f12cb91a7885f1e027e0e0a65a52ddbf   \n",
       "1    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  01754fd08306328a5c786d986d56f2d1   \n",
       "2    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  83cba0493179ae3d5198cfadbbebf603   \n",
       "3    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  b40558e18a88b22be1709b473f46771f   \n",
       "4    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  d1358cfce5e18b1adef106f37428153e   \n",
       "..                                    ...                               ...   \n",
       "287  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  e20cc302bad8a36bca21aeab6d15c052   \n",
       "288  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  282804bf069cacf4844e7e853b3c9e14   \n",
       "289  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  11e8d7685077eaf7ecaf65daca09ae25   \n",
       "290  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  7019c3f09e780f9e91e21246c4afc753   \n",
       "291  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  ae85c391f2ea8f0212ee4b08738f1b00   \n",
       "\n",
       "      item_type  play_count           play_date  rating  starred  \\\n",
       "0    media_file           1 2021-11-30 01:59:43       3    False   \n",
       "1         album           2 2021-11-30 21:29:44       0    False   \n",
       "2    media_file           1 2021-11-30 02:11:51       3     True   \n",
       "3    media_file           0                 NaT       3    False   \n",
       "4    media_file           0                 NaT       3    False   \n",
       "..          ...         ...                 ...     ...      ...   \n",
       "287       album           1 2022-01-12 15:34:10       0    False   \n",
       "288      artist           1 2022-01-12 15:34:10       0    False   \n",
       "289  media_file           1 2022-01-12 15:39:54       0    False   \n",
       "290       album           1 2022-01-12 15:39:54       0    False   \n",
       "291      artist           1 2022-01-12 15:39:54       0    False   \n",
       "\n",
       "             starred_at  \n",
       "0                   NaT  \n",
       "1                   NaT  \n",
       "2   2021-11-30 02:11:59  \n",
       "3                   NaT  \n",
       "4                   NaT  \n",
       "..                  ...  \n",
       "287                 NaT  \n",
       "288                 NaT  \n",
       "289                 NaT  \n",
       "290                 NaT  \n",
       "291                 NaT  \n",
       "\n",
       "[292 rows x 9 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annotation_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "3d60a51f-45f7-4633-9092-14a8677d5453",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_song_df = navidrome_song_df.merge(itunes_df, how=\"left\", left_on=\"mbz_track_id\", right_on=\"musicbrainz_track_id\", suffixes=('_navidrome', '_itunes'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "1cb969b4-de5a-4cbd-89a0-c7b077bb0303",
   "metadata": {},
   "outputs": [],
   "source": [
    "rating_to_song = merged_song_df[merged_song_df[\"rating_in_stars\"].notnull()][[\"id\", \"rating_in_stars\", \"play_count\", \"lastplayed\"]]\n",
    "rating_to_song[\"rating_in_stars\"] = rating_to_song[\"rating_in_stars\"].astype(int)\n",
    "rating_to_song[\"play_count\"] = rating_to_song[\"play_count\"].fillna(0).astype(int)\n",
    "rating_to_song = rating_to_song.rename(columns={\"id\": \"item_id\", \"rating_in_stars\": \"rating\", \"lastplayed\": \"play_date\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "d9f25744-424c-4757-9b85-49f0d27e80c1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>item_id</th>\n",
       "      <th>rating</th>\n",
       "      <th>play_count</th>\n",
       "      <th>play_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>b40558e18a88b22be1709b473f46771f</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-11-16 21:56:20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>ffc81d9d1b466032ebeda801c31be67d</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>7943b316060e57b174ca09cf76ee8ad3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-12 05:03:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>5e58db3d07a7b7405b70e2d221f04705</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-19 22:21:09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>35cea62323cd1d9cbf4f87a87cc44fde</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>2021-10-20 04:20:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7594</th>\n",
       "      <td>040c7b744439c7b0db4b6cfe122ec43c</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7605</th>\n",
       "      <td>e95ecaf4e5242401a41ba615313827e9</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7609</th>\n",
       "      <td>806d963d48d0b6eb54d54c51da97e42d</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-02-14 03:51:38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7613</th>\n",
       "      <td>b6eb95893e90cdec2328a923cb8838fa</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-02-13 02:15:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7617</th>\n",
       "      <td>c3dabbef2757582a22a58636fc9b00af</td>\n",
       "      <td>3</td>\n",
       "      <td>26</td>\n",
       "      <td>2012-07-22 05:13:44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>374 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               item_id  rating  play_count           play_date\n",
       "32    b40558e18a88b22be1709b473f46771f       3           1 2021-11-16 21:56:20\n",
       "129   ffc81d9d1b466032ebeda801c31be67d       4           0                 NaT\n",
       "130   7943b316060e57b174ca09cf76ee8ad3       3           1 2022-01-12 05:03:54\n",
       "173   5e58db3d07a7b7405b70e2d221f04705       3           1 2020-11-19 22:21:09\n",
       "176   35cea62323cd1d9cbf4f87a87cc44fde       4           6 2021-10-20 04:20:27\n",
       "...                                ...     ...         ...                 ...\n",
       "7594  040c7b744439c7b0db4b6cfe122ec43c       3           0                 NaT\n",
       "7605  e95ecaf4e5242401a41ba615313827e9       3           0                 NaT\n",
       "7609  806d963d48d0b6eb54d54c51da97e42d       3           1 2022-02-14 03:51:38\n",
       "7613  b6eb95893e90cdec2328a923cb8838fa       3           1 2022-02-13 02:15:37\n",
       "7617  c3dabbef2757582a22a58636fc9b00af       3          26 2012-07-22 05:13:44\n",
       "\n",
       "[374 rows x 4 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rating_to_song"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "2160862d-59b5-4007-8b9b-2513ddce9b94",
   "metadata": {},
   "outputs": [],
   "source": [
    "annotation_rows_to_add = rating_to_song\n",
    "annotation_rows_to_add[\"user_id\"] = annotation_df[\"user_id\"].value_counts().index[0]\n",
    "annotation_rows_to_add['ann_id'] = [uuid.uuid4() for _ in range(len(annotation_rows_to_add.index))]\n",
    "annotation_rows_to_add[\"item_type\"] = \"media_file\"\n",
    "annotation_rows_to_add[\"starred\"] = 0\n",
    "annotation_rows_to_add[\"starred_at\"] = None\n",
    "annotation_rows_to_add[\"starred_at\"] = pd.to_datetime(annotation_rows_to_add[\"starred_at\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "b80adf69-8e82-45c6-bd12-613104565501",
   "metadata": {},
   "outputs": [],
   "source": [
    "annotation_rows_to_add = annotation_rows_to_add[[\"ann_id\", \"user_id\", \"item_id\", \"item_type\", \"play_count\", \"play_date\", \"rating\", \"starred\", \"starred_at\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "79cb2d59-c5b8-4b4c-82da-bd1aae1becd2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ann_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>item_type</th>\n",
       "      <th>play_count</th>\n",
       "      <th>play_date</th>\n",
       "      <th>rating</th>\n",
       "      <th>starred</th>\n",
       "      <th>starred_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>5edd480f-e95f-4373-8c96-999d6012a418</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>b40558e18a88b22be1709b473f46771f</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-11-16 21:56:20</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>e5af5996-b47b-4217-81fe-60e050c71ee9</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>ffc81d9d1b466032ebeda801c31be67d</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>7239b6f0-1ff6-4148-844e-ae74bcdba705</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>7943b316060e57b174ca09cf76ee8ad3</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-01-12 05:03:54</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>6d3afe36-314f-49a9-b062-9a03c8bd7b96</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>5e58db3d07a7b7405b70e2d221f04705</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1</td>\n",
       "      <td>2020-11-19 22:21:09</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>36184f75-3e99-4f43-8ee0-b8f434e5376f</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>35cea62323cd1d9cbf4f87a87cc44fde</td>\n",
       "      <td>media_file</td>\n",
       "      <td>6</td>\n",
       "      <td>2021-10-20 04:20:27</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7594</th>\n",
       "      <td>d25daee6-e086-4729-ab84-58265e25411a</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>040c7b744439c7b0db4b6cfe122ec43c</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7605</th>\n",
       "      <td>f9b1dcdc-e54c-46a4-b92c-070509fd3447</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>e95ecaf4e5242401a41ba615313827e9</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7609</th>\n",
       "      <td>af2cd0a9-6849-41cb-9cc7-de3d925c5047</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>806d963d48d0b6eb54d54c51da97e42d</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-02-14 03:51:38</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7613</th>\n",
       "      <td>cc51444f-24f2-4293-b44e-a2e43db1624d</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>b6eb95893e90cdec2328a923cb8838fa</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-02-13 02:15:37</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7617</th>\n",
       "      <td>14cbb917-0dd2-4c92-9ff7-8d615fb3b5fa</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>c3dabbef2757582a22a58636fc9b00af</td>\n",
       "      <td>media_file</td>\n",
       "      <td>26</td>\n",
       "      <td>2012-07-22 05:13:44</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>374 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    ann_id  \\\n",
       "32    5edd480f-e95f-4373-8c96-999d6012a418   \n",
       "129   e5af5996-b47b-4217-81fe-60e050c71ee9   \n",
       "130   7239b6f0-1ff6-4148-844e-ae74bcdba705   \n",
       "173   6d3afe36-314f-49a9-b062-9a03c8bd7b96   \n",
       "176   36184f75-3e99-4f43-8ee0-b8f434e5376f   \n",
       "...                                    ...   \n",
       "7594  d25daee6-e086-4729-ab84-58265e25411a   \n",
       "7605  f9b1dcdc-e54c-46a4-b92c-070509fd3447   \n",
       "7609  af2cd0a9-6849-41cb-9cc7-de3d925c5047   \n",
       "7613  cc51444f-24f2-4293-b44e-a2e43db1624d   \n",
       "7617  14cbb917-0dd2-4c92-9ff7-8d615fb3b5fa   \n",
       "\n",
       "                                   user_id                           item_id  \\\n",
       "32    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  b40558e18a88b22be1709b473f46771f   \n",
       "129   2e7d8278-2533-45e2-a1b9-0a702f87ddbf  ffc81d9d1b466032ebeda801c31be67d   \n",
       "130   2e7d8278-2533-45e2-a1b9-0a702f87ddbf  7943b316060e57b174ca09cf76ee8ad3   \n",
       "173   2e7d8278-2533-45e2-a1b9-0a702f87ddbf  5e58db3d07a7b7405b70e2d221f04705   \n",
       "176   2e7d8278-2533-45e2-a1b9-0a702f87ddbf  35cea62323cd1d9cbf4f87a87cc44fde   \n",
       "...                                    ...                               ...   \n",
       "7594  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  040c7b744439c7b0db4b6cfe122ec43c   \n",
       "7605  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  e95ecaf4e5242401a41ba615313827e9   \n",
       "7609  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  806d963d48d0b6eb54d54c51da97e42d   \n",
       "7613  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  b6eb95893e90cdec2328a923cb8838fa   \n",
       "7617  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  c3dabbef2757582a22a58636fc9b00af   \n",
       "\n",
       "       item_type  play_count           play_date  rating  starred starred_at  \n",
       "32    media_file           1 2021-11-16 21:56:20       3        0        NaT  \n",
       "129   media_file           0                 NaT       4        0        NaT  \n",
       "130   media_file           1 2022-01-12 05:03:54       3        0        NaT  \n",
       "173   media_file           1 2020-11-19 22:21:09       3        0        NaT  \n",
       "176   media_file           6 2021-10-20 04:20:27       4        0        NaT  \n",
       "...          ...         ...                 ...     ...      ...        ...  \n",
       "7594  media_file           0                 NaT       3        0        NaT  \n",
       "7605  media_file           0                 NaT       3        0        NaT  \n",
       "7609  media_file           1 2022-02-14 03:51:38       3        0        NaT  \n",
       "7613  media_file           1 2022-02-13 02:15:37       3        0        NaT  \n",
       "7617  media_file          26 2012-07-22 05:13:44       3        0        NaT  \n",
       "\n",
       "[374 rows x 9 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annotation_rows_to_add"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "32b22691-45f6-4677-b653-00bf184e4fc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_annotation_df = annotation_df.merge(annotation_rows_to_add, how=\"outer\", on=\"item_id\", suffixes=(\"_nvd\", \"_itu\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "2826c725-df2a-4a90-807e-6e3b9e0d23ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ann_id_nvd</th>\n",
       "      <th>user_id_nvd</th>\n",
       "      <th>item_id</th>\n",
       "      <th>item_type_nvd</th>\n",
       "      <th>play_count_nvd</th>\n",
       "      <th>play_date_nvd</th>\n",
       "      <th>rating_nvd</th>\n",
       "      <th>starred_nvd</th>\n",
       "      <th>starred_at_nvd</th>\n",
       "      <th>ann_id_itu</th>\n",
       "      <th>user_id_itu</th>\n",
       "      <th>item_type_itu</th>\n",
       "      <th>play_count_itu</th>\n",
       "      <th>play_date_itu</th>\n",
       "      <th>rating_itu</th>\n",
       "      <th>starred_itu</th>\n",
       "      <th>starred_at_itu</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>c4234b4b-ef97-4719-b9a3-b045ba287a76</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>f12cb91a7885f1e027e0e0a65a52ddbf</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-11-30 01:59:43</td>\n",
       "      <td>3.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01442847-631d-410d-b063-19c7de57f4c9</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>01754fd08306328a5c786d986d56f2d1</td>\n",
       "      <td>album</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2021-11-30 21:29:44</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6eaabbf8-6b01-4380-8d13-ff3d1c4578ec</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>83cba0493179ae3d5198cfadbbebf603</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-11-30 02:11:51</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-11-30 02:11:59</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a0a363f1-d752-4cf0-8ce7-fe570b7c4579</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>b40558e18a88b22be1709b473f46771f</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "      <td>5edd480f-e95f-4373-8c96-999d6012a418</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-11-16 21:56:20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5dc610c3-5f30-4cf9-89d9-eeea80fc28a2</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>d1358cfce5e18b1adef106f37428153e</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>040c7b744439c7b0db4b6cfe122ec43c</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>d25daee6-e086-4729-ab84-58265e25411a</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>e95ecaf4e5242401a41ba615313827e9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>f9b1dcdc-e54c-46a4-b92c-070509fd3447</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>647</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>806d963d48d0b6eb54d54c51da97e42d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>af2cd0a9-6849-41cb-9cc7-de3d925c5047</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2022-02-14 03:51:38</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>648</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>b6eb95893e90cdec2328a923cb8838fa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>cc51444f-24f2-4293-b44e-a2e43db1624d</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2022-02-13 02:15:37</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>649</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>c3dabbef2757582a22a58636fc9b00af</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>14cbb917-0dd2-4c92-9ff7-8d615fb3b5fa</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>media_file</td>\n",
       "      <td>26.0</td>\n",
       "      <td>2012-07-22 05:13:44</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>650 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               ann_id_nvd  \\\n",
       "0    c4234b4b-ef97-4719-b9a3-b045ba287a76   \n",
       "1    01442847-631d-410d-b063-19c7de57f4c9   \n",
       "2    6eaabbf8-6b01-4380-8d13-ff3d1c4578ec   \n",
       "3    a0a363f1-d752-4cf0-8ce7-fe570b7c4579   \n",
       "4    5dc610c3-5f30-4cf9-89d9-eeea80fc28a2   \n",
       "..                                    ...   \n",
       "645                                   NaN   \n",
       "646                                   NaN   \n",
       "647                                   NaN   \n",
       "648                                   NaN   \n",
       "649                                   NaN   \n",
       "\n",
       "                              user_id_nvd                           item_id  \\\n",
       "0    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  f12cb91a7885f1e027e0e0a65a52ddbf   \n",
       "1    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  01754fd08306328a5c786d986d56f2d1   \n",
       "2    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  83cba0493179ae3d5198cfadbbebf603   \n",
       "3    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  b40558e18a88b22be1709b473f46771f   \n",
       "4    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  d1358cfce5e18b1adef106f37428153e   \n",
       "..                                    ...                               ...   \n",
       "645                                   NaN  040c7b744439c7b0db4b6cfe122ec43c   \n",
       "646                                   NaN  e95ecaf4e5242401a41ba615313827e9   \n",
       "647                                   NaN  806d963d48d0b6eb54d54c51da97e42d   \n",
       "648                                   NaN  b6eb95893e90cdec2328a923cb8838fa   \n",
       "649                                   NaN  c3dabbef2757582a22a58636fc9b00af   \n",
       "\n",
       "    item_type_nvd  play_count_nvd       play_date_nvd  rating_nvd starred_nvd  \\\n",
       "0      media_file             1.0 2021-11-30 01:59:43         3.0       False   \n",
       "1           album             2.0 2021-11-30 21:29:44         0.0       False   \n",
       "2      media_file             1.0 2021-11-30 02:11:51         3.0        True   \n",
       "3      media_file             0.0                 NaT         3.0       False   \n",
       "4      media_file             0.0                 NaT         3.0       False   \n",
       "..            ...             ...                 ...         ...         ...   \n",
       "645           NaN             NaN                 NaT         NaN         NaN   \n",
       "646           NaN             NaN                 NaT         NaN         NaN   \n",
       "647           NaN             NaN                 NaT         NaN         NaN   \n",
       "648           NaN             NaN                 NaT         NaN         NaN   \n",
       "649           NaN             NaN                 NaT         NaN         NaN   \n",
       "\n",
       "         starred_at_nvd                            ann_id_itu  \\\n",
       "0                   NaT                                   NaN   \n",
       "1                   NaT                                   NaN   \n",
       "2   2021-11-30 02:11:59                                   NaN   \n",
       "3                   NaT  5edd480f-e95f-4373-8c96-999d6012a418   \n",
       "4                   NaT                                   NaN   \n",
       "..                  ...                                   ...   \n",
       "645                 NaT  d25daee6-e086-4729-ab84-58265e25411a   \n",
       "646                 NaT  f9b1dcdc-e54c-46a4-b92c-070509fd3447   \n",
       "647                 NaT  af2cd0a9-6849-41cb-9cc7-de3d925c5047   \n",
       "648                 NaT  cc51444f-24f2-4293-b44e-a2e43db1624d   \n",
       "649                 NaT  14cbb917-0dd2-4c92-9ff7-8d615fb3b5fa   \n",
       "\n",
       "                              user_id_itu item_type_itu  play_count_itu  \\\n",
       "0                                     NaN           NaN             NaN   \n",
       "1                                     NaN           NaN             NaN   \n",
       "2                                     NaN           NaN             NaN   \n",
       "3    2e7d8278-2533-45e2-a1b9-0a702f87ddbf    media_file             1.0   \n",
       "4                                     NaN           NaN             NaN   \n",
       "..                                    ...           ...             ...   \n",
       "645  2e7d8278-2533-45e2-a1b9-0a702f87ddbf    media_file             0.0   \n",
       "646  2e7d8278-2533-45e2-a1b9-0a702f87ddbf    media_file             0.0   \n",
       "647  2e7d8278-2533-45e2-a1b9-0a702f87ddbf    media_file             1.0   \n",
       "648  2e7d8278-2533-45e2-a1b9-0a702f87ddbf    media_file             1.0   \n",
       "649  2e7d8278-2533-45e2-a1b9-0a702f87ddbf    media_file            26.0   \n",
       "\n",
       "          play_date_itu  rating_itu  starred_itu starred_at_itu  \n",
       "0                   NaT         NaN          NaN            NaT  \n",
       "1                   NaT         NaN          NaN            NaT  \n",
       "2                   NaT         NaN          NaN            NaT  \n",
       "3   2021-11-16 21:56:20         3.0          0.0            NaT  \n",
       "4                   NaT         NaN          NaN            NaT  \n",
       "..                  ...         ...          ...            ...  \n",
       "645                 NaT         3.0          0.0            NaT  \n",
       "646                 NaT         3.0          0.0            NaT  \n",
       "647 2022-02-14 03:51:38         3.0          0.0            NaT  \n",
       "648 2022-02-13 02:15:37         3.0          0.0            NaT  \n",
       "649 2012-07-22 05:13:44         3.0          0.0            NaT  \n",
       "\n",
       "[650 rows x 17 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_annotation_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7da52342-f362-4fb1-872d-55bdce1c5571",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_annotation_df[\"ann_id\"] = merged_annotation_df[\"ann_id_nvd\"].combine_first(merged_annotation_df[\"ann_id_itu\"])\n",
    "merged_annotation_df[\"user_id\"] = merged_annotation_df[\"user_id_nvd\"].combine_first(merged_annotation_df[\"user_id_itu\"])\n",
    "merged_annotation_df[\"item_type\"] = merged_annotation_df[\"item_type_nvd\"].combine_first(merged_annotation_df[\"item_type_itu\"])\n",
    "merged_annotation_df[\"play_count\"] = merged_annotation_df[\"play_count_nvd\"].combine_first(merged_annotation_df[\"play_count_itu\"])\n",
    "merged_annotation_df[\"play_date\"] = merged_annotation_df[\"play_date_nvd\"].combine_first(merged_annotation_df[\"play_date_itu\"])\n",
    "merged_annotation_df[\"rating\"] = merged_annotation_df[\"rating_nvd\"].combine_first(merged_annotation_df[\"rating_itu\"])\n",
    "merged_annotation_df[\"starred\"] = merged_annotation_df[\"starred_nvd\"].combine_first(merged_annotation_df[\"starred_itu\"])\n",
    "merged_annotation_df[\"starred_at\"] = merged_annotation_df[\"starred_at_nvd\"].combine_first(merged_annotation_df[\"starred_at_itu\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "643f269a-38f4-4225-ad3c-e4b41739dc50",
   "metadata": {},
   "outputs": [],
   "source": [
    "select_criteria = (merged_annotation_df[\"play_count_nvd\"] * 5) <= (merged_annotation_df[\"play_count_itu\"])\n",
    "merged_annotation_df.loc[select_criteria, \"play_count\"] = merged_annotation_df.loc[select_criteria, \"play_count_nvd\"] + merged_annotation_df.loc[select_criteria, \"play_count_itu\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "8ba4dd6c-14dc-43da-9352-395e965c333d",
   "metadata": {},
   "outputs": [],
   "source": [
    "select_criteria = merged_annotation_df[\"play_date_nvd\"] != merged_annotation_df[\"play_date_itu\"]\n",
    "merged_annotation_df[select_criteria][\"play_count\"] += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "e68eb9e7-d5a5-44c4-b222-b66979ed8be3",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_annotation_df = merged_annotation_df[[\"ann_id\", \"user_id\", \"item_id\", \"item_type\", \"play_count\", \"play_date\", \"rating\", \"starred\", \"starred_at\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "de56dc96-0b44-449a-9ee7-1a4e1b1cc0f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_annotation_df = merged_annotation_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "e5a60b6d-6c67-4ef8-9bfb-5133e93c48df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ann_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>item_type</th>\n",
       "      <th>play_count</th>\n",
       "      <th>play_date</th>\n",
       "      <th>rating</th>\n",
       "      <th>starred</th>\n",
       "      <th>starred_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>c4234b4b-ef97-4719-b9a3-b045ba287a76</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>f12cb91a7885f1e027e0e0a65a52ddbf</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-11-30 01:59:43</td>\n",
       "      <td>3.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01442847-631d-410d-b063-19c7de57f4c9</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>01754fd08306328a5c786d986d56f2d1</td>\n",
       "      <td>album</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2021-11-30 21:29:44</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6eaabbf8-6b01-4380-8d13-ff3d1c4578ec</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>83cba0493179ae3d5198cfadbbebf603</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-11-30 02:11:51</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-11-30 02:11:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a0a363f1-d752-4cf0-8ce7-fe570b7c4579</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>b40558e18a88b22be1709b473f46771f</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-11-16 21:56:20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5dc610c3-5f30-4cf9-89d9-eeea80fc28a2</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>d1358cfce5e18b1adef106f37428153e</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>d25daee6-e086-4729-ab84-58265e25411a</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>040c7b744439c7b0db4b6cfe122ec43c</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>f9b1dcdc-e54c-46a4-b92c-070509fd3447</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>e95ecaf4e5242401a41ba615313827e9</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>647</th>\n",
       "      <td>af2cd0a9-6849-41cb-9cc7-de3d925c5047</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>806d963d48d0b6eb54d54c51da97e42d</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2022-02-14 03:51:38</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>648</th>\n",
       "      <td>cc51444f-24f2-4293-b44e-a2e43db1624d</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>b6eb95893e90cdec2328a923cb8838fa</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2022-02-13 02:15:37</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>649</th>\n",
       "      <td>14cbb917-0dd2-4c92-9ff7-8d615fb3b5fa</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>c3dabbef2757582a22a58636fc9b00af</td>\n",
       "      <td>media_file</td>\n",
       "      <td>26.0</td>\n",
       "      <td>2012-07-22 05:13:44</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>650 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   ann_id  \\\n",
       "0    c4234b4b-ef97-4719-b9a3-b045ba287a76   \n",
       "1    01442847-631d-410d-b063-19c7de57f4c9   \n",
       "2    6eaabbf8-6b01-4380-8d13-ff3d1c4578ec   \n",
       "3    a0a363f1-d752-4cf0-8ce7-fe570b7c4579   \n",
       "4    5dc610c3-5f30-4cf9-89d9-eeea80fc28a2   \n",
       "..                                    ...   \n",
       "645  d25daee6-e086-4729-ab84-58265e25411a   \n",
       "646  f9b1dcdc-e54c-46a4-b92c-070509fd3447   \n",
       "647  af2cd0a9-6849-41cb-9cc7-de3d925c5047   \n",
       "648  cc51444f-24f2-4293-b44e-a2e43db1624d   \n",
       "649  14cbb917-0dd2-4c92-9ff7-8d615fb3b5fa   \n",
       "\n",
       "                                  user_id                           item_id  \\\n",
       "0    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  f12cb91a7885f1e027e0e0a65a52ddbf   \n",
       "1    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  01754fd08306328a5c786d986d56f2d1   \n",
       "2    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  83cba0493179ae3d5198cfadbbebf603   \n",
       "3    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  b40558e18a88b22be1709b473f46771f   \n",
       "4    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  d1358cfce5e18b1adef106f37428153e   \n",
       "..                                    ...                               ...   \n",
       "645  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  040c7b744439c7b0db4b6cfe122ec43c   \n",
       "646  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  e95ecaf4e5242401a41ba615313827e9   \n",
       "647  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  806d963d48d0b6eb54d54c51da97e42d   \n",
       "648  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  b6eb95893e90cdec2328a923cb8838fa   \n",
       "649  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  c3dabbef2757582a22a58636fc9b00af   \n",
       "\n",
       "      item_type  play_count           play_date  rating starred  \\\n",
       "0    media_file         1.0 2021-11-30 01:59:43     3.0   False   \n",
       "1         album         2.0 2021-11-30 21:29:44     0.0   False   \n",
       "2    media_file         1.0 2021-11-30 02:11:51     3.0    True   \n",
       "3    media_file         1.0 2021-11-16 21:56:20     3.0   False   \n",
       "4    media_file         0.0                 NaT     3.0   False   \n",
       "..          ...         ...                 ...     ...     ...   \n",
       "645  media_file         0.0                 NaT     3.0     0.0   \n",
       "646  media_file         0.0                 NaT     3.0     0.0   \n",
       "647  media_file         1.0 2022-02-14 03:51:38     3.0     0.0   \n",
       "648  media_file         1.0 2022-02-13 02:15:37     3.0     0.0   \n",
       "649  media_file        26.0 2012-07-22 05:13:44     3.0     0.0   \n",
       "\n",
       "             starred_at  \n",
       "0                   NaT  \n",
       "1                   NaT  \n",
       "2   2021-11-30 02:11:59  \n",
       "3                   NaT  \n",
       "4                   NaT  \n",
       "..                  ...  \n",
       "645                 NaT  \n",
       "646                 NaT  \n",
       "647                 NaT  \n",
       "648                 NaT  \n",
       "649                 NaT  \n",
       "\n",
       "[650 rows x 9 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_annotation_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "3d60bf8a-3954-4bcd-ac0c-95a05928e621",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ann_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>item_id</th>\n",
       "      <th>item_type</th>\n",
       "      <th>play_count</th>\n",
       "      <th>play_date</th>\n",
       "      <th>rating</th>\n",
       "      <th>starred</th>\n",
       "      <th>starred_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>c4234b4b-ef97-4719-b9a3-b045ba287a76</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>f12cb91a7885f1e027e0e0a65a52ddbf</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-11-30 01:59:43</td>\n",
       "      <td>3.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01442847-631d-410d-b063-19c7de57f4c9</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>01754fd08306328a5c786d986d56f2d1</td>\n",
       "      <td>album</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2021-11-30 21:29:44</td>\n",
       "      <td>0.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6eaabbf8-6b01-4380-8d13-ff3d1c4578ec</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>83cba0493179ae3d5198cfadbbebf603</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-11-30 02:11:51</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "      <td>2021-11-30 02:11:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a0a363f1-d752-4cf0-8ce7-fe570b7c4579</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>b40558e18a88b22be1709b473f46771f</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-11-16 21:56:20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5dc610c3-5f30-4cf9-89d9-eeea80fc28a2</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>d1358cfce5e18b1adef106f37428153e</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3.0</td>\n",
       "      <td>False</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>d25daee6-e086-4729-ab84-58265e25411a</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>040c7b744439c7b0db4b6cfe122ec43c</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>646</th>\n",
       "      <td>f9b1dcdc-e54c-46a4-b92c-070509fd3447</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>e95ecaf4e5242401a41ba615313827e9</td>\n",
       "      <td>media_file</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>647</th>\n",
       "      <td>af2cd0a9-6849-41cb-9cc7-de3d925c5047</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>806d963d48d0b6eb54d54c51da97e42d</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2022-02-14 03:51:38</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>648</th>\n",
       "      <td>cc51444f-24f2-4293-b44e-a2e43db1624d</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>b6eb95893e90cdec2328a923cb8838fa</td>\n",
       "      <td>media_file</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2022-02-13 02:15:37</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>649</th>\n",
       "      <td>14cbb917-0dd2-4c92-9ff7-8d615fb3b5fa</td>\n",
       "      <td>2e7d8278-2533-45e2-a1b9-0a702f87ddbf</td>\n",
       "      <td>c3dabbef2757582a22a58636fc9b00af</td>\n",
       "      <td>media_file</td>\n",
       "      <td>26.0</td>\n",
       "      <td>2012-07-22 05:13:44</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>650 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   ann_id  \\\n",
       "0    c4234b4b-ef97-4719-b9a3-b045ba287a76   \n",
       "1    01442847-631d-410d-b063-19c7de57f4c9   \n",
       "2    6eaabbf8-6b01-4380-8d13-ff3d1c4578ec   \n",
       "3    a0a363f1-d752-4cf0-8ce7-fe570b7c4579   \n",
       "4    5dc610c3-5f30-4cf9-89d9-eeea80fc28a2   \n",
       "..                                    ...   \n",
       "645  d25daee6-e086-4729-ab84-58265e25411a   \n",
       "646  f9b1dcdc-e54c-46a4-b92c-070509fd3447   \n",
       "647  af2cd0a9-6849-41cb-9cc7-de3d925c5047   \n",
       "648  cc51444f-24f2-4293-b44e-a2e43db1624d   \n",
       "649  14cbb917-0dd2-4c92-9ff7-8d615fb3b5fa   \n",
       "\n",
       "                                  user_id                           item_id  \\\n",
       "0    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  f12cb91a7885f1e027e0e0a65a52ddbf   \n",
       "1    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  01754fd08306328a5c786d986d56f2d1   \n",
       "2    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  83cba0493179ae3d5198cfadbbebf603   \n",
       "3    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  b40558e18a88b22be1709b473f46771f   \n",
       "4    2e7d8278-2533-45e2-a1b9-0a702f87ddbf  d1358cfce5e18b1adef106f37428153e   \n",
       "..                                    ...                               ...   \n",
       "645  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  040c7b744439c7b0db4b6cfe122ec43c   \n",
       "646  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  e95ecaf4e5242401a41ba615313827e9   \n",
       "647  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  806d963d48d0b6eb54d54c51da97e42d   \n",
       "648  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  b6eb95893e90cdec2328a923cb8838fa   \n",
       "649  2e7d8278-2533-45e2-a1b9-0a702f87ddbf  c3dabbef2757582a22a58636fc9b00af   \n",
       "\n",
       "      item_type  play_count           play_date  rating starred  \\\n",
       "0    media_file         1.0 2021-11-30 01:59:43     3.0   False   \n",
       "1         album         2.0 2021-11-30 21:29:44     0.0   False   \n",
       "2    media_file         1.0 2021-11-30 02:11:51     3.0    True   \n",
       "3    media_file         1.0 2021-11-16 21:56:20     3.0   False   \n",
       "4    media_file         0.0                 NaT     3.0   False   \n",
       "..          ...         ...                 ...     ...     ...   \n",
       "645  media_file         0.0                 NaT     3.0     0.0   \n",
       "646  media_file         0.0                 NaT     3.0     0.0   \n",
       "647  media_file         1.0 2022-02-14 03:51:38     3.0     0.0   \n",
       "648  media_file         1.0 2022-02-13 02:15:37     3.0     0.0   \n",
       "649  media_file        26.0 2012-07-22 05:13:44     3.0     0.0   \n",
       "\n",
       "             starred_at  \n",
       "0                   NaT  \n",
       "1                   NaT  \n",
       "2   2021-11-30 02:11:59  \n",
       "3                   NaT  \n",
       "4                   NaT  \n",
       "..                  ...  \n",
       "645                 NaT  \n",
       "646                 NaT  \n",
       "647                 NaT  \n",
       "648                 NaT  \n",
       "649                 NaT  \n",
       "\n",
       "[650 rows x 9 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_annotation_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "9d95c791-bd87-4999-baf3-4930a5b48a80",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_annotation_df = merged_annotation_df.astype(annotation_df.dtypes.to_dict())\n",
    "merged_annotation_df = merged_annotation_df.astype({\"ann_id\": \"string\", \"user_id\": \"string\", \"item_id\": \"string\", \"item_type\": \"string\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "4eb9f069-7f2b-47fa-863b-9090fc41b74c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ann_id                object\n",
       "user_id               object\n",
       "item_id               object\n",
       "item_type             object\n",
       "play_count             int64\n",
       "play_date     datetime64[ns]\n",
       "rating                 int64\n",
       "starred                 bool\n",
       "starred_at    datetime64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annotation_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "03280cef-5a6f-4100-b84b-9b7dd814d5b6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ann_id                string\n",
       "user_id               string\n",
       "item_id               string\n",
       "item_type             string\n",
       "play_count             int64\n",
       "play_date     datetime64[ns]\n",
       "rating                 int64\n",
       "starred                 bool\n",
       "starred_at    datetime64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_annotation_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "7a0ded83-96b2-4a59-b3ee-07d22fc3e041",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create your connection.\n",
    "engine = sqlalchemy.create_engine(\"sqlite:///\" + NEW_NAVIDROME_LIBRARY_LOCATION)\n",
    "\n",
    "with engine.connect() as conn:\n",
    "    merged_annotation_df.to_sql(name='annotation', con=conn, if_exists='replace', index=False)\n",
    "\n",
    "engine.dispose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1c8c582-9e9c-4884-8008-ad15b32747e1",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}