{
"cells": [
{
"attachments": {},
"cell_type": "markdown",
"id": "88a31059",
"metadata": {},
"source": [
"# 2. Data Cleaning\n"
]
},
{
"cell_type": "code",
"execution_count": 240,
"id": "a30a20db",
"metadata": {
"tags": [
"remove_input"
]
},
"outputs": [],
"source": [
"# Importing Libraries\n",
"import pandas as pd\n",
"import numpy as np\n",
"import matplotlib.pyplot as plt\n",
"import seaborn as sns\n",
"from IPython.display import set_matplotlib_formats\n",
"\n",
"sns.set(style=\"whitegrid\")\n",
"sns.set_context(\"poster\", font_scale=.5, rc={\"grid.linewidth\": 0.6})\n",
"sns.set_style({'font.family': 'Roboto'})\n",
"# jupyter-book clean ./ --all\n",
"# jupyter-book build ./\n",
"# ghp-import -n -p -f _build/html\n",
"\n",
"import warnings\n",
"warnings.filterwarnings(\"ignore\")\n",
"from pandas.core.common import SettingWithCopyWarning\n",
"warnings.simplefilter(action=\"ignore\", category=SettingWithCopyWarning)\n"
]
},
{
"cell_type": "markdown",
"id": "6d9d793a",
"metadata": {},
"source": [
"### 2.1 Members Data\n"
]
},
{
"cell_type": "code",
"execution_count": 289,
"id": "7356f566",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"memberID 0\n",
"holdtime 0\n",
"puzzlepack 0\n",
"dtype: int64"
]
},
"execution_count": 289,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"member_holdtime_df=pd.read_csv('data/member_holdtime_df.csv')\n",
"member_holdtime_df.isnull().sum()"
]
},
{
"cell_type": "code",
"execution_count": 290,
"id": "07c920d8",
"metadata": {},
"outputs": [],
"source": [
"# removing outliers\n",
"member_holdtime_df = member_holdtime_df[(member_holdtime_df['holdtime'] >= 0.1) & \n",
" (member_holdtime_df['holdtime'] <= 150)]"
]
},
{
"cell_type": "markdown",
"id": "8ea7c5df",
"metadata": {},
"source": [
"### 2.2 Packs Data\n"
]
},
{
"cell_type": "code",
"execution_count": 243,
"id": "2f29f47c",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" pack_name \n",
" brand \n",
" piece_count \n",
" difficulty_rating \n",
" piece_count_1 \n",
" piece_count_2 \n",
" difficulty_rating_1 \n",
" difficulty_rating_2 \n",
" \n",
" \n",
" \n",
" \n",
" 0 \n",
" Anne Belle Thompson The Mikado Anne Belle Thom... \n",
" Other-Hand-cut \n",
" 387,242 \n",
" A-Easy,A-Easy \n",
" 387.0 \n",
" 242.0 \n",
" A-Easy \n",
" A-Easy \n",
" \n",
" \n",
" 1 \n",
" Anthology Puzzles Alphonse Mucha La Plume Anth... \n",
" NaN \n",
" 320,160 \n",
" Average,Average \n",
" 320.0 \n",
" 160.0 \n",
" Average \n",
" Average \n",
" \n",
" \n",
" 2 \n",
" Anthology Puzzles Colorful Cat Anthology Puzzl... \n",
" NaN \n",
" 150,170 \n",
" Really-Hard,A-Easy \n",
" 150.0 \n",
" 170.0 \n",
" Really-Hard \n",
" A-Easy \n",
" \n",
" \n",
" 3 \n",
" Anthology Puzzles Framed American Gothic Antho... \n",
" NaN \n",
" 320,300 \n",
" Average,Average \n",
" 320.0 \n",
" 300.0 \n",
" Average \n",
" Average \n",
" \n",
" \n",
" 4 \n",
" Anthology Puzzles Over The Moon Anthology Puzz... \n",
" NaN \n",
" 278,177 \n",
" Average,A-Easy \n",
" 278.0 \n",
" 177.0 \n",
" Average \n",
" A-Easy \n",
" \n",
" \n",
"
\n",
"
"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Bar charts for distribution of brand\n",
"fig, axs = plt.subplots(1, 2, figsize=(15, 5))\n",
"fig.suptitle(\"Figure 3. Value Counts for Brand\", weight='heavy',y =0.99, x=0.16).set_fontsize('18')\n",
"\n",
"sns.histplot(data=packs_df, y=packs_df[packs_df['brand_1'].notnull()]['brand_1'], color=\"#e794bb\", alpha = 1.0, ax=axs[0])\n",
"axs[0].set_title(\"Puzzle 1\")\n",
"sns.histplot(data=packs_df, y=packs_df[packs_df['brand_2'].notnull()]['brand_2'], color=\"#ba328b\", alpha = 1.0, ax=axs[1])\n",
"axs[1].set_title(\"Puzzle 2\")\n",
"\n",
"sns.despine(left=True, bottom=True)\n",
"fig.tight_layout()\n",
"# plt.savefig(\"_static/images/brand_dist.png\", format=\"png\", dpi=1200)\n",
"plt.show()"
]
},
{
"cell_type": "code",
"execution_count": 252,
"id": "ebb6c87d",
"metadata": {},
"outputs": [],
"source": [
"# Some 1 puzzle packs have a second number for pieces, this seems to be legit, updating to be 2 puzzle packs\n",
"packs_df.loc[((packs_df.num_puzzles == 1) & (packs_df.piece_count_2.notna())), 'num_puzzles'] = 2"
]
},
{
"cell_type": "code",
"execution_count": 253,
"id": "d5459e84",
"metadata": {},
"outputs": [],
"source": [
"packs_df.piece_count_1 = packs_df.piece_count_1.astype('float').astype('Int64')\n",
"packs_df.piece_count_2 = packs_df.piece_count_2.astype('float').astype('Int64')\n",
"\n",
"avg_pc1 = packs_df['piece_count_1'].median()\n",
"avg_pc2 = packs_df['piece_count_2'].median()"
]
},
{
"cell_type": "code",
"execution_count": 254,
"id": "38e4c2e2",
"metadata": {},
"outputs": [],
"source": [
"# puzzles which have no value for piece_1 is replaced with avg values\n",
"packs_df['piece_count_1'].fillna(avg_pc1, inplace=True)"
]
},
{
"cell_type": "code",
"execution_count": 255,
"id": "a19a8ece",
"metadata": {},
"outputs": [],
"source": [
"# if 2 puzzles and second piececount is not available, replace with average\n",
"packs_df.loc[((packs_df['piece_count_2'].isna() ) &(packs_df['num_puzzles'] ==2)), 'piece_count_2'] = int(avg_pc2)"
]
},
{
"cell_type": "code",
"execution_count": 256,
"id": "10d2d338",
"metadata": {},
"outputs": [],
"source": [
"# puzzles with no second piece_count, make second piece count 0 \n",
"# (because num_puzzle = 2 was dealt with earlier)\n",
"packs_df.loc[\n",
" (packs_df['piece_count_2'].isna()) & (packs_df['num_puzzles'] == 1),\n",
" 'piece_count_2',\n",
"] = 0"
]
},
{
"cell_type": "code",
"execution_count": 257,
"id": "433f63ca",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"pack_name 0\n",
"piece_count_1 0\n",
"piece_count_2 0\n",
"difficulty_rating_1 4\n",
"difficulty_rating_2 4\n",
"brand_2 0\n",
"brand_1 0\n",
"num_puzzles 0\n",
"dtype: int64"
]
},
"execution_count": 257,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"packs_df.isnull().sum()"
]
},
{
"cell_type": "code",
"execution_count": 258,
"id": "05fdf166",
"metadata": {},
"outputs": [],
"source": [
"packs_df['difficulty_rating_1'].fillna('Average', inplace=True)\n",
"packs_df['difficulty_rating_2'].fillna('Average', inplace=True)"
]
},
{
"cell_type": "markdown",
"id": "dfb4cb56",
"metadata": {},
"source": [
"### 2.3 Merged data"
]
},
{
"cell_type": "code",
"execution_count": 259,
"id": "f95473b8",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" memberID \n",
" holdtime \n",
" puzzlepack \n",
" pack_name \n",
" piece_count_1 \n",
" piece_count_2 \n",
" difficulty_rating_1 \n",
" difficulty_rating_2 \n",
" brand_2 \n",
" brand_1 \n",
" num_puzzles \n",
" \n",
" \n",
" \n",
" \n",
" 0 \n",
" member1 \n",
" 2.939411 \n",
" Artifact Puzzles Justin Hillgrove Word Travels... \n",
" Artifact Puzzles Justin Hillgrove Word Travels... \n",
" 456 \n",
" 548 \n",
" A-Easy \n",
" Average \n",
" Artifact \n",
" Artifact \n",
" 2.0 \n",
" \n",
" \n",
" 1 \n",
" member1 \n",
" 0.998885 \n",
" DaVici Puzzles Full Moon Feast DaVici Puzzles ... \n",
" DaVici Puzzles Full Moon Feast DaVici Puzzles ... \n",
" 195 \n",
" 220 \n",
" A-Easy \n",
" Hard \n",
" DaVici \n",
" DaVici \n",
" 2.0 \n",
" \n",
" \n",
"
\n",
"
\n",
"Tip: # Ideally we should have information about all the packs. Since we do not have this information and it is recommended that we do not drop these rows, we will impute these with the average values.\n",
"
"
]
},
{
"cell_type": "markdown",
"id": "aa05a8a3",
"metadata": {},
"source": [
"### 2.4 Dealing with Nulls!\n",
"**Methodology**\n",
"\n",
"* num_puzzles $\\longrightarrow$ Count number of times 'Puzzle' appears in puzzlepack\n",
"* brand_1, brand_2 $\\longrightarrow$ Take word before 'Puzzle' as brand name\n",
"* difficulty_rating $\\longrightarrow$ Take average at brand level from known data\n",
"* piece_count $\\longrightarrow$ Take average at brand level from known data"
]
},
{
"cell_type": "code",
"execution_count": 263,
"id": "d7c0c333",
"metadata": {},
"outputs": [],
"source": [
"missing_pack_data = df.loc[df['pack_name'].isna()][['puzzlepack', 'piece_count_1', 'piece_count_2',\n",
" 'difficulty_rating_1', 'difficulty_rating_2', 'brand_1', \n",
" 'brand_2', 'num_puzzles']]\n",
"missing_pack_data.reset_index(drop=True, inplace=True)"
]
},
{
"cell_type": "code",
"execution_count": 264,
"id": "1282719e",
"metadata": {},
"outputs": [],
"source": [
"# getting number of puzzles\n",
"missing_pack_data['num_puzzles'] = missing_pack_data['puzzlepack'].apply(lambda x: (x.count('Puzzles')))"
]
},
{
"cell_type": "code",
"execution_count": 265,
"id": "f8a92e5f",
"metadata": {},
"outputs": [],
"source": [
"# packs can have only 1 or two puzzles\n",
"for i in range(len(missing_pack_data)):\n",
" if missing_pack_data['num_puzzles'][i] < 1:\n",
" missing_pack_data['num_puzzles'][i] = 1\n",
" elif missing_pack_data['num_puzzles'][i] > 2:\n",
" missing_pack_data['num_puzzles'][i] = 2"
]
},
{
"cell_type": "code",
"execution_count": 266,
"id": "60ad0f2b",
"metadata": {},
"outputs": [],
"source": [
"## to limit the number of brand names we will only use the brands that we know\n",
"known_brands = list(set(packs_df['brand_1'].unique()).union(set(packs_df['brand_2'].unique())))"
]
},
{
"cell_type": "code",
"execution_count": 267,
"id": "57ca7af2",
"metadata": {},
"outputs": [],
"source": [
"# getting brand_1\n",
"for i in range(len(missing_pack_data)):\n",
" words = missing_pack_data['puzzlepack'][i].split()[:2]\n",
"\n",
" if 'Puzzles' in words[1:]: # if its the second or third word\n",
" if words[words.index('Puzzles')-1] in (known_brands):\n",
" missing_pack_data['brand_1'][i] = words[words.index('Puzzles')-1]\n",
" else:\n",
" missing_pack_data['brand_1'][i] = 'unknown'\n"
]
},
{
"cell_type": "code",
"execution_count": 268,
"id": "7529e7eb",
"metadata": {},
"outputs": [],
"source": [
"# getting brand_2\n",
"for i in range(len(missing_pack_data)):\n",
" words = missing_pack_data['puzzlepack'][i].split()[2:]\n",
" if 'Puzzles' in words[1:]: # if its the 4th word onwards\n",
" if words[words.index('Puzzles')-1] in (known_brands):\n",
" missing_pack_data['brand_2'][i] = words[words.index('Puzzles')-1]\n",
" else:\n",
" missing_pack_data['brand_2'][i] = 'unknown'\n"
]
},
{
"cell_type": "code",
"execution_count": 269,
"id": "44c2ab96",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"num_puzzles 0\n",
"brand_1 139\n",
"brand_2 333\n",
"dtype: int64"
]
},
"execution_count": 269,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"missing_pack_data[['num_puzzles', 'brand_1', 'brand_2']].isnull().sum()"
]
},
{
"cell_type": "code",
"execution_count": 270,
"id": "e14ac8a2",
"metadata": {},
"outputs": [],
"source": [
"# if anything still missing in num_puzzles, brands\n",
"missing_pack_data['num_puzzles'].fillna(1, inplace=True)\n",
"missing_pack_data['brand_1'].fillna('unknown', inplace=True)\n",
"missing_pack_data['brand_2'].fillna('unknown', inplace=True)"
]
},
{
"cell_type": "code",
"execution_count": 271,
"id": "dd163317",
"metadata": {},
"outputs": [],
"source": [
"# brand level average piece count values -- KNOWN data\n",
"groupby_brand_pieces_1 = packs_df.groupby(\"brand_1\")[\"piece_count_1\"].mean()\n",
"groupby_brand_pieces_2 = packs_df.groupby(\"brand_2\")[\"piece_count_2\"].mean()"
]
},
{
"cell_type": "code",
"execution_count": 272,
"id": "424af44b",
"metadata": {},
"outputs": [],
"source": [
"# getting piece_count_1\n",
"for i in range(0, len(missing_pack_data)):\n",
" try:\n",
" missing_pack_data['piece_count_1'][i] = int(groupby_brand_pieces_1[missing_pack_data['brand_1'][i]])\n",
" # getting piece_count_2 if needed\n",
" if missing_pack_data['num_puzzles'][i] == 1:\n",
" missing_pack_data['piece_count_2'][i] = 0\n",
" else:\n",
" missing_pack_data['piece_count_2'][i] = int(groupby_brand_pieces_2[missing_pack_data['brand_2'][i]])\n",
" except Exception as e:\n",
" pass\n",
"# print(e)"
]
},
{
"cell_type": "code",
"execution_count": 273,
"id": "47c40d25",
"metadata": {},
"outputs": [],
"source": [
"# brand level most common difficulty values -- KNOWN data\n",
"groupby_brand_diff_1 = packs_df.groupby(\"brand_1\")[\"difficulty_rating_1\"].agg(pd.Series.mode)\n",
"groupby_brand_diff_2 = packs_df.groupby(\"brand_2\")[\"difficulty_rating_2\"].agg(pd.Series.mode)"
]
},
{
"cell_type": "code",
"execution_count": 274,
"id": "dacfe152",
"metadata": {},
"outputs": [],
"source": [
"# getting difficulty_rating_1\n",
"for i in range(0, len(missing_pack_data)):\n",
" try:\n",
" missing_pack_data['difficulty_rating_1'][i] = groupby_brand_diff_1[missing_pack_data['brand_1'][i]]\n",
" # getting difficulty_rating_2 if needed\n",
" if missing_pack_data['num_puzzles'][i] == 1:\n",
" missing_pack_data['difficulty_rating_2'][i] = 'Average'\n",
" else:\n",
" missing_pack_data['difficulty_rating_2'][i] = (groupby_brand_diff_2[missing_pack_data['brand_2'][i]])\n",
" except Exception as e:\n",
" pass"
]
},
{
"cell_type": "code",
"execution_count": 275,
"id": "edeef37f",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"puzzlepack 0\n",
"piece_count_1 0\n",
"piece_count_2 0\n",
"difficulty_rating_1 0\n",
"difficulty_rating_2 0\n",
"brand_1 0\n",
"brand_2 0\n",
"num_puzzles 0\n",
"dtype: int64"
]
},
"execution_count": 275,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"missing_pack_data.isnull().sum()"
]
},
{
"cell_type": "markdown",
"id": "11d4827c",
"metadata": {},
"source": [
"**Comments:**\n",
"- No missing values in the data\n",
"- We have information about 675 members and 910 unique puzzle packs\n"
]
},
{
"cell_type": "markdown",
"id": "a0c1be0f",
"metadata": {},
"source": [
"### Making a final packs data"
]
},
{
"cell_type": "code",
"execution_count": 276,
"id": "c12eb596",
"metadata": {},
"outputs": [],
"source": [
"# setting order same as missing pack to concat easily\n",
"packs_df = packs_df[['pack_name', 'piece_count_1', 'piece_count_2', 'difficulty_rating_1', \n",
" 'difficulty_rating_2', 'brand_1', 'brand_2', 'num_puzzles']]"
]
},
{
"cell_type": "code",
"execution_count": 277,
"id": "54ca2cc3",
"metadata": {},
"outputs": [],
"source": [
"# setting same column names to concat easily\n",
"missing_pack_data = missing_pack_data.rename(columns = {'puzzlepack':'pack_name'})"
]
},
{
"cell_type": "code",
"execution_count": 278,
"id": "4b3386c5",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"((920, 8), (1085, 8))"
]
},
"execution_count": 278,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"packs_df.shape, missing_pack_data.shape"
]
},
{
"cell_type": "code",
"execution_count": 279,
"id": "10f53433",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(2005, 8)"
]
},
"execution_count": 279,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"packs_updated = pd.concat([packs_df, missing_pack_data])\n",
"packs_updated.reset_index(inplace=True, drop = True)\n",
"packs_updated.shape"
]
},
{
"cell_type": "code",
"execution_count": 280,
"id": "911bf569",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" memberID \n",
" holdtime \n",
" puzzlepack \n",
" pack_name \n",
" piece_count_1 \n",
" piece_count_2 \n",
" difficulty_rating_1 \n",
" difficulty_rating_2 \n",
" brand_1 \n",
" brand_2 \n",
" num_puzzles \n",
" \n",
" \n",
" \n",
" \n",
" 0 \n",
" member1 \n",
" 2.939411 \n",
" Artifact Puzzles Justin Hillgrove Word Travels... \n",
" Artifact Puzzles Justin Hillgrove Word Travels... \n",
" 456 \n",
" 548 \n",
" A-Easy \n",
" Average \n",
" Artifact \n",
" Artifact \n",
" 2 \n",
" \n",
" \n",
" 1 \n",
" member1 \n",
" 0.998885 \n",
" DaVici Puzzles Full Moon Feast DaVici Puzzles ... \n",
" DaVici Puzzles Full Moon Feast DaVici Puzzles ... \n",
" 195 \n",
" 220 \n",
" A-Easy \n",
" Hard \n",
" DaVici \n",
" DaVici \n",
" 2 \n",
" \n",
" \n",
"
\n",
"