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

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"8. Python 101 - Python Libraries for Data Analysis - Pandas Skeleton.ipynb","provenance":[],"collapsed_sections":[],"authorship_tag":"ABX9TyPjsGobczCN4PBzrMR3LnNo"},"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":{}},"source":["import pandas as pd"],"execution_count":1,"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"],"execution_count":2,"outputs":[{"output_type":"execute_result","data":{"text/plain":["['AAPL', 'AMZN', 'T']"]},"metadata":{},"execution_count":2}]},{"cell_type":"code","metadata":{"id":"MU5GA6-EsjJt","colab_type":"code","colab":{}},"source":["label=['stock#1','stock#2','stock#3']\n","label\n"],"execution_count":2,"outputs":[{"output_type":"execute_result","data":{"text/plain":["['stock#1', 'stock#2', 'stock#3']"]},"metadata":{},"execution_count":2}]},{"cell_type":"code","metadata":{"id":"flWWE-6YqX7Z","colab_type":"code","colab":{}},"source":["type(label)"],"execution_count":3,"outputs":[{"output_type":"execute_result","data":{"text/plain":["list"]},"metadata":{},"execution_count":3}]},{"cell_type":"code","metadata":{"id":"0mf-BKxQsmKO","colab_type":"code","colab":{}},"source":["type(my_list)"],"execution_count":5,"outputs":[{"output_type":"execute_result","data":{"text/plain":["list"]},"metadata":{},"execution_count":5}]},{"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":7,"outputs":[]},{"cell_type":"code","metadata":{"id":"YhPzprisqX_8","colab_type":"code","colab":{}},"source":["# Let's view the series\n","x_series"],"execution_count":8,"outputs":[{"output_type":"execute_result","data":{"text/plain":["stock#1    AAPL\n","stock#2    AMZN\n","stock#3       T\n","dtype: object"]},"metadata":{},"execution_count":8}]},{"cell_type":"code","metadata":{"id":"4LPwMO8fsvi1","colab_type":"code","colab":{}},"source":["# Let's obtain the datatype\n","type(x_series)"],"execution_count":9,"outputs":[{"output_type":"execute_result","data":{"text/plain":["pandas.core.series.Series"]},"metadata":{},"execution_count":9}]},{"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":4,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","0             111           Chanel       3500                3\n","1             222            Steve      29000                4\n","2             333            Mitch      10000                9\n","3             444             Ryan       2000                5"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":4}]},{"cell_type":"code","metadata":{"id":"9LXSKDSTq4Fb","colab_type":"code","colab":{}},"source":["# Let's obtain the data type \n","type(bank_client_df)"],"execution_count":12,"outputs":[{"output_type":"execute_result","data":{"text/plain":["pandas.core.frame.DataFrame"]},"metadata":{},"execution_count":12}]},{"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":14,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","0             111           Chanel       3500                3\n","1             222            Steve      29000                4"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":14}]},{"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":16,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","3             444             Ryan       2000                5"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":16}]},{"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":["portfolio_df=pd.DataFrame({'Stock Ticker':['AMZN','AAPL','TSLA','TNT'],\n","                           'No of Shares ':[500,300,240,555],\n","                           'Price per Share [$]':[25,15,67,33]})\n","portfolio_df['Stock Value [$]']=portfolio_df['No of Shares ']*portfolio_df['Price per Share [$]']\n","portfolio_df"],"execution_count":14,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  Stock Ticker  No of Shares   Price per Share [$]  Stock Value [$]\n","0         AMZN            500                   25            12500\n","1         AAPL            300                   15             4500\n","2         TSLA            240                   67            16080\n","3          TNT            555                   33            18315"],"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>Stock Ticker</th>\n      <th>No of Shares</th>\n      <th>Price per Share [$]</th>\n      <th>Stock Value [$]</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>AMZN</td>\n      <td>500</td>\n      <td>25</td>\n      <td>12500</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>AAPL</td>\n      <td>300</td>\n      <td>15</td>\n      <td>4500</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>TSLA</td>\n      <td>240</td>\n      <td>67</td>\n      <td>16080</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>TNT</td>\n      <td>555</td>\n      <td>33</td>\n      <td>18315</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":14}]},{"cell_type":"code","metadata":{"id":"idvE_4vAH1lq","colab_type":"code","colab":{}},"source":["print(\"Total Value {}\".format(portfolio_df['Stock Value [$]'].sum()))"],"execution_count":15,"outputs":[{"output_type":"stream","name":"stdout","text":["Total Value 51395\n"]}]},{"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"],"execution_count":4,"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('G:\\Python+for+Financial+Analysis+-+Course+Package\\Part 1. Python Programming Fundamentals/bank_client_information.csv')"],"execution_count":5,"outputs":[]},{"cell_type":"code","metadata":{"id":"OqdhTgIfuoTy","colab_type":"code","colab":{}},"source":["bank_df"],"execution_count":22,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  First Name Last Name              Email Postal Code   Net Worth\n","0     Joseph    Patton     daafeja@boh.jm     M6U 5U7  $2,629.13 \n","1       Noah     Moran  guutodi@bigwoc.kw     K2D 4M9  $8,626.96 \n","2       Nina    Keller    azikez@gahew.mr     S1T 4E6  $9,072.02 "],"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>First Name</th>\n      <th>Last Name</th>\n      <th>Email</th>\n      <th>Postal Code</th>\n      <th>Net Worth</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Joseph</td>\n      <td>Patton</td>\n      <td>daafeja@boh.jm</td>\n      <td>M6U 5U7</td>\n      <td>$2,629.13</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Noah</td>\n      <td>Moran</td>\n      <td>guutodi@bigwoc.kw</td>\n      <td>K2D 4M9</td>\n      <td>$8,626.96</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Nina</td>\n      <td>Keller</td>\n      <td>azikez@gahew.mr</td>\n      <td>S1T 4E6</td>\n      <td>$9,072.02</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":22}]},{"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)\n","f=open('G:\\Python+for+Financial+Analysis+-+Course+Package\\Part 1. Python Programming Fundamentals/sample output.csv','r')\n","print(f.read())"],"execution_count":27,"outputs":[{"output_type":"stream","name":"stdout","text":["First Name,Last Name,Email,Postal Code,Net Worth\nJoseph,Patton,daafeja@boh.jm,M6U 5U7,\"$2,629.13 \"\nNoah,Moran,guutodi@bigwoc.kw,K2D 4M9,\"$8,626.96 \"\nNina,Keller,azikez@gahew.mr,S1T 4E6,\"$9,072.02 \"\n\n"]}]},{"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 index.csv',index=True)\n","f=open('G:\\Python+for+Financial+Analysis+-+Course+Package\\Part 1. Python Programming Fundamentals/sample output index.csv','r')\n","print(f.read())"],"execution_count":28,"outputs":[{"output_type":"stream","name":"stdout","text":[",First Name,Last Name,Email,Postal Code,Net Worth\n0,Joseph,Patton,daafeja@boh.jm,M6U 5U7,\"$2,629.13 \"\n1,Noah,Moran,guutodi@bigwoc.kw,K2D 4M9,\"$8,626.96 \"\n2,Nina,Keller,azikez@gahew.mr,S1T 4E6,\"$9,072.02 \"\n\n"]}]},{"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":3,"outputs":[]},{"cell_type":"code","metadata":{"id":"Al8bvyYEuods","colab_type":"code","colab":{}},"source":["house_prices_df[1]"],"execution_count":5,"outputs":[{"output_type":"execute_result","data":{"text/plain":["                                             Province  \\\n","0                                    British Columbia   \n","1                                             Ontario   \n","2                                             Alberta   \n","3                                              Quebec   \n","4                                            Manitoba   \n","5                                        Saskatchewan   \n","6                                         Nova Scotia   \n","7                                Prince Edward Island   \n","8                             Newfoundland / Labrador   \n","9                                       New Brunswick   \n","10                                   Canadian Average   \n","11  (adsbygoogle = window.adsbygoogle || []).push(...   \n","\n","                                  Average House Price  \\\n","0                                            $736,000   \n","1                                            $594,000   \n","2                                            $353,000   \n","3                                            $340,000   \n","4                                            $295,000   \n","5                                            $271,000   \n","6                                            $266,000   \n","7                                            $243,000   \n","8                                            $236,000   \n","9                                            $183,000   \n","10                                           $488,000   \n","11  (adsbygoogle = window.adsbygoogle || []).push(...   \n","\n","                                      12 Month Change  \n","0                                             + 7.6 %  \n","1                                             – 3.2 %  \n","2                                             – 7.5 %  \n","3                                             + 7.6 %  \n","4                                             – 1.4 %  \n","5                                             – 3.8 %  \n","6                                             + 3.5 %  \n","7                                             + 3.0 %  \n","8                                             – 1.6 %  \n","9                                             – 2.2 %  \n","10                                            – 1.3 %  \n","11  (adsbygoogle = window.adsbygoogle || []).push(...  "],"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>Province</th>\n      <th>Average House Price</th>\n      <th>12 Month Change</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>British Columbia</td>\n      <td>$736,000</td>\n      <td>+ 7.6 %</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Ontario</td>\n      <td>$594,000</td>\n      <td>– 3.2 %</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Alberta</td>\n      <td>$353,000</td>\n      <td>– 7.5 %</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Quebec</td>\n      <td>$340,000</td>\n      <td>+ 7.6 %</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Manitoba</td>\n      <td>$295,000</td>\n      <td>– 1.4 %</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Saskatchewan</td>\n      <td>$271,000</td>\n      <td>– 3.8 %</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Nova Scotia</td>\n      <td>$266,000</td>\n      <td>+ 3.5 %</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>Prince Edward Island</td>\n      <td>$243,000</td>\n      <td>+ 3.0 %</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>Newfoundland / Labrador</td>\n      <td>$236,000</td>\n      <td>– 1.6 %</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>New Brunswick</td>\n      <td>$183,000</td>\n      <td>– 2.2 %</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>Canadian Average</td>\n      <td>$488,000</td>\n      <td>– 1.3 %</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>(adsbygoogle = window.adsbygoogle || []).push(...</td>\n      <td>(adsbygoogle = window.adsbygoogle || []).push(...</td>\n      <td>(adsbygoogle = window.adsbygoogle || []).push(...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":5}]},{"cell_type":"code","metadata":{"id":"K-abffkEuoZs","colab_type":"code","colab":{}},"source":[""],"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":["US_Retirement=pd.read_html('https://www.ssa.gov/oact/progdata/nra.html')\n","US_Retirement[0]"],"execution_count":14,"outputs":[{"output_type":"execute_result","data":{"text/plain":["                                        Year of birth  \\\n","0                                      1937 and prior   \n","1                                                1938   \n","2                                                1939   \n","3                                                1940   \n","4                                                1941   \n","5                                                1942   \n","6                                             1943-54   \n","7                                                1955   \n","8                                                1956   \n","9                                                1957   \n","10                                               1958   \n","11                                               1959   \n","12                                     1960 and later   \n","13  Notes: 1. Persons born on January 1 of any yea...   \n","\n","                                                  Age  \n","0                                                  65  \n","1                                     65 and 2 months  \n","2                                     65 and 4 months  \n","3                                     65 and 6 months  \n","4                                     65 and 8 months  \n","5                                    65 and 10 months  \n","6                                                  66  \n","7                                     66 and 2 months  \n","8                                     66 and 4 months  \n","9                                     66 and 6 months  \n","10                                    66 and 8 months  \n","11                                   66 and 10 months  \n","12                                                 67  \n","13  Notes: 1. Persons born on January 1 of any yea...  "],"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>Year of birth</th>\n      <th>Age</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1937 and prior</td>\n      <td>65</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1938</td>\n      <td>65 and 2 months</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1939</td>\n      <td>65 and 4 months</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>1940</td>\n      <td>65 and 6 months</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1941</td>\n      <td>65 and 8 months</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>1942</td>\n      <td>65 and 10 months</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>1943-54</td>\n      <td>66</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>1955</td>\n      <td>66 and 2 months</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>1956</td>\n      <td>66 and 4 months</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>1957</td>\n      <td>66 and 6 months</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>1958</td>\n      <td>66 and 8 months</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>1959</td>\n      <td>66 and 10 months</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>1960 and later</td>\n      <td>67</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>Notes: 1. Persons born on January 1 of any yea...</td>\n      <td>Notes: 1. Persons born on January 1 of any yea...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":14}]},{"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":3,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","0             111           Chanel       3500                3\n","1             222            Steve      29000                4\n","2             333            Mitch      10000                9\n","3             444             Ryan       2000                5"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":3}]},{"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":4,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","2             333            Mitch      10000                9\n","3             444             Ryan       2000                5"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":4}]},{"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":5,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  Bank Client Name  Net Worth  Years With Bank\n","0           Chanel       3500                3\n","1            Steve      29000                4\n","2            Mitch      10000                9\n","3             Ryan       2000                5"],"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 Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":5}]},{"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":["high_net_worth=bank_client_df[(bank_client_df['Net Worth']>=5000)]\n","high_net_worth"],"execution_count":8,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  Bank Client Name  Net Worth  Years With Bank\n","1            Steve      29000                4\n","2            Mitch      10000                9"],"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 Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":8}]},{"cell_type":"code","metadata":{"id":"rr_nhgq-VGfC","colab_type":"code","colab":{}},"source":["cmbn=high_net_worth['Net Worth'].sum()\n","print('Total Combined Value of High Net Worth Individual = ${}'.format(cmbn))"],"execution_count":14,"outputs":[{"output_type":"stream","name":"stdout","text":["Total Combined Value of High Net Worth Individual = $39000\n"]}]},{"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":2,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","0             111           Chanel       3500                3\n","1             222            Steve      29000                4\n","2             333            Mitch      10000                9\n","3             444             Ryan       2000                5"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":2}]},{"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.10"],"execution_count":3,"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)"],"execution_count":4,"outputs":[{"output_type":"execute_result","data":{"text/plain":["0     3850.0\n","1    31900.0\n","2    11000.0\n","3     2200.0\n","Name: Net Worth, dtype: float64"]},"metadata":{},"execution_count":4}]},{"cell_type":"code","metadata":{"id":"WsObpnkou69Q","colab_type":"code","colab":{}},"source":["bank_client_df['Bank Client Name'].apply(len)"],"execution_count":6,"outputs":[{"output_type":"execute_result","data":{"text/plain":["0    6\n","1    5\n","2    5\n","3    4\n","Name: Bank Client Name, dtype: int64"]},"metadata":{},"execution_count":6}]},{"cell_type":"code","metadata":{"id":"E52xRMulu6_I","colab_type":"code","colab":{}},"source":["bank_client_df['Years With Bank'].sum()"],"execution_count":8,"outputs":[{"output_type":"execute_result","data":{"text/plain":["21"]},"metadata":{},"execution_count":8}]},{"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":["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":12,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","0             111           Chanel       3500                3\n","1             222            Steve      29000                4\n","2             333            Mitch      10000                9\n","3             444             Ryan       2000                5"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":12}]},{"cell_type":"code","metadata":{"id":"dW5exU8voFud","colab_type":"code","colab":{}},"source":["def up_value(price):\n","    return (price*2)+100"],"execution_count":13,"outputs":[]},{"cell_type":"code","metadata":{"id":"F0ctpy1eoFsd","colab_type":"code","colab":{}},"source":["bank_client_df['Net Worth Updated']=bank_client_df['Net Worth'].apply(up_value)\n","bank_client_df"],"execution_count":29,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank  \\\n","0             111           Chanel       3500                3   \n","1             222            Steve      29000                4   \n","2             333            Mitch      10000                9   \n","3             444             Ryan       2000                5   \n","\n","   Net Worth Updated  \n","0               7100  \n","1              58100  \n","2              20100  \n","3               4100  "],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n      <th>Net Worth Updated</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n      <td>7100</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n      <td>58100</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n      <td>20100</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n      <td>4100</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":29}]},{"cell_type":"code","metadata":{"id":"CEBxdOd9oFqA","colab_type":"code","colab":{}},"source":["value=bank_client_df['Net Worth'].sum()\n","value1=bank_client_df['Rev Value'].sum()\n","print('The total value after applying the function = {}'.format(value))\n","print(\"The total value after applying the function = {}\".format(value1))"],"execution_count":27,"outputs":[{"output_type":"stream","name":"stdout","text":["The total value after applying the function = 44500\nThe total value after applying the function = 89400\n"]}]},{"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":30,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","0             111           Chanel       3500                3\n","1             222            Steve      29000                4\n","2             333            Mitch      10000                9\n","3             444             Ryan       2000                5"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":30}]},{"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":35,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","0             111           Chanel       3500                3\n","1             222            Steve      29000                4\n","3             444             Ryan       2000                5\n","2             333            Mitch      10000                9"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":35}]},{"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":36,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","0             111           Chanel       3500                3\n","1             222            Steve      29000                4\n","3             444             Ryan       2000                5\n","2             333            Mitch      10000                9"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":36}]},{"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":37,"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":38,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","0             111           Chanel       3500                3\n","1             222            Steve      29000                4\n","3             444             Ryan       2000                5\n","2             333            Mitch      10000                9"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":38}]},{"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":["bank_client_df.sort_values(by= ['Net Worth'],inplace=True)\n","bank_client_df"],"execution_count":43,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","3             444             Ryan       2000                5\n","0             111           Chanel       3500                3\n","2             333            Mitch      10000                9\n","1             222            Steve      29000                4"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":43}]},{"cell_type":"code","execution_count":44,"metadata":{},"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID Bank Client Name  Net Worth  Years With Bank\n","3             444             Ryan       2000                5\n","0             111           Chanel       3500                3\n","2             333            Mitch      10000                9\n","1             222            Steve      29000                4"],"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>Bank Client Name</th>\n      <th>Net Worth</th>\n      <th>Years With Bank</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>3</th>\n      <td>444</td>\n      <td>Ryan</td>\n      <td>2000</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>111</td>\n      <td>Chanel</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>333</td>\n      <td>Mitch</td>\n      <td>10000</td>\n      <td>9</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>222</td>\n      <td>Steve</td>\n      <td>29000</td>\n      <td>4</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":44}],"source":["bank_client_df.sort_values(by= (['Net Worth']+['Years With Bank']),inplace=True)\n","bank_client_df"]},{"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":45,"outputs":[]},{"cell_type":"code","metadata":{"id":"MfimJ_PPvQIA","colab_type":"code","colab":{}},"source":["df1"],"execution_count":46,"outputs":[{"output_type":"execute_result","data":{"text/plain":["    A   B   C   D\n","0  A0  B0  C0  D0\n","1  A1  B1  C1  D1\n","2  A2  B2  C2  D2\n","3  A3  B3  C3  D3"],"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>A</th>\n      <th>B</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A0</td>\n      <td>B0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A1</td>\n      <td>B1</td>\n      <td>C1</td>\n      <td>D1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A2</td>\n      <td>B2</td>\n      <td>C2</td>\n      <td>D2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A3</td>\n      <td>B3</td>\n      <td>C3</td>\n      <td>D3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":46}]},{"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":48,"outputs":[]},{"cell_type":"code","metadata":{"id":"wNuUFBsqvQMU","colab_type":"code","colab":{}},"source":["df2"],"execution_count":49,"outputs":[{"output_type":"execute_result","data":{"text/plain":["    A   B   C   D\n","4  A4  B4  C4  D4\n","5  A5  B5  C5  D5\n","6  A6  B6  C6  D6\n","7  A7  B7  C7  D7"],"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>A</th>\n      <th>B</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>4</th>\n      <td>A4</td>\n      <td>B4</td>\n      <td>C4</td>\n      <td>D4</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>A5</td>\n      <td>B5</td>\n      <td>C5</td>\n      <td>D5</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>A6</td>\n      <td>B6</td>\n      <td>C6</td>\n      <td>D6</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>A7</td>\n      <td>B7</td>\n      <td>C7</td>\n      <td>D7</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":49}]},{"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])\n"],"execution_count":51,"outputs":[]},{"cell_type":"code","metadata":{"id":"wI10aNahvcM1","colab_type":"code","colab":{}},"source":["df3"],"execution_count":52,"outputs":[{"output_type":"execute_result","data":{"text/plain":["      A    B    C    D\n","8    A8   B8   C8   D8\n","9    A9   B9   C9   D9\n","10  A10  B10  C10  D10\n","11  A11  B11  C11  D11"],"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>A</th>\n      <th>B</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>8</th>\n      <td>A8</td>\n      <td>B8</td>\n      <td>C8</td>\n      <td>D8</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>A9</td>\n      <td>B9</td>\n      <td>C9</td>\n      <td>D9</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>A10</td>\n      <td>B10</td>\n      <td>C10</td>\n      <td>D10</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>A11</td>\n      <td>B11</td>\n      <td>C11</td>\n      <td>D11</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":52}]},{"cell_type":"code","metadata":{"id":"ljXyrXSjvcPG","colab_type":"code","colab":{}},"source":["pd.concat([df1,df2,df3])"],"execution_count":53,"outputs":[{"output_type":"execute_result","data":{"text/plain":["      A    B    C    D\n","0    A0   B0   C0   D0\n","1    A1   B1   C1   D1\n","2    A2   B2   C2   D2\n","3    A3   B3   C3   D3\n","4    A4   B4   C4   D4\n","5    A5   B5   C5   D5\n","6    A6   B6   C6   D6\n","7    A7   B7   C7   D7\n","8    A8   B8   C8   D8\n","9    A9   B9   C9   D9\n","10  A10  B10  C10  D10\n","11  A11  B11  C11  D11"],"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>A</th>\n      <th>B</th>\n      <th>C</th>\n      <th>D</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>A0</td>\n      <td>B0</td>\n      <td>C0</td>\n      <td>D0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>A1</td>\n      <td>B1</td>\n      <td>C1</td>\n      <td>D1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>A2</td>\n      <td>B2</td>\n      <td>C2</td>\n      <td>D2</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>A3</td>\n      <td>B3</td>\n      <td>C3</td>\n      <td>D3</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>A4</td>\n      <td>B4</td>\n      <td>C4</td>\n      <td>D4</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>A5</td>\n      <td>B5</td>\n      <td>C5</td>\n      <td>D5</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>A6</td>\n      <td>B6</td>\n      <td>C6</td>\n      <td>D6</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>A7</td>\n      <td>B7</td>\n      <td>C7</td>\n      <td>D7</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>A8</td>\n      <td>B8</td>\n      <td>C8</td>\n      <td>D8</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>A9</td>\n      <td>B9</td>\n      <td>C9</td>\n      <td>D9</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>A10</td>\n      <td>B10</td>\n      <td>C10</td>\n      <td>D10</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>A11</td>\n      <td>B11</td>\n      <td>C11</td>\n      <td>D11</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":53}]},{"cell_type":"code","metadata":{"id":"4_KBD8YGvcRV","colab_type":"code","colab":{}},"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","raw_data={'Bank Client ID':['1','2','3','4','5'],\n","          'First Name':['Nancy','Alex','Shep','Max','Allen'],\n","          'Last Name':['Rob','Shephard','Georg','Mitch','Steve']}\n","raw_data"],"execution_count":127,"outputs":[{"output_type":"execute_result","data":{"text/plain":["{'Bank Client ID': ['1', '2', '3', '4', '5'],\n"," 'First Name': ['Nancy', 'Alex', 'Shep', 'Max', 'Allen'],\n"," 'Last Name': ['Rob', 'Shephard', 'Georg', 'Mitch', 'Steve']}"]},"metadata":{},"execution_count":127}]},{"cell_type":"code","metadata":{"id":"bVVjCOCAwubr","colab_type":"code","colab":{}},"source":["bank_df_1=pd.DataFrame(raw_data,columns=['Bank Client ID','First Name','Last Name'])\n","bank_df_1"],"execution_count":128,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  Bank Client ID First Name Last Name\n","0              1      Nancy       Rob\n","1              2       Alex  Shephard\n","2              3       Shep     Georg\n","3              4        Max     Mitch\n","4              5      Allen     Steve"],"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>Shephard</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>Shep</td>\n      <td>Georg</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>"},"metadata":{},"execution_count":128}]},{"cell_type":"code","metadata":{"id":"wq_TBWkvvgNE","colab_type":"code","colab":{}},"source":["# Let's define another dataframe for a separate list of clients (IDs = 6, 7, 8, 9, 10)\n","raw_data={'Bank Client ID':['6','7','8','9','10'],\n","          'First Name':['Ankur','Gunugn','Ganesh','Anmol','Chintu'],\n","          'Last Name':['Pandey','Pandey','Pandey','Pandey','Dwivedi']}\n","bank_df_2=pd.DataFrame(raw_data,columns=['Bank Client ID','First Name','Last Name'])\n","bank_df_2"],"execution_count":129,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  Bank Client ID First Name Last Name\n","0              6      Ankur    Pandey\n","1              7     Gunugn    Pandey\n","2              8     Ganesh    Pandey\n","3              9      Anmol    Pandey\n","4             10     Chintu   Dwivedi"],"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>Ankur</td>\n      <td>Pandey</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>7</td>\n      <td>Gunugn</td>\n      <td>Pandey</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>8</td>\n      <td>Ganesh</td>\n      <td>Pandey</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>9</td>\n      <td>Anmol</td>\n      <td>Pandey</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>10</td>\n      <td>Chintu</td>\n      <td>Dwivedi</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":129}]},{"cell_type":"code","metadata":{"id":"zRgyJPjivgPZ","colab_type":"code","colab":{}},"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","raw_data={'Bank Client ID':['1','2','3','4','5','6','7','8','9','10'],\n","        'Annual Salary [$/Yr]':[25000,54333,55555,22224,55253,5111110,100000,422244,221010,90000]}\n","bank_client_salary=pd.DataFrame(raw_data,columns=['Bank Client ID','Annual Salary [$/Yr]'])\n","bank_client_salary"],"execution_count":130,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  Bank Client ID  Annual Salary [$/Yr]\n","0              1                 25000\n","1              2                 54333\n","2              3                 55555\n","3              4                 22224\n","4              5                 55253\n","5              6               5111110\n","6              7                100000\n","7              8                422244\n","8              9                221010\n","9             10                 90000"],"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 [$/Yr]</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>54333</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>55555</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>4</td>\n      <td>22224</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>5</td>\n      <td>55253</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>6</td>\n      <td>5111110</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>7</td>\n      <td>100000</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>8</td>\n      <td>422244</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>9</td>\n      <td>221010</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>10</td>\n      <td>90000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":130}]},{"cell_type":"code","metadata":{"id":"i2X65wc0vidf","colab_type":"code","colab":{}},"source":["# Let's concatenate both dataframes #1 and #2\n","# Note that we now have client IDs from 1 to 10\n","bank_client_dfinal=pd.concat([bank_df_1,bank_df_2])\n","bank_client_dfinal"],"execution_count":131,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  Bank Client ID First Name Last Name\n","0              1      Nancy       Rob\n","1              2       Alex  Shephard\n","2              3       Shep     Georg\n","3              4        Max     Mitch\n","4              5      Allen     Steve\n","0              6      Ankur    Pandey\n","1              7     Gunugn    Pandey\n","2              8     Ganesh    Pandey\n","3              9      Anmol    Pandey\n","4             10     Chintu   Dwivedi"],"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>Shephard</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>Shep</td>\n      <td>Georg</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>Ankur</td>\n      <td>Pandey</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>7</td>\n      <td>Gunugn</td>\n      <td>Pandey</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>8</td>\n      <td>Ganesh</td>\n      <td>Pandey</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>9</td>\n      <td>Anmol</td>\n      <td>Pandey</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>10</td>\n      <td>Chintu</td>\n      <td>Dwivedi</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":131}]},{"cell_type":"code","metadata":{"id":"9bgjH8mkvQOY","colab_type":"code","colab":{}},"source":["# Let's merge all data on 'Bank Client ID'\n","bank_client_dfinal=pd.merge(bank_client_dfinal,bank_client_salary,on='Bank Client ID')"],"execution_count":132,"outputs":[]},{"cell_type":"code","metadata":{"id":"5g5tCggp9gce","colab_type":"code","colab":{}},"source":["bank_client_dfinal"],"execution_count":133,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  Bank Client ID First Name Last Name  Annual Salary [$/Yr]\n","0              1      Nancy       Rob                 25000\n","1              2       Alex  Shephard                 54333\n","2              3       Shep     Georg                 55555\n","3              4        Max     Mitch                 22224\n","4              5      Allen     Steve                 55253\n","5              6      Ankur    Pandey               5111110\n","6              7     Gunugn    Pandey                100000\n","7              8     Ganesh    Pandey                422244\n","8              9      Anmol    Pandey                221010\n","9             10     Chintu   Dwivedi                 90000"],"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 [$/Yr]</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>Shephard</td>\n      <td>54333</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>Shep</td>\n      <td>Georg</td>\n      <td>55555</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>4</td>\n      <td>Max</td>\n      <td>Mitch</td>\n      <td>22224</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>5</td>\n      <td>Allen</td>\n      <td>Steve</td>\n      <td>55253</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>6</td>\n      <td>Ankur</td>\n      <td>Pandey</td>\n      <td>5111110</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>7</td>\n      <td>Gunugn</td>\n      <td>Pandey</td>\n      <td>100000</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>8</td>\n      <td>Ganesh</td>\n      <td>Pandey</td>\n      <td>422244</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>9</td>\n      <td>Anmol</td>\n      <td>Pandey</td>\n      <td>221010</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>10</td>\n      <td>Chintu</td>\n      <td>Dwivedi</td>\n      <td>90000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":133}]},{"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":["raw_data={'Bank Client ID':[11],\n","         'First Name':['Kishalay'],\n","         'Last Name':['Pandey'],\n","         'Annual Salary [$/Yr]':[1000000]}\n","df4=pd.DataFrame(raw_data,columns=['Bank Client ID','First Name','Last Name','Annual Salary [$/Yr]'])\n","df4"],"execution_count":134,"outputs":[{"output_type":"execute_result","data":{"text/plain":["   Bank Client ID First Name Last Name  Annual Salary [$/Yr]\n","0              11   Kishalay    Pandey               1000000"],"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 [$/Yr]</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>11</td>\n      <td>Kishalay</td>\n      <td>Pandey</td>\n      <td>1000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":134}]},{"cell_type":"code","metadata":{"id":"WcmoZpFt0-fW","colab_type":"code","colab":{}},"source":["bank_client_dfinal=pd.concat([bank_client_dfinal,df4],axis=0)"],"execution_count":135,"outputs":[]},{"cell_type":"code","execution_count":140,"metadata":{},"outputs":[{"output_type":"error","ename":"ModuleNotFoundError","evalue":"No module named 'tensorflow'","traceback":["\u001b[1;31m---------------------------------------------------------------------------\u001b[0m","\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)","\u001b[1;32m<ipython-input-140-64156d691fe5>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[1;32mimport\u001b[0m \u001b[0mtensorflow\u001b[0m \u001b[1;32mas\u001b[0m \u001b[0mtf\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m","\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'tensorflow'"]}],"source":["\n"]},{"cell_type":"code","metadata":{"id":"XQiP3NFy1Jbi","colab_type":"code","colab":{}},"source":["bank_client_dfinal\n"],"execution_count":136,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  Bank Client ID First Name Last Name  Annual Salary [$/Yr]\n","0              1      Nancy       Rob                 25000\n","1              2       Alex  Shephard                 54333\n","2              3       Shep     Georg                 55555\n","3              4        Max     Mitch                 22224\n","4              5      Allen     Steve                 55253\n","5              6      Ankur    Pandey               5111110\n","6              7     Gunugn    Pandey                100000\n","7              8     Ganesh    Pandey                422244\n","8              9      Anmol    Pandey                221010\n","9             10     Chintu   Dwivedi                 90000\n","0             11   Kishalay    Pandey               1000000"],"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 [$/Yr]</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>Shephard</td>\n      <td>54333</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3</td>\n      <td>Shep</td>\n      <td>Georg</td>\n      <td>55555</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>4</td>\n      <td>Max</td>\n      <td>Mitch</td>\n      <td>22224</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>5</td>\n      <td>Allen</td>\n      <td>Steve</td>\n      <td>55253</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>6</td>\n      <td>Ankur</td>\n      <td>Pandey</td>\n      <td>5111110</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>7</td>\n      <td>Gunugn</td>\n      <td>Pandey</td>\n      <td>100000</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>8</td>\n      <td>Ganesh</td>\n      <td>Pandey</td>\n      <td>422244</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>9</td>\n      <td>Anmol</td>\n      <td>Pandey</td>\n      <td>221010</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>10</td>\n      <td>Chintu</td>\n      <td>Dwivedi</td>\n      <td>90000</td>\n    </tr>\n    <tr>\n      <th>0</th>\n      <td>11</td>\n      <td>Kishalay</td>\n      <td>Pandey</td>\n      <td>1000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":136}]},{"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":14,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  stock ticker symbols  price per share [$]  Number of stocks\n","0                 AAPL                 3500                 3\n","1                 AMZN                  200                 4\n","2                    T                   40                 9"],"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>stock ticker symbols</th>\n      <th>price per share [$]</th>\n      <th>Number of stocks</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>AAPL</td>\n      <td>3500</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>AMZN</td>\n      <td>200</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>T</td>\n      <td>40</td>\n      <td>9</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":14}]},{"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":15,"outputs":[{"output_type":"stream","name":"stdout","text":["0    10500\n1      800\n2      360\ndtype: int64\nTotal portfolio value = 11660\n"]}]},{"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":123,"outputs":[{"output_type":"execute_result","data":{"text/plain":["  Bank Client ID First Name Last Name  Annual Salary [$/year]\n","0             11         Ry       Aly                    1000"],"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>11</td>\n      <td>Ry</td>\n      <td>Aly</td>\n      <td>1000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":123}]},{"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":124,"outputs":[{"output_type":"error","ename":"NameError","evalue":"name 'bank_df_all' is not defined","traceback":["\u001b[1;31m---------------------------------------------------------------------------\u001b[0m","\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)","\u001b[1;32m<ipython-input-124-580c0194152b>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mnew_df\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconcat\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mbank_df_all\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mnew_client_df\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxis\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;36m0\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mnew_df\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n","\u001b[1;31mNameError\u001b[0m: name 'bank_df_all' is not defined"]}]},{"cell_type":"code","execution_count":null,"metadata":{},"outputs":[],"source":[]}]}