parse_scores_json_to_tables.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "48b53de1-af62-4338-85ee-08579854f1df",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "import numpy as np\n",
    "import os\n",
    "from pluralizer import Pluralizer\n",
    "import csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8f22cdf5-7e68-4479-9ef1-902abde23bf4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = \\\n",
    "( pd.read_json('dwts_scrapy/scores.json')\n",
    "  # scores comes in as one row per performance\n",
    "  .assign(performance_id = lambda df_: np.arange(df_.shape[0]))\n",
    "  .replace(r'^\\s*$', np.nan, regex=True)\n",
    "  # .filter(index = lambda df_: df_.query(\"couple == ['Dance-off','Dance Duel']\").index)\n",
    "  .rename(columns = {'judge': 'judge_excluded', \"dance\": \"dance_style\", \"dance_&_theme\": \"dance_and_theme\"})\n",
    "  .dropna(subset=['couple'])\n",
    "  .assign(judge = lambda df_: df_.judge_phrase.str.strip(to_strip=\" .\\n\").str.split(\", \", expand = False))\n",
    "  .assign(week = lambda df_: df_.week_title.str.extract(r'Week (\\d+)'), #NaN if no Week Theme.\n",
    "          week_theme = lambda df_: df_.week_title.str.extract(r': (.*?)$').fillna('No Theme')\n",
    "         )\n",
    "   # \"Caveman\" Hustle # Argentine tango\n",
    "  .assign(michael_jackson_theme = lambda df_: df_.dance_and_theme.str.extract(r'\"(.*?)\"'),\n",
    "          michael_jackson_dance = lambda df_: df_.dance_and_theme.str.extract(r'(?:\".*?\")?(.*?)$'),\n",
    "          dance_style = lambda df_: np.where(df_.dance_and_theme.notna(), df_.michael_jackson_dance, df_.dance_style)\n",
    "         )\n",
    "  .drop(columns = ['michael_jackson_dance'])\n",
    "  .assign(dance_styles = lambda df_: df_.dance_style.str.split(\"---\"),\n",
    "         primary_dance_style = lambda df_: df_.dance_styles.apply(lambda x: x[0]))\n",
    "  .assign(primary_dance_style = lambda df_: df_.primary_dance_style.str.replace(\"&\",'').str.strip().str.title())\n",
    " \n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b0767569-6435-416c-a523-b757e9c1f5eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# coalesce the music columns\n",
    "\n",
    "\n",
    "music_cols = ['elvis_music', 'classical_music', 'motown_music', 'stevie_wonder_music', \n",
    "              'music_by_britney_spears', 'music_from_grease', 'cher_music',\n",
    "              'music_by_queen', 'music_by_janet_jackson', 'michael_buble_music']\n",
    "\n",
    "# I would like to reimplement these in a chained fashion. I think it's Series.where but I'm not sure.\n",
    "\n",
    "df['music'] = np.where(df['elvis_music'].notna(), df['elvis_music'] + \"—Elvis Presley\", df['music'])\n",
    "df['music'] = np.where(df['classical_music'].notna(), df['classical_music'], df['music'])\n",
    "df['music'] = np.where(df['motown_music'].notna(), df['motown_music'], df['music'])\n",
    "df['music'] = np.where(df['stevie_wonder_music'].notna(), df['stevie_wonder_music'] + \"—Stevie Wonder\", df['music'])\n",
    "df['music'] = np.where(df['music_by_britney_spears'].notna(), df['music_by_britney_spears'] + \"—Brittany Spears\", df['music'])\n",
    "df['music'] = np.where(df['music_from_grease'].notna(), df['music_from_grease'], df['music'])\n",
    "df['music'] = np.where(df['cher_music'].notna(), df['cher_music'] + \"—Cher\", df['music'])\n",
    "df['music'] = np.where(df['music_by_queen'].notna(), df['music_by_queen'] + \"—Queen\", df['music'])\n",
    "df['music'] = np.where(df['music_by_janet_jackson'].notna(), df['music_by_janet_jackson'] + \"—Janet Jackson\", df['music'])\n",
    "df['music'] = np.where(df['michael_buble_music'].notna(), df['michael_buble_music'] + \"—Michael Buble\", df['music'])\n",
    "\n",
    "df = \\\n",
    "(\n",
    "    df\n",
    "    .drop(columns = music_cols)\n",
    "    .assign(songs = lambda df_: df_.music.str.split(\"---\"),\n",
    "            primary_song_phrase = lambda df_: df_.songs.apply(lambda x: x[0]))\n",
    ")\n",
    "\n",
    "# I don't know how to do this in a chained fashion.  Probably a custom function and a pipe?\n",
    "df[['primary_song','primary_song_artist']] = df.primary_song_phrase.str.extract(r'(.*)[—―一]+(.*?)$')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b81827ec-f2e4-487f-a861-e54089d9614a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Some weeks have a technical score/performance score. but no scores column.\n",
    "\n",
    "# https://en.wikipedia.org/wiki/Dancing_with_the_Stars_(American_season_11)#Weekly_scores Week 4\n",
    "# https://en.wikipedia.org/wiki/Dancing_with_the_Stars_(American_season_10)#Weekly_scores Week 4\n",
    "\n",
    "# double_score = df[df['technical_score'].notna()]\n",
    "\n",
    "# double_score[double_score.columns[~double_score.isnull().any()]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "56225ede-e30f-4bfb-8ee4-611fbd1b554f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# season 22, week 8 has a very difficult judges score arrangement\n",
    "# https://en.wikipedia.org/wiki/Dancing_with_the_Stars_(American_season_22)#Week_8:_Judges'_Team-up_Challenge\n",
    "\n",
    "#  total (9, X, 9, 9) where the final score is \"america's score\" and the X is the score that would have been the score from the judge\n",
    "# in df['judge'] column.  gah.  but those are multi couple dances.  phew."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8ed499af-6ac3-4938-b7e2-255d68f663dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# editing on the pages made this moot.\n",
    "# Some couple columns have additional info which is in parens in the column. This creates an NA couple column.\n",
    "\n",
    "# pattern = r'(?P<couple_alone>.*?)\\((?P<additional>.*?)\\)$'\n",
    "\n",
    "# # e.g., https://en.wikipedia.org/wiki/Dancing_with_the_Stars_(American_season_27) Week 4.  Couple (Trio Dance Partner)\n",
    "# df[['couple_alone','trio_partner']] = df['couple_trio_dance_partner_'].str.extract(pattern)\n",
    "# df['couple'] = df['couple'].fillna(df['couple_alone'])\n",
    "# df.drop(columns='couple_alone', inplace=True)\n",
    "\n",
    "# # https://en.wikipedia.org/wiki/Dancing_with_the_Stars_(American_season_16) Week 5 (Professionals).\n",
    "# df[['couple_alone','additional_dancers']] = df['couple_professionals_'].str.extract(pattern)\n",
    "# df['couple'] = df['couple'].fillna(df['couple_alone'])\n",
    "# df.drop(columns='couple_alone', inplace=True)\n",
    "\n",
    "# # couple_team_up_judge_ from https://en.wikipedia.org/wiki/Dancing_with_the_Stars_(American_season_22)#Week_8:_Judges'_Team-up_Challenge\n",
    "# # Some of These are multi-couple dances, with a judge listed i the couple column, and then an X for the score that the judge that coached the team would have given.\n",
    "# # but others are regular dances (just a different column name)\n",
    "# df['couple'] = df['couple'].fillna(df['couple_team_up_judge_'])\n",
    "\n",
    "# # couple_judge_  from https://en.wikipedia.org/wiki/Dancing_with_the_Stars_(American_season_11)#Week_10:_Finals is much simpler (just a judge chosen dance).\n",
    "# df[['couple_alone','judge_choosing_dance']] = df['couple_judge_'].str.extract(pattern)\n",
    "# df['couple'] = df['couple'].fillna(df['couple_alone'])\n",
    "# df.drop(columns='couple_alone', inplace=True)\n",
    "\n",
    "# # One remains from the couple_judge_ due to a substitution which then didn't have a judge (so the regex above doesn't catch it)\n",
    "# # 2341 where the couple_judge_ is Nastia & Sasha[a]\n",
    "# df['couple'] = df['couple'].fillna(df['couple_judge_'])\n",
    "# # df.iloc[2341]\n",
    "\n",
    "# df.drop(columns=['couple_trio_dance_partner_', 'couple_professionals_', 'couple_judge_'], inplace=True)\n",
    "\n",
    "# df[df['couple'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "71af2ceb-44c2-4d00-b588-290686a6cfc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Some have technical_score and performance_score.\n",
    "# add a score_type column.\n",
    "# split these out, going to be two rows, each with scores column and a score_type column.\n",
    "\n",
    "double_scores = ( df[df['technical_scores'].notna()]\n",
    "                  .drop(columns=\"scores\")\n",
    "                  .dropna(axis = 1, how = \"all\") )\n",
    "\n",
    "val_cols = ['technical_scores','performance_scores']\n",
    "id_cols = [ele for ele in double_scores if ele not in val_cols]\n",
    "# now get two rows per\n",
    "double_scores = double_scores.melt(id_vars = id_cols, value_vars = val_cols, value_name = \"scores\", var_name = \"score_type\")\n",
    "\n",
    "single_scores = df.drop(df.index[df['technical_scores'].notna()])\n",
    "# make others be score_type\n",
    "single_scores['score_type'] = \"single_score\"\n",
    "single_scores.drop(columns=['technical_scores','performance_scores'], inplace=True)\n",
    "\n",
    "df_score_type = pd.concat([double_scores, single_scores])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "effc8368-49af-4f3f-b24b-df2a2b51c337",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_score_type.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "40fdb70d-bbe1-486c-b33e-465b877a108d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_score_type[['total_score','indiv_scores']] = df_score_type['scores'].str.extract(r'(?P<total_score>\\d+)\\s\\((?P<indiv_scores>.*)\\)')\n",
    "\n",
    "# new_cols['judge_score_list'] = new_cols['indiv_scores'].str.split(\",\")\n",
    "\n",
    "\n",
    "# df_new = pd.concat([df, new_cols], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "729f0cb7-0647-4760-8e01-b8c635727b6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# song_cols = df['music'].str.extract(r'\"(?P<song_name>.*)\"—(?P<song_artist>.*)$')\n",
    "# song_cols\n",
    "# df[\"music\"].str.split(\" / \") Some music columns have multiples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97417bf0-cf82-4f36-b40e-6e5a79547687",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3f5f6e1e-bc9f-4573-b311-9660ffeda218",
   "metadata": {},
   "outputs": [],
   "source": [
    "# most are multi couple dances, with ranking scores.\n",
    "\n",
    "df_score_type[\"couple_list\"] = df_score_type[\"couple\"].str.split(\"---\")\n",
    "df_score_type[\"couple_count\"] = df_score_type[\"couple_list\"].apply(len)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f04efea1-fdf5-4334-aa26-573b2e42169e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now eliminate multi-couple dances and dances with NA total scores (mostly dance offs and others). \n",
    "#  Might still be one or two issues with total scores but not individual scores.\n",
    "\n",
    "df_single_couple = df_score_type.loc[(df_score_type['couple_count'] == 1) & df_score_type['total_score'].notna() ].copy()\n",
    "\n",
    "# df_single_couple.head()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "c795053a-9259-4994-a5f5-371efecfa5db",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_single_couple[df_single_couple['indiv_scores'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a65dcf34-3506-4e82-a763-dff2c3e3f1f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_single_couple.head()\n",
    "\n",
    "df_single_couple['judge_score_list'] = df_single_couple['indiv_scores'].str.split(\", \")\n",
    "\n",
    "# df_single_couple['judge_score_list']\n",
    "df_single_couple['judge_score_count'] = df_single_couple['judge_score_list'].apply(len)\n",
    "\n",
    "df_single_couple['judge_count'] = df_single_couple['judge'].apply(len)\n",
    "\n",
    "\n",
    "# a few have a total score, but not an individual score.  e.g., s9w10."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1f4b04a2-c78e-48c8-9064-8367e6e52a0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#pd.set_option('max_colwidth', None) \n",
    "#df_single_couple[df_single_couple['season'] == 31][['week','judge_phrase']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "79251523-2af5-40f7-882f-051d07769184",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_single_couple.loc[df_single_couple['judge_score_count'] != df_single_couple['judge_count']]\n",
    "\n",
    "# Trouble in \n",
    "# https://en.wikipedia.org/wiki/Dancing_with_the_Stars_(American_season_23)#Week_5:_Most_Memorable_Year_Night\n",
    "# https://en.wikipedia.org/wiki/Dancing_with_the_Stars_(American_season_31)#Week_6:_Michael_Bubl%C3%A9_Night"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "37683ff5-da9c-4a5f-b288-c94271d5b894",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = ( df_single_couple\n",
    "              #  .head(5)\n",
    "               .explode(['judge','judge_score_list'])\n",
    "               .rename(columns={'judge_score_list': 'judge_score'})\n",
    "               .drop(columns=['indiv_scores','judge_phrase','scores'])\n",
    "               .assign(judge = lambda df: df.judge.str.strip(\" .\"))\n",
    ")\n",
    "\n",
    "df_final.drop( df_final.query(\" judge_score == 'X' \").index, inplace = True)\n",
    "\n",
    "df_final['judge_score_id'] = np.arange(df_final.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "84af39ae-2e1a-429b-9978-b57842bfec16",
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename \"Guest judge\" with value from df_final['guest_judge']\n",
    "df_final['judge'] = np.where(df_final['judge'] == 'Guest judge', df_final['guest_judge'], df_final['judge'])\n",
    "\n",
    "# fix Julianne Hough (Night 1 only)\n",
    "df_final['judge'] = np.where(df_final['judge'] == 'Julianne Hough (Night 1 only)', 'Julianne Hough', df_final['judge'])\n",
    "\n",
    "# sorted(df_final['judge'].unique().tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "c0d83ab8-cc93-4489-9bcd-d9c98f2321a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final['judge_score'] = df_final['judge_score'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "658ab130-619e-4781-be89-14c25a052590",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This code resolves the multiple score type weeks by taking the average and then merging back to the dataframe.\n",
    "df_agg = df_final.groupby([\"performance_id\", \"judge\"]).agg({'judge_score':\"mean\"}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "96b60db0-4f39-45d1-9cab-0aed15d76ed7",
   "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>performance_id</th>\n",
       "      <th>judge</th>\n",
       "      <th>judge_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Bruno Tonioli</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>Carrie Ann Inaba</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>Len Goodman</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Bruno Tonioli</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Carrie Ann Inaba</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   performance_id             judge  judge_score\n",
       "0               0     Bruno Tonioli          6.0\n",
       "1               0  Carrie Ann Inaba          7.0\n",
       "2               0       Len Goodman          5.0\n",
       "3               1     Bruno Tonioli          6.0\n",
       "4               1  Carrie Ann Inaba          6.0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "673655d2-0f89-4fa8-84fe-55545d2b69f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# In the original, non-summarized dataframe we have two rows for the weeks with two scores,\n",
    "# the only difference is score_type, but we are going to drop that.\n",
    "# We are going to join back to this, so before we do that we want one row for each group\n",
    "# .head enables us to take just the first row in each group.\n",
    "df_summarized = \\\n",
    "(  df_final\n",
    "   .drop(columns=['score_type','judge_score'])\n",
    "   .groupby([\"performance_id\", \"judge\"])\n",
    "     .head(1)\n",
    "   .merge(df_agg, how = 'left', on = [\"performance_id\", \"judge\"])\n",
    ")\n",
    "# "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "19c2f76b-b4c2-4dc3-b793-42b90fc81b15",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['couple', 'dance_style', 'music', 'result', 'season', 'week_title',\n",
       "       'celeb_name_part', 'pro_name_part', 'professional', 'celebrity',\n",
       "       'notability', 'performance_id', 'judge', 'week', 'week_theme',\n",
       "       'dance_styles', 'primary_dance_style', 'songs', 'primary_song_phrase',\n",
       "       'primary_song', 'primary_song_artist', 'tv_show', 'original_couple',\n",
       "       'film_theme', 'mentor', 'film', 'james_bond_film', 'dance_chosen_by',\n",
       "       'disney_film', 'cirque_du_soleil_show', 'professionals',\n",
       "       'television_show', 'film_series', 'broadway_show', 'era', 'guest_judge',\n",
       "       'genre', 'judge_excluded', 'icons', 'trio_partner', 'dynamic_duo',\n",
       "       'horror_film_show', 'icon', 'dance_and_theme', 'musical', 'villain',\n",
       "       'trio_dance_partner', 'michael_jackson_theme', 'total_score',\n",
       "       'couple_list', 'couple_count', 'judge_score_count', 'judge_count',\n",
       "       'judge_score_id', 'judge_score'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final = df_summarized.copy()\n",
    "\n",
    "df_final.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "5c55a715-a6c3-45fd-aa60-e5f8dac4a406",
   "metadata": {},
   "outputs": [],
   "source": [
    "# reordering to make more understandable tables.\n",
    "# Likely there is a much better way to do this with df.insert and df.filter or reindex\n",
    "\n",
    "# all_cols = df_final.columns.values.tolist()\n",
    "\n",
    "# to_remove = ['judge_excluded', 'couple_list', 'couple_count', 'judge_score_count', \n",
    "#              'judge_count', 'dance_styles', 'songs', 'primary_song_phrase', 'dance_and_theme']\n",
    "\n",
    "# all_cols = [ele for ele in all_cols if ele not in to_remove]\n",
    "\n",
    "# front_cols = [ 'judge_score_id', 'judge','judge_score', 'performance_id','season','week','week_theme', 'couple',\n",
    "#               'primary_dance_style','primary_song', 'primary_song_artist', 'total_score' ]\n",
    "\n",
    "# remaining_cols = [ele for ele in all_cols if ele not in front_cols]\n",
    "\n",
    "# reordered = front_cols + remaining_cols\n",
    "# df_final = df_final[reordered]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "0748e016-1297-484a-90a6-4d7ed2ffe9a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "def move_to_front(df, front_cols):\n",
    "    all_cols = df.columns.values.tolist()\n",
    "    remaining_cols = [ele for ele in all_cols if ele not in front_cols]\n",
    "    return df.reindex(columns = front_cols + remaining_cols)\n",
    "               \n",
    "df_final = (\n",
    "    df_final\n",
    "    .drop(columns = ['judge_excluded', 'couple_list', 'couple_count', 'judge_score_count', \n",
    "                     'judge_count', 'dance_styles', 'songs', 'primary_song_phrase', 'dance_and_theme']      )\n",
    "    .pipe(move_to_front, front_cols = [ 'judge_score_id', 'judge', 'judge_score', 'performance_id',\n",
    "                                        'season', 'week', 'week_theme', 'couple', \n",
    "                                        'primary_dance_style','primary_song', 'primary_song_artist', \n",
    "                                        'total_score' ])\n",
    "    \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "c78c6077-3deb-46cf-8eb3-a37f0b0ca3f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_final.to_csv(\"dwts_dataset.csv\", index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "9d967471-e5de-49b6-a003-261dd73d401c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9733, 46)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f27e865d-26b2-41b5-bb92-bb2e0901d84f",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Produce tables\n",
    "\n",
    "```\n",
    "judges, celebrities and professionals all in different tables, although they are all people and people do migrate between roles.\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "76b4b9a5-0dc0-4b61-a3a1-c6343236cb78",
   "metadata": {},
   "outputs": [],
   "source": [
    "pluralizer = Pluralizer()\n",
    "\n",
    "for dim in ['couple','primary_dance_style', 'week_theme', 'judge', 'professional', 'primary_song_artist']:\n",
    "    df_final[dim + \"_id\"] = df_final[dim].factorize()[0] + 1\n",
    "    filename = \"dwts_dataset/\" + pluralizer.plural(dim) + \".csv\"\n",
    "    # os.remove(filename)\n",
    "    df_final[[dim + \"_id\", dim]].drop_duplicates().to_csv(filename, index = False, quoting=csv.QUOTE_NONNUMERIC)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "4271fbf0-2b28-4fc8-abef-c9fd160e38e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# celebrity needs known_for\n",
    "(\n",
    "    df_final\n",
    "    .assign(celebrity_id = lambda df_: df_.celebrity.factorize()[0] + 1)\n",
    "    .filter(['celebrity_id', \"celebrity\", 'notability'], axis='columns')\n",
    "    .drop_duplicates(subset=['celebrity_id', 'celebrity'])\n",
    "    .to_csv(\"dwts_dataset/\" + pluralizer.plural('celebrity') + \".csv\", \n",
    "            index = False, \n",
    "            quoting=csv.QUOTE_NONNUMERIC)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "bbca343f-4a23-4f0b-bea8-c1c1e177a4fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# song needs primary_artist\n",
    "(\n",
    "    df_final\n",
    "    .assign(primary_song_id = lambda df_: df_.primary_song.factorize()[0] + 1)\n",
    "    .filter(['primary_song_id', \"primary_song\", 'primary_song_artist_id'], axis='columns')\n",
    "    .drop_duplicates(subset=['primary_song_id', \"primary_song\"])\n",
    "    .to_csv(\"dwts_dataset/\" + pluralizer.plural('primary_song') + \".csv\", \n",
    "            index = False, \n",
    "            quoting=csv.QUOTE_NONNUMERIC)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "9ae37b5d-5770-481b-8bab-011e2e4b8771",
   "metadata": {},
   "outputs": [],
   "source": [
    "# performances\n",
    "(\n",
    "  df_final\n",
    "  .filter(['performance_id', 'season', 'week', 'week_theme_id', \n",
    "           'couple_id', 'professional_id', 'celebrity_id',\n",
    "           'primary_dance_style_id', 'primary_song_id'], axis = 'columns')\n",
    "  .drop_duplicates()\n",
    "  .to_csv(\"dwts_dataset/performances.csv\", \n",
    "          index = False, \n",
    "          quoting=csv.QUOTE_NONNUMERIC)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "55cbb7f2-782c-43eb-90f0-5156416a6b2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# scores\n",
    "(\n",
    "  df_final\n",
    "  .assign(score_id = lambda df_: np.arange(1, df_.shape[0] + 1))\n",
    "  .filter(['score_id', 'performance_id', 'judge_id', 'judge_score'], axis = 'columns')\n",
    "  .to_csv(\"dwts_dataset/scores.csv\", \n",
    "          index = False, \n",
    "          quoting=csv.QUOTE_NONNUMERIC)\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}