Open-FF_Data_Dictionary.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36a357aa-a7a2-4ae6-b7db-72ef084dfde2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run \"catalog_common.py\" \n",
    "ID_header('Open-FF:  Data Dictionary', incl_links=True)\n",
    "set_page_param()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f3070be-1d50-4e49-b5b4-fda2b5e9f0df",
   "metadata": {},
   "source": [
    "#### Description of the contents of the final data files generated by Open-FF from the FracFocus data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "different-broad",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from itables import init_notebook_mode\n",
    "init_notebook_mode(all_interactive=True)\n",
    "import itables.options as opt\n",
    "from itables import show as iShow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "portuguese-muscle",
   "metadata": {},
   "outputs": [],
   "source": [
    "def addfield(dict,fn, table):\n",
    "    dict.setdefault(fn, []).append(table)\n",
    "\n",
    "tables = grd.get_repo_tables(repo_name,verbose=False)\n",
    "all_fn = {}\n",
    "for t in tables.keys():\n",
    "    for fn in tables[t]:\n",
    "        addfield(all_fn,fn,t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fa9ccf2-e7a8-4035-a674-25a39fe080b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "kys = list(all_fn.keys())\n",
    "tbls = []\n",
    "for k in kys:\n",
    "    tbls.append(all_fn[k])\n",
    "    \n",
    "full = ana_set.Full_set(repo = repo_name, outdir='../common/').get_set()\n",
    "#print(f'Number of fields in Full: {len(full.columns)}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c44625b5-af55-48de-a112-7ec78c12e959",
   "metadata": {},
   "outputs": [],
   "source": [
    "# find fields create outside of the tables\n",
    "for col in full.columns:\n",
    "    if not col in kys:\n",
    "        #print(col)\n",
    "        kys.append(col)\n",
    "        tbls.append('filter flag')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "distributed-adelaide",
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate a few stats for each field        \n",
    "uniq = []\n",
    "num_val = []\n",
    "dt = []\n",
    "for k in kys:\n",
    "    try:\n",
    "        uniq.append(full[k].nunique())\n",
    "    except:\n",
    "        uniq.append(-1)\n",
    "    try:\n",
    "        #num_val.append(len(full[full[k].notna()]))\n",
    "        num_val.append(full[k].notna().sum())\n",
    "    except:\n",
    "        num_val.append(-1)\n",
    "    try:\n",
    "        dt.append(full[k].dtype)\n",
    "    except:\n",
    "        dt.append('?')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "greenhouse-vehicle",
   "metadata": {},
   "outputs": [],
   "source": [
    "all_fn_df = pd.DataFrame({'fieldName':kys,'Database_Tables':tbls,'Data_type':dt,'Num':num_val,\n",
    "                          'Unique':uniq})\n",
    "#all_fn_df.Num = all_fn_df.Num.map(lambda x: round_sig(x))\n",
    "#all_fn_df.Unique = all_fn_df.Unique.map(lambda x: round_sig(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "photographic-guidance",
   "metadata": {},
   "source": [
    "### Acceptable use of FracFocus data\n",
    "One requirement \n",
    "for using the FracFocus data is stipulated on the FracFocus website:\n",
    "\n",
    "**\"Downloaded data may be aggregated or combined with other datasets,** \n",
    "**but the FracFocus data may not be altered in any way.\"**\n",
    "\n",
    "Please read the entire \"Terms of use\" at http://fracfocus.org/data-download.\n",
    "\n",
    "The work in this project maintains the original FracFocus data as is reported\n",
    "in the bulk download.  The field names used in the original are kept: All of \n",
    "these original names begin with an upper-case letter and can be identified\n",
    "in that way.  Fields generated by this project or from external data sources will begin with a lower case\n",
    "letter (for example, `CASNumber` is the original field, `bgCAS` is the \n",
    "generated field. Note there are two exceptions: `DTXSID` and `MI_inconsistent` are NOT \n",
    "original with FracFocus.)\n",
    "\n",
    "In the zipped bulk download from FracFocus, a data dictionary is provided in \n",
    "the **'readme.txt'** file. (This zipped download is in the /sources or /data directory\n",
    "and we rename it as 'currentData.zip')  This file gives some information about many of \n",
    "the fields found; however, it is written for the SQL database version of the bulk \n",
    "download, not the CSV version which we use in this project.  Further, some important fields are not mentioned in that readme.txt file; they are\n",
    "described below.  In the descriptions of all fields below, we cite the FracFocus text \n",
    "from a June 2021 bulk download."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fifty-bulletin",
   "metadata": {},
   "source": [
    "## Descriptions of fields in the output data sets\n",
    "\n",
    "|Explanation of columns in the table below|\n",
    "| --- |\n",
    "\n",
    "|column| what it is|\n",
    "| --- | :--- |\n",
    "|**fieldName:**|The name of the field or column in the data set. All field names that are capitalized are from the original FracFocus downloaded data.  Lower-case names are generated by Open-FF.  <br>**tables**: Which Open-FF internal tables that are used to construct output data sets have this field|\n",
    "|**FracFocus description:**| Description of the (original) field given by FracFocus in the bulk download file, *readme.txt*.|\n",
    "|**Open-FF description:**|Our description of the field|\n",
    "|**source:**|is this field a direct copy of the original FracFocus data or is it generated by Open-FF?|\n",
    "|**Num:**| the number of non-empty values in the field|\n",
    "|**Unique:**| the number of unique types (including NaN) in the field|\n",
    "|**Data_type:**| the python/pandas data type for the field|\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "beneficial-merit",
   "metadata": {},
   "outputs": [],
   "source": [
    "desc = {'bgCAS':\"\"\"this is our best guess for the CAS registry number based on: 1) CASNumber and IngredientName, \n",
    "2) an automated cleaning and validation process, 3) a manual curation process and \n",
    "4) a check against an authoritative catalog of CAS numbers.\"\"\",\n",
    "'bgIngredientName':\"\"\"the primary ingredient name used for bgCAS in the online \n",
    "reference, SciFinder.  While other synonyms may be valid, a single name used\n",
    "across the whole data set allows for easier searches.\"\"\",\n",
    "'calcMass':\"\"\"The calculated mass (in pounds) of the chemical in the record. \n",
    "This mass is calculated using the total mass of the fracking job and the \n",
    "percentage of the chemical in the whold job (PercentHFJob).  <br>(This field was\n",
    "previously called 'bgMass')\"\"\",\n",
    "'bgOperatorName':\"\"\"our curatated version of the raw field 'OperatorName.'  This is \n",
    "a simple translation of the OperatorName field using the file: xlate_companies.csv.\n",
    "See that file in the /data/transformed directory.\"\"\",\n",
    "'bgStateName':\"\"\"Our corrected and normalized version of the raw field 'StateName.' \"\"\",\n",
    "'bgSupplier':\"\"\"our curatated version of the raw field 'Supplier.'  This is \n",
    "a simple translation of the Supplier field using the file: xlate_companies.csv.\n",
    "See that file in the /data/transformed directory.  This is an attempt to clean\n",
    "up the field to make it more searchable. While the raw field has over 80\n",
    "versions of the company name Halliburton, bgSupplier has only one.\"\"\",\n",
    "'data_source':\"\"\"indicates whether record was sourced from 'bulk' or some other source. Open-FF focuses primarily on the bulk download that is published\n",
    "regularly; however some archived or special purpose data sets may be used in this data framework.\"\"\",\n",
    "'date':\"\"\"a cleaned version of JobEndDate.  This version has removed time stamps and \n",
    "has corrected a few erroneous years.  Note that early disclosures only specified a \"fracture date\" which seems to be a starting date. When there is\n",
    "only a single date given, it is used for this field.\"\"\",\n",
    "'primarySupplier' :\"\"\"(added in version 9.0) This field is simply the most frequent bgSupplier in a disclosure (ignoring all non-company values such as\n",
    "'n/a', 'Listed Above', etc.)  This field is an attempt to overcome the disconnect\n",
    "between the chemical records and the descriptor fields in the System Approach.\n",
    "It should be noted that any specific chemical cannot be *directly* connected\n",
    "to the company in this field; use bgSupplier or Supplier for that.\"\"\",\n",
    "'reckey':\"\"\"a simple incrementing index of records across all raw input files.\"\"\",\n",
    "# 'alt_CAS':\"\"\"indicates CAS numbers implied when comparing IngredientName to a long list of chemical synonyms. This is used to determine if the IngredientName is specific\n",
    "# enough to authenticate the final CAS number.\"\"\",\n",
    "'bgSource':\"\"\"in the evaluation of a CASNumber|IngredientName pair, this indicates the source of the final bgCAS.\"\"\",\n",
    "'auto_carrier_type':\"\"\"for disclosures where carrier record(s) are auto-detected, this field indicates which\n",
    "auto-detection method was triggered.\"\"\",\n",
    "'cleanMI' :\"\"\"version of the MassIngredient value for this record that is filtered for inconsistancy within the\n",
    "disclosure.  If cleanMI has a value, the disclosure passed the consistency test.\"\"\",\n",
    "'MI_inconsistent':\"\"\"True/False where True indicates that this disclosure did NOT pass the consistency test.\"\"\",\n",
    "'IndianWell':\"\"\"(text field) Presumably, True when the well is within Native American controlled land. Note that\n",
    "Early disclosures were not categorized for this characteristic.  Furthermore, in other disclosures that are in the bulk download, if this\n",
    "value is not included in the disclosure, FracFocus gives a value of \"**False**\" when it should be unknown or unreported.\n",
    " See **bgNativeAmericanLand**.\"\"\",\n",
    "'FederalWell':\"\"\" (text field) Presumably, True when the well is on Federal lands. Note that\n",
    "Early disclosures were not categorized for this characteristic.Furthermore, in other disclosures that are in the bulk download, if this\n",
    "value is not included in the disclosure, FracFocus gives a value of \"**False**\" when it should be unknown or unreported.\n",
    " See **bgFederalLand**.\"\"\",\n",
    "'bgCountyName':\"\"\" Our corrected and normalized version of the raw field 'CountyName.'\"\"\",\n",
    "'bgLatitude':\"\"\" Our best guess of the latitude of the well. In a small proportion of cases, FracFocus Latitude is wrong\n",
    "but can be corrected. Although FracFocus lat/lon values may be reported in any of a number of 'Projections', all Open-FF latitudes and\n",
    "longitudes are converted to a single projection: WGS84.  See bgLocationSource.\"\"\",\n",
    "'bgLongitude':\"\"\" Our best guess of the longitude of the well. In a small proportion of cases, FracFocus Longitude is wrong\n",
    "but can be corrected. Although FracFocus lat/lon values may be reported in any of a number of 'Projections', all Open-FF latitudes and\n",
    "longitudes are converted to a single projection: WGS84. See bgLocationSource.\"\"\",\n",
    "'latlon_too_coarse':\"\"\"This flag is set to True when the decimal digits of Latitude and Longitude (together) are\n",
    "fewer than 5 digits. (For example 31.1, -89.34 is only 3 decimal digits.) This is an indication that the characterization\n",
    "of the location is <a href=\"https://gisjames.wordpress.com/2016/04/27/deciding-how-many-decimal-places-to-include-when-reporting-latitude-and-longitude/\">coarse enough to obscure </a>\n",
    "the well's actual place on the map.\"\"\",\n",
    "'no_chem_recs':\"\"\"This flag indicates that the disclosure has no chemical records \"\"\",\n",
    "'is_duplicate':\"\"\"This flag indicates that this disclosure has a duplicate in FracFocus; that is, has the same APINumber and\n",
    "JobEndDate.  Currently, there is no way to detect which is the correct disclosure, so all duplicates are removed in\n",
    "the standard filter.\"\"\",\n",
    "'has_TBWV':\"\"\"This flag indicates that a disclosure has a non-zero TotalBaseWaterVolume, and is therefore a\n",
    "candidate for mass calculations\"\"\",\n",
    "'within_total_tolerance':\"\"\"This flag indicates whether the totalPercent is within 5% tolerance of 100%. \"\"\",\n",
    "'carrier_mass_MI':\"\"\" This is the mass of the carrier calculated from the MassIngredient field.  Used only for \n",
    "comparative purposes.\"\"\",\n",
    "'carrier_density_from_comment':\"\"\"The density value reported in the IngredientComment field for those water records\n",
    "with a PercentHFJob value greater than 50%\"\"\",\n",
    "'carrier_percent':\"\"\"The PercentHFJob value for the carrier record(s) in the disclosure\"\"\",\n",
    "'carrier_density_MI':\"\"\"This is the density of the carrier calculated from the MassIngredient field. Used only for\n",
    "comparative purposes\"\"\",\n",
    "'bgDensity':\"\"\"The density of the carrier used in calculating mass of the carrier.\"\"\",\n",
    "'bgDensity_source':\"\"\"Currently, either the carrier density as reported in the IngredientComment for the carrier or\n",
    "the default.\"\"\",\n",
    "'carrier_mass':\"\"\"The calculated mass of the carrier for this disclosure.\"\"\",\n",
    "'job_mass':\"\"\"The mass of the entire hydraulic fracking fluid as calculated from the carrier mass and carrier volume.\"\"\",\n",
    "'MassIngredient':\"\"\"A FracFocus supplied value of the mass of the chemical in the record (in pounds).  This field\n",
    "is not documented in the FracFocus README.txt file. These mass values are absent for more than 30% of records\n",
    "and we have found that some values are very inconsistent with other information in a disclosure.  Where we can verify its internal consistency,\n",
    "we use this field to compare to and validate our calcMass value.\"\"\",\n",
    "'ingKeyPresent':\"\"\" (used internally)\"\"\",\n",
    "'density_from_comment':\"\"\" the density of the chemical in the record as reported in the IngredientComment \"\"\",\n",
    "# 'categoryCAS':\"\"\" This is the category of status and processing performed in the translation of CASNumber to an authoritative value.  \"\"\",\n",
    "'DTXSID':\"\"\"EPA's code for this CAS number\"\"\",\n",
    "'is_on_CWA':\"\"\"indicates if the chemical is on the Clean Water Act list as compiled in EPA's CompTox\"\"\",\n",
    "'is_on_DWSHA':\"\"\"indicates if the chemical is on the EPA's Drinking Water Safety and Health Advisory list\"\"\",        \n",
    "'dup_rec':\"\"\"This flag indicates that a record is a redundant duplicate of another in the same disclosure. These\n",
    "are removed in the standard filter.\"\"\",\n",
    "'massComp':\"\"\"indicates the degree of agreement between calcMass and MassIngredient for a given record.  The smaller the number, the better the agreement. \n",
    "\"\"\",\n",
    "'massCompFlag':\"\"\"indicates if calcMass and MassIngredient are out of tolerance.\"\"\", # changed 'in' to 'out of' Dec. 20,2021\n",
    "'syn_code': \"\"\"indicates the match of the raw IngredientName with the synonym reference.\"\"\",\n",
    "'is_water_carrier':\"\"\"This flag indicates the carrier record of the disclosure. \"\"\",\n",
    "'job_mass_MI':\"\"\"The mass of the entire job (in pounds) as calculated using MassIngredient.\"\"\",\n",
    "'is_on_prop65':\"\"\"The chemical in this record is on the California Prop 65  list.\"\"\",\n",
    "'is_on_TEDX':\"\"\" The chemical in this record is on the Endocrine Disruptor Exchange list. \"\"\",\n",
    "'rawName':\"\"\" (internal use) \"\"\",\n",
    "'cleanName':\"\"\" (internal use) \"\"\",\n",
    "'xlateName':\"\"\" (internal use)  \"\"\",\n",
    "'status':\"\"\" (internal use)  \"\"\",\n",
    "'FFVersion': \"\"\"Most disclosures of FFVersion 1 (2011 - May 2013) do not have chemical records, only metadata.  In earlier versions,\n",
    "Open-FF used the SkyTruth archive to include the chemical records from these fracking events but\n",
    "that has been discontinued.\"\"\",\n",
    "'APINumber':\"\"\"This field is treated as a TEXT field, not a number, to maintain the integrity of any leading zeros.  Note that there\n",
    "are not dashes in the APINumbers reported in the bulk download, despite what FracFocus indicates.\"\"\",\n",
    "'UploadKey':\"\"\"The values in this field are unique identifiers of individual disclosures.\"\"\",\n",
    "'WellName':\"\"\"While the WellName should be a unique identifier for a given well, it is often not used consistently\n",
    "when a well has multiple disclosures.\"\"\",\n",
    "'TVD':\"\"\"Measurement in feet.\"\"\",\n",
    "'pub_delay_days':\"\"\"Number of days between JobEndDate and day of first detection in Open-FF. Note that these values\n",
    "are only valid for JobEndDates after Sept. 2018 (when Open-FF started keeping track).\"\"\",\n",
    "'in_std_filtered':\"\"\"This True/False flag indicates the records that are included in the standard filtered set. \n",
    "This is generated in the Analysis_set.py module\"\"\",\n",
    "'date_added':\"\"\"Indicates the date that the given disclosure is first detected in Open-FF. Note that these values\n",
    "are only valid for JobEndDates after Sept. 2018 (when Open-FF started keeping track).\"\"\",\n",
    "'carrier_problem_flags': \"\"\"For those disclosures that detect problems when Open-FF is determining the carrier record, this \n",
    "field indicates the type(s) of issues found.  See table below for codes.\"\"\",\n",
    "'carrier_status': \"\"\"indicates the method by which the carrier record was identified (if possible). See table below for description of detection criteria\"\"\",\n",
    "'IngredientKey':\"\"\"FracFocus-created key that is a unique number identifying a specific record in the whole data base. \"\"\",\n",
    "'is_on_PFAS_list':\"\"\"True/False flag indicating if the bgCAS of the given record is on EPA's master list of PFAS chemicals\n",
    "and precursors\"\"\",\n",
    "'is_on_diesel':\"\"\"True/False flag indicating if the bgCAS of the given record is on EPA's list that 'represent the most appropriate interpretation of the statutory term 'diesel fuels' \n",
    "to use for permitting diesel fuels hydraulic fracturing under the UIC Program nationwide.'\"\"\",\n",
    "'is_on_AQ_CWA':\"\"\"True/False flag indicating if the bgCAS of the given record is on EPA CompTox WATERQUALCRIT list\"\"\",\n",
    "'is_on_HH_CWA':\"\"\"True/False flag indicating if the bgCAS of the given record is on EPA CompTox NWATRQHHC list\"\"\",\n",
    "'is_on_NPDWR':\"\"\"True/False flag indicating if the bgCAS of the given record is on National Primary Drinking Water Regulation list\"\"\",\n",
    "'is_on_IRIS':\"\"\"True/False flag indicating if the bgCAS of the given record is on EPA CompTox IRIS list\"\"\",\n",
    "'is_on_UVCB':\"\"\"True/False flag indicating if the bgCAS of the given record is on the TSCA 'Unknown, Variable composition, Complex\n",
    "reaction products and Biological' materials list\"\"\",\n",
    "'epa_pref_name':\"\"\"EPA's \"preferred name\" for this chemical.\"\"\",     \n",
    "'has_water_carrier':\"\"\"True/False flag indicating if this disclosure has an identified water carrier record that is used\n",
    "to calculate mass of the chemicals in the disclosure.\"\"\",\n",
    "'is_valid_cas':\"\"\"True/False flag to distinguish bgCAS values that are valid from others 'ambiguousID' and 'sysAppMeta'. Note that 'conflictingID' and 'proprietary' are valid.\"\"\",\n",
    "'total_percent_all_records':\"\"\"a disclosure-level sum of PercentHFJob for all records in a disclosure.\"\"\",\n",
    "'total_percent_of_valid':\"\"\"a disclosure-level sum of PercentHFJob for just the valid records in a disclosure. For this measure, 'proprietary' and \n",
    "'conflicting' records are considered valid.\"\"\",\n",
    "'IngredientComment':\"\"\"Lots of different types of info show up in this field: density of the chemical in the record, contact info for a proprietary\n",
    "claim, etc.\"\"\",\n",
    "'loc_name_mismatch':\"\"\"Indicates that the StateName or CountyName provided in the original disclosure do not match the expected names\n",
    "given the codes embedded in the APINumber. Sometimes these are simply alternative spellings of the expected name, other times they are \n",
    "completely wrong.\"\"\",\n",
    "'loc_within_county':\"\"\"True/False flag value of whether the lat/log values reported in the original disclosure are actually in the reported county.\"\"\",\n",
    "'loc_within_state':\"\"\"True/False flag value of whether the lat/log values reported in the original disclosure are actually in the reported state.\"\"\",\n",
    "'Projection': \"\"\"While the reported 'Latitude' and 'Longitude' are in this projection, all 'bgLatitude' / 'bgLongitude' pairs are converted to \n",
    "'WGS84'.\"\"\",\n",
    "'rq_lbs':\"\"\"The 'reportable quantity' for this chemical (in pounds), see \n",
    "<a href=\"https://www.law.cornell.edu/cfr/text/40/302.4\">this website </a>\"\"\",\n",
    "'api10':\"\"\"A 10 character version of APINumber. For almost all uses, this shortened number is sufficient to identify any particular well.\n",
    "In many cases, states do not even use the extra 4 digits of a 'full' API number.\"\"\",\n",
    "'bgLocationSource': \"\"\"Indicates the source of the bgLatitude and bgLongitude values. Typically, they are taken\n",
    "from the FracFocus disclosures, but in cases where those values appear error-ridden, Open-FF uses values from\n",
    "a state-derived source, such as Texas RR Commission or Ohio Department of Natural Resources.\"\"\",\n",
    "'iupac_name':\"\"\"The official IUPAC name for the given substance.\"\"\",\n",
    "'bgFederalLand':\"\"\"A True/False indication of whether the given well is on Federal lands.  Unlike the FracFocus field, FederalWell,\n",
    "this one is generated in OpenFF by overlaying the lat/lon of the well with an authoratative USGS geographic resource,\n",
    "PADUS-3, that characterizes all federal lands (the field `Own_Name` == 'FED') (currently in beta)\"\"\",\n",
    "'bgStateLand':\"\"\"A True/False indication of whether the given well is on State lands.  This is generated in OpenFF by overlaying the lat/lon of the well with an authoratative USGS geographic resource,\n",
    "PADUS-3, that characterizes all state lands (the field `Own_Name` == 'STAT') (currently in beta)\"\"\",\n",
    "'bgNativeAmericanLand':\"\"\"A True/False indication of whether the given well is on land designated as tribal.  Unlike the FracFocus field, IndianWell,\n",
    "this one is generated in OpenFF by overlaying the lat/lon of the well with an authoratative USGS geographic resource,\n",
    "PADUS-3, that characterizes identifies native lands by using the Census shape files for American Indian, Alaska Native Areas \n",
    "and Hawaiian Home Lands. (currently in beta)\"\"\",\n",
    "'stLatitude':\"\"\"a latitude value that comes from a state regulatory agency for the given well.  When FracFocus location values are\n",
    "flagged as erroneous, this state-derived value is used instead for bgLatitude.\"\"\",\n",
    "'stLongitude':\"\"\"a longitude value that comes from a state regulatory agency for the given well. When FracFocus location values are\n",
    "flagged as erroneous, this state-derived value is used instead for bgLongitude.\"\"\",\n",
    "        \n",
    "       }\n",
    "\n",
    "ffdesc = {'JobStartDate':\"\"\"The date on which the hydraulic fracturing job was initiated.  Does not include site preparation or setup.\"\"\",\n",
    "'JobEndDate':\"\"\"The date on which the hydraulic fracturing job was completed.  Does not include site teardown.\"\"\",\n",
    "'APINumber':\"\"\"The American Petroleum Institute well identification number formatted as follows xx-xxx-xxxxx0000 Where: First two digits \n",
    "represent the state, second three digits represent the county, third 5 digits represent the well.\"\"\",\n",
    "'StateNumber':\"\"\"The first two digits of the API number.  Range is from 01-50.\"\"\",\n",
    "'CountyNumber':\"\"\"The 3 digit county code.\"\"\",\n",
    "'OperatorName':\"\"\"The name of the operator.\"\"\",\n",
    "'WellName':\"\"\"The name of the well.\"\"\",\n",
    "'Latitude':\"\"\"The lines that circle the earth horizontally, running side to side at equal distances apart on the earth.   Latitude is typically \n",
    "expressed in degrees North/ South.  In the FracFocus system these lines are shown in decimal degrees and must be between 15 and 75.\"\"\",\n",
    "'Longitude':\"\"\"The lines that circle the earth vertically, running top to bottom that are equal distances apart at the equator \n",
    "and merge at the geographic top and bottom of the earth.  Longitude is typically expressed in degrees East/ West.  In the FracFocus \n",
    "system the number representing these  lines are shown in decimal degrees and must be between -180 and -163 Note: Longitude number must \n",
    "be preceded by a negative sign.\"\"\",\n",
    "'Projection':\"\"\"The geographic coordinate system to which the latitude and longitude are related. In the FracFocus \n",
    "system the projection systems allowed are NAD (North American Datum) 27 or 83 and UTM (Universal Transverse Mercator).\"\"\",\n",
    "'TVD':\"\"\"The vertical distance from a point in the well (usually the current or final depth) to a point at the surface, usually the \n",
    "elevation of the rotary kelly bushing.\"\"\",\n",
    "'TotalBaseWaterVolume':\"\"\"The total volume of water used as a carrier fluid for the hydraulic fracturing job (in gallons).\"\"\",\n",
    "'TotalBaseNonWaterVolume':\"\"\"The total volume of non water components used as a carrier fluid for the hydraulic fracturing job (in gallons)\"\"\",\n",
    "'StateName':\"\"\"The name of the state where the surface location of the well resides.  Calculated from the API number.\"\"\",\n",
    "'CountyName':\"\"\"The name of the county were the surface location of the well resides.  Calculated from the API number.\"\"\",\n",
    "'FFVersion':\"\"\"A key which designates which version of FracFocus was used when the disclosure was submitted.\"\"\",\n",
    "'FederalWell':\"\"\"True = Yes<br>False = No.\"\"\",\n",
    "'UploadKey':\"\"\"Foreign key linking to the RegistryUpload table.\"\"\",\n",
    "'TradeName':\"\"\"The name of the product as defined by the supplier.\"\"\",\n",
    "'Supplier':\"\"\"The name of the company that supplied the product for the hydraulic fracturing job (Usually the service company).\"\"\",\n",
    "'Purpose':\"\"\"The reason the product was used (e.g. Surfactant, Biocide, Proppant).\"\"\",\n",
    "'IngredientName':\"\"\"Name of the chemical or for Trade Secret chemicals the chemical family name.\"\"\",\n",
    "'CASNumber':\"\"\"The Chemical Abstract Service identification number.\"\"\",\n",
    "'PercentHighAdditive':\"\"\"The percent of the ingredient in the Trade Name product in % (Top of the range from MSDS).\"\"\",\n",
    "'PercentHFJob':\"\"\"The amount of the ingredient in the total hydraulic fracturing volume in % by Mass.\"\"\",\n",
    "'IngredientComment':\"\"\"Any comments related to the specific ingredient.\"\"\",\n",
    "'MassIngredient': \"\"\"(definition not provided)\"\"\",\n",
    "'IndianWell': \"\"\"(definition not provided)\"\"\",\n",
    "'IngredientKey':\"\"\"(definition not provided)\"\"\",\n",
    "'IngredientComment':\"\"\"Any comments related to the specific ingredient.\"\"\"\n",
    "       }\n",
    "descs = []\n",
    "kys = list(desc.keys())\n",
    "for k in kys:\n",
    "    descs.append(desc[k].replace('\\n',' '))\n",
    "desc_df = pd.DataFrame({'fieldName':kys,'Open-FF description':descs})\n",
    "desc_df.head()\n",
    "\n",
    "ffdescs = []\n",
    "kys = list(ffdesc.keys())\n",
    "for k in kys:\n",
    "    ffdescs.append(ffdesc[k].replace('\\n',' '))\n",
    "ffdesc_df = pd.DataFrame({'fieldName':kys,'FracFocus description':ffdescs})\n",
    "ffdesc_df['source'] = 'original FF'\n",
    "\n",
    "table = pd.merge(all_fn_df,desc_df,on='fieldName',how='outer')\n",
    "table.Database_Tables.fillna('none',inplace=True)\n",
    "table = pd.merge(table,ffdesc_df,on='fieldName',how='left').reset_index(drop=True)\n",
    "# no_desc = table[(table['Open-FF description'].isna())&(table['FracFocus description'].isna())].fieldName.tolist()\n",
    "\n",
    "# make a csv file:\n",
    "table.to_csv('./out/data_dictionary.csv')\n",
    "\n",
    "table.fieldName = '<b><h3>'+table.fieldName+'</h3></b>'\n",
    "table['FracFocus description'].fillna(' ',inplace=True) \n",
    "table['Open-FF description'].fillna(' ',inplace=True) \n",
    "table['source'].fillna('generated',inplace=True) \n",
    "\n",
    "table['field Name, [tables]'] = table.fieldName + ' ' + table.Database_Tables.astype('str') \n",
    "iShow(table[['field Name, [tables]','FracFocus description','Open-FF description','source',\n",
    "            'Num','Unique','Data_type']].sort_values('field Name, [tables]'),\n",
    "      classes=\"display compact cell-border\", \n",
    "      columnDefs=[{\"className\":\"dt-left\",  \"targets\": \"_all\"}],\n",
    "     paging=False,footer=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "016d6d5d-55cf-4a9f-868d-f1eb62690fb3",
   "metadata": {},
   "source": [
    "## Carrier detection sets:\n",
    "Among the filters below, **s1** finds the majority of water carriers.  However, there is no single set of criteria that can be used to identify the water carrier record(s) for all FracFocus disclosures.  Therefore the other filters are employed to catch many other disclosure patterns without needing to curate each by hand.\n",
    "\n",
    "| Set name | description | Criteria to be detected|\n",
    "| :--: | --- | :-- |\n",
    "| **s1** | Primary filter; most recent disclosures are detected with this|- Only one record whose `Purpose` is \"carrier\" (or related)<br>- `bgCAS` is '7732-18-5'<br>- at least 50% `PercentHFJob`<br>- total % of disclosure is 95% > x > 105%|\n",
    "| **s2** |More than one record as the carrier;<br>covers situations, for example, where there are two water records<br> (fresh and produced) and where other chemicals are also labeled as part of the carrier. <br>It is important to include all water carrier records <br>to avoid underestimating carrier mass  |- More than one record whose `Purpose` is \"carrier\" (or related)<br>- at least one `bgCAS` is '7732-18-5'<br>- total of water records is at least 50% `PercentHFJob`<br>- total % of disclosure is 95% > x > 105%|\n",
    "| **s3** |No carrier records labeled; but clear water record with typical percentage |- `bgCAS` is '7732-18-5'<br>- at least 40% `PercentHFJob`<br>- `IngredientName` contains phrase \"including mix water\"<br>- total % of disclosure is 95% > x > 105%|\n",
    "| **s4** | Like s3, but CAS number missing; still obvious water record |- `CASNumber` is empty <br>- at least 60% `PercentHFJob`<br>- `IngredientName` contains phrase \"including mix water\"<br>- total % of disclosure is 95% > x > 105%|\n",
    "| **s5** | Like s1 but no carrier records are labeled; |<br>- `bgCAS` is '7732-18-5'<br>- at least 50% `PercentHFJob`<br>- total % of disclosure is 95% > x > 105%|\n",
    "| **s6** |`CASNumber` missing but clear carrier label |- `bgCAS` is ambiguousID<br>- single record with a carrier `Purpose`<br>- `IngredientName` is either \"carrier\" (or related) or has \"water\" in it<br>- `TradeName` has \"water\" in it<br>- 50% < %HFJob < 100%<br>- total % of disclosure is 95% > x > 105%|\n",
    "| **s7** |Like s1, but for \"salted\" water<br>Note that even though the record is labeled with the salt CAS number,<br> the predominant mass is water |- Only one record whose `Purpose` is \"carrier\" (or related)<br>- `bgCAS` is either '7747-40-7' (kcl) or '7647-14-5' (nacl)<br>- at least 50% `PercentHFJob`<br>- total % of disclosure is 95% > x > 105%|\n",
    "| **s8** |Common pattern in the older disclosures (incl. SkyTruth archive)|- `bgCAS` is ambiguousID or 7732-18-5<br>- `IngredientName` is MISSING<br>- `Purpose` is \"unrecorded purpose\"<br>- `TradeName` has either \"water\" or \"brine\"<br>- can be one or two records in each disclosure<br>- 50% < sum of `PercentHFJob` of these records < 100%<br>- total % of disclosure is 95% > x > 105%|  \n",
    "| **s9** |Common pattern in the older disclosures (incl. SkyTruth archive)|- `bgCAS` is ambiguousID or 7732-18-5<br>- `IngredientName` is MISSING<br>- `Purpose` is one of the standard carrier words or phrases<br>- `TradeName` has either \"water\" or \"brine\"<br>- can be one or two records in each disclosure<br>- 50% < sum of `PercentHFJob` of these records < 100%<br>- total % of disclosure is 95% > x > 105%|  \n",
    "| **s10** |A pattern seen in later disclosures: <br>the carrier is only reported in the top part of the <br>systems approach section under the \"Listed Below\"  `CASNumber`.<br>The actual `PercentHFJob` value isn't even reported in the PDF <br>version, but is in the bulk download.|- `CASNumber` is \"Listed Below\"<br>- record has a carrier `Purpose`<br>- `PercentHFJob`>50 %<br>- `TradeName` has \"water\" in it<br>- total % of disclosure is 95% > x > 105%|  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7c357ed-2e8a-42b7-b2a1-2075f8cfeafe",
   "metadata": {},
   "source": [
    "## Disclosures with detected problems for determination of water carrier ID"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c13f887-2e5a-43df-985f-a6e18198bca9",
   "metadata": {},
   "source": [
    "| code | description |\n",
    "| :---: | --- |\n",
    "| **0** | Disclosure has no valid chemical records. |\n",
    "| **1** | `TotalBaseWaterVolume` is empty or 0 gallons.|\n",
    "| **2** | None of the chemical records have non-zero `PercentHFJob`.|\n",
    "| **3** | The sum of `PercentHFJob` values for valid CAS records is larger than limit (105%)|\n",
    "| **4** | The sum of `PercentHFJob` values for all records excluding SystemApproach is larger than limit (105%)|\n",
    "| **5** | `PercentHFJob` of all \"proppant\" records is greater than 50% (not used after v16) |\n",
    "| **6** | The sum of `PercentHFJob` values for all records is less than 90% - a partial disclosure|\n",
    "| **7** | `PercentHFJob` of Nitrogen or Carbon Dioxide records is greater than 50% (so carrier will be smaller) (not used after v16) |\n",
    "| **8** | `PercentHFJob` of Chlorine dioxide records is 100% (it is typically an additive to the water; not a replacement) (added 3/2023, after v16). |\n",
    "| **9** | `PercentHFJob` of Nonwater carrier record too large (>50%) (added 3/2023, after v16). |\n"
   ]
  }
 ],
 "metadata": {
  "hide_input": true,
  "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.10.12"
  },
  "toc-autonumbering": false,
  "toc-showcode": false,
  "toc-showmarkdowntxt": false,
  "toc-showtags": true
 },
 "nbformat": 4,
 "nbformat_minor": 5
}