coding-challenge.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CODING CHALLENGE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data:\n",
    "Music reviews of independent music collected from 1995 to 1999.\n",
    "\n",
    "\n",
    "Questions:\n",
    "    1. Count the number of reviews by genre\n",
    "    2. Count the number of reviews per genre per year\n",
    "    3. Quartile of the distribution of the number of genres per review\n",
    "    4. Evaluate the mean and standard deviation of each genre's score\n",
    "    5. Find the review id with the highest number of words in the content for the label 'drag city\n",
    "    6. Find the most used word in the content for the label 'drag city\n",
    "    7. How many content of the artist \"neil young\" contain the word \"who\"\n",
    "    8. Find the artist with the most reviews each year\n",
    "    9. Modify the artist column of the artists table by adding a capital letter to the first letter. \n",
    "    10. Build a function that returns the Top 10 artists for one or more genres  \n",
    "        - def get_top_10(genres_list)\n",
    "        - return Top 10\n",
    "    11. Build a function that returns statistics for an artist\n",
    "        - def get_artist_info(name)\n",
    "        - return a DataFrame with the following information\n",
    "            - Number of reviews\n",
    "            - The genre with the most reviews\n",
    "            - The label with the most reviews\n",
    "            - The average score\n",
    "            - The number of 'best_new_music' reviews\n",
    "            - The most recent reviewid\n",
    "            \n",
    "\n",
    "You can use the method (SQL/Pandas) of your choice to answer the questions.  \n",
    "The questions can be answered independently. \n",
    "\n",
    "Translated with www.DeepL.com/Translator (free version)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3 as sql\n",
    "import pandas as pd\n",
    "import statistics\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "db = sql.connect('database.sqlite')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('artists',), ('content',), ('genres',), ('labels',), ('reviews',), ('years',)]\n"
     ]
    }
   ],
   "source": [
    "tables = db.cursor() \n",
    "tables.execute(\"SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;\")\n",
    "print(tables.fetchall())"
   ]
  },
  {
   "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>reviewid</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22703</td>\n",
       "      <td>1998.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22721</td>\n",
       "      <td>2016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22659</td>\n",
       "      <td>2016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22661</td>\n",
       "      <td>2016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22725</td>\n",
       "      <td>2016.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19103</th>\n",
       "      <td>1535</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19104</th>\n",
       "      <td>1341</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19105</th>\n",
       "      <td>5376</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19106</th>\n",
       "      <td>2413</td>\n",
       "      <td>1999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19107</th>\n",
       "      <td>3723</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>19108 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       reviewid    year\n",
       "0         22703  1998.0\n",
       "1         22721  2016.0\n",
       "2         22659  2016.0\n",
       "3         22661  2016.0\n",
       "4         22725  2016.0\n",
       "...         ...     ...\n",
       "19103      1535     NaN\n",
       "19104      1341     NaN\n",
       "19105      5376     NaN\n",
       "19106      2413  1999.0\n",
       "19107      3723     NaN\n",
       "\n",
       "[19108 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "query = db.execute(\"SELECT * From artists\")\n",
    "cols = [column[0] for column in query.description]\n",
    "artists= pd.DataFrame.from_records(data = query.fetchall(), columns = cols)\n",
    "\n",
    "query = db.execute(\"SELECT * From content\")\n",
    "cols = [column[0] for column in query.description]\n",
    "content= pd.DataFrame.from_records(data = query.fetchall(), columns = cols)\n",
    "\n",
    "query = db.execute(\"SELECT * From genres\")\n",
    "cols = [column[0] for column in query.description]\n",
    "genres= pd.DataFrame.from_records(data = query.fetchall(), columns = cols)\n",
    "\n",
    "query = db.execute(\"SELECT * From labels\")\n",
    "cols = [column[0] for column in query.description]\n",
    "labels= pd.DataFrame.from_records(data = query.fetchall(), columns = cols)\n",
    "\n",
    "query = db.execute(\"SELECT * From reviews\")\n",
    "cols = [column[0] for column in query.description]\n",
    "reviews= pd.DataFrame.from_records(data = query.fetchall(), columns = cols)\n",
    "\n",
    "query = db.execute(\"SELECT * From years\")\n",
    "cols = [column[0] for column in query.description]\n",
    "years= pd.DataFrame.from_records(data = query.fetchall(), columns = cols)\n",
    "\n",
    "years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#task1\n",
    "#genres.groupby(\"genre\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "genre\n",
       "electronic      3874\n",
       "experimental    1815\n",
       "folk/country     685\n",
       "global           219\n",
       "jazz             435\n",
       "metal            862\n",
       "pop/r&b         1432\n",
       "rap             1559\n",
       "rock            9438\n",
       "Name: reviewid, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Task 1\n",
    "task1 = pd.merge(genres,reviews, on='reviewid',how='inner').groupby(['genre'])['reviewid'].count()\n",
    "task1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "year    genre       \n",
       "1959.0  global            1\n",
       "1960.0  jazz              2\n",
       "1962.0  folk/country      1\n",
       "        jazz              1\n",
       "        pop/r&b           1\n",
       "                       ... \n",
       "2016.0  metal            56\n",
       "        pop/r&b         121\n",
       "        rap             179\n",
       "        rock            399\n",
       "2017.0  experimental      1\n",
       "Length: 346, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#task2\n",
    "task2 = pd.merge(years,pd.merge(genres,reviews, on='reviewid',how='inner'), on='reviewid',how='inner').groupby(['year','genre']).size()\n",
    "\n",
    "task2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7491111.111111111\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "2736.989424734979"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#taks4\n",
    "#Evaluate the mean \n",
    "\n",
    "\n",
    "df  =genres.groupby(\"genre\")[\"reviewid\"].count().reset_index(name=\"count\")\n",
    "df\n",
    "Evaluate = statistics.pvariance(df[\"count\"])\n",
    "print (Evaluate)\n",
    "\n",
    "b=np.std(df[\"count\"])  \n",
    "b\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5701    17041\n",
       "Name: reviewid, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#taks 5\n",
    "#he = labels[labels[\"label\"].str.contains(\"drag city\")]\n",
    "he =labels.loc[labels['label'].str.contains(\"drag city\", case=False)== True]\n",
    "\n",
    "he[\"label\"].str.len().max()\n",
    "df_col_len = int(he[\"label\"].astype(bytes).str.len().max())\n",
    "hml = he[he[\"label\"].astype(bytes).str.len()==df_col_len]\n",
    "hml[\"reviewid\"]\n",
    "#he.str.contains('drag city',case=True, regex=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5701    drag city / sun ark\n",
       "Name: label, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#task6\n",
    "he =labels.loc[labels['label'].str.contains(\"drag city\", case=False)== True]\n",
    "he[\"label\"].str.len().max()\n",
    "df_col_len = int(he[\"label\"].astype(bytes).str.len().max())\n",
    "hml = he[he[\"label\"].astype(bytes).str.len()==df_col_len]\n",
    "hml[\"label\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(17041, 'drag city / sun ark')]\n"
     ]
    }
   ],
   "source": [
    "tables5 = db.cursor() \n",
    "tables5.execute(\"select * from labels where length(label) =(select max (length(label))rrr from labels where label like '%drag city%') and label like '%drag city%'\")\n",
    "print(tables5.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(15,)]\n"
     ]
    }
   ],
   "source": [
    "tables7 = db.cursor() \n",
    "tables7.execute(\"select count(1) content from artists a inner join  content b on a.reviewid = b.reviewid where content like '%who%' and a.artist = 'neil young'\")\n",
    "print(tables7.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "reviewid    15\n",
       "artist      15\n",
       "content     15\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#task7\n",
    "artistsNeil = artists.loc[artists['artist']==\"neil young\"]\n",
    "contentWho = content.loc[content['content'].str.contains(\"who\", case=False)== True]\n",
    "ts7 = pd.merge(artistsNeil, contentWho, on=\"reviewid\")\n",
    "ts7.count()"
   ]
  },
  {
   "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>pub_year</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1999</td>\n",
       "      <td>223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000</td>\n",
       "      <td>348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2001</td>\n",
       "      <td>456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2002</td>\n",
       "      <td>923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2003</td>\n",
       "      <td>1060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2004</td>\n",
       "      <td>1121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2005</td>\n",
       "      <td>1186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2006</td>\n",
       "      <td>1189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2007</td>\n",
       "      <td>1216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2008</td>\n",
       "      <td>1201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2009</td>\n",
       "      <td>1171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2010</td>\n",
       "      <td>1170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2011</td>\n",
       "      <td>1165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2012</td>\n",
       "      <td>1185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2013</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2014</td>\n",
       "      <td>1162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2015</td>\n",
       "      <td>1135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2016</td>\n",
       "      <td>1265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2017</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    pub_year  counts\n",
       "0       1999     223\n",
       "1       2000     348\n",
       "2       2001     456\n",
       "3       2002     923\n",
       "4       2003    1060\n",
       "5       2004    1121\n",
       "6       2005    1186\n",
       "7       2006    1189\n",
       "8       2007    1216\n",
       "9       2008    1201\n",
       "10      2009    1171\n",
       "11      2010    1170\n",
       "12      2011    1165\n",
       "13      2012    1185\n",
       "14      2013    1200\n",
       "15      2014    1162\n",
       "16      2015    1135\n",
       "17      2016    1265\n",
       "18      2017      17"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#taks 8\n",
    "reviews.groupby(\"pub_year\").size().reset_index(name='counts')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>reviewid</th>\n",
       "      <th>artist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22703</td>\n",
       "      <td>Massive Attack</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22721</td>\n",
       "      <td>Krallice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22659</td>\n",
       "      <td>Uranium Club</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22661</td>\n",
       "      <td>Kleenex</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>22661</td>\n",
       "      <td>Liliput</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18826</th>\n",
       "      <td>1535</td>\n",
       "      <td>Coldcut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18827</th>\n",
       "      <td>1341</td>\n",
       "      <td>Cassius</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18828</th>\n",
       "      <td>5376</td>\n",
       "      <td>Mojave 3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18829</th>\n",
       "      <td>2413</td>\n",
       "      <td>Don Caballero</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18830</th>\n",
       "      <td>3723</td>\n",
       "      <td>Neil Hamburger</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>18831 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       reviewid          artist\n",
       "0         22703  Massive Attack\n",
       "1         22721        Krallice\n",
       "2         22659    Uranium Club\n",
       "3         22661         Kleenex\n",
       "4         22661         Liliput\n",
       "...         ...             ...\n",
       "18826      1535         Coldcut\n",
       "18827      1341         Cassius\n",
       "18828      5376        Mojave 3\n",
       "18829      2413   Don Caballero\n",
       "18830      3723  Neil Hamburger\n",
       "\n",
       "[18831 rows x 2 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#task9\n",
    "#artists['artist'].title()\n",
    "artists['artist'] = artists['artist'].str.title()\n",
    "artists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#task 10\n",
    "def get_top_10(genres_list):\n",
    "        ts10 = pd.merge(artists, genres, on=\"reviewid\")\n",
    "        ts10l= ts10[ts10['genre'].isin(genres_list)]\n",
    "\n",
    "        ts10g =ts10l.groupby([\"artist\", \"genre\"]).size().reset_index(name='counts')\n",
    "        ts10s = ts10g.sort_values('counts',ascending = False).head(10)\n",
    "        return ts10s\n"
   ]
  },
  {
   "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>artist</th>\n",
       "      <th>genre</th>\n",
       "      <th>counts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2213</th>\n",
       "      <td>Neil Young</td>\n",
       "      <td>rock</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1302</th>\n",
       "      <td>Guided By Voices</td>\n",
       "      <td>rock</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3099</th>\n",
       "      <td>The Beatles</td>\n",
       "      <td>rock</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>760</th>\n",
       "      <td>David Bowie</td>\n",
       "      <td>rock</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2297</th>\n",
       "      <td>Of Montreal</td>\n",
       "      <td>rock</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2122</th>\n",
       "      <td>Mogwai</td>\n",
       "      <td>rock</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2628</th>\n",
       "      <td>Robert Pollard</td>\n",
       "      <td>rock</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3218</th>\n",
       "      <td>The Fall</td>\n",
       "      <td>rock</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>Animal Collective</td>\n",
       "      <td>rock</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3901</th>\n",
       "      <td>Xiu Xiu</td>\n",
       "      <td>rock</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 artist genre  counts\n",
       "2213         Neil Young  rock      23\n",
       "1302   Guided By Voices  rock      23\n",
       "3099        The Beatles  rock      21\n",
       "760         David Bowie  rock      21\n",
       "2297        Of Montreal  rock      20\n",
       "2122             Mogwai  rock      20\n",
       "2628     Robert Pollard  rock      19\n",
       "3218           The Fall  rock      18\n",
       "166   Animal Collective  rock      18\n",
       "3901            Xiu Xiu  rock      18"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "listgen = ['rock']\n",
    "get_top_10(listgen)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 11\n",
    "11. Build a function that returns statistics for an artist - def get_artist_info(name) - return a DataFrame with the following information - Number of reviews - The genre with the most reviews - The label with the most reviews - The average score - The number of 'best_new_music' reviews - The most recent reviewid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_artist_info(name):\n",
    "    demo_df = pd.DataFrame(columns=['name','Num_of_reviews','genre_most_reviews','label_most_reviews','average_score','num_best_reviews','most_recent_reviewid'])\n",
    "    try:\n",
    "        demo_df['name'] = [name]\n",
    "        demo_df['Num_of_reviews'] = reviews[reviews['artist'] == name.lower()].value_counts().count()\n",
    "        demo_df['genre_most_reviews'] =  pd.merge(genres,reviews[reviews['artist'] == name.lower()]['reviewid'], on='reviewid',how='inner')['genre'].mode().to_list()[0]\n",
    "        demo_df['label_most_reviews'] = pd.merge(labels,reviews[reviews['artist'] == name.lower()]['reviewid'], on='reviewid',how='inner')['label'].mode().to_list()[0]\n",
    "        demo_df['average_score'] = reviews[reviews['artist'] == name.lower()]['score'].mean()\n",
    "        demo_df['num_best_reviews'] = reviews[(reviews['artist'] == name.lower()) & (reviews['best_new_music'] == 1 )]['best_new_music'].count()\n",
    "        demo_df['most_recent_reviewid'] = reviews[(reviews['artist'] == name.lower())]['reviewid'].max()\n",
    "    except:\n",
    "        return 'artist not found'\n",
    "    return demo_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Num_of_reviews</th>\n",
       "      <th>genre_most_reviews</th>\n",
       "      <th>label_most_reviews</th>\n",
       "      <th>average_score</th>\n",
       "      <th>num_best_reviews</th>\n",
       "      <th>most_recent_reviewid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Neil Young</td>\n",
       "      <td>17</td>\n",
       "      <td>rock</td>\n",
       "      <td>reprise</td>\n",
       "      <td>7.168421</td>\n",
       "      <td>2</td>\n",
       "      <td>22654</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name  Num_of_reviews genre_most_reviews label_most_reviews  \\\n",
       "0  Neil Young              17               rock            reprise   \n",
       "\n",
       "   average_score  num_best_reviews  most_recent_reviewid  \n",
       "0       7.168421                 2                 22654  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_artist_info('Neil Young')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}