Skip to content

Instantly share code, notes, and snippets.

@farosFreed

farosFreed/Analyzing Album Sales to Find Inventory & Advertising Oppurtunies w SQL - FINAL.ipynb Secret

Created August 23, 2020 00:58
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save farosFreed/f9107b6166c52a66d035dbd94aff34bf to your computer and use it in GitHub Desktop.
Save farosFreed/f9107b6166c52a66d035dbd94aff34bf to your computer and use it in GitHub Desktop.
Download ZIP
Analyzing Album Sales to Find Inventory & Advertising Oppurtunies w SQL
Display the source blob
Display the rendered blob
Raw
Analyzing Album Sales to Find Inventory & Advertising Oppurtunies w SQL - FINAL.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Analyzing Album Sales to Find Inventory & Advertising Opportunities w SQL"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Our record store has a database of sales, customer, and employee info that we rarely look at. We want to use our sales database to see if we can answer some questions:\n",
"\n",
"- What genres should we order the most of?\n",
"- Which employees are performing well?\n",
"- Which countries do people order from the most?\n",
"- Should we buy more individual tracks or full albums?"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Set Up Connection & Look at Database Tables\n",
"\n",
"Before we get started, we need to take care of some basics like loading dependencies, testing our connection, and taking a look at our database to see what we have to work with. \n",
"\n",
"Let's do that now. \n"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"# load SQL for queries, pandas for dataframes, and matplotlib for charts\n",
"import sqlite3 as sql\n",
"import pandas as pd\n",
"import numpy as np\n",
"import matplotlib.pyplot as plt\n",
"#make plots show in notebook\n",
"%matplotlib inline"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[('Luís Gonçalves',), ('Leonie Köhler',), ('François Tremblay',), ('Bjørn Hansen',), ('František Wichterlová',), ('Helena Holý',), ('Astrid Gruber',), ('Daan Peeters',), ('Kara Nielsen',), ('Eduardo Martins',)]\n"
]
}
],
"source": [
"# open connection to database and test a query\n",
"conn = sql.connect(\"Data/chinook.db\")\n",
"query = 'SELECT first_name || \" \" || last_name name FROM customer LIMIT 10'\n",
"result = conn.execute(query).fetchall()\n",
"conn.close()\n",
"\n",
"print(result)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"**Success.** Our query returned 10 customers so our connection to the database is working. Before we look further at our data, let's write a couple functions to make it easy to safety query the database and run commands. While we're at it, we'll make a function to show which tables we have data for. "
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"# function to query database safely\n",
"def run_query(q):\n",
" #open connection with context manager to ensure its not left open\n",
" with sql.connect('Data/chinook.db') as conn:\n",
" #return query as a pandas dataframe instead of text output\n",
" result = pd.read_sql(q, conn)\n",
" return result"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"# function to run commands on database that dont require a query (create view, etc)\n",
"def run_command(c):\n",
" with sql.connect('Data/chinook.db') as conn:\n",
" #auto commit any changes\n",
" conn.isolation_level = None\n",
" conn.execute(c)"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [],
"source": [
"# function to show all tables and views in the database currently\n",
"def show_tables():\n",
" query = 'SELECT name, type FROM sqlite_master WHERE type IN (\"table\",\"view\");'\n",
" return run_query(query)"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" name type\n",
"0 album table\n",
"1 artist table\n",
"2 customer table\n",
"3 employee table\n",
"4 genre table\n",
"5 invoice table\n",
"6 invoice_line table\n",
"7 media_type table\n",
"8 playlist table\n",
"9 playlist_track table\n",
"10 track table\n",
"11 invoice_track_list view\n",
"12 album_track_list view\n",
"13 full_album view\n"
]
}
],
"source": [
"# using our function to see all our database tables\n",
"data = show_tables()\n",
"print(data)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Now we have our basic setup and we know what kind of data we have in the database. Let's start answering the questions we have. We will chart results where possible to make it easier to infer answers.\n",
"\n",
"## Question 1: What albums should we order?\n",
"\n",
"4 new albums released this week and we need to decide which ones to order. \n",
"\n",
"**Artist Name\t\\ Genre**\n",
"- Regal \\ Hip-Hop\n",
"- Red Tone \\ Punk\n",
"- Meteor and the Girls \\ Pop\n",
"- Slim Jim Bites \\ Blues\n",
"\n",
"Let's analyze our sales data by genre and see which genres are popular."
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" genre quantity percentage\n",
"0 Rock 2635 0.553921\n",
"1 Metal 619 0.130124\n",
"2 Alternative & Punk 492 0.103427\n",
"3 Latin 167 0.035106\n",
"4 R&B/Soul 159 0.033424\n",
"5 Blues 124 0.026067\n",
"6 Jazz 121 0.025436\n",
"7 Alternative 117 0.024595\n",
"8 Easy Listening 74 0.015556\n",
"9 Pop 63 0.013244\n",
"10 Electronica/Dance 55 0.011562\n",
"11 Classical 47 0.009880\n",
"12 Reggae 35 0.007358\n",
"13 Hip Hop/Rap 33 0.006937\n",
"14 Heavy Metal 8 0.001682\n",
"15 Soundtrack 5 0.001051\n",
"16 TV Shows 2 0.000420\n",
"17 Drama 1 0.000210\n",
"18 World 0 0.000000\n",
"19 Science Fiction 0 0.000000\n",
"20 Sci Fi & Fantasy 0 0.000000\n",
"21 Rock And Roll 0 0.000000\n",
"22 Opera 0 0.000000\n",
"23 Comedy 0 0.000000\n",
"24 Bossa Nova 0 0.000000\n"
]
}
],
"source": [
"# SELECT genres and order by quantity of sales \n",
"genres_sold = \"\"\"\n",
"SELECT g.name genre, COUNT(il.quantity) quantity, CAST(COUNT(il.quantity) as float)/CAST((SELECT SUM(quantity) FROM invoice_line) as float) percentage FROM track t \n",
"LEFT JOIN genre g ON g.genre_id = t.genre_id \n",
"LEFT JOIN invoice_line il ON il.track_id = t.track_id \n",
"GROUP BY genre ORDER BY quantity DESC \n",
"\"\"\"\n",
"# display the results\n",
"genres_analysis = run_query(genres_sold)\n",
"print(genres_analysis)"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Text(0.5, 1.0, 'Albums Sales by Genre')"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
},
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAX0AAAFcCAYAAAAkiW7CAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nO2dd5glRdWH3x9LluwuiAvsIkEBlZwNICjBAAaSCIgB/UQFTB+YyIoi8hlRFBARFkFAVkCCKznJ7rIkAVmJKyAZVkARON8fp+5Oz51O987MnRn6vM/Tz71dXdVVt2/36apT55ySmREEQRA0g/lGugFBEARB7wihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdDgqRfSToifd9C0pyRbtNQIskkrTrazxkEVYTQDzpC0mWSnpS00Ei3pVMk7SBplqRnJD0maZqkySPdrqFA0vKSfiHpQUn/knR3ehG/YaTbFowuQugHtUkC8q2AAe8b0cZ0SOpR/xr4IrAksDLwU+DlkWzXUCDp1cA1wKL4/7M4sB5wOfDOYahv/qE+Z9A7QugHnbAncB3wK2CvqsySvpp61PdK2j2TfpmkT2T2Pyrpqsy+SfqMpLskzZV0uKRVJF2beulnSFow5R0v6TxJT0l6QtKVkvLu63WAe8xsmjlzzewsM7s/nWejdP6nJD0k6cetOnJ+10KSvifpfkn/lPQzSYt02J4W26de+WOSjpY0Xzr/E5LelKlzWUnPS5qQc44DgGeAPczs7+n3PWVmJ5nZjzLn2ETSNaltN0naou0/OVzS1emaXyxpfDo2Of0nH5d0P/DnqvMFo5cQ+kEn7AmcmrZtJC1Xkvc1wHhgIv6COF7S6zuoa1tgfWAT4CvA8cDuwIrAG4HdUr4vAnOACcBywFfxkUg7M4E3SDpW0paSFms7/hIuPMcDmwJbAZ8paNt3gNXxF8mq6Td+s8P2tHg/sAHeM98B+JiZ/Qc4HfhIJt9uwJ/M7NGcc2wNnGNmhaMWSROB84EjgGWALwFntb1EPgzsDSwLLJjyZHk7sAb+39c5XzAKCaEf1ELSW4BJwBlmNgP4Oy4kyviGmf3HzC7HBcTOHVT5HTN7xsxuA24FLjazu83saeCPwLop33+B5YFJZvZfM7vScgJKmdndwBa4gD4DeCzpvBdLx2eY2XVm9qKZ3Qv8HBdy7ddBwCeBA8zsCTObC3wL2LWT9rT9zifSiOP/6HuZnQx8ODNK2AM4peAc44GHM218X+p9z5V0cUr+CHCBmV1gZi+b2SXAdGD7zHlOMrO/mdnz6Rqt01bPIWb2bDpe53zBKCSEflCXvXDB+1jaP41yFc+TZvZsZv8+4LUd1PfPzPfnc/ZbPfWjgdnAxUlNcmDRCZNQ39nMJuC677cBXwOQtHpSyzws6RlckI/POc0EXHc+IwnWp4ALU3pH7Uk8kPk+7xqZ2fXAs8Db02TsqsDUgnM8jr9oWr9zqpkthY9cWiqqScBOrTandr8lW47MiwN4jr5rnNfWOucLRiExIRNUkvTVOwPjJLUEw0LAUpLWNrObcootLelVGcG/Et5jBxdmi2byvqbbtqWe9heBL0paC7hU0g1mNq2i3A2SzsZVRQDHATcCu5nZXEn7Ax/KKfoY/tJZy8z+MQTtWRG4LX1fCXgwc+xkvEf9MPA7M/t3wTmmATtKOrRExfMAcIqZfbLgeB2yI5ahOF8wAkRPP6jDjrjOe018yL8Ortu9EtfzF3GopAUlvRV4D3BmSp8FfEDSosmq5uPdNkzSeyStmtQuz6R2vpST7y2SPilp2bT/BtwC6bqUZfFU/l/p2P/k1ZeE6i+AYzPnmihpm07ak+HLkpaWtCKwH/DbzLFTcJ3/R3DLoyK+DywNnCKf8JakxemvnvkN8F5J20gaJ2lhuT/FCiXnLWOozxf0iBD6QR32wvW995vZw60N+DGwu/JN+B4GnsR7rqcCnzazO9KxY4EXcJXNyel4t6wG/An4F3At8FMzuywn31O4kL9F0r9wlcw5wHfT8S/hcxRzcaH+25xztPhfXIVzXVIF/QloTVLXbU+Lc4EZ+IvwfOCE1gEzm4NPQBv+gs0lqdw2Af4NXJV+wyz8RfY/Kc8D+ETxV4FH8Z76l+lSBgz1+YLeoVhEJQhGL5JOBB40s6+PdFuCVwah0w+CUYrcGe4D9FkqBcGgiaFYEIxCJB2OT3wfbWb3jHR7glcOod4JgiBoENHTD4IgaBAh9IMgCBrEqJ7IHT9+vE2ePHmkmxEEQTCmmDFjxmPJ83wAo1roT548menTp490M4IgCMYUku4rOhbqnSAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGsSods5qZ/KB5+em33vUu3vckiAIgrFJ9PSDIAgaRAj9IAiCBhFCPwiCoEGE0A+CIGgQIfSDIAgaRAj9IAiCBlEp9CWtKOlSSbdLuk3Sfin9EEn/kDQrbdtnyhwkabakOyVtk0nfNqXNlnTg8PykIAiCoIg6dvovAl80s5mSFgdmSLokHTvWzL6XzSxpTWBXYC3gtcCfJK2eDv8EeCcwB7hB0lQz++tQ/JAgCIKgmkqhb2YPAQ+l73Ml3Q5MLCmyA3C6mf0HuEfSbGCjdGy2md0NIOn0lDeEfhAEQY/oSKcvaTKwLnB9SvqspJslnShp6ZQ2EXggU2xOSitKD4IgCHpEbaEvaTHgLGB/M3sGOA5YBVgHHwkc08qaU9xK0tvr2UfSdEnTH3300brNC4IgCGpQS+hLWgAX+Kea2dkAZvZPM3vJzF4GfkGfCmcOsGKm+ArAgyXp/TCz481sAzPbYMKE3MXcgyAIgi6pY70j4ATgdjP7fiZ9+Uy29wO3pu9TgV0lLSRpZWA14C/ADcBqklaWtCA+2Tt1aH5GEARBUIc61jubA3sAt0ialdK+CuwmaR1cRXMv8CkAM7tN0hn4BO2LwL5m9hKApM8CFwHjgBPN7LYh/C1BEARBBXWsd64iXx9/QUmZI4Ejc9IvKCsXBEEQDC/hkRsEQdAgQugHQRA0iBD6QRAEDSKEfhAEQYMIoR8EQdAgQugHQRA0iBD6QRAEDSKEfhAEQYMIoR8EQdAgQugHQRA0iBD6QRAEDSKEfhAEQYMIoR8EQdAgQugHQRA0iBD6QRAEDSKEfhAEQYMIoR8EQdAgQugHQRA0iBD6QRAEDSKEfhAEQYMIoR8EQdAgQugHQRA0iBD6QRAEDSKEfhAEQYMIoR8EQdAgQugHQRA0iBD6QRAEDSKEfhAEQYMIoR8EQdAgQugHQRA0iEqhL2lFSZdKul3SbZL2S+nLSLpE0l3pc+mULkk/lDRb0s2S1suca6+U/y5Jew3fzwqCIAjyqNPTfxH4opmtAWwC7CtpTeBAYJqZrQZMS/sA2wGrpW0f4DjwlwRwMLAxsBFwcOtFEQRBEPSGSqFvZg+Z2cz0fS5wOzAR2AE4OWU7Gdgxfd8B+LU51wFLSVoe2Aa4xMyeMLMngUuAbYf01wRBEASldKTTlzQZWBe4HljOzB4CfzEAy6ZsE4EHMsXmpLSi9CAIgqBH1Bb6khYDzgL2N7NnyrLmpFlJens9+0iaLmn6o48+Wrd5QRAEQQ1qCX1JC+AC/1QzOzsl/zOpbUifj6T0OcCKmeIrAA+WpPfDzI43sw3MbIMJEyZ08luCIAiCCupY7wg4AbjdzL6fOTQVaFng7AWcm0nfM1nxbAI8ndQ/FwHvkrR0msB9V0oLgiAIesT8NfJsDuwB3CJpVkr7KnAUcIakjwP3AzulYxcA2wOzgeeAvQHM7AlJhwM3pHyHmdkTQ/IrgiAIglpUCn0zu4p8fTzAVjn5Ddi34FwnAid20sAgCIJg6AiP3CAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoEJVCX9KJkh6RdGsm7RBJ/5A0K23bZ44dJGm2pDslbZNJ3zalzZZ04ND/lCAIgqCKOj39XwHb5qQfa2brpO0CAElrArsCa6UyP5U0TtI44CfAdsCawG4pbxAEQdBD5q/KYGZXSJpc83w7AKeb2X+AeyTNBjZKx2ab2d0Akk5Pef/acYuDIAiCrhmMTv+zkm5O6p+lU9pE4IFMnjkprSh9AJL2kTRd0vRHH310EM0LgiAI2ulW6B8HrAKsAzwEHJPSlZPXStIHJpodb2YbmNkGEyZM6LJ5QRAEQR6V6p08zOyfre+SfgGcl3bnACtmsq4APJi+F6UHQRAEPaKrnr6k5TO77wdalj1TgV0lLSRpZWA14C/ADcBqklaWtCA+2Tu1+2YHQRAE3VDZ05c0BdgCGC9pDnAwsIWkdXAVzb3ApwDM7DZJZ+ATtC8C+5rZS+k8nwUuAsYBJ5rZbUP+a4IgCIJS6ljv7JaTfEJJ/iOBI3PSLwAu6Kh1QRAEwZASHrlBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBg6gU+pJOlPSIpFszactIukTSXelz6ZQuST+UNFvSzZLWy5TZK+W/S9Jew/NzgiAIgjLq9PR/BWzblnYgMM3MVgOmpX2A7YDV0rYPcBz4SwI4GNgY2Ag4uPWiCIIgCHpHpdA3syuAJ9qSdwBOTt9PBnbMpP/anOuApSQtD2wDXGJmT5jZk8AlDHyRBEEQBMNMtzr95czsIYD0uWxKnwg8kMk3J6UVpQ9A0j6Spkua/uijj3bZvCAIgiCPoZ7IVU6alaQPTDQ73sw2MLMNJkyYMKSNC4IgaDrdCv1/JrUN6fORlD4HWDGTbwXgwZL0IAiCoId0K/SnAi0LnL2AczPpeyYrnk2Ap5P65yLgXZKWThO470ppQRAEQQ+ZvyqDpCnAFsB4SXNwK5yjgDMkfRy4H9gpZb8A2B6YDTwH7A1gZk9IOhy4IeU7zMzaJ4eDIAiCYaZS6JvZbgWHtsrJa8C+Bec5ETixo9YFQRAEQ0p45AZBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CDmH+kGDDeTDzy/8Ni9R727hy0JgiAYeaKnHwRB0CBC6AdBEDSIEPpBEAQNIoR+EARBgwihHwRB0CAGJfQl3SvpFkmzJE1PactIukTSXelz6ZQuST+UNFvSzZLWG4ofEARBENRnKHr6W5rZOma2Qdo/EJhmZqsB09I+wHbAamnbBzhuCOoOgiAIOmA41Ds7ACen7ycDO2bSf23OdcBSkpYfhvqDIAiCAgYr9A24WNIMSfuktOXM7CGA9LlsSp8IPJApOyelBUEQBD1isB65m5vZg5KWBS6RdEdJXuWk2YBM/vLYB2CllVYaZPO6p8iTN7x4gyAYywyqp29mD6bPR4BzgI2Af7bUNunzkZR9DrBipvgKwIM55zzezDYwsw0mTJgwmOYFQRAEbXQt9CW9StLire/Au4BbganAXinbXsC56ftUYM9kxbMJ8HRLDRQEQRD0hsGod5YDzpHUOs9pZnahpBuAMyR9HLgf2CnlvwDYHpgNPAfsPYi6gyAIgi7oWuib2d3A2jnpjwNb5aQbsG+39QVBEASDJzxygyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGkQI/SAIggYRQj8IgqBBhNAPgiBoECH0gyAIGsT8I92AVxKTDzy/8Ni9R727hy0JgiDIJ4T+CBMviiAIekmod4IgCBpE9PTHKEUjhBgdBEFQRgj9BtGtKileMEHwyiHUO0EQBA0ihH4QBEGDCPVOMCyEVVIQjE5C6AejhnhRBMHwE0I/GPN0M9EcL5igqfRc6EvaFvgBMA74pZkd1es2BEG3xAsmGOv0dCJX0jjgJ8B2wJrAbpLW7GUbgiAImkyve/obAbPN7G4ASacDOwB/7XE7gmBUE6ODYLiQmfWuMulDwLZm9om0vwewsZl9NpNnH2CftPt64M6C040HHuuiGd2U61WZXtY12tvXy7pGe/t6Wddob18v6xrt7SsrN8nMJuSWMLOebcBOuB6/tb8H8KMuzzW9V+V6VSbaF9dipOsa7e2LazH4cr12zpoDrJjZXwF4sMdtCIIgaCy9Fvo3AKtJWlnSgsCuwNQetyEIgqCx9HQi18xelPRZ4CLcZPNEM7uty9Md38NyvSrTy7pGe/t6Wddob18v6xrt7etlXaO9fV2V6+lEbhAEQTCyRMC1IAiCBhFCPwiCoEGE0M9B0vo5ae8dibYEQRAMJaHTz0HSTGAvM7sl7e8G7G9mG49sy7pH0t+Bo83sZ5m088zsPcNQ1wdykp8GbjGzR4a6vtFOuh5vAQy4yszOqchbiJmdXVHX+mY2oy3tvWb2h4L8qwBzzOw/krYA3gz82syeKqun10iaCEwiY3xiZleU5P8scKqZPVnj3H/A/5tczOx9nbV2eJG0NLAasHArrexaDCg/loS+pMPM7JuZ/XH4Dbp7Qf6u/kxJrwN+B+yOP6x7Au8xs6cr2rc5cAh9N6e8GntdQf4fVbTv8yV1fRc4AngeuBBYG38x/aYg/x3ATcBzwKfM7AVJN5rZuhW/Sfh1eJ2ZHSZpJeA1ZvaXkjLnA5sCl6akLYDrgNWBw8zslJKyCwOfISMkgePM7N8V7ZwErGZmf5K0CDC/mc3NyXcL+de89V+9uayedI63pLpOkjQBWMzM7inI+1NgVWBKStoF+LuZ7VuQ/6SSqs3MPlbRto46LJJmARsAk3GruqnA681s+7J6UtnVgG/jcbSyAmjA/T6Yl5mk7+DX7a/AS31FioWxpCNwk/CZwInARVYg7CS9vaJtl5cdT+d4N7AW/a/DYRVlar+YMmU+AeyH+zjNAjYBrjWzd9Q9R8ceYCO5Ab8CDkrfF8Jv0ENK8r+9bKuoa3X8JrsIWKRm++7Ag8ktC7y6tZXk36tsq6hrVvp8P3AysAxwU0n+menzK8D1+ItpZo3fdBweJO/2tL80cENFmT8Ay2X2lwPOTm28taLsGcAJwJZpOx44s6LMJ3EfkL+n/dWAaQV5J5VtNa7Hwen3/S3tvxa4uiT/baTOVdqfD7htGJ+R1+GCbo10Xa4ElqxxX3wZ+Fz6fmPNuq4CtgJuTtfvEODQgrwnpe184EngrLQ9AZxdUc+dwEJdXAsB2wCnA7OBbwGrDMM1/xnwa+CBdH/cApxQo9wRqV1nANtm75OSMrfgL5bW8/8G4LcdtXe4br7h2NKfeBpwEHAxcMAQn/+WdAO3tofTDXczcHON8tf38Frclj5/gcczgnKhf2Pm+1b4C+qRGvXMzClfWE/rOub8b7e2n6eg7IBz16hvFrBgWxtvKSsziOs+K/2ebF2F9wb+spuU2Z8ETKlZ17vxl/Q3W1vNcrU7LHgHYDfgVmDllFb6Ys6UndF+rYErK8qcByyf2V++htD/Iz6a6ub/Whv4v3S/HwfcCHy3/X5te+5b2y01n/ub2z4XAy6u2b6OXkykDle6Dxdqfe/kmoyJRVQkrZfZ/QHwc+Bq4HJJ65nZzIrydYehg9VvXyrpaPxB/0+mnqr2XUqOysHKh2x/SCqb54HPJDVDmQpknlrMzKZJ2gYfUVTx36RGs9TWCcDLFWWulHQecGba/yBwhaRXAVW64hslbWJm16X6Nsb/6zL+Y66uIpWZnxK1WcqzCfAjvEe8IO4s+KyZLVFR1wtmZpJa1+NVFflfDdwuqaUO2xC4VtJUKFUx/gxYFB/t/BL4EFCmUmtXWy2TftP1krBitdXewKeBI83sHkkrA7kqwhz+LWk+4K6kqvgHPsotY7KZPZTZ/yf+kirjOWCWpGn0f67K1J+fx+/vx/Dr92Uz+2+rvfjLtMVgn/vWc/ecpNcCjwMr1ymY7qWH8Q7mi/hI+neSLjGzr+QUmSNpKeD3wCWSnqTDUDZjQqefhGIRViEckXQVPuw6FngvfqPLzA4uKTMOV0tkJ47u76KdddqXtRZaGBeSLxb86dlySwPPmNlLSfgsbmYPF+SdBhxjZhdk0o43s33y8mfy7I7rU9fD1UgfAr5uZmeWlFH6DZvjPZmrgLOsxs0m6XY8umrrWq8E3I6/aCxPeKX5jafwuZfP4XMCfzWzr5XUMx3X+Z6J67T3BFYtK5PKfQlXH70T70h8DDjNzH5UkL8rfbGkm83szZnPxfAe8bsK8k+qqOe+gnLvAK4zs+fKyheU3RD/b5YCDgeWxHvR15WU+TF+/abgL6ld8XDrnyspk9s5MbOTS8ochqtYBvxuSWuY2e0F5ZbDX8wAf7EahgeSvoF3ILbCVaEG/MIy848F5dpfTL/PvpjMbJWK8m/Hr/mFZvZCVTvnlRsLQn+wSJphZutLusXM3pTSrjSztxbk/xz+kvgnfb3aXIEzXEi63MwKBYakRYEvACuZ2T5pNPN6MzuvIP/duM7xz2Z2aEqbaWbr5eVvK/sG/IYWrivPfWCGgm6EV3pIPg68C2/jRXg018KbW9J0M9ugJVRT2jVmtlmNNr4zW5eZXVJVplMkXW9mG0u6DvgA3nu81cxWqyi3Ca76m5v2FwfWNLPrC/L/Gp8MfBzX/1+JWxjVnlzslDSp23r2rrASa6ZMmQXpGxHcaWb/rcjfsVWSpJ2Bo4HL8P/2rfgI4XclZeYDNjGza9L+QsDCVmH0kfJ2/GKS9ANch39N1fkL6UQXNNIbru9aKrO/NHBEjXJX4xNoZwOfxSc/7yzJP5uSCdic/B9Jn1/I22qUXyazjcd1fIXtS2V+iw9RW7ryRSjR7eGTe/MDP8UnIpek3kTuD4DNOvyfPoAPoZ8GngHm4iOSuuXXTv/TZ4G1a+R/FTAusz8OWLSizBW4WufXwHeBA6iYO+jynt0En2T+F/ACbn1SeS2Ab+A96A/iQ/+HgMNrlLuRgRPHdf7n1wKfx0dYL9b8bRsA56R7a54ufBiu4RbAfcDl6X+7B3hbRZlZ6X5fFfg7Psq/oKLMTcCymf0Jde4J3Hqmk9+zTNlWUXYv4IIko44GNuj4eg71HzScGzmTgDVv6A3xyZUVcAuCs/DFW4ryX4qb/NVt16fS58E5W+XkW7qJ706fd+GT1G+pKDO9/ZqU3aBt+T6KT1LNqdG2jm+ylHeNLv/j/fBJxcPSdgvJqqSkzHVkJvrSf31NRZlJuCptifQ/fR9X71S1ryMhDkxPgudG/GW0N/CtGvUslP2Ov6QrLVjIefFTPtH8EXyO7BrcGu4rwKY1/6s7gffh+utJlFhAkV78mc9nqNkhAGbgo9jW/uqkSeSSMh1bJTHQAGG+9rSCcofiL+dK65uUP/u8v4Srdx5P3++peY5lcOusabgqqP4z1knmkd7wnkT2YViEGuZvwE510jLHTsD10AfRWY998zppQ3Qtrkm/v3Vzr4LrIIvyf6ptf308ymnd+mrfZJSYMNb8j1+V2X9VmdBKefIEXalFQzrvfJn9ytFBypcnxI8sy9/6Xdn/rkY9AzozeWk5ec7Ge+wLpG0/XFdclP8x3IJnb3yStZP/6qpu/+dO74k6aW3HO7ZKwjs1F+Gdoo/iVkPfrdG+ubga+L90MLLFTT23z+xvh8+71bkmGwHH4KOYP3RyPceE9U6G3wDTkgOL4ZNohZM5GQ6iz5KkLK3F/WlbMG11+RE+4VmV1g9JCwD/A7wtJV0G/NzK9ZYH405ZK0o6FZ80/WhRZjP7eaprWbyH+yhuV12XVXGb4MlUr2k8XdJvcQuDrLVFqTdpQvQ54JC+q6LMs1krrjQx/nxFmWnA1niPHfwFejFQqdM3s9mSxpnZS8BJksr0q88lffSsNOH8EP7CyUXSa4CJwCKS1qXvty+BW/NU8Wngh8DX8WdkGn3Lj+b9lvGS1sLvvSPT3NCdZrZHjboOlvTLVEfl/5z03zeb2RtrnDvLdEknAC2nvt3x3n8ZHVslmdmX1ec9LeB4qzHfYGaLV+UpYEMz+3TmPH+UdHhZgeSo9gFc2J+Bq/w68p4eU0LfzL4r6Wb8YQX/wRcV5Ze0HbA9MFHSDzOHlsDNo4rqObSTdknaFBcWEyR9oa2ecTVOcRzeK/tp2t8jpX2ipI2XyL0vN8Fv0P3MrHCNTXnsoO/juttH6LOKKX0Au7zJlsDN7LKWJob3Qqs4CTczbD1sO+IjrzL2B86U1DJdWx63OCpjYTNrCXzM7F9pcryKjoQ4/l/Oh89PHICvHPfBkvzb4C/vFfD/q8UzwFerGmdubbJrVb4WkpbA74VJ+At9SapNclvsjXcEFsiUKfyfzexlSTdJWskqLOHa+B9gX3wEI1yv/9OyAmb215S/tX8PcFRVRemFdTa4BZ+k3c3s1LIyyVptd3xEcbikFXFfhEIT28Rjkr6Ov4wMV7U9XlHmHlz91s16ut7eNFQYMySTqo3wi1RqUiVpbWAdXLEmCrUAACAASURBVDecNZ+aC1xqBRYK6tBuPplObYH3LH6WOTQXH3rdVfKTkHSTma1dldZ2/G156VYQg0PSTcA7gD+Z2bqStgR2s2qTzU8DvxvMTdYpcr+MVm/rCjO7sUaZBXBTTwF3VIySkHQ1ruvNjg5+bGabVpSbhL80F8CF+JLAT81sduUP6zvH5mZW6nsg6YNmdlbdc2bKLYxbMrWHBMgN35A6UVel7Qozm9NBXfOs4Too82d8ju0vwLOZ9g1pfBt1FiJiCfylMhGf17gk7X8ZVxPuUFHXcfhL7x1mtkYypb7YzDasKLcMPmJ/Gy5vrsDDlDxRUe599GkFLreCuEqF5ceS0O/GpCqVWwAf1axkZnfWqKdbu/lJVmAPXVFuJj7H8Pe0/zpc0BaqheRxhbJt3Aif3Cp6MbVMFG8C1k29rr+Y2UYF+d9gZneov2PcPCzH4UzSV9JoLDemkJU70yyMvzRXpc+NvXA0llN+M7ynmvWr+HVJ/g1xL8h+owNrC1bWLXI/j51xQXKhmd0q6T14b30Rq4559BrgSOC1ZradpDXxHl7pqEfSmbj36Yfxzs7ueAiN/SrKLY6bJf+rLF9bmV8Ax6Zedd0yb89Ltxx/BUlnmNnOKoiXZCUm1OrAN0fSuXhoiGtx0+SlcbXufmY2q8Zvmmlm6ykTy6qq09ZWfrG6113St/FnvTX62A2fNzqoTnkYe0L/JuCdrd693Dv0T1UXN6k2vgcsaGYrS1oHf6PW7l2owm4+056vMLCXVeWctRWu1rgbf5lNAvY2s0s7aN+K+KTTbgXH/4SrSo7CvUQfwXWKuTpsJcctdeBwphTNUd050/wWnwi7Ep/QutfM9i/K31b2FHwiexb9A3IVvmRSuY5GB6lMngB6Gp/gPcLMHk/5foWrcv4CbIybHG4KHGhmv69Rzx/xe+JrZra23Mv4xqqedUvwqM+pawHcl6CoM/BGXFe+DH4dHsXjPt1ao42349f9HlynXytonWo6QEla3sweUoHvRlkHSx345rTlGYdPbq9kOQH7Cuq6Hlfv3pCE/wS8p1/1Yt8Md8pazMxWSpqJT5nZZ0rK3AysY2YvZ9p7Y9U174f1YPZ9qDa6N6magQ/D68ZL6dhuPpW7GB9a344HdTsR+E7N37YQ7kCyNt0Hlyq8Fvgk4Hz4HMNeuOdqqU1wKrdwnbSh/G/x3nqlpUom/+3UN5d7R/r8QN5Wo/x3cbXBm9J2JO4/8r9krChwq5H5WtcLnzB+TQe/qRVjJXvPVsZYIVlw4aqCN6b79+6S/NcAW2b2t6CGdVHKOylvqyizM/4CPBn3kbgH+FBFmQHPUNVzRQe+Oe33Wif3Xsq/O64W+ke6H+6kxDowU+56vGOQ/Y+rLIxuzj63uIzqyDdiTE3kAhdKuoj+YWr/WKPci2b2tFRlBDKP7BD/RfzG/HiNcq82sxMk7Wc+XL1cUmFY1qRieMDMHjb3HFwHVyXdJ+kQK9HttalQ5sPnLm7KyTeXgT3T1oX4pjzO/tfMbFpBVdcw0PooLy1b5+rAlxiobikb8fw3k+/FDv4rcAH7GnxStYq3A3/Gh/zt1Jls3tzMNs/s3yLpajPbXNJHMukvWOqNmdm/Jf3NCkJkFPCspFfTF/NoE3xEUcXxSaf8dVwQLYY7ehXxKsuMKM3sMlXHE2rlvS+1rWURVoev4SPMfqN1PJR5Ee/EX6pZtstJy7I/3tH5PB4iYks81EYea0t6Jn0Xbjn1DH0jl9J4TGZ2qqQZuGoIYEer6bVuZg+03esvFeVNfBuPT3Vpat/bcEvE2owpoW9dmlQBt0r6MDAuTfB8HhdcRfXUCpaUQ0twPSSPr/0gboVRxM9JlkhpYvYovAe+Dh5S+EMlZadnvr+IR24cMDloJeZkaWj4Rlw/+Ma2Y4MxHTwTn9D+JdU3cYvBPHjjgb/Kg5plTQcHqO8s6XTNbO+a7WpnMUkbWwprIGkjXLBCf4uwN6SheOv3rJL268bt/wIutFeRTzpPoPx+aJlEPmNuoHAFHma5irvlsWNa5pAfwTs5laQJxWPoswibhI+61iopNp/1V+c8TsEKfpL+B4+jtErmWgIsTsnzm5hsZi0nur3T+XbCe9f9MLM6FnZVLIqPog03/63DA0nFY3KLsM/j168QM5si6TJcPSbgfzvsTIwt9U7OUGccsHuNfIviw64bcGF5JPlqi9WAc/Ge4xRgYofteQ+uRnoj7tU7A3hvSf6bMt9/QmZtADoMlzrI6/ipnLS90m+Ymz5b21Qq1CBUeEsOQ/vfnrdVlNkPf4EJfznNBN5Vo64N8Ynme4B78eH2RrjZ5s6ZfLmqD2rG7U/nmB8XoG8EFqhZ5ooOr93SuF3/TNzh7AfA0jXL3oTPD92Y9rfEO2JlZfIcoHJVNelZmpyexez1q6OW7Mq5rcv775vpnjgE9869CQ9KWFVuPN7h+if+0vwNBeFfcLPawq2T9o6JiVwN0qSqg3quxPWMV+Du5ZuaWemKPzXOub+Z/V/BsVvxSZkX5WGS97FkcinpVstxYimyZKB+D7Ij1IXpoKRD8Jv4HPr3vEtN0XpJy7pCHmJ6X1wFcpLVCECXyi+JzyPk+iwkNeSFwB/N7I4O2jXY5RK/gTum/Zb+JpFDfu3VgUWYpP1xPfssXLWWNcktHa2rgyBy6vPN2Rm/Bi2WSGVyrdUGQ5rQXtfS6m7yldtmmtkaQ1hH67nP6oIMHwEuax2MVsaKeucU+kyqPoEL+wWBHazEpEopZnkRNnD4v7iZ/SJ9P1puSjlYvoAv4pDHFFzv/xj+oF4JIGlVivW3Q76mbRlmdpY6XwquZb3z5eypqKdu6Bh1Fxu/9fBsjwv7m1RjIkH9ne9IRZ7GRzfZe3EvfDWkQ9Icx/X4S2CalZvn/Q4XjK1ztT/kVXMOLXv87HKMhde+y/mXFk/JQz5fAZwq6RGKnR5XwEcRb8BHR9fgL4Fra9RzHP3nkJ7NSWvxID6afx/95+bm4n4Vw8G9+LPRiqu/EO7MWIrcS/hzDLz2eWrJflZbkibjcxpb44YEtRkrPf2uTKokPYqHE56CP3T9Hmprsw1Ove3dMvlOxe2dlfJ3/BKQ9ICZrVhyfBPcRvxiM3s2pa2Om3HVqk/SeOBxG4Y/UwWLeZhZnYntnqD82PirmVmhB6s8lMdEYGXcYmoccJmZrV9UJpU7LdXR8pN4N642fAO+rON3c8rMh5ttbodP9j2P/995ed+PGyisiqsap1hnjl8LW9t6wnlpmWM34fMvM8jMv1gNf4U04fs8rpPfHVfHnGrJbLWgzIL49dsMN2HdFHjKzNYsKTPLzNZpS5sXErugzAJWwwR3KJD0e1zt1wqxvTXu7PYIFPunpGt/Aq4amucF3S6X2sqshk+Gb4zPp5zc6e8cK0K/X9z39v2ScuPwmf/dcHPI8/GH6LaC/GV28Vaz99N+zvvNbKWCY9Px3s4fcYFTuvh3KrMJPuH7BG6VcAquG5wP2NPMLuy0jRX1dbSYRyrTUaz/IWhjx7HxkyBeBzdnfEruHbmCmd1cVCaVuwj4YKu3nq7H73CTwBllwitzjvHANlbi3p8E6g74C+DVuIVVnQW6BzwbZc+Lkj171Xlzyo3D7f+3rszcv9ySuKDfPH0uhZvrFk6sSzobd8g8LiV9Bjcz3bGkzOa4jn0S3otuqT/zPHLzLNzmUTFibE04z48L7pdoi/tkBf4pSmsmlJ07k/eNuLBfCzcbnmIe+6ljxop6pyvLjnRRLsRNPRfChf9lkg6znJWOzGzLbhpXctOI8pn8TXDd5rbAoZIexye5/mhmfyso82Pcq3NJ3PRwOzO7Tr7QyRT89w4lrRu4k6XgTsJ7ji2hOwfvhQ+L0KfzeDjgAmeWmT0rN7VcD1c/VLESHlK5xX/xidnnJf2nPbPcu/oHqb6XcXXGAWUCP/Fv+tYjWIkKk0h1aG2VXnLgy25+hg7nX8xXa3tO0pJWb8GQ43GBNRcfdV8DfN/qLdbSURC5xAm4OqffCCYPSxZu8kVNHsY7Uq14OmXWb/PjqpWP4b4H8+F29ycBX63RA/+BpINx/56q5VVvwrUW5+OGAxtltZFFo4k8xoTQ72SSop0k7N+NC/zJ+M1TJ/BXbazLKHvmYQYuSxuSlsdVAEek3vG1NtA7b34zuzjlP8zS0nTmIRO6+wHlnCdfk/No3MLDcDVPGauY2S6Sdktte76OvnwQdBrUDLzXuLbcC/IruJD4NW75U8ZpwHVy133wSckpqWeeF47gNNwy6/1pf1f85Zzbw1OKiYQ/2H8CfmBm0/PytlEUqG0u+YHaZtB/YrCb+Zd/434Kl9B/0jhPAK2E67rvwp2Y5lC9XnLrfB0FkUs8bWZ1fHiybNPW8z5O7m07QA2XOBp/KaycmWReAvf+Pxr3FSjjTfi9+w76B6zL0yjkxk7qhjGh3ukWSSfj5m5/BE63Gq7lo4WkftjU2mzvs0P1btVeg2hTraXg5KGGt8Lj6q8nX7puynBYTmTqnABgZo/WzN+Kl/JN4B/mTnV11Ybr02d9clWZUM4bwku6zsw2Kcj/Mj7ReRUuAPo9oFU9OnUZqK0b1GG4jfTiXwsfAW6GP5tP4J2bg3PyDyaW01H4PM3ZVPeiW2WuwV/Qp6f6dgP2LVITSroLWL19Li2pvu6w6qUt7wDebB2sbzsUjIme/iDYA++BrA58PtPZrOVpN9wk3e6+uGXSiXjv4K34zP8X2gV+oqXqyqq5SPt1vSI7bWe/YGaSsJJgZrgutT3Wf7fOUGXtEh5U67P4759P0ovAj6zcughgrqSDcGekt6UHdYGaVS+CO0GdJGmCpJXNQ/fmcamkA+kTJLsA57fUKzlqlK6ukzJWRWqzMEr1fL8t/zxv8LS/J8kbHPcXqTTxNLOTO3nZJuF4q6SncNXV07g12kb4/9hOy1GpzkinndaLdoNsE8jvRbf4MK6K+0HKe3VKK8LaBX5KfElSnd70TficRuXi60PJK7qnP1jkNr+XJfXEB6zeIiCdnP9i/IZeHO8Zn4RbhbwVdzrbYijr6wZ1H8zs1fTF+r/OhiE0s6QDcJPLfVpCN+nQj8MjWx5bUvY1+AN9g5ldKWklYIuKlxlJB7sBPjG9eprnONP6h2bI5i/zbs2dWOyG1C7wAHIb4v4s4OqnK8zsE235ZwJbm9kTcm/w0+nzBl/DzAq9f/NetripZuHLVtLn8d795vg8SMtc82p8IndADH9J81sHkVZ7jdxq5+z2eybNEe1sFQEd5Z61b8atv0o9yYeSEPolSPoJvqzgTHzF+yFVnajPQUjAfZax8lGOmdpIIHc8WTOvR1NSZpqZbVWVNgRtuxGPuvpYW3qtKIdd1jkLWBd3vmmF0S01H+wlqSPxQevvyHSmmW3blm9e6N90nz9qZoek/dJ7r5uXraTvk2zzzaxOjKR2VeaPzOxzdcplynfkX5Lum08y0G6+aC2Cibj66Hn65kg2xEeC7zezf1S0L3f+yGpYaQ2GV7p6pyMkbYyb8D0KYGb7Jp3vfvR3dhkqXkr1mNxBK0vd1YuGm9rBzOQx8RcFxsuDfmUtSF47DG1bIG8EYWaPykMKF6J8h65/mdmSFXW+kP6vViC0UishdbcU5mBoty56ARdi7YzL9KS3or81TJVc2JO2l62Z3Z16uBfjMez7YWYDVE41yE7+546kCgsW+JdUFDsXd5D8EzViRiWhvrGkd+AvF+GWd0XBC9vLX66aYaZbqMNFcvIIod+f48lYVaTeyWTc8eYcfAg8lLxO7jWszHfSfrdB34aa2sHMgE/hFguvxXs+rYf2GXyCbKgpmwCrmhz7MTkOXTXqPEPSz4GlJH0St6oos2bqeClMcJPKOnr1HE4B/iJfbtJwq6E8lVU33uAtun7Zdshg1BCbWZ9/yaGSjqHaam9RMyuL3JmLmf0ZN5/uCA1cFOpHkqoWhToFXyRnGzKL5HRUb6h3+pB0m5mtJbe//RW+zuunzWOKDLllTNHwrsVwD/Pq0M0QVNLnLMcPYqiR9BI+US/6CwjhVkaFAkhdOHRlyr4TX/9XuIPSJSV5O14KM+W5C59HOQnvPXaiXmtZF0HJcpPq0hu87FkYyudE0nPAbPw6r5K+Q58hRplH7vVmtrGk6/C1Eh7HY9UXvtglHYGvJXDBULS/CnWxKJQ6XCQnj+jp9+cqSdNwdcbiwFZJ4L+dNi+7oaBCcL56qOvrlGTR8g3r0OsSeFjS4mY2V77w83r4qlJDEctoHja4kLjdOHS16r2E5HKv6sWzX5K0ivVfCrOOJ+XquDv/x/Ae4G+BX1mx016WWfjvaVlb5S5EbsnHoy2tzvmzzpJZhtqCbDABy/L8S35RXoT9gK/Knez+y/Bb+dUOM52hpRZ8Su6l+zD56rtibBhCjY7lDe8hbYS7b9+Ah9C9B1ivB3X/HXce2wj460hfi9SmqcCSHZa5OXMtr8TDCVw/DG2bjpvXbUuHq3ml/3cRfL7hYNyhadWS/Evgi1X8mL5e/mdxE8dzS8ptBdyPD+Evx4NzbdlhW7fEHZqeSufYtCTv5/DYVLfh9v630OHKSq+0DXcK6+ge7lG7aoeZzpT5BB4O+2348qqP4NqI2vWGeqcCSROspsPPENV3AO7Rt7dVmA/2qD1n4KaXdbwuW2VaQ9Bv4+Z4pymzaPQQtm1++sJYbIn3lKrCWHRbV9eLZ8ud2rJr8Q4I15BT5tW4D8EeeLz1E/AX8Dq4NU7unI+k2cDGVhL07JWMpG9ZCrQn6Z1WonqrOM8q+JzPbpYT4nyQbVwVWM7Mrlb/RaGexAPWVUboHFT9IfRHjmRe90nrW3ZuE3zt0KPxBT12Hsn2AR17XaYy5+E9061xk9fnccuEUj32YFFfGItt8UnZAWEsVLweAQBWoCdWh5FeNfi4+H/DJ+1OMrM5bcf+18y+U1DuUlxPXGrfLunHwGlmVrUC1ZhCJR7rNcouTxL0uP38t3E7/FuGuI3n4bF5bm5L3wA42MzylvJs5dkPn+eZi6ur1gMOtBSapVb9IfRHjqw9dLIpPhpfX/Nvkm4wsw3Lz9Ab5ItCrGRmd9bMvygueG8xs7vSw/SmTm7MwaLiMBarAcvhwauyTAIetIIwxu0CpEqgyEM3t3gvfaGYwfXEpSZ2ktaz7kJ5n4CPKs6nv7VVu0fufriAWx5fbGRK1YhlJKl7D3Yj9JMV1m543KIz0nZu0WhqsKhggaR0bF7nouD4oBb/gZjIHWn+k3rSK+LrY65rZv+QB22qNak43Eh6L65uWhBYWb54+2GWY7IpaQkzewafzLsspS2DC59uXOnrtnECvqDEmvS3Xc6zaDgW72Xdl3OOY8lfMB06jPRqmVDBSbXVaXiFY9LL8kw8blRuOPAc7k/bgmnLxcx+gEd5nIQL/5PkNuBTUn1Dqh4bDJ3cg8Cy8jAUynyfR/vLL/ETXG33YUtxlFQvjEK3lE12V62v29XiP/1OED39gaSLuDvwOjM7TO6i/xozq3Lu6LSeVYEDcZvyv+OqiSvwic9TC27QniJpBh6v5DLr80DN7Y1IOs/M3iMPPTBgaTcbopADOfVejPdWv4SH4d0L9zAdYHM9mF7WINrXlRmjPFTEzni8niWA35rZEUPdvkx96+IxoN5sQ7NY+JBQcA/mekGrLxxFLmZ2aE6Z8cBOeG9/Obyn/1ErWfxoMEiaAvzZ+lbpa6V/HFfr7lJStqvFf/qdI4T+QCQdh3vEvsPM1pB7l1483OqW9NBtjS80/afhrKsu6rN3vrHqgas4z0SrcEsfRBtnmNn66m9zf7mZDfAxkDTbzFYtOE/hsUG2b1C265LehId/3sXMCnvvKe+l5EekzLXjltt5b4v39rfCrYOmmNnvu23vUDNU92DNulagT6+/KHCOlazA1mUdy+HOni/Qt6TjBvhI5v2WguAVlO1q8Z8sod7JZ2PzsLs3ApjZk3Kb7mHF3InmRlXbfveSWyV9GHfbXw1XQ3Uz+XctHiJgOGjZLj+U5kYexPWzedwg6ZMFvazKJQLrIukP9AnfrLc1UB1US9IaeA//Q7hV0unAF2tU/aXM94XxyJkDJnXlDma74WtN/CWdfx9LTlqjjKG6BytJk+bfA74n6fV0Hse/Th3/BDaTr53QGnWeb+7ZW0W3i//MI3r6OcgXTtgMj8C4noYpgFfS3e+LD9em4maR++ILWswysx2Gsr5uSJOyX8Nt08FNIg+3GmaHbecpXSt4MEh6D+4PsCIeT2cJ4FAzm5qTt+teVodtGpS3tdyTdApunvngINsyYNSTOjQ/Bc6y7sI99IyCe/AIq7G86CsNSTfjap0349ZdJwAfyBvVFp4jhP5AJO2O97LWw00oPwR83czOHOJ6urb97hWSdmr/3XlpNc5TuFbwSNDWy7qtZi9r1KO+ZRDBvTvXB35oZq9vyzfkfhPB8KNBLP4z7xwh9PORrzm7FT4ZOc3MOgpqVLOOjmy/R4K8G6roJlPBCkf4Ndyr3cJlCNt4Mv6ifCrtLw0cU2UWOZpJaoxvM9AiqXQyvG0S/UXcm/wwM7uqLd8c+i+r2I/RYETQQr4c405t/+/pZrZNjbLfAc4wsxmSjjWzA4a5ucOKpMvxBYo+hq+78SiuFahtgBA6/Rwk/QC3lBiOyJBZ5oXXNV9t557RIvDlC8hsD0yU9MPMoSXI0REnyswyh81kE7c2mbfeapqDGeu92JPw8BDH4t7Ge9PfGioXq29bPo6SRb9HGeNz/t9la5adDnxZ0lp4WJVC5GtOfzOzPw74tZnt3k2jh4ld8MV/PmZmDyfLwqM7OUEI/XxmAl+XRxw8B38BDIfQ6sj2u8c8iOu830f/Cc65+OLjA7ASL91hZj5JS5vZkzBPxTGq7u00f2MdvNQXMbNpkmTuU3CIpCvJX1YwW0/d+P0P5ZkvjlJeViZonNy3IFdFIenTwAXWF2DuPNyE9wl8UfYyVpJ0kJl9Wx4640xcFowakqA/FdgwzWX9xToM1xLqnRKS8PggPoO/klUsdPxKRKN8yToA+fquBwGtOOQ7AUea2Skj1ypH7lp/Et6rFh407WNmVmopJOlqfPj+OzxW+z+Ao9p18znlfonH72+9gPcAXrKByyWOGZ2+pG3xtS5ak99vwy2NLsrJm1WZLo17Qp+Nj5iuN7ONSuoRcCoepG5LPIZT4ZKbI4EGxuB/K1AVg7//OULoFyNpI3w4tSMe9bIwJsYrDZXHqDEb5jg6nSJpTdyBpzUH89cRbhIwz9piXzNrLVLyFuCnVTbm8oXLb8cXzj4cV6sdbTnhkNvK1Yrfr+4XaRkR5A5UrTWXr7WCNZcl3YFPXo8Hfo/P7fwmHct1zJOUnZ9aAPg5vnbvCQA2xCHBB4O6iMHfzqgaAo8W0uTPB3Av2TNwE8Wnyku94nhPTppw+/dSZ5VeCRSlsA9pRPYwcFqv21CDuS2BD2BmV0mqVPGY2Q0Art3pKIRDrfj9o+TadMJCuIpmfmBNSZjZFTn5jsFDDo/De+wLJL33XkBR3J5j2vafxCfQj8E7PrUXKOkB3cTg70f09HNIesHfFfUmmoY81smH8ZAA9+C23T8uyd/1qk8dtqs97MO8Qwxj2IdOkHQs7tk5BW/jLrhQOQuKe5GSNsV7mouZ2UqS1gY+ZW1RQ3PKbYVf97tT0mQ8TPelg/81I0PqhO2CrxHQWjvarMDBLU3Agr8gvo0vLTgTOGCsP9OSjsZt9KekpF3w9RJqL/MYQj+DpDeY2R1tw715jKZh3nCTJrFb7uiPk2LbmNmkGmVF36pPG6Wyv7JRFMSrV8jDIhRhVhwe4XrcP2Sq9YUeKIsbtCHwQJroWwhfr3hrfAR04Bjs2c9D0p24hVZHDoFd1PMt4LttpqFfNLOvD2e9naL+MfivMLNzOiofQr8PSceb2T4FD2rhA/pKRNLLuJfrxy2FG5Z0d6e95+QE9Rs8auhNuAC6dhjaOxEPjzxPZVkw/B8TKD/eTOHaupJmAlub2ROS3oaHVfgcHqdlDTP7UM8aP8RI+iNup/+vYa5nwOR2p45PvSTNczze6Ug6dPoZzGyf9HU7a3PxloedbRItq6VLJV2IC5FaIVw1cNWnz5FZ9QkY0jjlmeH/X+nTXxsesXREkPQRM/uN2kL7trBq56cHJG0GmDzu0+fxid0ixmV687sAx5vZWcBZkkaFd/cgeA5fy3ga/dcIKFy9rUvGSVqoNaKQx/BfaIjr6Ar5AktH4fMah+MhGMbj5sp7mtmFdc8VQj+fa/AQDFVpr1jSkPEcSa/CrZcOAJaTRyA9x8oXRLkWvyl3tP6rPk2X9LNhaO6OwOuHe/jfIa31ELp1gPo0HkhrIjAHuBiPy1TEuIx57VbAPpljY/05n5q24eY3wDR5+GLD1ZMj5XvSzo9xA4olcRPe7czsOnnkgCm4l24tQr2TQR6/fCL+53+Yvp7tEsDPzOwNI9W20UCyktkJD/FbqOpKDkWWvs+HT0Y+U5R/CNrVk+H/aEbS13AP6sfwaKbrmZnJ12w42cw2H9EGDhLVXzlrUCOs5BOwddq9JM8XYCRQ/1X2bjezNTLHOvK5GOs9gKFmG3xV+hXoH5dkLhVmik0gqQ9+nrYyTk0WUC/h3rxLSvq+mXXkLt4BvRr+10b9Q1cMoKhtKo5fVFrOzI5Mv395PCJs6xzz4eq1MYs6WzlrsCOsG3FbfUvfRwsvZ74/33aso5579PRzkPTBpA8NuqDVK5FHK10fX8pwRpVD0iDq63jx9uGmrU2H0hY+oaht3ZZ7JaMOVm8bZD2D9nYdLiS9BDxLCteCd3RI+wub2QK1zxVCPx/5Yhxr0T/C4WEj16Kxg6Tb8Enb04Afm9nlZZYnQ1TngsDqafdOGxhrZsTodPg92HKvNAosmYqWS7zYzN6Vvh9kZt/uoJ5Be7uOBTry5GoKabJxF3xYLFyPXWmfHszj58C9+FD7CnmAPUnbRQAABplJREFUrOHU6W+BB9P6Cb4wyN+S2eJoodueVfTInH4rZyUVWNHKWRMy33fqsJ5Be7uOBV5xP2iI2MzM9gSeNI9EuCm+KlNQAzP7oZlNNLPtzbkPD2A1XByDLyj9djN7Gz43M6oCZQWD4nP4qPs/uKXKM8D+BXkH86K8UNJFkj4q6aPA+cAfB3G+UUmod3LIDCevw2PwPA7cag2MstktvVSP5Q31i4b/vUIeX6f1cC1Kfx2sWUHY7G7LBY6kp3D/jJZOvp+vRlHohkz5QXm7jgXCeief8yQthU/qzMQfwl+ObJPGDkk9tijeu/8lHk7gL8NY5XRJJ+C+AQC7M4SLnHeDmXVlPdJtuVcikv7PzPZX/0Xm51EgwLPrSn+v0zrN7Gw8FDOSxkna3cxO7fQ8o5no6VeQ4pgsbGZPj3RbxgqtXnbmczHg7NYE2zDUtxDuuDSvh4aHLx5NzlpBh0ha33yZw9xFv61icfkO6lkCv38m4k5gl6T9L+NLEe5QUnzMEUK/gOQCP5n+sVw6WqGmqfRSPSaPqHiymX1kqM8djA6SV/jzZvZy2h8HLGRmz5WXrH3+c/HIp9fi3sxL4z4B+5nZWA9hMYBQ7+Qg6RRgFTw8cDaWSwj9evRMPWa+tvAESQua2QvDUUcw4kzDvWRbHteL4GEpNhui87/O+lbb+iXJq9lGyXrVQ00I/Xw2ANbsNHpd4JjZ4enrWZLOY/jVY/cCV0uaijuwtNpRFdQsGBssnA2xYWb/krToEJ5/nk9H6kTc80oV+BAmm0XcCrxmpBsx1pD0lcz3nQDM7D9m9rQ8Vvlw8SC+APZ8uPv94sBiw1hf0FueVWaNC0nrMzAUQevY/6XPP0ia2r4VnH9tSc+kbS7w5tZ3ScPmXzJShE4/B3k8/XVwi5NsLJdSc6+mo0zscbXFIW/fH+J6dzKzM6vSgrGJfIGY0/GXO3h8oV0sZ3H5Xk3+jmVC6OcQN0x3tLnJ9wshMJwhBfJeKMP5kgl6j6QFgNfj1ll3VIXZGO7J37FM6PTbSDfHN8xs68rMQTtW8D1vf9BI2g4PJzyxLarlEsCLQ11f0FuUWQLSzP6bVDwfBO6TdIiVLwE53JO/Y5bQ6bdhZi8Bz0lacqTbMgZZO0cv2tof0oiIiQeB6cC/cWes1jYVD8UQjG1+DrwAkGIpHYVb0D0NHF9RdsDkL+4w2Hiip5/Pv4FbJF1Cf2uQEYvPPhYws3E9ru8m4CZJ5wDPphf2vKF8L9sSDAuDWQLyWUnrmdlMKJ/8bRoh9PM5P23B2OBiYij/SmQwS0DuD5wpqd/k7zC0ccwRQj8HMztZNZdnC0YFw23HHYwMU4DLJT2G99KvBJAvAVnq92FmN8jXj609+dsUQqefg3x5tlmkxYYlrVNi4xuMPLXtuIOxg5kdCXwR+BXwloyzZOESkJI2lK91TRLy6wFHAMfI13huPGGymYN6tDxbMDR0YscdvLKRNBPY2syeSJO/p+MviHWANczsQyPawFFAqHfyeTF5kWbT4u04SomhfJBhMJO/jSDUO/l0sjxbMMIk/f3/4lERbwEmS3rPCDcrGBnGSWp1ZrcC/pw5Fp1cQugXkV2e7TR80mi/EW1RUMZJuD33pml/Dq7HDZpHa/L3XDqc/G0KodPPIWK5jC0kTTezDdrCQNxkZmuPdNuC3iNpE3xe52IzezalrQ4s1rLbbzLR08/noJppwejghWRiawCSViETKC9oFmZ2nZmd0xL4Ke1vIfCd0HFliFguY5aDcfPaFSWdCmwOfHREWxQEo5RQ72SQtDawLnAo8M3MobnApWb25Ig0LKhE0quBTXDrnevM7LERblIQjEpC6OeQcf0ORjFZh6w8YjgfBAMJoZ9B0i3k2+MLMDN7c4+bFJSQFrspwszsHT1rTBCMEULoZ5A0qey4md3Xq7YEQRAMB2G9k8HM7svbgBWAr1SVD3pL3pq8mf3hXJM3CMYsIfQLSEHWvivpXtzR544RblIwkF0z39tNarftZUOCYKwQJpsZkgPHrsBuwOPAb3EV2JYj2rCgCBV8z9sPgoAQ+u3cgbttv9fMZgNIOmBkmxSU0NM1eYPglUAI/f58EO/pXyrpQjwsa/QYRy9rS3oG/48WSd9J+wuPXLOCYPQS1js5SHoVsCOu5nkHcDJwjpldPKINC4IgGCQh9CtIq+3shC/KEXbfQRCMaULoB0EQNIgw2QyCIGgQIfSDIAgaRAj9IAiCBhFCPwiCoEGE0A+CIGgQ/w/ky5N7ZqpSPgAAAABJRU5ErkJggg==\n",
"text/plain": [
"<Figure size 432x288 with 1 Axes>"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"# plot results on a bar chart\n",
"genre_plot = genres_analysis['quantity'].plot.bar().set_xticklabels(genres_analysis['genre'])\n",
"plt.title('Albums Sales by Genre')\n"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Answer 1:\n",
"It looks like Rock, Metal, and Punk are the best selling genres. Our list of choices included albums in the punk, blues, pop, and hip-hop genres. Based on this data we'd suggest:\n",
"\n",
"- Ordering *Red Tone*, Punk, as a first priority. \n",
"- *Slim Jim Bites*, Blues, as the next priority.\n",
"- *Meteor and the Girls*, Pop, as the last priority if we want to ensure store variety. \n",
"\n",
"The hip-hop album sales aren't doing great comparatively so we won't suggest ordering that one. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Question 2: Which Employees are Performing Well?\n",
"\n",
"To answer this, we'll write a query that finds the total dollar amount of sales assigned to each sales support agent within the company. We'll include attributes for that employee that may be relevant to the analysis like how long the employee has worked for us."
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" name hire_date reports_to total_sales\n",
"0 Nancy Edwards 2016-05-01 00:00:00 Andrew Adams NaN\n",
"1 Andrew Adams 2016-08-14 00:00:00 None NaN\n",
"2 Michael Mitchell 2016-10-17 00:00:00 Andrew Adams NaN\n",
"3 Robert King 2017-01-02 00:00:00 Michael Mitchell NaN\n",
"4 Laura Callahan 2017-03-04 00:00:00 Michael Mitchell NaN\n",
"5 Jane Peacock 2017-04-01 00:00:00 Nancy Edwards 1731.51\n",
"6 Margaret Park 2017-05-03 00:00:00 Nancy Edwards 1584.00\n",
"7 Steve Johnson 2017-10-17 00:00:00 Nancy Edwards 1393.92\n"
]
}
],
"source": [
"# SELECT employees, their start date, their superior, and the total amount of customer sales they supported\n",
"total_sales_query = \"\"\"\n",
"SELECT \n",
" e1.first_name || \" \" || e1.last_name name, \n",
" e1.hire_date, \n",
" e2.first_name || \" \" || e2.last_name reports_to, \n",
" SUM(i.total) total_sales \n",
"FROM employee e1\n",
"LEFT JOIN customer c ON e1.employee_id = c.support_rep_id\n",
"LEFT JOIN invoice i ON c.customer_id = i.customer_id\n",
"LEFT JOIN employee e2 ON e1.reports_to=e2.employee_id\n",
"GROUP BY name\n",
"ORDER BY e1.hire_date\n",
"\"\"\"\n",
"# display the results\n",
"sales_analysis = run_query(total_sales_query)\n",
"print(sales_analysis)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"It looks like only 3 employees are involved in sales. They all report to Nancy Edwards. To get a better idea of performance lets chart their sales relative to their hire date."
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAgQAAAEICAYAAAAgKOxwAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nO3dd5xV1bn/8c8j4iBFkAxIVURBpUkZFNsVoyJFxOhFJEExJCJ2740aJVdBo4mJJsb2k9goiQW7EGIQFCwI6DCOgiChCKGFKiAChoHn98deZ9wcTplBmBng+3699mvmrL3Ks9eg59lrrzNj7o6IiIgc2A4q7wBERESk/CkhEBERESUEIiIiooRAREREUEIgIiIiKCEQERERlBCIVBhm5mZ2bHnHsSeY2Qgzu6ei95llvE1m1rSsxhMpb0oIRDIws0VmtiW8OSSOR8s7rrJgZo3M7BUzW2NmG8xsppldUd5x7Qmpki8zG2pmf028dvfq7r5wD413hZltj/0b+tLMhptZ81L0UaYJkRx4lBCIZNczvDkkjuvKO6Ay8hdgCXAU8APgcmBluUZUQZhZpd1oNtXdqwM1gXOALcAMM2u1R4MT2U1KCER2U7jrm2JmD5rZejNbaGanhvIlZrbKzPrH6o8ws2FmNsHMvjazd83sqDR91zSzUWa22swWm9n/mdlBZpZjZuvMrHWsbt2wilEnvD7fzApDTB+aWZtY3Qbhrn91uEu9IcMldgRGuPs37l7k7p+4+5uxvl4ys3+H1YP3zKxlhrnKFNMvzWxZmJO5ZnZ2hphyU82fmT1mZn9IGnOsmd2Uoa+M4qsI4Wf3uJn93cy+Ac4KP4sHzOxfZrYy/GwPzdavu2939wXufg3wLjA0NmbKOTWzgcBPgFvDCsPYUF6an6dIRkoIRL6fk4HPiO6gnwNeIHojPRboBzxqZtVj9X8C/BrIBQqBZ9P0+wjRnWRT4Eyiu/Ofuvu3YYx+sbp9gYnuvtrM2gPPAFeFmP4MjAlvXgcBY4FPgYbA2cBNZnZemhimAY+Z2aVmdmSK828CzYC6QEG6a8kS03HAdUBHd68BnAcsShMPpJ+/kUDfcI2YWW64vucz9FVaPwbuBWoAHwC/A5oDbYl+3g2BO0vZ56vAGbHXKefU3Z8I3/8+rFL13I2fp0hm7q5Dh440B9Gb0yZgfey4Mpy7ApgXq9sacOCIWNlaoG34fgTwQuxcdWA70Di8dqI3lkrAt0CLWN2rgMnh+5OJlvIPCq/zgUvC948Dv066hrlEScXJwL+Szt0ODE9z7YcD9wGfhzgLid64U9WtFeKvGbvWe0oQ07HAKqIl9MpZfhbZ5m8OcG74/jrg7xn6cmBj0s91K/DXpDrHxsYeFTtnwDfAMbGyU4Av04x3BfBBivKuwLbSzmns30GJf546dGQ7tEIgkt2F7l4rdjwZOxd/pr4FwN2Ty+IrBEsS37j7JmAd0CBpvFzgEGBxrGwx0V0g7j6d6M3oTDM7nuhNdUyodxTwi7A0v97M1gONwxhHAQ2Szg0Gjkh10e7+lbvf5u4tQ51C4HWLVDKz+8xsgZlt5Lu7+twUXaWNyd3nAzcRLZuvMrMXzCx5PuIyzd9Ivls56Ue0ByKT9vGfK1Hyk8mS2Pd1gKpEewAS1/SPUF4aDYmugVLOKZTy5ymSzcHlHYDIAaZx4pvwKKE2sDypzhpgG9H/8GeHsiOBZbE6iTe/fwMvu/vWUL4EuNfd700e2MwSd7DNShu0u68xsweA/iHm7kAvojv7RUSPN74iunNOljam0PdzwHNmdhjR44TfAZelCSXT/P0VmGVmJwInAK+X4hJLIv6nYdcQJXst3X1Zmvol8SPg/fD9j8k8p8l/mnYJu/nzFElFKwQiZau7mZ1uZocQPQuf7u7xO0/cfTvwInCvmdUIG+f+l+gNL+EvRG8m/YBRsfIngUFmdnK4k69mZj3MrAbwEbAxbOI7NNyRtjKzjqkCNbPfhfMHh/ZXA/PdfS3Rc/RviR6JVAV+k+Ga08ZkZseZ2Q/NLIdoyX4L0WOAUs+fuy8FPg5z84q7b8nQz/fi7jvCdT1oZnUBzKxhSZ7fh3k/2sweAToDd4VT2eZ0JdGekoRS/TxFslFCIJLdWNv59xC89j36eg4YQrRM3IFok1wq1xM9FlhItIHtOaKNeUDxm18B0V3j+7HyfOBK4FGiu8v5RM+vE4lGT6JNcF8S3eU+RXQnmkpV4DWi5+sLiVYsLgjnRhE9xlhGtIoxLd0FZ4oJyCFaql9DtNpRl2jZO51s8zeSaC9HtscFe8Ivia5lWljinwgcl6H+KWa2iWjvwmTgMKI9GTPD+Wxz+jTQIjweeH03fp4iGZl78iqUiOwNZjYCWOru/7eH+nsGWL6n+tsfmNl/Ea2kNAl38SJSQtpDILIPMrMmwEVAu/KNpOIws8rAjcBTSgZESk+PDET2MWb2a2AWcL+7f1ne8VQEZnYC0aON+sCfyjkckX2SHhmIiIiIVghEREREewj2a7m5ud6kSZPyDkNEZJ8yY8aMNe5e2l8ytc9TQrAfa9KkCfn5+eUdhojIPsXMFmevtf/RIwMRERFRQiAiIiJKCERERAQlBLIb7r33Xlq2bEmbNm1o27Yt06dPB+BPf/oTmzdv3qtjX3HFFbz88ssZ6zRp0oQ1a9bs1ThERPY32lQopTJ16lT+9re/UVBQQE5ODmvWrOE///kPECUE/fr1o2rVquUcpYiIlJZWCKRUVqxYQW5uLjk5OQDk5ubSoEEDHn74YZYvX85ZZ53FWWedBcBbb73FKaecQvv27enduzebNm3izTff5JJLLinub/LkyfTs2TNt/Uzefvtt2rVrR+vWrRkwYADffvtt8blHHnmE9u3b07p1a7744gsAhg4dyoABA+jcuTNNmzbl4YcfBuCbb76hR48enHjiibRq1YrRo0dn7L9JkyYMGTJkl/5FRPZlSgikVLp06cKSJUto3rw511xzDe+++y4AN9xwAw0aNGDSpElMmjSJNWvWcM899zBx4kQKCgrIy8vjj3/8I+eeey7Tpk3jm2++AWD06NH06dMnbf10tm7dyhVXXMHo0aOZOXMmRUVFPP7448Xnc3NzKSgo4Oqrr+aBBx4oLv/iiy8YP348H330EXfddRfbtm3jH//4Bw0aNODTTz9l1qxZdO3adbf7FxHZV2VMCMyssZlNMrM5Zva5md0YO1fbzCaY2bzw9fBQfryZTTWzb83s5lj948ysMHZsNLOb0oz7jJmtMrNZSeUpx0zR/jozm29mbma5sfJbYuPPMrPtZlY7RfsOZjYz9PGwmVkpx+8f6swzs/7Z+k1qa+HcfDP7zMzaZ+t3bxi3cBxdXu5Cm5Ft6PJyF8YtHAdA9erVmTFjBk888QR16tShT58+jBgxYpf206ZNY/bs2Zx22mm0bduWkSNHsnjxYg4++GC6du3K2LFjKSoqYty4cfTq1Stt/XTmzp3L0UcfTfPmzQHo378/7733XvH5iy66CIAOHTqwaNGi4vIePXqQk5NDbm4udevWZeXKlbRu3ZqJEyfyy1/+kvfff5+aNWvudv8iIvuqbCsERcAv3P0EoBNwrZm1COduA95292bA2+E1RH+n/AZgp9smd5/r7m3dvS3R3zHfTPS31lMZAXRNUZ5uzGRTgHOI/rZ4PIb7YzHcDrzr7utStH8cGAg0C0cilqzjhwRjCHAycBIwJJY4pOs3rlvs/MDQJlu/e9S4heMY+uFQVnyzAsdZ8c0Khn44tDgpqFSpEp07d+auu+7i0Ucf5ZVXXtmlD3fn3HPPpbCwkMLCQmbPns3TTz8NQJ8+fXjxxRd555136NixIzVq1MhYP5Vsf4Mj8UijUqVKFBUV7VIeP9e8eXNmzJhB69atuf3227n77rt3u38RkX1VxoTA3Ve4e0H4/mtgDtAwnO4FjAzfjwQuDPVWufvHwLYMXZ8NLHD3lLeA7v4eUWKRLOWYKdp/4u6LMowP0Bd4PrnQzOoDh7n7VI/eFUbFxinJ+OcBE9x9nbt/BUwAumbpN/kaR3lkGlArtE3Zb5Zr3C0PFTzE1u1bdyrbun0rDxU8xNy5c5k3b15xeWFhIUcddRQANWrU4OuvvwagU6dOTJkyhfnz5wOwefNm/vnPfwLQuXNnCgoKePLJJ+nTp0/W+qkcf/zxLFq0qLj+X/7yF84888zdut7ly5dTtWpV+vXrx80330xBQcEe7V9EZF9Q4k8ZhL+/3g6YHoqOcPcVECUOZla3FONeSoo34xL4PmMWM7OqRG+m16U43RBYGnu9lO+SoJTjm1keMMjdfx7qLknRPm2/ZjYo9DksS/tU5cnXNpBoZYEjjzwy5fVn8+9v/p22fNOmTVx//fWsX7+egw8+mGOPPZYnnngCgIEDB9KtWzfq16/PpEmTGDFiBH379i3ejHfPPffQvHlzKlWqxPnnn8+IESMYOTLKr+rUqZO2flxRURE5OTlUqVKF4cOH07t3b4qKiujYsSODBg3areudOXMmt9xyCwcddBCVK1fm8ccf36P9i4jsC0r054/NrDrwLnCvu78ayta7e61Yna/c/fDY66HAJnd/IKmvQ4DlQEt3X5lhzCbA39y9Vaws45gp+lgE5Ln7mqTyPkA/d++Zok1H4Lfufk54fQZwq7v3LMn4ZnYLkOPu94TXdxA9HnkvXb9J7ceFeh+E128DtwI/TNWvu/8h3fXn5eX57vwtgy4vd2HFNyt2Ka9frT5v/fdbpe5vT9mxYwcdO3Zk1KhRtGzZstziEJH9m5nNcPe88o6jrGX9lIGZVQZeAZ5NJAPByrCUnVhmX1XCMbsBBYlkIGxcTGz0y3YLlnJMMxsf2j9VwhgyrVAsBRrFXjciSmDSjp+ifeMU7TP1W9L2qcr3uBvb30iVSlV2KqtSqQo3tr8xTYu9b/ny5bRq1YpOnTopGRAR2QuyfcrAgKeBOe6e/BmwMUBip3t/4I0SjrnTs3t3X5LY6BeWzDNJOaa7nxfa/zzb4GZWEzgzXbzhkcDXZtYpXP/lsboluebxQBczOzxs+usCjM/Sb/I1Xh4+bdAJ2BDapuw32/Xujh5NezD01KHUr1Yfw6hfrT5DTx1Kj6Y99sZwJdKgQQNmz57NY489Vm4xiIjs19w97QGcDjjwGVAYju7h3A+IdtrPC19rh/J6RHezG4H14fvDwrmqwFqgZpZxnwdWEG1MXAr8LNOYKdrfENoVEd1FPxU7dwXwQpbx84BZwALgUb57tJLumvOSxhgAzA/HT0vQ7yCiPQgABjwW6swkeuSRsd90R4cOHVxEREoHyPcs/3/dH48S7SGQfdPu7iEQETmQaQ+BiIiIHLCUEIiIiIgSAhEREVFCICIiIighEBEREZQQiIiICEoIREREBCUEIiIighICERERQQmBiIiIoIRAREREUEIgIiIiKCEQERERlBCIiIgISghEREQEJQQiIiKCEgIRERFBCYGIiIighEBERERQQiAiIiIoIRARERGUEIiIiAhKCERERAQlBCIiIoISAhEREUEJgYiIiKCEQERERFBCICIiIighEBEREZQQiIiICEoIREREBCUEIiIighICERERQQmBiIiIoIRAREREUEIgIiIiKCEQERERlBCIiIgISghEREQEJQQiIiKCEgIRERFBCYGIiIighEBERERQQiAiIiIoIRARERGUEIiIiAhKCERERAQlBCIiIoISAhEREUEJgYiIiKCEQERERFBCICIiIighEBEREZQQiIiICEoIREREBCUEIiIighICERERQQmBiIiIoIRAREREUEIgIiIiKCEQERERlBCIiIgISghEREQEJQQiIiKCEgIRERFBCYGIiIighEBERERQQiAiIiIoIRARERGUEIiIiAhKCERERAQlBCIiIoISAhEREUEJgYiIiKCEQERERFBCICIiIighEBEREZQQiIiICEoIREREBCUEIiIighICERERQQmBiIiIoIRAREREUEIgIiIiKCEQkf2YmXHZZZcVvy4qKqJOnTqcf/755RjVziZPnsyHH36Y8tyIESOoU6cObdu2pUWLFjz55JOl7rsiXatUbEoIRGS/Va1aNWbNmsWWLVsAmDBhAg0bNixVH0VFRd87jkx9ZEoIAPr06UNhYSGTJ09m8ODBrFy58nuPKZKKEgIR2a9169aNcePGAfD888/Tt2/f4nMfffQRp556Ku3atePUU09l7ty5QHRn3rt3b3r27EmXLl3YsWMH11xzDS1btuT888+ne/fuvPzyywDcfffddOzYkVatWjFw4EDcHYDOnTszePBgzjzzTB566CFWr17NxRdfTMeOHenYsSNTpkxh0aJFDBs2jAcffJC2bdvy/vvvp72OunXrcswxx7B48eISxx338ccf065dOxYuXLjnJlf2L+6uYz89OnTo4CIHsmrVqvmnn37qF198sW/ZssVPPPFEnzRpkvfo0cPd3Tds2ODbtm1zd/cJEyb4RRdd5O7uw4cP94YNG/ratWvd3f2ll17ybt26+fbt233FihVeq1Ytf+mll9zdi+u4u/fr18/HjBnj7u5nnnmmX3311cXn+vbt6++//767uy9evNiPP/54d3cfMmSI33///SnjHz58uF977bXu7r5gwQKvU6eOr127tsRxJ651ypQp3r59e1+8ePH3ms8DBZDvFeD/4WV9HJwpWTCzxsAooB6wA3jC3R8K52oDo4EmwCLgEnf/ysyOB4YD7YFfufsDof5xoX5CU+BOd/9TinG7Ag8BlYCn3P2+UD4COBPYEKpe4e6FKdofDbwA1AYKgMvc/T9mNgi4FtgObAIGuvvsFO37A/8XXt7j7iNDeQdgBHAo8HfgxvCPJ97WQuzdgc0hxoJM/Sa1TzevafsVOWB99iK8fTdsWAo1G8HZd0KbS3aq0qZNGxYtWsTzzz9P9+7ddzq3YcMG+vfvz7x58zAztm3bVnzu3HPPpXbt2gB88MEH9O7dm4MOOoh69epx1llnFdebNGkSv//979m8eTPr1q2jZcuW9OzZE4iW+xMmTpzI7Nnf/e9m48aNfP3111kvcfTo0XzwwQfk5OTw5z//mdq1a7NkyZISxQ0wZ84cBg4cyFtvvUWDBg2yjicHrmyPDIqAX7j7CUAn4FozaxHO3Qa87e7NgLfDa4B1wA3AA/GO3H2uu7d197ZAB6I3tdeSBzSzSsBjQDegBdA3NibALYl+UiUDwe+AB0NsXwE/C+XPuXvrEMPvgT+mGL82MAQ4GTgJGGJmh4fTjwMDgWbh6Jpi7G6x8wNDm2z9xqWb15T9ihywPnsRxt4AG5YAHn0de0NUnuSCCy7g5ptv3ulxAcAdd9zBWWedxaxZsxg7dixbt24tPletWrXi75Py/mJbt27lmmuu4eWXX2bmzJlceeWVafvYsWMHU6dOpbCwkMLCQpYtW0aNGjWyXmZiD8H06dP50Y9+VKq4AerXr0+VKlX45JNPso4lB7aMCYG7r0jchbr718AcILEjpxeQuMMdCVwY6q1y94+BbaR3NrDA3RenOHcSMN/dF7r7f4ju9HuV8HoSd+g/BF5OEdvGWNVqQKr/ys8DJrj7Onf/CpgAdDWz+sBh7j41rAqMSvSbpBcwKqw8TQNqhbYp+03Tfpd5zdCvyIHp7bth25ady7ZticqTDBgwgDvvvJPWrVvvVL5hw4biTYYjRoxIO9Tpp5/OK6+8wo4dO1i5ciWTJ08GKH4jzs3NZdOmTcX7ClLp0qULjz76aPHrwsLofqZGjRolWinYnbgBatWqxbhx4xg8eHBx3CKplHhToZk1AdoB00PREe6+AqLEAahbinEvBZ5Pc64hsCT2einfJSEA95rZZ2b2oJnlpGj/A2C9uye22O7U3syuNbMFRCsEN5Ri/Ibh+13iMrNB4XFEtvYpr8vMnjKzvFCebl6zzUvi+gaaWb6Z5a9evTrF5YnsJzYsLXF5o0aNuPHGG3cpv/XWW7n99ts57bTT2L59e9qhLr74Yho1akSrVq246qqrOPnkk6lZsya1atXiyiuvpHXr1lx44YV07NgxbR8PP/ww+fn5tGnThhYtWjBs2DAAevbsyWuvvZZ1U+HuxJ1wxBFHMHbsWK699lqmT5+etb4cmCzdUthOlcyqA+8C97r7q6FsvbvXitX5yt0Pj70eCmxK7CGIlR8CLAdauvsun58xs97Aee7+8/D6MuAkd78+3BH/GzgEeIJoleHupPZ1gKnufmx43Rj4u7u3Tqr34zBO/6TyW4Acd78nvL6D6PHGe8Bv3f2cUH4GcKu790xqPy7U+yC8fhu4lWjVYpd+3f0PSe1Tzmu6ft19RvIcJuTl5Xl+fn660yL7tgdbhccFSWo2hv+ZtceH27RpE9WrV2ft2rWcdNJJTJkyhXr16u3xcaT8mdkMd8/LXnP/knWFwMwqA68AzyaSgWBlYsk6fF1VwjG7AQWJZMDMGptZYTgGEd35No7Vb0SUQCQeYbi7f0u0cfGk0Mf40P4pYA3RcvrBye2TvEDqJf904y8N3+8SVynap7yuJOnmtaTtRQ4MZ98JlQ/duazyoVH5XnD++efTtm1bzjjjDO644w4lA7LfyfYpAwOeBua4e/IGvDFAf+C+8PWNEo7Zl9jjAndfArSNjXkw0Cx8UmAZ0eOFH4dz9d19RYjrQmBW6OO8pLgnAf9N9KZfHJuZNXP3eaFaD2AeuxoP/Ca24a8LcLu7rzOzr82sE9Fjk8uBR1K0HwNcZ2YvEG0g3BBiTtlvmvap5jVlvynaixwYEp8myPIpgz1Fz99lf5fxkYGZnQ68D8wk+tghwGB3/7uZ/QB4ETgS+BfQO7xp1gPygcNCm01AC3ffaGZViZ6DN3X3DaRhZt2BPxF97PAZd783lL8D1AEMKAQGufumFO2b8t3HDj8B+rn7t2b2EHAO0YbHr4Dr3P3zFO0HAIPDy3vdfXgoz+O7jx2+CVzv7p7YP+Duw0Ky8ijRhsHNwE/dPT9Lv08Bw9w9P8O8pu03HT0yEBEpvQP1kUGJ9hDIvkkJgYhI6R2oCYF+dbGIiIgoIRARERElBCIiIoISAhEREUEJgYiIiKCEQERERFBCICIiIighEBEREZQQiIiICEoIREREBCUEIiIighICERERQQmBiIiIoIRAREREUEIgIiIiKCEQERERlBCIiIgISghEREQEJQQiIiKCEgIRERFBCYGIiIighEBERERQQiAiIiIoIRARERGUEIiIiAhKCERERAQlBCIiIoISAhEREUEJgYiIiKCEQERERFBCICIiIighEBEREZQQiIiICEoIREREBCUEIiIighICERERQQmBiIiIoIRAREREUEIgIiIiKCEQERERlBCIiIgISghEREQEJQQiIiKCEgIRERFBCYGIiIighEBERERQQiAiIiIoIRARERGUEIiIiAhKCERERAQlBCIiIoISAhEREUEJgYiIiKCEQERERFBCICIiIighEBEREZQQiIiICEoIREREBCUEIiIighICERERQQmBiIiIoIRAREREUEIgIiIiKCEQERERlBCIiIgISghEREQEJQQiIiKCEgIRERFBCYGIiIighEBERERQQiAiIiIoIRARERGUEIiIiAhKCERERAQlBCIiIoISAhEREUEJgYiIiKCEQERERFBCICIiIighEBEREZQQiIiICEoIREREBCUEIiIighICERERQQmBiIgcQKpXr77X+jazK8xstZkVmtlsM7tyrw2WPZbOZva30rRRQiAiIrLnjHb3tkBn4DdmdkQ5x1NiSghEROSAsmnTJs4++2zat29P69ateeONNwBYtGgRJ5xwAsBRZva5mb1lZocCmNkxZvYPM5thZu+b2fGZxnD3VcCC0Fc1M3vGzD42s0/MrFfos0noqyAcpybam9mtZjbTzD41s/tCWVszm2Zmn5nZa2Z2eCg/1swmhroFZnZMPBYz6xjGbZop5oNLOY8iIiL7tCpVqvDaa69x2GGHsWbNGjp16sQFF1wAwLx58wBWuXtLM3sRuBj4K/AEMMjd55nZycD/A36Ybozw5tsUmA/8CnjH3QeYWS3gIzObCKwCznX3rWbWDHgeyDOzbsCFwMnuvtnMaoduRwHXu/u7ZnY3MAS4CXgWuM/dXzOzKkQ3+41DHKcCjwC93P1fmeYl4wqBmTU2s0lmNidkSzfGztU2swlmNi98TWQqx5vZVDP71sxujtU/LjxXSRwbzeymNON2NbO5ZjbfzG5Lcf4RM9uUIe4OIbOab2YPm5mF8t7hOnaYWV6G9v3Ddc0zs/7Z+k1qa+Hc/JDFtc/Wb1L7dPOatl8REYm8/skyTrvvHY6+bRyn3fcOr3+ybJc67s7gwYNp06YN55xzDsuWLWPlypUAHH300QBbQtUZQBMzqw6cCrxkZoXAn4H6aULoE+o8D1zl7uuALsBtoXwyUAU4EqgMPGlmM4GXgBahj3OA4e6+OcS7zsxqArXc/d1QZyTwX2ZWA2jo7q+FulsT7YATiBKZntmSAcj+yKAI+IW7nwB0Aq41s0TAtwFvu3sz4O3wGmAdcAPwQLwjd5/r7m3Ds5UOwGbgteQBzawS8BjQjWhy+sbGJLyR18oS9+PAQKBZOLqG8lnARcB76RqGTGwIcDJwEjAk8aacod+4brHzA0ObbP3GpZvXlP2KiEjk9U+WcfurM1m2fgsOLFu/hdtfnblLUvDss8+yevVqZsyYQWFhIUcccQRbt24FICcnJ151O9FK+kHA+sR7WDhOSBPG6HD+5MSbNGDAxbG2R7r7HOB/gJXAiUAecEisvpfwsne5MY1ZAWwF2pWko4wJgbuvcPeC8P3XwBygYTjdiyhDIXy9MNRb5e4fA9sydH02sMDdF6c4dxIw390Xuvt/gBfCWIlk4X7g1nQdm1l94DB3n+ruTrTEkohtjrvPzXTNwHnABHdf5+5fAROArpn6TdILGOWRaUCt0DZlv2na7zKvGfoVERHg/vFz2bJt+05lW7Zt5/7xO/9vf8OGDdStW5fKlSszadIkFi9O9Vb0HXffCHxpZr2heMX2xFKENh64PrZanXiDrgmscPcdwGVApVD+FjDAzKqG+rXdfQPwlZmdEepcBrwbYjWVLBsAAAgsSURBVFtqZheGujmJdsB6oAfR5sbO2YIs8aZCM2tClGVMD0VHuPsKiBIHoG5J+wIuJVpOSaUhsCT2einfJSHXAWMS42ZovzRN+5JIN37afs1skJkNKkH7lNdlZk/FHmGkm9dM81LMzAaaWb6Z5a9evbpEFywisj9Yvn5LxvKioiJycnL4yU9+Qn5+Pnl5eTz77LMcf3zG/YEJPwF+ZmafAp8TblRL6NdEjwc+M7NZ4TVE+xD6m9k0oDnwDYC7/wMYA+SHxwyJx+/9gfvN7DOgLXB3KL8MuCGUfwjUSwzs7iuBnsBjFu19SKtEmwrD85NXgJtCNrLbzOwQ4ALg9nRVUpS5mTUAehN9lCPjEKnalzjA9O3T9uvuw75n+59/j7h2LnB/guiZEXl5eaW5bhGRfVqDWoeyLEVS0KDWoQB8/vnnHHPMMeTm5jJ16tSUfcyaNYvE9jB3L3707e5fknpVl1idEcCIFOVbgKtSlM8D2sSKbo+duw+4L6l+IdHj+1T9JG9wXEi0X4Gwf6BlptihBCsEZlaZKBl41t1fjZ1amViyDl9XZesr6AYUhKwlsXExsdFwENGdb+NY/UbAcqLViWOB+Wa2CKgaNthVirW/O7RvlKJ9SaUbv6T9ZmqfqjxZunktaXsRkQPSLecdx6GVK+1UdmjlStxy3nEMGzaMvn37cs8995RTdBVftk8ZGPA0MMfd/5h0egzR8gXh6xslHLMvsccF7r4kttFiGPAx0MzMjg6rCZcSPSYY5+713L2JuzcBNrv7se6+Pdb+zrDM/rWZdQrxX16K2CB61tPFzA4Pm/66AONL0e8Y4PLwjKkTsCG0Tdlvmvap5jVdvyIiAlzYriG/vag1DWsdigENax3Kby9qzYXtGjJo0CBmz55Nly5dyjvMisvd0x7A6UTL0p8BheHoHs79gGgX/LzwtXYor0d0N7uRaEPDUqLNeABVgbVAzSzjdgf+SfRLHX6Vps6mDO3ziD5RsAB4FLBQ/qMQz7dEOzvHp2k/gOizo/OBn5ag30FEn0+FaGn/sVBnJpBXgn6fStTLMK9p+013dOjQwUVEpHSAfM/y/9f98Ui8ocl+KC8vz/Pz88s7DBGRfYqZzXD3tL+rZn+lX10sIiIiSghERERECYGIiIighEBERERQQiAiIiIoIRARERHQxw73Z2a2Gsj8Vzt2Ty6wZi/0uzcp5rKhmMuGYt67jnL3OuUdRFlTQiClZmb5+9pndBVz2VDMZUMxy96gRwYiIiKihEBERESUEMjueaK8A9gNirlsKOayoZhlj9MeAhEREdEKgYiIiCghEBEREZQQSGBmz5jZKjObleLczWbmZpYbXpuZPWxm883sMzNrH6vb38zmhaN/BYq5s5ltMLPCcNwZq9vVzOaG67mtrGM2s6FmtiwWW/fYudtDXHPN7Lyyjrk08ZpZEzPbEisfFmvTwcxmhngfNjMry5hD+fVhzj43s9/Hyst1jksbc0WeZzMbHYtrkZkVxs6V+zxLFu6uQwfAfwHtgVlJ5Y2B8US/4Cg3lHUH3gQM6ARMD+W1gYXh6+Hh+8MrSMydgb+l6KMSsABoChwCfAq0KMuYgaHAzSnqtgjx5ABHhzgrlWXMpYy3SfLPInbuI+CU8G/mTaBbGc/xWcBEICe8rltR5ng3Yq6w85x0/g/AnRVpnnVkPrRCIAC4+3vAuhSnHgRuBeK7T3sBozwyDahlZvWB84AJ7r7O3b8CJgBdK0jM6ZwEzHf3he7+H+AFouvbKzLEnEov4AV3/9bdvwTmE8VbZjGXMt6Uwr+Nw9x9qkfvDqOAC/dEfKmkiflq4D53/zbUWRXKy32OdyPmlCrIPCdiMeAS4PlQVCHmWTJTQiBpmdkFwDJ3/zTpVENgSez10lCWrrzMZIgZ4BQz+9TM3jSzlqGs3GMOrguPX54xs8NDWYWdZ1LHC3C0mX1iZu+a2RmhrGGIMaE84m0OnGFm00NsHWOxVdQ5ThczVNx5TjgDWOnu88LrijzPEighkJTMrCrwK+DOVKdTlHmG8jKRJeYCot9PfiLwCPB6olmKumX9WdzHgWOAtsAKoqVWqKDzTPp4VwBHuns74H+B58zsMMo/XoCDiR5jdQJuAV4Md7EVdY4hfcwVeZ4T+vLd6gBU7HmWQAmBpHMM0bO+T81sEdAIKDCzekRZfONY3UbA8gzlZSVtzO6+0d03Abj734HKFm04LO+YcfeV7r7d3XcATxIto5IhtnKNOV28YTl4bfh+BtGz4eYh3kblFW+wFHg1POb6CNhB9Md2KuQcByljruDzjJkdDFwEjI4VV+R5lkAJgaTk7jPdva67N3H3JkT/4bZ3938DY4DLLdIJ2ODuK4g28nUxs8PDMnKXUFbuMZtZvcSOazM7iejf/lrgY6CZmR1tZocAl4brKzPh2W/Cj4DEru0xwKVmlmNmRwPNiDaNlWvM6eI1szpmVil83zTEuzD82/jazDqFn8HlwBtlFW/wOvDDEFtzog1sa6igc5wp5go+zwDnAF+4e/zxRUWeZ0ko712NOirGQbS8twLYRvRG+rOk84v4bse+AY8R3ZnMBPJi9QYQbRiaD/y0AsV8HfA50S7macCpsXrdgX+G6/lVWccM/CXM42dE/zOsH6v/qxDXXGI7xssq5tLEC1wcm+MCoGesnzyixGEB8Cjht6SWYcyHAH8NMRQAP6woc1zamCvyPIfyEcCgFPXLfZ51ZD70q4tFREREjwxERERECYGIiIighEBERERQQiAiIiIoIRARERGUEIiIiAhKCERERAT4/1+OVQkUS4w0AAAAAElFTkSuQmCC\n",
"text/plain": [
"<Figure size 432x288 with 1 Axes>"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"#get last 3 rows of sales_analysis only (the ones with the sales values)\n",
"SAS = sales_analysis.iloc[5:8,:]\n",
"#reset the index on our table slice so loops will work as expected\n",
"SAS = SAS.reset_index()\n",
"\n",
"#set x,y coords (for labels)\n",
"x_coords = SAS['total_sales']\n",
"y_coords = SAS['hire_date']\n",
"labels = SAS['name']\n",
"\n",
"# for counter i, loop through labels and plot corresponding x,y on a scatter plot\n",
"for i, name in enumerate(labels):\n",
" x = x_coords[i]\n",
" y = y_coords[i]\n",
" plt.scatter(x, y)\n",
" # label dots with a slight horizontal offset\n",
" plt.text(x+5, y, name, fontsize=10)\n",
" plt.title('Employee Sales by Hire Date')\n",
"plt.show()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Answer 2:\n",
"The newest employee seems to have sold well considering their time with the company. It's difficult to judge much more with so few data points.\n",
"\n",
"## Question 3: What countries have our best customers?\n",
"\n",
"We want to calculate some metrics for each country to get a picture of how our customers are distributed. For each country, we'll find:\n",
"\n",
"- Total number of customers\n",
"- Total value of sales\n",
"- Average value of sales per customer\n",
"- Average order value"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" country total average_sales_per_customer one_customer\n",
"0 Argentina 39.60 39.600000 1\n",
"1 Australia 81.18 81.180000 1\n",
"2 Austria 69.30 69.300000 1\n",
"3 Belgium 60.39 60.390000 1\n",
"4 Brazil 108.90 85.536000 0\n",
"5 Canada 99.99 66.948750 0\n",
"6 Chile 97.02 97.020000 1\n",
"7 Czech Republic 144.54 136.620000 0\n",
"8 Denmark 37.62 37.620000 1\n",
"9 Finland 79.20 79.200000 1\n",
"10 France 79.20 77.814000 0\n",
"11 Germany 82.17 83.655000 0\n",
"12 Hungary 78.21 78.210000 1\n",
"13 India 111.87 91.575000 0\n",
"14 Ireland 114.84 114.840000 1\n",
"15 Italy 50.49 50.490000 1\n",
"16 Netherlands 65.34 65.340000 1\n",
"17 Norway 72.27 72.270000 1\n",
"18 Poland 76.23 76.230000 1\n",
"19 Portugal 102.96 92.565000 0\n",
"20 Spain 98.01 98.010000 1\n",
"21 Sweden 75.24 75.240000 1\n",
"22 USA 74.25 80.037692 0\n",
"23 United Kingdom 68.31 81.840000 0\n"
]
}
],
"source": [
"## Get the total amount of sales per customer by country\n",
"# Let's also add a one_customer column to indicate if the country only has 1 customer\n",
"total_per_customer_table = \"\"\"\n",
"WITH total_per_customer AS \n",
"(SELECT c.country, c.last_name, SUM(i.total) total FROM invoice i\n",
"LEFT JOIN customer c ON c.customer_id=i.customer_id\n",
"GROUP BY c.customer_id\n",
"ORDER BY c.country),\n",
"avg_sales_by_country AS\n",
"(SELECT \n",
" tpc.country,\n",
" tpc.total,\n",
" AVG(tpc.total) average_sales_per_customer, \n",
" CASE\n",
" WHEN COUNT(tpc.country) = 1 THEN 1\n",
" Else 0\n",
" END as one_customer\n",
"FROM total_per_customer tpc\n",
"GROUP BY tpc.country)\n",
"\n",
"SELECT * FROM avg_sales_by_country\n",
"\"\"\"\n",
"country_analysis = run_query(total_per_customer_table)\n",
"print(country_analysis)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let's refine this result a little bit. We'll add all the countries with 1 customer into an 'Other' column. We will reuse the majorty of the query above with a few tweaks."
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" Countries_Other total_customers total_sales average_sales_per_customer \\\n",
"0 Brazil 5 427.68 85.536000 \n",
"1 Canada 8 535.59 66.948750 \n",
"2 Czech Republic 2 273.24 136.620000 \n",
"3 France 5 389.07 77.814000 \n",
"4 Germany 4 334.62 83.655000 \n",
"5 India 2 183.15 91.575000 \n",
"6 Other 15 1094.94 76.230000 \n",
"7 Portugal 2 185.13 92.565000 \n",
"8 USA 13 1040.49 80.037692 \n",
"9 United Kingdom 3 245.52 81.840000 \n",
"\n",
" avg_order_value \n",
"0 7.011148 \n",
"1 7.047237 \n",
"2 9.108000 \n",
"3 7.781400 \n",
"4 8.161463 \n",
"5 8.721429 \n",
"6 7.448571 \n",
"7 6.383793 \n",
"8 7.942672 \n",
"9 8.768571 \n"
]
}
],
"source": [
"country_query = \"\"\"\n",
"WITH total_per_customer AS \n",
"(SELECT c.country, c.last_name, SUM(i.total) total FROM invoice i\n",
"LEFT JOIN customer c ON c.customer_id=i.customer_id\n",
"GROUP BY c.customer_id\n",
"ORDER BY c.country),\n",
"avg_sales_by_country AS\n",
"(SELECT \n",
" tpc.country,\n",
" tpc.total,\n",
" AVG(tpc.total) average_sales_per_customer, \n",
" CASE\n",
" WHEN COUNT(tpc.country) = 1 THEN 1\n",
" Else 0\n",
" END as one_customer\n",
"FROM total_per_customer tpc\n",
"GROUP BY tpc.country)\n",
"\n",
"\n",
"SELECT \n",
" CASE\n",
" WHEN asc.one_customer = 1 THEN 'Other'\n",
" Else c.country\n",
" END as Countries_Other,\n",
" COUNT(DISTINCT(c.customer_id)) total_customers,\n",
" SUM(i.total) total_sales, \n",
" asc.average_sales_per_customer,\n",
" AVG(i.total) avg_order_value\n",
"FROM invoice i\n",
"LEFT JOIN customer c ON c.customer_id=i.customer_id\n",
"LEFT JOIN avg_sales_by_country asc ON asc.country=c.country\n",
"GROUP BY Countries_Other\n",
"\"\"\"\n",
"\n",
"country_analysis_other = run_query(country_query)\n",
"print(country_analysis_other)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Lastly we will re-order everything so that the 'Other' column is at the bottom. We'll copy our work from above again."
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" country_name total_customers total_sales average_sales_per_customer \\\n",
"0 Brazil 5 427.68 85.536000 \n",
"1 Canada 8 535.59 66.948750 \n",
"2 Czech Republic 2 273.24 136.620000 \n",
"3 France 5 389.07 77.814000 \n",
"4 Germany 4 334.62 83.655000 \n",
"5 India 2 183.15 91.575000 \n",
"6 Portugal 2 185.13 92.565000 \n",
"7 USA 13 1040.49 80.037692 \n",
"8 United Kingdom 3 245.52 81.840000 \n",
"9 Other 15 1094.94 76.230000 \n",
"\n",
" avg_order_value \n",
"0 7.011148 \n",
"1 7.047237 \n",
"2 9.108000 \n",
"3 7.781400 \n",
"4 8.161463 \n",
"5 8.721429 \n",
"6 6.383793 \n",
"7 7.942672 \n",
"8 8.768571 \n",
"9 7.448571 \n"
]
}
],
"source": [
"country_query = \"\"\"\n",
"WITH total_per_customer AS \n",
"(SELECT c.country, c.last_name, SUM(i.total) total FROM invoice i\n",
"LEFT JOIN customer c ON c.customer_id=i.customer_id\n",
"GROUP BY c.customer_id\n",
"ORDER BY c.country),\n",
"avg_sales_by_country AS\n",
"(SELECT \n",
" tpc.country,\n",
" tpc.total,\n",
" AVG(tpc.total) average_sales_per_customer, \n",
" CASE\n",
" WHEN COUNT(tpc.country) = 1 THEN 1\n",
" Else 0\n",
" END as one_customer\n",
"FROM total_per_customer tpc\n",
"GROUP BY tpc.country),\n",
"combined_view AS\n",
"(SELECT \n",
" CASE\n",
" WHEN asc.one_customer = 1 THEN 'Other'\n",
" Else c.country\n",
" END as country_name,\n",
" COUNT(DISTINCT(c.customer_id)) total_customers,\n",
" SUM(i.total) total_sales, \n",
" asc.average_sales_per_customer,\n",
" AVG(i.total) avg_order_value\n",
"FROM invoice i\n",
"LEFT JOIN customer c ON c.customer_id=i.customer_id\n",
"LEFT JOIN avg_sales_by_country asc ON asc.country=c.country\n",
"GROUP BY country_name)\n",
"\n",
"SELECT country_name, total_customers, total_sales, average_sales_per_customer, avg_order_value FROM \n",
"( \n",
"SELECT cv.*, \n",
" CASE\n",
" WHEN cv.country_name = 'Other' THEN 1\n",
" ELSE 0\n",
" END AS sort\n",
"FROM combined_view cv\n",
")\n",
"ORDER BY sort ASC\n",
"\"\"\"\n",
"\n",
"country_analysis_other = run_query(country_query)\n",
"print(country_analysis_other)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"With this much information, it's best to use a chart to fully digest the data. We have both *total_sales* and *average_sales_per_customer* to consider, so we'll use a scatter plot to see both."
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAZIAAAEWCAYAAABMoxE0AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nO3dd3xUVfr48c9DCIQiINVARMqidCIEEBYURAHpRUXEFfliYcVV+VkW1xXCyqKuLmBbWbAgbgClCYoFpFpASCRGQJAWaugSOqY8vz/uzZiESTJhMinkeb9e88rcc8s5dxjy5JR7jqgqxhhjzKUqUdAFMMYYU7RZIDHGGOMXCyTGGGP8YoHEGGOMXyyQGGOM8YsFEmOMMX6xQGIuGyISLyK3FPZrGnO5sUBiChUR6SAi34lIoogcF5FvRaR1QZcrL4hIBRGZLCJ7ROS0iGx3t6sGON/7ROSbQOZhijcLJKbQEJEKwKfA60BloBYwDrhQkOXKCyJSClgGNAG6AxWA9sAxoE0BFg0AEQkq6DKYossCiSlMrgVQ1VmqmqKq51R1iarGAYhIfRFZLiLHROSoiESJSCVvFxKREiIyWkR2uMd/JCKV3X0hIvI/N/2EiKwXkRrZlKu1iGwWkV9F5D0RCXGvs1FEeqfLM9gtV7iXa9wL1Ab6q+pmVU1V1cOq+ryqfuae30hEVrpl2iQifdJde6WI3J9uO0MtQ0RUREaIyDa3nG+KoxEwBWjn1oJOuMdPF5G3ROQzETkD/D8ROSQiJdNdc6CIxGbzuRgDWCAxhcsvQIqIvC8it4nIlZn2C/ACUBNoBFwNRGZxrUeBfsBN7vG/Am+6+4YCFd3zqwAjgHPZlGsI0A2ojxPs/u6mzwDuSXdcDyBBVb398r0F+EJVT3vLQESCgU+AJUB14C9AlIhcl025MusFtAZaAHcC3VT1Z5z7W6Oq5VU1feC9G/gncAVOLfAYcGu6/fcAH+Qif1NMWSAxhYaqngQ6AApMA46IyKK02oKqblfVpap6QVWPABNxAoU3DwHPquo+Vb2AE3Bud//iTsIJIH9waz4xbt5ZeUNV96rqcZxfvIPd9P8BPdwmOYA/kfUv3ipAQjZ53ACUB15U1d9UdTlOM9/gbM7J7EVVPaGqe4AVgLeaUXoLVfVbt3Z0HngfNzC6tbduwMxc5G+KKQskplBR1Z9V9T5VDQOa4tQmJgOISHURmS0i+0XkJM4v8qw6qq8BFrjNRCeAn4EUoAbOL/svgdkickBE/uXWCLKyN9373W6ZUNUDwLfAQLeJ7TYgKotrHANCs8mjJrBXVVMz5VUrm3MyO5ju/VmcwJSdvZm2/wf0FpHyODWar1U1u+BnDGCBxBRiqroFmI4TUMBp1lKguapWwPnrWbI4fS9wm6pWSvcKUdX9qpqkquNUtTFOh3cvnD6MrFyd7n1t4EC67bS/4u/AaT7an8U1vgK6iUi5LPYfAK4WkfT/J2sDadc7A5RNt++qbMqbWVZTfGdId8u+BuhP9rUrYzKwQGIKDRFpKCJPiEiYu301TtPOWveQK4DTwAkRqQU8lc3lpgD/FJFr3GtVE5G+7vvOItLMHal0EqepKyWba40UkTC3uedvwIfp9n0MtAQew+kzycoHOMFtnnufJUSkioj8TUR6AN/jBIun3U77TkBvYLZ7fiwwQETKisgfgOHZ5JXZISDMHTmWkxnA00AzYEEu8jDFmAUSU5icAtoC37sjidYCG4En3P3jcH5pJwKLgfnZXOtVYBGwREROuddq6+67CpiLE0R+BlbhNOtkZSZOJ/hO9zU+bYeqngPmAXWzK4/bT3MLsAVY6ua9Dqdp7ntV/Q3og9M8dhT4D3CvWysDmAT8hhMU3ifrJjRvlgObgIMicjSHYxfgNguq6plc5GGKMbGFrYzxj4iMAa5V1XtyPLgIEJEdwEOq+lVBl8UUDSVzPsQYkxW3uWs4Tp9CkSciA3H6TpYXdFlM0WFNW8ZcIhF5AKff43NVXV3Q5fGXiKwE3gJGZho9Zky2Ata0JSLv4oyGOayqTTPtexJ4GaimqkdFRHDatHvgDFu8T1V/CEjBjDHG5KlA1kim48wplIE7EudWYE+65NuABu7rQZy/iowxxhQBAesjUdXVIlLHy65JOMMLF6ZL6wvMUKd6tFZEKolIaE4PQ1WtWlXr1PGWhTHGmKzExMQcVdVqeXW9fO1sdyeh26+qPzqtWR61yPiU7T437aJAIiIP4tRaqF27NtHR0YErsDHGXIZEZHdeXi/fOttFpCzwLDDG224vaV47b1R1qqpGqGpEtWp5FlCNMcZcovyskdTHeWgrrTYSBvwgIm1waiDpp6EII+M0FMYYYwqpfKuRqOpPqlpdVeuoah2c4NFSVQ/iPIF8r7t+wg1Aok0WZ4wxRUPAAomIzMKZAO46EdknItnNDfQZztQT23GmD384UOUyxhiTtwI5aivbdRTcWknaewVGBqosxhhjAseebC8EDh48yF133UX9+vVp3LgxPXr04JdffsmTa0dGRvLKK6/keFydOnVo1qwZzZs356abbmL37jwd1AHA9OnTeeSRR7zuK1/eWTrjwIED3H777XmetzEmcCyQ5KPFOxfTdW5Xmr/fnK5zu7J452JUlf79+9OpUyd27NjB5s2bmTBhAocOHcr38q1YsYK4uDg6derE+PHjcz4hAGrWrMncuXMLJG9jzKWxQJJPFu9cTOR3kSScSUBREs4kEPldJC9EvUBwcDAjRozwHBseHk7Hjh0ZM2YM4eHhhIeHU6tWLYYNGwbA//73P9q0aUN4eDgPPfQQKSnOUhpffPEFLVu2pEWLFnTp0sVzvc2bN9OpUyfq1avHa6+9lmNZ27Vrx/79v6/PlFV+5cuX54knnqBly5Z06dKFI0eOANCpUyfP8z1Hjx4l/UOje/fupXv37lx33XWMGzfuorzj4+Np2tSZUSclJYUnn3zSU1N6/fXXffqsjTH5ywJJPnn1h1c5n3I+Q9r5lPPMWD6DVq1aeT3nH//4B7GxsaxatYoqVarwyCOP8PPPP/Phhx/y7bffEhsbS1BQEFFRURw5coQHHniAefPm8eOPPzJnzhzPdbZs2cKXX37JunXrGDduHElJSdmW9YsvvqBfv34AWeYHcObMGVq2bMkPP/zATTfd5DUwZLZu3TqioqKIjY1lzpw52T5QOnXqVHbt2sWGDRuIi4tjyJAhOV7fGJP/bBr5fHLwzEGv6Sd/O5ntearKkCFDGDVqFK1ateKNN94gJiaG1q1bA3Du3DmqV6/O2rVrufHGG6lbty4AlStX9lyjZ8+elC5dmtKlS1O9enUOHTpEWFjYRXl17tyZQ4cOUb16dU/T1rJly7zmB1CiRAkGDRoEwD333MOAAQNy/BxuvfVWqlSpAsCAAQP45ptviIiI8HrsV199xYgRIyhZsuRF92SMKTwskOSTq8pdRcKZix+NCa0fSsyKmCzPi4yMJCwszNOspaoMHTqUF154IcNxixYtItO0Mx6lS5f2vA8KCiI5OdnrcStWrKBcuXLcd999jBkzhokTJ2aZnzdp+ZcsWZLUVGcW8vPnz3s9Jqvt9FQ12/3GmMLBmrbyyWMtHyMkKCRDWkhQCOPuHceFCxeYNm2aJ339+vWsWrWKTz/9lKVLl2bo1+jSpQtz587l8OHDABw/fpzdu3fTrl07Vq1axa5duzzpl6JMmTJMnjyZGTNmcPz48SzzA0hNTfV0jM+cOZMOHToAzgiwmBgnOGbuOF+6dCnHjx/n3LlzfPzxx/zxj3/Msixdu3ZlypQpnsB3qfdkjAksCyT5pGe9nkS2jyS0XCiCEFoulMj2kfSq34sFCxawdOlS6tevT5MmTYiMjKRmzZr8+9//5sCBA56O7jFjxtC4cWPGjx9P165dad68ObfeeisJCQlUq1aNqVOnMmDAAFq0aOFpcroUoaGhDB48mDfffDPL/ADKlSvHpk2baNWqFcuXL2fMGGcatSeffJK33nqL9u3bc/RoxiXCO3TowJ/+9CfCw8MZOHBgls1aAPfffz+1a9emefPmtGjRgpkzZ17yPRljAqdIr9keERGhNvtvwSlfvjynT58u6GIYY3JJRGJUNeu/4nLJaiTGGGP8YoHEXDKrjRhjwAKJMcYYP1kgMcYY4xcLJMYYY/xigcQYY4xfLJAYY4zxiwUSY4wxfrFAYowxxi8WSIwxxvjFAokxxhi/BCyQiMi7InJYRDamS3teROJEJFZElohITTddROQ1Ednu7m8ZqHIZY4zJW4GskUwHumdKe1lVm6tqOPApMMZNvw1o4L4eBN4KYLmMMcbkoYAFElVdDRzPlJZ+OcByQNrUw32BGepYC1QSkdBAlc0YY0zeyfcVEkXkn8C9QCLQ2U2uBexNd9g+N+2iJQVF5EGcWgu1a9cOaFmNMcbkLN8721X1WVW9GogCHnGTva2n6nWhFFWdqqoRqhpRrVq1QBXTGGOMjwpy1NZMYKD7fh9wdbp9YcCBfC+RMcaYXMvXQCIiDdJt9gG2uO8XAfe6o7duABJV9aJmLWOMMYVPwPpIRGQW0AmoKiL7gLFADxG5DkgFdgMj3MM/A3oA24GzwLBAlcsYY0zeClggUdXBXpLfyeJYBUYGqizGGGMCx55sN8YY4xcLJMYYY/xigcQYY4xfLJAYY4zxiwUSY4wxfrFAYowxxi8WSIwxxvjFAokxxhi/WCAxxhjjFwskxhhj/GKBxBhjjF8skBhjjPGLBRJjjDF+sUBijDHGLxZIjDHG+CXbQCIiQSLyVX4VxhhjTNGTbSBR1RTgrIhUzKfyGGOMKWJ8WSHxPPCTiCwFzqQlquqjASuVMcaYIsOXQLLYfRljjDEXyTGQqOr7IlIGqK2qW329sIi8C/QCDqtqUzftZaA38BuwAximqifcfc8Aw4EU4FFV/TK3N2OMMSb/5ThqS0R6A7HAF+52uIgs8uHa04HumdKWAk1VtTnwC/CMe83GwF1AE/ec/4hIkI/3YIwxpgD5Mvw3EmgDnABQ1Vigbk4nqepq4HimtCWqmuxurgXC3Pd9gdmqekFVdwHb3TyNMcYUcr4EkmRVTcyUpnmQ9/8Bn7vvawF70+3b56ZdREQeFJFoEYk+cuRIHhTDGGOMP3wJJBtF5G4gSEQaiMjrwHf+ZCoizwLJQFRakpfDvAYrVZ2qqhGqGlGtWjV/imGMMSYP+BJI/oLTd3EBmAWcBB6/1AxFZChOJ/wQVU0LFvuAq9MdFgYcuNQ8jDHG5B9fRm2dBZ51X34Rke7AX4Gb3OumWQTMFJGJQE2gAbDO3/yMMcYEXo6BREQigL8BddIf7468yu68WUAnoKqI7APG4ozSKg0sFRGAtao6QlU3ichHwGacJq+R7lP1xhhjCjn5vXUpiwNEtgJPAT8BqWnpqro7sEXLWUREhEZHRxd0MYwxpkgRkRhVjcir6/nyZPsRVfXluRFjjDHFkC+BZKyIvA0sw+lwB0BV5wesVMYYY4oMXwLJMKAhEMzvTVsKWCAxxhjjUyBpoarNAl4SY4wxRZIvz5GsdefCMsYYYy7iS42kAzBURHbh9JEIoDkN/zXGGFM8+BJIMs/ga4wxxnjk2LTlPi9SCWcdkd5ApcLwDIkxxpjCwZf1SB7DmVyxuvv6n4j8JdAFM8YYUzT40rQ1HGirqmcAROQlYA3weiALZowxpmjwZdSW4Cx/myYF79O+G2OMKYZ8qZG8B3wvIgvc7X7Au4ErkjHGmKLEl2nkJ4rISpxhwAIMU9UNgS6YMcaYosGXaeQ/UNU/AT94STPGGFPM+dJH0iT9hogEAa0CUxxjjDFFTZaBRESeEZFTQHMROem+TgGHgYX5VkJjjDGFWpaBRFVfUNUrgJdVtYL7ukJVq6jqM/lYRmOMMYWYL01bn4pIOQARuUdEJorINQEulzHGmCLCl0DyFnBWRFoATwO7gRkBLZUxxpgiw5dAkqzOwu59gVdV9VXgipxOEpF3ReSwiGxMl3aHiGwSkVQRich0/DMisl1EtopIt9zeiDHGmILhSyA5JSLPAPcAi91RW8E+nDedi2cO3ggMAFanT3TXO7kLZ4RYd+A/bj7GGGMKOV8CySCcdUiGq+pBoBbwck4nqepq4HimtJ9VdauXw/sCs1X1gqruArYDbXwomzHGmALmy5PtB4GJ6bb3kPd9JLWAtem297lpFxGRB4EHAWrXrp3HxTDGGJNbvkwjfyrdcyTnRSRFRBLzuBzeJoFUbweq6lRVjVDViGrVquVxMYwxxuSWLzWSDB3rItKPvG922gdcnW47DDiQx3kYY4wJAF/6SDJQ1Y+Bm/O4HIuAu0SktIjUBRoA6/I4D2OMMQHgy6SNA9JtlgAiyKLZKdN5s4BOQFUR2QeMxel8fx2ohjMCLFZVu6nqJhH5CNgMJAMjVTUli0sbY4wpRHxZj6R3uvfJQDzOKKtsqergLHYt8Jaoqv8E/ulDeYwxxhQivvSRDMuPghhjjCmaspv9918iMsJL+ih33XZjjDEm2872XsBUL+mvAj0DUxxjjDFFTXaBRFU11UtiKt6f+zDGGFMMZRdIzopIg8yJbtq5wBXJGGNMUZJdZ/sY4HMRGQ/EuGkRwDPA44EumDHGmKIhy0Ciqp+7T7E/BfzFTd4IDFTVn/KjcMYYYwq/bIf/qupGYGg+lcUYY0wRlOspUowxxpj0LJAYY4zxS7aBRESCRGRUfhXGGGNM0ZNtIHEnTsxxXi1jjDHFly+TNn4rIm8AHwJn0hJV9YeAlcoYY0yR4Usgae/+/Ee6NCXv1yQxxhhTBPky+2/n/CiIMcaYosmXNdtriMg7IvK5u91YRIYHvmjGGGOKAl+G/04HvgRqutu/YFOkGGOMcfkSSKqq6kdAKoCqJgO2DK4xxhjAt0ByRkSq4K7TLiI3AIkBLZUxxpgiw5dA8v+ARUB9EfkWmMHvkzhmSUTeFZHDIrIxXVplEVkqItvcn1e66SIir4nIdhGJE5GWl3g/xhhj8lmOgcR9XuQmnGHADwFNVDXOh2tPB7pnShsNLFPVBsAydxvgNqCB+3oQeMuXwhtjjCl4OQ7/FZEQ4GGgA07z1tciMkVVz2d3nqquFpE6mZL7Ap3c9+8DK4G/uukzVFWBtSJSSURCVTXB91sxxhhTEHxp2poBNAFeB94AGgMfXGJ+NdKCg/uzupteC9ib7rh9bpoxxphCzpcn269T1RbptleIyI95XA5va8Cr1wNFHsRp/qJ27dp5XAxjjDG55UuNZIM7UgsAEWkLfHuJ+R0SkVD3OqHAYTd9H3B1uuPCgAPeLqCqU1U1QlUjqlWrdonFMMYYk1d8CSRtge9EJF5E4oE1wE0i8pOI+NLpnt4ifl9xcSiwMF36ve7orRuAROsfMcaYosGXpq3MI698IiKzcDrWq4rIPmAs8CLwkTvFyh7gDvfwz4AewHbgLDDsUvI0xhiT/3yZtHH3pVxYVQdnsauLl2MVGHkp+RhjjClYttSuMcYYv1ggMcYY4xdfppEvJyIl3PfXikgfEQkOfNGMMcYUBb7USFYDISJSC2dak2E4058YY4wxPgUSUdWzwADgdVXtj/N0uzHGGONbIBGRdsAQYLGb5suwYWOMMcWAL4HkceAZYIGqbhKResCKwBbLGGNMUeHLcySrgFUiUs7d3gk8GuiCGWOMKRp8GbXVTkQ2Az+72y1E5D8BL5kxxpgiwZemrclAN+AYgKr+CNwYyEIZY4wpOnx6IFFV92ZKSglAWYwxxhRBvgSSvSLSHlARKSUiT+I2c12OgoKCCA8Pp2nTptxxxx2cPXs2V+dPmDAhIOWKj4+nadOmAbm2Mcb4w5dAMgJnQsVaOOuGhHMZT7BYpkwZYmNj2bhxI6VKlWLKlCk+naeqpKamBiyQGGNMYZVjIFHVo6o6RFVrqGp1Vb1HVY/lR+EC6cyGwyS8uI59o78m4cV1nNlw+KJjOnbsyPbt2wGYOHEiTZs2pWnTpkyePBlwagmNGjXi4YcfpmXLlgwfPpxz584RHh7OkCFDLqpFvPLKK0RGRgKwfv16mjdvTrt27Xjqqac8x8XHx9OxY0datmxJy5Yt+e677wL8SRhjjH+yHP4rIq+TxXK3AKpaZIcAn9lwmBPzt6FJqQCknLjAifnbnH1nzgCQnJzM559/Tvfu3YmJieG9997j+++/R1Vp27YtN910E1deeSVbt26lcuXKbNiwgUWLFjFz5kxiY2MBJyhkZdiwYUydOpX27dszevRoT3r16tVZunQpISEhbNu2jcGDBxMdHR2gT8IYY/yX3XMkl+1vr2OLtlAiKeMy8ZqUyskv4wEIDw8HnBrJ8OHDeeutt+jfvz/lypUDYMCAAXz99df06dOHGjVqcOWVVwLQp08fgoNzns/yxIkTnDp1ivbt2wNw99138+mnnwKQlJTEI488QmxsLEFBQfzyyy95cs/GGBMoWQYSVX0/PwuSX37+egXlzwaBXLwv5cQFAGJjY1m5ciWRkZHcfffdrF69mho1ajBu3DhEhB07djB16lRmzJjBb7/95jl/+vTpXLjgXOOTTz5hzJgxbN++nVtuuYWoqCjOnz8POP0pWZk0aRI1atTgxx9/JDU1lZCQkDy8e2OMyXu+PJBYTUReEZHPRGR52is/ChcIX8+ewdnkk173BVUqnWF7w4YNTJ48mc8++4ydO3eybNkyjh07xpw5c5g6dSpz5swhOTk54zWCgkhKSqJDhw58//33lC9fnl69ejFhwgRPrePKK6/kiiuuYO3atQDMnj3bc35iYiKhoaGUKFGCDz74gJQUG2ltjCncfJl8MQr4EOiJM4JrKHAkkIUKpFPHjhJXdhWtq95GyRK/N0MlpyZxZbfrnFnFXG3atCEsLIywsDBatGjBfffdR0hICLVr16Zv377Ex8dTqVKlDNdv3LgxzZs3p169eiQlJVGqVCmefvppKlSoQK9evTzHvfPOOzzwwAOUK1eOTp06UbFiRQAefvhhBg4cyJw5c+jcubOnOc0YYworX4b/VlHVd4AkVV2lqv8H3BDgcgXMFVWqsufMz6w/+jlnkhJRVc4kJfLThW8pd331DL+4S5f+vYbSsmVLxo8fz9y5cwkLCwOgTp06vP766xmu3759e37++WfOnDnDI488woEDB1iyZAlNmzZl+vTpnlFbTZo0IS4ujjVr1lCxYkUiIiIAaNCgAXFxcaxdu5YXXniB06dPe/LauHFjID8aY4y5JL7USJLcnwki0hM4AIT5k6mIPAY8gNNTMU1VJ4tIZZyaTx0gHrhTVX/1Jx9vOt51L0umvsGeMz+z54zzXGXJUqXp+uAjPp3fsGFDdu3axY4dO6hfvz6zZs3yelxiYiK1atUC4P33L+5uWrx4MS+88ALJyclcc801TJ8+/dJuyBhjCpgvgWS8iFQEngBeByoAoy41QxFpihNE2gC/AV+IyGI3bZmqvigio4HRwF8vNZ+sNOrYGXD6Sk4dO8oVVarS8a57Pek5CQkJYerUqfTs2ZOqVavSoUMHrzWFyMhI7rjjDmrVqsUNN9zArl27MuwfNGgQgwYN8v+GjDGmgEl2I4gCkqHIHUA3Vb3f3X4OuAAMBzqpaoKIhAIrVfW67K4VERGh9oyFMcbkjojEqGpEXl0vyz4SEXlARBq470VE3hORRBGJE5Hr/chzI3CjiFQRkbJAD+BqoIaqJgC4P6tnUa4HRSRaRKKPHCmyff7GGHPZyK6z/TGcvgqAwUBzoB7w/4DXLjVDVf0ZeAlYCnwB/AgkZ3tSxvOnqmqEqkZUq1btUothjDEmj2QXSJJVNa2jvRcwQ1WPqepXgF9jUlX1HVVtqao3AseBbcAht0kL9+fFk18ZY4wpdLILJKkiEioiIUAX4Kt0+8r4k6mIVHd/1gYGALOARTjPqOD+XOhPHsYYY/JHdqO2xuDMtxUELFLVTQAichOw089854lIFZyhxSNV9VcReRH4SESGA3uAO/zMwxhjTD7Ibq6tT0XkGuCKTM9zRAN+jVtV1Y5e0o7h1HyMMcYUIdk+R6KqycCvmdLOBLRExhhjihSf1mw3xhhjsmKBxOSptDXvW7RokacrPN5///1s3rwZcOYdO3r0aJ5c1xjjvxynSBGRll6SE4HdbtOXKYY+3rCfl7/cyoET56hZqQxPdbuOftfX8qx5D/Dll1/yzDPPsGrVqgznpqSkEBQUlKv83n777TwruzEmb/lSI/kPsBaYCkwD1gCzgV9EpGsAy2YKqY837OeZ+T+x/8Q5FNh/4hzPzP+Jjzfsz3DcyZMnPatHrly5ks6dO3P33XfTrFkzAPr160erVq1o0qQJU6dOBWDRokWEh4cTHh7OddddR926dQHo1KmTLTlsTCHly6SN8cDwdMN/GwNPAc8D84ElASudKZRe/nIr55IyLrh1LinFST93jvDwcM6fP09CQgLLl/++Btq6devYuHGjJzi8++67VK5cmXPnztG6dWsGDhxInz596NOnDwB33nknN910U/7dmDHmkvgSSBqmBREAVd0sIter6k4RL+vVmsvegRPnskxP37S1Zs0a7r33Xs/syG3atPEEEYDXXnuNBQsWALB37162bdtGlSpVAPjXv/5FmTJlGDlyZCBvxRiTB3xp2toqIm+JyE3u6z84zVql+X2tElPIHDp0iLvvvpt69erRqlUr2rVr5/ml7a+albxPbJA5vV27dhw9epS0yTXTLxq2cuVKvvrqK9asWcOPP/7I9ddf71nTftmyZcyZM4cpU6bkSXmNMYHlSyC5D9gOPI6zDslONy0J8G0RD5OvVJV+/fpx4403snPnTmJiYpg9ezb79u3z6fyc1ol/qtt1lAnO2FleJjiIp7plnPV/y5YtpKSkeGoZ6SUmJnLllVdStmxZtmzZ4lm/fvfu3Tz88MN89NFHlCnj10w8xph84kvTVnfgDVX9t5d9p/O4PCYXfvn+IGsW7uD08QuUr1yadn3rc23bq1i+fDmlSpVixIgRnmOvueYa/vKXv5CSksLo0aNZuXIlFy5cYOTIkTz00EOsXLmScePGERoaSmxsLJ999hndu3enQ4cOrF27lhYtWjBs2DDGjh3L4cOHeWDMJBbuL0ioHA8AABtBSURBVMuuzbGcXvUOVcsI/1pWgbNnzxIeHs7x48dJTEzkD3/4Aw0bNqRVq1aAs1b9xo0befHFF5kyZQphYWGULVuWG25wVm+ePn06x44do3///gDUrFmTzz77LP8/XGOM71Q12xfwHrAb+ADoCZTM6Zz8erVq1UqLq61rE3TKX1boGw8t87ym/GWFbl2boK+++qo+/vjjXs/773//q88//7yqqp4/f15btWqlO3fu1BUrVmjZsmV1586dqqq6a9cuDQoK0ri4OE1JSdGWLVvqsGHDNDU1VT/++GPt27evqqomJiZqUlKSqqouXbpUBwwYoKqq7733ntatW1dPnDih586d09q1a+uePXv09OnTWq9ePf3tt99UVbVdu3YaFxcX0M/KGJMREK15+Ls4xxqJqg4TkWDgNuBu4D8islTdFQ5NwVizcAfJv6VmSEv+LZU1C3fAVRmPHTlyJN988w2lSpXimmuuIS4ujrlz5wJOE9O2bdsoVarURZ3hdevW9QzVbdKkCV26dEFEaNasGfHx8Z7zhw4dyrZt2xARkpJ+7zbr0qULFStWBKBx48bs3r2bq6++mptvvplPP/2URo0akZSU5MnDGFM0+dK0haomicjngOJMId8XsEBSgE4fv5BlepMuTZg3b54n7c033+To0aNERERQu3ZtXn/9dbp165bhvJUrV2boDAcoXbq0532JEiU82yVKlCA52XkW9bnnnqNz584sWLCA+Ph4OnXq5PX8oKAgzzn3338/EyZMoGHDhgwbNuwS7t4YU5jk2NkuIt1FZDpOh/vtwNtAaIDLdVmIj4+nadOmGdIiIyN55ZVXsj0vOjqaRx99FHB+wXubZqR85dIXpaWl33zzzaxdu5aXX37Zk3727FkAVq1axeTJk0lKSiImJoawsDC+++47vv32W7Zv356r+wOnRlKrVi3A6d9IK/PkyZOzPKdt27bs3buXmTNnMnjw4FznaYwpXHypkdyH8yT7Q6rq/c9gQ8LBhezc8QrnLyQQUjqUevWfBFpc0rUiIiKIiIgAnF/K5cuXp3379hmOade3PiuitmRo3ipZqgTt+tZHRKhevTrfffcddevWpVq1apQrV46XXnqJv/71r1x33XWepqZmzZrRpEkTfvvtN9asWZPrsj799NMMHTqUiRMncvPNN/t83p133klsbKznyXdjTNGVY41EVe9S1Y/TgoiI/FFE3gx80YqOhIML2bLlWc5fOAAo5y8cYMuWZzl8JPuH/jt16sRf//pX2rRpw7XXXsvXX38NOMGjV69exMfHM2XKFCZNmkR4eDhff/01R44cYeDAgQx5pDdvfPU4CWe3OhcLOc8H349l0IjbeOihhyhRogTTpk1j165drFu3jhUrVjBokLOMzMCBA0lJSWH58uXExMRQsWJF4uPjqVOnDgD33XcfEydOpEKFCtSrV4+5c+cyffp0BgwYwMMPP0zPnj2pU6cOPXr0YP/+/fzyyy8899xzzJkzh7CwMObPn09YWBhvvPEGx48fp1+/fuzZs4fRo0cTFxcHOE+1Hz9+nK5du1KnTh3mz5/P008/TbNmzejevXuGvhZjTOHm0+y/IhIuIv8SkXhgPLAloKUqYnbueIXU1IxPe6emnmPP7mk5npucnMy6deuYPHky48aNy7CvTp06jBgxglGjRhEbG0vHjh157LHHGDVqFOvXr2fxF4uYt/41Rk65ma2pX9C1Zxc2bNhAnz592LNnT5Z59u3blzfeeIMOHTpkeUxCQgLffPMNn376KaNHjwZg/vz5xMfH89NPP/H22297ajDnz5/ngQce4JNPPuHrr7/m4MGDnuuMHTuW66+/nri4OCZMmMCQIUO49tprKVmyJGfPnmXx4sUsXLiQe+65h86dO/PTTz9RpkwZFi9enONnZ4wpHLJs2hKRa4G7gMHAMeBDQFTVHkLM5PyFBK/pvyUdBq64KD391DIDBgwAoFWrVp6RUNn56quvPNOpgzMx4qlTp1i9ejXz588HoGfPntk2Gd1yyy28/fbbdOvWLctZePv160eJEiVo3Lgxhw4dAuCbb77hjjvuoESJElx11VV07ux8FbZs2ULdunVp0KABAPfcc49nEsZvvvnG0/F/8803c+LECTZu3MikSZMIDg4mODiYZs2akZKSQvfu3QEyjAozxhR+2dVItuAsfdtbVTuo6utA9o88F1Mhpb2PPahWtRa//pphgUmOHz9O1apVPdtpI5vSj2rKTmpqKmvWrCE2NpbY2Fj279/PFVc4wcrXuc/eeOMNAB5++OEsj0k/4soZdv77T2+yytvbOWnHph8FFhwc7ElPPyrMmMtRdgNx1q5dS9u2bQkPD6dRo0ZERkZmOK5v3760a9cuH0ubs+wCyUDgILBCRKaJSBcgT2ZpFJFRIrJJRDaKyCwRCRGRuiLyvYhsE5EPRaRUXuSVH+rVf5ISJTJO51GiRBmaNvsroaGhLFu2DHCCyBdffJFtk1JmV1xxBadOnfJsd+3a1RMIAM8EiTfeeCNRUVEAfP755xcFsIxlK8GsWbPYunUrY8aM8bksHTp0YN68eaSmpnLo0CFWrlwJQMOGDdm1axc7duwAYNasWZ5z0pdr5cqVVK1alQoVKvicpzFFWVxcHJMmTSIyMpJJkyZ5+gizM3ToUKZOnUpsbCwbN27kzjvv9Ow7ceIEP/zwAydOnGDXrl2BLHquZBlIVHWBqg4CGgIrcebZquFO4HjJ65CISC3gUSBCVZsCQThNaC8Bk1S1Ac468cMvNY/8FnpVXxo2/CchpWsCQkjpmjRs+E9Cr+rLjBkzGD9+POHh4dx8882MHTuW+vXr+3zt3r17s2DBAk9n+2uvvUZ0dDTNmzencePGnokNx44dy+rVq2nZsiVLliyhdu3a2V63dOnSLFy4kEWLFvHmm76NnRg4cCBhYWE0bdqUhx56iLZt21KxYkVCQkKYOnUqPXv2pEOHDlxzzTWecyIjIz3lHT16NO+//77P925MURYXF8cnn3xCYmIi4AyV/+STT3IMJocPHyY01GnlCAoKonHjxp598+bNo3fv3tx1113Mnj07cIXPJcmuueKig0UqA3cAg1TV97GeGa9RC2ehrBbASeBj4HUgCrhKVZNFpB0Qqardsr4SREREqC12lL9Onz5N+fLlOXbsGG3atOHbb7/lqquuyvlEY4qZSZMmeYJIehUrVqR///706tXLs8QCOH90lS9fnrNnzzJp0iQ6depE9+7dGTp0KCEhIYDTvzl27Fhq1KjB7bff7lMNxxsRiVHViEu7s4vlas12VT2uqv+91CDiXmM/8AqwB0jAWbY3Bjihvy/duw+o5e18EXlQRKJFJDptenKTf3r16kV4eDgdO3bkueeesyBiTBa8BZG09Kz6FEWEMWPGEB0dTdeuXZk5c6ZnEMqhQ4fYvn07HTp08Ix8TB+ICpJPU6TkJRG5EmeKlbrACWAOzjxemXmtKqnqVJxlf4mIiPC9OmXyRFq/iDEmexUrVsyyRlKlShWvA3HS5rqrX78+f/7zn3nggQeoVq0ax44d48MPP+TXX3/1HHPy5Elmz57N+PHjA38zOchVjSSP3ALsUtUjqpqEs1xve6CSiKQFtjDgQAGUzaQTFBTkWT89PDzchuQakwtdunQhODg4Q1pwcDBdunShfPnyWQ7EWbx4sWe047Zt2wgKCqJSpUrMmjWLL774gvj4eOLj4z3rDBUG+V4jwWnSukFEygLncIYYRwMrcObymg0MBRYWQNmKpcRPPuHwpMkkJyRQMjSU6qMep2Lv3hmWzfUmOTmZkiUL4itkTOHXvHlzwFnxMzExkYoVK9KlSxdP+owZMxg5ciRPPPEEgGcgzrPPPsuoUaMoW7YsJUuWJCoqir1797Jnzx7Puj3gzM5doUIFvv/+e9q2bZv/N5hOrjrb8yxTkXHAICAZ2IAzk3AtnCBS2U27J6e5vayz3X+Jn3xCwnNjUHeZWwAJCSH0+X9Qa/BgTp/OuHbZ9OnTWbx4MefPn+fMmTMsWrSIvn378uuvv5KUlMT48ePp27cv8fHx3HbbbXTo0IHvvvuOWrVqsXDhQsqUKcP27dsZMWIER44cISgoiDlz5lC/fn1efvllPvroIy5cuED//v0vetLfGJM38rqzvUD+nFTVscDYTMk7gTYFUJxi7fCkyRmCCICeP8/hSZM5d+4c4eHhgPPXT9qa72vWrCEuLo7KlSuTnJzMggULqFChAkePHuWGG26gT58+gFMtnzVrFtOmTePOO+9k3rx53HPPPQwZMoTRo0fTv39/zp8/T2pqKkuWLGHbtm2sW7cOVaVPnz6sXr2aG2+8MX8/EGNMrlm7RDGXnOB9epfkhIQsm7ZuvfVWKleuDDhPrv/tb39j9erVlChRgv3793umVKlbt64nEKVNAXPq1Cn279/vWUo3bVjjkiVLWLJkCddffz3gDDPetm2bBRJjcrBv3z5GjhzJ5s2bSU1NpVevXrz88sts3ryZAwcO0KNHD+D34cVPPvlknpehIDrbTSFSMtT79C5ZpQMZFsCKioriyJEjxMTEEBsbS40aNTjv1nC8LWyVVVOqqvLMM894pn7Zvn07w4cXmWdSjSkQqsqAAQPo168f27Zt45dffuH06dM8++yzxMbG8tlnn+VZXiLifWI+LJAUe9VHPY64tYI0EhJC9VGP+3R+YmIi1atXJzg4mBUrVrB79+5sj69QoQJhYWF8/PHHAFy4cIGzZ8/SrVs33n33XU+fzP79+zl8+PAl3JExl595B48T8d0mQlfEEvHdJuYdPA7A8uXLCQkJ8aw0GhQUxKRJk3j77bd5+umn+fDDDwkPD+fDDz8EYPPmzWmrmDYTkUfTri8i94jIOhGJFZH/pgUNETktIv8Qke+BLCf4skBSzFXs3ZvQ5/9ByZo1QYSSNWsS+vw/qNi7t0/nDxkyhOjoaCIiIoiKiqJhw4Y5nvPBBx/w2muv0bx5c9q3b8/Bgwfp2rUrd999N+3ataNZs2bcfvvtGeYYM6a4mnfwOE9u3cu+C0kosO9CEk9u3cu8g8fZtGkTrVq1ynB8hQoVqFOnDn//+98ZNGgQsbGxnrWItmzZwpdffgnwMzBWRIJFpBHO4Kc/qmo4zuS8Q9zLlQM2qmpbVf0mqzIWyKitvGKjtowxl7uI7zax78LFC72FlQ7mT+u/Yvfu3UycODHDvvDwcIYPH87WrVs9k7xGRkYSHBzMs88+i4jE4ASJW4F+wN+AtCaAMsAsVY0UkWSgtKpmO/O7dbYbY0whtt9LEElLb9KkiWe9nzQnT55k7969XtcaSt9viVPzKIkzq/v7qvqMl2zO5xREwJq2jDGmUKtVOjjL9C5dunD27FlmzJgBQEpKCk888QT33XcfNWrU8LV5eBlwu4hUB2dyXhG5JodzMrBAYowxhdgz9UIpUyLjJI9lSgjP1AtFRFiwYAFz5syhQYMGXHvttYSEhDBhwgQ6d+7M5s2bM3S2e6Oqm4G/A0tEJA5YCmQ9bNML6yMxxphCbt7B47ywM4H9F5KoVTqYZ+qFMvCqypd8vcviyXZjjDG+G3hVZb8CR6BZ05Yxxhi/WCAxxhjjFwskxhhj/GKBxBhjjF8skBhjjPGLBRJjjDF+sUBijDHGLxZIjDHG+MUCiTHGGL/keyARkevcxVPSXidF5HF3orClIrLN/XllfpfNGGNM7uV7IFHVraoa7i6g0go4CywARgPLVLUBzmyUo/O7bMYYY3KvoJu2ugA7VHU30Bd4301/H2exFWOMMYVcQQeSu4BZ7vsaqpoA4P6s7u0EEXlQRKJFJPrIkSP5VExjjDFZKbBAIiKlgD7AnNycp6pTVTVCVSOqVasWmMIZY4zxWUHWSG4DflDVQ+72IREJBXB/Hs7yTGOMMYVGQQaSwfzerAWwCBjqvh8KLMz3EhljjMm1AgkkIlIWuBWYny75ReBWEdnm7nuxIMpmjDEmdwokkKjqWVWtoqqJ6dKOqWoXVW3g/jxeEGUzl5eDBw9y1113Ub9+fRo3bkyPHj345ZdfApZf+fLlA3ZtYwqrgh61ZUzeiPsIJjWFyErOz7iPUFX69+9Pp06d2LFjB5s3b2bChAkcOnQo5+sZY3xmgcQUfXEfwSePQuJeQJ2fnzzKirfHEhwczIgRIzyHhoeHc/3119OlSxdatmxJs2bNWLjQ6Y6Lj4+nUaNGPPDAAzRp0oSuXbty7tw5AKZNm0br1q1p0aIFAwcO5OzZswDs2rWLdu3a0bp1a5577jlPPqdPn/aahzGXIwskpuhb9g9IOpcxLekcGz+bRqtWrS46PCQkhAULFvDDDz+wYsUKnnjiCVQVgG3btjFy5Eg2bdpEpUqVmDdvHgADBgxg/fr1/PjjjzRq1Ih33nkHgMcee4w///nPrF+/nquuusqnPIy53FggMUVf4j7v6edPeE1WVf72t7/RvHlzbrnlFvbv3+9p7qpbty7h4eEAtGrVivj4eAA2btxIx44dadasGVFRUWzatAmAb7/9lsGDBwPwpz/9yac8jLnclCzoAhjjt4phbrNWRk3q1GBuTMxF6VFRURw5coSYmBiCg4OpU6cO58+fB6B06dKe44KCgjxNW/fddx8ff/wxLVq0YPr06axcudJznIjkKg9jLjdWIzFFX5cxEFwmY1pwGW4e8RIXLlxg2rRpnuT169eze/duqlevTnBwMCtWrGD37t05ZnHq1ClCQ0NJSkoiKirKk/7HP/6R2bNnA2RIT0xMzHUexhRVFkhM0df8Tuj9GlS8GhDnZ+/XkBaDWLBgAUuXLqV+/fo0adKEyMhIevToQXR0NBEREURFRdGwYcMcs3j++edp27Ytt956a4bjX331Vd58801at25NYqJnNDtDhgzJdR7GFFVSlDsAIyIiNDo6uqCLYYwxRYqIxKhqRF5dz2okxhhj/GKBxBhjjF8skBhjjPGLBRJjjDF+sUBijDHGL0V61JaIHAECMUC/KnA0ANctauxzcNjn4LDPwXE5fA7XqGqeLTFbpANJoIhIdF4OjSuq7HNw2OfgsM/BYZ/DxaxpyxhjjF8skBhjjPGLBRLvphZ0AQoJ+xwc9jk47HNw2OeQifWRGGOM8YvVSIwxxvjFAokxxhi/FLtAIiJXi8gKEflZRDaJyGNuemURWSoi29yfV7rpIiKvich2EYkTkZYFewd5S0SCRGSDiHzqbtcVke/dz+FDESnlppd2t7e7++sUZLnzkohUEpG5IrLF/V60K47fBxEZ5f6f2Cgis0QkpDh8H0TkXRE5LCIb06Xl+t9fRIa6x28TkaEFcS8FpdgFEiAZeEJVGwE3ACNFpDEwGlimqg2AZe42wG1AA/f1IPBW/hc5oB4Dfk63/RIwyf0cfgWGu+nDgV9V9Q/AJPe4y8WrwBeq2hBogfN5FKvvg4jUAh4FIlS1KRAE3EXx+D5MB7pnSsvVv7+IVAbGAm2BNsDYtOBTLKhqsX4BC4Fbga1AqJsWCmx13/8XGJzueM9xRf0FhOH8J7kZ+BQQnCd2S7r72wFfuu+/BNq570u6x0lB30MefAYVgF2Z76W4fR+AWsBeoLL77/sp0K24fB+AOsDGS/33BwYD/02XnuG4y/1VHGskHm51/Hrge6CGqiYAuD+ru4el/QdLs89NuxxMBp4GUt3tKsAJVU12t9Pfq+dzcPcnuscXdfWAI8B7bhPf2yJSjmL2fVDV/cArwB4gAeffN4bi931Ik9t//8vye+GrYhtIRKQ8MA94XFVPZneol7QiP2ZaRHoBh1U1Jn2yl0PVh31FWUmgJfCWql4PnOH3ZgxvLsvPwW2G6QvUBWoC5XCacTK73L8POcnqvovr5wEU00AiIsE4QSRKVee7yYdEJNTdHwocdtP3AVenOz0MOJBfZQ2gPwJ9RCQemI3TvDUZqCQiJd1j0t+r53Nw91cEjudngQNkH7BPVb93t+fiBJbi9n24BdilqkdUNQmYD7Sn+H0f0uT23/9y/V74pNgFEhER4B3gZ1WdmG7XIiBtpMVQnL6TtPR73dEaNwCJaVXeokxVn1HVMFWtg9OpulxVhwArgNvdwzJ/Dmmfz+3u8UX+Ly5VPQjsFZHr3KQuwGaK2fcBp0nrBhEp6/4fSfscitX3IZ3c/vt/CXQVkSvd2l1XN614KOhOmvx+AR1wqpxxQKz76oHTvrsM2Ob+rOweL8CbwA7gJ5xRLQV+H3n8mXQCPnXf1wPWAduBOUBpNz3E3d7u7q9X0OXOw/sPB6Ld78THwJXF8fsAjAO2ABuBD4DSxeH7AMzC6RdKwqlZDL+Uf3/g/9zPYzswrKDvKz9fNkWKMcYYvxS7pi1jjDF5ywKJMcYYv1ggMcYY4xcLJMYYY/xigcQYY4xfLJCYYkNEqohIrPs6KCL7022X8nJ8ZREZ4cN1S4rIiSz2jXFn1I1zp2BpncO1/ici/Xy/K2MKXsmcDzHm8qCqx3CeGUFEIoHTqvpKNqdUBkYAUy4lPxHpiPNg2vWq+puIVMP+z5nLkNVIjAFE5Gl3HY6NIvIXN/lF4Dq3xvKiiFQQkeUi8oNbw+iVw2VDgSOq+huAOtOPJLj5jROR9W5+U9ynyTOXqbWIrBKRGBH5XERquOmjRGSziPwoIv/Lu0/BmEtjDySaYil9jURE2gDTcKZJD8J5UnsQcBaYq6pptZhgIERVT4lIdeBbVW3gzjV1VFUrZcqjAvANzhPiXwGzVfVrd19lVT3uBpCZwAxV/dwNDHOBz3GmJ+mjqkdFZAhwk6o+KCIJwDVuLaeSqnptVjMmv1iNxBjoCMxT1bOqegpnmpQOXo4T4CURiQOWAFeLSNWsLqrOrNItcZrHjgFzReRP7u4uIrIO+BG4CWiS6fRGbtpXIhKLMyNx2qSAm4D/ucElKdd3a0wes/ZaY7xPAe7NvTiz3LZU1WQR2Ycz51SW1FmrYwWwQkQ2A4NEZB7whnud/SIy3st1BIhT1Y5eLtsNJ/j0Bf4uIk1VNcXHezAmz1mNxBhYDfQXkTLuOjV9ga+BU8AV6Y6riLOGS7KI3EoOCxeJSCMR+UO6pBbAbqAMzmJiR0XkCmCgl9M3A7XcZjdEpJSINBGRICBMVZcDTwHVgLK5v2Vj8o7VSEyxp6rrRGQWsN5NektVfwIQkWgR+QlYDEwEPhGRaOAHnJlhs1MeeE1EKgIpOMuyPuj2jbyPM8vubpwVOjOX6YKI3O6efwXO/9V/48wsO9NNKwG85DbHGVNgrLPdGGOMX6xpyxhjjF8skBhjjPGLBRJjjDF+sUBijDHGLxZIjDHG+MUCiTHGGL9YIDHGGOOX/w8eGybpshDGxwAAAABJRU5ErkJggg==\n",
"text/plain": [
"<Figure size 432x288 with 1 Axes>"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"#scatter plot total sales by average sales per customer, grouped by country\n",
"labels = country_analysis_other['country_name']\n",
"x_coords = country_analysis_other['total_sales']\n",
"y_coords = country_analysis_other['average_sales_per_customer']\n",
"\n",
"for i, country in enumerate(labels):\n",
" x = x_coords[i]\n",
" y = y_coords[i]\n",
" plt.scatter(x, y)\n",
" #label the dots with a slight offset\n",
" plt.text(x+10, y-1, country, fontsize=10)\n",
" plt.xlabel('Total Sales')\n",
" plt.ylabel('Avg Sales per Customer')\n",
" plt.title('Sales by Country')\n",
"plt.show()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The USA and our catch-all 'Other' category lead in total sales. Canada is the second most-likely country to buy from us. \n",
"\n",
"Germany, Brazil, UK, and France are clustered around the average number of customers and sales\n",
"\n",
"\n",
"The Czech Republic has only a few customers but a very high average per customer. It's hard to know if thats a trend or a fluke without a few more customers to compare. \n",
"\n",
"This above chart doesn't visualize **total sales** per country. Let's see how those compare."
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAYsAAAFPCAYAAAC8meIpAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nO3debycZX3+8c8FiKDsEKhlCwpFXErFCCjWBequgFVaFAEtGmxREezP4lJA0FatFRUVi6Ci4oKKiIoLi4KoLAmCCKggskQwhC3ssl2/P+57yORkcp6T5MzzTHKu9+t1XmeeZeb+npOT+c69yzYRERHjWanrACIiYvQlWURERKMki4iIaJRkERERjZIsIiKiUZJFREQ0SrKI5Z6k1SRZ0iZDeO3zJL1usl83YnmTZBFDIemuvq+HJd3bd7xXw3NfLOmqSYxlW0lnSrqtfl0o6R8m6/VHkaR9JV0k6W5JN0r6nqQdh1zm0JJ2dG+VrgOIFZPtNXqPJV0DvNH2GW3HIWkl4PvAh4EXUz4g7QA80HYsk63+bNh+eMz5dwNvBfYHzgAeBF4K7Aac13KYC5G0iu0Hu4whlk5qFtEJSatL+lT91DtH0v9IepSk9YFvA4/vq4msL2knSedLmi/pBklHSZrIh52/BjYGPmv7Adt/sX2O7V/WOKZJ+oGkeZJulfQdSY8bJ+79Jf2u3vt9SRvX8ytL+mR9nfmSLpG09WJe4zxJR0qaXe/9lqS1+67/ff1Zb6+1g53GPPcISecD99Sfr/+11wcOBWbaPtX2Pbbvt32K7XeN97uv194s6Yy+11uotiDpa5I+JulHku6U9HNJm9fbz6nff1f/3Xbv1RIl/aekucAx9fgFY8qYL2mbcf8lo1NJFtGV9wF/CzwVeDrwPOCdtm8BXglcbXuN+nULpSbwFmA94O+BVwBvnEA5fwauBb4iaTdJG465vhLwGWAzYIt67qhBLyRpT+DtteyNgF8BX66XX15/jicA6wKvBW4bJ659gL0oiWxV4H9rGdOBU4D31J/1vcApktbte+7r6vPXrD9fv78HDHxvnLIH/u7HuX+s1wLvqvHdWF8P4Dn1+9b13+2UejwdeBSwKfA24Iv1Z+jZDfi97SuWIIZoWZJFdGUv4DDbN9ueC7wf2HtxN9u+wPaFth+y/QfgOOC5TYXUJo/nAnOBjwE31v6LLer1uba/Y/te2/OB/x7ndfcH3m/797YfoLxJPlvSRpRkthbwxPKyvsz2TeOE9nnbv7V9F3AY8Jp6fl/gZNtn2H7Y9mnA5cAL+557nO3f1ZrS2Cad9YG5Hn/RtyX63Q9wku2L6u/gK8DfNdz/F+DIWsO5l5Isdpf0mHp9b+BLS1B+dCDJIlonScBfUT7x91xL+ZS9uOc8qTYXzZV0B6WpZYOJlGf7Wttvtr0F8Ph6+nP1ddeU9DlJ19XX/fE4r7s58JnaPHQ7MI/SH7AJ8APgeOD/gLmSPi1pjcW8DsD1fY+vBR5Tm6I2B17XK6OWM4OFm5v6nzvWLcBG9Xe8iKX53Q/QX5u5Bxjv5wT4c00sANi+hlIr203SNGBn4GtLUH50IMkiWlc/9f6Z8sbYsxnwp94tA572WeAi4Am21wKOAAa+ITaUfS1wDPCUeuoQypv9M+rrvnCc170eeL3tdfq+Vrc928VHbT+N0sSzLXDgOKFs2vd4M+CeWrO5nlJz6C/jsbb7m8bGqzWcW+N/2WJ+/qbf/d3AY/qu/dU4ZS3y8ktw/gRKU9SewFkNtbAYAUkW0ZWvAofVzusNKW30vfb/ucCGYz6ZrwnMt32XpCcDb5pIIZI2knSopMer2BB4PQtGBa1J+XR8u6QNKH0Ei/MZ4L29jmtJ60p6VX28o6QZtdP9buB+4KFxXuv1kv6m/oyHA1+v508A9pC0S+00X70+ntCbtu2bgSOB/5P08vr8R0l6haT/qreN97u/GHiapCfXZqJDJ1JuLfsvwHwW1N7G803g2cC/UpqlYsQlWURXDqW0xV9GeYP6OWV4K8AlwKnAtbUpZj3gIOCNku4CPsWCN9cm9wFbAT8F7qyvfRsLOsc/Qml2uoXyqfy0xb2Q7a8CnwROrk1WFwO9UT3rAF8AbgeupjTtfGKcuL5EedP+E/Aw8I5axtXAqyj9ITfX1zmQJfi/avsDlARwZH2N64CZwHfqLYv93du+tD7+GfBbyu9tSRwKfKP+u+06Tox3At+lNH+duoRlRAeUzY8i2iXpPOCTtr/cePMKrNZ0NrQ9kVFt0bFMyouI1tWO7dcDu3ccSkxQmqEiolWS3gJcA3zD9gUdhxMTlGaoiIholJpFREQ0SrKIiIhGK2QH9wYbbODp06d3HUZExHJl9uzZN9ueNujaCpkspk+fzqxZs7oOIyJiuSLp2sVdSzNUREQ0SrKIiIhGSRYREdEoySIiIholWURERKMki4iIaJRkERERjZIsIiKi0Qo5KS8iYkUz/ZDvL/NrXPPBgbvtTkhqFhER0SjJIiIiGiVZREREoySLiIholGQRERGNkiwiIqJRkkVERDRKsoiIiEZJFhER0SjJIiIiGmW5j4gYWV0vcRELpGYRERGNkiwiIqJRkkVERDQaWrKQ9DlJN0n6Td+59SSdLunK+n3del6SPiHpKkm/lrRd33P2rfdfKWnfYcUbERGLN8yaxReAF485dwhwpu2tgDPrMcBLgK3q10zgGCjJBTgM2AHYHjisl2AiIqI9Q0sWts8Bbh1zejfghPr4BGD3vvNfdHEesI6kxwEvAk63favt24DTWTQBRUTEkLXdZ7GR7RsB6vcN6/mNgev77ptTzy3u/CIkzZQ0S9KsefPmTXrgERFT2ah0cGvAOY9zftGT9rG2Z9ieMW3atEkNLiJiqms7WcytzUvU7zfV83OATfvu2wS4YZzzERHRoraTxalAb0TTvsB3+s7vU0dF7QjMr81UPwJeKGnd2rH9wnouIiJaNLTlPiR9FXgesIGkOZRRTR8ETpK0H3AdsEe9/TTgpcBVwD3AGwBs3yrpSODCet8Rtsd2mkdExJANLVnYfs1iLu0y4F4DByzmdT4HfG4SQ4uIiCU0Kh3cERExwpIsIiKiUZJFREQ0SrKIiIhGSRYREdEoySIiIholWURERKMki4iIaJRkERERjZIsIiKiUZJFREQ0SrKIiIhGSRYREdEoySIiIholWURERKMki4iIaJRkERERjZIsIiKiUZJFREQ0SrKIiIhGSRYREdEoySIiIholWURERKMki4iIaJRkERERjZIsIiKiUZJFREQ0SrKIiIhGnSQLSQdJukzSbyR9VdJqkraQdL6kKyV9XdKq9d5H1+Or6vXpXcQcETGVtZ4sJG0MvA2YYfspwMrAnsCHgKNsbwXcBuxXn7IfcJvtLYGj6n0REdGirpqhVgFWl7QK8BjgRmBn4Jv1+gnA7vXxbvWYen0XSWox1oiIKa/1ZGH7T8BHgOsoSWI+MBu43faD9bY5wMb18cbA9fW5D9b7128z5oiIqa6LZqh1KbWFLYC/Bh4LvGTAre49ZZxr/a87U9IsSbPmzZs3WeFGRATdNEP9A/BH2/NsPwCcDDwLWKc2SwFsAtxQH88BNgWo19cGbh37oraPtT3D9oxp06YN+2eIiJhSVmm+BST9HfD3lJrAvcBvgDNtz1+KMq8DdpT0mPpauwCzgJ8Arwa+BuwLfKfef2o9/mW9fpbtRWoWERExPOPWLCS9TtJs4H3AusC1wB2U2sFPJR0vaZMlKdD2+ZSO6ouAS2sMxwL/ARws6SpKn8Tx9SnHA+vX8wcDhyxJeRERseyaahbrA8+xffegi5JmANtQmoomzPZhwGFjTl8NbD/g3vuAPZbk9SMiYnKNmyxsf7zh+qzJDSciIkZRY5+FpPWBe23fI2k14EBgTeBo23OHHWBERHRvIqOhTgJ6w4sOB55M6Zj+6pBiioiIEdPUwb0XsCWwU328J/AL4BpguqTXSnrK0KOMiIhONTVDnQfcB/yBMr9hHvBjykS5W+r124YZYEREdK+pg/sPko4GTgFWBd5g+2pJmwI32766jSAjIqJbjR3ctj8p6UvAw7bvrKdvA/YaamQRETEyJjSDe+xMbdt3DSeciIgYRdkpLyIiGiVZREREoySLiIhoNKFkIWk3SVdImi/pDkl3Srpj2MFFRMRomFAHN/C/wCttXzrMYCIiYjRNtBlqbhJFRMTUNdGaxYWSTqRMzvtL76TtU4cSVUREjJSJJov1gYeBXfvOmbKLXURErOAmOilv72EHEhERo2vcZCHpHbb/V9JHB123ffBwwoqIiFHSVLP4Q/1+2bADiYiI0dW06uwp9fvx7YQTERGjqGnzo2MkbbOYa6tL2kfSa4YTWkREjIqmZqjjgA9I2hr4NWXzo9WArYANgC8AnxpmgBER0b2mZqjZwD9KWgvYHngcZf/tj9tOP0ZExBQx0aGzdwBnDDmWiIgYUVl1NiIiGiVZREREoyVOFioeO4xgIiJiNE10P4svSlpL0mMoE/T+KCmztyMipoiJ1iyeWju5dwd+DGwCvH5YQUVExGiZaLJYVdIqwG7AKbbvp6xCu1QkrSPpm5J+W3fge6ak9SSdLunK+n3deq8kfULSVZJ+LWm7pS03IiKWzkSTxXHAdcC6wNmSNgPuWoZyPw780PYTgW2BK4BDgDNtbwWcWY8BXkKZBLgVMBM4ZhnKjYiIpTChZGH7KNt/bfuFtg3MAXZemgLrBL/nAMfX177f9u2UWssJ9bYTKE1e1PNfdHEesI6kxy1N2RERsXQm2sE9TdL/SfpePfVE4LVLWebjKcuGfF7SryQdV0dXbWT7RoD6fcN6/8bA9X3Pn1PPRURESybaDPUF4Gxg03p8JfCOpSxzFWA74BjbTwPuZkGT0yAacM6L3CTNlDRL0qx58+YtZWgRETHIRJPFhra/Qu3Utv0A8NBSljkHmGP7/Hr8TUrymNtrXqrfb+q7f9O+528C3DD2RW0fa3uG7RnTpk1bytAiImKQiSaLuyWtR/1EL+kZwJ1LU6DtPwPX15VsAXYBLqfs571vPbcv8J36+FRgnzoqakdgfq+5KiIi2jGhhQSBfwe+Czxe0tmUPoNXL0O5bwVOlLQqcDXwBkriOknSfpSRV3vUe08DXgpcBdxT742IiBZNdNXZWZKeD2xD6UO4vM61WCq2LwZmDLi0y4B7DRywtGVFRMSyGzdZSNp1MZc2k4TtU4cQU0REjJimmsUe41wzpT8hIiJWcE075e3dViARETG6JtrBjaQXAU+m7MENgO3/GkZQERExWiaULCR9GliHskzH54FXAecNMa6IiBghE51n8WzbrwVusf2fwA6UyXERETEFTDRZ3Fu/3yfpr4D7gOlDiSgiIkbORPssfiBpHeAjwMWUpT5OGP8p0WT6Id9f5te45oMvm4RIIiLGN9FJeYfXh9+oK8+ubvvWoUUVEREjZdxmKElPl7RR3/FewJeB99aaRkRETAFNfRbHAg8CSHo2pRnqJEqfxbHDDS0iIkZFUzPUKrZvqY/3BI61/XXg65IuGW5oERExKppqFitLWrk+3gU4q//acEKKiIhR01SzOAn4iaR5wP3AzwAkPQG4Y8ixRUTEiGhaG+oISWcBjwN+aPvheulRwNuGHVxERIyGxqGzts8dcO63wwknIiJG0URncEdExBSWZBEREY2SLCIiolHTtqq3UXbEW+QSZXvs9YYSVUREjJSmDu4NWokiIiJGWtPQ2Yf6jyWtR99OecANwwgqIiJGy4T6LCS9TNLvgTnA+fX7WeM/KyIiVhQT7eD+ALAT8DvbmwIvAn46rKAiImK0TDRZPGh7HrCSJNk+HdhuiHFFRMQImehOefMlPRY4F/iipJuAhxueExERK4iJ1ix2p+xh8XZK89OfgJcPKaaIiBgxE00W77L9kO0HbB9v+6PAwcMMLCIiRsdEm6FeDLx7zLmXDTi33Jh+yPeX6fnXfPBlkxRJRMToa9qDe39JvwK2lnRR39eVwOXLUrCklSX9StL36vEWks6XdKWkr0tatZ5/dD2+ql6fvizlRkTEkmtqhjoJ2AM4rX7vfe1k+zXLWPaBwBV9xx8CjrK9FXAbsF89vx9wm+0tgaPqfRER0aJxk4Xt22xfZXsPYHXgBfVr2rIUKmkTSjPWcfVYwM7AN+stJ1A61QF2q8fU67vU+yMioiUTncF9AKWWsVn9OknSvy1DuR8D3smC4bfrA7fbfrAezwE2ro83Bq4HqNfn1/sjIqIlE+3g3h/Y3vZdAJL+C/gF8OklLVDSy4GbbM+W9Lze6QG3egLX+l93JjATYLPNNlvSsCIiYhwTHTor4IG+4wcY/CY+ETsBu0q6BvgapfnpY8A6knrJaxMWLFI4B9gUoF5fG7h17IvaPtb2DNszpk1bplayiIgYo2k/i1Vq08+XgPMkfateeiUL+hGWiO13Ae+qr/884N9t7yXpG8CrKQlkX+A79Smn1uNf1utn2R60x0Ysx5Z1KDNkOHPEMDXVLC4AsP1hShPPPcC9wJttf2SSY/kP4GBJV1H6JI6v548H1q/nDwYOmeRyIyKiQVOfxSNNTbYvBC6czMJt/5S6eq3tq4HtB9xzH2W4bkREdKQpWUyTtNhlPeqyHxERsYJrShYrA2uw9J3ZERGxAmhKFjfaPqKVSCIiYmQ1dXCnRhEREY3JYpdWooiIiJHWtDbUIpPfIiJi6pnoDO6IiJjCJro2VMSUkE2xIgZLzSIiIholWURERKMki4iIaJRkERERjZIsIiKiUZJFREQ0SrKIiIhGSRYREdEoySIiIholWURERKMki4iIaJS1oSLrIUVEo9QsIiKiUZJFREQ0SrKIiIhGSRYREdEoHdwREQ0yCCQ1i4iImIAki4iIaJRkERERjZIsIiKiUevJQtKmkn4i6QpJl0k6sJ5fT9Lpkq6s39et5yXpE5KukvRrSdu1HXNExFTXRc3iQeAdtrcBdgQOkPQk4BDgTNtbAWfWY4CXAFvVr5nAMe2HHBExtbWeLGzfaPui+vhO4ApgY2A34IR62wnA7vXxbsAXXZwHrCPpcS2HHRExpXXaZyFpOvA04HxgI9s3QkkowIb1to2B6/ueNqeei4iIlnSWLCStAXwLeLvtO8a7dcA5D3i9mZJmSZo1b968yQozIiLoKFlIehQlUZxo++R6em6veal+v6menwNs2vf0TYAbxr6m7WNtz7A9Y9q0acMLPiJiCupiNJSA44ErbH+079KpwL718b7Ad/rO71NHRe0IzO81V0VERDu6WBtqJ2Bv4FJJF9dz7wY+CJwkaT/gOmCPeu004KXAVcA9wBvaDTeiXcu6DhFMzlpEWQ8p+rWeLGyfy+B+CIBdBtxv4IChBhUREePKDO6IiGiUZBEREY2SLCIiolGSRURENEqyiIiIRkkWERHRKMkiIiIaJVlERESjJIuIiGiUZBEREY2SLCIiolGSRURENEqyiIiIRkkWERHRKMkiIiIaJVlERESjJIuIiGiUZBEREY2SLCIiolGSRURENEqyiIiIRkkWERHRKMkiIiIaJVlERESjJIuIiGiUZBEREY2SLCIiolGSRURENEqyiIiIRstNspD0Ykm/k3SVpEO6jiciYipZLpKFpJWBTwEvAZ4EvEbSk7qNKiJi6lgukgWwPXCV7att3w98Ddit45giIqaM5SVZbAxc33c8p56LiIgWyHbXMTSStAfwIttvrMd7A9vbfmvfPTOBmfVwa+B3y1jsBsDNy/gak2EU4hiFGGA04kgMC4xCHKMQA4xGHJMRw+a2pw26sMoyvnBb5gCb9h1vAtzQf4PtY4FjJ6tASbNsz5is11ue4xiFGEYljsQwWnGMQgyjEsewY1hemqEuBLaStIWkVYE9gVM7jikiYspYLmoWth+U9BbgR8DKwOdsX9ZxWBERU8ZykSwAbJ8GnNZikZPWpLWMRiGOUYgBRiOOxLDAKMQxCjHAaMQx1BiWiw7uiIjo1vLSZxERER1KsoiIiEbLTZ9FtEfSAcCJtm+vx+sCr7H96W4jixhM0jNsX9hieSsDLwOm0/c+avujLcawErCj7V+0Ul76LEDSeuNdt31rW7H0SNoQWK0vhutaLPti23835tyvbD+trRjGlP1sYCvbn5c0DVjD9h9bKvsjwOe7Hn1Xf+7/oKyN1v93sXNL5V8KDHqzUAnDf9tGHAsVXNaH2xN4DTC/zXkOkk4D7gMuBR7unbf9vrZiqHH80vYz2ygrNYtiNuU/ggZcM/D4tgKRtCvwv8BfAzcBmwNXAE9uKwZgJUly/SRRP0Wt2mL5j5B0GDCDMiv/88CjgC8DO7UUwm+BYyWtUsv/qu35LZXd70Tg65RPs28G9gXmtVj+y1ssa7EkbU5JDq8BHqT8/5hh+5qWQ9mkiwQ5wI8lvQo42UP+5J+axYiRdAmwM3CG7adJej6lCWhmw1MnM4b/oVSvP0NJlm8Grrf9jrZi6IvlYuBpwEW9mo2kX7f9H1XS1sAbKG9SPwc+a/snLZY/2/bT+392SWfbfm5bMXRN0i+AtSkLiX7N9pWS/mh7iw5i+RBwpu0ft132mDjuBB4LPATcy4Ka3lqTXVZqFoCkJ9r+raTtBl23fVGL4Txg+xZJK0layfZP6h9mm/4D2B/4V8of34+B41qOoed+25bUq+U8tu0Aas3qifXrZuAS4GBJ+9ves6UwHqjfb5T0MspyN5u0VPYjJO0IHA1sQ6ltrgzcPYw3pwHmUX7mjYBpwJUMbhprw3nAt2u/wQMM8U16PLbXbKus1CwAScfanilp0CdFt9UuXGM5A9gd+G/KwmA3Ac+w/ay2Yhglkv4d2Ap4AeV38i/AV2wf3VL5HwV2Bc4Ejrd9Qd+139neuqU4Xg78jLJG2tHAWsD7bLe67I2kWZR+gm9Qmgf3Aba0/Z6Wyl8beBWlhrclsA5lkdELxn3i5MdxNeX/6aXDbv5piEPAXsAWto+UtCnwuGH8PpIsRkz95Hwf5ZPKXpRq94m2b2mh7JNs/9PiOjO7aqOV9ALghZTfyY9sn95i2f9CafK4Z8C1tTvqv+hMb7G6Mc1hv+jiw4ykjYB/piSvTW1v2vCUySz7R8BLbD/cePNw4ziG0sG+s+1t6sjFH9t+xqSXlWSxgKQ/AP9j+zN9575neyQ694ZN0uNs31g7ERdh+9oOYtoCuNH2ffV4dWCjNjs0JW1M6UjtHyJ5Tktlv9P2hyUdzeAE/rY24uiL5xzgHyjNkn8GbgReb3vbNuPoi2dd4HZgszb/PiV9gTLw5QfAX3rn2xw6W+O4yPZ2/aMVJV0yjH+P9Fks7AHg+ZJ2APavu/K1sslS7ahabOZuoy3U9o31e+tJYRzfAPo/tT5Uz036J6dBJH2Q8sn18lo2lH+nVpIFZSQcwKyWymuyN6Wf4i3AQZRmsVe1UbCkQ4GTav/io4EfAttSRkW9Fmjz7/aP9WtVOhopWD1Q+9R6fXrT6BvKO5mSLBZ2j+1/lvRO4GeS/omWOtB6HVWSjqB8YvsSC5qiWunEGpCwxIIhxa133lWr1KQNJYj76zL1bXklsLXtvzTeOQS2v1u/n9BF+WP1fZC4F2h1TgGlyenI+njf+n0a8DfACcAZbQXSm08hac1y6LvaKnuMTwDfBjaU9AHg1cB7h1FQksXCBFCr/bMpS6KPO2FvCF5ke4e+42MknQ98eNgFtzmyYgnMk7RrryNX0m60uyPZ1ZS5HZ0kC0nfZfwa564thrO4yXnzKTWf9w+5b+3+vs7kF1H6kh4CrpD0qCGWuwhJT6F8oFuvHt8M7NP25E3bJ9b3ql0o71+7276i4WlLJcliYYf2Htg+U9ILgde3HMNDkvaijCU3ZdTHQ+M/ZfLVYcTPrjGca/tXbcdQvRk4UdInKf8ZrqeMwGnLPcDFks5k4bbptvoKPlK//yPwV5QJiVD+Lq5pKYZ+P6D8PX6lHu9J+XeZD3wBeMUQy/5LfZOeCzwf+Pe+a6sPsdxBjgUO7s21kfQ84LMs3GTaliuBO6jv55I2G8aKD+ngHqN2mG3FwksqtNU+jaTpwMcpM5RNmQD29pY7dA8F9gBOrqd2B75h+/1txTAgpjUof693tlzuvoPOt90sJOkc289pOtdCHD+3vdOgc5Iutf3UIZa9A6W5aRpwVO/vUdJLgb1tv2ZYZQ+IZZFO5GF1LDfE8VbgMEoCfYghLr+SZNFH0huBAykTfy4GdgR+2eY8i1Eg6QrgaWNGIF1ke5sOYnk0pQN1OguPRjqi7Vi6VP9NXmb76nq8BXBa2/8mdYWBmbbPr8fbU2azb6shrx8m6WAWLMnj+nUzpebbylphfbF8G7iI0hQF8DrKsiO7txzHVcAObQytTzPUwg6kjLI5z/bzJT2RljvxJK0G7EdZC6q/dvMvLYZxTS37vnr8aOAPLZbf7zuUJo7ZdNBvIGkrymTAsQv4tbZeWHUQ8NM6GQxK8ty/5RgA3gh8rlfTozR/vLHOD/rvIZc9qE9tOvAeSYfb/tqQy+/3L5T3hpMpv4dzKMvBtO16yv+PoUvNoo+kC20/Q2U9oh1s/0UDVmAdcgzfoCxe91rgCMpoqCtsH9hC2b2x/JtRkubp9fgFlE9vbS1t0R/Tb2w/pe1y+8o/l1LNP4rSHv8Gyv+bwzqI5dGUJUcAftvVCK0ay9qU38PtXcXQF8t6lLXUBi7XsyKqtSwoHyq3Br7PkOd7pGaxsDmS1gFOAU6XdBtlDZ42bWl7D0m72T5B0lcoo7La0BvLP5syHK/npy2VP8gvJD3V9qUdlb96HeygOmz0cEk/oySQtj2dBc1x20rC9hfbDKD2Z/UfA902C9q+Vb1AhmyERqf1alnX1a/++R5DqQEkWfSx/cr68HCVdaLWpkz8aVNvwbjb68iPP1PeIIZuVMbyj/Fs4PWS/kj55NT2/gn3qSwWd6WktwB/AjZsqexHSPoS8ARKX1r/5MBWkwVwd9/j1ShLlw9lqOZESdoZuK2l4kZidFrfPI89bH+j/5qkPYZRZpqhqvqG8OsumzxqHG8EvgX8LWX/hDWAQ/uXIGkhhj8yeGmJttvpe/sXLKKtWeaSnkF5M1yHMiFsbeDDts9ro/y+OK4AnkAyl5MAAAwcSURBVOQR+w9bm8ZOtf2iFsoaNMdjPUrtfx/bvx12DH2xjMrotIvGNr8NOjcZUrOobD8s6ZJhjVFegjh6S4GfTYubLo3Rv+PYapRhtG1PTgQWJAWN2TmwxfJ7W3XeRTcdmD2/oXySvbHDGAZ5DO39nY5do83ALbbvHnTzkE2T9Pgxo9OmtVW4pJcALwU2lvSJvktrUZY/mXRJFgt7HHCZpAvoq263OUt2FIaKDhiG97Ha0XvooPuHSR3vHChpBvAeFl1IsO0VeDcALq9/m/0dmV3O4F6Z8gZ55OKfMXlGbM2yrken3UDpY9wD+D3l3+QhynyLg4ZRYJLFwtpe62aQToeKwiOzt3tWotQ0uloK5EjKfJeFdg5ssfwTgf/HmL2WO3B4h2X36/90/yAw1/ZQPsmOMts/rMOquxqddjllpOSqlGG8oizq+Hnge8MoMH0WiyFpA0oVt9VfUNdDRWsM/ZtAPUjpuPuI7d91EEtv/4RLKBMFH5Z0ge3tWyr/XNvPbqOs5YGkL9neu+ncik7SPw44PZ+yGdJNLZR/FKU/8+DeqgaS1qJ0wN87jKH2qVlAb6vIDwK3Uj7JfolS7V9J0j622xwR1fVQUWw/v6uyB7i9TgA7h7JG1E0MqU12MQ6TdBxlp7z+5p+TF/+UyaPFL13f1UrACzX/SVqFMqR3qtkPeCZwFuXf4nmUrVb/RtIRtr80znMnw8uBv+n/MGv7Dkn/SpmnlWQxJJ8E3k0Z6XIWZQes8+oM7q/S7vDZroeKIml9yjyCRxYSBI5oY0mBAXajLId9EAt2DmxzTP8bKE0Nj2JBM5RZsG7WUHlEVgKW9C7K/5HVJd3ROw3cT1lUb6p5GNjG9lygt2vfMcAOlA82w04WHtTqYfsh1f3qJ1uaoYD+WdqSruhfb2fY690MiKXzXeoknU75g++NId8LeJ7tf2grhhrHypRtVFstd0wMQ10cb3lSh5cf1/LSMyNp7N9FnRR4qe2ntPGeIekU4OSxkzIlvQ74p2EMfEjNoujvuLx3zLVWs2nXQ0Wr9Wz3j3B5v6RWF0iDRz4l3aNu97o+T9KTbF/eUfkjo/YXdbJ96gj6maTvUXZthDKC8Zy6RlYbS6AcAJysskf8bMr71DMoS7W/crwnLq3ULABJD1GGyoryy76ndwlYzXZrG6ssbqio7VaGitYYPkIZlndSPfVq4MkdrYd0EmU01OksPJy5lf0k6mS4J1C20OykWXCUSPoU8IW++SdTUq1JvIqylYAoTbXf6mBAzM6UfiQBl9k+c2hlJVmMljrqZ2fGDBW1PbPFGO4EHkupcZkynr73Rt1qp6o63k9iFJoFR4mkyynbmF7Lgg9YUzZ5TiVJFiOm66Gio6LrmfQ1hpFYAmaUJHkWdejshyjrhInuRqe1ZqWuA4hFjB0q+nHaHSqKitdJ+s96vKnKJjdtOqUvnm+1XDZQ2uiBSyRt1kX5o6gmhXUoy7W/AlhnqiWK6sPArrbXtr2W7TVX5EQBSRYjQ9KWknaiDBW9hzJU9IfALcBbWw7n05Qx5K+tx3cBn2o5hv4lp7taIwsWLAFzpqRTe18dxtMpSQdSZrVvWL++rLK151Qz13anq+22LaOhRsfHgHf3LYr2MHBCXZvocMqnuLbsYHs7Sb8CsH2bpFWbnjTJvJjHbRuFJWBGyX6Uv4+7ASR9CPglcHSnUbVvlqSvU2rArU/W7EKSxeiYbvvXY0/aniVpesuxPFDnOBhA0jTaXxdp2zr5Syw6Eay1tmHbZ9d2+q1snyHpMZQO/6lKLNhPg/q4lY2HRsxalBaAF/ada22yZheSLEbHeHMqVm8tiuITlJ3yNpT0AcrQ2fe2GYDtkXhDlvQmYCZlifYnABsDnwF26TKuDn0eOF9SbyfF3YHjO4ynE7a7XK6+ExkNNSIkfRU4y/Znx5zfD3ih7X9uOZ4nUt4QBZw51dpne1T2Y98eOL83K3eqz+quqxI/m/K3cY7tX3UcUmskvdP2h7Vgv/qFtDX/pwupWYyOtwPflrQXZUYmlKXBV2VIMzLH47Lr2G8BJK0j6T22P9B2HCPgL7bvV93iuS6cN+U+YUlaDXgzsCVlufZPT8WlyVmwheysce9aASVZjIi6INmz6iS83rj+79s+q60YJG0K/Cdl9vgpwFcoq/DuTVlQcSo6W1JvAb0XAP8GfLfjmLpwAmV/+J8BLwG2oXzAmWqug8GTQuuKryusNEPFI1T2sTibMrrlxZRmqMuAg2z/ucvYulIn5u3Hgo7MH3nB1rdTRn/TW61dXeAh7PM86lR2xtvD9uwx598HvGJF/p0kWcQjJF1ie9u+47nAZm53B7CRIGk3YBPbn6rHF1C2EDXwTtvf7DK+tkm6qP+NcOzxVCHp6ZTFA/ey/cu6RtQxlCVQdrd9x7gvsBxLM1QsRNK6LBgK+WfgMXUlTWzf2llg7XsnsGff8aqUTX7WoIwImlLJggVDmWHh4cwr/DIX/WzPriswf1vSAcCb6qUX276/w9CGLski+q1N6VzvHzd/Uf1uup1J3bZVbV/fd3xuTZa39pLnVDIqQ5m7Jmk9YA6wL6Vf7wzgLcAaklboD1RphooYQNJVtrdczLU/2H5C2zFF91R2sOy9afY+VJkFNawV9gNVahYRg50v6U0D5r3sD1zQUUzRMdtbdB1DV1KziBig7lTYW/en1xT3dODRlI7MuV3FFtGFJIuIcfTtRAZlJ7LW5r1EjJIkixioLiS4EX1NlV1vRhQR3UmfRSyi7k9wGDCXBavNGsjWmTGl1dFQi5XRUDGlSLqKsmfBLV3HEjFK+kZDCdgMuK0+Xge4bkXuAM9OeTHI9cD8roOIGDW2t6jDY39EWd5jA9vrAy9nBd7LAlKziD6SDq4PnwxsDXyfhXcB+2gXcUWMGkmzbT99zLlZtmd0FdOwpc8i+q1Zv19Xv1atXxGxsJslvRf4MqVZ6nXACt1sm5pFRMQSqh3dhwHPoSSLc4Aj0sEdU4qk0ynLMN9ej9cFvmb7Rd1GFjFaJK1h+66u42hDOrhjkGm9RAFg+zZgww7jiRgpkp4l6XLg8nq8raRPdxzWUCVZxCAPSdqsdyBpc6bgVqIR4zgKeBG1n8L2JZQmqRVWOrhjkPcA50o6ux4/B5jZYTwRI8f29b292auHuoqlDUkWsQjbP5S0HbAjZcLRQbZv7jisiFFyvaRnAZa0KvA24IqOYxqqNEPFIupWkS8GtrP9Xcpuedt3HFbEKHkzcACwMWUzpL8D/q3TiIYso6FiEZKOoawJtbPtbepoqB/bfkbHoUWMBEk72f5507kVSWoWMcgOtg8A7oNHRkNlcl7EAkdP8NwKI30WMcgDdYlyA0iaxoLVZyOmLEnPBJ4FTOtbHgdgLWCF3qc8NYsY5BPAt4ENJX0AOBf4r25DihgJqwJrUD5or9n3dQfw6g7jGrr0WcRAkp4I7EIZDXWm7RV6pEfEkpC0ue1ru46jTUkWsQhJxwNH276479zhtg/vLqqI7kn6mO23S/ouAyaq2t61g7BakWQRi5A0B7gZ+KjtL9ZzF9nertvIIrol6em2Z0t67qDrts8edH5FkGQRi5B0EfA84ETKUuUHAhfaflqXcUVEd9LBHYPI9h22XwHMA84G1u44poiRIWknSadL+r2kqyX9UdLVXcc1TBk6G4Oc2ntg+3BJs4CDx7k/Yqo5HjgImM0KviZUT5qh4hGStgQ2GjAz9TnAn2z/oZvIIkaLpPNt79B1HG1KM1T0+xhw54Dz99RrEVH8RNL/SHqmpO16X10HNUypWcQjJP3G9lMWc+1S209tO6aIUSTpJwNO2/bOrQfTkvRZRL/Vxrm2emtRRIw428/vOoa2JVlEvwslvcn2Z/tPStqP0pEXMaWNWQ8KysS8m4Fzbf+xg5Bak2aoeISkjShrQt3PguQwg7Iezitt/7mr2CJGgaTDBpxej7LF6uG2v9ZySK1JsohFSHo+0Ou7uMz2WV3GEzHqJK0HnLEir3KQZBERMQkk/WpFXuUgQ2cjIpaRpJ2B27qOY5jSwR0RMUGSLmXR1WbXA24A9mk/ovakGSoiYoIkbT7mlIFbbN/dRTxtSrKIiIhG6bOIiIhGSRYREdEoySIiIholWURERKMki4iIaPT/AdIJjLP6/pAZAAAAAElFTkSuQmCC\n",
"text/plain": [
"<Figure size 432x288 with 1 Axes>"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"# plot the total sales for each country on a bar chart\n",
"total_sales_chart = country_analysis_other['total_sales'].plot.bar().set_xticklabels(country_analysis_other['country_name'])\n",
"plt.title('Total Sales per Country')\n",
"plt.ylabel('Total Sales (in $)')\n",
"plt.show()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Total sales trends mostly mirror our other data so far: USA and our 'Other' category are clear leaders, with Canada coming in third. \n",
"\n",
"India, Portugal, the UK, and the Czech Republic, despite it's large average per customer, make up the countries with the least sales.\n",
"\n",
"Let's look at 2 more metrics before making a conclusion: the *total customers* and *the average order value* per country."
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAXAAAAE/CAYAAABfF5iGAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nO3da5RlVXnu8f8DiIDcJBTEA7RNhKDGhACNqDiMYFAMChglgaCioq05ahCTQ0hIBDWeEPWokXgZeCWKGC9gVIyCqCARwe4G5NIYFRBQhEYEVFRuz/kw17Z3F9VV1VV7r7Vm1/Mbo0bVWntXzbe7dr17rrneOadsExER9dmg6wAiImJuksAjIiqVBB4RUakk8IiISiWBR0RUKgk8IqJSG7XZ2LbbbuvFixe32WRERPWWL19+m+2JyedbTeCLFy9m2bJlbTYZEVE9ST+Y6nyGUCIiKpUEHhFRqSTwiIhKJYFHRFQqCTwiolJJ4BERlUoCj4ioVBJ4RESlWp3IExGxvll8/Nnz+v7rTz5ozt+bHnhERKWSwCMiKpUEHhFRqSTwiIhKJYFHRFQqCTwiolJJ4BERlUoCj4ioVBJ4RESlZkzgkj4o6VZJV07x2N9IsqRtxxNeRESszWx64B8GDpx8UtJOwAHADSOOKSIiZmHGBG77AuD2KR56O3Ac4FEHFRERM5vTGLikg4Ef2r58xPFERMQsrfNqhJI2A04Anj7L5y8FlgIsWrRoXZuLiIi1mEsP/FHAzsDlkq4HdgRWSPrtqZ5s+1TbS2wvmZiYmHukERGxhnXugdu+AthucNwk8SW2bxthXBERMYPZlBGeAVwE7CbpJklHjz+siIiYyYw9cNtHzPD44pFFExERs5aZmBERlUoCj4ioVBJ4RESlksAjIiqVBB4RUakk8IiISiWBR0RUKgk8IqJSSeAREZVa57VQIiIWH3/2vL7/+pMPGlEkC1t64BERlUoCj4ioVBJ4RESlksAjIiqVBB4RUakk8IiISiWBR0RUKgk8IqJSSeAREZWazabGH5R0q6Qrh869RdI1kr4t6SxJW483zIiImGw2PfAPAwdOOncu8DjbfwD8D/B3I44rIiJmMGMCt30BcPukc+fYvq85/Caw4xhii4iIaYxiDPwlwH+N4OdERMQ6mFcCl3QCcB9w+jTPWSppmaRlq1atmk9zERExZM4JXNJRwLOAI217bc+zfartJbaXTExMzLW5iIiYZE7rgUs6EPhb4I9s3z3akCIiYjZmU0Z4BnARsJukmyQdDfwbsAVwrqTLJL13zHFGRMQkM/bAbR8xxekPjCGWiIhYB5mJGRFRqSTwiIhKJYFHRFQqCTwiolJJ4BERlUoCj4ioVBJ4RESlksAjIiqVBB4RUakk8IiISiWBR0RUKgk8IqJSSeAREZVKAo+IqFQSeEREpZLAIyIqlQQeEVGpJPCIiEolgUdEVGo2mxp/UNKtkq4cOreNpHMlfbf5/PDxhhkREZPNpgf+YeDASeeOB86zvStwXnMcEREtmjGB274AuH3S6UOA05qvTwMOHXFcERExg7mOgW9v+2aA5vN2a3uipKWSlklatmrVqjk2FxERk439JqbtU20vsb1kYmJi3M1FRCwYc03gt0h6BEDz+dbRhRQREbMx1wT+WeCo5uujgP8cTTgRETFbsykjPAO4CNhN0k2SjgZOBg6Q9F3ggOY4IiJatNFMT7B9xFoeetqIY4mIiHWQmZgREZVKAo+IqFQSeEREpZLAIyIqlQQeEVGpJPCIiEolgUdEVCoJPCKiUkngERGVSgKPiKhUEnhERKWSwCMiKpUEHhFRqSTwiIhKJYFHRFQqCTwiolJJ4BERlUoCj4io1LwSuKRjJV0l6UpJZ0jaZFSBRUTE9OacwCXtAPwVsMT244ANgcNHFVhERExvvkMoGwGbStoI2Az40fxDioiI2ZhzArf9Q+CtwA3AzcCdts8ZVWARETG9jeb6jZIeDhwC7AzcAXxS0vNtf3TS85YCSwEWLVo0j1Dbs/j4s+f9M64/+aARRBIRsXbzGUL5Y+A626ts3wucCTxp8pNsn2p7ie0lExMT82guIiKGzSeB3wA8QdJmkgQ8DVg5mrAiImIm8xkDvxj4FLACuKL5WaeOKK6IiJjBnMfAAWyfCJw4olgiImIdZCZmRESlksAjIiqVBB4RUakk8IiISiWBR0RUKgk8IqJSSeAREZVKAo+IqFQSeEREpZLAIyIqlQQeEVGpJPCIiEolgUdEVCoJPCKiUkngERGVSgKPiKhUEnhERKWSwCMiKpUEHhFRqXklcElbS/qUpGskrZT0xFEFFhER05vXpsbAvwJftP08SRsDm40gpoiImIU5J3BJWwJPAV4EYPse4J7RhBURETOZzxDK7wCrgA9JulTS+yU9bERxRUTEDOaTwDcC9gTeY3sP4BfA8ZOfJGmppGWSlq1atWoezUVExLD5JPCbgJtsX9wcf4qS0Ndg+1TbS2wvmZiYmEdzERExbM4J3PaPgRsl7dacehpw9UiiioiIGc23CuXVwOlNBcq1wIvnH1JERMzGvBK47cuAJSOKJSIi1kFmYkZEVCoJPCKiUkngERGVSgKPiKhUEnhERKWSwCMiKpUEHhFRqSTwiIhKJYFHRFRqvlPpR2rx8WfP+2dcf/JBI4gkBvI7ieiv9MAjIiqVBB4RUakk8IiISiWBR0RUKgk8IqJSSeAREZVKAo+IqFQSeEREpeadwCVtKOlSSZ8fRUARETE7o+iBHwOsHMHPiYiIdTCvBC5pR+Ag4P2jCSciImZrvj3wdwDHAQ+MIJaIiFgHc17MStKzgFttL5f01GmetxRYCrBo0aK5NhcLWBbUipjafHrg+wIHS7oe+Diwv6SPTn6S7VNtL7G9ZGJiYh7NRUTEsDkncNt/Z3tH24uBw4Gv2H7+yCKLiIhppQ48IqJSI9nQwfbXgK+N4mdFRMTspAceEVGpJPCIiEolgUdEVCoJPCKiUkngERGVSgKPiKhUEnhERKWSwCMiKpUEHhFRqZHMxIyIaFtWqUwPPCKiWkngERGVSgKPiKhUEnhERKWSwCMiKpUEHhFRqSTwiIhKJYFHRFQqCTwiolJzTuCSdpL0VUkrJV0l6ZhRBhYREdObz1T6+4C/tr1C0hbAcknn2r56RLFFRMQ05twDt32z7RXN1z8DVgI7jCqwiIiY3kjGwCUtBvYALh7Fz4uIiJnNezVCSZsDnwZeY/uuKR5fCiwFWLRo0XybWzCy0lr/zPd3MorfR14XMWxePXBJD6Ek79NtnznVc2yfanuJ7SUTExPzaS4iIobMpwpFwAeAlbbfNrqQIiJiNubTA98XeAGwv6TLmo8/GVFcERExgzmPgdu+ENAIY4mIiHWQmZgREZVKAo+IqFQSeEREpZLAIyIqlQQeEVGpJPCIiEolgUdEVCoJPCKiUkngERGVSgKPiKhUEnhERKWSwCMiKpUEHhFRqSTwiIhKJYFHRFQqCTwiolJJ4BERlUoCj4ioVBJ4RESl5pXAJR0o6TuSvifp+FEFFRERM5tzApe0IfAu4JnAY4EjJD12VIFFRMT05tMDfzzwPdvX2r4H+DhwyGjCioiImcwnge8A3Dh0fFNzLiIiWiDbc/tG6TDgGbZf2hy/AHi87VdPet5SYGlzuBvwnbmHC8C2wG3z/Bnz1YcYoB9x9CEG6EccfYgB+hFHH2KAfsQxihgeaXti8smN5vEDbwJ2GjreEfjR5CfZPhU4dR7trEHSMttLRvXzao2hL3H0IYa+xNGHGPoSRx9i6Esc44xhPkMo3wJ2lbSzpI2Bw4HPjiasiIiYyZx74Lbvk/Qq4EvAhsAHbV81ssgiImJa8xlCwfYXgC+MKJbZGtlwzDz0IQboRxx9iAH6EUcfYoB+xNGHGKAfcYwthjnfxIyIiG5lKn1ERKWSwCMiKjWvMfBol6RXAqfbvqM5fjhwhO13dxtZxNQk7W37Wy22tyFwELCYofxm+20txrAB8ATb3xh7W30eA5e0zXSP2769rVgGJG0HbDIUww0ttn2Z7T+cdO5S23u0FcNQu08GdrX9IUkTwOa2r2ux/bcCH+q68qn5t/8tZT2g4dfF/i21fwUw1R+xShj+gzbiWKPhsibS4cARwJ1t1mFL+gLwK+AK4IHBeduvbyuGJo6LbD9x3O30vQe+nPLi1BSPGfidtgKRdDDw/4D/BdwKPBJYCfxeWzEAG0iSm3fdprexcYvt07R7IrCEMrP2Q8BDgI8C+7YYxjXAqZI2amI4w/adLbY/cDrwH5Re3yuAo4BVLbb/rBbbWitJj6Qk7COA+yh/H0tsX99yKDt28aY1hXMkPRc402PsJfe6B94nki4H9ge+bHsPSftRhi+WzvCto4zhLZRLw/dS3sBeAdxo+6/biqGJ4zJgD2DFoPcv6dsd9fZ2A15MSRz/DbzP9ldbbH+57b2G//2Szrf9R23F0DVJ3wC2oixo93Hb35V0ne2dO4jlX4DzbJ/TdtuT4vgZ8DDgfuCXrL4i2nKU7fS6By7p0bavkbTnVI/bXtFiOPfa/omkDSRtYPurzYulTX8LvBz4S8oL4hzg/S3HAHCPbUsaXAk8rIMYBlcgj24+bgMuB14r6eW2D28pjHubzzdLOoiynMSOLbX9G5KeAJwCPIZyVbYh8ItRJ4y1WEX5N28PTADfZephnTZ8EzirGYe+lzElzpnY3qKNdnrdA5d0qu2lkqbqUbmtccYmli8DhwL/TFmc5lZgb9tPaiuGvpD0N8CuwAGU/4+XAB+zfUqLMbwNOBg4D/iA7UuGHvuO7d1aiuNZwNcp6wKdAmwJvN52q8tKSFpGGXf+JGV464XALrZPaKn9rYDnUq6EdgG2pix2d8m03zj6OK6l/J1eMc6hi1nEIeBIYGfbb5S0E/CIUf9/9DqB90nTy/wV5R39SMol4+m2f9JC25+w/Wdru2HV0dDFAcDTKf8fX7J9bsvtv4RyuX73FI9t1dF4eGcGCyZNGsr5RhcdDEnbA39OeUPZyfZOM3zLKNv+EvBM2w/M+OTxxvEeyk3U/W0/pqkYO8f23iNtp4YELun7wFtsv3fo3Odt9+IGzrhJeoTtm5sbRQ9i+wctx7MzcLPtXzXHmwLbt33DStIOlJtlw+ViF7TU9nG23yzpFKZ+U/2rNuIYiucC4I8pQ2o/Bm4GXmR79zbjGIrn4cAdwKI2X5+SPkwpbvgv4NeD822WETZxrLC953CVmKTLR/376PUY+JB7gf0k7QO8vNkBqJXNI5qbEWt9l2tjbM32zc3nVhP1ND4JDPfs7m/OjbR3MR1JJ1N6eFc37UP5PbWSwCkVSADLWmpvJi+gjHu/CjiWMqTz3DYalvQ64BPN/aqHAl8EdqdUo/wF0Obr9rrmY2M6qNAacm9zj2Zwn2iCobLGUaklgd9t+88lHQd8XdKf0dJNksHNCElvoPRsPsLqYZR2blQ8+E1ErC6vbP0GDbBR8yYKJYB7miWF2/QcYDfbv57xmWNg+3PN59O6aH+yoTf3XwKt1jxThkve2Hx9VPN5Avhd4DTgy20FMqj3lrRFOfTP22p7kncCZwHbSXoT8DzgH0bdSC0JXADNJetyyhK2007yGYNn2N5n6Pg9ki4G3jzuhtu6o70OVkk6eHCjTtIhtL/rybWU+vNOErikzzH9ldnBLYaztgk9d1KuEP5pzPdq7hm6YfgMyr2J+4GVkh4yxnYfRNLjKJ2sbZrj24AXtj3hy/bpTa56GiV/HWp75Qzfts5qSeCvG3xh+zxJTwde1HIM90s6klLrasrd9vun/5bRa0oqn9zEcKHtS9uOgVJ/frqkf6O8OG+kVD206W7gMknnseZYZ1tjz29tPv8p8NuUiUxQXhfXtxTDsP+ivB4/1hwfTvnd3Al8GHj2GNv+dZM4bwH2A/5m6LFNx9juVE4FXjuYCyDpqcD7WHPIry3fBe6iybOSFo165nYVNzHhNzdFdmXN6cptjXciaTHwr5TZhqZMGnlNmzfumrHGw4Azm1OHAp+0/U9txTApns0pr6GfddD2UVOdb3tIQ9IFtp8y07kW4vhv2/tOdU7SFbZ/f4xt70MZKpkA3j54PUr6E+AFto8YV9tTxPKgG4XjuHk4izheDZxIeVO7nzEtbVBFApf0UuAYymSBy4AnABe1WQfeB5JWAntMqv5YYfsxLcfxUMoNssWsWQHyhjbj6IPmd3KQ7Wub452BL3TwO7kcWGr74ub48ZRZqbtrzOvlSHotq5e7cPNxG+UKsbX1cZpYzgJWUIZRAJ5PmdJ/aMtxfA/YZ9xlxrUMoRxDqXD4pu39JD2alm/USNoEOJqy9snwVcBLWgzj+qbtXzXHDwW+32L7A/9JuTRfTndj0LtSJhFNXkSqtfVxGscCX2smkEB5U3t5yzEAvBT44OCqiHLp/tJm/sI/j7ntqe7RLAZOkHSS7Y+Puf1hL6HkhjMp/w8XUJZaaNuNlL+RsaqlB/4t23urrMGxj+1fa4qV+cYcwycpCyj9BfAGShXKStvHtND2oNZ4EeWN7Nzm+ABKL6etaeODeK60/bg225wihgspl6hvp4zvvpjyej6xg1geSpnOD3BNV5UxTSxbUf4f7ugqhqFYtqGsHTTlUhjro+ZqBEpHbzfgbMZYj15LD/wmSVsDnwHOlfRTypoTbdrF9mGSDrF9mqSPUaph2jCoNV5OKU0a+FpL7U/2DUm/b/uKjtoH2LS5oa2mhO4kSV+nJPW27cXq4aTdJWH739sMoLk/MnwMdDusZft2DQIZsx5VBQ2uRm5oPobr0UfeW64igdt+TvPlSSrromxFmSzQpsGiRXc0d9x/TPmjHbu+1BoPeTLwIknXUXoXXaw9/SuVBYu+K+lVwA+B7VpsHwBJHwEeRbk3MzyhqNUEDvxi6OtNKMvMjrxsbV1I2h/4aUvN9aIqaKgO/TDbnxx+TNJho26v90MozR/pt3twyf5S4NPAH1DWn94ceN3w9P4WYriOqadttzru24cp/ZL2piSorSmTSLYC3mz7m23F0MSxEnise/aH1AzrfNb2M1poa6oa9G0oV8kvtH3NuGMYiqUvVUErJg8dTXVuvnrfA7f9gKTLx1FDuY5xDJZtPZ8WN5KYZHhnk00oJYVtT2j6TaLWpN2JWo5hsE3Xz+nmJtXAlZQe380dxjCVzWjvdTp5TSIDP7H9i6mePGYTkn5nUlXQRFuNS3om8CfADpLeOfTQlpSlBUaq9wm88QjgKkmXMHSp2OZstz6Uzk1RkvSO5mbe66Z6/rioB7sTSVoCnMCDF7Nqe2XGbYGrm9fm8M2qLmdibkhJWm9c+3eMTo/W6IHuq4J+RLlndRjwP5Tfyf2UevBjR91YLQm87bUdptKH0rnhy68NKD3yLqbZv5FSi7/G7kQtx3A68H+YtPdhB07qsO1hw73g+4BbbI+8x9d3tr/YlJh2VRV0NaVCbWNKSaMoC4t9CPj8qBvr/Rj4ZJK2pVyetRp4T0rnhje2uI9yc+attr/TchyDtacvp0wsekDSJbYf32IMF9p+clvt9Z2kj9h+wUzn1neS/nSK03dSNni4tYX23065P/bawQxlSVtSbrL+ctRlx73ugatsE3UycDul1/cRyiXrBpJeaLvNSpTOS+ds79dV25Pc0UwYuYCyJsqtjGF8bwYnSno/ZUee4aGLM9f+LaOjtS8z3NUKkWsMX6ls9rxXyzH0wdHAE4GvUH4XT6Vss/a7kt5g+yPTfO8oPAv43eEOpu27JP0lZR7JwkngwL8Bf0+pMPgKZaeNbzYzMc+g3VLCzkvnJP0Wpc75N4tZAW8Y93TdKRxCWbb0WFbvTtR2vfGLKZfJD2H1EIpZvU7MWLknK0RK+jvK38imku4anAbuoSzstNA8ADzG9i3AYHeg9wD7UDoc407gnmp0wPb9avaQHaVeD6EMz7aUtHJ4fYlxr+8wRSx9KJ07l/IiHNS4Hgk81fYftxjDhpQt1Fprcy1xjHWBppo0pbbvb3lZh16a/LpoJhJdYftxbeQMSZ8Bzpw8kUvS84E/G/XN7b73wIdvTv1y0mOtvvP0oXQO2Mb2cGXBP0lqdZGepidxt7rfd/Kbkh5r++oOY+iF5h5EJ1un9dDXJX2eskMUlMqxC5o1YdpYXuCVwJkqe7Yup+SpvSnL6j5num+ci773wO+nlA2K8h8w2MBWwCa2W1ssfm2lc7bbLJ17K6VE6RPNqecBv9f2+h+SPkGpQjmXNcs6W9sHsplA8yjK9lldzQbtDUnvAj48VB+/IDU97udSln0WZZjx0x0UPexPuS8h4Crb542lnT4n8D5pKi72Z1LpnO2lLcbwM+BhlCsTU+p9Bwm0tRtn6sFa3H0Y0uoTSVdTtjD7Aas7PQv2DW2hSAKfpT6UznWt69mwQ3H0YnmFPskbWtGUEf4LZV0c0V1VUCs26DqAikwunftXWi6dU/F8Sf/YHO+ksnB/Wz4zFMunW2x3DbYfAC6XtKirGPqmSdRbU5bWfTaw9UJL3o03Awfb3sr2lra3WF+TNySBz0jSLpL2pZTO3U0pnfsi8BPg1S2H825KjetfNMc/B97VYvvDS4N2tR7MwGB5hfMkfXbw0XFMnZF0DGV26nbNx0dVtvVaaG7xGDYP7qu+V6H0wTuAvx9amOcB4LRmLY6TGO9msZPtY3tPSZcC2P6ppI1n+qYR8lq+7kIfllfok6Mpr49fAEj6F+Ai4JROo2rfMkn/QblabH2CV9uSwGe22Pa3J5+0vUxlo+M23dvUYRtA0gTtrgOyezNZRDx44kir44y2z2/GfXe1/WVJm1Fu6i5UYvV65LB6I92FZkvKlfLTh861NsGrbUngM5uu5nvT1qIo3knZkWc7SW+ilBH+Q1uN2+5NgpT0MmApZTndRwE7AO8FntZlXB36EHCxyqa+AIcCH+gwnk7Y7nJp4dalCmUGks4AvmL7fZPOHw083faftxzPoylJSsB5C2m8b5jK/qiPBy4ezK5b6LMzm9Uqn0yzma/tSzsOqTWSjrP9Zq3eP3YNbc5RaFN64DN7DXCWpCMpM6ugLOO6MWOYWTUTl91NrgGQtLWkE2y/qe04euDXtu9Rs+Vis3jTguuNSNoEeAWwC2Vp3XcvxGVkWb193LJpn7WeSQKfQbMozpOaiTuDuuOzbX+lrRgk7QT8I2UW6GeAj1FWZ3wBZVGvheh8SYNFnA4A/jfwuY5j6sJplP1avw48E3gMpdOx0NwAU08ma1YCXC9lCKUCKuuAn0+pKjiQMoRyFXCs7R93GVtXmsk8R7P6ZtWXvHrbuwVjeNiouQq5xCPed7EGKjvwHGZ7+aTzrweevb7+nySBV0DS5bZ3Hzq+BVjkdnca6QVJhwA72n5Xc3wJZfswA8fZ/lSX8bVNkzbKnXy8UEjai7KA1ZG2L2rWRHkPZXmBQ23fNe0PqFSGUCoh6eGsLgv7MbBZs8Iatm/vLLD2HQccPnS8MWXjgs0plRgLKoGzurQT1izvXK+nkE9me3mzMudZkl4JvKx56EDb93QY2lglgddhK8oN1OG63hXNZ9P9rMg2bWz7xqHjC5s3sNsHb2gLSZ9KO7skaRvgJuAoyn2iLwOvAjaXtN52cjKEElWR9D3bu6zlse/bflTbMUX3VHbKGiSzQUfHrL4SWS87OemBR20ulvSyKeryXw5c0lFM0THbO3cdQxfSA4+qNDsiDda5GAwj7QU8lHKz6pauYotoWxJ4VGloxxMoO560Vpcf0RdJ4JVpFrPanqHhrz5sshAR7csYeEWa9Z1PBG5h9SqEBrJtVixoTRXKWqUKJTon6XuUNZ9/0nUsEX0yVIUiYBHw0+brrYEb1tebnNmRpy43And2HURE39jeuSkV/BJl6vy2tn8LeBbr6VrgkB54FSS9tvny94DdgLNZc7eRt3URV0TfSFpue69J55bZXtJVTOOUMfA6bNF8vqH52Lj5iIg13SbpH4CPUoZUnk/Zv3a9lB54RKw3mpuZJwJPoSTwC4A35CZmdE7SuZQlM+9ojh8OfNz2M7qNLKJfJG1u++ddxzFuuYlZl4lB8oayKz2wXYfxRPSKpCdJuhq4ujneXdK7Ow5rbJLA63K/pEWDg2ZX9lxCRaz2duAZNOPeti+nDKesl3ITsy4nABdKOr85fgplZ/aIaNi+cbBXauP+rmIZtyTwitj+YrPz+BMokxSOtX1bx2FF9MmNkp4EWNLGwF+xesPj9U6GUCrSbBN1ILCn7c9RduV5fMdhRfTJK4BXAjtQNnj4Q8qG1+ulVKFURNJ7KGug7G/7MU0Vyjm29+44tIhekLSv7f+e6dz6Ij3wuuxj+5XAr+A3VSiZ0BOx2imzPLdeyBh4Xe5tlpM1gKQJVq9KGLFgSXoi8CRgYmjpCYAtgfV239D0wOvyTuAsYDtJbwIuBP5vtyFF9MLGwOaUTukWQx93Ac/rMK6xyhh4ZSQ9GngapQrlPNvr7R32iHUl6ZG2f9B1HG1JAq+IpA8Ap9i+bOjcSbZP6i6qiO5Jeoft10j6HFNMbrN9cAdhjV0SeEUk3QTcBrzN9r8351bY3rPbyCK6JWkv28sl/dFUj9s+f6rztUsCr4ikFcBTgdMpy8oeA3zL9h5dxhUR3chNzLrI9l22nw2sAs4Htuo4pojekLSvpHMl/Y+kayVdJ+naruMal5QR1uWzgy9snyRpGfDaaZ4fsdB8ADgWWM56vAbKQIZQKiBpF2D7KWaYPQX4oe3vdxNZRL9Iutj2Pl3H0ZYModThHcDPpjh/d/NYRBRflfQWSU+UtOfgo+ugxiU98ApIutL249by2BW2f7/tmCL6SNJXpzht2/u3HkwLMgZeh02meWzT1qKI6Dnb+3UdQ5uSwOvwLUkvs/2+4ZOSjqbcrIlY0CatfwJlMs9twIW2r+sgpFZkCKUCkranrIFyD6sT9hLK+g/Psf3jrmKL6ANJJ05xehvK9mon2f54yyG1Igm8IpL2AwZj4VfZ/kqX8UT0naRtgC+vr7OVk8AjYr0m6dL1dbZyyggjYr0laX/gp13HMS65iRkR1ZN0BQ9ehXAb4EfAC9uPqB0ZQomI6kl65KRTBn5i+xddxNOWJPCIiEplDDwiolJJ4JEPIGcAAAAaSURBVBERlUoCj4ioVBJ4RESlksAjIir1/wH+XvXc8wmzgQAAAABJRU5ErkJggg==\n",
"text/plain": [
"<Figure size 432x288 with 1 Axes>"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"# plot the total customers for each country on a bar chart\n",
"num_customers_chart = country_analysis_other['total_customers'].plot.bar().set_xticklabels(country_analysis_other['country_name'])"
]
},
{
"cell_type": "code",
"execution_count": 17,
"metadata": {},
"outputs": [
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAWoAAAE/CAYAAABvm4mnAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAdyElEQVR4nO3deZSlVX3u8e8DiIAISGi8hsFGQcQhBGhAwWUEBxxBoygKqCiiuQ6IySUmGkHUxOk6EcWFGjSKEgfwOkWZFMQB7G5AhMaooIAiNqigKILw3D/2e6jTxemuQuvd76bq+axVq+sM1e+vu6qes88eZZuIiGjXWkMXEBERa5agjohoXII6IqJxCeqIiMYlqCMiGpegjoho3Dp9/KWbbbaZFy9e3MdfHRExLy1btuxa24smPdZLUC9evJilS5f28VdHRMxLkn6yusfS9RER0bgEdURE4xLUERGNS1BHRDQuQR0R0bgEdURE4xLUERGNS1BHRDSulwUvMXuLX/3FP/vv+PGbnzQHlUREq9KijohoXII6IqJxCeqIiMYlqCMiGpegjohoXII6IqJxCeqIiMZlHnVENC1rDRLUEatIKESL0vUREdG4BHVEROMS1BERjUtQR0Q0LkEdEdG4BHVEROMS1BERjUtQR0Q0LkEdEdG4BHVEROMS1BERjUtQR0Q0blabMkk6AjgUMHARcIjtm/osLBaebIgUMdmMLWpJWwCvAJbYfgiwNnBA34VFREQx266PdYD1Ja0DbAD8rL+SIiJi3IxBbfunwNuBK4Crgettn9p3YRERUcym6+NewH7ANsBfAveQdNCE5x0maamkpStXrpz7SiMiFqjZDCY+Brjc9koASScDewAfG3+S7eOB4wGWLFniOa4zIirL4G47ZtNHfQXwMEkbSBLwaGBFv2VFRMTIbPqozwU+DSynTM1bi67lHBER/ZvVPGrbRwFH9VxLRERMkFPII32REY3LEvKIiMYlqCMiGpegjohoXII6IqJxCeqIiMYlqCMiGpfpeRERMxh6Cmta1BERjUtQR0Q0LkEdEdG4BHVEROMS1BERjUtQR0Q0LkEdEdG4BHVEROMW7IKXoSewR0TM1iBBnZCMiJi9dH1ERDQuQR0R0bgEdURE4xLUERGNS1BHRDQuQR0R0bgEdURE4xLUERGNS1BHRDQuQR0R0bgEdURE4xLUERGNS1BHRDRuwW5zGtGy7DAZ49KijohoXII6IqJxCeqIiMYlqCMiGjeroJa0iaRPS7pU0gpJD++7sIiIKGY76+PdwJdtP0PSusAGPdYUERFjZgxqSRsBjwSeD2D7ZuDmfsuKiIiR2XR93A9YCZwg6XxJH5R0j57rioiIzmyCeh1gZ+A42zsBNwKvnv4kSYdJWipp6cqVK+e4zIiIhWs2QX0VcJXtc7vbn6YE9ypsH297ie0lixYtmssaIyIWtBmD2vbPgSslbd/d9Wjgkl6rioiI28121sfLgRO7GR+XAYf0V1JERIybVVDbvgBY0nMtERExQVYmRkQ0LkEdEdG4BHVEROMS1BERjUtQR0Q0LkEdEdG4BHVEROMS1BERjUtQR0Q0LkEdEdG4BHVEROMS1BERjUtQR0Q0LkEdEdG4BHVEROMS1BERjUtQR0Q0LkEdEdG4BHVEROMS1BERjUtQR0Q0LkEdEdG4BHVEROMS1BERjUtQR0Q0LkEdEdG4BHVEROMS1BERjUtQR0Q0LkEdEdG4BHVEROMS1BERjUtQR0Q0LkEdEdG4BHVEROMS1BERjUtQR0Q0btZBLWltSedL+kKfBUVExKruTIv6cGBFX4VERMRkswpqSVsCTwI+2G85EREx3Wxb1O8CjgRu67GWiIiYYMaglvRk4Be2l83wvMMkLZW0dOXKlXNWYETEQjebFvWewL6SfgycBOwt6WPTn2T7eNtLbC9ZtGjRHJcZEbFwzRjUtv/J9pa2FwMHAGfaPqj3yiIiAsg86oiI5q1zZ55s+2vA13qpJCIiJkqLOiKicQnqiIjGJagjIhqXoI6IaFyCOiKicQnqiIjGJagjIhqXoI6IaFyCOiKicQnqiIjGJagjIhqXoI6IaFyCOiKicQnqiIjGJagjIhqXoI6IaFyCOiKicQnqiIjGJagjIhqXoI6IaFyCOiKicQnqiIjGJagjIhqXoI6IaFyCOiKicQnqiIjGJagjIhqXoI6IaFyCOiKicQnqiIjGJagjIhqXoI6IaFyCOiKicQnqiIjGJagjIhqXoI6IaNyMQS1pK0lflbRC0sWSDq9RWEREFOvM4jl/BP7e9nJJ9wSWSTrN9iU91xYREcyiRW37atvLu89/A6wAtui7sIiIKO5UH7WkxcBOwLl9FBMREXc066CWtCHwGeCVtm+Y8PhhkpZKWrpy5cq5rDEiYkGbVVBLuhslpE+0ffKk59g+3vYS20sWLVo0lzVGRCxos5n1IeBDwArb7+i/pIiIGDebFvWewMHA3pIu6D6e2HNdERHRmXF6nu1zAFWoJSIiJsjKxIiIxiWoIyIal6COiGhcgjoionEJ6oiIxiWoIyIal6COiGhcgjoionEJ6oiIxiWoIyIal6COiGhcgjoionEJ6oiIxiWoIyIal6COiGhcgjoionEJ6oiIxiWoIyIal6COiGhcgjoionEJ6oiIxiWoIyIal6COiGhcgjoionEJ6oiIxiWoIyIal6COiGhcgjoionEJ6oiIxiWoIyIal6COiGhcgjoionEJ6oiIxiWoIyIal6COiGhcgjoionEJ6oiIxs0qqCU9XtL3Jf1Q0qv7LioiIqbMGNSS1gbeCzwBeBDwbEkP6ruwiIgoZtOi3g34oe3LbN8MnATs129ZERExMpug3gK4cuz2Vd19ERFRgWyv+QnS/sA+tg/tbh8M7Gb75dOedxhwWHdze+D7f0ZdmwHX/hlfP1daqKOFGqCNOlqoAdqoo4UaoI06WqgB/vw67mt70aQH1pnFF18FbDV2e0vgZ9OfZPt44Pg/qbxpJC21vWQu/q67eh0t1NBKHS3U0EodLdTQSh0t1NB3HbPp+vgOsJ2kbSStCxwAfK6PYiIi4o5mbFHb/qOklwFfAdYG/sP2xb1XFhERwOy6PrD9JeBLPdcybk66UOZAC3W0UAO0UUcLNUAbdbRQA7RRRws1QI91zDiYGBERw8oS8oiIxiWoIyIaN6s+6qhH0kuBE23/urt9L+DZtt83bGURqydpV9vfqXSttYEnAYsZyzDb76hx/a6GtYCH2f5mlesN3UctadM1PW77l7VqGZG0ObDeWA1XVLz2Bbb/etp959veqVYN0679CGA72ydIWgRsaPvyitd/O3DC0DONun/7P1L2uxn/2di7wrUvAib9oqqU4L/qu4ZJuj1/DgCeDVxfay6zpC8BNwEXAbeN7rf9+hrXH6vjW7YfXuNaLbSol1F+CDXhMQP3q1WIpH2B/wv8JfAL4L7ACuDBtWoA1pIkd6+gXeth3YrXv52ko4AllJWmJwB3Az4G7FmxjEuB4yWt09XwCdvXV7z+yInAf1Faci8BngesrHTtJ1e6zowk3ZcSzM8G/kj5HVli+8cVy9hyqBenaU6V9HTgZPfc4h28Rd0SSRcCewOn295J0l6UbofDZvjSuazhbZS3dO+nvFC9BLjS9t/XqmGslguAnYDloxa9pO8O8UsiaXvgEEpAfAP4gO2vVrz+Mtu7jP/7JZ1l+29q1TA0Sd8ENqZszHaS7R9Iutz2NpXreAtwhu1Ta153Qh2/Ae4B3Ar8nql3OBvN9bUGb1FLeqDtSyXtPOlx28srlnOL7eskrSVpLdtf7X4oavpH4MXA31G+8acCH6xcw8jNti1p1Lq/xxBFdO8qHth9XAtcCLxK0ottH1CpjFu6P6+W9CTKNgpbVro2AJIeBhwL7EB5l7U2cGMfwbAaKyn/5nsDi4AfMLlLpm/fBk7p+olvoceAXBPb96x1rcFb1JKOt32YpEmtI9foAxyr5XTgqcC/UTZY+QWwq+09atXQEkn/AGwHPJbyf/IC4OO2j61YwzuAfYEzgA/ZPm/sse/b3r5SHU8Gvk7Z9+ZYYCPg9barbacgaSmlT/hTlC6p5wLb2n5NxRo2Bp5OeWezLbAJZdO289b4hXNbw2WU39OL+u5ymKEOAQcC29h+g6StgPv08X8xeFC3pGsx3kR5hT6Q8jbvRNvXVbj2J20/c3UDRwMOGD0WeBzl/+Qrtk+rfP0XUN5m/27CYxsP1F89iNGmP9O6X745VENC0r2BZ1FePLayvdUMXzJX1/0K8ATbt8345H7rOI4ymLm37R26GVqn2t51zq/VSlBL+hHwNtvvH7vvC7abGUjpk6T72L66G6y5A9s/GaCmbYCrbd/U3V4fuHflgSMkbUEZtBqfinV2pWsfafutko5l8gvoK2rU0dVyNvAYSlfYz4Grgefb3rFWDRNquhfwa2DrWj+jkj5MmWTw38AfRvfXnJ7X1bHc9s7js7IkXdjH92PwPuoxtwB7SdodeHF3mkyVAwq6QYHVvmLV6PuyfXX3Z/VAXoNPAeOttVu7++a8xbA6kt5MabFd0l0fyveqSlBTZv0ALK10vTU5mNIv/TLgCEo3zNNrXVzS64BPdmNKdwe+DOxImf3xHKDWz+7l3ce6DDQjqnNLN34yGsNZxNh0wbnUUlD/zvazJB0JfF3SM6k0UDEaFJB0DKWl8lGmuj+qDBhMeLEQU9MWqw+UdNbpXjChFHFzt9VtTU8Dtrf9hxmf2QPbn+/+/MgQ159WyygIfw9UnTPceRbwhu7z53V/LgIeAHwEOL1GEaP50pLuWW76tzWuO8F7gFOAzSW9CXgG8No+LtRSUAuge5u5jLKt6hoXw/RgH9u7j90+TtK5wFv7vnDNEeQ7YaWkfUcDZpL2o/5JGpdR5m8PEtSSPs+a323tW7GWSeMX11Na+2+sMJZy89jg3T6UsYNbgRWS7tbztW8n6SGUxtSm3e1rgefWXhRl+8Quqx5Nya+n2l4xw5f9SVoK6teNPrF9hqTHAc+vXMOtkg6kzBM1ZWT71jV/ydzrpio+oqvhHNvn166h8xLgREn/TvlBvJIy06Cm3wEXSDqDVfsja/UNv73782+B/0VZ8APlZ+PHlWoY+W/Kz+PHu9sHUL4v1wMfBp7S8/X/0IXkNcBewD+MPbZ+z9cedzzwqtE8ekmPAj7Aqt10tfwAuIEuSyVt3cdK5mYGE+H2gYntWHWJbq2+SCQtBt5NWXlnysKKV9YcPOv6AfcHTu7ueirwKdtvrFXDhJo2pPys/GaAaz9v0v21uyIknW37kTPd13MN37C956T7JF1k+6E9X393ShfHIuCdo59JSU8EDrb97D6vP1bHHQbs+hrEm6GOlwNHUV64bqXHJf3NBLWkQ4HDKRPqLwAeBnyr5jzqFkhaAew0babFcts7DFDL3SmDVYtZdcbFMbVrGVr3fXmS7cu629sAX6r5felWzh5m+9zu9m6UFZo7qsJ+MJJexdRWD+4+rqW866u5/8spwHJK9wfAQZRl7E+tVUNXxw+B3WtM322p6+NwymyCb9veS9IDqTxgImk94IWUvT3GW/UvqFjGj7tr39Tdvjvwo4rXH/f/KG+rlzFcH/F2lMU20zdDqrYHTOcI4GvdYgsoL14vrlzDocB/jN7hUN5yH9rN//+3CtefNI6yGHiNpKNtn1ShBigLr15PedcpygygQypde9yVlN+P3rXUov6O7V1V9pfY3fYfNGEnuZ5r+BRlE6DnAMdQZn2ssH14hWuP5uluTXnBOq27/VhKi6XWUunxmr5n+yG1rzuthnMoby/fSemDPYTyc3vUALXcnbKMHeDSoWaidKsD5W4r3KGp7IB5uu2J20DMN907CygNuu2BL9LzfO6WWtRXSdoE+CxwmqRfUfZTqGlb2/tL2s/2RyR9nDL7pIbRPN1llCk/I1+rdP1JvinpobYvGrCG9bvBZXXT046W9HVKeNe2C1PdQDtKwvZ/1rp4N34xfhsYvivK9i81KqZHDc3AGb2zuKL7GJ/P3UvLt5mgtv207tOjVfb92Jgyob6m0cY7v+5Gt39O+cXsXQvzdCd4BPB8SZdTWgxD7H98k8rmOz+Q9DLgp8DmFa8PgKSPAvenjJ+ML7ypFtTAjWOfr0fZ/rSX6WB3hqS9gV9VuFQTM3DG5nHvb/tT449J2r+PazbR9dH9In63gbfZhwKfAf6KsvfxhsDrxpe1V6jhciYvVa7dJzvae/gOaq6elLQrJYw2oSy22Bh4q+1v16qhq2MF8CC38AvT6bpiPmd7n0rXmzSPe1PKO9/n2r60Uh2Dz8Dprrl8enfPpPvmQhMtatu3SbqwrzmId6KO0XaiZ1HxwIJpxk/JWI8yVa/2wh9gKpA17cSbyjWMjnf6LcMMGI18j9KKu3rAGqbbgLo/p9P33TFwne0bJz25R4sk3W/aDJxFtS4u6QnAE4EtJL1n7KGNKMvp51wTQd25D3CxpPMYe4tXeeXX4NPRJkz1eVc3oPa6Sc/vkxo48UbSEuA13HFTptq7CW4GXNL9fI4PHA21MnFtSji9YfVfMbca2odm6Bk4P6OMKe0P/A/le3IrZT71EX1csKWgHmLvgulamI42/rZpLUoLe6jl5W+gzGdf5cSbyjWcCPwfpp2PN4CjB7z2yHiL9o/ANbZ7acG1zPaXu2mbQ83AuYQyI2xdylRBUTbIOgH4Qh8XbKKPejpJm1HeUlUtrpHpaOMHKPyRMkjydtvfH6CW0f7HF1IW4dwm6Tzbu1Ws4Rzbj6h1vZZJ+qjtg2e6b76T9LcT7r6ecpDALypc/52U8atXjVbrStqIMtj5+z6m8w7eolY5XujNwC8pLbiPUt5mriXpubZrzvwYfDqa7b2GuvYEv+4WV5xN2fPjF/TUB7cGR0n6IOWEl/Euh5NX/yVzR6vfAneIXQ1X6XJSOfB3l4rXb8ULgYcDZ1K+D4+iHM/1AEnH2P7oGr52LjwZeMB4Q9L2DZL+jrIOY/4FNfDvwD9TRvPPpJzc8O1uZeInqDtFb/DpaJL+gjJH+PZNmYBjaixTnWA/ypaaRzB14k3tObuHUN7i3o2prg8ztRdKr9zAroaS/onyO7K+pBtGdwM3UzYoWmhuA3awfQ0wOmnmOGB3SqOi76D2pHf7tm9Vd77oXBu862N89aGkFeN7J9TYv2BaLS1MRzuN8sM2miN6IPAo24+pVUNXx9qUo7eqXndCHb1vNnRX0E1h/WDl7QyaNP1noltsc5Hth1Ta8+SzwMnTFztJOgh4Zh8DzC20qMcHiH4/7bGqryItTEcDNrU9PpL/RklVN5uB21sHv9Pw5xJ+W9KDbF8yYA2D68YHBjtyqzFfl/QFymlDUGZqnd3teVJjWf1LgZNVzvNcRsmpXSlbvT5tTV/4p2qhRX0rZTqeKP/Q0SGmAtazXXND8onT0WzXnI72dsrUn092dz0DePBAe1t8kjLr4zRWnTJZ85zAFZQVgUOujmyCpPcCHx6bW74gdS3op1O2Ixale/AzA0w+2JsybiDgYttn9HatoYO6Jd3shr2ZNh3N9mEVa/gNcA/KOw1T5suOQrLq4JUa2Au6he6oVki6hHLs1U+YatwsyBethSZBPaaF6WgtGHqF6FgdTWwt0Iq8aBXd9Ly3UPZ8EcPMwKlqraELaMz06WjvpvJ0NBUHSfqX7vZWKhvE1/TZsXo+U/nat7N9G3ChpK2HqqElXSBvQtnu9SnAJgstpDtvBfa1vbHtjWzfcz6HNCSoAZC0raQ9KdPRfkeZjvZl4Drg5ZXLeR9ljuhzutu/Bd5buYbxLSuH2vNkZLS1wBmSPjf6GLimQUg6nLJSc/Pu42Mqx0EtNNe4p0NkW9XCrI8WvAv457HNZW4DPtLtM3E0/R8aOm532ztLOh/A9q8krTvTF80xr+bzIbSwtUArXkj5+bgRQNJbgG8Bxw5aVX1LJf0X5Z1f9UVQQ0hQF4ttf3f6nbaXqhx4W9Mt3RxmA0haRP09LnbsFlaIOy6yqNoXaPusrm92O9unS9qAMsC6EImpvbBh6kDVhWYjyjvfx43dV20R1BAS1MWa5kyvX62K4j2UE142l/QmyvS819YswHYzQSjpRcBhlK1e7w9sAbwfePSQdQ3kBOBclcNdoZxQ/6EB6xmE7SG3ux1EZn0Akj4BnGn7A9PufyHwONvPqlzPAylBJOCMhdYfN07lDM3dgHNHK84W8mrFbnfFR9Ad6mr7/IFLqkbSkbbfqqnzRVdRc35/bWlRF68ETpF0IGWlEZTtRdelp5VGa+JyUsalAJI2kfQa22+qXUcj/mD7ZnVH8nUbES2o1oWk9YCXANtStnt930Lc3pSpY8eWrvFZ81CCGug2d9mjW+AymrP7Rdtn1qpB0lbAv1BWRX4W+DhlN8GDKZtTLVRnSRptSPRY4H8Dnx+4pto+QjnP8+vAE4AdKI2LheYKmLzgqtu5bt5K10cjVPahPosyiv94StfHxcARtn8+ZG1D6ha9vJCpgaOveOrItAVhvKune0dxnns4l691Kie67G972bT7Xw88ZT7/nySoGyHpQts7jt2+BtjadU+uaIak/YAtbb+3u30e5egpA0fa/vSQ9dWkaQemTr+9UEjahbIR04G2v9Xt+XEcZVn9U23fsMa/4C4sXR8NkXQvpqZb/RzYoNsRDNu/HKywYRwJHDB2e13KJvkbUmY/LJigZmq6JKw6ZXLeL50eZ3tZt5PkKZJeCryoe+jxtm8esLTeJajbsTFlIHN8Xuzy7k8z/ArB2ta1feXY7XO6F6tfjl68FoqWpksOSdKmwFXA8yjjOKcDLwM2lDSvGzPp+ogmSfqh7W1X89iPbN+/dk0xLJWTl0aBNWrQmKl3FvO2MZMWdbTqXEkvmjC3/cXAeQPVFAOyvc3QNQwlLepoUnfKzmgvh1EX0C7A3SkDR9cMVVtEbQnqaNrYKRpQTtGoNrc9ohUJ6gZ1mzLdm7GuqRY28o+IYaSPujHd/sJHAdcwtWuegRy3FAtaN+tjtTLrI6qR9EPKnsPXDV1LREvGZn0I2Br4Vff5JsAV83mwMSe8tOdK4Pqhi4hoje1tuil4X6EsGd/M9l8AT2Ye70UNaVE3Q9Kruk8fDGwPfJFVT694xxB1RbRG0jLbu0y7b6ntJUPV1Lf0Ubfjnt2fV3Qf63YfEbGqayW9FvgYpSvkIMr5pvNWWtQRcZfSDSoeBTySEtRnA8dkMDGqkXQaZSvHX3e37wWcZHufYSuLaIukDW3/dug6ashgYnsWjUIayinkwOYD1hPRFEl7SLoEuKS7vaOk9w1cVq8S1O25VdLWoxvdCdx52xMx5Z3APnT90rYvpHSDzFsZTGzPa4BzJJ3V3X4k5RTuiOjYvnJ0jmbn1qFqqSFB3RjbX+5Omn4YZTL/EbavHbisiJZcKWkPwJLWBV7B1MG381K6PhrTHS/0eGBn25+nnPKy28BlRbTkJcBLgS0oBwn8NeXQ43krsz4aI+k4yh4fe9veoZv1cartXQcuLaIJkva0/Y2Z7ptP0qJuz+62XwrcBLfP+sjCl4gpx87yvnkjfdTtuaXb5tQAkhYxtYtexIIl6eHAHsCisS0XADYC5vW5kmlRt+c9wCnA5pLeBJwD/OuwJUU0YV3KKfTrULZcGH3cADxjwLp6lz7qBkl6IPBoyqyPM2zP6xHtiDtD0n1t/2ToOmpKUDdG0oeAY21fMHbf0baPHq6qiOFJepftV0r6PBMWgdned4CyqkhQN0bSVcC1wDts/2d333LbOw9bWcSwJO1ie5mkv5n0uO2zJt0/HySoGyNpOfAo4ETKdqeHA9+xvdOQdUXEcDKY2B7ZvsH2U4CVwFnAxgPXFNEMSXtKOk3S/0i6TNLlki4buq4+ZXpeez43+sT20ZKWAq9aw/MjFpoPAUcAy5jne3yMpOujEZK2Be49YcXVI4Gf2v7RMJVFtEXSubZ3H7qOmtL10Y53Ab+ZcP/vuscioviqpLdJeriknUcfQxfVp7SoGyHpe7YfsprHLrL90No1RbRI0lcn3G3be1cvppL0UbdjvTU8tn61KiIaZ3uvoWuoLUHdju9IepHtD4zfKemFlEGTiAVt2v4eUBa9XAucY/vyAUqqJl0fjZB0b8oeHzczFcxLKPsbPM32z4eqLaIFko6acPemlGO5jrZ9UuWSqklQN0bSXsCor/pi22cOWU9E6yRtCpw+n1fvJqgj4i5P0vnzefVupudFxF2apL2BXw1dR58ymBgRdwmSLuKOu+ZtCvwMeG79iupJ10dE3CVIuu+0uwxcZ/vGIeqpKUEdEdG49FFHRDQuQR0R0bgEdURE4xLUERGNS1BHRDTu/wNZJk+3qypd4gAAAABJRU5ErkJggg==\n",
"text/plain": [
"<Figure size 432x288 with 1 Axes>"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"# plot the average order value on a bar chart\n",
"avg_order = country_analysis_other['avg_order_value'].plot.bar().set_xticklabels(country_analysis_other['country_name'])"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Average orders are pretty similiar across all countries. Interestingly, the countries with the highest average order also seem to have the fewest customers (with the exception of Portugal). Maybe expensive shipping rates make smaller orders too cost-prohibitive for most customers.\n",
"\n",
"The USA is our best customer by far, having the most total sales and the most total customers. The next closest is Canada at roughly half the number of customers and sales. \n",
"\n",
"The Czech Republic has few customers but the ones that do order tend to be repeat customers. \n",
"\n",
"The 'Other' category contains all the countries with only 1 customer. Considering Australia and the UK are other english speaking countries that can be similiar to the American and Canadian audience in music taste, it might be worth spending some advertising money there to see if any growth can be spurred.\n",
"\n",
"### Answer 3:\n",
"We would recommend some advertising spending in the UK, Australia, and maybe the Czech Republic to try new markets. Shipping may be the reason small orders aren't being made, so consider incentives for large orders.\n",
"\n",
"Advertising in Canada and the USA would support existing audiences. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Question 4: What percentage of purchases are individual tracks vs whole albums?\n",
"\n",
"We want to know if we can order just the popular singles rather than full albums without disrupting most of our customers. \n",
"\n",
"To find out, we'll get lists of tracks from the invoices and compare them to the list of tracks on the albums to see how often a whole album is purchased."
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" invoice_id customer_id full_album\n",
"0 1 18 1\n",
"1 2 30 0\n",
"2 3 40 0\n",
"3 4 18 0\n",
"4 5 27 1\n",
"5 6 31 0\n",
"6 7 49 0\n",
"7 8 59 0\n",
"8 9 18 0\n",
"9 10 31 0\n",
"10 11 38 0\n",
"11 12 42 0\n",
"12 13 35 0\n",
"13 14 25 0\n",
"14 15 44 0\n",
"15 16 1 0\n",
"16 17 20 0\n",
"17 18 24 0\n",
"18 19 10 0\n",
"19 20 43 0\n",
"20 21 9 0\n",
"21 22 35 0\n",
"22 23 53 1\n",
"23 24 4 1\n",
"24 25 39 0\n"
]
}
],
"source": [
"# find the purchases that included full albums\n",
"# helpers:\n",
"# - invoice_track_list : the list of tracks on an invoice\n",
"# - album_track_list : the list of tracks on an album\n",
"# - full_album : 1 indicated a complete album was purchased\n",
"album_query = \"\"\"\n",
"WITH \n",
"invoice_track_list AS\n",
"(\n",
"SELECT i.invoice_id, t.name FROM track t\n",
"LEFT JOIN invoice_line il ON il.track_id=t.track_id\n",
"LEFT JOIN invoice i ON i.invoice_id=il.invoice_id\n",
"),\n",
"album_track_list AS\n",
"(\n",
"SELECT a.album_id, t.name FROM track t\n",
"LEFT JOIN album a ON a.album_id=t.album_id\n",
"),\n",
"full_albums AS (\n",
"SELECT \n",
" i.invoice_id, \n",
" i.customer_id,\n",
" CASE \n",
" WHEN \n",
" (SELECT name FROM invoice_track_list\n",
" WHERE invoice_id = i.invoice_id\n",
" EXCEPT\n",
" SELECT name FROM album_track_list\n",
" WHERE album_id = t.album_id)\n",
" IS NULL\n",
" AND\n",
" (\n",
" SELECT name FROM album_track_list\n",
" WHERE album_id = t.album_id\n",
" EXCEPT\n",
" SELECT name FROM invoice_track_list\n",
" WHERE invoice_id = i.invoice_id) IS NULL\n",
" THEN 1\n",
" ELSE 0 \n",
" END AS full_album\n",
"FROM invoice i\n",
"LEFT JOIN invoice_line il on i.invoice_id=il.invoice_id\n",
"LEFT JOIN track t on t.track_id=il.track_id\n",
"LEFT JOIN album a on a.album_id=t.album_id\n",
"GROUP BY i.invoice_id\n",
")\n",
"\n",
"SELECT * FROM full_albums\n",
"\"\"\"\n",
"# show the first 25 results\n",
"album_analysis = run_query(album_query)\n",
"print(album_analysis.head(25))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Let's save these queries so we can easily refer to them."
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [
{
"ename": "OperationalError",
"evalue": "table invoice_track_list already exists",
"output_type": "error",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[0;31mOperationalError\u001b[0m Traceback (most recent call last)",
"\u001b[0;32m<ipython-input-20-396a0536c52a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m 6\u001b[0m \u001b[0mLEFT\u001b[0m \u001b[0mJOIN\u001b[0m \u001b[0minvoice_line\u001b[0m \u001b[0mil\u001b[0m \u001b[0mON\u001b[0m \u001b[0mil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtrack_id\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mt\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtrack_id\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 7\u001b[0m \u001b[0mLEFT\u001b[0m \u001b[0mJOIN\u001b[0m \u001b[0minvoice\u001b[0m \u001b[0mi\u001b[0m \u001b[0mON\u001b[0m \u001b[0mi\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minvoice_id\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minvoice_id\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 8\u001b[0;31m \"\"\")\n\u001b[0m",
"\u001b[0;32m<ipython-input-4-330f5e7f177b>\u001b[0m in \u001b[0;36mrun_command\u001b[0;34m(c)\u001b[0m\n\u001b[1;32m 4\u001b[0m \u001b[0;31m#auto commit any changes\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 5\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0misolation_level\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mc\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
"\u001b[0;31mOperationalError\u001b[0m: table invoice_track_list already exists"
]
}
],
"source": [
"#will cause an error if run more than once\n",
"#save a view to use invoice_track_list everywhere\n",
"run_command(\"\"\"\n",
"CREATE VIEW invoice_track_list AS\n",
"SELECT i.invoice_id, t.name FROM track t\n",
"LEFT JOIN invoice_line il ON il.track_id=t.track_id\n",
"LEFT JOIN invoice i ON i.invoice_id=il.invoice_id\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"#will cause an error if run more than once\n",
"#save a view to use album_track_list\n",
"run_command(\"\"\"\n",
"CREATE VIEW album_track_list AS\n",
"SELECT a.album_id, t.name FROM track t\n",
"LEFT JOIN album a ON a.album_id=t.album_id\n",
"\"\"\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"#will cause an error if run more than once\n",
"#save a view to use full_album\n",
"run_command(\"\"\"\n",
"CREATE VIEW full_album AS\n",
"SELECT \n",
" i.invoice_id, \n",
" i.customer_id,\n",
" CASE \n",
" WHEN \n",
" (SELECT name FROM invoice_track_list\n",
" WHERE invoice_id = i.invoice_id\n",
" EXCEPT\n",
" SELECT name FROM album_track_list\n",
" WHERE album_id = t.album_id)\n",
" IS NULL\n",
" AND\n",
" (\n",
" SELECT name FROM album_track_list\n",
" WHERE album_id = t.album_id\n",
" EXCEPT\n",
" SELECT name FROM invoice_track_list\n",
" WHERE invoice_id = i.invoice_id) IS NULL\n",
" THEN 1\n",
" ELSE 0 \n",
" END AS full_album\n",
"FROM invoice i\n",
"LEFT JOIN invoice_line il on i.invoice_id=il.invoice_id\n",
"LEFT JOIN track t on t.track_id=il.track_id\n",
"LEFT JOIN album a on a.album_id=t.album_id\n",
"GROUP BY i.invoice_id\n",
"\"\"\")"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Before we continue, let's double check that our results are true. We will visually check the album track list against the invoice track list to make sure we see all the album tracks. "
]
},
{
"cell_type": "code",
"execution_count": 21,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" name\n",
"0 Right Next Door to Hell\n",
"1 Dust N' Bones\n",
"2 Live and Let Die\n",
"3 Don't Cry (Original)\n",
"4 Perfect Crime\n",
"5 You Ain't the First\n",
"6 Bad Obsession\n",
"7 Back off Bitch\n",
"8 Double Talkin' Jive\n",
"9 November Rain\n",
"10 The Garden\n",
"11 Garden of Eden\n",
"12 Don't Damn Me\n",
"13 Bad Apples\n",
"14 Dead Horse\n",
"15 Coma\n",
" album_id name\n",
"0 91 Right Next Door to Hell\n",
"1 91 Dust N' Bones\n",
"2 91 Live and Let Die\n",
"3 91 Don't Cry (Original)\n",
"4 91 Perfect Crime\n",
"5 91 You Ain't the First\n",
"6 91 Bad Obsession\n",
"7 91 Back off Bitch\n",
"8 91 Double Talkin' Jive\n",
"9 91 November Rain\n",
"10 91 The Garden\n",
"11 91 Garden of Eden\n",
"12 91 Don't Damn Me\n",
"13 91 Bad Apples\n",
"14 91 Dead Horse\n",
"15 91 Coma\n",
"16 92 Civil War\n",
"17 92 14 Years\n",
"18 92 Yesterdays\n",
"19 92 Knockin' On Heaven's Door\n",
"20 92 Get In The Ring\n",
"21 92 Shotgun Blues\n",
"22 92 Breakdown\n",
"23 92 Pretty Tied Up\n",
"24 92 Locomotive\n",
"25 92 So Fine\n",
"26 92 Estranged\n",
"27 92 You Could Be Mine\n",
"28 92 Don't Cry\n",
"29 92 My World\n"
]
}
],
"source": [
"#check if invoice #1 is really a full album purchase\n",
"test_q = run_query(\"SELECT name FROM invoice_track_list WHERE invoice_id = 1\")\n",
"print(test_q)\n",
"test_a = run_query(\"SELECT a.album_id, t.name FROM track t LEFT JOIN album a ON a.album_id=t.album_id WHERE a.title LIKE '%Use Your Illusion I%'\")\n",
"print(test_a)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"We can see the query is working correctly because the full album appears on invoice #1.\n",
"\n",
"Now that we know our results are correct, let's count the number of full albums."
]
},
{
"cell_type": "code",
"execution_count": 22,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" total_full_albums pct_full_albums\n",
"0 114 0.185668\n"
]
}
],
"source": [
"test_q = \"\"\"\n",
"SELECT SUM(fa.full_album) total_full_albums, CAST(SUM(fa.full_album) as float)/CAST(COUNT(*)as float) pct_full_albums FROM full_album fa\n",
"\"\"\"\n",
"result = run_query(test_q)\n",
"print(result)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Of the over 600 purchases, 114 contained full_albums, or roughly 18%. \n",
"\n",
"### Answer 4:\n",
"\n",
"Management can go ahead with buying only popular songs because less than 1/5th of business is geared towards full albums. \n",
"\n",
"## Conclusion\n",
"\n",
"Using our database we have identified our most popular genres, the best countries for sales, and the album buying behavior of our customers. We also looked at employee performance. \n"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"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.6"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment