8. Python 101 - Python Libraries for Data Analysis - Pandas Solution.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"8. Python 101 - Python Libraries for Data Analysis - Pandas Solution.ipynb","provenance":[],"collapsed_sections":[],"authorship_tag":"ABX9TyMd8HToOL1IC3RQ4wvq+dYs"},"kernelspec":{"name":"python3","display_name":"Python 3"}},"cells":[{"cell_type":"markdown","metadata":{"id":"1ivPDcc7p8lD","colab_type":"text"},"source":["# 1. PANDAS BASICS"]},{"cell_type":"code","metadata":{"id":"4YdCraz8p_XF","colab_type":"code","colab":{}},"source":["# Pandas is a data manipulation and analysis tool that is built on Numpy.\n","# Pandas uses a data structure known as DataFrame (think of it as Microsoft excel in Python). \n","# DataFrames empower programmers to store and manipulate data in a tabular fashion (rows and columns).\n","# Series Vs. DataFrame? Series is considered a single column of a DataFrame."],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"6H2808pkqKIR","colab_type":"code","colab":{},"executionInfo":{"status":"ok","timestamp":1600576549022,"user_tz":240,"elapsed":543,"user":{"displayName":"Stemplicity","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gj2vCFt-bD5mPBhUWX0QA6xiBgoXQXtwUm2jgjaCg=s64","userId":"10668071569687886186"}}},"source":["import pandas as pd "],"execution_count":2,"outputs":[]},{"cell_type":"code","metadata":{"id":"jHc9-dmUqX48","colab_type":"code","colab":{}},"source":["# Let's define two lists as shown below:\n","my_list = ['AAPL','AMZN','T']\n","my_list\n"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"MU5GA6-EsjJt","colab_type":"code","colab":{}},"source":["label   = ['stock#1', 'stock#2', 'stock#3']\n","label"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"flWWE-6YqX7Z","colab_type":"code","colab":{}},"source":["type(my_list)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"0mf-BKxQsmKO","colab_type":"code","colab":{}},"source":["type(label)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"U4Fq06XwqX9v","colab_type":"code","colab":{}},"source":["# Let's create a one dimensional Pandas \"series\" \n","# Note that series is formed of data and associated labels \n","x_series = pd.Series(data = my_list, index = label)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"YhPzprisqX_8","colab_type":"code","colab":{}},"source":["# Let's view the series\n","x_series"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"4LPwMO8fsvi1","colab_type":"code","colab":{}},"source":["# Let's obtain the datatype\n","type(x_series)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"YnV7pFX0q3_C","colab_type":"code","colab":{}},"source":["# Let's define a two-dimensional Pandas DataFrame\n","# Note that you can create a pandas dataframe from a python dictionary\n","bank_client_df = pd.DataFrame({'Bank client ID':[111, 222, 333, 444], \n","                               'Bank Client Name':['Chanel', 'Steve', 'Mitch', 'Ryan'], \n","                               'Net worth [$]':[3500, 29000, 10000, 2000], \n","                               'Years with bank':[3, 4, 9, 5]})\n","bank_client_df"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"9LXSKDSTq4Fb","colab_type":"code","colab":{}},"source":["# Let's obtain the data type \n","type(bank_client_df)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"h93nb1trtwhu","colab_type":"code","colab":{}},"source":["# you can only view the first couple of rows using .head()\n","bank_client_df.head(2)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"7kkAAG0Ft2Q1","colab_type":"code","colab":{}},"source":["# you can only view the last couple of rows using .tail()\n","bank_client_df.tail(1)"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"Ro-OAYbsy_EA","colab_type":"text"},"source":["**MINI CHALLENGE #1:**\n","- **A porfolio contains a collection of securities such as stocks, bonds and ETFs. Define a dataframe named 'portfolio_df' that holds 3 different stock ticker symbols, number of shares, and price per share (feel free to choose any stocks)**\n","- **Calculate the total value of the porfolio including all stocks**"]},{"cell_type":"code","metadata":{"id":"zgAFS4N-1cDg","colab_type":"code","colab":{}},"source":[""],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"d06IyHT9uffC","colab_type":"text"},"source":["# 2. PANDAS WITH CSV AND HTML DATA"]},{"cell_type":"code","metadata":{"id":"6C6tjxUCwV8B","colab_type":"code","colab":{}},"source":["# In order to access data on Google Drive, you need to mount the drive to access it's content\n","from google.colab import drive\n","drive.mount('/content/drive')"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"lHB4RP5luJwF","colab_type":"code","colab":{}},"source":["# Pandas is used to read a csv file and store data in a DataFrame\n","bank_df = pd.read_csv('/content/drive/My Drive/Colab Notebooks/Python & ML in Finance/Part 1. Python Programming Fundamentals/bank_client_information.csv')"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"OqdhTgIfuoTy","colab_type":"code","colab":{}},"source":["bank_df"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"lhwAwJ7BuoXn","colab_type":"code","colab":{}},"source":["# write to a csv file without an index\n","bank_df.to_csv('sample_output.csv', index = False)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"Xbhgb5EYxR3o","colab_type":"code","colab":{}},"source":["# write to a csv file with an index\n","bank_df.to_csv('sample_output.csv', index = True)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"tAJRGqF-uobr","colab_type":"code","colab":{}},"source":["# Read tabular data using read_html\n","house_prices_df = pd.read_html('https://www.livingin-canada.com/house-prices-canada.html')"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"Al8bvyYEuods","colab_type":"code","colab":{}},"source":["house_prices_df[0]"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"K-abffkEuoZs","colab_type":"code","colab":{}},"source":["house_prices_df[1]"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"jRtr2lpr2NHr","colab_type":"text"},"source":["**MINI CHALLENGE #2:**\n","- **Write a code that uses Pandas to read tabular US retirement data**\n","- **You can use data from here: https://www.ssa.gov/oact/progdata/nra.html** "]},{"cell_type":"code","metadata":{"id":"qF-eEa5h27rh","colab_type":"code","colab":{}},"source":[""],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"usUtfW0WuuBb","colab_type":"text"},"source":["# 3. PANDAS OPERATIONS"]},{"cell_type":"code","metadata":{"id":"wI7pvcR01NbV","colab_type":"code","colab":{}},"source":["# Let's define a dataframe as follows:\n","bank_client_df = pd.DataFrame({'Bank client ID':[111, 222, 333, 444], \n","                               'Bank Client Name':['Chanel', 'Steve', 'Mitch', 'Ryan'], \n","                               'Net worth [$]':[3500, 29000, 10000, 2000], \n","                               'Years with bank':[3, 4, 9, 5]})\n","bank_client_df"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"kw_260rpuxzc","colab_type":"code","colab":{}},"source":["# Pick certain rows that satisfy a certain criteria \n","df_loyal = bank_client_df[ (bank_client_df['Years with bank'] >= 5) ]\n","df_loyal"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"uudZyadKux12","colab_type":"code","colab":{}},"source":["# Delete a column from a DataFrame\n","del bank_client_df['Bank client ID']\n","bank_client_df"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"M5_PHunK2_UZ","colab_type":"text"},"source":["**MINI CHALLENGE #3:**\n","- **Using \"bank_client_df\" DataFrame, leverage pandas operations to only select high networth individuals with minimum $5000** \n","- **What is the combined networth for all customers with 5000+ networth?**"]},{"cell_type":"code","metadata":{"id":"wLyH7CnC4Vjj","colab_type":"code","colab":{}},"source":[""],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"3-5RHcLDu1wL","colab_type":"text"},"source":["# 4. PANDAS WITH FUNCTIONS"]},{"cell_type":"code","metadata":{"id":"w6Vs9IxC14kV","colab_type":"code","colab":{}},"source":["# Let's define a dataframe as follows:\n","bank_client_df = pd.DataFrame({'Bank client ID':[111, 222, 333, 444], \n","                               'Bank Client Name':['Chanel', 'Steve', 'Mitch', 'Ryan'], \n","                               'Net worth [$]':[3500, 29000, 10000, 2000], \n","                               'Years with bank':[3, 4, 9, 5]})\n","bank_client_df"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"X8HedZiGux37","colab_type":"code","colab":{}},"source":["# Define a function that increases all clients networth (stocks) by a fixed value of 10% (for simplicity sake) \n","def networth_update(balance):\n","    return balance * 1.1 # assume that stock prices increased by 10%"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"H15eem3Qu67H","colab_type":"code","colab":{}},"source":["# You can apply a function to the DataFrame \n","bank_client_df['Net worth [$]'].apply(networth_update)\n"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"WsObpnkou69Q","colab_type":"code","colab":{}},"source":["bank_client_df['Bank Client Name'].apply(len)"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"E52xRMulu6_I","colab_type":"code","colab":{}},"source":["bank_client_df['Years with bank'].sum()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"VaXV-VYg4xG0","colab_type":"text"},"source":["**MINI CHALLENGE #4:**\n","- **Define a function that doubles stock prices and adds $100**\n","- **Apply the function to the DataFrame**\n","- **Calculate the updated total networth of all clients combined**"]},{"cell_type":"code","metadata":{"id":"xqWCYc0n6V-d","colab_type":"code","colab":{}},"source":[""],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"LKDuJ94RvB85","colab_type":"text"},"source":["# 5. SORTING AND ORDERING"]},{"cell_type":"code","metadata":{"id":"f-aRTmn9u7DA","colab_type":"code","colab":{}},"source":["# Let's define a dataframe as follows:\n","bank_client_df = pd.DataFrame({'Bank client ID':[111, 222, 333, 444], \n","                               'Bank Client Name':['Chanel', 'Steve', 'Mitch', 'Ryan'], \n","                               'Net worth [$]':[3500, 29000, 10000, 2000], \n","                               'Years with bank':[3, 4, 9, 5]})\n","bank_client_df"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"DaglhPNevFYC","colab_type":"code","colab":{}},"source":["# You can sort the values in the dataframe according to number of years with bank\n","bank_client_df.sort_values(by = 'Years with bank') "],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"AjzLtDlevFaW","colab_type":"code","colab":{}},"source":["# Note that nothing changed in memory! you have to make sure that inplace is set to True\n","bank_client_df"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"Pgt25eXmvFce","colab_type":"code","colab":{}},"source":["# Set inplace = True to ensure that change has taken place in memory \n","bank_client_df.sort_values(by = 'Years with bank', inplace = True) "],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"jEw2Gw_hu7Fo","colab_type":"code","colab":{}},"source":["# Note that now the change (ordering) took place \n","bank_client_df"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"7bV_Yaq46ZTg","colab_type":"text"},"source":["**MINI CHALLENGE #5:**\n","- **Sort customers by networth instead of years with bank. Make sure to update values in-memory.**"]},{"cell_type":"code","metadata":{"id":"BYCD1yGi6q2M","colab_type":"code","colab":{}},"source":[""],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"XAix4DVfvKGe","colab_type":"text"},"source":["# 6. CONCATENATING AND MERGING WITH PANDAS"]},{"cell_type":"markdown","metadata":{"id":"eMBioTTMvWXI","colab_type":"text"},"source":["Check this out: https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html"]},{"cell_type":"code","metadata":{"id":"b1zfk3Euu7Bx","colab_type":"code","colab":{}},"source":["df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],\n","                    'B': ['B0', 'B1', 'B2', 'B3'],\n","                    'C': ['C0', 'C1', 'C2', 'C3'],\n","                    'D': ['D0', 'D1', 'D2', 'D3']},\n","index=[0, 1, 2, 3])"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"MfimJ_PPvQIA","colab_type":"code","colab":{}},"source":["df1"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"dKyIG2MjvQKN","colab_type":"code","colab":{}},"source":["df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],\n","                    'B': ['B4', 'B5', 'B6', 'B7'],\n","                    'C': ['C4', 'C5', 'C6', 'C7'],\n","                    'D': ['D4', 'D5', 'D6', 'D7']},\n","index=[4, 5, 6, 7]) "],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"wNuUFBsqvQMU","colab_type":"code","colab":{}},"source":["df2"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"BTmgXXvzvcKq","colab_type":"code","colab":{}},"source":["df3 = pd.DataFrame({'A': ['A8', 'A9', 'A10', 'A11'],\n","                    'B': ['B8', 'B9', 'B10', 'B11'],\n","                    'C': ['C8', 'C9', 'C10', 'C11'],\n","                    'D': ['D8', 'D9', 'D10', 'D11']},\n","index=[8, 9, 10, 11])"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"wI10aNahvcM1","colab_type":"code","colab":{}},"source":["df3"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"ljXyrXSjvcPG","colab_type":"code","colab":{}},"source":["pd.concat([df1, df2, df3])"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"4_KBD8YGvcRV","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":202},"executionInfo":{"status":"ok","timestamp":1600576555144,"user_tz":240,"elapsed":566,"user":{"displayName":"Stemplicity","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gj2vCFt-bD5mPBhUWX0QA6xiBgoXQXtwUm2jgjaCg=s64","userId":"10668071569687886186"}},"outputId":"ad3819d4-c034-4502-a387-c692b727d55d"},"source":["# Creating a dataframe from a dictionary\n","# Let's define a dataframe with a list of bank clients with IDs = 1, 2, 3, 4, 5 \n","\n","raw_data = {'Bank Client ID': ['1', '2', '3', '4', '5'],\n","            'First Name': ['Nancy', 'Alex', 'Shep', 'Max', 'Allen'], \n","            'Last Name': ['Rob', 'Ali', 'George', 'Mitch', 'Steve']}\n","\n","Bank_df_1 = pd.DataFrame(raw_data, columns = ['Bank Client ID', 'First Name', 'Last Name'])\n","Bank_df_1"],"execution_count":3,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Bank Client ID</th>\n","      <th>First Name</th>\n","      <th>Last Name</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1</td>\n","      <td>Nancy</td>\n","      <td>Rob</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>2</td>\n","      <td>Alex</td>\n","      <td>Ali</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>3</td>\n","      <td>Shep</td>\n","      <td>George</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>4</td>\n","      <td>Max</td>\n","      <td>Mitch</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>5</td>\n","      <td>Allen</td>\n","      <td>Steve</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["  Bank Client ID First Name Last Name\n","0              1      Nancy       Rob\n","1              2       Alex       Ali\n","2              3       Shep    George\n","3              4        Max     Mitch\n","4              5      Allen     Steve"]},"metadata":{"tags":[]},"execution_count":3}]},{"cell_type":"code","metadata":{"id":"wq_TBWkvvgNE","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":202},"executionInfo":{"status":"ok","timestamp":1600576556355,"user_tz":240,"elapsed":529,"user":{"displayName":"Stemplicity","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gj2vCFt-bD5mPBhUWX0QA6xiBgoXQXtwUm2jgjaCg=s64","userId":"10668071569687886186"}},"outputId":"60a02319-2123-43a7-b4d6-7391cfb406f0"},"source":["# Let's define another dataframe for a separate list of clients (IDs = 6, 7, 8, 9, 10)\n","raw_data = {\n","        'Bank Client ID': ['6', '7', '8', '9', '10'],\n","        'First Name': ['Bill', 'Dina', 'Sarah', 'Heather', 'Holly'], \n","        'Last Name': ['Christian', 'Mo', 'Steve', 'Bob', 'Michelle']}\n","Bank_df_2 = pd.DataFrame(raw_data, columns = ['Bank Client ID', 'First Name', 'Last Name'])\n","Bank_df_2"],"execution_count":4,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Bank Client ID</th>\n","      <th>First Name</th>\n","      <th>Last Name</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>6</td>\n","      <td>Bill</td>\n","      <td>Christian</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>7</td>\n","      <td>Dina</td>\n","      <td>Mo</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>8</td>\n","      <td>Sarah</td>\n","      <td>Steve</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>9</td>\n","      <td>Heather</td>\n","      <td>Bob</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>10</td>\n","      <td>Holly</td>\n","      <td>Michelle</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["  Bank Client ID First Name  Last Name\n","0              6       Bill  Christian\n","1              7       Dina         Mo\n","2              8      Sarah      Steve\n","3              9    Heather        Bob\n","4             10      Holly   Michelle"]},"metadata":{"tags":[]},"execution_count":4}]},{"cell_type":"code","metadata":{"id":"zRgyJPjivgPZ","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":355},"executionInfo":{"status":"ok","timestamp":1600576630669,"user_tz":240,"elapsed":504,"user":{"displayName":"Stemplicity","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gj2vCFt-bD5mPBhUWX0QA6xiBgoXQXtwUm2jgjaCg=s64","userId":"10668071569687886186"}},"outputId":"7d8b1433-3b21-489e-f610-8516728e7e05"},"source":["# Let's assume we obtained additional information (Annual Salary) about our bank customers \n","# Note that data obtained is for all clients with IDs 1 to 10\n"," \n","raw_data = {\n","        'Bank Client ID': ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10'],\n","        'Annual Salary [$/year]': [25000, 35000, 45000, 48000, 49000, 32000, 33000, 34000, 23000, 22000]}\n","bank_df_salary = pd.DataFrame(raw_data, columns = ['Bank Client ID','Annual Salary [$/year]'])\n","bank_df_salary"],"execution_count":9,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Bank Client ID</th>\n","      <th>Annual Salary [$/year]</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1</td>\n","      <td>25000</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>2</td>\n","      <td>35000</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>3</td>\n","      <td>45000</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>4</td>\n","      <td>48000</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>5</td>\n","      <td>49000</td>\n","    </tr>\n","    <tr>\n","      <th>5</th>\n","      <td>6</td>\n","      <td>32000</td>\n","    </tr>\n","    <tr>\n","      <th>6</th>\n","      <td>7</td>\n","      <td>33000</td>\n","    </tr>\n","    <tr>\n","      <th>7</th>\n","      <td>8</td>\n","      <td>34000</td>\n","    </tr>\n","    <tr>\n","      <th>8</th>\n","      <td>9</td>\n","      <td>23000</td>\n","    </tr>\n","    <tr>\n","      <th>9</th>\n","      <td>10</td>\n","      <td>22000</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["  Bank Client ID  Annual Salary [$/year]\n","0              1                   25000\n","1              2                   35000\n","2              3                   45000\n","3              4                   48000\n","4              5                   49000\n","5              6                   32000\n","6              7                   33000\n","7              8                   34000\n","8              9                   23000\n","9             10                   22000"]},"metadata":{"tags":[]},"execution_count":9}]},{"cell_type":"code","metadata":{"id":"i2X65wc0vidf","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":355},"executionInfo":{"status":"ok","timestamp":1600576633113,"user_tz":240,"elapsed":1324,"user":{"displayName":"Stemplicity","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gj2vCFt-bD5mPBhUWX0QA6xiBgoXQXtwUm2jgjaCg=s64","userId":"10668071569687886186"}},"outputId":"7fc07217-c37e-4107-b551-3b58f8024cff"},"source":["# Let's concatenate both dataframes #1 and #2\n","# Note that we now have client IDs from 1 to 10\n","bank_df_all = pd.concat([Bank_df_1, Bank_df_2])\n","bank_df_all"],"execution_count":10,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Bank Client ID</th>\n","      <th>First Name</th>\n","      <th>Last Name</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1</td>\n","      <td>Nancy</td>\n","      <td>Rob</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>2</td>\n","      <td>Alex</td>\n","      <td>Ali</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>3</td>\n","      <td>Shep</td>\n","      <td>George</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>4</td>\n","      <td>Max</td>\n","      <td>Mitch</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>5</td>\n","      <td>Allen</td>\n","      <td>Steve</td>\n","    </tr>\n","    <tr>\n","      <th>0</th>\n","      <td>6</td>\n","      <td>Bill</td>\n","      <td>Christian</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>7</td>\n","      <td>Dina</td>\n","      <td>Mo</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>8</td>\n","      <td>Sarah</td>\n","      <td>Steve</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>9</td>\n","      <td>Heather</td>\n","      <td>Bob</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>10</td>\n","      <td>Holly</td>\n","      <td>Michelle</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["  Bank Client ID First Name  Last Name\n","0              1      Nancy        Rob\n","1              2       Alex        Ali\n","2              3       Shep     George\n","3              4        Max      Mitch\n","4              5      Allen      Steve\n","0              6       Bill  Christian\n","1              7       Dina         Mo\n","2              8      Sarah      Steve\n","3              9    Heather        Bob\n","4             10      Holly   Michelle"]},"metadata":{"tags":[]},"execution_count":10}]},{"cell_type":"code","metadata":{"id":"9bgjH8mkvQOY","colab_type":"code","colab":{},"executionInfo":{"status":"ok","timestamp":1600576634045,"user_tz":240,"elapsed":447,"user":{"displayName":"Stemplicity","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gj2vCFt-bD5mPBhUWX0QA6xiBgoXQXtwUm2jgjaCg=s64","userId":"10668071569687886186"}}},"source":["# Let's merge all data on 'Bank Client ID'\n","bank_df_all = pd.merge(bank_df_all, bank_df_salary, on = 'Bank Client ID')\n"],"execution_count":11,"outputs":[]},{"cell_type":"code","metadata":{"id":"5g5tCggp9gce","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":355},"executionInfo":{"status":"ok","timestamp":1600576637553,"user_tz":240,"elapsed":987,"user":{"displayName":"Stemplicity","photoUrl":"https://lh3.googleusercontent.com/a-/AOh14Gj2vCFt-bD5mPBhUWX0QA6xiBgoXQXtwUm2jgjaCg=s64","userId":"10668071569687886186"}},"outputId":"3e7f3eea-1d4f-4e50-9068-4d1720bd077f"},"source":["bank_df_all"],"execution_count":12,"outputs":[{"output_type":"execute_result","data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Bank Client ID</th>\n","      <th>First Name</th>\n","      <th>Last Name</th>\n","      <th>Annual Salary [$/year]</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1</td>\n","      <td>Nancy</td>\n","      <td>Rob</td>\n","      <td>25000</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>2</td>\n","      <td>Alex</td>\n","      <td>Ali</td>\n","      <td>35000</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>3</td>\n","      <td>Shep</td>\n","      <td>George</td>\n","      <td>45000</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>4</td>\n","      <td>Max</td>\n","      <td>Mitch</td>\n","      <td>48000</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>5</td>\n","      <td>Allen</td>\n","      <td>Steve</td>\n","      <td>49000</td>\n","    </tr>\n","    <tr>\n","      <th>5</th>\n","      <td>6</td>\n","      <td>Bill</td>\n","      <td>Christian</td>\n","      <td>32000</td>\n","    </tr>\n","    <tr>\n","      <th>6</th>\n","      <td>7</td>\n","      <td>Dina</td>\n","      <td>Mo</td>\n","      <td>33000</td>\n","    </tr>\n","    <tr>\n","      <th>7</th>\n","      <td>8</td>\n","      <td>Sarah</td>\n","      <td>Steve</td>\n","      <td>34000</td>\n","    </tr>\n","    <tr>\n","      <th>8</th>\n","      <td>9</td>\n","      <td>Heather</td>\n","      <td>Bob</td>\n","      <td>23000</td>\n","    </tr>\n","    <tr>\n","      <th>9</th>\n","      <td>10</td>\n","      <td>Holly</td>\n","      <td>Michelle</td>\n","      <td>22000</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["  Bank Client ID First Name  Last Name  Annual Salary [$/year]\n","0              1      Nancy        Rob                   25000\n","1              2       Alex        Ali                   35000\n","2              3       Shep     George                   45000\n","3              4        Max      Mitch                   48000\n","4              5      Allen      Steve                   49000\n","5              6       Bill  Christian                   32000\n","6              7       Dina         Mo                   33000\n","7              8      Sarah      Steve                   34000\n","8              9    Heather        Bob                   23000\n","9             10      Holly   Michelle                   22000"]},"metadata":{"tags":[]},"execution_count":12}]},{"cell_type":"markdown","metadata":{"id":"Wm5PRpCM-2pK","colab_type":"text"},"source":["**MINI CHALLENGE #6:**\n","- **Let's assume that you became a new client to the bank**\n","- **Define a new DataFrame that contains your information such as client ID (choose 11), first name, last name, and annual salary.**\n","- **Add this new dataframe to the original dataframe \"bank_df_all\".** "]},{"cell_type":"code","metadata":{"id":"WpoFZeCt_g0c","colab_type":"code","colab":{}},"source":[""],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"i1gJS3K7vlxd","colab_type":"text"},"source":["# EXCELLENT JOB!"]},{"cell_type":"markdown","metadata":{"id":"MF2SdJZ_vn4z","colab_type":"text"},"source":["# MINI CHALLENGE SOLUTIONS"]},{"cell_type":"markdown","metadata":{"colab_type":"text","id":"Y6fUsXPv1Wom"},"source":["**MINI CHALLENGE #1 SOLUTION:**\n","- **A porfolio contains a collection of securities such as stocks, bonds and ETFs. Define a dataframe named 'portfolio_df' that holds 3 different stock ticker symbols, number of shares, and price per share (feel free to choose any stocks)**\n","- **Calculate the total value of the porfolio including all stocks**"]},{"cell_type":"code","metadata":{"colab_type":"code","id":"8pftwyY81Wos","colab":{}},"source":["portfolio_df = pd.DataFrame({'stock ticker symbols':['AAPL', 'AMZN', 'T'],\n","                             'price per share [$]':[3500, 200, 40], \n","                             'Number of stocks':[3, 4, 9]})\n","portfolio_df"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"colab_type":"code","id":"zTgj_BoE1Woy","colab":{}},"source":["stocks_dollar_value = portfolio_df['price per share [$]'] * portfolio_df['Number of stocks']\n","print(stocks_dollar_value)\n","print('Total portfolio value = {}'.format(stocks_dollar_value.sum()))"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"colab_type":"text","id":"5YQ1rFW723fN"},"source":["**MINI CHALLENGE #2 SOLUTION:**\n","- **Write a code that uses Pandas to read tabular US retirement data**\n","- **You can use data from here: https://www.ssa.gov/oact/progdata/nra.html** "]},{"cell_type":"code","metadata":{"colab_type":"code","id":"mmWTN7yD23fQ","colab":{}},"source":["# Read tabular data using read_html\n","retirement_age_df = pd.read_html('https://www.ssa.gov/oact/progdata/nra.html')\n","retirement_age_df"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"colab_type":"text","id":"qN1gAggk4QvE"},"source":["**MINI CHALLENGE #3 SOLUTION:**\n","- **Using \"bank_client_df\" DataFrame, leverage pandas operations to only select high networth individuals with minimum $5000** \n","- **What is the combined networth for all customers with 5000+ networth?**"]},{"cell_type":"code","metadata":{"colab_type":"code","id":"CSN32b-o4QvG","colab":{}},"source":["df_high_networth = bank_client_df[ (bank_client_df['Net worth [$]'] >= 5000) ]\n","df_high_networth"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"colab_type":"code","id":"8x2FQdmp4QvN","colab":{}},"source":["df_high_networth['Net worth [$]'].sum()"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"id":"zubS-MOi6O-y","colab_type":"code","colab":{}},"source":[""],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"colab_type":"text","id":"T3xpj-hW6PYi"},"source":["**MINI CHALLENGE #4 SOLUTION:**\n","- **Define a function that doubles stock prices and adds $100**\n","- **Apply the function to the DataFrame**\n","- **Calculate the updated total networth of all clients combined**"]},{"cell_type":"code","metadata":{"colab_type":"code","id":"S8O56b_W6PYk","colab":{}},"source":["def networth_update(balance):\n","    return balance * 2 + 100 # assume that stock prices increased by 10%"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"colab_type":"code","id":"r2crG2yx6PYt","colab":{}},"source":["# You can apply a function to the DataFrame \n","results = bank_client_df['Net worth [$]'].apply(networth_update)\n","results"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"colab_type":"code","id":"SAzpFD5Y6PY1","colab":{}},"source":["results.sum()"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"colab_type":"text","id":"Trx36mNb62JX"},"source":["**MINI CHALLENGE #5 SOLUTION:**\n","- **Sort customers by networth instead of years with bank. Make sure to update values in-memory.**"]},{"cell_type":"code","metadata":{"colab_type":"code","id":"b4nvA-VQ62Ja","colab":{}},"source":["# You can sort the values in the dataframe according to number of years with bank\n","bank_client_df.sort_values(by = 'Net worth [$]', inplace = True) \n","bank_client_df"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"colab_type":"text","id":"i83R6Kvb_cGZ"},"source":["**MINI CHALLENGE #6 SOLUTION:**\n","- **Let's assume that you became a new client to the bank**\n","- **Define a new DataFrame that contains your information such as client ID (choose 11), first name, last name, and annual salary.**\n","- **Add this new dataframe to the original dataframe \"bank_df_all\".** "]},{"cell_type":"code","metadata":{"colab_type":"code","id":"YmxlF6op_cGc","colab":{}},"source":["new_client = {\n","        'Bank Client ID': ['11'],\n","        'First Name': ['Ry'], \n","        'Last Name': ['Aly'],\n","        'Annual Salary [$/year]' : [1000]}\n","new_client_df = pd.DataFrame(new_client, columns = ['Bank Client ID', 'First Name', 'Last Name', 'Annual Salary [$/year]'])\n","new_client_df"],"execution_count":null,"outputs":[]},{"cell_type":"code","metadata":{"colab_type":"code","id":"dhOKOcVR_cGl","colab":{}},"source":["new_df = pd.concat([bank_df_all, new_client_df], axis = 0)\n","new_df"],"execution_count":null,"outputs":[]}]}