{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# TDDA: Test-Driven Data Analysis\n", "\n", "[TDDA](https://github.com/tdda/tdda) uses file inputs (such as NumPy arrays or Pandas DataFrames) and a set of constraints that are stored as a JSON file.\n", "\n", "* [Reference Tests](https://tdda.readthedocs.io/en/latest/referencetest.html) supports the creation of reference tests based on either unittest or pytest.\n", "* [Constraints](https://tdda.readthedocs.io/en/tdda-1.0.13/constraints.html) is used to retrieve constraints from a (pandas) DataFrame, write them out as JSON and check whether records satisfy the constraints in the constraints file. It also supports tables in a variety of relational databases.\n", "* [Rexpy](https://tdda.readthedocs.io/en/v1.0.30/rexpy.html) is a tool for automatically deriving regular expressions from a column in a pandas DataFrame or from a (Python) list of examples." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 1. Imports" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import numpy as np\n", "import pandas as pd\n", "\n", "from tdda.constraints import discover_df, verify_df" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "df = pd.read_csv(\n", " \"https://raw.githubusercontent.com/kjam/data-cleaning-101/master/data/iot_example.csv\"\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 2. Check data\n", "\n", "With [pandas.DataFrame.sample](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.sample.html) we display ten random data sets:" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
timestampusernametemperatureheartratebuildlatestnote
1347492017-02-24T08:10:53michellereed117724e04514-95c1-823b-1559-eda8a3eb7b350update
533012017-01-22T19:17:53donaldjohnson2884256c0202-0696-6ecf-5979-13d1886a31200user
791452017-02-02T03:45:05derek5325638b03fa9d-5992-22f6-9977-b668b3ffc3c61test
771112017-02-01T07:59:34jason722484487965ae-c546-eef9-43ef-c09d4780dfdc0user
994932017-02-10T06:38:47tonyahopkins187430287b8f-afbb-e2e8-7e8a-4ce35191fdaa0sleep
743752017-01-31T05:37:53wholmes247552428d30-6f5d-2c95-b6a9-82f5a587d47a0interval
1441992017-02-28T03:01:21velazquezchristina227191b3a39a-26cc-1a22-9d92-946d6bbe8b120interval
507572017-01-21T18:52:32sdonovan5771b18d840-dc92-3f8b-85a1-8904a00c697c0test
1277982017-02-21T13:21:39mossdavid1462b80db398-98d4-061a-d29a-284dac7edc900interval
960682017-02-08T21:53:09veronicaanderson1778c344b7b5-b032-757b-21e2-cc9762fcd6d51test
\n", "
" ], "text/plain": [ " timestamp username temperature heartrate \\\n", "134749 2017-02-24T08:10:53 michellereed 11 77 \n", "53301 2017-01-22T19:17:53 donaldjohnson 28 84 \n", "79145 2017-02-02T03:45:05 derek53 25 63 \n", "77111 2017-02-01T07:59:34 jason72 24 84 \n", "99493 2017-02-10T06:38:47 tonyahopkins 18 74 \n", "74375 2017-01-31T05:37:53 wholmes 24 75 \n", "144199 2017-02-28T03:01:21 velazquezchristina 22 71 \n", "50757 2017-01-21T18:52:32 sdonovan 5 77 \n", "127798 2017-02-21T13:21:39 mossdavid 14 62 \n", "96068 2017-02-08T21:53:09 veronicaanderson 17 78 \n", "\n", " build latest note \n", "134749 24e04514-95c1-823b-1559-eda8a3eb7b35 0 update \n", "53301 256c0202-0696-6ecf-5979-13d1886a3120 0 user \n", "79145 8b03fa9d-5992-22f6-9977-b668b3ffc3c6 1 test \n", "77111 487965ae-c546-eef9-43ef-c09d4780dfdc 0 user \n", "99493 30287b8f-afbb-e2e8-7e8a-4ce35191fdaa 0 sleep \n", "74375 52428d30-6f5d-2c95-b6a9-82f5a587d47a 0 interval \n", "144199 91b3a39a-26cc-1a22-9d92-946d6bbe8b12 0 interval \n", "50757 1b18d840-dc92-3f8b-85a1-8904a00c697c 0 test \n", "127798 b80db398-98d4-061a-d29a-284dac7edc90 0 interval \n", "96068 c344b7b5-b032-757b-21e2-cc9762fcd6d5 1 test " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.sample(10)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "And with [pandas.DataFrame.dtypes](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.dtypes.html) we display the data types for the individual columns:" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "timestamp object\n", "username object\n", "temperature int64\n", "heartrate int64\n", "build object\n", "latest int64\n", "note object\n", "dtype: object" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.dtypes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 3. Creating a constraints object\n", "\n", "With `discover_constraints` a constraints object can be created." ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "constraints = discover_df(df)" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "constraints" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Fields([('timestamp', ),\n", " ('username', ),\n", " ('temperature',\n", " ),\n", " ('heartrate', ),\n", " ('build', ),\n", " ('latest', ),\n", " ('note', )])" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "constraints.fields" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4. Writing the constraints into a file" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "with open(\"../../data/ignore-iot_constraints.tdda\", \"w\") as f:\n", " f.write(constraints.to_json())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If we take a closer look at the file, we can see that, for example, a string with 19 characters is expected for the `timestamp` column and `temperature` expects integers with values from 5-29." ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "{\n", " \"creation_metadata\": {\n", " \"local_time\": \"2024-11-02T19:34:53\",\n", " \"utc_time\": \"2024-11-02T18:34:53\",\n", " \"creator\": \"TDDA 2.2.05\",\n", " \"host\": \"fay.local\",\n", " \"user\": \"veit\",\n", " \"n_records\": 146397,\n", " \"n_selected\": 146397\n", " },\n", " \"fields\": {\n", " \"timestamp\": {\n", " \"type\": \"string\",\n", " \"min_length\": 19,\n", " \"max_length\": 19,\n", " \"max_nulls\": 0,\n", " \"no_duplicates\": true\n", " },\n", " \"username\": {\n", " \"type\": \"string\",\n", " \"min_length\": 3,\n", " \"max_length\": 21,\n", " \"max_nulls\": 0\n", " },\n", " \"temperature\": {\n", " \"type\": \"int\",\n", " \"min\": 5,\n", " \"max\": 29,\n", " \"sign\": \"positive\",\n", " \"max_nulls\": 0\n", " },\n", " \"heartrate\": {\n", " \"type\": \"int\",\n", " \"min\": 60,\n", " \"max\": 89,\n", " \"sign\": \"positive\",\n", " \"max_nulls\": 0\n", " },\n", " \"build\": {\n", " \"type\": \"string\",\n", " \"min_length\": 36,\n", " \"max_length\": 36,\n", " \"max_nulls\": 0,\n", " \"no_duplicates\": true\n", " },\n", " \"latest\": {\n", " \"type\": \"int\",\n", " \"min\": 0,\n", " \"max\": 1,\n", " \"sign\": \"non-negative\",\n", " \"max_nulls\": 0\n", " },\n", " \"note\": {\n", " \"type\": \"string\",\n", " \"min_length\": 4,\n", " \"max_length\": 8,\n", " \"allowed_values\": [\n", " \"interval\",\n", " \"sleep\",\n", " \"test\",\n", " \"update\",\n", " \"user\",\n", " \"wake\"\n", " ]\n", " }\n", " }\n", "}\n" ] } ], "source": [ "!cat ../../data/ignore-iot_constraints.tdda" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 5. Checking data frames\n", "\n", "To do this, we first read in a new csv file with pandas and then have ten data records output as examples:" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
timestampusernametemperatureheartratebuildlatestnote
1177672017-02-17T13:18:01flynnkimberlyNaN80NaN0.0NaN
857872017-02-04T19:29:23djohnsNaN67e2af7ab7-938e-57e7-975d-441f84052abe1.0NaN
1283822017-02-21T18:59:14bradley7126.08486e34cba-6af2-5ae6-bb43-5f58839ddfc41.0NaN
1261892017-02-20T21:55:00vprice12.064780420ce-b902-fe52-d9f8-a46daa269dccNaNinterval
1073272017-02-13T09:35:13rachelrobinson27.0819c9cbbb2-22d1-29cf-c08e-ea5014fa22c71.0wake
1193622017-02-18T04:40:23diana5420.06112b0bff3-7598-37f9-a4d9-79a6f58756a80.0test
809932017-02-02T21:30:02tamarafrost16.085NaN1.0wake
460222017-01-19T21:31:10usellersNaN7898e08b68-6fe7-8345-a583-658516f4001b0.0NaN
1325682017-02-23T11:17:09jeffreyrodgersNaN64NaNNaNinterval
1136762017-02-15T22:17:25mcculloughmichelle7.077caf5d3ca-734f-6683-0d55-24c07d0c9e330.0test
\n", "
" ], "text/plain": [ " timestamp username temperature heartrate \\\n", "117767 2017-02-17T13:18:01 flynnkimberly NaN 80 \n", "85787 2017-02-04T19:29:23 djohns NaN 67 \n", "128382 2017-02-21T18:59:14 bradley71 26.0 84 \n", "126189 2017-02-20T21:55:00 vprice 12.0 64 \n", "107327 2017-02-13T09:35:13 rachelrobinson 27.0 81 \n", "119362 2017-02-18T04:40:23 diana54 20.0 61 \n", "80993 2017-02-02T21:30:02 tamarafrost 16.0 85 \n", "46022 2017-01-19T21:31:10 usellers NaN 78 \n", "132568 2017-02-23T11:17:09 jeffreyrodgers NaN 64 \n", "113676 2017-02-15T22:17:25 mcculloughmichelle 7.0 77 \n", "\n", " build latest note \n", "117767 NaN 0.0 NaN \n", "85787 e2af7ab7-938e-57e7-975d-441f84052abe 1.0 NaN \n", "128382 86e34cba-6af2-5ae6-bb43-5f58839ddfc4 1.0 NaN \n", "126189 780420ce-b902-fe52-d9f8-a46daa269dcc NaN interval \n", "107327 9c9cbbb2-22d1-29cf-c08e-ea5014fa22c7 1.0 wake \n", "119362 12b0bff3-7598-37f9-a4d9-79a6f58756a8 0.0 test \n", "80993 NaN 1.0 wake \n", "46022 98e08b68-6fe7-8345-a583-658516f4001b 0.0 NaN \n", "132568 NaN NaN interval \n", "113676 caf5d3ca-734f-6683-0d55-24c07d0c9e33 0.0 test " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "new_df = pd.read_csv(\n", " \"https://raw.githubusercontent.com/kjam/data-cleaning-101/master/data/iot_example_with_nulls.csv\"\n", ")\n", "new_df.sample(10)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We see several fields that are output as `NaN`. Now, to analyse this systematically, we apply [verify_df](https://tdda.readthedocs.io/en/v1.0.31/constraints.html#tdda.constraints.verify_df) to our new DataFrame. Here, `passes` returns the number of passed constraints, and `failures` returns the number of failed constraints." ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "v = verify_df(new_df, '../../data/ignore-iot_constraints.tdda')" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "v" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "30" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "v.passes" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "3" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "v.failures" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can also display which constraints passed and failed in which columns:" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "FIELDS:\n", "\n", "timestamp: 0 failures 5 passes type ✓ min_length ✓ max_length ✓ max_nulls ✓ no_duplicates ✓\n", "\n", "username: 0 failures 4 passes type ✓ min_length ✓ max_length ✓ max_nulls ✓\n", "\n", "temperature: 1 failure 4 passes type ✓ min ✓ max ✓ sign ✓ max_nulls ✗\n", "\n", "heartrate: 0 failures 5 passes type ✓ min ✓ max ✓ sign ✓ max_nulls ✓\n", "\n", "build: 1 failure 4 passes type ✓ min_length ✓ max_length ✓ max_nulls ✗ no_duplicates ✓\n", "\n", "latest: 1 failure 4 passes type ✓ min ✓ max ✓ sign ✓ max_nulls ✗\n", "\n", "note: 0 failures 4 passes type ✓ min_length ✓ max_length ✓ allowed_values ✓\n", "\n", "SUMMARY:\n", "\n", "Constraints passing: 30\n", "Constraints failing: 3\n" ] } ], "source": [ "print(str(v))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Alternatively, we can also display these results in tabular form:" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
fieldfailurespassestypeminmin_lengthmaxmax_lengthsignmax_nullsno_duplicatesallowed_values
0timestamp05TrueNaNTrueNaNTrueNaNTrueTrueNaN
1username04TrueNaNTrueNaNTrueNaNTrueNaNNaN
2temperature14TrueTrueNaNTrueNaNTrueFalseNaNNaN
3heartrate05TrueTrueNaNTrueNaNTrueTrueNaNNaN
4build14TrueNaNTrueNaNTrueNaNFalseTrueNaN
5latest14TrueTrueNaNTrueNaNTrueFalseNaNNaN
6note04TrueNaNTrueNaNTrueNaNNaNNaNTrue
\n", "
" ], "text/plain": [ " field failures passes type min min_length max max_length \\\n", "0 timestamp 0 5 True NaN True NaN True \n", "1 username 0 4 True NaN True NaN True \n", "2 temperature 1 4 True True NaN True NaN \n", "3 heartrate 0 5 True True NaN True NaN \n", "4 build 1 4 True NaN True NaN True \n", "5 latest 1 4 True True NaN True NaN \n", "6 note 0 4 True NaN True NaN True \n", "\n", " sign max_nulls no_duplicates allowed_values \n", "0 NaN True True NaN \n", "1 NaN True NaN NaN \n", "2 True False NaN NaN \n", "3 True True NaN NaN \n", "4 NaN False True NaN \n", "5 True False NaN NaN \n", "6 NaN NaN NaN True " ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "v.to_frame()" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3.13 Kernel", "language": "python", "name": "python313" }, "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.13.0" }, "latex_envs": { "LaTeX_envs_menu_present": true, "autoclose": false, "autocomplete": true, "bibliofile": "biblio.bib", "cite_by": "apalike", "current_citInitial": 1, "eqLabelWithNumbers": true, "eqNumInitial": 1, "hotkeys": { "equation": "Ctrl-E", "itemize": "Ctrl-I" }, "labels_anchors": false, "latex_user_defs": false, "report_style_numbering": false, "user_envs_cfg": false }, "widgets": { "application/vnd.jupyter.widget-state+json": { "state": {}, "version_major": 2, "version_minor": 0 } } }, "nbformat": 4, "nbformat_minor": 4 }