Lesson 2 - Validating restructured data against a schema.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# _Lesson 2_: Validating restructured data against a schema using a spreadsheet\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">\n",
    "    <b>Learning outcomes:</b>\n",
    "    <br>\n",
    "    <ul>\n",
    "        <li>Understand, and employ, standard definitions to write a JSON schema for data validation.</li>\n",
    "        <li>Perform data validation using Microsoft Excel.</li>\n",
    "        <li>Learn how to validate machine-readable data in online applications against a defined schema.</li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.1 Creating a JSON schema\n",
    "\n",
    "When you produced your machine-readable file in Lesson 1, you came up with your own approach to how to structure the header row and the data. You named the columns yourself, and decided on how many, and what data should be in them. You did so by reviewing the source data.\n",
    "\n",
    "However, there are problems with that approach:\n",
    "\n",
    "- How will you maintain consistency between updates? \n",
    "- How will different people, working on similar data or consecutive updates, ensure consistency between themselves?\n",
    "- How will you know if the restructured file you've created is actually machine-readable?\n",
    "\n",
    "If a programmer wanted to build an application - say an app that helps people find the nearest bus-stop - and you change update structure from release to release, they will not be able to automate their program, and that may prevent them from building it at all.\n",
    "\n",
    "The first step in the data lifecycle isn't simply heading into your data and restructuring it. First you need to define a `schema`.\n",
    "\n",
    "![Data lifecycle](images/data-lifecycle-en.jpg)\n",
    "\n",
    "Your `schema` defines the structural metadata for your wrangling process. This is not the format your input data arrive in, but it is what you require it to look like when you're done.\n",
    "\n",
    "The standard terms used are defined by [JSON Schema](https://json-schema.org/learn/) and further refined for use in structuring CSV data in Frictionless Data's [Table Schema](https://specs.frictionlessdata.io/table-schema/).\n",
    "\n",
    "In simple terms, you need to specify the columns which an input CSV or Excel-file will be restructured into. The new columns are defined by the fields in your schema. These target fields are likely to be those in your database, or in your analytical software. Until your input data conform to this structure, your data will not validate.\n",
    "\n",
    "### 2.1.1 Minimum valid requirements\n",
    "\n",
    "A minimum valid schema requires a `name` to identify the schema, and a single, minimally-valid `field` containing a `name` and `type`:\n",
    "\n",
    "    {\n",
    "        \"name\": \"A simple name\",\n",
    "        \"fields\": [\n",
    "            {\n",
    "                \"name\": \"Field name, e.g. 'column_name'\",\n",
    "                \"type\": \"Valid data type, e.g. 'string', 'number'\"\n",
    "                }\n",
    "            }\n",
    "        ]\n",
    "    }\n",
    "\n",
    "Everything else is optional, unless specifically required by that field-type.\n",
    "\n",
    "A JSON schema looks similar to a Python dictionary, but be very careful with `,` line-endings. The last item in a list must NOT have a comma. \n",
    "\n",
    "> You can think of a schema `field` as a `column` in a table, or a `field` in a database. Fields have a `type`, such as integer or text.\n",
    "\n",
    "The `fields` value is a list, or - in JSON terminology - an `array` of dictionary `objects`. Each field, unsurprisingly, has a `name`, `title` and `description`, of which only the `name` is required. \n",
    "\n",
    "### 2.1.2 Types\n",
    "\n",
    "Fields also have a `type`. This describes the data expected and limits the actions which can be performed during the wrangling process:\n",
    "\n",
    "* `string`: any text-based string.\n",
    "* `number`: any number-based value, including integers and floats.\n",
    "* `integer`: any integer-based value.\n",
    "* `boolean`: a boolean [`true`, `false`] value.\n",
    "* `object`: any valid JSON data.\n",
    "* `array`: any valid array-based data.\n",
    "* `date`: any date without a time. Must be in ISO8601 format, `YYYY-MM-DD`.\n",
    "* `datetime`: any date with a time. Must be in ISO8601 format, with UTC time specified (optionally) as `YYYY-MM-DD hh:mm:ss Zz`.\n",
    "* `year`: any year, formatted as `YYYY`.\n",
    "\n",
    "There are more [types and formats](https://specs.frictionlessdata.io/table-schema/#types-and-formats) like `geojson`, `geopoints` and variations on dates.\n",
    "\n",
    "### 2.1.3 Constraints\n",
    "\n",
    "In addition, these data can be `constrained`:\n",
    "\n",
    "* `required`: boolean, indicates whether this field is compulsory (but blank values in the input column are permitted and will be set to the `missing` default)\n",
    "* `unique`: boolean, if `true` then all values for that input column must be unique\n",
    "* `minimum`: `integer` / `number`, as appropriate defining min number of characters in a string, or the min values of numbers or integers\n",
    "* `maximum`: `integer` / `number`, as appropriate defining max number of characters in a string, or the max values of numbers or integers\n",
    "* `enum`: a list (or array) of unique terms permitted in this field\n",
    "\n",
    "As an example:\n",
    "\n",
    "    {\n",
    "        \"name\": \"A simple name\",\n",
    "        \"fields\": [\n",
    "            {\n",
    "                \"name\": \"Field name, e.g. 'column_name'\",\n",
    "                \"type\": \"Valid data type, e.g. 'string', 'number'\",\n",
    "                \"constraints\": {\n",
    "                    \"enum\": [\"cheddar\", \"gouda\", \"other\"]\n",
    "                }\n",
    "            }\n",
    "        ]\n",
    "    }\n",
    "\n",
    "Again, there are other [constraints](https://specs.frictionlessdata.io/table-schema/#constraints), such as `pattern`, `maxLength`, `minLength` you can use as well.\n",
    "\n",
    "### 2.1.4 Other properties\n",
    "\n",
    "There are also special properties you can add to your schema that are not part of the `fields` definitions:\n",
    "\n",
    "* `missingValues`: defines which terms in your data should be treated as missing values, e.g. `-`, `NaN`, `..`, etc. This must be presented as a list, with terms defined as strings, e.g. `[\"NaN\", \"..\"]`\n",
    "\n",
    "### 2.1.5 Example schema\n",
    "\n",
    "As an example, let's imagine we want our destination data to conform to the following structure:\n",
    "\n",
    "| la_code | ba_ref | occupant_name | postcode | occupation_state | occupation_state_date | prop_ba_rates | occupation_state_reliefs |\n",
    "|---------|--------|---------------|----------|------------------|-----------------------|---------------|-------------------------|\n",
    "| E06000044 | 177500080710 | A company | PO5 2SE | True | 2019-04-01 | 98530 | [small_business, retail] |\n",
    "\n",
    "The complete schema for this example is then:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "{\n",
    "    \"name\": \"test_schema\",\n",
    "    \"title\": \"Test Schema\",\n",
    "    \"description\": \"A test Schema\",\n",
    "    \"fields\": [\n",
    "        {\n",
    "            \"name\": \"la_code\",\n",
    "            \"title\": \"Local authority code\",\n",
    "            \"type\": \"string\",\n",
    "            \"description\": \"Standard code for local authority.\"\n",
    "        },\n",
    "        {\n",
    "            \"name\": \"ba_ref\",\n",
    "            \"title\": \"Billing reference\",\n",
    "            \"type\": \"string\",\n",
    "            \"description\": \"Unique code for a specific hereditament. May be multiple rows for history.\"\n",
    "        },\n",
    "        {\n",
    "            \"name\": \"prop_ba_rates\",\n",
    "            \"title\": \"Property billing rates\",\n",
    "            \"type\": \"number\",\n",
    "            \"description\": \"Actual rates paid by a specific ratepayer.\"\n",
    "        },\n",
    "        {\n",
    "            \"name\": \"occupant_name\",\n",
    "            \"title\": \"Occupier name\",\n",
    "            \"type\": \"string\",\n",
    "            \"description\": \"Name of the ratepayer.\"\n",
    "        },\n",
    "        {\n",
    "            \"name\": \"postcode\",\n",
    "            \"title\": \"Postcode\",\n",
    "            \"type\": \"string\",\n",
    "            \"description\": \"Full address or postcode of ratepayer.\"\n",
    "        },\n",
    "        {\n",
    "            \"name\": \"occupation_state\",\n",
    "            \"title\": \"Occupation state\",\n",
    "            \"type\": \"boolean\",\n",
    "            \"description\": \"Occupation status, void or occupied.\"\n",
    "        },\n",
    "        {\n",
    "            \"name\": \"occupation_state_date\",\n",
    "            \"title\": \"Date of occupation state\",\n",
    "            \"type\": \"date\",\n",
    "            \"description\": \"Date of the start of status in occupation_state.\"\n",
    "        },\n",
    "        {\n",
    "            \"name\": \"occupation_state_reliefs\",\n",
    "            \"title\": \"Occupation state reliefs\",\n",
    "            \"type\": \"array\",\n",
    "            \"description\": \"Array of the categories of reliefs / exemptions applied.\",\n",
    "            \"constraints\": {\n",
    "                \"enum\": [\n",
    "                    \"small_business\", \n",
    "                    \"rural\", \n",
    "                    \"charity\", \n",
    "                    \"enterprise_zone\", \n",
    "                    \"vacancy\", \n",
    "                    \"hardship\",\n",
    "                    \"retail\", \n",
    "                    \"discretionary\", \n",
    "                    \"exempt\", \n",
    "                    \"transitional\", \n",
    "                    \"other\"\n",
    "                ]\n",
    "            }\n",
    "        }\n",
    "    ],\n",
    "    \"missingValues\": [\"NaN\", \"..\"]\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Save__ your JSON schema in any ordinary text editor (__NOT__ Word or anything that'll add XML to it) as a `.json` file (e.g. `my_schema.json`).\n",
    "\n",
    "A really good text editor is [Notepad++](https://notepad-plus-plus.org/downloads/). Use this to create and save your JSON.\n",
    "\n",
    "Now your turn:\n",
    "\n",
    "<div class=\"alert alert-block alert-success\">\n",
    "    <p><b>Exercise:</b></p>\n",
    "    <p>Using the restructured file you created in Lesson 1.1 create a JSON Schema. Remember that each <code>field</code> <code>name</code> must be the same as the column header name. Define the field <code>type</code> and - where you have them - any <code>missingValues</code>.</p>\n",
    "</div>\n",
    "\n",
    "In the next section, you'll learn how to specify the value types in a spreadsheet.\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <b>References:</b>\n",
    "    <br>\n",
    "    <ul>\n",
    "        <li><a href=\"https://json-schema.org/learn/\">JSON Schema</a></li>\n",
    "        <li><a href=\"https://specs.frictionlessdata.io/table-schema/\">Frictionless Data Table Schema</a></li>\n",
    "        <li><a href=\"https://notepad-plus-plus.org/downloads/\">Notepad++</a></li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.2 Methods for data anonymisation\n",
    "\n",
    "There are a wide range of techniques available to support anonymisation. Broadly, though, they fit into two types:\n",
    "\n",
    "- __Redaction__: in which we remove fields or line-item information while maintaining sufficient integrity to permit semantic analysis;\n",
    "- __Aggregation__: in which we deliberately aggregate data to ensure outlier anonymity;\n",
    "\n",
    "### 2.2.1 Redaction methods\n",
    "\n",
    "Before we start doing anything, we need to understand our dataset, and understand how we intend to redact it _while maintaining its internal integrity so that we can continue to conduct analysis_.\n",
    "\n",
    "#### Attribute suppression\n",
    "\n",
    "An `attribute` is also known as a `field`. This method requires that we delete an entire field. It is one of the first, and easiest, steps we can take.\n",
    "\n",
    "- Remove data we do not need\n",
    "- Remove data we cannot easily redact\n",
    "\n",
    "This is a destructive step since suppression deletes the original data.\n",
    "\n",
    "#### Record suppression\n",
    "\n",
    "Some data are outliers; sufficiently rare that - in and of itself - these data can't be anonymised. With record suppression we remove all data related to these individuals. However, tread carefully.\n",
    "\n",
    "Outliers may be of significant interest if their status is part of the study. If a person's illness is unusual for the area where they live, for their ethnicity, gender or sexual orientation, then that would make them an outlier. However, that would also be important for understanding the disease.\n",
    "\n",
    "On the other hand, if their location, ethnicity, gender or sexual orientation have no bearing on the disease, then these could be safely redacted.\n",
    "\n",
    "#### Pseudonymisation\n",
    "\n",
    "Pseudonymisation is the replacement of identifying data with randomised values. This can be reversable, if you create a key between the data and the generated values, but irreversable if you deliberately throw aways the keys. Persistent pseudonyms support linkage between the same individual across different datasets.\n",
    "\n",
    "- `strings`: pseudonymise through replacement;\n",
    "\n",
    "#### Generalisation\n",
    "\n",
    "Generalisation is a deliberate reduction in the precision of data, such as converting a person's age into a range, or a precise location into a less precise location.\n",
    "\n",
    "- `range`: conversion of precise numbers into quantiles or statistical ranges;\n",
    "- `cluster`: aggregation of geospatial data into statistically less significant clusters - this can also be used to mask outliers;\n",
    "\n",
    "Design the data ranges with appropriate sizes. Sometimes quantiles are the most appropriate, sometimes we use statistical definitions (such as geospatial ranges that are designed to include sufficient numbers of people so as to reduce deanonymisation).\n",
    "\n",
    "#### Shuffling\n",
    "\n",
    "Shuffling is where data are rearranged such that the individual attribute values are still represented in the dataset, but generally, do not correspond to the original records. This is not appropriate for all data. Swapping diseases amongst different patients will certainly render the data anonymous, but will also confuse any epidemiological analysis.\n",
    "\n",
    "#### Data Perturbation\n",
    "\n",
    "Perturbation involves adding random noise to data to \"blur\" it. This can include rounding, shifting dates, or adding geospatial displacement (jitter) to coordinate data.  This means artificially moving the precision within a small range to obscure the exact details of the person.\n",
    "\n",
    "- `dates`: shift exact dates by days or months;\n",
    "- `rounding`: round off to the nearest decile or whole number, depending on the precision of the data;\n",
    "- `coordinates`: perturb the data through geospatial displacement (jitter);\n",
    "\n",
    "Care must be taken not to add too little or too much perturbation.\n",
    "\n",
    "### 2.2.2 Aggregation methods\n",
    "\n",
    "Aggregation is far more destructive than is redaction. We will lose resolution on patient morphology, and we will lose the direct relationships between data in exchange for summaries of that data. But we will gain security for the individuals concerned.\n",
    "\n",
    "Where redaction is guided by the data almost exclusively, aggregation is guided by the research objectives for the data. Any form of aggregation will limit what can be done and awareness of these limitations is critical.\n",
    "\n",
    "Census data are usually aggregated in this way, with the individual microdata (responses from each household) only made available to accredited researchers, while the aggregated versions are made available to the public.\n",
    "\n",
    "Our objective will be to create groups of data and then perform aggregations on each group. The range of aggregations we can form include:\n",
    "\n",
    "- `count`: count of the individual members of the group;\n",
    "- `totals`: sums of values, and sums of sub-groups within the values (e.g. total duration of illness, and duration of each type of illness);\n",
    "- `averages`: including `mean`, `median` and `mode` of data sequences;\n",
    "- `distributions`: including `quantiles`, `normals` or other types of distribution.\n",
    "\n",
    "The groups can be by specific `categories` or `geospatial` ranges.\n",
    "\n",
    "In many ways, an entire course of statistics is required to perform aggregations well.\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">\n",
    "    <p><b>Aggregations require familiarity and experience with the data being aggregated.</b> It's very difficult to simply pick up a random dataset and know how to aggregate it\n",
    "        in a way that supports analysis and extracts meaning from it. You are unlikely to be responsible for aggregating data you don't have experience with, and when you have that\n",
    "        experience, knowing how to aggregate it will become clearer.</p>\n",
    "</div>\n",
    "\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3 Apply data validation to cells in a spreadsheet\n",
    "\n",
    "Your `types` - at this stage - are only a guide. You will have no feedback, or error messages like you get when running Python code, if any of the data types in your field columns are wrong. There are a few ways to get that feedback so you can correct things, but we'll start with data validation in spreadsheet cells.\n",
    "\n",
    "The following is adapted from a [Microsoft Office tutorial](https://support.office.com/en-gb/article/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249). This approach will work in OpenOffice as well as Google Sheets, although the specific steps are different.\n",
    "\n",
    "Microsoft has an example file you can [download](http://download.microsoft.com/download/9/6/8/968A9140-2E13-4FDC-B62C-C1D98D2B0FE6/Data%20Validation%20Examples.xlsx).\n",
    "\n",
    "### 2.3.1 Specify validation for data types\n",
    "\n",
    "The process is straightforward:\n",
    "\n",
    "1. Select the cells in a specific column you wish to limit by type\n",
    "2. Select __Data > Data Tools > Data Validation__.\n",
    "\n",
    "  ![Excel data validation](images/excel-data-validation.png)\n",
    "\n",
    "3. On the __Settings__ tab, under __Allow__, select one of:\n",
    "\n",
    "  ![Validation settings](images/excel-validation-settings.jpg)\n",
    " \n",
    "  - __Whole Number__: restrict the cell to accept only `integer` values.\n",
    "  - __Decimal__: restrict the cell to accept only `float` or `number` values.\n",
    "  - __List__: pick data from a drop-down list, and limited by values constrained by `enum`.\n",
    "  - __Date__: restrict the cell to accept only `date`.\n",
    "  - __Time__: restrict the cell to accept only `datetime`.\n",
    "  - __Text Length__: restrict the length of the text, equivalent to constraints `maxLength`.\n",
    "  - __Custom__: for custom formula.\n",
    " \n",
    "4. Under __Data__, you can select a condition:\n",
    "\n",
    "  - between\n",
    "  - not between\n",
    "  - equal to\n",
    "  - not equal to\n",
    "  - greater than\n",
    "  - less than\n",
    "  - greater than or equal to\n",
    "  - less than or equal to\n",
    "\n",
    "5. Set the other required values, based on what you chose for __Allow__ and __Data__. For example, if you select between, then select the __Minimum:__ and __Maximum:__ values for the cell(s).\n",
    "6. Select the __Ignore blank__ checkbox if you want to ignore blank spaces (i.e. for missing data). Note, though, that Excel doesn't have any room for special characters you may be using as a marker for missing data (e.g. `..`) so these would be raised as errors.\n",
    "7. Select __OK__.\n",
    "\n",
    "Now - only for new data - if a user tries to enter a value that is not valid, a pop-up appears with the message, \"This value doesn’t match the data validation restrictions for this cell.\" We'll run validation on your existing data shortly, but first a detour into `lists`.\n",
    "\n",
    "### 2.3.2 Lists are a special type\n",
    "\n",
    "Before you can validate a `list` type, you need to specify valid terms. In Excel, this requires an [extra set of steps](https://support.office.com/en-us/article/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b).\n",
    "\n",
    "1. Create a new worksheet in Excel, and there list the terms you want to set as valid values. You can quickly convert your list to a table by selecting any cell in the range, and pressing __Ctrl+T__ (this may differ from version to version in Excel, or entirely in Open Office or other spreadsheet applications).\n",
    "\n",
    "  ![List terms](images/excel-list-terms.png)\n",
    "  \n",
    "2. Add your list data and format it as a Table (__Home tab > Styles > Format as Table__).\n",
    "3. You can name your table from the Table tools tab - this one could be named \"CityTable\".  This will help you keep track of multiple tables.\n",
    "4. In the validation process listed above, go to 3. and select __List__, then add a named range or table name for your list. \n",
    "5. Specify a source for your terms via __Data tab > Data Validation > Allow List > Source__. Then specify your list of terms as any of:\n",
    "\n",
    "  ![List source](images/excel-list-source.png)\n",
    "\n",
    "  - You can select the list sheet and range directly (e.g. `=Sheet1!A4:A10`)\n",
    "  - Convert your list to a table with __Ctrl+T__, then from the __Table Design__ tab give your table a name, permitting you to reference the table name and column (e.g. `=CityTable[City]`)\n",
    "  - From the __Formulas__ tab select __Name Manager__, create a __New__ item with an appropriate name (e.g. `CityList`), and reference the cells (e.g. `=Sheet1!A4:A10`), which then lets you reference your list anywhere (e.g. `=CityList`)\n",
    "\n",
    "### 2.3.3 Validate and get error messages for your existing data\n",
    "\n",
    "After you've specified validation rules on your existing data you might be disappoined. Excel does not automatically notify you whether these cells contain invalid data. Here's a quick way to [highlight existing invalid cells](https://support.office.com/en-us/article/more-on-data-validation-f38dee73-9900-4ca6-9301-8a5f6e1f0c4c) by circling the values:\n",
    "\n",
    "  ![Circle invalid data](images/excel-circled-cell.gif)\n",
    "\n",
    "1. To apply the circles, select the cells you want to evaluate and go to __Data > Data Tools > Data Validation > Circle Invalid Data__.\n",
    "\n",
    "  ![List terms](images/excel-data-circle.png)\n",
    "  \n",
    "2. If you correct an invalid entry, the circle disappears automatically.\n",
    "3. To remove data validation for a cell, select it, and then go to __Data > Data Tools > Data Validation > Settings > Clear All__.\n",
    "\n",
    "Now your turn:\n",
    "\n",
    "<div class=\"alert alert-block alert-success\">\n",
    "    <p><b>Exercise:</b></p>\n",
    "    <p>Using the restructured file you created in Lesson 1.1 specify validation criteria for each column based on the JSON Schema you created. Check for invalid data and correct where necessary.</p>\n",
    "    <p>One thing you might notice in your data ... sometimes you have an invalid integer specified as a range, e.g. <code>200-210</code>. Here are some ideas about how to deal with that.</p>\n",
    "    <ul>\n",
    "        <li><b>Ranges instead of numbers</b>: if, e.g. your range is <code>200-210</code>, you could reset this value as <code>200</code>, or <code>210</code> or even the range average <code>205</code>. Whatever you decide, document your decision in your metadata file.</li>\n",
    "        <li><b>Date ranges</b>: the same goes for dates, although you should be careful ... the likelihood is that a value applies from the end of the date range, not the beginning or middle, so e.g. <code>2008-2009</code> is most likely to be <code>2009</code></li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <b>References:</b>\n",
    "    <br>\n",
    "    <ul>\n",
    "        <li><a href=\"https://support.office.com/en-gb/article/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249\">Apply data validation to cells</a></li>\n",
    "        <li><a href=\"https://support.office.com/en-us/article/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b\">Create a drop-down list</a></li>\n",
    "        <li><a href=\"https://support.office.com/en-us/article/more-on-data-validation-f38dee73-9900-4ca6-9301-8a5f6e1f0c4c\">More on data validation</a></li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.4 Saving your validated file as a comma-separated-value\n",
    "\n",
    "Comma separated value files (`.csv`) are text files in which the comma character `,` separates each field of text. Where a comma appears in the value - whether a `string` or `number` - the value is then surrounded by quotation marks, e.g. `100, 200, \"20,000\"` indicates three values in three separate fields.\n",
    "\n",
    "You can change the separator character that is used in both delimited and .csv text files, and there are a wide range of formats (e.g. `;`, `*`). There are any number of reasons for this, and it is part of the reason that CSV-formatted files are not the cure-all we would hope for ensuring consistency in open data.\n",
    "\n",
    "In Excel, you can [export a spreadsheet](https://support.office.com/en-gb/article/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba) as a CSV using __Save As__ as follows:\n",
    "\n",
    "1. Go to __File > Save As__.\n",
    "2. Click __Browse__.\n",
    "3. In the __Save As__ dialog box, under __Save as type__ box, choose the text file format for the worksheet; for example, click __CSV (Comma delimited)__.\n",
    "4. Browse to the location where you want to save the new text file, and then click __Save__.\n",
    "\n",
    "> You are only able to export the current worksheet (i.e. the one in view when you complete this process) to the new CSV file. You can save other worksheets as separate text files by repeating this procedure for each worksheet.\n",
    "\n",
    "> All spreadsheet-specific features will be lost. Formatting (bold, colours, etc), formulae and validation criteria will be removed leaving only the data in a text file.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.5 Validating your data and JSON schema using CSVLint [optional]\n",
    "\n",
    "In the next lesson, we'll learn how to validate your data using Python directly in a Jupyter Notebook, for now we'll use an online resource provided by the Open Data Institute called [CSVLint](https://csvlint.io/).\n",
    "\n",
    "CSVLint provides the following guide on how to use it:\n",
    "\n",
    "![CSVLint](images/csv-lint.jpg)\n",
    "\n",
    "1. Enter the location of the CSV file you want to check, or upload it if it's not on the web already.\n",
    "2. Tick __Add optional schema (in .json format)__ and enter its location or upload it.\n",
    "3. Hit the big __Validate__ button.\n",
    "\n",
    "You'll see a page that tells you how to improve your CSV file, if it needs improvement.\n",
    "\n",
    "- __Errors__ are things you really need to fix, because they'll stop people from being able to use your data\n",
    "- __Warnings__ are thing you should fix if you can because doing so will help people to use your data\n",
    "- __Information__ messages are hints and tips of things that could make the data easier to use\n",
    "\n",
    "CSVLint also lists a number of common errors for you to watch out for:\n",
    "\n",
    "- __Invalid encoding__: if there are any odd characters in a file which could cause encoding errors\n",
    "- __Line breaks__: if line breaks are not the same throughout the file\n",
    "- __Undeclared header__: if you do not specify in a machine readable way whether or not your CSV has a header row\n",
    "- __Ragged rows__: if every row in the file doesn't have the same number of columns\n",
    "- __Blank rows__: if there are any blank rows\n",
    "- __Stray/Unclosed quote__: if there are any unclosed quotes in the file\n",
    "- __Whitespace__: if there is any whitespace between commas and double quotes around fields\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <b>References:</b>\n",
    "    <br>\n",
    "    <ul>\n",
    "        <li><a href=\"https://csvlint.io/\">CSVLint</a></li>\n",
    "        <li><a href=\"https://datagraft.io/\">DataGraft</a></li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.6 Lesson tutorial\n",
    "\n",
    "<div class=\"alert alert-block alert-success\">\n",
    "    <p><b>Tutorial:</b></p>\n",
    "    <p>Complete the processing of the file you started working with in Lesson 1.</p>\n",
    "    <ul>\n",
    "        <li><b>JSON schema</b>: Define a JSON schema to describe and specify validation criteria for your dataset.</li>\n",
    "        <li><b>Check for invalid data</b>: Specify validation criteria in your spreadsheet application, check for invalid data and correct where necessary..</li>\n",
    "        <li><b>Save as CSV</b>: Export your machine-readable data from your spreadsheet application and save it as a <code>.csv</code>.</li>\n",
    "        <li><b>Validate</b>: Upload your CSV and JSON schema onto <a href=\"https://csvlint.io/\">CSVLint</a> and ensure you are successfully validated. Fix any errors you receive.</li>\n",
    "    </ul>\n",
    "</div>\n",
    "\n",
    "Please complete the tutorial before continuing with this series. If you are participating in a taught class, please send your tutorial submission via the required process (email or online)."
   ]
  }
 ],
 "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.9.4"
  },
  "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
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}