04_Parsing_Dynamic_Properties_into_Traits.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "from pyspark.sql.functions import abs, col, explode, length, hash, udf, when, date_format\n",
    "from pyspark.sql.types import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "idb_df = sqlContext.read.parquet(\"/guoda/data/idigbio-20190612T171757.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "idb_dyn = (idb_df\n",
    "               .filter(length(col(\"data.dwc:dynamicProperties\")) > 2)\n",
    "              .withColumn(\"props_str\", col(\"data.dwc:dynamicProperties\"))\n",
    "              )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10374395"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idb_dyn.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------------------------------+\n",
      "|props_str                                    |\n",
      "+---------------------------------------------+\n",
      "|{\"life stage\":\"adult\"}                       |\n",
      "|{\"sex\":\"Female\"}                             |\n",
      "|{\"measurements\":\"ecotone area\" }             |\n",
      "|{ \"solr_long_lat\": \"-71.303074,44.061185\" }  |\n",
      "|{ \"solr_long_lat\": \"-71.319924,44.041766\" }  |\n",
      "|{ \"solr_long_lat\": \"-71.304611,44.059277\" }  |\n",
      "|{ \"solr_long_lat\": \"-71.3000108,44.0602311\" }|\n",
      "|{ \"solr_long_lat\": \"-71.297795,44.062155\" }  |\n",
      "|{\"measurements\":\"SV 71, Tail 175\" }          |\n",
      "|{ \"solr_long_lat\": \"-109.74341,34.91267\" }   |\n",
      "+---------------------------------------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "idb_dyn.select(col(\"props_str\")).show(10, truncate=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Need a udf that returns a mapType of string:string that explode will expand"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'solr_long_lat': '-5.87403,30.49728', 'related_record_types': 'PreservedSpecimen|PreservedSpecimen', 'related_record_links': 'YPM-IP-530950|YPM-IP-530951'}\n"
     ]
    }
   ],
   "source": [
    "def json_to_map(s):\n",
    "    \"\"\"Convert a string containing JSON into a dictionary,\n",
    "    Skip flattening for now.\"\"\"\n",
    "    try:\n",
    "        return json.loads(s)\n",
    "    except:\n",
    "        return {}\n",
    "    \n",
    "json_to_map_udf = udf(json_to_map, MapType(StringType(), StringType()))\n",
    "\n",
    "print(json_to_map('{ \"solr_long_lat\": \"-5.87403,30.49728\", \"related_record_types\": \"PreservedSpecimen|PreservedSpecimen\", \"related_record_links\": \"YPM-IP-530950|YPM-IP-530951\" }'))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------------------------------+\n",
      "|props_map                                   |\n",
      "+--------------------------------------------+\n",
      "|Map(life stage -> adult)                    |\n",
      "|Map(sex -> Female)                          |\n",
      "|Map(measurements -> ecotone area)           |\n",
      "|Map(solr_long_lat -> -71.303074,44.061185)  |\n",
      "|Map(solr_long_lat -> -71.319924,44.041766)  |\n",
      "|Map(solr_long_lat -> -71.304611,44.059277)  |\n",
      "|Map(solr_long_lat -> -71.3000108,44.0602311)|\n",
      "|Map(solr_long_lat -> -71.297795,44.062155)  |\n",
      "|Map(measurements -> SV 71, Tail 175)        |\n",
      "|Map(solr_long_lat -> -109.74341,34.91267)   |\n",
      "+--------------------------------------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "idb_map = (idb_dyn\n",
    " .withColumn(\"props_map\", json_to_map_udf(col(\"props_str\")))\n",
    ")\n",
    "idb_map.select(col(\"props_map\")).show(10, truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[uuid: string, recordset: string, institutioncode: string, key: string, value: string]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idb_triples = (idb_map\n",
    " .select(col(\"uuid\"), \n",
    "         col(\"recordset\"),\n",
    "         col(\"institutioncode\"),\n",
    "         explode(col(\"props_map\")).alias(\"key\", \"value\"))\n",
    ")\n",
    "idb_triples.cache()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31170327"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "idb_triples.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------------------------+------------------------------------+---------------+--------------------+-------------------------+\n",
      "|uuid                                |recordset                           |institutioncode|key                 |value                    |\n",
      "+------------------------------------+------------------------------------+---------------+--------------------+-------------------------+\n",
      "|44c2a8fa-5a06-44de-83e8-d29146031cfe|271a9ce9-c6d3-4b63-a722-cb0adc48863f|mcz            |life stage          |adult                    |\n",
      "|eb6c2256-73ae-4c52-95c2-cfb3292ed994|271a9ce9-c6d3-4b63-a722-cb0adc48863f|mcz            |sex                 |Female                   |\n",
      "|abeb01a0-c691-4262-924c-667d74187021|637d0f2f-a0b4-4f33-a1ad-bd0ab18b620d|uaz            |measurements        |ecotone area             |\n",
      "|626b8a8e-8109-421b-b09d-8725c4e6cd79|76015dea-c909-4e6d-a8e1-3bf35763571e|ypm            |solr_long_lat       |-71.303074,44.061185     |\n",
      "|951adfcd-c4e4-4173-ab08-e3a54f516167|76015dea-c909-4e6d-a8e1-3bf35763571e|ypm            |solr_long_lat       |-71.319924,44.041766     |\n",
      "|2c07f947-9c30-4f79-b849-56cc86443b2e|76015dea-c909-4e6d-a8e1-3bf35763571e|ypm            |solr_long_lat       |-71.304611,44.059277     |\n",
      "|b1e9c3dc-2552-44ee-989d-8ed84e92c34b|76015dea-c909-4e6d-a8e1-3bf35763571e|ypm            |solr_long_lat       |-71.3000108,44.0602311   |\n",
      "|c4f81e35-f665-4ab4-a310-d2e2d2dc9ae8|76015dea-c909-4e6d-a8e1-3bf35763571e|ypm            |solr_long_lat       |-71.297795,44.062155     |\n",
      "|1d387871-2d55-42a4-bebe-cde0fb2800ae|637d0f2f-a0b4-4f33-a1ad-bd0ab18b620d|uaz            |measurements        |SV 71, Tail 175          |\n",
      "|46c4adc7-171b-4813-a9b6-40182fc1e6d9|7ae4d15d-62e2-459b-842a-446f921b9d3f|ypm            |solr_long_lat       |-109.74341,34.91267      |\n",
      "|1e16711f-c30f-4480-845c-79a154b1239d|7ae4d15d-62e2-459b-842a-446f921b9d3f|ypm            |solr_long_lat       |-109.74341,34.91267      |\n",
      "|0a24b5f8-a80b-4d09-a487-02cb88c82966|7ae4d15d-62e2-459b-842a-446f921b9d3f|ypm            |solr_long_lat       |-109.74341,34.91267      |\n",
      "|1efe00ae-64b5-4670-88d3-50a630cbc905|7ae4d15d-62e2-459b-842a-446f921b9d3f|ypm            |solr_long_lat       |-109.74341,34.91267      |\n",
      "|3465b4cf-4824-4d93-a903-c9c414ed92f3|637d0f2f-a0b4-4f33-a1ad-bd0ab18b620d|uaz            |measurements        |Wt 23.2 g, SVL 130mm     |\n",
      "|085f2ffa-1def-4243-a833-bd2447437112|637d0f2f-a0b4-4f33-a1ad-bd0ab18b620d|uaz            |measurements        |Mesquite grass           |\n",
      "|72ee232f-c4d0-40c4-b8dc-fe6784d10bc5|637d0f2f-a0b4-4f33-a1ad-bd0ab18b620d|uaz            |measurements        |dited in CTM&5 C acc.Exp.|\n",
      "|25106b40-bafd-464c-a367-2c534915da56|137ed4cd-5172-45a5-acdb-8e1de9a64e32|ypm            |related_record_links|YPM-IP-586385            |\n",
      "|25106b40-bafd-464c-a367-2c534915da56|137ed4cd-5172-45a5-acdb-8e1de9a64e32|ypm            |related_record_types|PreservedSpecimen        |\n",
      "|25106b40-bafd-464c-a367-2c534915da56|137ed4cd-5172-45a5-acdb-8e1de9a64e32|ypm            |mm_repository_id    |458813                   |\n",
      "|25106b40-bafd-464c-a367-2c534915da56|137ed4cd-5172-45a5-acdb-8e1de9a64e32|ypm            |solr_long_lat       |-107.978086,39.729294    |\n",
      "+------------------------------------+------------------------------------+---------------+--------------------+-------------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "idb_triples.show(20, truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>recordtype</td>\n",
       "      <td>2946917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>centroid</td>\n",
       "      <td>2946917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>created</td>\n",
       "      <td>2946917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>gbifid</td>\n",
       "      <td>2463714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>determinations</td>\n",
       "      <td>2382962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>subdepartment</td>\n",
       "      <td>2323969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>gbifissue</td>\n",
       "      <td>1267532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>registrationcode</td>\n",
       "      <td>1039765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>donorname</td>\n",
       "      <td>905538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>collectionkind</td>\n",
       "      <td>799900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>solr_long_lat</td>\n",
       "      <td>794785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>preservative</td>\n",
       "      <td>686046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>kindofobject</td>\n",
       "      <td>663367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>kindofcollection</td>\n",
       "      <td>533086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>cultivated</td>\n",
       "      <td>481478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>sex</td>\n",
       "      <td>433463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>labellocality</td>\n",
       "      <td>373332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>relationshipofresource</td>\n",
       "      <td>368639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>relatedresourceid</td>\n",
       "      <td>368639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>project</td>\n",
       "      <td>330669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>chronostratigraphy</td>\n",
       "      <td>322012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>vessel</td>\n",
       "      <td>315190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>parttype</td>\n",
       "      <td>305961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>weight</td>\n",
       "      <td>281773</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>age class</td>\n",
       "      <td>257038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>mm_repository_id</td>\n",
       "      <td>249150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>incubation</td>\n",
       "      <td>207787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>lithostratigraphy</td>\n",
       "      <td>199759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>gonads</td>\n",
       "      <td>172031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>water depth</td>\n",
       "      <td>170991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>photo</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>preparationtype</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>culmenLengthInMM</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>rightTarsusLengthInMM</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>disk length</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182</th>\n",
       "      <td>carapace length</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>temperature experiment</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>abnormality</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>breeding remarks</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>crown-rump length</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>weightInoz</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>Date Collected</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>Site Number</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>nestshape</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>maxerror</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>nestsite</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>colors</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>weightInlbs</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>hindfootLengthInm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>totalLengthInm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>weightInlbs skinned</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>tailLengthInm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>tailLengthInmmr</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>weightInlbs (skinned)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>totalLengthInmmr</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>hindfootLengthInmmr</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>earLengthInm</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>earLengthInmmr</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>weightIn lbs</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>mutation</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>207 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        key    count\n",
       "0                recordtype  2946917\n",
       "1                  centroid  2946917\n",
       "2                   created  2946917\n",
       "3                    gbifid  2463714\n",
       "4            determinations  2382962\n",
       "5             subdepartment  2323969\n",
       "6                 gbifissue  1267532\n",
       "7          registrationcode  1039765\n",
       "8                 donorname   905538\n",
       "9            collectionkind   799900\n",
       "10            solr_long_lat   794785\n",
       "11             preservative   686046\n",
       "12             kindofobject   663367\n",
       "13         kindofcollection   533086\n",
       "14               cultivated   481478\n",
       "15                      sex   433463\n",
       "16            labellocality   373332\n",
       "17   relationshipofresource   368639\n",
       "18        relatedresourceid   368639\n",
       "19                  project   330669\n",
       "20       chronostratigraphy   322012\n",
       "21                   vessel   315190\n",
       "22                 parttype   305961\n",
       "23                   weight   281773\n",
       "24                age class   257038\n",
       "25         mm_repository_id   249150\n",
       "26               incubation   207787\n",
       "27        lithostratigraphy   199759\n",
       "28                   gonads   172031\n",
       "29              water depth   170991\n",
       "..                      ...      ...\n",
       "177                   photo       45\n",
       "178         preparationtype       37\n",
       "179        culmenLengthInMM       36\n",
       "180   rightTarsusLengthInMM       36\n",
       "181             disk length       31\n",
       "182         carapace length       23\n",
       "183  temperature experiment       22\n",
       "184             abnormality       20\n",
       "185        breeding remarks       13\n",
       "186       crown-rump length       12\n",
       "187              weightInoz        8\n",
       "188          Date Collected        7\n",
       "189             Site Number        5\n",
       "190               nestshape        5\n",
       "191                maxerror        4\n",
       "192                nestsite        4\n",
       "193                  colors        3\n",
       "194            weightInlbs         2\n",
       "195       hindfootLengthInm        1\n",
       "196          totalLengthInm        1\n",
       "197     weightInlbs skinned        1\n",
       "198           tailLengthInm        1\n",
       "199         tailLengthInmmr        1\n",
       "200   weightInlbs (skinned)        1\n",
       "201        totalLengthInmmr        1\n",
       "202     hindfootLengthInmmr        1\n",
       "203            earLengthInm        1\n",
       "204          earLengthInmmr        1\n",
       "205            weightIn lbs        1\n",
       "206                mutation        1\n",
       "\n",
       "[207 rows x 2 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(idb_triples\n",
    " .groupBy(col(\"key\"))\n",
    " .count()\n",
    " .sort(col(\"count\"), ascending=False)\n",
    " .limit(1000)\n",
    " ).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>institutioncode</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>nhmuk</td>\n",
       "      <td>25313796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ypm</td>\n",
       "      <td>1271259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>mcz</td>\n",
       "      <td>1193852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>lacm</td>\n",
       "      <td>569006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>sio</td>\n",
       "      <td>560147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>wfvz</td>\n",
       "      <td>404292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>omnh</td>\n",
       "      <td>288517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>uwbm</td>\n",
       "      <td>239439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ummz</td>\n",
       "      <td>236954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ku</td>\n",
       "      <td>207037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ttu</td>\n",
       "      <td>189813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>crcm</td>\n",
       "      <td>178677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>uwfc</td>\n",
       "      <td>127494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>uaz</td>\n",
       "      <td>113835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>usf</td>\n",
       "      <td>64481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>tcwc</td>\n",
       "      <td>41201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>but</td>\n",
       "      <td>40343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>uconn</td>\n",
       "      <td>34038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>os</td>\n",
       "      <td>32399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>csuc</td>\n",
       "      <td>21728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>usac</td>\n",
       "      <td>13358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>hsu</td>\n",
       "      <td>9202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>nmmnh</td>\n",
       "      <td>6000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>nbmb</td>\n",
       "      <td>4924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>uafmc</td>\n",
       "      <td>3280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>bsns</td>\n",
       "      <td>3080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>ucla</td>\n",
       "      <td>1655</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>unr</td>\n",
       "      <td>470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>csu</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>uf</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   institutioncode     count\n",
       "0            nhmuk  25313796\n",
       "1              ypm   1271259\n",
       "2              mcz   1193852\n",
       "3             lacm    569006\n",
       "4              sio    560147\n",
       "5             wfvz    404292\n",
       "6             omnh    288517\n",
       "7             uwbm    239439\n",
       "8             ummz    236954\n",
       "9               ku    207037\n",
       "10             ttu    189813\n",
       "11            crcm    178677\n",
       "12            uwfc    127494\n",
       "13             uaz    113835\n",
       "14             usf     64481\n",
       "15            tcwc     41201\n",
       "16             but     40343\n",
       "17           uconn     34038\n",
       "18              os     32399\n",
       "19            csuc     21728\n",
       "20            usac     13358\n",
       "21             hsu      9202\n",
       "22           nmmnh      6000\n",
       "23            nbmb      4924\n",
       "24           uafmc      3280\n",
       "25            bsns      3080\n",
       "26            ucla      1655\n",
       "27             unr       470\n",
       "28             csu        38\n",
       "29              uf        12"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(idb_triples\n",
    " .groupBy(col(\"institutioncode\"))\n",
    " .count()\n",
    " .sort(col(\"count\"), ascending=False)\n",
    " .limit(1000)\n",
    " ).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "77024"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(idb_triples\n",
    " .filter(col(\"key\") == \"NSF_TCN\")\n",
    " .count()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>institutioncode</th>\n",
       "      <th>value</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ypm</td>\n",
       "      <td>WIS</td>\n",
       "      <td>61477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ypm</td>\n",
       "      <td>FIC</td>\n",
       "      <td>15500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ypm</td>\n",
       "      <td>FIC WIS</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  institutioncode    value  count\n",
       "0             ypm      WIS  61477\n",
       "1             ypm      FIC  15500\n",
       "2             ypm  FIC WIS     47"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(idb_triples\n",
    " .filter(col(\"key\") == \"NSF_TCN\")\n",
    " .groupBy(col(\"institutioncode\"), col(\"value\"))\n",
    " .count()\n",
    " .sort(col(\"count\"), ascending=False)\n",
    " .limit(1000)\n",
    " ).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's write this out and go back and join to the main DF for some summaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#(idb_triples\n",
    "# .write\n",
    "# .parquet(\"/tmp/idigbio-20171014T023306-json-triples.parquet\")\n",
    "#)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How much more information might we be able to find in records that records that are not JSON parsable?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "(idb_triples\n",
    " .select(length(col(\"key\")).alias(\"len_key\"))\n",
    " .avg(col(\"len_key\"))\n",
    " .show()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#joined = idb_dyn.join(idb_triples, idb_dyn[\"uuid\"] == idb_triples[\"uuid\"], \"inner\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-16-4ed20ba2f946>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mjoined\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m3\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtruncate\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/opt/spark/latest/python/pyspark/sql/dataframe.py\u001b[0m in \u001b[0;36mshow\u001b[0;34m(self, n, truncate)\u001b[0m\n\u001b[1;32m    318\u001b[0m             \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshowString\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m20\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    319\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 320\u001b[0;31m             \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshowString\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtruncate\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    321\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    322\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__repr__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/spark/latest/python/lib/py4j-latest-src.zip/py4j/java_gateway.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, *args)\u001b[0m\n\u001b[1;32m   1129\u001b[0m             \u001b[0mproto\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mEND_COMMAND_PART\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1130\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1131\u001b[0;31m         \u001b[0manswer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgateway_client\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1132\u001b[0m         return_value = get_return_value(\n\u001b[1;32m   1133\u001b[0m             answer, self.gateway_client, self.target_id, self.name)\n",
      "\u001b[0;32m/opt/spark/latest/python/lib/py4j-latest-src.zip/py4j/java_gateway.py\u001b[0m in \u001b[0;36msend_command\u001b[0;34m(self, command, retry, binary)\u001b[0m\n\u001b[1;32m    881\u001b[0m         \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    882\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 883\u001b[0;31m             \u001b[0mresponse\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    884\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mbinary\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    885\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_create_connection_guard\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/spark/latest/python/lib/py4j-latest-src.zip/py4j/java_gateway.py\u001b[0m in \u001b[0;36msend_command\u001b[0;34m(self, command)\u001b[0m\n\u001b[1;32m   1026\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1027\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1028\u001b[0;31m             \u001b[0manswer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msmart_decode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstream\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreadline\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1029\u001b[0m             \u001b[0mlogger\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdebug\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Answer received: {0}\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0manswer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1030\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0manswer\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstartswith\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mproto\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mRETURN_MESSAGE\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/lib/python3.5/socket.py\u001b[0m in \u001b[0;36mreadinto\u001b[0;34m(self, b)\u001b[0m\n\u001b[1;32m    573\u001b[0m         \u001b[0;32mwhile\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    574\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 575\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sock\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrecv_into\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    576\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mtimeout\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    577\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_timeout_occurred\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "#joined.show(3, truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#joined.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Who provides the most additional information in valid JSON?\n",
    "\n",
    "What is the ratio of JSON to non-JSON text? How much more do we have to work on?"
   ]
  }
 ],
 "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
}