05_Exploring_Habitats.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A very quick example of pulling out unique values from one field and writing them as a CSV to a local file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# You can add \"-100k\" or \"-1M\" before the \".\" to load smaller data sets for testing\n",
    "idb_df = sqlContext.read.parquet(\"/guoda/data/idigbio-20190612T171757.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "115173471"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idb_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- barcodevalue: string (nullable = true)\n",
      " |-- basisofrecord: string (nullable = true)\n",
      " |-- bed: string (nullable = true)\n",
      " |-- canonicalname: string (nullable = true)\n",
      " |-- catalognumber: string (nullable = true)\n",
      " |-- class: string (nullable = true)\n",
      " |-- collectioncode: string (nullable = true)\n",
      " |-- collectionid: string (nullable = true)\n",
      " |-- collectionname: string (nullable = true)\n",
      " |-- collector: string (nullable = true)\n",
      " |-- commonname: string (nullable = true)\n",
      " |-- continent: string (nullable = true)\n",
      " |-- coordinateuncertainty: float (nullable = true)\n",
      " |-- country: string (nullable = true)\n",
      " |-- countrycode: string (nullable = true)\n",
      " |-- county: string (nullable = true)\n",
      " |-- data: struct (nullable = true)\n",
      " |    |-- coreid: string (nullable = true)\n",
      " |    |-- dc:rights: string (nullable = true)\n",
      " |    |-- dcterms:accessRights: string (nullable = true)\n",
      " |    |-- dcterms:bibliographicCitation: string (nullable = true)\n",
      " |    |-- dcterms:language: string (nullable = true)\n",
      " |    |-- dcterms:license: string (nullable = true)\n",
      " |    |-- dcterms:modified: string (nullable = true)\n",
      " |    |-- dcterms:references: string (nullable = true)\n",
      " |    |-- dcterms:rights: string (nullable = true)\n",
      " |    |-- dcterms:rightsHolder: string (nullable = true)\n",
      " |    |-- dcterms:source: string (nullable = true)\n",
      " |    |-- dcterms:type: string (nullable = true)\n",
      " |    |-- dwc:VerbatimEventDate: string (nullable = true)\n",
      " |    |-- dwc:acceptedNameUsage: string (nullable = true)\n",
      " |    |-- dwc:acceptedNameUsageID: string (nullable = true)\n",
      " |    |-- dwc:accessRights: string (nullable = true)\n",
      " |    |-- dwc:associatedMedia: string (nullable = true)\n",
      " |    |-- dwc:associatedOccurrences: string (nullable = true)\n",
      " |    |-- dwc:associatedOrganisms: string (nullable = true)\n",
      " |    |-- dwc:associatedReferences: string (nullable = true)\n",
      " |    |-- dwc:associatedSequences: string (nullable = true)\n",
      " |    |-- dwc:associatedTaxa: string (nullable = true)\n",
      " |    |-- dwc:basisOfRecord: string (nullable = true)\n",
      " |    |-- dwc:bed: string (nullable = true)\n",
      " |    |-- dwc:behavior: string (nullable = true)\n",
      " |    |-- dwc:catalogNumber: string (nullable = true)\n",
      " |    |-- dwc:class: string (nullable = true)\n",
      " |    |-- dwc:classs: string (nullable = true)\n",
      " |    |-- dwc:collectionCode: string (nullable = true)\n",
      " |    |-- dwc:collectionID: string (nullable = true)\n",
      " |    |-- dwc:continent: string (nullable = true)\n",
      " |    |-- dwc:coordinatePrecision: string (nullable = true)\n",
      " |    |-- dwc:coordinateUncertaintyInMeters: string (nullable = true)\n",
      " |    |-- dwc:country: string (nullable = true)\n",
      " |    |-- dwc:countryCode: string (nullable = true)\n",
      " |    |-- dwc:county: string (nullable = true)\n",
      " |    |-- dwc:dataGeneralizations: string (nullable = true)\n",
      " |    |-- dwc:datasetID: string (nullable = true)\n",
      " |    |-- dwc:datasetName: string (nullable = true)\n",
      " |    |-- dwc:dateIdentified: string (nullable = true)\n",
      " |    |-- dwc:day: string (nullable = true)\n",
      " |    |-- dwc:decimalLatitude: string (nullable = true)\n",
      " |    |-- dwc:decimalLongitude: string (nullable = true)\n",
      " |    |-- dwc:disposition: string (nullable = true)\n",
      " |    |-- dwc:dynamicProperties: string (nullable = true)\n",
      " |    |-- dwc:earliestAgeOrLowestStage: string (nullable = true)\n",
      " |    |-- dwc:earliestEonOrLowestEonothem: string (nullable = true)\n",
      " |    |-- dwc:earliestEpochOrLowestSeries: string (nullable = true)\n",
      " |    |-- dwc:earliestEraOrLowestErathem: string (nullable = true)\n",
      " |    |-- dwc:earliestPeriodOrLowestSystem: string (nullable = true)\n",
      " |    |-- dwc:endDayOfYear: string (nullable = true)\n",
      " |    |-- dwc:establishmentMeans: string (nullable = true)\n",
      " |    |-- dwc:eventDate: string (nullable = true)\n",
      " |    |-- dwc:eventID: string (nullable = true)\n",
      " |    |-- dwc:eventRemarks: string (nullable = true)\n",
      " |    |-- dwc:eventTime: string (nullable = true)\n",
      " |    |-- dwc:family: string (nullable = true)\n",
      " |    |-- dwc:fieldNotes: string (nullable = true)\n",
      " |    |-- dwc:fieldNumber: string (nullable = true)\n",
      " |    |-- dwc:footprintSRS: string (nullable = true)\n",
      " |    |-- dwc:footprintSpatialFit: string (nullable = true)\n",
      " |    |-- dwc:footprintWKT: string (nullable = true)\n",
      " |    |-- dwc:formation: string (nullable = true)\n",
      " |    |-- dwc:genus: string (nullable = true)\n",
      " |    |-- dwc:geodeticDatum: string (nullable = true)\n",
      " |    |-- dwc:geologicalContextID: string (nullable = true)\n",
      " |    |-- dwc:georeferenceProtocol: string (nullable = true)\n",
      " |    |-- dwc:georeferenceRemarks: string (nullable = true)\n",
      " |    |-- dwc:georeferenceSources: string (nullable = true)\n",
      " |    |-- dwc:georeferenceVerificationStatus: string (nullable = true)\n",
      " |    |-- dwc:georeferencedBy: string (nullable = true)\n",
      " |    |-- dwc:georeferencedDate: string (nullable = true)\n",
      " |    |-- dwc:group: string (nullable = true)\n",
      " |    |-- dwc:habitat: string (nullable = true)\n",
      " |    |-- dwc:higherClassification: string (nullable = true)\n",
      " |    |-- dwc:higherGeography: string (nullable = true)\n",
      " |    |-- dwc:higherGeographyID: string (nullable = true)\n",
      " |    |-- dwc:highestBiostratigraphicZone: string (nullable = true)\n",
      " |    |-- dwc:identificationID: string (nullable = true)\n",
      " |    |-- dwc:identificationQualifier: string (nullable = true)\n",
      " |    |-- dwc:identificationReferences: string (nullable = true)\n",
      " |    |-- dwc:identificationRemarks: string (nullable = true)\n",
      " |    |-- dwc:identificationVerificationStatus: string (nullable = true)\n",
      " |    |-- dwc:identifiedBy: string (nullable = true)\n",
      " |    |-- dwc:individualCount: string (nullable = true)\n",
      " |    |-- dwc:informationWithheld: string (nullable = true)\n",
      " |    |-- dwc:infraspecificEpithet: string (nullable = true)\n",
      " |    |-- dwc:institutionCode: string (nullable = true)\n",
      " |    |-- dwc:institutionID: string (nullable = true)\n",
      " |    |-- dwc:island: string (nullable = true)\n",
      " |    |-- dwc:islandGroup: string (nullable = true)\n",
      " |    |-- dwc:kingdom: string (nullable = true)\n",
      " |    |-- dwc:language: string (nullable = true)\n",
      " |    |-- dwc:latestAgeOrHighestStage: string (nullable = true)\n",
      " |    |-- dwc:latestEonOrHighestEonothem: string (nullable = true)\n",
      " |    |-- dwc:latestEpochOrHighestSeries: string (nullable = true)\n",
      " |    |-- dwc:latestEraOrHighestErathem: string (nullable = true)\n",
      " |    |-- dwc:latestPeriodOrHighestSystem: string (nullable = true)\n",
      " |    |-- dwc:lifeStage: string (nullable = true)\n",
      " |    |-- dwc:lithostratigraphicTerms: string (nullable = true)\n",
      " |    |-- dwc:locality: string (nullable = true)\n",
      " |    |-- dwc:locationAccordingTo: string (nullable = true)\n",
      " |    |-- dwc:locationID: string (nullable = true)\n",
      " |    |-- dwc:locationRemarks: string (nullable = true)\n",
      " |    |-- dwc:lowestBiostratigraphicZone: string (nullable = true)\n",
      " |    |-- dwc:materialSampleID: string (nullable = true)\n",
      " |    |-- dwc:maximumDepthInMeters: string (nullable = true)\n",
      " |    |-- dwc:maximumElevationInMeters: string (nullable = true)\n",
      " |    |-- dwc:member: string (nullable = true)\n",
      " |    |-- dwc:minimumDepthInMeters: string (nullable = true)\n",
      " |    |-- dwc:minimumElevationInMeters: string (nullable = true)\n",
      " |    |-- dwc:modified: string (nullable = true)\n",
      " |    |-- dwc:month: string (nullable = true)\n",
      " |    |-- dwc:municipality: string (nullable = true)\n",
      " |    |-- dwc:nameAccordingTo: string (nullable = true)\n",
      " |    |-- dwc:namePublishedIn: string (nullable = true)\n",
      " |    |-- dwc:namePublishedInID: string (nullable = true)\n",
      " |    |-- dwc:namePublishedInYear: string (nullable = true)\n",
      " |    |-- dwc:nomenclaturalCode: string (nullable = true)\n",
      " |    |-- dwc:nomenclaturalStatus: string (nullable = true)\n",
      " |    |-- dwc:occurrenceDetails: string (nullable = true)\n",
      " |    |-- dwc:occurrenceID: string (nullable = true)\n",
      " |    |-- dwc:occurrenceRemarks: string (nullable = true)\n",
      " |    |-- dwc:occurrenceStatus: string (nullable = true)\n",
      " |    |-- dwc:order: string (nullable = true)\n",
      " |    |-- dwc:organismID: string (nullable = true)\n",
      " |    |-- dwc:organismName: string (nullable = true)\n",
      " |    |-- dwc:organismQuantity: string (nullable = true)\n",
      " |    |-- dwc:organismQuantityType: string (nullable = true)\n",
      " |    |-- dwc:originalNameUsage: string (nullable = true)\n",
      " |    |-- dwc:originalNameUsageID: string (nullable = true)\n",
      " |    |-- dwc:otherCatalogNumbers: string (nullable = true)\n",
      " |    |-- dwc:ownerInstitutionCode: string (nullable = true)\n",
      " |    |-- dwc:parentNameUsage: string (nullable = true)\n",
      " |    |-- dwc:phylum: string (nullable = true)\n",
      " |    |-- dwc:pointRadiusSpatialFit: string (nullable = true)\n",
      " |    |-- dwc:preparations: string (nullable = true)\n",
      " |    |-- dwc:previousIdentifications: string (nullable = true)\n",
      " |    |-- dwc:recordNumber: string (nullable = true)\n",
      " |    |-- dwc:recordedBy: string (nullable = true)\n",
      " |    |-- dwc:reproductiveCondition: string (nullable = true)\n",
      " |    |-- dwc:rights: string (nullable = true)\n",
      " |    |-- dwc:rightsHolder: string (nullable = true)\n",
      " |    |-- dwc:sampleSizeValue: string (nullable = true)\n",
      " |    |-- dwc:samplingEffort: string (nullable = true)\n",
      " |    |-- dwc:samplingProtocol: string (nullable = true)\n",
      " |    |-- dwc:scientificName: string (nullable = true)\n",
      " |    |-- dwc:scientificNameAuthorship: string (nullable = true)\n",
      " |    |-- dwc:scientificNameID: string (nullable = true)\n",
      " |    |-- dwc:sex: string (nullable = true)\n",
      " |    |-- dwc:specificEpithet: string (nullable = true)\n",
      " |    |-- dwc:startDayOfYear: string (nullable = true)\n",
      " |    |-- dwc:stateProvince: string (nullable = true)\n",
      " |    |-- dwc:subgenus: string (nullable = true)\n",
      " |    |-- dwc:taxonID: string (nullable = true)\n",
      " |    |-- dwc:taxonRank: string (nullable = true)\n",
      " |    |-- dwc:taxonRemarks: string (nullable = true)\n",
      " |    |-- dwc:taxonomicStatus: string (nullable = true)\n",
      " |    |-- dwc:typeStatus: string (nullable = true)\n",
      " |    |-- dwc:verbatimCoordinateSystem: string (nullable = true)\n",
      " |    |-- dwc:verbatimCoordinates: string (nullable = true)\n",
      " |    |-- dwc:verbatimDepth: string (nullable = true)\n",
      " |    |-- dwc:verbatimElevation: string (nullable = true)\n",
      " |    |-- dwc:verbatimEventDate: string (nullable = true)\n",
      " |    |-- dwc:verbatimLatitude: string (nullable = true)\n",
      " |    |-- dwc:verbatimLocality: string (nullable = true)\n",
      " |    |-- dwc:verbatimLongitude: string (nullable = true)\n",
      " |    |-- dwc:verbatimSRS: string (nullable = true)\n",
      " |    |-- dwc:verbatimTaxonRank: string (nullable = true)\n",
      " |    |-- dwc:vernacularName: string (nullable = true)\n",
      " |    |-- dwc:waterBody: string (nullable = true)\n",
      " |    |-- dwc:year: string (nullable = true)\n",
      " |    |-- id: string (nullable = true)\n",
      " |    |-- idigbio:recordId: string (nullable = true)\n",
      " |    |-- symbiota:recordEnteredBy: string (nullable = true)\n",
      " |    |-- symbiota:verbatimScientificName: string (nullable = true)\n",
      " |-- datasetid: string (nullable = true)\n",
      " |-- datecollected: timestamp (nullable = true)\n",
      " |-- datemodified: timestamp (nullable = true)\n",
      " |-- dqs: float (nullable = true)\n",
      " |-- earliestageorloweststage: string (nullable = true)\n",
      " |-- earliesteonorlowesteonothem: string (nullable = true)\n",
      " |-- earliestepochorlowestseries: string (nullable = true)\n",
      " |-- earliesteraorlowesterathem: string (nullable = true)\n",
      " |-- earliestperiodorlowestsystem: string (nullable = true)\n",
      " |-- etag: string (nullable = true)\n",
      " |-- eventdate: string (nullable = true)\n",
      " |-- family: string (nullable = true)\n",
      " |-- fieldnumber: string (nullable = true)\n",
      " |-- formation: string (nullable = true)\n",
      " |-- genus: string (nullable = true)\n",
      " |-- geologicalcontextid: string (nullable = true)\n",
      " |-- geopoint: struct (nullable = true)\n",
      " |    |-- lat: double (nullable = true)\n",
      " |    |-- lon: double (nullable = true)\n",
      " |-- group: string (nullable = true)\n",
      " |-- hasImage: boolean (nullable = true)\n",
      " |-- hasMedia: boolean (nullable = true)\n",
      " |-- highertaxon: string (nullable = true)\n",
      " |-- highestbiostratigraphiczone: string (nullable = true)\n",
      " |-- individualcount: float (nullable = true)\n",
      " |-- infraspecificepithet: string (nullable = true)\n",
      " |-- institutioncode: string (nullable = true)\n",
      " |-- institutionid: string (nullable = true)\n",
      " |-- institutionname: string (nullable = true)\n",
      " |-- kingdom: string (nullable = true)\n",
      " |-- latestageorhigheststage: string (nullable = true)\n",
      " |-- latesteonorhighesteonothem: string (nullable = true)\n",
      " |-- latestepochorhighestseries: string (nullable = true)\n",
      " |-- latesteraorhighesterathem: string (nullable = true)\n",
      " |-- latestperiodorhighestsystem: string (nullable = true)\n",
      " |-- lithostratigraphicterms: string (nullable = true)\n",
      " |-- locality: string (nullable = true)\n",
      " |-- lowestbiostratigraphiczone: string (nullable = true)\n",
      " |-- maxdepth: float (nullable = true)\n",
      " |-- maxelevation: float (nullable = true)\n",
      " |-- member: string (nullable = true)\n",
      " |-- mindepth: float (nullable = true)\n",
      " |-- minelevation: float (nullable = true)\n",
      " |-- municipality: string (nullable = true)\n",
      " |-- occurrenceid: string (nullable = true)\n",
      " |-- order: string (nullable = true)\n",
      " |-- phylum: string (nullable = true)\n",
      " |-- recordnumber: string (nullable = true)\n",
      " |-- recordset: string (nullable = true)\n",
      " |-- scientificname: string (nullable = true)\n",
      " |-- specificepithet: string (nullable = true)\n",
      " |-- startdayofyear: integer (nullable = true)\n",
      " |-- stateprovince: string (nullable = true)\n",
      " |-- taxonid: string (nullable = true)\n",
      " |-- taxonomicstatus: string (nullable = true)\n",
      " |-- taxonrank: string (nullable = true)\n",
      " |-- typestatus: string (nullable = true)\n",
      " |-- uuid: string (nullable = true)\n",
      " |-- verbatimeventdate: string (nullable = true)\n",
      " |-- verbatimlocality: string (nullable = true)\n",
      " |-- version: integer (nullable = true)\n",
      " |-- waterbody: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "idb_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "distinct_habitats = (idb_df\n",
    "                     .groupBy(col(\"data.dwc:habitat\"))\n",
    "                     .count()\n",
    "                     )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------------------------------------------+-----+\n",
      "|dwc:habitat                                             |count|\n",
      "+--------------------------------------------------------+-----+\n",
      "|disturbed thickets and fields near watercourses to ocean|1    |\n",
      "|epiphyte on fallen tree                                 |1    |\n",
      "|dry oamy soil                                           |1    |\n",
      "|epiphyte in monte                                       |1    |\n",
      "|monte                                                   |4    |\n",
      "|Bosque primario, suelo de arena blanca                  |1    |\n",
      "|disturbed forest & trailside                            |1    |\n",
      "|Floodplain.; Compact, dry soil.                         |1    |\n",
      "|In clay soil on top of sharp ridge                      |1    |\n",
      "|Beitemark. Blokk/jord. På jord på blokker i beitemark   |1    |\n",
      "+--------------------------------------------------------+-----+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Uncomment for testing, otherwise leave commented as it causes extra gathering work\n",
    "# in addition to the writing of the CSV below\n",
    "distinct_habitats.show(10, truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# toPandas is inefficient (15 minutes) but simpler than working with HDFS\n",
    "(distinct_habitats\n",
    " .toPandas()\n",
    " .to_csv(\"distinct_habitats.csv\", index=False, header=True)\n",
    " )"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "3 - PySpark Python3 Medium",
   "language": "python",
   "name": "pyspark3-med"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}