docs/Statistical functions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "mdt0",
   "metadata": {},
   "source": [
    "# Statistical functions\n",
    "\n",
    "You can run the cells below directly in LUSID's JupyterHub.\n",
    "\n",
    "The `%%luminesce` is a magic command which passes the cell query string to Lumipy,\n",
    "which then returns a DataFrame.\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "mds00",
   "metadata": {},
   "source": [
    "#### Fuzzy search two files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ccs00",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%luminesce\n",
    "\n",
    "-- ============================================================\n",
    "-- Description:\n",
    "-- 1. This query demonstrates how to apply a Fuzzy search \n",
    "--    algorithm to match data entries across two files.\n",
    "-- \n",
    "-- 2. The first data file is from Companies Taking Action (CTA),\n",
    "--    and second from BCORP Impact Data.\n",
    "-- \n",
    "-- 3. Fuzzy search algorithm (Levenshtein distance) works by comparing\n",
    "--    the similarity of strings by calculating the number of \n",
    "--    characters that need to be changed in the first word to\n",
    "--    match the second word.\n",
    "--\n",
    "-- 4. The Fuzzy score denotes how many characters need to be\n",
    "--    changed to form a match.\n",
    "-- \n",
    "-- 5. Luminesce's built-in edit_distance function is an \n",
    "--    implementation of this algorithm.\n",
    "-- \n",
    "-- Links to Datasets :\n",
    "-- CTA - https://sciencebasedtargets.org/companies-taking-action\n",
    "-- BCORP - https://data.world/blab/b-corp-impact-data\n",
    "-- \n",
    "-- Save these files in your drive\n",
    "-- https://support.lusid.com/knowledgebase/article/KA-01635/en-us\n",
    "-- \n",
    "-- ============================================================\n",
    "\n",
    "-- Get files from drive\n",
    "@bcorp = use Drive.Csv\n",
    "--file=/luminesce-examples/b-corp.csv\n",
    "enduse;\n",
    "\n",
    "@cta = use Drive.Csv\n",
    "--file=/luminesce-examples/companies-taking-action.csv\n",
    "enduse;\n",
    "\n",
    "-- Make location values consistent across both datasets to help with comparison\n",
    "@cta_formatted = \n",
    "select \"Company Name\" as company_name,\n",
    "\"Near term - Target Status\" as target,\n",
    "ISIN,\n",
    "case\n",
    "when \"Location\" = \"United Kingdom (UK)\" then \"United Kingdom\"\n",
    "when \"Location\" = \"United States of America (USA)\" then \"United States\"\n",
    "when \"Location\" = \"Hong Kong, China\" then \"Hong Kong S.A.R\"\n",
    "when \"Location\" = \"Netherlands\" then \"Netherlands The\"\n",
    "when \"Location\" = \"Taiwan, Province of China\" then \"Taiwan\"\n",
    "when \"Location\" = \"United Arab Emirates (UAE)\" then \"United Arab Emirates\"\n",
    "else \"Location\"\n",
    "end as location\n",
    "from @cta;\n",
    "\n",
    "-- Filter down companies that are in same location, restrict search radius\n",
    "@data = \n",
    "select distinct\n",
    "b.company_name as BCORP_COMPANY,\n",
    "c.company_name as CTA_COMPANY,\n",
    "b.description as DESCRIPTION,\n",
    "c.target as TARGET_STATUS,\n",
    "b.company_id as COMPANY_ID,\n",
    "c.ISIN as ISIN\n",
    "from @bcorp b\n",
    "inner join @cta_formatted c\n",
    "where c.location = b.country;\n",
    "\n",
    "-- Run fuzzy search on remaining matches\n",
    "@fuzzy = \n",
    "select\n",
    "BCORP_COMPANY,\n",
    "CTA_COMPANY,\n",
    "edit_distance(BCORP_COMPANY, CTA_COMPANY) as 'LEVENSHTEIN_RESULTS',\n",
    "COMPANY_ID,\n",
    "ISIN,\n",
    "DESCRIPTION,\n",
    "TARGET_STATUS\n",
    "from @data;\n",
    "\n",
    "-- Display matches with Fuzzy score of less than 2\n",
    "select * from @fuzzy where \"LEVENSHTEIN_RESULTS\" < 2;\n"
   ]
  }
 ],
 "metadata": {},
 "nbformat": 4,
 "nbformat_minor": 5
}