Created
October 27, 2023 06:41
-
-
Save S2Ahmad/27c7f20c128359afaac0b202a6c2e4d2 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| {"cells":[{"source":"\n\n<br>\n\nPersonal loans are a lucrative revenue stream for banks. The typical interest rate of a two year loan in the United Kingdom is [around 10%](https://www.experian.com/blogs/ask-experian/whats-a-good-interest-rate-for-a-personal-loan/). This might not sound like a lot, but in September 2022 alone UK consumers borrowed [around £1.5 billion](https://www.ukfinance.org.uk/system/files/2022-12/Household%20Finance%20Review%202022%20Q3-%20Final.pdf), which would mean approximately £300 million in interest generated by banks over two years!\n\nYou have been asked to work with a bank to clean and store the data they collected as part of a recent marketing campaign, which aimed to get customers to take out a personal loan. They plan to conduct more marketing campaigns going forward so would like you to set up a PostgreSQL database to store this campaign's data, designing the schema in a way that would allow data from future campaigns to be easily imported. \n\nThey have supplied you with a csv file called `\"bank_marketing.csv\"`, which you will need to clean, reformat, and split, in order to save separate files based on the tables you will create. It is recommended to use `pandas` for these tasks.\n\nLastly, you will write the SQL code that the bank can execute to create the tables and populate with the data from the csv files. As the bank are quite strict about their security, you'll save SQL files as multiline string variables that they can then use to create the database on their end. \n\nYou have been asked to design a database that will have three tables:\n\n## client\n\n| column | data type | description | original column in dataset |\n|--------|-----------|-------------|----------------------------|\n| `id` | `serial` | Client ID - primary key | `client_id` |\n| `age` | `integer` | Client's age in years | `age` |\n| `job` | `text` | Client's type of job | `job` |\n| `marital` | `text` | Client's marital status | `marital` | \n| `education` | `text` | Client's level of education | `education` |\n| `credit_default` | `boolean` | Whether the client's credit is in default | `credit_default` |\n| `housing` | `boolean` | Whether the client has an existing housing loan (mortgage) | `housing` | \n| `loan` | `boolean` | Whether the client has an existing personal loan | `loan` |\n\n<br>\n\n## campaign\n\n| column | data type | description | original column in dataset |\n|--------|-----------|-------------|----------------------------|\n| `campaign_id` | `serial` | Campaign ID - primary key | N/A - new column |\n| `client_id` | `serial` | Client ID - references `id` in the `client` table | `client_id` |\n| `number_contacts` | `integer` | Number of contact attempts to the client in the current campaign | `campaign` |\n| `contact_duration` | `integer` | Last contact duration in seconds | `duration` |\n| `pdays` | `integer` | Number of days since contact in previous campaign (`999` = not previously contacted) | `pdays` |\n| `previous_campaign_contacts` | `integer` | Number of contact attempts to the client in the previous campaign | `previous` |\n| `previous_outcome` | `boolean` | Outcome of the previous campaign | `poutcome` |\n| `campaign_outcome` | `boolean` | Outcome of the current campaign | `y` |\n| `last_contact_date` | `date` | Last date the client was contacted | A combination of `day`, `month`, and the newly created `year` |\n\n<br>\n\n## economics\n\n| column | data type | description | original column in dataset |\n|--------|-----------|-------------|----------------------------|\n| `client_id` | `serial` | Client ID - references `id` in the `client` table | `client_id` |\n| `emp_var_rate` | `float` | Employment variation rate (quarterly indicator) | `emp_var_rate` |\n| `cons_price_idx` | `float` | Consumer price index (monthly indicator) | `cons_price_idx` |\n| `euribor_three_months` | `float` | Euro Interbank Offered Rate (euribor) three month rate (daily indicator) | `euribor3m` |\n| `number_employed` | `float` | Number of employees (quarterly indicator)| `nr_employed` |","metadata":{},"id":"02077ee3-e1e4-4fc5-8de1-16e987afa5fb","cell_type":"markdown"},{"source":"import pandas as pd\nimport numpy as np","metadata":{"executionCancelledAt":null,"executionTime":152,"lastExecutedAt":1698382955952,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"import pandas as pd\nimport numpy as np\n\n# Start coding here..."},"id":"e2edad3c-8286-4983-b5b7-35d94fd78023","cell_type":"code","execution_count":1,"outputs":[]},{"source":"d = pd.read_csv('bank_marketing.csv')","metadata":{"executionCancelledAt":null,"executionTime":110,"lastExecutedAt":1698382997867,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"d = pd.read_csv('bank_marketing.csv')"},"cell_type":"code","id":"3e7c99c3-4efe-4a2a-99e4-d67740b071de","execution_count":2,"outputs":[]},{"source":"d.head()","metadata":{"executionCancelledAt":null,"executionTime":null,"lastExecutedAt":null,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":null,"outputsMetadata":{"0":{"height":208,"type":"dataFrame"}}},"cell_type":"code","id":"57f822c7-7ae2-4e47-bceb-ec62bd6df47e","execution_count":3,"outputs":[{"output_type":"execute_result","data":{"application/com.datacamp.data-table.v1+json":{"table":{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"client_id","type":"integer"},{"name":"age","type":"integer"},{"name":"job","type":"string"},{"name":"marital","type":"string"},{"name":"education","type":"string"},{"name":"credit_default","type":"string"},{"name":"housing","type":"string"},{"name":"loan","type":"string"},{"name":"contact","type":"string"},{"name":"month","type":"string"},{"name":"day","type":"integer"},{"name":"duration","type":"integer"},{"name":"campaign","type":"integer"},{"name":"pdays","type":"integer"},{"name":"previous","type":"integer"},{"name":"poutcome","type":"string"},{"name":"emp_var_rate","type":"number"},{"name":"cons_price_idx","type":"number"},{"name":"cons_conf_idx","type":"number"},{"name":"euribor3m","type":"number"},{"name":"nr_employed","type":"number"},{"name":"y","type":"string"}],"primaryKey":["index"],"pandas_version":"1.4.0"},"data":[{"index":0,"client_id":0,"age":56,"job":"housemaid","marital":"married","education":"basic.4y","credit_default":"no","housing":"no","loan":"no","contact":"telephone","month":"may","day":13,"duration":261,"campaign":1,"pdays":999,"previous":0,"poutcome":"nonexistent","emp_var_rate":1.1,"cons_price_idx":93.994,"cons_conf_idx":-36.4,"euribor3m":4.857,"nr_employed":5191,"y":"no"},{"index":1,"client_id":1,"age":57,"job":"services","marital":"married","education":"high.school","credit_default":"unknown","housing":"no","loan":"no","contact":"telephone","month":"may","day":19,"duration":149,"campaign":1,"pdays":999,"previous":0,"poutcome":"nonexistent","emp_var_rate":1.1,"cons_price_idx":93.994,"cons_conf_idx":-36.4,"euribor3m":4.857,"nr_employed":5191,"y":"no"},{"index":2,"client_id":2,"age":37,"job":"services","marital":"married","education":"high.school","credit_default":"no","housing":"yes","loan":"no","contact":"telephone","month":"may","day":23,"duration":226,"campaign":1,"pdays":999,"previous":0,"poutcome":"nonexistent","emp_var_rate":1.1,"cons_price_idx":93.994,"cons_conf_idx":-36.4,"euribor3m":4.857,"nr_employed":5191,"y":"no"},{"index":3,"client_id":3,"age":40,"job":"admin.","marital":"married","education":"basic.6y","credit_default":"no","housing":"no","loan":"no","contact":"telephone","month":"may","day":27,"duration":151,"campaign":1,"pdays":999,"previous":0,"poutcome":"nonexistent","emp_var_rate":1.1,"cons_price_idx":93.994,"cons_conf_idx":-36.4,"euribor3m":4.857,"nr_employed":5191,"y":"no"},{"index":4,"client_id":4,"age":56,"job":"services","marital":"married","education":"high.school","credit_default":"no","housing":"no","loan":"yes","contact":"telephone","month":"may","day":3,"duration":307,"campaign":1,"pdays":999,"previous":0,"poutcome":"nonexistent","emp_var_rate":1.1,"cons_price_idx":93.994,"cons_conf_idx":-36.4,"euribor3m":4.857,"nr_employed":5191,"y":"no"}]},"total_rows":5,"truncation_type":null},"text/plain":" client_id age job ... euribor3m nr_employed y\n0 0 56 housemaid ... 4.857 5191.0 no\n1 1 57 services ... 4.857 5191.0 no\n2 2 37 services ... 4.857 5191.0 no\n3 3 40 admin. ... 4.857 5191.0 no\n4 4 56 services ... 4.857 5191.0 no\n\n[5 rows x 22 columns]","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>client_id</th>\n <th>age</th>\n <th>job</th>\n <th>marital</th>\n <th>education</th>\n <th>credit_default</th>\n <th>housing</th>\n <th>loan</th>\n <th>contact</th>\n <th>month</th>\n <th>day</th>\n <th>duration</th>\n <th>campaign</th>\n <th>pdays</th>\n <th>previous</th>\n <th>poutcome</th>\n <th>emp_var_rate</th>\n <th>cons_price_idx</th>\n <th>cons_conf_idx</th>\n <th>euribor3m</th>\n <th>nr_employed</th>\n <th>y</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>0</td>\n <td>56</td>\n <td>housemaid</td>\n <td>married</td>\n <td>basic.4y</td>\n <td>no</td>\n <td>no</td>\n <td>no</td>\n <td>telephone</td>\n <td>may</td>\n <td>13</td>\n <td>261</td>\n <td>1</td>\n <td>999</td>\n <td>0</td>\n <td>nonexistent</td>\n <td>1.1</td>\n <td>93.994</td>\n <td>-36.4</td>\n <td>4.857</td>\n <td>5191.0</td>\n <td>no</td>\n </tr>\n <tr>\n <th>1</th>\n <td>1</td>\n <td>57</td>\n <td>services</td>\n <td>married</td>\n <td>high.school</td>\n <td>unknown</td>\n <td>no</td>\n <td>no</td>\n <td>telephone</td>\n <td>may</td>\n <td>19</td>\n <td>149</td>\n <td>1</td>\n <td>999</td>\n <td>0</td>\n <td>nonexistent</td>\n <td>1.1</td>\n <td>93.994</td>\n <td>-36.4</td>\n <td>4.857</td>\n <td>5191.0</td>\n <td>no</td>\n </tr>\n <tr>\n <th>2</th>\n <td>2</td>\n <td>37</td>\n <td>services</td>\n <td>married</td>\n <td>high.school</td>\n <td>no</td>\n <td>yes</td>\n <td>no</td>\n <td>telephone</td>\n <td>may</td>\n <td>23</td>\n <td>226</td>\n <td>1</td>\n <td>999</td>\n <td>0</td>\n <td>nonexistent</td>\n <td>1.1</td>\n <td>93.994</td>\n <td>-36.4</td>\n <td>4.857</td>\n <td>5191.0</td>\n <td>no</td>\n </tr>\n <tr>\n <th>3</th>\n <td>3</td>\n <td>40</td>\n <td>admin.</td>\n <td>married</td>\n <td>basic.6y</td>\n <td>no</td>\n <td>no</td>\n <td>no</td>\n <td>telephone</td>\n <td>may</td>\n <td>27</td>\n <td>151</td>\n <td>1</td>\n <td>999</td>\n <td>0</td>\n <td>nonexistent</td>\n <td>1.1</td>\n <td>93.994</td>\n <td>-36.4</td>\n <td>4.857</td>\n <td>5191.0</td>\n <td>no</td>\n </tr>\n <tr>\n <th>4</th>\n <td>4</td>\n <td>56</td>\n <td>services</td>\n <td>married</td>\n <td>high.school</td>\n <td>no</td>\n <td>no</td>\n <td>yes</td>\n <td>telephone</td>\n <td>may</td>\n <td>3</td>\n <td>307</td>\n <td>1</td>\n <td>999</td>\n <td>0</td>\n <td>nonexistent</td>\n <td>1.1</td>\n <td>93.994</td>\n <td>-36.4</td>\n <td>4.857</td>\n <td>5191.0</td>\n <td>no</td>\n </tr>\n </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":3}]},{"source":"client = d[[\"client_id\", \"age\", \"job\", \"marital\", \"education\", \n \"credit_default\", \"housing\", \"loan\"]]\n\ncampaign = d[[\"client_id\", \"campaign\", \"month\", \"day\", \n \"duration\", \"pdays\", \"previous\", \"poutcome\", \"y\"]]\n\neconomics = d[[\"client_id\", \"emp_var_rate\", \"cons_price_idx\", \n \"euribor3m\", \"nr_employed\"]]","metadata":{"executionCancelledAt":null,"executionTime":18,"lastExecutedAt":1698387673293,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"client = d[[\"client_id\", \"age\", \"job\", \"marital\", \"education\", \n \"credit_default\", \"housing\", \"loan\"]]\n\ncampaign = d[[\"client_id\", \"campaign\", \"month\", \"day\", \n \"duration\", \"pdays\", \"previous\", \"poutcome\", \"y\"]]\n\neconomics = d[[\"client_id\", \"emp_var_rate\", \"cons_price_idx\", \n \"euribor3m\", \"nr_employed\"]]"},"cell_type":"code","id":"2070429e-3420-478b-8eab-4e7691a5bf2e","execution_count":115,"outputs":[]},{"source":"client.rename(columns={'client_id':'id'\n }, inplace=True)","metadata":{"executionCancelledAt":null,"executionTime":14,"lastExecutedAt":1698387680343,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"client.rename(columns={'client_id':'id'\n }, inplace=True)","outputsMetadata":{"0":{"height":357,"type":"dataFrame"}}},"cell_type":"code","id":"f89b3bd4-0f3c-48e3-a849-2dffa4dbcced","execution_count":116,"outputs":[]},{"source":"campaign.rename(columns={'campaign':'number_contacts',\n 'duration':'contact_duration',\n 'previous':'previous_campaign_contacts',\n 'y':'campaign_outcome',\n 'poutcome':'previous_outcome'}, inplace=True)","metadata":{"executionCancelledAt":null,"executionTime":14,"lastExecutedAt":1698387685281,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"campaign.rename(columns={'campaign':'number_contacts',\n 'duration':'contact_duration',\n 'previous':'previous_campaign_contacts',\n 'y':'campaign_outcome',\n 'poutcome':'previous_outcome'}, inplace=True)","outputsMetadata":{"0":{"height":208,"type":"dataFrame"}}},"cell_type":"code","id":"cbf95de6-7b65-4223-bc80-447d4566a2eb","execution_count":117,"outputs":[]},{"source":"economics.rename(columns={'euribor3m':'euribor_three_months',\n 'nr_employed':'number_employed'}, inplace=True)","metadata":{"executionCancelledAt":null,"executionTime":14,"lastExecutedAt":1698387686611,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"economics.rename(columns={'euribor3m':'euribor_three_months',\n 'nr_employed':'number_employed'}, inplace=True)"},"cell_type":"code","id":"8ad4becd-97f0-41e7-9c11-077e1cac680e","execution_count":118,"outputs":[]},{"source":"client['education'] = client['education'].str.replace('.', '_')\nclient['education'] = client['education'].replace('unknown', np.NaN)\nclient.head(10)","metadata":{"executionCancelledAt":null,"executionTime":39,"lastExecutedAt":1698387687836,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"client['education'] = client['education'].str.replace('.', '_')\nclient['education'] = client['education'].replace('unknown', np.NaN)\nclient.head(10)","outputsMetadata":{"0":{"height":312,"type":"dataFrame"}}},"cell_type":"code","id":"9e4f8005-b275-4695-b4b3-1e03eedbd2ba","execution_count":119,"outputs":[{"output_type":"execute_result","data":{"application/com.datacamp.data-table.v1+json":{"table":{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"id","type":"integer"},{"name":"age","type":"integer"},{"name":"job","type":"string"},{"name":"marital","type":"string"},{"name":"education","type":"string"},{"name":"credit_default","type":"string"},{"name":"housing","type":"string"},{"name":"loan","type":"string"}],"primaryKey":["index"],"pandas_version":"1.4.0"},"data":[{"index":0,"id":0,"age":56,"job":"housemaid","marital":"married","education":"basic_4y","credit_default":"no","housing":"no","loan":"no"},{"index":1,"id":1,"age":57,"job":"services","marital":"married","education":"high_school","credit_default":"unknown","housing":"no","loan":"no"},{"index":2,"id":2,"age":37,"job":"services","marital":"married","education":"high_school","credit_default":"no","housing":"yes","loan":"no"},{"index":3,"id":3,"age":40,"job":"admin.","marital":"married","education":"basic_6y","credit_default":"no","housing":"no","loan":"no"},{"index":4,"id":4,"age":56,"job":"services","marital":"married","education":"high_school","credit_default":"no","housing":"no","loan":"yes"},{"index":5,"id":5,"age":45,"job":"services","marital":"married","education":"basic_9y","credit_default":"unknown","housing":"no","loan":"no"},{"index":6,"id":6,"age":59,"job":"admin.","marital":"married","education":"professional_course","credit_default":"no","housing":"no","loan":"no"},{"index":7,"id":7,"age":41,"job":"blue-collar","marital":"married","education":null,"credit_default":"unknown","housing":"no","loan":"no"},{"index":8,"id":8,"age":24,"job":"technician","marital":"single","education":"professional_course","credit_default":"no","housing":"yes","loan":"no"},{"index":9,"id":9,"age":25,"job":"services","marital":"single","education":"high_school","credit_default":"no","housing":"yes","loan":"no"}]},"total_rows":10,"truncation_type":null},"text/plain":" id age job ... credit_default housing loan\n0 0 56 housemaid ... no no no\n1 1 57 services ... unknown no no\n2 2 37 services ... no yes no\n3 3 40 admin. ... no no no\n4 4 56 services ... no no yes\n5 5 45 services ... unknown no no\n6 6 59 admin. ... no no no\n7 7 41 blue-collar ... unknown no no\n8 8 24 technician ... no yes no\n9 9 25 services ... no yes no\n\n[10 rows x 8 columns]","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>id</th>\n <th>age</th>\n <th>job</th>\n <th>marital</th>\n <th>education</th>\n <th>credit_default</th>\n <th>housing</th>\n <th>loan</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>0</td>\n <td>56</td>\n <td>housemaid</td>\n <td>married</td>\n <td>basic_4y</td>\n <td>no</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>1</th>\n <td>1</td>\n <td>57</td>\n <td>services</td>\n <td>married</td>\n <td>high_school</td>\n <td>unknown</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>2</th>\n <td>2</td>\n <td>37</td>\n <td>services</td>\n <td>married</td>\n <td>high_school</td>\n <td>no</td>\n <td>yes</td>\n <td>no</td>\n </tr>\n <tr>\n <th>3</th>\n <td>3</td>\n <td>40</td>\n <td>admin.</td>\n <td>married</td>\n <td>basic_6y</td>\n <td>no</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>4</th>\n <td>4</td>\n <td>56</td>\n <td>services</td>\n <td>married</td>\n <td>high_school</td>\n <td>no</td>\n <td>no</td>\n <td>yes</td>\n </tr>\n <tr>\n <th>5</th>\n <td>5</td>\n <td>45</td>\n <td>services</td>\n <td>married</td>\n <td>basic_9y</td>\n <td>unknown</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>6</th>\n <td>6</td>\n <td>59</td>\n <td>admin.</td>\n <td>married</td>\n <td>professional_course</td>\n <td>no</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>7</th>\n <td>7</td>\n <td>41</td>\n <td>blue-collar</td>\n <td>married</td>\n <td>NaN</td>\n <td>unknown</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>8</th>\n <td>8</td>\n <td>24</td>\n <td>technician</td>\n <td>single</td>\n <td>professional_course</td>\n <td>no</td>\n <td>yes</td>\n <td>no</td>\n </tr>\n <tr>\n <th>9</th>\n <td>9</td>\n <td>25</td>\n <td>services</td>\n <td>single</td>\n <td>high_school</td>\n <td>no</td>\n <td>yes</td>\n <td>no</td>\n </tr>\n </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":119}]},{"source":"client['job'] = client['job'].str.replace('.', '')\nclient.head(10)","metadata":{"executionCancelledAt":null,"executionTime":36,"lastExecutedAt":1698387689020,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"client['job'] = client['job'].str.replace('.', '')\nclient.head(10)","outputsMetadata":{"0":{"height":312,"type":"dataFrame"}}},"cell_type":"code","id":"b7ae64a5-fa54-49ad-b7a8-fb8ad452ff3e","execution_count":120,"outputs":[{"output_type":"execute_result","data":{"application/com.datacamp.data-table.v1+json":{"table":{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"id","type":"integer"},{"name":"age","type":"integer"},{"name":"job","type":"string"},{"name":"marital","type":"string"},{"name":"education","type":"string"},{"name":"credit_default","type":"string"},{"name":"housing","type":"string"},{"name":"loan","type":"string"}],"primaryKey":["index"],"pandas_version":"1.4.0"},"data":[{"index":0,"id":0,"age":56,"job":"housemaid","marital":"married","education":"basic_4y","credit_default":"no","housing":"no","loan":"no"},{"index":1,"id":1,"age":57,"job":"services","marital":"married","education":"high_school","credit_default":"unknown","housing":"no","loan":"no"},{"index":2,"id":2,"age":37,"job":"services","marital":"married","education":"high_school","credit_default":"no","housing":"yes","loan":"no"},{"index":3,"id":3,"age":40,"job":"admin","marital":"married","education":"basic_6y","credit_default":"no","housing":"no","loan":"no"},{"index":4,"id":4,"age":56,"job":"services","marital":"married","education":"high_school","credit_default":"no","housing":"no","loan":"yes"},{"index":5,"id":5,"age":45,"job":"services","marital":"married","education":"basic_9y","credit_default":"unknown","housing":"no","loan":"no"},{"index":6,"id":6,"age":59,"job":"admin","marital":"married","education":"professional_course","credit_default":"no","housing":"no","loan":"no"},{"index":7,"id":7,"age":41,"job":"blue-collar","marital":"married","education":null,"credit_default":"unknown","housing":"no","loan":"no"},{"index":8,"id":8,"age":24,"job":"technician","marital":"single","education":"professional_course","credit_default":"no","housing":"yes","loan":"no"},{"index":9,"id":9,"age":25,"job":"services","marital":"single","education":"high_school","credit_default":"no","housing":"yes","loan":"no"}]},"total_rows":10,"truncation_type":null},"text/plain":" id age job ... credit_default housing loan\n0 0 56 housemaid ... no no no\n1 1 57 services ... unknown no no\n2 2 37 services ... no yes no\n3 3 40 admin ... no no no\n4 4 56 services ... no no yes\n5 5 45 services ... unknown no no\n6 6 59 admin ... no no no\n7 7 41 blue-collar ... unknown no no\n8 8 24 technician ... no yes no\n9 9 25 services ... no yes no\n\n[10 rows x 8 columns]","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>id</th>\n <th>age</th>\n <th>job</th>\n <th>marital</th>\n <th>education</th>\n <th>credit_default</th>\n <th>housing</th>\n <th>loan</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>0</td>\n <td>56</td>\n <td>housemaid</td>\n <td>married</td>\n <td>basic_4y</td>\n <td>no</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>1</th>\n <td>1</td>\n <td>57</td>\n <td>services</td>\n <td>married</td>\n <td>high_school</td>\n <td>unknown</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>2</th>\n <td>2</td>\n <td>37</td>\n <td>services</td>\n <td>married</td>\n <td>high_school</td>\n <td>no</td>\n <td>yes</td>\n <td>no</td>\n </tr>\n <tr>\n <th>3</th>\n <td>3</td>\n <td>40</td>\n <td>admin</td>\n <td>married</td>\n <td>basic_6y</td>\n <td>no</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>4</th>\n <td>4</td>\n <td>56</td>\n <td>services</td>\n <td>married</td>\n <td>high_school</td>\n <td>no</td>\n <td>no</td>\n <td>yes</td>\n </tr>\n <tr>\n <th>5</th>\n <td>5</td>\n <td>45</td>\n <td>services</td>\n <td>married</td>\n <td>basic_9y</td>\n <td>unknown</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>6</th>\n <td>6</td>\n <td>59</td>\n <td>admin</td>\n <td>married</td>\n <td>professional_course</td>\n <td>no</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>7</th>\n <td>7</td>\n <td>41</td>\n <td>blue-collar</td>\n <td>married</td>\n <td>NaN</td>\n <td>unknown</td>\n <td>no</td>\n <td>no</td>\n </tr>\n <tr>\n <th>8</th>\n <td>8</td>\n <td>24</td>\n <td>technician</td>\n <td>single</td>\n <td>professional_course</td>\n <td>no</td>\n <td>yes</td>\n <td>no</td>\n </tr>\n <tr>\n <th>9</th>\n <td>9</td>\n <td>25</td>\n <td>services</td>\n <td>single</td>\n <td>high_school</td>\n <td>no</td>\n <td>yes</td>\n <td>no</td>\n </tr>\n </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":120}]},{"source":"campaign[\"previous_outcome\"] = campaign[\"previous_outcome\"].replace(\"nonexistent\", \n np.NaN)\ncampaign[\"previous_outcome\"] = campaign[\"previous_outcome\"].map({\"success\": 1, \n \"failure\": 0})\ncampaign[\"campaign_outcome\"] = campaign[\"campaign_outcome\"].map({\"yes\": 1, \n \"no\": 0})","metadata":{"executionCancelledAt":null,"executionTime":23,"lastExecutedAt":1698387691395,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"campaign[\"previous_outcome\"] = campaign[\"previous_outcome\"].replace(\"nonexistent\", \n np.NaN)\ncampaign[\"previous_outcome\"] = campaign[\"previous_outcome\"].map({\"success\": 1, \n \"failure\": 0})\ncampaign[\"campaign_outcome\"] = campaign[\"campaign_outcome\"].map({\"yes\": 1, \n \"no\": 0})"},"cell_type":"code","id":"6ef211f6-7ca3-4158-8b7a-fc5a6e37a586","execution_count":121,"outputs":[]},{"source":"campaign.tail(10)","metadata":{"executionCancelledAt":null,"executionTime":null,"lastExecutedAt":null,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":null,"outputsMetadata":{"0":{"height":329,"type":"dataFrame"}}},"cell_type":"code","id":"c15b37c8-8fd3-4d55-b84e-68e1ded0e36a","execution_count":122,"outputs":[{"output_type":"execute_result","data":{"application/com.datacamp.data-table.v1+json":{"table":{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"client_id","type":"integer"},{"name":"number_contacts","type":"integer"},{"name":"month","type":"string"},{"name":"day","type":"integer"},{"name":"contact_duration","type":"integer"},{"name":"pdays","type":"integer"},{"name":"previous_campaign_contacts","type":"integer"},{"name":"previous_outcome","type":"number"},{"name":"campaign_outcome","type":"integer"}],"primaryKey":["index"],"pandas_version":"1.4.0"},"data":[{"index":41178,"client_id":41178,"number_contacts":2,"month":"nov","day":26,"contact_duration":483,"pdays":6,"previous_campaign_contacts":3,"previous_outcome":1,"campaign_outcome":1},{"index":41179,"client_id":41179,"number_contacts":3,"month":"nov","day":7,"contact_duration":151,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0},{"index":41180,"client_id":41180,"number_contacts":2,"month":"nov","day":27,"contact_duration":254,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0},{"index":41181,"client_id":41181,"number_contacts":1,"month":"nov","day":20,"contact_duration":281,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":1},{"index":41182,"client_id":41182,"number_contacts":1,"month":"nov","day":26,"contact_duration":112,"pdays":9,"previous_campaign_contacts":1,"previous_outcome":1,"campaign_outcome":0},{"index":41183,"client_id":41183,"number_contacts":1,"month":"nov","day":30,"contact_duration":334,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":1},{"index":41184,"client_id":41184,"number_contacts":1,"month":"nov","day":6,"contact_duration":383,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0},{"index":41185,"client_id":41185,"number_contacts":2,"month":"nov","day":24,"contact_duration":189,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0},{"index":41186,"client_id":41186,"number_contacts":1,"month":"nov","day":17,"contact_duration":442,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":1},{"index":41187,"client_id":41187,"number_contacts":3,"month":"nov","day":23,"contact_duration":239,"pdays":999,"previous_campaign_contacts":1,"previous_outcome":0,"campaign_outcome":0}]},"total_rows":10,"truncation_type":null},"text/plain":" client_id number_contacts ... previous_outcome campaign_outcome\n41178 41178 2 ... 1.0 1\n41179 41179 3 ... NaN 0\n41180 41180 2 ... NaN 0\n41181 41181 1 ... NaN 1\n41182 41182 1 ... 1.0 0\n41183 41183 1 ... NaN 1\n41184 41184 1 ... NaN 0\n41185 41185 2 ... NaN 0\n41186 41186 1 ... NaN 1\n41187 41187 3 ... 0.0 0\n\n[10 rows x 9 columns]","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>client_id</th>\n <th>number_contacts</th>\n <th>month</th>\n <th>day</th>\n <th>contact_duration</th>\n <th>pdays</th>\n <th>previous_campaign_contacts</th>\n <th>previous_outcome</th>\n <th>campaign_outcome</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>41178</th>\n <td>41178</td>\n <td>2</td>\n <td>nov</td>\n <td>26</td>\n <td>483</td>\n <td>6</td>\n <td>3</td>\n <td>1.0</td>\n <td>1</td>\n </tr>\n <tr>\n <th>41179</th>\n <td>41179</td>\n <td>3</td>\n <td>nov</td>\n <td>7</td>\n <td>151</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n </tr>\n <tr>\n <th>41180</th>\n <td>41180</td>\n <td>2</td>\n <td>nov</td>\n <td>27</td>\n <td>254</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n </tr>\n <tr>\n <th>41181</th>\n <td>41181</td>\n <td>1</td>\n <td>nov</td>\n <td>20</td>\n <td>281</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>1</td>\n </tr>\n <tr>\n <th>41182</th>\n <td>41182</td>\n <td>1</td>\n <td>nov</td>\n <td>26</td>\n <td>112</td>\n <td>9</td>\n <td>1</td>\n <td>1.0</td>\n <td>0</td>\n </tr>\n <tr>\n <th>41183</th>\n <td>41183</td>\n <td>1</td>\n <td>nov</td>\n <td>30</td>\n <td>334</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>1</td>\n </tr>\n <tr>\n <th>41184</th>\n <td>41184</td>\n <td>1</td>\n <td>nov</td>\n <td>6</td>\n <td>383</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n </tr>\n <tr>\n <th>41185</th>\n <td>41185</td>\n <td>2</td>\n <td>nov</td>\n <td>24</td>\n <td>189</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n </tr>\n <tr>\n <th>41186</th>\n <td>41186</td>\n <td>1</td>\n <td>nov</td>\n <td>17</td>\n <td>442</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>1</td>\n </tr>\n <tr>\n <th>41187</th>\n <td>41187</td>\n <td>3</td>\n <td>nov</td>\n <td>23</td>\n <td>239</td>\n <td>999</td>\n <td>1</td>\n <td>0.0</td>\n <td>0</td>\n </tr>\n </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":122}]},{"source":"campaign['campaign_id'] = 1\ncampaign.head()","metadata":{"executionCancelledAt":null,"executionTime":64,"lastExecutedAt":1698387697624,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"campaign['campaign_id'] = 1\ncampaign.head()","outputsMetadata":{"0":{"height":208,"type":"dataFrame"}}},"cell_type":"code","id":"3ebb26ae-25ce-4c5b-a577-9163bb59d99e","execution_count":123,"outputs":[{"output_type":"execute_result","data":{"application/com.datacamp.data-table.v1+json":{"table":{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"client_id","type":"integer"},{"name":"number_contacts","type":"integer"},{"name":"month","type":"string"},{"name":"day","type":"integer"},{"name":"contact_duration","type":"integer"},{"name":"pdays","type":"integer"},{"name":"previous_campaign_contacts","type":"integer"},{"name":"previous_outcome","type":"number"},{"name":"campaign_outcome","type":"integer"},{"name":"campaign_id","type":"integer"}],"primaryKey":["index"],"pandas_version":"1.4.0"},"data":[{"index":0,"client_id":0,"number_contacts":1,"month":"may","day":13,"contact_duration":261,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1},{"index":1,"client_id":1,"number_contacts":1,"month":"may","day":19,"contact_duration":149,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1},{"index":2,"client_id":2,"number_contacts":1,"month":"may","day":23,"contact_duration":226,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1},{"index":3,"client_id":3,"number_contacts":1,"month":"may","day":27,"contact_duration":151,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1},{"index":4,"client_id":4,"number_contacts":1,"month":"may","day":3,"contact_duration":307,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1}]},"total_rows":5,"truncation_type":null},"text/plain":" client_id number_contacts ... campaign_outcome campaign_id\n0 0 1 ... 0 1\n1 1 1 ... 0 1\n2 2 1 ... 0 1\n3 3 1 ... 0 1\n4 4 1 ... 0 1\n\n[5 rows x 10 columns]","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>client_id</th>\n <th>number_contacts</th>\n <th>month</th>\n <th>day</th>\n <th>contact_duration</th>\n <th>pdays</th>\n <th>previous_campaign_contacts</th>\n <th>previous_outcome</th>\n <th>campaign_outcome</th>\n <th>campaign_id</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>0</td>\n <td>1</td>\n <td>may</td>\n <td>13</td>\n <td>261</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n </tr>\n <tr>\n <th>1</th>\n <td>1</td>\n <td>1</td>\n <td>may</td>\n <td>19</td>\n <td>149</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n </tr>\n <tr>\n <th>2</th>\n <td>2</td>\n <td>1</td>\n <td>may</td>\n <td>23</td>\n <td>226</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n </tr>\n <tr>\n <th>3</th>\n <td>3</td>\n <td>1</td>\n <td>may</td>\n <td>27</td>\n <td>151</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n </tr>\n <tr>\n <th>4</th>\n <td>4</td>\n <td>1</td>\n <td>may</td>\n <td>3</td>\n <td>307</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n </tr>\n </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":123}]},{"source":"#create last_contact_date column\ncampaign['month'] = campaign['month'].str.capitalize()\ncampaign['year'] = '2022'\ncampaign['day'] = campaign['day'].astype(str)\ncampaign['last_contact_date'] = campaign['year'] + '-' + campaign['month'] + '-' + campaign['day']\ncampaign['last_contact_date'] = pd.to_datetime(campaign['last_contact_date'],\n format='%Y-%b-%d')\ncampaign.head()","metadata":{"executionCancelledAt":null,"executionTime":58,"lastExecutedAt":1698387973484,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"campaign['month'] = campaign['month'].str.capitalize()\ncampaign['year'] = '2022'\ncampaign['day'] = campaign['day'].astype(str)\ncampaign['last_contact_date'] = campaign['year'] + '-' + campaign['month'] + '-' + campaign['day']\ncampaign['last_contact_date'] = pd.to_datetime(campaign['last_contact_date'],\n format='%Y-%b-%d')\ncampaign.head()","outputsMetadata":{"0":{"height":208,"type":"dataFrame"}}},"cell_type":"code","id":"356e4906-f6dd-4584-b20d-c8ff9b8c9b3a","execution_count":129,"outputs":[{"output_type":"execute_result","data":{"application/com.datacamp.data-table.v1+json":{"table":{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"client_id","type":"integer"},{"name":"number_contacts","type":"integer"},{"name":"month","type":"string"},{"name":"day","type":"string"},{"name":"contact_duration","type":"integer"},{"name":"pdays","type":"integer"},{"name":"previous_campaign_contacts","type":"integer"},{"name":"previous_outcome","type":"number"},{"name":"campaign_outcome","type":"integer"},{"name":"campaign_id","type":"integer"},{"name":"year","type":"string"},{"name":"last_contact_date","type":"datetime"}],"primaryKey":["index"],"pandas_version":"1.4.0"},"data":[{"index":0,"client_id":0,"number_contacts":1,"month":"May","day":"13","contact_duration":261,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1,"year":"2022","last_contact_date":"2022-05-13T00:00:00.000"},{"index":1,"client_id":1,"number_contacts":1,"month":"May","day":"19","contact_duration":149,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1,"year":"2022","last_contact_date":"2022-05-19T00:00:00.000"},{"index":2,"client_id":2,"number_contacts":1,"month":"May","day":"23","contact_duration":226,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1,"year":"2022","last_contact_date":"2022-05-23T00:00:00.000"},{"index":3,"client_id":3,"number_contacts":1,"month":"May","day":"27","contact_duration":151,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1,"year":"2022","last_contact_date":"2022-05-27T00:00:00.000"},{"index":4,"client_id":4,"number_contacts":1,"month":"May","day":"3","contact_duration":307,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1,"year":"2022","last_contact_date":"2022-05-03T00:00:00.000"}]},"total_rows":5,"truncation_type":null},"text/plain":" client_id number_contacts month ... campaign_id year last_contact_date\n0 0 1 May ... 1 2022 2022-05-13\n1 1 1 May ... 1 2022 2022-05-19\n2 2 1 May ... 1 2022 2022-05-23\n3 3 1 May ... 1 2022 2022-05-27\n4 4 1 May ... 1 2022 2022-05-03\n\n[5 rows x 12 columns]","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>client_id</th>\n <th>number_contacts</th>\n <th>month</th>\n <th>day</th>\n <th>contact_duration</th>\n <th>pdays</th>\n <th>previous_campaign_contacts</th>\n <th>previous_outcome</th>\n <th>campaign_outcome</th>\n <th>campaign_id</th>\n <th>year</th>\n <th>last_contact_date</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>0</td>\n <td>1</td>\n <td>May</td>\n <td>13</td>\n <td>261</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n <td>2022</td>\n <td>2022-05-13</td>\n </tr>\n <tr>\n <th>1</th>\n <td>1</td>\n <td>1</td>\n <td>May</td>\n <td>19</td>\n <td>149</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n <td>2022</td>\n <td>2022-05-19</td>\n </tr>\n <tr>\n <th>2</th>\n <td>2</td>\n <td>1</td>\n <td>May</td>\n <td>23</td>\n <td>226</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n <td>2022</td>\n <td>2022-05-23</td>\n </tr>\n <tr>\n <th>3</th>\n <td>3</td>\n <td>1</td>\n <td>May</td>\n <td>27</td>\n <td>151</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n <td>2022</td>\n <td>2022-05-27</td>\n </tr>\n <tr>\n <th>4</th>\n <td>4</td>\n <td>1</td>\n <td>May</td>\n <td>3</td>\n <td>307</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n <td>2022</td>\n <td>2022-05-03</td>\n </tr>\n </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":129}]},{"source":"#remove unnecessary columns\ncampaign.drop(columns=['month', 'day', 'year'], inplace=True)\ncampaign.head()","metadata":{"executionCancelledAt":null,"executionTime":null,"lastExecutedAt":null,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":null,"outputsMetadata":{"0":{"height":208,"type":"dataFrame"}}},"cell_type":"code","id":"cc818d82-e021-43c6-8bb8-258b89fae665","execution_count":130,"outputs":[{"output_type":"execute_result","data":{"application/com.datacamp.data-table.v1+json":{"table":{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"client_id","type":"integer"},{"name":"number_contacts","type":"integer"},{"name":"contact_duration","type":"integer"},{"name":"pdays","type":"integer"},{"name":"previous_campaign_contacts","type":"integer"},{"name":"previous_outcome","type":"number"},{"name":"campaign_outcome","type":"integer"},{"name":"campaign_id","type":"integer"},{"name":"last_contact_date","type":"datetime"}],"primaryKey":["index"],"pandas_version":"1.4.0"},"data":[{"index":0,"client_id":0,"number_contacts":1,"contact_duration":261,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1,"last_contact_date":"2022-05-13T00:00:00.000"},{"index":1,"client_id":1,"number_contacts":1,"contact_duration":149,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1,"last_contact_date":"2022-05-19T00:00:00.000"},{"index":2,"client_id":2,"number_contacts":1,"contact_duration":226,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1,"last_contact_date":"2022-05-23T00:00:00.000"},{"index":3,"client_id":3,"number_contacts":1,"contact_duration":151,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1,"last_contact_date":"2022-05-27T00:00:00.000"},{"index":4,"client_id":4,"number_contacts":1,"contact_duration":307,"pdays":999,"previous_campaign_contacts":0,"previous_outcome":null,"campaign_outcome":0,"campaign_id":1,"last_contact_date":"2022-05-03T00:00:00.000"}]},"total_rows":5,"truncation_type":null},"text/plain":" client_id number_contacts ... campaign_id last_contact_date\n0 0 1 ... 1 2022-05-13\n1 1 1 ... 1 2022-05-19\n2 2 1 ... 1 2022-05-23\n3 3 1 ... 1 2022-05-27\n4 4 1 ... 1 2022-05-03\n\n[5 rows x 9 columns]","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>client_id</th>\n <th>number_contacts</th>\n <th>contact_duration</th>\n <th>pdays</th>\n <th>previous_campaign_contacts</th>\n <th>previous_outcome</th>\n <th>campaign_outcome</th>\n <th>campaign_id</th>\n <th>last_contact_date</th>\n </tr>\n </thead>\n <tbody>\n <tr>\n <th>0</th>\n <td>0</td>\n <td>1</td>\n <td>261</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n <td>2022-05-13</td>\n </tr>\n <tr>\n <th>1</th>\n <td>1</td>\n <td>1</td>\n <td>149</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n <td>2022-05-19</td>\n </tr>\n <tr>\n <th>2</th>\n <td>2</td>\n <td>1</td>\n <td>226</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n <td>2022-05-23</td>\n </tr>\n <tr>\n <th>3</th>\n <td>3</td>\n <td>1</td>\n <td>151</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n <td>2022-05-27</td>\n </tr>\n <tr>\n <th>4</th>\n <td>4</td>\n <td>1</td>\n <td>307</td>\n <td>999</td>\n <td>0</td>\n <td>NaN</td>\n <td>0</td>\n <td>1</td>\n <td>2022-05-03</td>\n </tr>\n </tbody>\n</table>\n</div>"},"metadata":{},"execution_count":130}]},{"source":"client.to_csv(\"client2.csv\", index=False)\ncampaign.to_csv(\"campaign2.csv\", index=False)\neconomics.to_csv(\"economics2.csv\", index=False)","metadata":{"executionCancelledAt":null,"executionTime":420,"lastExecutedAt":1698388183173,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"client.to_csv(\"client2.csv\", index=False)\ncampaign.to_csv(\"campaign2.csv\", index=False)\neconomics.to_csv(\"economics2.csv\", index=False)"},"cell_type":"code","id":"8a503b02-629e-471c-a836-9f77eee32dce","execution_count":131,"outputs":[]},{"source":"#store and print database_design\nclient_table = \"\"\"CREATE TABLE client\n(\n id SERIAL PRIMARY KEY,\n age INTEGER,\n job TEXT,\n marital TEXT,\n education TEXT,\n credit_default BOOLEAN,\n housing BOOLEAN,\n loan BOOLEAN\n);\n\\copy client from 'client2.csv' DELIMITER ',' CSV HEADER\n\"\"\"\n\ncampaign_table = \"\"\"CREATE TABLE campaign\n(\n campaign_id SERIAL PRIMARY KEY,\n client_id SERIAL references client (id),\n number_contacts INTEGER,\n contact_duration INTEGER,\n pdays INTEGER,\n previous_campaign_contacts INTEGER,\n previous_outcome BOOLEAN,\n campaign_outcome BOOLEAN,\n last_contact_date DATE \n);\n\\copy campaign from 'campaign2.csv' DELIMITER ',' CSV HEADER\n\"\"\"\n\neconomics_table = \"\"\"CREATE TABLE economics\n(\n client_id SERIAL references client (id),\n emp_var_rate FLOAT,\n cons_price_idx FLOAT,\n euribor_three_months FLOAT,\n number_employed FLOAT\n);\n\\copy economics from 'economics2.csv' DELIMITER ',' CSV HEADER\n\"\"\"","metadata":{"executionCancelledAt":null,"executionTime":49,"lastExecutedAt":1698388609804,"lastScheduledRunId":null,"lastSuccessfullyExecutedCode":"#store and print database_design\nclient_table = \"\"\"CREATE TABLE client\n(\n id SERIAL PRIMARY KEY,\n age INTEGER,\n job TEXT,\n marital TEXT,\n education TEXT,\n credit_default BOOLEAN,\n housing BOOLEAN,\n loan BOOLEAN\n);\n\\copy client from 'client.csv' DELIMITER ',' CSV HEADER\n\"\"\"\n\ncampaign_table = \"\"\"CREATE TABLE campaign\n(\n campaign_id SERIAL PRIMARY KEY,\n client_id SERIAL references client (id),\n number_contacts INTEGER,\n contact_duration INTEGER,\n pdays INTEGER,\n previous_campaign_contacts INTEGER,\n previous_outcome BOOLEAN,\n campaign_outcome BOOLEAN,\n last_contact_date DATE \n);\n\\copy campaign from 'campaign.csv' DELIMITER ',' CSV HEADER\n\"\"\"\n\neconomics_table = \"\"\"CREATE TABLE economics\n(\n client_id SERIAL references client (id),\n emp_var_rate FLOAT,\n cons_price_idx FLOAT,\n euribor_three_months FLOAT,\n number_employed FLOAT\n);\n\\copy economics from 'economics.csv' DELIMITER ',' CSV HEADER\n\"\"\""},"cell_type":"code","id":"a00a7373-b55a-408c-bf04-ec27efb26835","execution_count":132,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3 (ipykernel)","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.8.10","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"editor":"DataCamp Workspace"},"nbformat":4,"nbformat_minor":5} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment