Metadata_spark_exploration.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import json as json\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pyspark.sql.types import *\n",
    "from pyspark.sql import Row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Populating the interactive namespace from numpy and matplotlib\n"
     ]
    }
   ],
   "source": [
    "%pylab inline\n",
    "from pyspark.sql.types import * "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pyspark.sql import SQLContext \n",
    "titlesdf = sqlContext.createDataFrame(pd.read_json('nasa_title.json')).persist(StorageLevel.MEMORY_AND_DISK_SER)\n",
    "titlesdf.registerTempTable('title')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Peek json contents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+\n",
      "|                  id|             keyword|\n",
      "+--------------------+--------------------+\n",
      "|55942a57c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a57c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "|55942a58c63a7fe59...|ArrayBuffer(EARTH...|\n",
      "+--------------------+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "keywordsdf = sqlContext.createDataFrame(pd.read_json('nasa_keyword.json')).persist(StorageLevel.MEMORY_AND_DISK_SER)\n",
    "keywordsdf.registerTempTable('keyword')\n",
    "keywordsdf.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "descriptiondf = sqlContext.createDataFrame(pd.read_json('nasa_description.json')).persist(StorageLevel.MEMORY_AND_DISK_SER)\n",
    "descriptiondf.registerTempTable('description')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "landingdf = sqlContext.createDataFrame(pd.read_json('nasa_landingPage.json')).persist(StorageLevel.MEMORY_AND_DISK_SER)\n",
    "landingdf.registerTempTable('landingPage')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "languagedf = sqlContext.createDataFrame(pd.read_json('nasa_language.json')).persist(StorageLevel.MEMORY_AND_DISK_SER)\n",
    "languagedf.registerTempTable('language')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "themedf = sqlContext.createDataFrame(pd.read_json('nasa_theme.json')).persist(StorageLevel.MEMORY_AND_DISK_SER)\n",
    "themedf.registerTempTable('theme')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "temporaldf = sqlContext.createDataFrame(pd.read_json('nasa_temporal.json')).persist(StorageLevel.MEMORY_AND_DISK_SER)\n",
    "temporaldf.registerTempTable('temporal')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "spatialdf = sqlContext.createDataFrame(pd.read_json('nasa_spatial.json')).persist(StorageLevel.MEMORY_AND_DISK_SER)\n",
    "spatialdf.registerTempTable('spatial')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "modifieddf = sqlContext.createDataFrame(pd.read_json('nasa_modified.json')).persist(StorageLevel.MEMORY_AND_DISK_SER)\n",
    "modifieddf.registerTempTable('modified')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from operator import add\n",
    "\n",
    "title_count = titlesdf.map( lambda x: x.title.replace(',',' ').replace('.',' ').replace('-',' ').lower()) \\\n",
    "        .flatMap(lambda x: x.split()) \\\n",
    "        .map(lambda x: (x, 1)) \\\n",
    "        .reduceByKey(lambda x,y:x+y) \\\n",
    "        .map(lambda x:(x[1],x[0])) \\\n",
    "        .sortByKey(False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "output = title_count.collect()  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7555: project\n",
      "4843: and\n",
      "4344: for\n",
      "2994: data\n",
      "2703: of\n",
      "2394: level\n",
      "1807: global\n",
      "1720: 0\n",
      "1649: v1\n",
      "1485: 3\n",
      "1433: 2\n",
      "1397: daily\n",
      "1387: 1\n",
      "1311: l2\n",
      "1157: in\n",
      "1142: high\n",
      "1136: monthly\n",
      "1120: a\n",
      "1086: from\n",
      "1066: surface\n",
      "1035: the\n",
      "946: v003\n",
      "930: product\n",
      "917: gpm\n",
      "909: system\n",
      "893: grid\n",
      "854: l3\n",
      "821: boreas\n",
      "803: v005\n",
      "797: lba\n",
      "788: 5\n",
      "727: ground\n",
      "683: eco\n",
      "682: land\n",
      "674: based\n",
      "663: grids\n",
      "630: validation\n",
      "625: 2000\n",
      "623: v7\n",
      "621: swath\n",
      "607: omi/aura\n",
      "600: degree\n",
      "589: rss\n",
      "588: soil\n",
      "575: temperature\n",
      "575: cloud\n",
      "573: space\n",
      "562: total\n",
      "552: v001\n",
      "548: ozone\n",
      "546: aerosol\n"
     ]
    }
   ],
   "source": [
    "iter_count = 0\n",
    "for (count, word) in output:\n",
    "    print(\"%i: %s\" % (count, word))\n",
    "    iter_count = iter_count + 1\n",
    "    if iter_count > 50:\n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(id=u'55942a57c63a7fe59b495a77', title=u'15 Minute Stream Flow Data: USGS (FIFE)'),\n",
       " Row(id=u'55942a57c63a7fe59b495a78', title=u'15 Minute Stream Flow Data: USGS (FIFE)'),\n",
       " Row(id=u'55942a58c63a7fe59b495a79', title=u'15 Minute Stream Flow Data: USGS (FIFE)'),\n",
       " Row(id=u'55942a58c63a7fe59b495a7a', title=u'2000 Pilot Environmental Sustainability Index (ESI)'),\n",
       " Row(id=u'55942a58c63a7fe59b495a7b', title=u'2000 Pilot Environmental Sustainability Index (ESI)')]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "titlesdf.take(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "title_sentences = titlesdf.map(lambda x: ''.join(x.title)) \\\n",
    "        .flatMap(lambda x: x.split('.'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "title_bigram = title_sentences.map(lambda x: x.split()) \\\n",
    "    .flatMap(lambda x: [((x[i],x[i+1]),1) for i in range(0,len(x)-1)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "title_freq_bigrams = title_bigram.reduceByKey(lambda x,y:x+y) \\\n",
    "    .map(lambda x:(x[1],x[0])) \\\n",
    "    .sortByKey(False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(836, (u'Level', u'3')),\n",
       " (682, (u'L3', u'Global')),\n",
       " (550, (u'GROUND', u'VALIDATION')),\n",
       " (550, (u'GPM', u'GROUND')),\n",
       " (488, (u'L2', u'Swath')),\n",
       " (469, (u'NETCDF', u'V7')),\n",
       " (460, (u'FROM', u'DMSP')),\n",
       " (453, (u'Level', u'2')),\n",
       " (448, (u'PRODUCT', u'GRIDS')),\n",
       " (448, (u'OCEAN', u'PRODUCT')),\n",
       " (444, (u'Daily', u'L3')),\n",
       " (367, (u'Land', u'Surface')),\n",
       " (357, (u'Total', u'Column')),\n",
       " (356, (u'GHRSST', u'Level')),\n",
       " (336, (u'AVERAGE', u'FROM')),\n",
       " (336, (u'SSM/I', u'OCEAN')),\n",
       " (336, (u'RSS', u'SSM/I')),\n",
       " (335, (u'Lat/Lon', u'Grid')),\n",
       " (324, (u'AIRS/Aqua', u'Level')),\n",
       " (324, (u'SAFARI', u'2000')),\n",
       " (321, (u'Mixing', u'Ratio')),\n",
       " (319, (u'Global', u'0')),\n",
       " (306, (u'System', u'Project')),\n",
       " (299, (u'Grid', u'V003')),\n",
       " (286, (u'of', u'the')),\n",
       " (278, (u'Aqua', u'AIRS')),\n",
       " (273, (u'25', u'deg')),\n",
       " (261, (u'x', u'0')),\n",
       " (258, (u'MLS/Aura', u'L2')),\n",
       " (249, (u'Applications', u'Project')),\n",
       " (245, (u'Surface', u'Model')),\n",
       " (243, (u'Systems', u'Project')),\n",
       " (242, (u'Model', u'L4')),\n",
       " (241, (u'deg', u'Lat/Lon')),\n",
       " (238, (u'System', u'for')),\n",
       " (236, (u'AIRS', u'Level')),\n",
       " (230, (u'Ozone', u'(O3)')),\n",
       " (222, (u'5-Min', u'L2')),\n",
       " (221, (u'Aerosol', u'Optical')),\n",
       " (216, (u'GCPEX', u'V1')),\n",
       " (213, (u'Level', u'2P')),\n",
       " (209, (u'Water', u'Vapor')),\n",
       " (207, (u'3', u'Monthly')),\n",
       " (199, (u'km', u'V003')),\n",
       " (188, (u'Optical', u'Depth')),\n",
       " (185, (u'NPP', u'Grassland:')),\n",
       " (184, (u'MC3E', u'V1')),\n",
       " (183, (u'125', u'x')),\n",
       " (183, (u'125', u'degree')),\n",
       " (183, (u'Swath', u'13x24'))]"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "title_freq_bigrams.take(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "desc_sentences = descriptiondf.map(lambda x: ''.join(x.description)) \\\n",
    "        .flatMap(lambda x: x.split('.'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "desc_bigram = desc_sentences.map(lambda x: x.split()) \\\n",
    "    .flatMap(lambda x: [((x[i],x[i+1]),1) for i in range(0,len(x)-1)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(37194, (u'in', u'the')),\n",
       " (24517, (u'of', u'the')),\n",
       " (11715, (u'on', u'the')),\n",
       " (9989, (u'is', u'a')),\n",
       " (9100, (u'from', u'the')),\n",
       " (8993, (u'and', u'Aqua')),\n",
       " (8979, (u'the', u'equator')),\n",
       " (8962, (u'equator', u'in')),\n",
       " (8010, (u'for', u'the')),\n",
       " (7824, (u'to', u'the')),\n",
       " (6696, (u'data', u'set')),\n",
       " (6667, (u'will', u'be')),\n",
       " (5998, (u'spectral', u'bands,')),\n",
       " (5814, (u'development', u'of')),\n",
       " (5772, (u'aboard', u'the')),\n",
       " (5559, (u'over', u'the')),\n",
       " (5484, (u'These', u'data')),\n",
       " (5464, (u'by', u'the')),\n",
       " (5381, (u'the', u'development')),\n",
       " (5161, (u'and', u'the')),\n",
       " (5126, (u'and', u'in')),\n",
       " (5108, (u'so', u'that')),\n",
       " (4994, (u'data', u'in')),\n",
       " (4978, (u'the', u'entire')),\n",
       " (4888, (u'data', u'will')),\n",
       " (4860, (u'able', u'to')),\n",
       " (4774, (u'understanding', u'of')),\n",
       " (4771, (u'the', u'Earth')),\n",
       " (4749, (u'that', u'it')),\n",
       " (4744, (u'data', u'are')),\n",
       " (4739, (u'of', u'our')),\n",
       " (4707, (u'across', u'the')),\n",
       " (4697, (u'role', u'in')),\n",
       " (4692, (u'instrument', u'aboard')),\n",
       " (4681, (u'of', u'global')),\n",
       " (4668, (u'a', u'key')),\n",
       " (4620, (u'1', u'to')),\n",
       " (4614, (u'dynamics', u'and')),\n",
       " (4607, (u'to', u'predict')),\n",
       " (4600, (u'Earth', u'system')),\n",
       " (4600, (u'Resolution', u'Imaging')),\n",
       " (4591, (u'around', u'the')),\n",
       " (4564, (u'Moderate', u'Resolution')),\n",
       " (4563, (u'the', u'lower')),\n",
       " (4563, (u'to', u'assist')),\n",
       " (4558, (u'enough', u'to')),\n",
       " (4548, (u\"Earth's\", u'surface')),\n",
       " (4541, (u'to', u'2')),\n",
       " (4538, (u'will', u'improve')),\n",
       " (4515, (u'south', u'to'))]"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desc_freq_bigrams = desc_bigram.reduceByKey(lambda x,y:x+y) \\\n",
    "    .map(lambda x:(x[1],x[0])) \\\n",
    "    .sortByKey(False)\n",
    "desc_freq_bigrams.take(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n",
    "from nltk.corpus import stopwords\n",
    "nltk_stop = stopwords.words('english')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def remove_stopwords_bigram(rec_tup):\n",
    "    if rec_tup[0][0] not in nltk_stop and rec_tup[0][1] not in nltk_stop:\n",
    "        return rec_tup\n",
    "    return ((), 0)\n",
    "\n",
    "desc_bigram_nostopwords = desc_bigram.map(remove_stopwords_bigram)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[((u'USGS', u'15'), 1),\n",
       " ((u'15', u'minute'), 1),\n",
       " ((u'minute', u'stream'), 1),\n",
       " ((u'stream', u'flow'), 1),\n",
       " ((u'flow', u'data'), 1)]"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desc_bigram_nostopwords.take(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "desc_freq_bigrams_nostopwords = desc_bigram_nostopwords.reduceByKey(lambda x,y:x+y) \\\n",
    "    .map(lambda x:(x[1],x[0])) \\\n",
    "    .sortByKey(False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(6696, (u'data', u'set')),\n",
       " (5998, (u'spectral', u'bands,')),\n",
       " (5484, (u'These', u'data')),\n",
       " (4692, (u'instrument', u'aboard')),\n",
       " (4600, (u'Earth', u'system')),\n",
       " (4600, (u'Resolution', u'Imaging')),\n",
       " (4564, (u'Moderate', u'Resolution')),\n",
       " (4548, (u\"Earth's\", u'surface')),\n",
       " (4514, (u'global', u'change')),\n",
       " (4503, (u'system', u'models')),\n",
       " (4496, (u'Aqua', u'MODIS')),\n",
       " (4493, (u'Imaging', u'Spectroradiometer)')),\n",
       " (4493, (u'every', u'1')),\n",
       " (4492, (u'acquiring', u'data')),\n",
       " (4488, (u'Terra', u'MODIS')),\n",
       " (4485, (u'orbit', u'around')),\n",
       " (4484, (u'entire', u\"Earth's\")),\n",
       " (4483, (u'vital', u'role')),\n",
       " (4482, (u'processes', u'occurring')),\n",
       " (4482, (u'lower', u'atmosphere')),\n",
       " (4482, (u'key', u'instrument')),\n",
       " (4481, (u'Terra', u'(EOS')),\n",
       " (4481, (u'days,', u'acquiring')),\n",
       " (4481, (u'wavelengths', u'(see')),\n",
       " (4481, (u'(see', u'MODIS')),\n",
       " (4481, (u'surface', u'every')),\n",
       " (4481, (u'(EOS', u'AM)')),\n",
       " (4481, (u'passes', u'south')),\n",
       " (4481, (u'Aqua', u'passes')),\n",
       " (4481, (u'accurately', u'enough')),\n",
       " (4481, (u\"Terra's\", u'orbit')),\n",
       " (4481, (u'Technical', u'Specifications)')),\n",
       " (4481, (u'(EOS', u'PM)')),\n",
       " (4481, (u'PM)', u'satellites')),\n",
       " (4481, (u'Aqua', u'(EOS')),\n",
       " (4481, (u'south', u'across')),\n",
       " (4481, (u'MODIS', u'Technical')),\n",
       " (4481, (u'2', u'days,')),\n",
       " (4481, (u'36', u'spectral')),\n",
       " (4481, (u'(or', u'Moderate')),\n",
       " (4481, (u'MODIS', u'(or')),\n",
       " (4480, (u'decisions', u'concerning')),\n",
       " (4480, (u'policy', u'makers')),\n",
       " (4480, (u'assist', u'policy')),\n",
       " (4480, (u'sound', u'decisions')),\n",
       " (4480, (u'models', u'able')),\n",
       " (4480, (u'validated,', u'global,')),\n",
       " (4480, (u'interactive', u'Earth')),\n",
       " (4480, (u'making', u'sound')),\n",
       " (4480, (u'global,', u'interactive'))]"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "desc_freq_bigrams_nostopwords.take(50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Questions:\n",
    "1. What are the keywords that are also most frequent words in titles? What are their frequencies?\n",
    "2. What are the keywords that are also most frequent words in descriptions? What are their frequencies?\n",
    "3. Can we use word frequencies in title to accurately predict keywords?\n",
    "4. What is the TF/IDF for each word in description?\n",
    "5. Can we use TF/IDF in description to accurately predict keywords?\n",
    "6. How do you use bigrams/n-grams? Jaccard-similarity (or other similarity measure) between n-grams in title and n-grams in description? To accurately predict the keywords of other title-description pairs? Use splitting test & train?"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}