notebooks/1_database_data_validation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# Database data validation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "In this notebook, we will see how we can use the great_expectations package to validate data in our database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/NatanMish/data_validation/blob/main/notebooks/1_database_data_validation.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Install the required packages and import them to the notebook\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "!pip install -U great_expectations pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# import the required packages\n",
    "import great_expectations as ge\n",
    "from great_expectations.core.batch import RuntimeBatchRequest\n",
    "from great_expectations.profile.user_configurable_profiler import (\n",
    "    UserConfigurableProfiler,\n",
    ")\n",
    "from great_expectations.checkpoint import SimpleCheckpoint\n",
    "from ruamel import yaml\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "[![Great Expectations](https://docs.greatexpectations.io/img/great-expectations-long-logo.svg)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Named after the famous 19th century novel, Great Expectations is a shared, open sourced package for data quality. It helps eliminate pipeline debt, through data testing, documentation, and profiling. It is a tool for data scientists, data engineers, and data analysts to validate data. GE has many useful integrations and can be connected directly to SQL databases, Apache Spark, Apache Airflow, Bigquery, and more. In this tutorial, we will validate a database hosted on a  local file system, but the process for a cloud file system such as a Data Lake, Azure Blob Storage, GCP bucket or AWS S3 is almost identical.\n",
    "\n",
    "**Terminology**\n",
    "1. *Data Context* - The primary entry point for a Great Expectations deployment, with configurations and methods for all supporting components.\n",
    "\n",
    "2. *Data Source* - Provides a standard API for accessing and interacting with data from a wide variety of source systems.\n",
    "\n",
    "3. *Data Asset* - A collection of records within a Datasource which is usually named based on the underlying data system and sliced to correspond to a desired specification.\n",
    "\n",
    "4. *Expectation Suite* - A collection of verifiable assertions about data.\n",
    "\n",
    "5. *Validation* - The act of applying an Expectation Suite to a Batch.\n",
    "\n",
    "6. *Batch Identifier* - contains information that uniquely identifies a specific batch from the Data Asset, such as the delivery date or query time.\n",
    "\n",
    "7. *Data Connector* - Provides the configuration details based on the source data system which are needed by a Datasource to define Data Assets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 1. Create a Data Context\n",
    "\n",
    "We will now create a data context, which is the first step in setting up Great Expectations for our project. Creating a data context is actually most easily done in bash using the great_expectations CLI. Run the shell command below and this will initialize a new data context in the current directory. The `echo y` bit is used to suppress the interactive prompt. You will now see a new directory called `great_expectations` created in your current directory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "!echo y | great_expectations init"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "After running the init command, your great_expectations directory will contain all the important components of a local Great Expectations deployment. This is what the directory structure looks like:\n",
    "\n",
    "- `great_expectations.yml` contains the main configuration of your deployment.\n",
    "The expectations directory stores all your Expectations as JSON files. If you want to store them somewhere else, you can change that later.\n",
    "\n",
    "- The `plugins/` directory holds code for any custom plugins you develop as part of your deployment.\n",
    "\n",
    "- The `uncommitted/` directory contains files that shouldn’t live in version control. It has a .gitignore configured to exclude all its contents from version control. The main contents of the directory are:\n",
    "    1. `uncommitted/config_variables.yml`, which holds sensitive information, such as database credentials and other secrets.\n",
    "    2. `uncommitted/data_docs`, which contains Data Docs generated from Expectations, Validation Results, and other metadata.\n",
    "    3. `uncommitted/validations`, which holds Validation Results generated by Great Expectations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<div>\n",
    "<img src=\"https://docs.greatexpectations.io/assets/images/data_context_does_for_you-df2eca32d0152ead16cccd5d3d226abb.png\" width=\"1000\"/>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 2. Create a Data Source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# We will start by reading in the GE data context we have created in the previous step\n",
    "context = ge.get_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Now we will script a yaml file to create a data source. We will need the following configuration parameters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "datasource_name = \"house_prices\"\n",
    "# Data Source - Provides a standard API for accessing and interacting with data from a wide variety of source systems."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "execution_engine = \"PandasExecutionEngine\"  # alternatively we can use SparkExecutionEngine for PySpark oriented\n",
    "# projects or SqlAlchemyExecutionEngine for creating a SQL database data source.\n",
    "data_directory = \"data\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "data_asset_name = f\"{datasource_name}_survey_2006\"\n",
    "# Data Asset - A collection of records within a Datasource which is usually named based on the underlying data system and sliced to correspond to a desired specification."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "runtime_data_connector_name = \"runtime_batch_files_connector\"\n",
    "# Data Connector - Provides the configuration details based on the source data system which are needed by a Datasource to define Data Assets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "batch_identifier_name = \"pipeline_step\"\n",
    "# Batch Identifier - contains information that uniquely identifies a specific batch from the Data Asset, such as the delivery date or query time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "datasource_config = {\n",
    "    \"name\": datasource_name,\n",
    "    \"class_name\": \"Datasource\",\n",
    "    \"module_name\": \"great_expectations.datasource\",\n",
    "    \"execution_engine\": {\n",
    "        \"module_name\": \"great_expectations.execution_engine\",\n",
    "        \"class_name\": execution_engine,\n",
    "    },\n",
    "    \"data_connectors\": {\n",
    "        runtime_data_connector_name: {\n",
    "            \"class_name\": \"RuntimeDataConnector\",\n",
    "            \"module_name\": \"great_expectations.datasource.data_connector\",\n",
    "            \"assets\": {\n",
    "              data_asset_name: {\n",
    "                \"class_name\": \"Asset\",\n",
    "                \"batch_identifiers\": [batch_identifier_name],\n",
    "                \"module_name\": \"great_expectations.datasource.data_connector.asset\"}}\n",
    "        },\n",
    "    },\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Test that the configuration is valid\n",
    "context.test_yaml_config(yaml.dump(datasource_config))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# If the configuration is valid, we can create the datasource\n",
    "context.add_datasource(**datasource_config)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Now we can see that the datasource was created.\n",
    "context.list_datasources()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 3. Create an Expectation Suite\n",
    "Expectations are the core of Great Expectations. They are the assertions that are used to validate data. Let's create an expectation suite which is a collection of expectations. This diagram below shows how we can define good expectations for our data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<div>\n",
    "<img src=\"https://docs.greatexpectations.io/assets/images/where_expectations_come_from-b3504cf51ad304c8e4a73677a0e73156.png\" width=\"1000\"/>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "We will create expectations while exploring the data in the notebook. The method below behaves  exactly the same as `pandas.read_csv`. Similarly wrapped versions of other pandas methods (`read_excel`, `read_table`, `read_parquet`, `read_pickle`, `read_json`, etc.) are also available."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "house_data = ge.read_csv(\"https://github.com/NatanMish/data_validation/blob/a77b247b25c6622ce0c8f8cbc505228161c31a3c/data/train.csv?raw=true\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# The house_data variable is a pandas dataframe with all the methods and properties we know and love. We can use the `head` method to see the\n",
    "# first few rows of the data.\n",
    "house_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# beyond the Pandas methods and properties, we can use GE's expectations methods to define expectations. \n",
    "# In Jupyter, type in `house_data.expect` and press tab to see the list of available expectations.\n",
    "dir(house_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Let's create a few example expectations and see if they are valid on this dataset.\n",
    "house_data.expect_column_to_exist(\"Id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Notice the `\"success\": true` key in the result dictionary, this means the expectation is valid for this data source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "house_data.expect_column_values_to_be_unique(\"Id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "The expectation above checked the contents of the column, hence we got a few other useful metrics, showing how many \n",
    "rows were inspected, how many were missing etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# This expectation should fail, lets see what happens:\n",
    "house_data.expect_column_max_to_be_between(\"SalePrice\", 0, 100000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "The returned dictionary shows that the expectation is not valid, and the value observed that is not in the expected range.\n",
    "Here are a few more useful expectation definitions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%% mdhhome_data.expect_column_distinct_values_to_be_in_set\n"
    }
   },
   "outputs": [],
   "source": [
    "house_data.expect_column_distinct_values_to_be_in_set(\"MSZoning\", [\"C (all)\", \"FV\", \"RH\", \"RL\", \"RM\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "house_data.expect_column_mean_to_be_between(\"GrLivArea\", 0, 10000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# This will create an expectation suite from all the valid expectations we created above.\n",
    "house_data.get_expectation_suite()\n",
    "# If we want the non-valid expectations as well, we can use the `get_expectation_suite` method with the \n",
    "# `discard_failed_expectations` parameter set to True. If there are any duplicate expectations in the suite, \n",
    "# the duplicates will be discarded:\n",
    "# house_data.get_expectation_suite(discard_failed_expectations=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# This line will save the expectation suite to the data context\n",
    "expectation_suite_name = \"my_expectations\"\n",
    "context.save_expectation_suite(house_data.get_expectation_suite(), expectation_suite_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### Exercise 1\n",
    "Check the following expectations to see if they are valid on the house_data dataframe:\n",
    "\n",
    "(Not all the expectations were included in the examples above. You can find more expectations in the [expectations directory](https://greatexpectations.io/expectations).)\n",
    "1. `Street` column should be a string.\n",
    "2. `LandContour` column cannot be null.\n",
    "3. `YearBuilt` minimal value should be between 1700 and 1900.\n",
    "4. `LotArea` median value should be between 5000 and 15000.\n",
    "5. The most common values in `SaleType` must be either `WD` or `New`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# your answers here:\n",
    "# house_data.expect_column_\n",
    "# house_data.expect_column_\n",
    "# house_data.expect_column_\n",
    "# house_data.expect_column_\n",
    "# house_data.expect_column_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "*Exercise solutions can be found in the exercise solutions file in the current directory.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 4. Validate the Data\n",
    "We will now validate the test data using the expectations we have created for the train data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<div>\n",
    "<img src=\"https://docs.greatexpectations.io/assets/images/how_a_checkpoint_works-10e7fda2c9013d98a36c1d8526036764.png\" width=\"1000\"/>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "checkpoint_name = \"data_batch_appended\"\n",
    "# Checkpoint - The primary means for validating data in a production deployment of Great Expectations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "checkpoint_config = {\n",
    "    \"name\": checkpoint_name,\n",
    "    \"config_version\": 1,\n",
    "    \"class_name\": \"SimpleCheckpoint\",\n",
    "    \"validations\": [\n",
    "        {\n",
    "            \"batch_request\": {\n",
    "                \"datasource_name\": datasource_name,\n",
    "                \"data_connector_name\": runtime_data_connector_name,\n",
    "                \"data_asset_name\": data_asset_name,\n",
    "            },\n",
    "            \"expectation_suite_name\": expectation_suite_name,\n",
    "        }\n",
    "    ],\n",
    "}\n",
    "context.add_checkpoint(**checkpoint_config)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Looking at the dictionary returned by the `add_checkpoint` methods we can see what are the actions performed every time the checkpoint will run:\n",
    "1. Store validation result.\n",
    "2. Store evaluation parameters.\n",
    "3. Update data docs. (we will look at the data docs later in this notebook)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "house_data_test = ge.read_csv(\"https://github.com/NatanMish/data_validation/blob/a77b247b25c6622ce0c8f8cbc505228161c31a3c/data/test.csv?raw=true\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "results = context.run_checkpoint(\n",
    "    checkpoint_name=checkpoint_name,\n",
    "    batch_request={\n",
    "        \"runtime_parameters\": {\"batch_data\": house_data_test},\n",
    "        \"batch_identifiers\": {\n",
    "            batch_identifier_name: \"step_1\"\n",
    "        },\n",
    "    },\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Let's take a look at the validation result object we got:\n",
    "run_identifier = next(iter(results['run_results']))\n",
    "results['run_results'][run_identifier]['validation_result']['statistics']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Here is an example of one of the validations on one of the expectations. The check has passed and there are some \n",
    "# useful extra details too.\n",
    "results['run_results'][run_identifier]['validation_result']['results'][2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### How does an invalid data checkpoint look like?\n",
    "Glad you asked, let's inject a duplicate value to our `Id` column to see how it behaves:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# This will create a duplicate id value for two separate records\n",
    "house_data_test.at[0, 'Id'] = 1462"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "validator = {\n",
    "            \"batch_request\": {\n",
    "                \"datasource_name\": datasource_name,\n",
    "                \"data_connector_name\": runtime_data_connector_name,\n",
    "                \"data_asset_name\": data_asset_name,\n",
    "            },\n",
    "            \"expectation_suite_name\": expectation_suite_name,\n",
    "        }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Notice how we've adde the runtime configuration and result format = COMPLETE, this will give us the unexpected index list\n",
    "\n",
    "bad_data_checkpoint_name = \"my_bad_data_checkpoint\"\n",
    "bad_data_checkpoint_config = {\n",
    "    \"name\": bad_data_checkpoint_name,\n",
    "    \"config_version\": 1,\n",
    "    \"class_name\": \"SimpleCheckpoint\",\n",
    "    \"runtime_configuration\": {\n",
    "        \"result_format\": {\n",
    "            \"result_format\": \"COMPLETE\", # BASIC\n",
    "            \"include_unexpected_rows\": True\n",
    "        }\n",
    "    },\n",
    "    \"validations\": [\n",
    "      validator \n",
    "    ],\n",
    "}\n",
    "context.add_checkpoint(**bad_data_checkpoint_config)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "results_bad_data_checkpoint = context.run_checkpoint(\n",
    "    checkpoint_name=bad_data_checkpoint_name,\n",
    "    batch_request={\n",
    "        \"runtime_parameters\": {\"batch_data\": house_data_test},\n",
    "        \"batch_identifiers\": {\n",
    "            batch_identifier_name: \"step_2\"\n",
    "        },\n",
    "    },\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# As expected, not all expectations were successful.\n",
    "bad_data_run_identifier = next(iter(results_bad_data_checkpoint['run_results']))\n",
    "results_bad_data_checkpoint['run_results'][bad_data_run_identifier]['validation_result']['statistics']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# And here is the summary for the failed expectation\n",
    "results_bad_data_checkpoint['run_results'][bad_data_run_identifier]['validation_result']['results'][1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# We can unpack the unexpected data indices list\n",
    "unexpected_data_indices = results_bad_data_checkpoint['run_results'][bad_data_run_identifier]['validation_result']['results'][1]['result']['unexpected_index_list']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# And then filter the invalid data from the dataframe\n",
    "filtered_house_data_test = house_data_test[~house_data_test.index.isin(unexpected_data_indices)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 6. Create Data Docs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Build the data docs, in Jupyter a new tab will open up with the data docs page\n",
    "!echo y | great_expectations docs build --site-name local_site"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# in Google Colab you'll have to download the folder and open it up locally in /uncommited/data_docs/local_site/index.html. \n",
    "# Uncomment and run the 3 lines below:\n",
    "# from google.colab import files\n",
    "# !zip -r data_docs.zip great_expectations/uncommitted/data_docs\n",
    "# files.download('data_docs.zip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 7. Using the User Configurable Profiler to compare two tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "The configurable profiler feature provided by Great Expectations can be a great place to start with a never seen before dataset. It creates automated Expectations\n",
    "based on the values and aggregates of the data. Another way to utilise it is to create automated expectations on one table of data, and then validate them against\n",
    "a second table, which allows a deep comparison between the two."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Exclude most columns so the expectation creation process is not too long\n",
    "exclude_column_names = ['LotArea', 'Street', 'Alley', 'LotShape', 'LandContour', 'Utilities', \"LotConfig\", \"LandSlope\", \"Neighborhood\", \"Condition1\", \"Condition2\", \"BldgType\", \"HouseStyle\", \"OverallQual\", \"OverallCond\", \"YearBuilt\", \"YearRemodAdd\", \"RoofStyle\", \"RoofMatl\", \"Exterior1st\", \"Exterior2nd\", \"MasVnrType\", \"MasVnrArea\", \"ExterQual\", \"ExterCond\", \"Foundation\", \"BsmtQual\", \"BsmtCond\", \"BsmtExposure\", \"BsmtFinType1\", \"BsmtFinSF1\", \"BsmtFinType2\", \"BsmtFinSF2\", \"BsmtUnfSF\", \"TotalBsmtSF\", \"Heating\", \"HeatingQC\", \"CentralAir\", \"Electrical\", \"PoolArea\", \"PoolQC\", \"LowQualFinSF\", \"GrLivArea\", \"BsmtFullBath\", \"BsmtHalfBath\", \"FullBath\", \"HalfBath\", \"BedroomAbvGr\", \"KitchenAbvGr\", \"KitchenQual\", \"TotRmsAbvGrd\", \"Functional\", \"Fireplaces\", \"FireplaceQu\", \"GarageType\", \"GarageYrBlt\", \"GarageFinish\", \"GarageCars\", \"GarageArea\", \"GarageQual\", \"GarageCond\", \"PavedDrive\", \"WoodDeckSF\", \"OpenPorchSF\", \"EnclosedPorch\", \"3SsnPorch\", \"ScreenPorch\", \"Fence\", \"MiscFeature\", \"MiscVal\", \"MoSold\", \"YrSold\", \"SaleType\", \"SaleCondition\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "house_data.drop(exclude_column_names, axis=1).columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "runtime_batch_request = RuntimeBatchRequest(\n",
    "    datasource_name=datasource_name,\n",
    "    data_connector_name=runtime_data_connector_name,\n",
    "    data_asset_name=data_asset_name,\n",
    "    runtime_parameters={\"batch_data\": house_data.drop(exclude_column_names, axis=1)},\n",
    "    batch_identifiers={\n",
    "        batch_identifier_name: \"step_3\",\n",
    "    },\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Create a validator instance from the batch request\n",
    "validator = context.get_validator(batch_request=runtime_batch_request)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "profiler = UserConfigurableProfiler(\n",
    "    profile_dataset=validator,\n",
    "    excluded_expectations=None,\n",
    "    ignored_columns=exclude_column_names,\n",
    "    not_null_only=False,\n",
    "    primary_or_compound_key=None,\n",
    "    semantic_types_dict=None,\n",
    "    table_expectations_only=False,\n",
    "    value_set_threshold=\"MANY\",\n",
    ")\n",
    "suite = profiler.build_suite()\n",
    "validator.expectation_suite = suite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "validator.save_expectation_suite(discard_failed_expectations=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Create a checkpoint based on the expectation suite built using the profiler on the house_data table, and use it to validate the house_data_test\n",
    "profiled_validator_checkpoint = \"profiled_validator\"\n",
    "checkpoint_config = {\n",
    "    \"name\": profiled_validator_checkpoint,\n",
    "    \"config_version\": 1,\n",
    "    \"class_name\": \"SimpleCheckpoint\",\n",
    "    \"validations\": [\n",
    "        {\n",
    "            \"batch_request\": {\n",
    "                \"datasource_name\": datasource_name,\n",
    "                \"data_connector_name\": runtime_data_connector_name,\n",
    "                \"data_asset_name\": data_asset_name,\n",
    "            },\n",
    "            \"expectation_suite_name\": \"default\",\n",
    "        }\n",
    "    ],\n",
    "}\n",
    "context.add_checkpoint(**checkpoint_config)\n",
    "results_profiled_checkpoint = context.run_checkpoint(\n",
    "    checkpoint_name=profiled_validator_checkpoint,\n",
    "    batch_request={\n",
    "        \"runtime_parameters\": {\"batch_data\": house_data_test.drop(exclude_column_names, axis=1)},\n",
    "        \"batch_identifiers\": {\n",
    "            batch_identifier_name: \"step_4\"\n",
    "        },\n",
    "    },\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Run and open the data docs to look at the comparison summary\n",
    "context.build_data_docs()\n",
    "\n",
    "validation_result_identifier = results_profiled_checkpoint.list_validation_result_identifiers()[0]\n",
    "context.open_data_docs(resource_identifier=validation_result_identifier)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# in Google Colab you'll have to download the folder and open it up locally in /uncommited/data_docs/local_site/index.html. \n",
    "# Uncomment and run the 3 lines below:\n",
    "# from google.colab import files\n",
    "# !zip -r data_docs.zip great_expectations/uncommitted/data_docs\n",
    "# files.download('data_docs.zip')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### 8. Custom Expectations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "We will explain the process of creating a custom expectation using an example. A common method to check for outliers is to calculate the Z-score for each value\n",
    "and signal out any value with a Z-score above 3."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<div>\n",
    "<img src=\"https://datasciencelk.com/wp-content/uploads/2020/05/normal-plot-with-sigma.jpg\" width=\"600\"/>\n",
    "<img src=\"https://www.gstatic.com/education/formulas2/443397389/en/z_score.svg\" width=200/>\n",
    "</div>\n",
    "<a href=\"https://datasciencelk.com/normal-distribution-z-scores-standardization-explained/\">https://datasciencelk.com/normal-distribution-z-scores-standardization-explained/</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Creating a custom expectation requires creating a separate module, we will use a template provided by Great Expectations and make minor adjustments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "%%writefile expect_column_z_score_lower_than_3.py\n",
    "\"\"\"\n",
    "This is a template for creating custom ColumnExpectations.\n",
    "For detailed instructions on how to use it, please see:\n",
    "    https://docs.greatexpectations.io/docs/guides/expectations/creating_custom_expectations/how_to_create_custom_column_aggregate_expectations\n",
    "\"\"\"\n",
    "\n",
    "import json\n",
    "from typing import Callable, Dict, Optional\n",
    "\n",
    "from numpy import array\n",
    "\n",
    "from great_expectations.core.expectation_configuration import ExpectationConfiguration\n",
    "from great_expectations.execution_engine import (\n",
    "    PandasExecutionEngine,\n",
    "    SparkDFExecutionEngine,\n",
    "    SqlAlchemyExecutionEngine,\n",
    ")\n",
    "from great_expectations.execution_engine.execution_engine import (\n",
    "    ExecutionEngine,\n",
    "    MetricDomainTypes,\n",
    "    MetricPartialFunctionTypes,\n",
    ")\n",
    "from great_expectations.expectations.expectation import (\n",
    "    ColumnMapExpectation,\n",
    "    ExpectationValidationResult,\n",
    ")\n",
    "from great_expectations.expectations.metrics import (\n",
    "    ColumnMapMetricProvider,\n",
    "    column_condition_partial,\n",
    "    metric_partial,\n",
    ")\n",
    "from great_expectations.expectations.metrics.import_manager import F, sa\n",
    "from great_expectations.expectations.util import render_evaluation_parameter_string\n",
    "from great_expectations.render.renderer.renderer import renderer\n",
    "from great_expectations.render.types import (\n",
    "    CollapseContent,\n",
    "    RenderedStringTemplateContent,\n",
    ")\n",
    "from great_expectations.render.util import (\n",
    "    handle_strict_min_max,\n",
    "    parse_row_condition_string_pandas_engine,\n",
    "    substitute_none_for_missing,\n",
    ")\n",
    "from great_expectations.validator.metric_configuration import MetricConfiguration\n",
    "\n",
    "    \n",
    "# This class defines a Metric to support your Expectation.\n",
    "# For most ColumnMapExpectations, the main business logic for calculation will live in this class.\n",
    "class ColumnValuesLowerThanZScoreOf3(ColumnMapMetricProvider):\n",
    "\n",
    "    # This is the id string that will be used to reference your metric.\n",
    "    condition_metric_name = \"column_values.lower_than_z_score_of_3\"\n",
    "\n",
    "    # This method implements the core logic for the PandasExecutionEngine\n",
    "    @column_condition_partial(engine=PandasExecutionEngine)\n",
    "    def _pandas(cls, column, **kwargs):\n",
    "        return abs((abs(column.mean()) - abs(column))/column.std()) < 3\n",
    "\n",
    "\n",
    "# This class defines the Expectation itself\n",
    "class ExpectColumnZScoreLowerThan3(ColumnMapExpectation):\n",
    "    \"\"\"This expectation takes the input column, calculates the standarad deviation, mean for the entire column and then calculates the \n",
    "    Z-score for each value in the column. Any value with a Z-score larger than 3 is considered an outlier. Z-score is defined as: \n",
    "    (value-column_mean)/standard_deviation\"\"\"\n",
    "\n",
    "    # These examples will be shown in the public gallery.\n",
    "    # They will also be executed as unit tests for your Expectation.\n",
    "    examples = [\n",
    "        {\n",
    "            \"data\": {\"x\": [1, 2, 3, 4, 5], \"y\": [-15, 2, 3, 4, 5]},\n",
    "            \"tests\": [\n",
    "                {\n",
    "                    \"title\": \"basic_positive_test\",\n",
    "                    \"exact_match_out\": False,\n",
    "                    \"include_in_gallery\": True,\n",
    "                    \"in\": {\n",
    "                        \"column\": \"x\",\n",
    "                    },\n",
    "                    \"out\": {\"success\": True},\n",
    "                },\n",
    "                {\n",
    "                    \"title\": \"basic_negative_test\",\n",
    "                    \"exact_match_out\": False,\n",
    "                    \"include_in_gallery\": True,\n",
    "                    \"in\": {\n",
    "                        \"column\": \"y\",\n",
    "                    },\n",
    "                    \"out\": {\"success\": False},\n",
    "                },\n",
    "            ],\n",
    "            \"test_backends\": [\n",
    "                {\n",
    "                    \"backend\": \"pandas\",\n",
    "                    \"dialects\": None,\n",
    "                },\n",
    "                # {\n",
    "                #     \"backend\": \"sqlalchemy\",\n",
    "                #     \"dialects\": [\"sqlite\", \"postgresql\"],\n",
    "                # },\n",
    "                # {\n",
    "                #     \"backend\": \"spark\",\n",
    "                #     \"dialects\": None,\n",
    "                # },\n",
    "            ],\n",
    "        }\n",
    "    ]\n",
    "\n",
    "    # This is the id string of the Metric used by this Expectation.\n",
    "    # For most Expectations, it will be the same as the `condition_metric_name` defined in your Metric class above.\n",
    "    map_metric = \"column_values.lower_than_z_score_of_3\"\n",
    "\n",
    "    # This is a list of parameter names that can affect whether the Expectation evaluates to True or False\n",
    "    # Please see https://docs.greatexpectations.io/en/latest/reference/core_concepts/expectations/expectations.html#expectation-concepts-domain-and-success-keys\n",
    "    # for more information about domain and success keys, and other arguments to Expectations\n",
    "    success_keys = (\"mostly\",)\n",
    "\n",
    "    # This dictionary contains default values for any parameters that should have default values\n",
    "    default_kwarg_values = {}\n",
    "\n",
    "    @renderer(renderer_type=\"renderer.diagnostic.observed_value\")\n",
    "    @render_evaluation_parameter_string\n",
    "    def _diagnostic_observed_value_renderer(\n",
    "        cls,\n",
    "        configuration: ExpectationConfiguration = None,\n",
    "        result: ExpectationValidationResult = None,\n",
    "        language: str = None,\n",
    "        runtime_configuration: dict = None,\n",
    "        **kwargs,\n",
    "    ):\n",
    "        assert result, \"Must provide a result object.\"\n",
    "\n",
    "        result_dict = result.result\n",
    "        if result_dict is None:\n",
    "            return \"--\"\n",
    "\n",
    "        if result_dict.get(\"observed_value\"):\n",
    "            observed_value = result_dict.get(\"observed_value\")\n",
    "            if isinstance(observed_value, (int, float)) and not isinstance(\n",
    "                observed_value, bool\n",
    "            ):\n",
    "                return num_to_str(observed_value, precision=10, use_locale=True)\n",
    "            return str(observed_value)\n",
    "        elif result_dict.get(\"unexpected_percent\") is not None:\n",
    "            return (\n",
    "                num_to_str(result_dict.get(\"unexpected_percent\"), precision=5)\n",
    "                + \"% unexpected\"\n",
    "            )\n",
    "        else:\n",
    "            return \"--\"\n",
    "\n",
    "    @renderer(renderer_type=\"renderer.diagnostic.unexpected_statement\")\n",
    "    @render_evaluation_parameter_string\n",
    "    def _diagnostic_unexpected_statement_renderer(\n",
    "        cls,\n",
    "        configuration: ExpectationConfiguration = None,\n",
    "        result: ExpectationValidationResult = None,\n",
    "        language: str = None,\n",
    "        runtime_configuration: dict = None,\n",
    "        **kwargs,\n",
    "    ):\n",
    "        assert result, \"Must provide a result object.\"\n",
    "\n",
    "        success = result.success\n",
    "        result = result.result\n",
    "\n",
    "        if result.exception_info[\"raised_exception\"]:\n",
    "            exception_message_template_str = (\n",
    "                \"\\n\\n$expectation_type raised an exception:\\n$exception_message\"\n",
    "            )\n",
    "\n",
    "            exception_message = RenderedStringTemplateContent(\n",
    "                **{\n",
    "                    \"content_block_type\": \"string_template\",\n",
    "                    \"string_template\": {\n",
    "                        \"template\": exception_message_template_str,\n",
    "                        \"params\": {\n",
    "                            \"expectation_type\": result.expectation_config.expectation_type,\n",
    "                            \"exception_message\": result.exception_info[\n",
    "                                \"exception_message\"\n",
    "                            ],\n",
    "                        },\n",
    "                        \"tag\": \"strong\",\n",
    "                        \"styling\": {\n",
    "                            \"classes\": [\"text-danger\"],\n",
    "                            \"params\": {\n",
    "                                \"exception_message\": {\"tag\": \"code\"},\n",
    "                                \"expectation_type\": {\n",
    "                                    \"classes\": [\"badge\", \"badge-danger\", \"mb-2\"]\n",
    "                                },\n",
    "                            },\n",
    "                        },\n",
    "                    },\n",
    "                }\n",
    "            )\n",
    "\n",
    "            exception_traceback_collapse = CollapseContent(\n",
    "                **{\n",
    "                    \"collapse_toggle_link\": \"Show exception traceback...\",\n",
    "                    \"collapse\": [\n",
    "                        RenderedStringTemplateContent(\n",
    "                            **{\n",
    "                                \"content_block_type\": \"string_template\",\n",
    "                                \"string_template\": {\n",
    "                                    \"template\": result.exception_info[\n",
    "                                        \"exception_traceback\"\n",
    "                                    ],\n",
    "                                    \"tag\": \"code\",\n",
    "                                },\n",
    "                            }\n",
    "                        )\n",
    "                    ],\n",
    "                }\n",
    "            )\n",
    "\n",
    "            return [exception_message, exception_traceback_collapse]\n",
    "\n",
    "        if success or not result_dict.get(\"unexpected_count\"):\n",
    "            return []\n",
    "        else:\n",
    "            unexpected_count = num_to_str(\n",
    "                result_dict[\"unexpected_count\"], use_locale=True, precision=20\n",
    "            )\n",
    "            unexpected_percent = (\n",
    "                num_to_str(result_dict[\"unexpected_percent\"], precision=4) + \"%\"\n",
    "            )\n",
    "            element_count = num_to_str(\n",
    "                result_dict[\"element_count\"], use_locale=True, precision=20\n",
    "            )\n",
    "\n",
    "            template_str = (\n",
    "                \"\\n\\n$unexpected_count unexpected values found. \"\n",
    "                \"$unexpected_percent of $element_count total rows.\"\n",
    "            )\n",
    "\n",
    "            return [\n",
    "                RenderedStringTemplateContent(\n",
    "                    **{\n",
    "                        \"content_block_type\": \"string_template\",\n",
    "                        \"string_template\": {\n",
    "                            \"template\": template_str,\n",
    "                            \"params\": {\n",
    "                                \"unexpected_count\": unexpected_count,\n",
    "                                \"unexpected_percent\": unexpected_percent,\n",
    "                                \"element_count\": element_count,\n",
    "                            },\n",
    "                            \"tag\": \"strong\",\n",
    "                            \"styling\": {\"classes\": [\"text-danger\"]},\n",
    "                        },\n",
    "                    }\n",
    "                )\n",
    "            ]\n",
    "\n",
    "    @renderer(renderer_type=\"renderer.diagnostic.unexpected_table\")\n",
    "    @render_evaluation_parameter_string\n",
    "    def _diagnostic_unexpected_table_renderer(\n",
    "        cls,\n",
    "        configuration: ExpectationConfiguration = None,\n",
    "        result: ExpectationValidationResult = None,\n",
    "        language: str = None,\n",
    "        runtime_configuration: dict = None,\n",
    "        **kwargs,\n",
    "    ):\n",
    "        try:\n",
    "            result_dict = result.result\n",
    "        except KeyError:\n",
    "            return None\n",
    "\n",
    "        if result_dict is None:\n",
    "            return None\n",
    "\n",
    "        if not result_dict.get(\"partial_unexpected_list\") and not result_dict.get(\n",
    "            \"partial_unexpected_counts\"\n",
    "        ):\n",
    "            return None\n",
    "\n",
    "        table_rows = []\n",
    "\n",
    "        if result_dict.get(\"partial_unexpected_counts\"):\n",
    "            total_count = 0\n",
    "            for unexpected_count_dict in result_dict.get(\"partial_unexpected_counts\"):\n",
    "                value = unexpected_count_dict.get(\"value\")\n",
    "                count = unexpected_count_dict.get(\"count\")\n",
    "                total_count += count\n",
    "                if value is not None and value != \"\":\n",
    "                    table_rows.append([value, count])\n",
    "                elif value == \"\":\n",
    "                    table_rows.append([\"EMPTY\", count])\n",
    "                else:\n",
    "                    table_rows.append([\"null\", count])\n",
    "\n",
    "            if total_count == result_dict.get(\"unexpected_count\"):\n",
    "                header_row = [\"Unexpected Value\", \"Count\"]\n",
    "            else:\n",
    "                header_row = [\"Sampled Unexpected Values\"]\n",
    "                table_rows = [[row[0]] for row in table_rows]\n",
    "\n",
    "        else:\n",
    "            header_row = [\"Sampled Unexpected Values\"]\n",
    "            sampled_values_set = set()\n",
    "            for unexpected_value in result_dict.get(\"partial_unexpected_list\"):\n",
    "                if unexpected_value:\n",
    "                    string_unexpected_value = str(unexpected_value)\n",
    "                elif unexpected_value == \"\":\n",
    "                    string_unexpected_value = \"EMPTY\"\n",
    "                else:\n",
    "                    string_unexpected_value = \"null\"\n",
    "                if string_unexpected_value not in sampled_values_set:\n",
    "                    table_rows.append([unexpected_value])\n",
    "                    sampled_values_set.add(string_unexpected_value)\n",
    "\n",
    "        unexpected_table_content_block = RenderedTableContent(\n",
    "            **{\n",
    "                \"content_block_type\": \"table\",\n",
    "                \"table\": table_rows,\n",
    "                \"header_row\": header_row,\n",
    "                \"styling\": {\n",
    "                    \"body\": {\"classes\": [\"table-bordered\", \"table-sm\", \"mt-3\"]}\n",
    "                },\n",
    "            }\n",
    "        )\n",
    "\n",
    "        return unexpected_table_content_block\n",
    "\n",
    "    # This dictionary contains metadata for display in the public gallery\n",
    "    library_metadata = {\n",
    "        \"tags\": [],\n",
    "        \"contributors\": [\"@NatanMish\"],\n",
    "    }\n",
    "\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    ExpectColumnZScoreLowerThan3().print_diagnostic_checklist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Running this module as is will run a series of checks to see whether the expectation can be used.\n",
    "!python expect_column_z_score_lower_than_3.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "from expect_column_z_score_lower_than_3 import ExpectColumnZScoreLowerThan3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "validator.expect_column_z_score_lower_than3(column=\"SalePrice\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "validator.expect_column_value_z_scores_to_be_less_than(\"SalePrice\", 3, double_sided=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "### Exercise 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "In this exercise we will create a custom expectation using the ColumnPairMapExpectation template. This template is useful for creating an expectation that compares values from two columns. The expectation we are going to create is \"Expect Proportional Floor SF(square feet) Ratio\". This expectation will pick up two columns - `1stFlrSF` and `2ndFlrSF` and validate that the 2nd floor is not more than twice larger than the first one.\n",
    "\n",
    "To build this custom expectation follow these steps:\n",
    "1. In line 37, insert a Pandas expression that returns a Series of booleans: True if the expectation holds, meaning the ratio between the 2nd floor SF to the 1st one is less than 2, and False otherwise.\n",
    "2. In lines 48-49, insert values for the two lists that are going to be used in the tests. col_a is for the 1st floor SF and col_b is for the 2nd floor SF. Notice there is one positive scenario and one negative scenario. The `mostly` success key is set at 0.6 for the succesful scenario and 1 for the failed scenario, which means that in your lists at least 60% of the pairs should be valid according to the logic. For example, two valid pairs and one non-valid.\n",
    "3. Run the rest of the cells and see that checks are passing and the results you get are as expected.\n",
    "\n",
    "To add line numbers in Google Colab select Tools -> Settings -> Editor -> show line numbers\n",
    "To add line numbers in Jupyter select view -> toggle line numbers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    },
    "tags": []
   },
   "source": [
    "**For the purpose of this exercise, column A is the 1st flor SF and column B is the 2nd floor SF.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "%%writefile expect_proportional_floor_sf_ratio.py\n",
    "from typing import Dict, Optional\n",
    "\n",
    "from great_expectations.core.expectation_configuration import ExpectationConfiguration\n",
    "from great_expectations.exceptions.exceptions import (\n",
    "    InvalidExpectationConfigurationError,\n",
    ")\n",
    "from great_expectations.execution_engine import (\n",
    "    ExecutionEngine,\n",
    "    PandasExecutionEngine,\n",
    "    SparkDFExecutionEngine,\n",
    "    SqlAlchemyExecutionEngine,\n",
    ")\n",
    "from great_expectations.expectations.expectation import (\n",
    "    ColumnPairMapExpectation,\n",
    "    ExpectationValidationResult,\n",
    ")\n",
    "from great_expectations.expectations.metrics.import_manager import F, sa\n",
    "from great_expectations.expectations.metrics.map_metric_provider import (\n",
    "    ColumnPairMapMetricProvider,\n",
    "    column_pair_condition_partial,\n",
    ")\n",
    "from great_expectations.validator.metric_configuration import MetricConfiguration\n",
    "\n",
    "\n",
    "class ColumnFloorsSquareFeetComparison(ColumnPairMapMetricProvider):\n",
    "    \"\"\"MetricProvider Class for columns floors square feet comparison\"\"\"\n",
    "    condition_metric_name = \"column_pair_values.floors_square_feet_ratio\"\n",
    "    condition_domain_keys = (\n",
    "        \"column_A\",\n",
    "        \"column_B\",\n",
    "    )\n",
    "    condition_value_keys = ()\n",
    "    @column_pair_condition_partial(engine=PandasExecutionEngine)\n",
    "    def _pandas(cls, column_A, column_B, **kwargs):\n",
    "        # This methold should return a Pandas series of booleans\n",
    "        return <YOUR PANDAS EXPRESSION HERE>\n",
    "\n",
    "\n",
    "class ExpectProportionalFloorDifference(ColumnPairMapExpectation):\n",
    "    \"\"\"Expect house 2nd floor to be no more than twice larger than the 1st floor\"\"\"\n",
    "    map_metric = \"column_pair_values.floors_square_feet_ratio\"\n",
    "    # These examples will be shown in the public gallery.\n",
    "    # They will also be executed as unit tests for your Expectation.\n",
    "    examples = [\n",
    "        {\n",
    "            \"data\": {\n",
    "                \"col_a\": [<test values for 1st floor SF>],\n",
    "                \"col_b\": [<test values for 2nd floor SF>],\n",
    "            },\n",
    "            \"tests\": [\n",
    "                {\n",
    "                    \"title\": \"basic_positive_test\",\n",
    "                    \"exact_match_out\": False,\n",
    "                    \"include_in_gallery\": True,\n",
    "                    \"in\": {\"column_A\": \"col_a\", \"column_B\": \"col_b\", \"mostly\": 0.6},\n",
    "                    \"out\": {\n",
    "                        \"success\": True,\n",
    "                    },\n",
    "                },\n",
    "                {\n",
    "                    \"title\": \"basic_negative_test\",\n",
    "                    \"exact_match_out\": False,\n",
    "                    \"include_in_gallery\": True,\n",
    "                    \"in\": {\"column_A\": \"col_a\", \"column_B\": \"col_b\", \"mostly\": 1},\n",
    "                    \"out\": {\n",
    "                        \"success\": False,\n",
    "                    },\n",
    "                },\n",
    "            ],\n",
    "        }\n",
    "    ]\n",
    "    # Setting necessary computation metric dependencies and defining kwargs, as well as assigning kwargs default values\n",
    "    success_keys = (\n",
    "        \"column_A\",\n",
    "        \"column_B\",\n",
    "        \"mostly\",\n",
    "    )\n",
    "\n",
    "    default_kwarg_values = {\n",
    "        \"row_condition\": None,\n",
    "        \"condition_parser\": None,  # we expect this to be explicitly set whenever a row_condition is passed\n",
    "        \"mostly\": 1.0,\n",
    "        \"result_format\": \"COMPLETE\",\n",
    "        \"include_config\": True,\n",
    "        \"catch_exceptions\": False,\n",
    "    }\n",
    "    args_keys = (\n",
    "        \"column_A\",\n",
    "        \"column_B\",\n",
    "    )\n",
    "\n",
    "    def validate_configuration(\n",
    "        self, configuration: Optional[ExpectationConfiguration]\n",
    "    ) -> None:\n",
    "        super().validate_configuration(configuration)\n",
    "        if configuration is None:\n",
    "            configuration = self.configuration\n",
    "        try:\n",
    "            assert (\n",
    "                \"column_A\" in configuration.kwargs\n",
    "                and \"column_B\" in configuration.kwargs\n",
    "            ), \"both columns must be provided\"\n",
    "        except AssertionError as e:\n",
    "            raise InvalidExpectationConfigurationError(str(e))\n",
    "\n",
    "    # This dictionary contains metadata for display in the public gallery\n",
    "    library_metadata = {\n",
    "        \"tags\": [],\n",
    "        \"contributors\": [\"<YOUR GITHUB USERNAME HERE>\"],\n",
    "    }\n",
    "\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    ExpectProportionalFloorDifference().print_diagnostic_checklist()\n",
    "# Note to users: code below this line is only for integration testing -- ignore!\n",
    "\n",
    "diagnostics = ExpectProportionalFloorDifference().run_diagnostics()\n",
    "\n",
    "for check in diagnostics[\"tests\"]:\n",
    "    assert check[\"test_passed\"] is True\n",
    "    assert check[\"error_diagnostics\"] is None\n",
    "\n",
    "for check in diagnostics[\"errors\"]:\n",
    "    assert check is None\n",
    "\n",
    "for check in diagnostics[\"maturity_checklist\"][\"experimental\"]:\n",
    "    if check[\"message\"] == \"Passes all linting checks\":\n",
    "        continue\n",
    "    assert check[\"passed\"] is True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Check that the module was configured correctly\n",
    "!python expect_proportional_floor_sf_ratio.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "from expect_proportional_floor_sf_ratio import ExpectProportionalFloorDifference"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Run the expectation using the validator instance, you should get one unexpected value in the results\n",
    "validator.expect_proportional_floor_difference(\"1stFlrSF\", \"2ndFlrSF\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "*Exercise solutions can be found in the exercise solutions file in the current directory.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.9"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}