initial_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# imports\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reading the csv into a dataframe, requires data/chartsTop200_2017-2021.csv\n",
    "d0 = pd.read_csv('data/chartsTop200_2017-2021.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 26173514 entries, 0 to 26173513\n",
      "Data columns (total 9 columns):\n",
      " #   Column   Dtype  \n",
      "---  ------   -----  \n",
      " 0   title    object \n",
      " 1   rank     int64  \n",
      " 2   date     object \n",
      " 3   artist   object \n",
      " 4   url      object \n",
      " 5   region   object \n",
      " 6   chart    object \n",
      " 7   trend    object \n",
      " 8   streams  float64\n",
      "dtypes: float64(1), int64(1), object(7)\n",
      "memory usage: 1.8+ GB\n",
      "None (26173514, 9)\n"
     ]
    }
   ],
   "source": [
    "# 26.1 million rows, 9 columns\n",
    "print(d0.info(), d0.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>url</th>\n",
       "      <th>region</th>\n",
       "      <th>chart</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chantaje (feat. Maluma)</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Shakira</td>\n",
       "      <td>https://open.spotify.com/track/6mICuAdrwEjh6Y6...</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>top200</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>253019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vente Pa' Ca (feat. Maluma)</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Ricky Martin</td>\n",
       "      <td>https://open.spotify.com/track/7DM4BPaS7uofFul...</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>223988.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         title  rank        date        artist  \\\n",
       "0      Chantaje (feat. Maluma)     1  2017-01-01       Shakira   \n",
       "1  Vente Pa' Ca (feat. Maluma)     2  2017-01-01  Ricky Martin   \n",
       "\n",
       "                                                 url     region   chart  \\\n",
       "0  https://open.spotify.com/track/6mICuAdrwEjh6Y6...  Argentina  top200   \n",
       "1  https://open.spotify.com/track/7DM4BPaS7uofFul...  Argentina  top200   \n",
       "\n",
       "           trend   streams  \n",
       "0  SAME_POSITION  253019.0  \n",
       "1        MOVE_UP  223988.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# just a sneak peek\n",
    "d0.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5851610"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking count of null values for each column manually\n",
    "d0.streams.isnull().values.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "streams\n",
       "False    20321904\n",
       "True      5851610\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# alternative method \n",
    "d0.streams.isnull().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking if there are no Nan's in the region column\n",
    "d0.region.notnull().all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function for count of null values for all columns\n",
    "def count_nulls(data):\n",
    "    n = []\n",
    "    c = data.columns.to_list()\n",
    "    for i in data.columns:\n",
    "        n.append(data[i].isnull().values.sum())\n",
    "    nulls = pd.DataFrame({'name': c, 'count': n})\n",
    "    return nulls   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>title</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rank</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>date</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>artist</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>url</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>region</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>chart</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>trend</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>streams</td>\n",
       "      <td>5851610</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name    count\n",
       "0    title       11\n",
       "1     rank        0\n",
       "2     date        0\n",
       "3   artist       18\n",
       "4      url        0\n",
       "5   region        0\n",
       "6    chart        0\n",
       "7    trend        0\n",
       "8  streams  5851610"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count of null values for all columns\n",
    "count_nulls(d0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>url</th>\n",
       "      <th>region</th>\n",
       "      <th>chart</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7305002</th>\n",
       "      <td>NaN</td>\n",
       "      <td>120</td>\n",
       "      <td>2019-06-24</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>NEW_ENTRY</td>\n",
       "      <td>11942.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7401555</th>\n",
       "      <td>NaN</td>\n",
       "      <td>167</td>\n",
       "      <td>2019-06-25</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>10310.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7479161</th>\n",
       "      <td>NaN</td>\n",
       "      <td>128</td>\n",
       "      <td>2019-06-26</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>11620.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7563494</th>\n",
       "      <td>NaN</td>\n",
       "      <td>164</td>\n",
       "      <td>2019-06-27</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>10229.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7670140</th>\n",
       "      <td>NaN</td>\n",
       "      <td>174</td>\n",
       "      <td>2019-06-28</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>9348.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7772268</th>\n",
       "      <td>NaN</td>\n",
       "      <td>188</td>\n",
       "      <td>2019-06-29</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>9808.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7879888</th>\n",
       "      <td>NaN</td>\n",
       "      <td>169</td>\n",
       "      <td>2019-06-30</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>10900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14391788</th>\n",
       "      <td>NaN</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-07-15</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>NEW_ENTRY</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14481594</th>\n",
       "      <td>NaN</td>\n",
       "      <td>48</td>\n",
       "      <td>2019-07-16</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14768101</th>\n",
       "      <td>NaN</td>\n",
       "      <td>46</td>\n",
       "      <td>2019-07-17</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14773425</th>\n",
       "      <td>NaN</td>\n",
       "      <td>48</td>\n",
       "      <td>2019-07-18</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         title  rank        date artist  \\\n",
       "7305002    NaN   120  2019-06-24  Nissy   \n",
       "7401555    NaN   167  2019-06-25  Nissy   \n",
       "7479161    NaN   128  2019-06-26  Nissy   \n",
       "7563494    NaN   164  2019-06-27  Nissy   \n",
       "7670140    NaN   174  2019-06-28  Nissy   \n",
       "7772268    NaN   188  2019-06-29  Nissy   \n",
       "7879888    NaN   169  2019-06-30  Nissy   \n",
       "14391788   NaN    38  2019-07-15  Nissy   \n",
       "14481594   NaN    48  2019-07-16  Nissy   \n",
       "14768101   NaN    46  2019-07-17  Nissy   \n",
       "14773425   NaN    48  2019-07-18  Nissy   \n",
       "\n",
       "                                                        url region    chart  \\\n",
       "7305002   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7401555   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7479161   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7563494   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7670140   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7772268   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7879888   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "14391788  https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan  viral50   \n",
       "14481594  https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan  viral50   \n",
       "14768101  https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan  viral50   \n",
       "14773425  https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan  viral50   \n",
       "\n",
       "              trend  streams  \n",
       "7305002   NEW_ENTRY  11942.0  \n",
       "7401555   MOVE_DOWN  10310.0  \n",
       "7479161     MOVE_UP  11620.0  \n",
       "7563494   MOVE_DOWN  10229.0  \n",
       "7670140   MOVE_DOWN   9348.0  \n",
       "7772268   MOVE_DOWN   9808.0  \n",
       "7879888     MOVE_UP  10900.0  \n",
       "14391788  NEW_ENTRY      NaN  \n",
       "14481594  MOVE_DOWN      NaN  \n",
       "14768101    MOVE_UP      NaN  \n",
       "14773425  MOVE_DOWN      NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check all null values of the title column\n",
    "d0.query('title.isnull()')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7305002    https://open.spotify.com/track/4cP6KmNvTFkLHZo...\n",
       "Name: url, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# getting the url of the song with the missing title\n",
    "d0[d0['url'].str.contains('4cP6KmNvTFkLHZo')].url.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "974"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# total occurrence of 'Nissy' artist in the database\n",
    "d0.query('artist == \"Nissy\"').artist.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>url</th>\n",
       "      <th>region</th>\n",
       "      <th>chart</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7305002</th>\n",
       "      <td>No Title</td>\n",
       "      <td>120</td>\n",
       "      <td>2019-06-24</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>NEW_ENTRY</td>\n",
       "      <td>11942.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7401555</th>\n",
       "      <td>No Title</td>\n",
       "      <td>167</td>\n",
       "      <td>2019-06-25</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>10310.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7479161</th>\n",
       "      <td>No Title</td>\n",
       "      <td>128</td>\n",
       "      <td>2019-06-26</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>11620.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7563494</th>\n",
       "      <td>No Title</td>\n",
       "      <td>164</td>\n",
       "      <td>2019-06-27</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>10229.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7670140</th>\n",
       "      <td>No Title</td>\n",
       "      <td>174</td>\n",
       "      <td>2019-06-28</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>9348.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7772268</th>\n",
       "      <td>No Title</td>\n",
       "      <td>188</td>\n",
       "      <td>2019-06-29</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>9808.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7879888</th>\n",
       "      <td>No Title</td>\n",
       "      <td>169</td>\n",
       "      <td>2019-06-30</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>10900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14391788</th>\n",
       "      <td>No Title</td>\n",
       "      <td>38</td>\n",
       "      <td>2019-07-15</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>NEW_ENTRY</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14481594</th>\n",
       "      <td>No Title</td>\n",
       "      <td>48</td>\n",
       "      <td>2019-07-16</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14768101</th>\n",
       "      <td>No Title</td>\n",
       "      <td>46</td>\n",
       "      <td>2019-07-17</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14773425</th>\n",
       "      <td>No Title</td>\n",
       "      <td>48</td>\n",
       "      <td>2019-07-18</td>\n",
       "      <td>Nissy</td>\n",
       "      <td>https://open.spotify.com/track/4cP6KmNvTFkLHZo...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             title  rank        date artist  \\\n",
       "7305002   No Title   120  2019-06-24  Nissy   \n",
       "7401555   No Title   167  2019-06-25  Nissy   \n",
       "7479161   No Title   128  2019-06-26  Nissy   \n",
       "7563494   No Title   164  2019-06-27  Nissy   \n",
       "7670140   No Title   174  2019-06-28  Nissy   \n",
       "7772268   No Title   188  2019-06-29  Nissy   \n",
       "7879888   No Title   169  2019-06-30  Nissy   \n",
       "14391788  No Title    38  2019-07-15  Nissy   \n",
       "14481594  No Title    48  2019-07-16  Nissy   \n",
       "14768101  No Title    46  2019-07-17  Nissy   \n",
       "14773425  No Title    48  2019-07-18  Nissy   \n",
       "\n",
       "                                                        url region    chart  \\\n",
       "7305002   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7401555   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7479161   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7563494   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7670140   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7772268   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "7879888   https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan   top200   \n",
       "14391788  https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan  viral50   \n",
       "14481594  https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan  viral50   \n",
       "14768101  https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan  viral50   \n",
       "14773425  https://open.spotify.com/track/4cP6KmNvTFkLHZo...  Japan  viral50   \n",
       "\n",
       "              trend  streams  \n",
       "7305002   NEW_ENTRY  11942.0  \n",
       "7401555   MOVE_DOWN  10310.0  \n",
       "7479161     MOVE_UP  11620.0  \n",
       "7563494   MOVE_DOWN  10229.0  \n",
       "7670140   MOVE_DOWN   9348.0  \n",
       "7772268   MOVE_DOWN   9808.0  \n",
       "7879888     MOVE_UP  10900.0  \n",
       "14391788  NEW_ENTRY      NaN  \n",
       "14481594  MOVE_DOWN      NaN  \n",
       "14768101    MOVE_UP      NaN  \n",
       "14773425  MOVE_DOWN      NaN  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# giving a title to the song with the empty title\n",
    "d0.fillna(value={\"title\": \"No Title\"}, inplace=True)\n",
    "d0.query('title == \"No Title\"').head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>url</th>\n",
       "      <th>region</th>\n",
       "      <th>chart</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20596664</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>NEW_ENTRY</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20616457</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20640094</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20661724</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20677645</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20705363</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20726697</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20748638</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>13</td>\n",
       "      <td>2020-07-20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20788975</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-07-21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20833982</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>19</td>\n",
       "      <td>2020-07-22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20865824</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>23</td>\n",
       "      <td>2020-07-23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20887012</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>20</td>\n",
       "      <td>2020-07-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20903930</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-07-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20932541</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>24</td>\n",
       "      <td>2020-07-26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20960407</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>36</td>\n",
       "      <td>2020-07-27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20992837</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>43</td>\n",
       "      <td>2020-07-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21026109</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>40</td>\n",
       "      <td>2020-07-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21057585</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>46</td>\n",
       "      <td>2020-07-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            title  rank        date artist  \\\n",
       "20596664  NO GOOD    10  2020-07-13    NaN   \n",
       "20616457  NO GOOD    10  2020-07-14    NaN   \n",
       "20640094  NO GOOD    10  2020-07-15    NaN   \n",
       "20661724  NO GOOD    10  2020-07-16    NaN   \n",
       "20677645  NO GOOD    10  2020-07-17    NaN   \n",
       "20705363  NO GOOD    10  2020-07-18    NaN   \n",
       "20726697  NO GOOD    10  2020-07-19    NaN   \n",
       "20748638  NO GOOD    13  2020-07-20    NaN   \n",
       "20788975  NO GOOD    14  2020-07-21    NaN   \n",
       "20833982  NO GOOD    19  2020-07-22    NaN   \n",
       "20865824  NO GOOD    23  2020-07-23    NaN   \n",
       "20887012  NO GOOD    20  2020-07-24    NaN   \n",
       "20903930  NO GOOD    21  2020-07-25    NaN   \n",
       "20932541  NO GOOD    24  2020-07-26    NaN   \n",
       "20960407  NO GOOD    36  2020-07-27    NaN   \n",
       "20992837  NO GOOD    43  2020-07-28    NaN   \n",
       "21026109  NO GOOD    40  2020-07-29    NaN   \n",
       "21057585  NO GOOD    46  2020-07-30    NaN   \n",
       "\n",
       "                                                        url region    chart  \\\n",
       "20596664  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20616457  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20640094  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20661724  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20677645  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20705363  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20726697  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20748638  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20788975  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20833982  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20865824  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20887012  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20903930  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20932541  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20960407  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20992837  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "21026109  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "21057585  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "\n",
       "                  trend  streams  \n",
       "20596664      NEW_ENTRY      NaN  \n",
       "20616457  SAME_POSITION      NaN  \n",
       "20640094  SAME_POSITION      NaN  \n",
       "20661724  SAME_POSITION      NaN  \n",
       "20677645  SAME_POSITION      NaN  \n",
       "20705363  SAME_POSITION      NaN  \n",
       "20726697  SAME_POSITION      NaN  \n",
       "20748638      MOVE_DOWN      NaN  \n",
       "20788975      MOVE_DOWN      NaN  \n",
       "20833982      MOVE_DOWN      NaN  \n",
       "20865824      MOVE_DOWN      NaN  \n",
       "20887012        MOVE_UP      NaN  \n",
       "20903930      MOVE_DOWN      NaN  \n",
       "20932541      MOVE_DOWN      NaN  \n",
       "20960407      MOVE_DOWN      NaN  \n",
       "20992837      MOVE_DOWN      NaN  \n",
       "21026109        MOVE_UP      NaN  \n",
       "21057585      MOVE_DOWN      NaN  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rows with Nan value in the artist column\n",
    "d0.query('artist.isnull()').head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "20596664    https://open.spotify.com/track/4Qnz8tARYhUtDNe...\n",
       "Name: url, dtype: object"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# gettng the url\n",
    "d0.query('artist.isnull()').url.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# no other songs with the same title\n",
    "d0.query('title == \"NO GOOD\" and not artist.isnull()')['rank'].count().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>url</th>\n",
       "      <th>region</th>\n",
       "      <th>chart</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20596664</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-13</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>NEW_ENTRY</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20616457</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-14</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20640094</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-15</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20661724</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-16</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20677645</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-17</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20705363</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-18</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20726697</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>10</td>\n",
       "      <td>2020-07-19</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20748638</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>13</td>\n",
       "      <td>2020-07-20</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20788975</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>14</td>\n",
       "      <td>2020-07-21</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20833982</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>19</td>\n",
       "      <td>2020-07-22</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20865824</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>23</td>\n",
       "      <td>2020-07-23</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20887012</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>20</td>\n",
       "      <td>2020-07-24</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20903930</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>21</td>\n",
       "      <td>2020-07-25</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20932541</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>24</td>\n",
       "      <td>2020-07-26</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20960407</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>36</td>\n",
       "      <td>2020-07-27</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20992837</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>43</td>\n",
       "      <td>2020-07-28</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21026109</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>40</td>\n",
       "      <td>2020-07-29</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21057585</th>\n",
       "      <td>NO GOOD</td>\n",
       "      <td>46</td>\n",
       "      <td>2020-07-30</td>\n",
       "      <td>N/A</td>\n",
       "      <td>https://open.spotify.com/track/4Qnz8tARYhUtDNe...</td>\n",
       "      <td>Japan</td>\n",
       "      <td>viral50</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            title  rank        date artist  \\\n",
       "20596664  NO GOOD    10  2020-07-13    N/A   \n",
       "20616457  NO GOOD    10  2020-07-14    N/A   \n",
       "20640094  NO GOOD    10  2020-07-15    N/A   \n",
       "20661724  NO GOOD    10  2020-07-16    N/A   \n",
       "20677645  NO GOOD    10  2020-07-17    N/A   \n",
       "20705363  NO GOOD    10  2020-07-18    N/A   \n",
       "20726697  NO GOOD    10  2020-07-19    N/A   \n",
       "20748638  NO GOOD    13  2020-07-20    N/A   \n",
       "20788975  NO GOOD    14  2020-07-21    N/A   \n",
       "20833982  NO GOOD    19  2020-07-22    N/A   \n",
       "20865824  NO GOOD    23  2020-07-23    N/A   \n",
       "20887012  NO GOOD    20  2020-07-24    N/A   \n",
       "20903930  NO GOOD    21  2020-07-25    N/A   \n",
       "20932541  NO GOOD    24  2020-07-26    N/A   \n",
       "20960407  NO GOOD    36  2020-07-27    N/A   \n",
       "20992837  NO GOOD    43  2020-07-28    N/A   \n",
       "21026109  NO GOOD    40  2020-07-29    N/A   \n",
       "21057585  NO GOOD    46  2020-07-30    N/A   \n",
       "\n",
       "                                                        url region    chart  \\\n",
       "20596664  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20616457  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20640094  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20661724  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20677645  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20705363  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20726697  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20748638  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20788975  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20833982  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20865824  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20887012  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20903930  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20932541  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20960407  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "20992837  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "21026109  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "21057585  https://open.spotify.com/track/4Qnz8tARYhUtDNe...  Japan  viral50   \n",
       "\n",
       "                  trend  streams  \n",
       "20596664      NEW_ENTRY      NaN  \n",
       "20616457  SAME_POSITION      NaN  \n",
       "20640094  SAME_POSITION      NaN  \n",
       "20661724  SAME_POSITION      NaN  \n",
       "20677645  SAME_POSITION      NaN  \n",
       "20705363  SAME_POSITION      NaN  \n",
       "20726697  SAME_POSITION      NaN  \n",
       "20748638      MOVE_DOWN      NaN  \n",
       "20788975      MOVE_DOWN      NaN  \n",
       "20833982      MOVE_DOWN      NaN  \n",
       "20865824      MOVE_DOWN      NaN  \n",
       "20887012        MOVE_UP      NaN  \n",
       "20903930      MOVE_DOWN      NaN  \n",
       "20932541      MOVE_DOWN      NaN  \n",
       "20960407      MOVE_DOWN      NaN  \n",
       "20992837      MOVE_DOWN      NaN  \n",
       "21026109        MOVE_UP      NaN  \n",
       "21057585      MOVE_DOWN      NaN  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# found the song online, the artist is called N/A\n",
    "d0.fillna(value={\"artist\": \"N/A\"}, inplace=True) \n",
    "d0.query('artist == \"N/A\"').head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>title</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rank</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>date</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>artist</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>url</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>region</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>chart</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>trend</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>streams</td>\n",
       "      <td>5851610</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      name    count\n",
       "0    title        0\n",
       "1     rank        0\n",
       "2     date        0\n",
       "3   artist        0\n",
       "4      url        0\n",
       "5   region        0\n",
       "6    chart        0\n",
       "7    trend        0\n",
       "8  streams  5851610"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count of null values for all columns\n",
    "count_nulls(d0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26173514 26173514\n"
     ]
    }
   ],
   "source": [
    "# count total url values vs url values containing 'https://open.spotify.com/track/', all url rows contain the exact string\n",
    "count1 = d0.url.count()\n",
    "mask = d0['url'].str.contains('https://open.spotify.com/track/')\n",
    "count2 = d0[mask]['url'].count()\n",
    "print(count1, count2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# double checking the inverted mask is 0 url rows\n",
    "d0[~mask]['url'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# double checking the string url length is always 53\n",
    "d1 = d0.copy()\n",
    "d1['url_length'] = d1.url.str.len()\n",
    "d1.query('url_length != 53 and not url_length > 53 and not url_length < 53')['url_length'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>url</th>\n",
       "      <th>region</th>\n",
       "      <th>chart</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chantaje (feat. Maluma)</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Shakira</td>\n",
       "      <td>6mICuAdrwEjh6Y6lroV2Kg</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>top200</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>253019.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Vente Pa' Ca (feat. Maluma)</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Ricky Martin</td>\n",
       "      <td>7DM4BPaS7uofFul3ywMe46</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>223988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Reggaetón Lento (Bailemos)</td>\n",
       "      <td>3</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>CNCO</td>\n",
       "      <td>3AEZUABDXNtecAOSC1qTfo</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>top200</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>210943.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         title  rank        date        artist  \\\n",
       "0      Chantaje (feat. Maluma)     1  2017-01-01       Shakira   \n",
       "1  Vente Pa' Ca (feat. Maluma)     2  2017-01-01  Ricky Martin   \n",
       "2   Reggaetón Lento (Bailemos)     3  2017-01-01          CNCO   \n",
       "\n",
       "                      url     region   chart          trend   streams  \n",
       "0  6mICuAdrwEjh6Y6lroV2Kg  Argentina  top200  SAME_POSITION  253019.0  \n",
       "1  7DM4BPaS7uofFul3ywMe46  Argentina  top200        MOVE_UP  223988.0  \n",
       "2  3AEZUABDXNtecAOSC1qTfo  Argentina  top200      MOVE_DOWN  210943.0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# shortening the url to the track id\n",
    "d0['url'].replace('https://open.spotify.com/track/', '', regex=True, inplace=True)\n",
    "d0.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>track_id</th>\n",
       "      <th>region</th>\n",
       "      <th>chart</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chantaje (feat. Maluma)</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Shakira</td>\n",
       "      <td>6mICuAdrwEjh6Y6lroV2Kg</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>top200</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>253019.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     title  rank        date   artist                track_id  \\\n",
       "0  Chantaje (feat. Maluma)     1  2017-01-01  Shakira  6mICuAdrwEjh6Y6lroV2Kg   \n",
       "\n",
       "      region   chart          trend   streams  \n",
       "0  Argentina  top200  SAME_POSITION  253019.0  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rename the url column to track_id\n",
    "d0.rename(columns={'url':'track_id'}, inplace=True)\n",
    "d0.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title       20321904\n",
       "rank        20321904\n",
       "date        20321904\n",
       "artist      20321904\n",
       "track_id    20321904\n",
       "region      20321904\n",
       "chart       20321904\n",
       "trend       20321904\n",
       "streams     20321904\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating 'top200' dataframe\n",
    "d_t200 = d0.query('chart == \"top200\"')\n",
    "d_t200.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title       5851610\n",
       "rank        5851610\n",
       "date        5851610\n",
       "artist      5851610\n",
       "track_id    5851610\n",
       "region      5851610\n",
       "chart       5851610\n",
       "trend       5851610\n",
       "streams           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating 'viral50' dataframe\n",
    "d_v50 = d0.query('chart == \"viral50\"')\n",
    "d_v50.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "26173514 26173514\n"
     ]
    }
   ],
   "source": [
    "# double checking total of rows\n",
    "total_count = d0['chart'].count()\n",
    "v50_count = d_v50['chart'].count() \n",
    "t200_count = d_t200['chart'].count()\n",
    "sum_divided = v50_count + t200_count\n",
    "print(sum_divided, total_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove chart column in new dataframes, also the streams column in the viral 50\n",
    "d_t200_final = d_t200.drop('chart', axis=1)\n",
    "d_v50_final = d_v50.drop(['chart', 'streams'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating top 100 international charts\n",
    "d_t100 = d_t200_final.query('rank <= 100')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating top 100 germany charts\n",
    "d_t100_de = d_t100.query('region == \"Germany\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# removing the region in new top 100 germany charts\n",
    "d_t100_de_x = d_t100_de.drop('region', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "title       7156\n",
       "rank          52\n",
       "date        1817\n",
       "artist      5694\n",
       "track_id    7876\n",
       "region         1\n",
       "trend          4\n",
       "dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# unique songs in the viral top 50 charts\n",
    "d_v50_final.query('region == \"Germany\"').nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "69"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_t100.region.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Argentina', 'Australia', 'Austria', 'Belgium', 'Colombia',\n",
       "       'Bolivia', 'Brazil', 'Bulgaria', 'Canada', 'Denmark', 'Chile',\n",
       "       'Costa Rica', 'Czech Republic', 'Finland', 'Dominican Republic',\n",
       "       'Ecuador', 'El Salvador', 'Estonia', 'France', 'Germany', 'Global',\n",
       "       'Greece', 'Guatemala', 'Honduras', 'Hong Kong', 'Hungary',\n",
       "       'Iceland', 'Indonesia', 'Ireland', 'Italy', 'Japan', 'Latvia',\n",
       "       'Lithuania', 'Malaysia', 'Luxembourg', 'Mexico', 'Netherlands',\n",
       "       'New Zealand', 'Nicaragua', 'Norway', 'Panama', 'Paraguay', 'Peru',\n",
       "       'Philippines', 'Poland', 'Portugal', 'Singapore', 'Spain',\n",
       "       'Slovakia', 'Sweden', 'Taiwan', 'Switzerland', 'Turkey',\n",
       "       'United Kingdom', 'United States', 'Uruguay', 'Thailand', 'Egypt',\n",
       "       'India', 'Israel', 'Morocco', 'Romania', 'Saudi Arabia',\n",
       "       'South Africa', 'United Arab Emirates', 'Vietnam', 'Ukraine',\n",
       "       'Russia', 'South Korea'], dtype=object)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# list of countries included in the daily top 100 charts\n",
    "d_t100.region.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create dataframe with 5 countries South Africa, USA, South Korea, Mexico, Germany\n",
    "d_t100_5c = d_t100.query('region == \"Germany\" or region == \"United States\" or region == \"South Korea\" or region == \"Mexico\" or region == \"South Africa\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>track_id</th>\n",
       "      <th>region</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3033</th>\n",
       "      <td>Rockabye (feat. Sean Paul &amp; Anne-Marie)</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Clean Bandit</td>\n",
       "      <td>5knuzwU65gJK7IF5yJsuaW</td>\n",
       "      <td>Germany</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>229398.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3034</th>\n",
       "      <td>Alone</td>\n",
       "      <td>2</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Alan Walker</td>\n",
       "      <td>0JiVRyTJcJnmlwCZ854K4p</td>\n",
       "      <td>Germany</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>192026.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3035</th>\n",
       "      <td>Chöre</td>\n",
       "      <td>3</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Mark Forster</td>\n",
       "      <td>2nYpbaLykC2cjU8Yt5KF1v</td>\n",
       "      <td>Germany</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>181869.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3036</th>\n",
       "      <td>Starboy</td>\n",
       "      <td>4</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>The Weeknd, Daft Punk</td>\n",
       "      <td>5aAx2yezTd8zXrkmtKl66Z</td>\n",
       "      <td>Germany</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>176328.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3037</th>\n",
       "      <td>Closer</td>\n",
       "      <td>5</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>The Chainsmokers, Halsey</td>\n",
       "      <td>7BKLCZ1jbUBVqRi2FVlTVw</td>\n",
       "      <td>Germany</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>170745.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        title  rank        date  \\\n",
       "3033  Rockabye (feat. Sean Paul & Anne-Marie)     1  2017-01-01   \n",
       "3034                                    Alone     2  2017-01-01   \n",
       "3035                                    Chöre     3  2017-01-01   \n",
       "3036                                  Starboy     4  2017-01-01   \n",
       "3037                                   Closer     5  2017-01-01   \n",
       "\n",
       "                        artist                track_id   region  \\\n",
       "3033              Clean Bandit  5knuzwU65gJK7IF5yJsuaW  Germany   \n",
       "3034               Alan Walker  0JiVRyTJcJnmlwCZ854K4p  Germany   \n",
       "3035              Mark Forster  2nYpbaLykC2cjU8Yt5KF1v  Germany   \n",
       "3036     The Weeknd, Daft Punk  5aAx2yezTd8zXrkmtKl66Z  Germany   \n",
       "3037  The Chainsmokers, Halsey  7BKLCZ1jbUBVqRi2FVlTVw  Germany   \n",
       "\n",
       "              trend   streams  \n",
       "3033  SAME_POSITION  229398.0  \n",
       "3034        MOVE_UP  192026.0  \n",
       "3035      MOVE_DOWN  181869.0  \n",
       "3036        MOVE_UP  176328.0  \n",
       "3037        MOVE_UP  170745.0  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_t100_5c.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d_v50</td>\n",
       "      <td>5851610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>d_t200</td>\n",
       "      <td>20321904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>d_t100</td>\n",
       "      <td>10739635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>d_t100_5c</td>\n",
       "      <td>694683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>d_t100_de</td>\n",
       "      <td>178724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>d_t100_de_x</td>\n",
       "      <td>178724</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name     count\n",
       "0        d_v50   5851610\n",
       "1       d_t200  20321904\n",
       "2       d_t100  10739635\n",
       "3    d_t100_5c    694683\n",
       "4    d_t100_de    178724\n",
       "5  d_t100_de_x    178724"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# total row count for 6 final dataframes\n",
    "c1 = d_v50_final['track_id'].count()\n",
    "c2 = d_t200_final['track_id'].count()\n",
    "c3 = d_t100['track_id'].count()\n",
    "c4 = d_t100_5c['track_id'].count()\n",
    "c5 = d_t100_de['track_id'].count()\n",
    "c6 = d_t100_de_x['track_id'].count()\n",
    "df_rows = pd.DataFrame({'name':['d_v50', 'd_t200', 'd_t100', 'd_t100_5c', 'd_t100_de', 'd_t100_de_x'], 'count':[c1, c2, c3, c4, c5, c6]})\n",
    "df_rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>track_id</th>\n",
       "      <th>region</th>\n",
       "      <th>trend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5851605</th>\n",
       "      <td>BYE</td>\n",
       "      <td>46</td>\n",
       "      <td>2021-07-31</td>\n",
       "      <td>Jaden</td>\n",
       "      <td>3OUyyDN7EZrL7i0Sbi5SVd</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>MOVE_UP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5851606</th>\n",
       "      <td>Pillars</td>\n",
       "      <td>47</td>\n",
       "      <td>2021-07-31</td>\n",
       "      <td>My Anh</td>\n",
       "      <td>6eky30oFiQbHUATDlOCGys</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>NEW_ENTRY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5851607</th>\n",
       "      <td>Gái Độc Thân</td>\n",
       "      <td>48</td>\n",
       "      <td>2021-07-31</td>\n",
       "      <td>Tlinh</td>\n",
       "      <td>2klsSb2iTfgDh95Ak9uWY2</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5851608</th>\n",
       "      <td>Renegade (feat. Taylor Swift)</td>\n",
       "      <td>49</td>\n",
       "      <td>2021-07-31</td>\n",
       "      <td>Big Red Machine</td>\n",
       "      <td>1aU1wpYBSpP0M6IiihY5Ue</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5851609</th>\n",
       "      <td>Letter to Jarad</td>\n",
       "      <td>50</td>\n",
       "      <td>2021-07-31</td>\n",
       "      <td>LRN Slime, Shiloh Dynasty</td>\n",
       "      <td>508QhA2SncMbh5CnuxkvUc</td>\n",
       "      <td>Vietnam</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 title  rank        date  \\\n",
       "5851605                            BYE    46  2021-07-31   \n",
       "5851606                        Pillars    47  2021-07-31   \n",
       "5851607                   Gái Độc Thân    48  2021-07-31   \n",
       "5851608  Renegade (feat. Taylor Swift)    49  2021-07-31   \n",
       "5851609                Letter to Jarad    50  2021-07-31   \n",
       "\n",
       "                            artist                track_id   region      trend  \n",
       "5851605                      Jaden  3OUyyDN7EZrL7i0Sbi5SVd  Vietnam    MOVE_UP  \n",
       "5851606                     My Anh  6eky30oFiQbHUATDlOCGys  Vietnam  NEW_ENTRY  \n",
       "5851607                      Tlinh  2klsSb2iTfgDh95Ak9uWY2  Vietnam  MOVE_DOWN  \n",
       "5851608            Big Red Machine  1aU1wpYBSpP0M6IiihY5Ue  Vietnam  MOVE_DOWN  \n",
       "5851609  LRN Slime, Shiloh Dynasty  508QhA2SncMbh5CnuxkvUc  Vietnam  MOVE_DOWN  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_v50_final.reset_index(drop=True, inplace=True)\n",
    "d_v50_final.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>track_id</th>\n",
       "      <th>region</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20321899</th>\n",
       "      <td>Ojalá (feat. Darell)</td>\n",
       "      <td>196</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>De La Ghetto, Almighty, Bryant Myers</td>\n",
       "      <td>3EMDvnVpQd9RZJvKSpOhUm</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>1178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20321900</th>\n",
       "      <td>Lo Que Pasa en la Noche</td>\n",
       "      <td>197</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Mano Arriba</td>\n",
       "      <td>2eOleVJlGvBE027wpnYt2H</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>NEW_ENTRY</td>\n",
       "      <td>1178.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20321901</th>\n",
       "      <td>El Equivocado</td>\n",
       "      <td>198</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Mano Arriba</td>\n",
       "      <td>5vy1C7DD9xJ5fRB3KqV6Kq</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>1170.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20321902</th>\n",
       "      <td>Que Fui Tu Amante</td>\n",
       "      <td>199</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>El Gucci y Su Banda</td>\n",
       "      <td>1fmiCxwEbZFIszITXsV65M</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>1165.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20321903</th>\n",
       "      <td>Solo</td>\n",
       "      <td>200</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Amenazzy, Lary Over</td>\n",
       "      <td>2rrJfOphzn4uQCK9X0x6R6</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>NEW_ENTRY</td>\n",
       "      <td>1162.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            title  rank        date  \\\n",
       "20321899     Ojalá (feat. Darell)   196  2018-01-31   \n",
       "20321900  Lo Que Pasa en la Noche   197  2018-01-31   \n",
       "20321901            El Equivocado   198  2018-01-31   \n",
       "20321902        Que Fui Tu Amante   199  2018-01-31   \n",
       "20321903                     Solo   200  2018-01-31   \n",
       "\n",
       "                                        artist                track_id  \\\n",
       "20321899  De La Ghetto, Almighty, Bryant Myers  3EMDvnVpQd9RZJvKSpOhUm   \n",
       "20321900                           Mano Arriba  2eOleVJlGvBE027wpnYt2H   \n",
       "20321901                           Mano Arriba  5vy1C7DD9xJ5fRB3KqV6Kq   \n",
       "20321902                   El Gucci y Su Banda  1fmiCxwEbZFIszITXsV65M   \n",
       "20321903                   Amenazzy, Lary Over  2rrJfOphzn4uQCK9X0x6R6   \n",
       "\n",
       "           region      trend  streams  \n",
       "20321899  Uruguay  MOVE_DOWN   1178.0  \n",
       "20321900  Uruguay  NEW_ENTRY   1178.0  \n",
       "20321901  Uruguay  MOVE_DOWN   1170.0  \n",
       "20321902  Uruguay  MOVE_DOWN   1165.0  \n",
       "20321903  Uruguay  NEW_ENTRY   1162.0  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_t200_final.reset_index(drop=True, inplace=True)\n",
    "d_t200_final.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>track_id</th>\n",
       "      <th>region</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10739630</th>\n",
       "      <td>Cuando Se Pone a Bailar</td>\n",
       "      <td>96</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Rombai</td>\n",
       "      <td>1MpKZi1zTXpERKwxmOu1PH</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>2007.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10739631</th>\n",
       "      <td>Otra vez (feat. J Balvin)</td>\n",
       "      <td>97</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Zion &amp; Lennox</td>\n",
       "      <td>3QwBODjSEzelZyVjxPOHdq</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>1991.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10739632</th>\n",
       "      <td>La Rompe Corazones</td>\n",
       "      <td>98</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Daddy Yankee, Ozuna</td>\n",
       "      <td>4okba5wu9mMLXx79DXLKi3</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>1988.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10739633</th>\n",
       "      <td>rockstar</td>\n",
       "      <td>99</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Post Malone, 21 Savage</td>\n",
       "      <td>7wGoVu4Dady5GV0Sv4UIsx</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>1965.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10739634</th>\n",
       "      <td>Una y Otra Vez</td>\n",
       "      <td>100</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Rombai</td>\n",
       "      <td>3VSt7R9LHTomKGP1RhkvuT</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>1958.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              title  rank        date                  artist  \\\n",
       "10739630    Cuando Se Pone a Bailar    96  2018-01-31                  Rombai   \n",
       "10739631  Otra vez (feat. J Balvin)    97  2018-01-31           Zion & Lennox   \n",
       "10739632         La Rompe Corazones    98  2018-01-31     Daddy Yankee, Ozuna   \n",
       "10739633                   rockstar    99  2018-01-31  Post Malone, 21 Savage   \n",
       "10739634             Una y Otra Vez   100  2018-01-31                  Rombai   \n",
       "\n",
       "                        track_id   region      trend  streams  \n",
       "10739630  1MpKZi1zTXpERKwxmOu1PH  Uruguay    MOVE_UP   2007.0  \n",
       "10739631  3QwBODjSEzelZyVjxPOHdq  Uruguay  MOVE_DOWN   1991.0  \n",
       "10739632  4okba5wu9mMLXx79DXLKi3  Uruguay  MOVE_DOWN   1988.0  \n",
       "10739633  7wGoVu4Dady5GV0Sv4UIsx  Uruguay    MOVE_UP   1965.0  \n",
       "10739634  3VSt7R9LHTomKGP1RhkvuT  Uruguay    MOVE_UP   1958.0  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_t100.reset_index(drop=True, inplace=True)\n",
    "d_t100.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>track_id</th>\n",
       "      <th>region</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>694678</th>\n",
       "      <td>You Make It Easy</td>\n",
       "      <td>96</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Jason Aldean</td>\n",
       "      <td>2ZxTjs4EdJl4Y9YByIiNoo</td>\n",
       "      <td>United States</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>295550.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694679</th>\n",
       "      <td>Marry Me</td>\n",
       "      <td>97</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Thomas Rhett</td>\n",
       "      <td>0OWZFobGSIW9GrSlQ9C5pc</td>\n",
       "      <td>United States</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>294405.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694680</th>\n",
       "      <td>Capital Letters</td>\n",
       "      <td>98</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Hailee Steinfeld, BloodPop®</td>\n",
       "      <td>3towKHKVOXCCcL1fHv6xFO</td>\n",
       "      <td>United States</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>293109.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694681</th>\n",
       "      <td>That's What I Like</td>\n",
       "      <td>99</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Bruno Mars</td>\n",
       "      <td>0KKkJNfGyhkQ5aFogxQAPU</td>\n",
       "      <td>United States</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>292967.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694682</th>\n",
       "      <td>The Weekend</td>\n",
       "      <td>100</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>SZA</td>\n",
       "      <td>6gU9OKjOE7ghfEd55oRO57</td>\n",
       "      <td>United States</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>292261.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     title  rank        date                       artist  \\\n",
       "694678    You Make It Easy    96  2018-01-31                 Jason Aldean   \n",
       "694679            Marry Me    97  2018-01-31                 Thomas Rhett   \n",
       "694680     Capital Letters    98  2018-01-31  Hailee Steinfeld, BloodPop®   \n",
       "694681  That's What I Like    99  2018-01-31                   Bruno Mars   \n",
       "694682         The Weekend   100  2018-01-31                          SZA   \n",
       "\n",
       "                      track_id         region          trend   streams  \n",
       "694678  2ZxTjs4EdJl4Y9YByIiNoo  United States        MOVE_UP  295550.0  \n",
       "694679  0OWZFobGSIW9GrSlQ9C5pc  United States        MOVE_UP  294405.0  \n",
       "694680  3towKHKVOXCCcL1fHv6xFO  United States      MOVE_DOWN  293109.0  \n",
       "694681  0KKkJNfGyhkQ5aFogxQAPU  United States      MOVE_DOWN  292967.0  \n",
       "694682  6gU9OKjOE7ghfEd55oRO57  United States  SAME_POSITION  292261.0  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_t100_5c.reset_index(drop=True, inplace=True)\n",
    "d_t100_5c.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>track_id</th>\n",
       "      <th>region</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>178719</th>\n",
       "      <td>Von Party zu Party</td>\n",
       "      <td>96</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>SXTN</td>\n",
       "      <td>4h6UIDvGWTYZvu4BLf2GpO</td>\n",
       "      <td>Germany</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>62294.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178720</th>\n",
       "      <td>XO Tour Llif3</td>\n",
       "      <td>97</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Lil Uzi Vert</td>\n",
       "      <td>7GX5flRQZVHRAGd6B4TmDO</td>\n",
       "      <td>Germany</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>61681.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178721</th>\n",
       "      <td>I Fall Apart</td>\n",
       "      <td>98</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Post Malone</td>\n",
       "      <td>75ZvA4QfFiZvzhj2xkaWAh</td>\n",
       "      <td>Germany</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>60691.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178722</th>\n",
       "      <td>Gucci Gang</td>\n",
       "      <td>99</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Lil Pump</td>\n",
       "      <td>43ZyHQITOjhciSUUNPVRHc</td>\n",
       "      <td>Germany</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>59454.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178723</th>\n",
       "      <td>Handschellen (feat. Ardian Bujupi)</td>\n",
       "      <td>100</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Payy</td>\n",
       "      <td>4dL59MdDzLINk1Sq04y9Cf</td>\n",
       "      <td>Germany</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>58932.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     title  rank        date        artist  \\\n",
       "178719                  Von Party zu Party    96  2018-01-31          SXTN   \n",
       "178720                       XO Tour Llif3    97  2018-01-31  Lil Uzi Vert   \n",
       "178721                        I Fall Apart    98  2018-01-31   Post Malone   \n",
       "178722                          Gucci Gang    99  2018-01-31      Lil Pump   \n",
       "178723  Handschellen (feat. Ardian Bujupi)   100  2018-01-31          Payy   \n",
       "\n",
       "                      track_id   region          trend  streams  \n",
       "178719  4h6UIDvGWTYZvu4BLf2GpO  Germany      MOVE_DOWN  62294.0  \n",
       "178720  7GX5flRQZVHRAGd6B4TmDO  Germany      MOVE_DOWN  61681.0  \n",
       "178721  75ZvA4QfFiZvzhj2xkaWAh  Germany        MOVE_UP  60691.0  \n",
       "178722  43ZyHQITOjhciSUUNPVRHc  Germany  SAME_POSITION  59454.0  \n",
       "178723  4dL59MdDzLINk1Sq04y9Cf  Germany        MOVE_UP  58932.0  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_t100_de.reset_index(drop=True, inplace=True)\n",
    "d_t100_de.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>title</th>\n",
       "      <th>rank</th>\n",
       "      <th>date</th>\n",
       "      <th>artist</th>\n",
       "      <th>track_id</th>\n",
       "      <th>trend</th>\n",
       "      <th>streams</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>178719</th>\n",
       "      <td>Von Party zu Party</td>\n",
       "      <td>96</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>SXTN</td>\n",
       "      <td>4h6UIDvGWTYZvu4BLf2GpO</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>62294.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178720</th>\n",
       "      <td>XO Tour Llif3</td>\n",
       "      <td>97</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Lil Uzi Vert</td>\n",
       "      <td>7GX5flRQZVHRAGd6B4TmDO</td>\n",
       "      <td>MOVE_DOWN</td>\n",
       "      <td>61681.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178721</th>\n",
       "      <td>I Fall Apart</td>\n",
       "      <td>98</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Post Malone</td>\n",
       "      <td>75ZvA4QfFiZvzhj2xkaWAh</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>60691.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178722</th>\n",
       "      <td>Gucci Gang</td>\n",
       "      <td>99</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Lil Pump</td>\n",
       "      <td>43ZyHQITOjhciSUUNPVRHc</td>\n",
       "      <td>SAME_POSITION</td>\n",
       "      <td>59454.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178723</th>\n",
       "      <td>Handschellen (feat. Ardian Bujupi)</td>\n",
       "      <td>100</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>Payy</td>\n",
       "      <td>4dL59MdDzLINk1Sq04y9Cf</td>\n",
       "      <td>MOVE_UP</td>\n",
       "      <td>58932.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     title  rank        date        artist  \\\n",
       "178719                  Von Party zu Party    96  2018-01-31          SXTN   \n",
       "178720                       XO Tour Llif3    97  2018-01-31  Lil Uzi Vert   \n",
       "178721                        I Fall Apart    98  2018-01-31   Post Malone   \n",
       "178722                          Gucci Gang    99  2018-01-31      Lil Pump   \n",
       "178723  Handschellen (feat. Ardian Bujupi)   100  2018-01-31          Payy   \n",
       "\n",
       "                      track_id          trend  streams  \n",
       "178719  4h6UIDvGWTYZvu4BLf2GpO      MOVE_DOWN  62294.0  \n",
       "178720  7GX5flRQZVHRAGd6B4TmDO      MOVE_DOWN  61681.0  \n",
       "178721  75ZvA4QfFiZvzhj2xkaWAh        MOVE_UP  60691.0  \n",
       "178722  43ZyHQITOjhciSUUNPVRHc  SAME_POSITION  59454.0  \n",
       "178723  4dL59MdDzLINk1Sq04y9Cf        MOVE_UP  58932.0  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d_t100_de_x.reset_index(drop=True, inplace=True)\n",
    "d_t100_de_x.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Work with 5 dataframes in code cells above, below only exporting to .csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# exporting to csv >>> d_v50_final, d_t200_final, d_t100, d_t100_de, d_t100_de_x\n",
    "from os.path import expanduser\n",
    "from pathlib import Path\n",
    "\n",
    "def get_name(variable):\n",
    "    for name in globals():\n",
    "        if id(globals()[name]) == id(variable):\n",
    "            return name\n",
    "    for name in locals():\n",
    "        if id(locals()[name]) == id(variable):\n",
    "            return name\n",
    "    return None\n",
    "\n",
    "def save(df):\n",
    "    \"\"\"Saves a dataframe to the Downloads folder\"\"\"\n",
    "    home = Path(expanduser(\"~\"))\n",
    "    path = home / \"Downloads\"\n",
    "    df_name = get_name(df) + \".csv\"\n",
    "    file_name = path / df_name\n",
    "    df.to_csv(file_name, index=False)\n",
    "    print(file_name) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/inthesea/Downloads/d_t100_de.csv\n",
      "/Users/inthesea/Downloads/d_t100.csv\n",
      "/Users/inthesea/Downloads/d_t200_final.csv\n",
      "/Users/inthesea/Downloads/d_v50_final.csv\n",
      "/Users/inthesea/Downloads/d_t100_de_x.csv\n"
     ]
    }
   ],
   "source": [
    "save(d_t100_de)\n",
    "save(d_t100)\n",
    "save(d_t200_final)\n",
    "save(d_v50_final)\n",
    "save(d_t100_de_x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/Users/inthesea/Downloads/d_t100_5c.csv\n"
     ]
    }
   ],
   "source": [
    "save(d_t100_5c)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "nf_sql",
   "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.17"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}