Martina.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "matched = pd.read_csv('/Users/verna/OneDrive/Desktop/words_songs_matched.csv')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# how many rows, how many columns\n",
    "matched.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['word', 'count', 'track_id', 'song_id', 'artist_id', 'title',\n",
       "       'artist_name', 'duration', 'year', 'artist_hotttnesss'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# name of columns\n",
    "matched.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>word</th>\n",
       "      <th>count</th>\n",
       "      <th>track_id</th>\n",
       "      <th>song_id</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>title</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>duration</th>\n",
       "      <th>year</th>\n",
       "      <th>artist_hotttnesss</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>i</td>\n",
       "      <td>6</td>\n",
       "      <td>TRAAAAV128F421A322</td>\n",
       "      <td>SOQPWCR12A6D4FB2A3</td>\n",
       "      <td>AR73AIO1187B9AD57B</td>\n",
       "      <td>A Poor Recipe For Civic Cohesion</td>\n",
       "      <td>Western Addiction</td>\n",
       "      <td>118.07302</td>\n",
       "      <td>2005</td>\n",
       "      <td>0.386606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>the</td>\n",
       "      <td>4</td>\n",
       "      <td>TRAAAAV128F421A322</td>\n",
       "      <td>SOQPWCR12A6D4FB2A3</td>\n",
       "      <td>AR73AIO1187B9AD57B</td>\n",
       "      <td>A Poor Recipe For Civic Cohesion</td>\n",
       "      <td>Western Addiction</td>\n",
       "      <td>118.07302</td>\n",
       "      <td>2005</td>\n",
       "      <td>0.386606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>you</td>\n",
       "      <td>2</td>\n",
       "      <td>TRAAAAV128F421A322</td>\n",
       "      <td>SOQPWCR12A6D4FB2A3</td>\n",
       "      <td>AR73AIO1187B9AD57B</td>\n",
       "      <td>A Poor Recipe For Civic Cohesion</td>\n",
       "      <td>Western Addiction</td>\n",
       "      <td>118.07302</td>\n",
       "      <td>2005</td>\n",
       "      <td>0.386606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>to</td>\n",
       "      <td>2</td>\n",
       "      <td>TRAAAAV128F421A322</td>\n",
       "      <td>SOQPWCR12A6D4FB2A3</td>\n",
       "      <td>AR73AIO1187B9AD57B</td>\n",
       "      <td>A Poor Recipe For Civic Cohesion</td>\n",
       "      <td>Western Addiction</td>\n",
       "      <td>118.07302</td>\n",
       "      <td>2005</td>\n",
       "      <td>0.386606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>and</td>\n",
       "      <td>5</td>\n",
       "      <td>TRAAAAV128F421A322</td>\n",
       "      <td>SOQPWCR12A6D4FB2A3</td>\n",
       "      <td>AR73AIO1187B9AD57B</td>\n",
       "      <td>A Poor Recipe For Civic Cohesion</td>\n",
       "      <td>Western Addiction</td>\n",
       "      <td>118.07302</td>\n",
       "      <td>2005</td>\n",
       "      <td>0.386606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19045327</td>\n",
       "      <td>easili</td>\n",
       "      <td>1</td>\n",
       "      <td>TRZZZZD128F4236844</td>\n",
       "      <td>SOANRDO12A81C21E36</td>\n",
       "      <td>ART48CP1187B9AE314</td>\n",
       "      <td>Mr. Soul (Album Version)</td>\n",
       "      <td>Rush</td>\n",
       "      <td>229.27628</td>\n",
       "      <td>0</td>\n",
       "      <td>0.546253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19045328</td>\n",
       "      <td>disast</td>\n",
       "      <td>1</td>\n",
       "      <td>TRZZZZD128F4236844</td>\n",
       "      <td>SOANRDO12A81C21E36</td>\n",
       "      <td>ART48CP1187B9AE314</td>\n",
       "      <td>Mr. Soul (Album Version)</td>\n",
       "      <td>Rush</td>\n",
       "      <td>229.27628</td>\n",
       "      <td>0</td>\n",
       "      <td>0.546253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19045329</td>\n",
       "      <td>frown</td>\n",
       "      <td>1</td>\n",
       "      <td>TRZZZZD128F4236844</td>\n",
       "      <td>SOANRDO12A81C21E36</td>\n",
       "      <td>ART48CP1187B9AE314</td>\n",
       "      <td>Mr. Soul (Album Version)</td>\n",
       "      <td>Rush</td>\n",
       "      <td>229.27628</td>\n",
       "      <td>0</td>\n",
       "      <td>0.546253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19045330</td>\n",
       "      <td>teas</td>\n",
       "      <td>1</td>\n",
       "      <td>TRZZZZD128F4236844</td>\n",
       "      <td>SOANRDO12A81C21E36</td>\n",
       "      <td>ART48CP1187B9AE314</td>\n",
       "      <td>Mr. Soul (Album Version)</td>\n",
       "      <td>Rush</td>\n",
       "      <td>229.27628</td>\n",
       "      <td>0</td>\n",
       "      <td>0.546253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19045331</td>\n",
       "      <td>upset</td>\n",
       "      <td>1</td>\n",
       "      <td>TRZZZZD128F4236844</td>\n",
       "      <td>SOANRDO12A81C21E36</td>\n",
       "      <td>ART48CP1187B9AE314</td>\n",
       "      <td>Mr. Soul (Album Version)</td>\n",
       "      <td>Rush</td>\n",
       "      <td>229.27628</td>\n",
       "      <td>0</td>\n",
       "      <td>0.546253</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>19045332 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            word  count            track_id             song_id  \\\n",
       "0              i      6  TRAAAAV128F421A322  SOQPWCR12A6D4FB2A3   \n",
       "1            the      4  TRAAAAV128F421A322  SOQPWCR12A6D4FB2A3   \n",
       "2            you      2  TRAAAAV128F421A322  SOQPWCR12A6D4FB2A3   \n",
       "3             to      2  TRAAAAV128F421A322  SOQPWCR12A6D4FB2A3   \n",
       "4            and      5  TRAAAAV128F421A322  SOQPWCR12A6D4FB2A3   \n",
       "...          ...    ...                 ...                 ...   \n",
       "19045327  easili      1  TRZZZZD128F4236844  SOANRDO12A81C21E36   \n",
       "19045328  disast      1  TRZZZZD128F4236844  SOANRDO12A81C21E36   \n",
       "19045329   frown      1  TRZZZZD128F4236844  SOANRDO12A81C21E36   \n",
       "19045330    teas      1  TRZZZZD128F4236844  SOANRDO12A81C21E36   \n",
       "19045331   upset      1  TRZZZZD128F4236844  SOANRDO12A81C21E36   \n",
       "\n",
       "                   artist_id                             title  \\\n",
       "0         AR73AIO1187B9AD57B  A Poor Recipe For Civic Cohesion   \n",
       "1         AR73AIO1187B9AD57B  A Poor Recipe For Civic Cohesion   \n",
       "2         AR73AIO1187B9AD57B  A Poor Recipe For Civic Cohesion   \n",
       "3         AR73AIO1187B9AD57B  A Poor Recipe For Civic Cohesion   \n",
       "4         AR73AIO1187B9AD57B  A Poor Recipe For Civic Cohesion   \n",
       "...                      ...                               ...   \n",
       "19045327  ART48CP1187B9AE314          Mr. Soul (Album Version)   \n",
       "19045328  ART48CP1187B9AE314          Mr. Soul (Album Version)   \n",
       "19045329  ART48CP1187B9AE314          Mr. Soul (Album Version)   \n",
       "19045330  ART48CP1187B9AE314          Mr. Soul (Album Version)   \n",
       "19045331  ART48CP1187B9AE314          Mr. Soul (Album Version)   \n",
       "\n",
       "                artist_name   duration  year  artist_hotttnesss  \n",
       "0         Western Addiction  118.07302  2005           0.386606  \n",
       "1         Western Addiction  118.07302  2005           0.386606  \n",
       "2         Western Addiction  118.07302  2005           0.386606  \n",
       "3         Western Addiction  118.07302  2005           0.386606  \n",
       "4         Western Addiction  118.07302  2005           0.386606  \n",
       "...                     ...        ...   ...                ...  \n",
       "19045327               Rush  229.27628     0           0.546253  \n",
       "19045328               Rush  229.27628     0           0.546253  \n",
       "19045329               Rush  229.27628     0           0.546253  \n",
       "19045330               Rush  229.27628     0           0.546253  \n",
       "19045331               Rush  229.27628     0           0.546253  \n",
       "\n",
       "[19045332 rows x 10 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matched"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['A Poor Recipe For Civic Cohesion', 'Soul Deep', \"It's About Time\",\n",
       "       ..., \"Operator's Manual\", 'Toca Me', 'Non Cambiare Tu'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matched.title.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     19045259\n",
       "unique      190524\n",
       "top          Intro\n",
       "freq         15650\n",
       "Name: title, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matched['title'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count               19045332\n",
       "unique                237662\n",
       "top       SOTWXIM12A6D4FA4B2\n",
       "freq                     728\n",
       "Name: song_id, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matched['song_id'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "year\n",
       "0       5035452\n",
       "1924         82\n",
       "1925         84\n",
       "1926        118\n",
       "1927        779\n",
       "         ...   \n",
       "2006     966695\n",
       "2007     997716\n",
       "2008     832067\n",
       "2009     645166\n",
       "2010     128363\n",
       "Name: song_id, Length: 88, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count how many songs for each year grouped by years\n",
    "matched.groupby('year')['song_id'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# how many words in each song?\n",
    "matched.groupby('song_id')['word'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Artists and value count\n",
    "artist_freq = matched['artist_name'].apply(lambda x: pd.Series(x).value_counts()).sum().astype('int').sort_values(ascending=False).to_frame().reset_index(level=0)\n",
    "\n",
    "# Add column names\n",
    "artist_freq.columns = ['subject', 'count']\n",
    "# Display with horizontal bars\n",
    "display(artist_freq.style.bar(subset=['count'], color='#d65f5f').set_properties(subset=['count'], **{'width': '300px'}))"
   ]
  }
 ],
 "metadata": {
  "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}