{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "13418e05-f970-4aa9-9dd8-35b4bd0eaf71",
      "metadata": {},
      "outputs": [],
      "source": [
        "import pandas as pd"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "b5d08168-f04f-4769-93f7-c5e3c25e5c0f",
      "metadata": {},
      "outputs": [],
      "source": [
        "from marine_qc import (\n",
        "    duplicate_check,\n",
        "    flag_duplicates,\n",
        "    get_duplicates,\n",
        "    remove_duplicates,\n",
        ")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "3fa6ca48-7f38-4127-93e8-8a8f4119d5e7",
      "metadata": {},
      "outputs": [],
      "source": [
        "from data import get_advanced_data"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "56c5d833-c51f-4b59-8c75-9166cbcbf530",
      "metadata": {},
      "source": [
        "# How to use the duplicate checker to detect possibly duplicated observations"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "5cddbfc6-f401-4c8f-bfea-b08e09ed38ee",
      "metadata": {},
      "source": [
        "The ``marine_qc`` toolbox provides some checks to detect possibly duplicated observations. Creating some test dataset we can show how to use them on \"real\" data. There are several checks that are shown here:\n",
        "\n",
        "* a duplicate check to detect possibly duplicated observations\n",
        "* a function that gets potentially duplicated observations and returns the corresponding detected duplicate matches for each observation\n",
        "* a function that flags potentially duplicated observations and returns the duplicate flags for each observation\n",
        "* a function that removes potentially duplicated observations and returns the input data with duplicates excluded\n",
        "\n",
        "For more information of all available QC checks on individual reports see [the overview of QC functions to detect possibly duplicated observations](https://marine-qc.readthedocs.io/en/stable/overview_dup.html)."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "48231885-5c59-4ea1-9c5a-e48a5dc6dacd",
      "metadata": {},
      "source": [
        "The test dataset we provide is a pandas.DataFrame including several individual marine reports."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "b65d2a1a-4113-4d16-b416-5548a90424de",
      "metadata": {
        "scrolled": true
      },
      "outputs": [],
      "source": [
        "data = get_advanced_data()\n",
        "data"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "735af2d8-b1ec-40ba-b1bb-8c197980cd12",
      "metadata": {},
      "source": [
        "We have several different marine reports that include six different parameters (`lat`, `lon`, `date`, `sst` (sea-surface temperature), `vsi` (ship speed), and `dsi` (ship heading))."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "a856eef7-82bf-498f-95f6-9aa2aa26e957",
      "metadata": {},
      "source": [
        "First of all, we detect possibly duplicated observations. This function returns a `marine_qc.duplicate_checker.duplicates.DupDetect` class that contains all information needed for the other duplicate functions. "
      ]
    },
    {
      "cell_type": "markdown",
      "id": "a05ea814-9a25-4e16-97a6-781b6885ea1c",
      "metadata": {},
      "source": [
        "## Use the default settings of the duplicate checker"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "7742cb43-add7-4b46-bd30-1260bca8fa28",
      "metadata": {},
      "source": [
        "To introduce the duplicate check functions, we work with the default settings. \n",
        "\n",
        "> **Note:**\n",
        "> \n",
        "> By default, the duplicate check comprises six kinds of observational values:\n",
        "> \n",
        "> * `station_id`: Any kind of idnetifier of the observing station (e.g. ship name, WIGOS Station Identifier).\n",
        "> * `lat`: Latitude of the observing station in degrees.\n",
        "> * `lon`: Longitude of the observing station in degrees.\n",
        "> * `date`: Time stamp of the obersing station (datetime object or datetime-formatted string).\n",
        "> * `vsi`: Speed of the observing station in kilometers per hour.\n",
        "> * `dsi`: Course of the observing station in degrees.\n",
        "> \n",
        "> By default, duplicate observations are detected according to the following criteria:\n",
        "> \n",
        "> * `station_id`: values must match exactly for observations to be considered duplicates.\n",
        "> * `lat`: values may differ by up to 0.11 degrees for observations to be considered duplicates.\n",
        "> * `lon`: values may differ by up to 0.11 degrees for observations to be considered duplicates.\n",
        "> * `date`: values may differ by up to 1 minute for observations to be considered duplicates.\n",
        "> * `vsi`: values may differ by up to 0.09 kilometers per hour for observations to be considered duplicates.\n",
        "> * `dsi`: values may differ by up to 0.9 degrees for observations to be considered duplicates.\n",
        "\n",
        "Later, we will see how to add extra kinds of observational values and their corresponding criteria."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "a0c57426-9b96-4249-a23b-1f32debb0342",
      "metadata": {},
      "source": [
        "Firstly, we run the `duplicate_check`. The result can be used to run the other duplicate check functions. Alternatively, the functions can be run directly with the data itself. If so, `duplicate_check` is run internally."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "ff3b550c-471f-4b0b-827f-8c6d22d8740a",
      "metadata": {},
      "outputs": [],
      "source": [
        "detected = duplicate_check(data=data)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "b824a093-bf75-4d75-8d03-cf04b5bdc6da",
      "metadata": {
        "scrolled": true
      },
      "outputs": [],
      "source": [
        "dups = get_duplicates(\n",
        "    detected=detected,\n",
        ")\n",
        "pd.DataFrame({**data, \"duplicates\": dups})"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "9322dc34-92a3-495b-8f23-516eefe53ccf",
      "metadata": {},
      "source": [
        "As we can see, the following duplicates has been detected:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "9380a496-b74b-4f7f-8530-f76c81ee4c47",
      "metadata": {},
      "outputs": [],
      "source": [
        "data.loc[[\"B\", \"H\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "48fdc582-796e-4c31-af39-c499e6e08c79",
      "metadata": {},
      "source": [
        "We expect these to be detected as duplicates since:\n",
        "\n",
        "* `station_id`: do match exactly\n",
        "* `lon`: do match exactly\n",
        "* `lat`: do match exactly\n",
        "* `date`: differ less than 1 minute\n",
        "* `vsi`: do match exactly\n",
        "* `dsi`: do match exactly"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "fef2e3f7-1616-470a-9d6e-87fe3b3230e3",
      "metadata": {},
      "outputs": [],
      "source": [
        "data.loc[[\"C\", \"J\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "f18530e5-9063-4cf6-b6f8-6c848affd9d7",
      "metadata": {},
      "source": [
        "We expect these to be detected as duplicates since:\n",
        "\n",
        "* `station_id`: do match exactly\n",
        "* `lon`: do match exactly\n",
        "* `lat`: do match exactly\n",
        "* `date`: do match exactly\n",
        "* `vsi`: are both `NaN`\n",
        "* `dsi`: are both `NaN` "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "5299a5a8-e73c-41e0-9ad2-7066147919fb",
      "metadata": {},
      "outputs": [],
      "source": [
        "data.loc[[\"D\", \"L\", \"N\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "40fe5ed5-a830-4cdc-9626-96cbfa56dc25",
      "metadata": {},
      "source": [
        "We expect these to be detected as duplicates since:\n",
        "\n",
        "* `station_id`: do match exactly\n",
        "* `lon`: differ less than 0.11 degrees\n",
        "* `lat`: differ less than 0.11 degrees\n",
        "* `date`: do match exactly\n",
        "* `vsi`: do match exactly\n",
        "* `dsi`: do match exactly"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "64a84180-5721-46d7-841f-f99bd69491e1",
      "metadata": {},
      "outputs": [],
      "source": [
        "data.loc[[\"E\", \"F\", \"Q\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "105f729f-1c0b-48c2-b5a9-d5bd4b8b60e0",
      "metadata": {},
      "source": [
        "We expect these to be detected as duplicates since:\n",
        "\n",
        "* `station_id`: do match exactly\n",
        "* `lon`: differ less than 0.11 degrees\n",
        "* `lat`: differ less than 0.11 degrees\n",
        "* `date`: do match exactly\n",
        "* `vsi`: do match exactly\n",
        "* `dsi`: do match exactly"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "abc3f684-e233-403c-abe0-a950e9120f96",
      "metadata": {},
      "source": [
        "Now, we can flag the duplicates. The flagging scheme is:\n",
        "\n",
        "* `0`: unique observation\n",
        "* `1`: best duplicate\n",
        "* `3`: worst duplicate"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "1ffbc100-7a18-4f57-8b53-6e04398664cd",
      "metadata": {},
      "outputs": [],
      "source": [
        "flags = flag_duplicates(detected=detected)\n",
        "pd.DataFrame({**data, \"duplicate_flag\": flags})"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "9dcdaec2-bf28-476e-8b23-d826bea13631",
      "metadata": {},
      "source": [
        "Optionally, we can remove the duplicated observations."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "bfc051ab-3d2a-4630-8115-0f94f1e165fa",
      "metadata": {},
      "outputs": [],
      "source": [
        "series = remove_duplicates(detected=detected)\n",
        "pd.DataFrame(series).T"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "76e0f78b-875a-4517-a95d-f83bd559f13f",
      "metadata": {},
      "source": [
        "## Manipulate the settings of the duplicate checker"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "01a4e536-35bf-4767-abdb-ad16c664e751",
      "metadata": {},
      "source": [
        "Here are some more examples of how to use the duplicate checker with user-specific settings.\n",
        "\n",
        "* `ignore_entires`: Ignore specific values for selected columns during comparison.\n",
        "* `ignore_nan_both`: For selected columns, consider two observations as duplicates whenever both compared value are NaN. By default, this is true for all columns.\n",
        "* `ignore_nan_either`: For selected columns, consider two observations as duplicates whenever either compared value is NaN.\n",
        "* `ignore_columns`: Column names to exclude entirely from duplicate detection.\n",
        "* `offsets`: Override comparison offsets for selected columns.\n",
        "* `compare_level_libraries`: Override comparison levels for selected columns. For more information see [Splink: Comparison Level Library](https://moj-analytical-services.github.io/splink/api_docs/comparison_level_library.html)."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "845d915f-e347-4373-bb3f-bdb2cf38360d",
      "metadata": {},
      "source": [
        "### `ignore_entries`"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "2ce8f2c0-7f66-474d-9383-2940d4b9fd0c",
      "metadata": {},
      "source": [
        "If `station_id` of an observation is \"AA\" or \"BB\", this entry is considered placeholder which is compatible with and equivalent to any other `station_id` value."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "20f026dc-4abd-47e9-a402-40e54ea26676",
      "metadata": {},
      "outputs": [],
      "source": [
        "dups = get_duplicates(\n",
        "    data=data,\n",
        "    ignore_entries={\"station_id\": [\"AA\", \"BB\"]},\n",
        ")\n",
        "pd.DataFrame({**data, \"duplicates\": dups})"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "cecac783-f29a-4b70-a757-00a5694268c1",
      "metadata": {},
      "source": [
        "In addition to the default example, we get the following duplicates:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "00f50110-b923-48d7-8109-4987cecdc2be",
      "metadata": {},
      "outputs": [],
      "source": [
        "data.loc[[\"D\", \"L\", \"M\", \"N\", \"O\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "63077603-9291-41b5-8520-f66cb195f0c9",
      "metadata": {},
      "source": [
        "* \"O\" is detected as duplicate as expected since we use \"BB\" as a placeholder.\n",
        "* \"M\" is detected as duplicate as expected since we use \"AA\" and \"BB\" as placeholders which makes \"AB\" the only valid ``station_id``."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "860ccf10-e5bf-4b05-a8ab-239b22007a7d",
      "metadata": {},
      "source": [
        "### `ignore_nan_both`"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "f2b1df67-cfd8-4002-ad44-153459c3da31",
      "metadata": {},
      "source": [
        "Two observations are considered as duplicates whenever both compared value are NaN only for selected column `vsi`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "f39f349e-220b-4249-a015-42e520dc6087",
      "metadata": {
        "scrolled": true
      },
      "outputs": [],
      "source": [
        "dups = get_duplicates(\n",
        "    data=data,\n",
        "    ignore_nan_both=\"vsi\",\n",
        ")\n",
        "pd.DataFrame({**data, \"duplicates\": dups})"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "7b39d7f8-6317-415f-a54e-5cab61dccab8",
      "metadata": {},
      "source": [
        "In comparison with the default example \"C\" and \"J\" are not considered as duplicates since `dsi` has NaN values:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "25063f05-976e-47f6-a403-f5ad8af8b6f9",
      "metadata": {},
      "outputs": [],
      "source": [
        "data.loc[[\"C\", \"J\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "dd70cef1-4c87-43d6-bd6a-4cd3499705ba",
      "metadata": {},
      "source": [
        "### `ignore_nan_either`"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "bcd1d7ee-c1b6-44bb-aa00-60c6cf0b6876",
      "metadata": {},
      "source": [
        "For `vsi`, consider two observations as duplicates whenever either compared value is NaN."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "78a13919-efe6-4b99-8b3f-f821018b4db7",
      "metadata": {},
      "outputs": [],
      "source": [
        "dups = get_duplicates(\n",
        "    data=data,\n",
        "    ignore_nan_either=\"vsi\",\n",
        ")\n",
        "pd.DataFrame({**data, \"duplicates\": dups})"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "16be1383-38df-483e-8fb3-7e0bfd0b6cb6",
      "metadata": {},
      "source": [
        "The duplicate group (\"B\", \"H\") is extended by (\"U\", \"S\")."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "1b5c8d09-3e23-4e19-b01e-93e4de8a1f67",
      "metadata": {},
      "outputs": [],
      "source": [
        "data.loc[[\"B\", \"H\", \"U\", \"S\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "5c7efc9c-444e-404f-8e21-0a75c12d32ab",
      "metadata": {},
      "source": [
        "<div style=\"border-left: 4px solid #f0ad4e; background-color: #fcf8e3; padding: 10px; margin: 10px 0;\">\n",
        "<strong>Warning:</strong> \n",
        "\n",
        "Here we can see, that `ignore_nan_either` can lead to misleading duplicate chains:\n",
        "\n",
        "* (\"B\", \"H\") is obviously a duplicate pair.\n",
        "* (\"H\", \"U\") is a duplicate pair as well since we ignore NaNs for `vsi`.\n",
        "* (\"U\", \"S\") is a duplicate pair as well since we ignore NaNs for `vsi`.\n",
        "\n",
        "This leads to the following duplicate chain:\n",
        "\n",
        "\"B\" -> \"H\" -> \"U\" -> \"S\"\n",
        "\n",
        "But obviously, (\"B\", \"S\") is not a duplicate pair since `vsi` differs more than 0.11 degrees.\n",
        "\n",
        "</div> "
      ]
    },
    {
      "cell_type": "markdown",
      "id": "89bd1116-5d2a-4354-a972-bb1708ab9fff",
      "metadata": {},
      "source": [
        "### `ignore_columns`"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "0690adee-7996-46f2-b1da-0cf468c36f41",
      "metadata": {},
      "source": [
        "We exclude `station_id` entirely from duplicate detection."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "45b438dd-3f5d-43f6-8730-1bb4f2647aa4",
      "metadata": {},
      "outputs": [],
      "source": [
        "dups = get_duplicates(\n",
        "    data=data,\n",
        "    ignore_columns=\"station_id\",\n",
        ")\n",
        "pd.DataFrame({**data, \"duplicates\": dups})"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "8ea4209c-e305-4988-bf44-4f3a7dbcf7b7",
      "metadata": {},
      "source": [
        "We extend one duplicate group from the default example since we ignore `station_id` completely."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "883ee0af-0665-4a30-9782-e0e729c79a18",
      "metadata": {},
      "outputs": [],
      "source": [
        "data.loc[[\"D\", \"L\", \"M\", \"N\", \"O\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "1e9c579d-16b6-4f6a-9ea2-17bbabb57a3b",
      "metadata": {},
      "source": [
        "### `offsets`"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "875bb5c5-8a2c-4b3c-8455-cb1286dcf36c",
      "metadata": {},
      "source": [
        "We now consider two observations as duplicates if:\n",
        "\n",
        "* `lat` differs up to 1.0 degrees\n",
        "* `lon` differs up to 1.0 degrees\n",
        "* `date` differs up to 360 seconds (1 hour)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "3c583c8a-35b6-4c78-ab24-e6f2daa14cb2",
      "metadata": {},
      "outputs": [],
      "source": [
        "dups = get_duplicates(\n",
        "    data=data,\n",
        "    offsets={\"lat\": 1.0, \"lon\": 1.0, \"date\": 360},\n",
        ")\n",
        "pd.DataFrame({**data, \"duplicates\": dups})"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "220318f1-ffef-44ac-b270-a5e5581787a4",
      "metadata": {},
      "source": [
        "We expand two duplicate groups:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "c117ec99-a36b-42c5-9603-8cc0ef6590d0",
      "metadata": {},
      "outputs": [],
      "source": [
        "data.loc[[\"D\", \"K\", \"L\", \"N\"]]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "aedd60f5-72ba-4b34-9407-647abbfd9d91",
      "metadata": {},
      "outputs": [],
      "source": [
        "data.loc[[\"E\", \"F\", \"P\", \"Q\"]]"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "2f8089b8-b944-4a1c-ab2c-3b831c2b1208",
      "metadata": {},
      "source": [
        "### `compare_level_libraries`"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "85561595-fd9a-45c6-8d39-9c02a608817b",
      "metadata": {},
      "source": [
        "Finally, we add `sst` with an offset of 1K to the criteria list."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "ef4e4172-599e-4ae6-938a-49986b7f41d0",
      "metadata": {},
      "outputs": [],
      "source": [
        "dups = get_duplicates(\n",
        "    data=data,\n",
        "    compare_level_libraries={\"sst\": \"AbsoluteDifferenceLevel\"},\n",
        "    offsets={\"sst\": 1.0},\n",
        ")\n",
        "pd.DataFrame({**data, \"duplicates\": dups})"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "3816d2dc-28cb-4ef5-aea7-d7d5d33abf92",
      "metadata": {},
      "source": [
        "Now (\"B\", \"H\") is no duplicate pair anymore since `sst` differs more than 1 Kelvin."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "1fecb281-d524-478c-b18f-243ece794dbf",
      "metadata": {},
      "outputs": [],
      "source": [
        "data.loc[[\"B\", \"H\"]]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "e3c8710f-f860-4f39-b30c-aebd45a93742",
      "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.13.2"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}