Lesson 5

MongoDB for Data Analysis

Problem sets


In [31]:
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
In this problem set you work with another type of infobox data, audit it,
clean it, come up with a data model, insert it into MongoDB and then run some
queries against your database. The set contains data about Arachnid class
animals.

Your task in this exercise is to parse the file, process only the fields that
are listed in the FIELDS dictionary as keys, and return a list of dictionaries
of cleaned values. 

The following things should be done:
- keys of the dictionary changed according to the mapping in FIELDS dictionary
- trim out redundant description in parenthesis from the 'rdf-schema#label'
  field, like "(spider)"
- if 'name' is "NULL" or contains non-alphanumeric characters, set it to the
  same value as 'label'.
- if a value of a field is "NULL", convert it to None
- if there is a value in 'synonym', it should be converted to an array (list)
  by stripping the "{}" characters and splitting the string on "|". Rest of the
  cleanup is up to you, e.g. removing "*" prefixes etc. If there is a singular
  synonym, the value should still be formatted in a list.
- strip leading and ending whitespace from all fields, if there is any
- the output structure should be as follows:

[ { 'label': 'Argiope',
    'uri': 'http://dbpedia.org/resource/Argiope_(spider)',
    'description': 'The genus Argiope includes rather large and spectacular spiders that often ...',
    'name': 'Argiope',
    'synonym': ["One", "Two"],
    'classification': {
                      'family': 'Orb-weaver spider',
                      'class': 'Arachnid',
                      'phylum': 'Arthropod',
                      'order': 'Spider',
                      'kingdom': 'Animal',
                      'genus': None
                      }
  },
  { 'label': ... , }, ...
]

  * Note that the value associated with the classification key is a dictionary
    with taxonomic labels.
"""
import codecs
import csv
import json
import pprint
import re

DATAFILE = 'arachnid.csv'
FIELDS ={'rdf-schema#label': 'label',
         'URI': 'uri',
         'rdf-schema#comment': 'description',
         'synonym': 'synonym',
         'name': 'name',
         'family_label': 'family',
         'class_label': 'class',
         'phylum_label': 'phylum',
         'order_label': 'order',
         'kingdom_label': 'kingdom',
         'genus_label': 'genus'}


def process_file(filename, fields):

    process_keys = fields.keys()
    data = []
    with open(filename, "r") as f:
        reader = csv.DictReader(f)
        for i in range(3):
            #l = reader.next()
            l = next(reader)

        # loop over lines
        for line in reader:
                
                # map input dict keys to fields keys
                for lk in line.keys():
                    for fk in fields.keys():
                        if lk == fk:
                            line[fk] = line.pop(lk)
                            print(line[fk])
                
                # check schema-label field
                line["label"].strip("(").strip(")")

                # check name field
                if line["name"] == "NULL" or line["name"].isallnum():
                    line["name"] = "name"
                    
                # check synonym field
                elif line["synonym"] != None:
                    line["synonym"] = line["synonym"].strip("{").strip("}").split("|")

                # loop over fields
                for k,v in line.iteritems():
                    
                    # convert NULL strings to None
                    if line[k] == "NULL":
                        line[field] = None

                    # strip whitespace
                    line[field].strip()
                
                # add processed line to data
                data.append(line)
                
    return data


def parse_array(v):
    if (v[0] == "{") and (v[-1] == "}"):
        v = v.lstrip("{")
        v = v.rstrip("}")
        v_array = v.split("|")
        v_array = [i.strip() for i in v_array]
        return v_array
    return [v]


def test():
    data = process_file(DATAFILE, FIELDS)
    print("Your first entry:")
    pprint.pprint(data[0])
    first_entry = {
        "synonym": None, 
        "name": "Argiope", 
        "classification": {
            "kingdom": "Animal", 
            "family": "Orb-weaver spider", 
            "order": "Spider", 
            "phylum": "Arthropod", 
            "genus": None, 
            "class": "Arachnid"
        }, 
        "uri": "http://dbpedia.org/resource/Argiope_(spider)", 
        "label": "Argiope", 
        "description": "The genus Argiope includes rather large and spectacular spiders that often have a strikingly coloured abdomen. These spiders are distributed throughout the world. Most countries in tropical or temperate climates host one or more species that are similar in appearance. The etymology of the name is from a Greek name meaning silver-faced."
    }

    assert len(data) == 76
    assert data[0] == first_entry
    assert data[17]["name"] == "Ogdenia"
    assert data[48]["label"] == "Hydrachnidiae"
    assert data[14]["synonym"] == ["Cyrene Peckham & Peckham"]

if __name__ == "__main__":
    test()


http://dbpedia.org/resource/Argiope_(spider)
NULL
Arthropod
Argiope (spider)
The genus Argiope includes rather large and spectacular spiders that often have a strikingly coloured abdomen. These spiders are distributed throughout the world. Most countries in tropical or temperate climates host one or more species that are similar in appearance. The etymology of the name is from a Greek name meaning silver-faced.
Orb-weaver spider
Arachnid
Animal
Argiope
NULL
Spider
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-31-e4d2637eaec6> in <module>()
    149 
    150 if __name__ == "__main__":
--> 151     test()

<ipython-input-31-e4d2637eaec6> in test()
    123 
    124 def test():
--> 125     data = process_file(DATAFILE, FIELDS)
    126     print("Your first entry:")
    127     pprint.pprint(data[0])

<ipython-input-31-e4d2637eaec6> in process_file(filename, fields)
     86 
     87                 # check schema-label field
---> 88                 line["label"].strip("(").strip(")")
     89 
     90                 # check name field

KeyError: 'label'

In [20]:
FIELDS ={'rdf-schema#label': 'label',
         'URI': 'uri',
         'rdf-schema#comment': 'description',
         'synonym': 'synonym',
         'name': 'name',
         'family_label': 'family',
         'class_label': 'class',
         'phylum_label': 'phylum',
         'order_label': 'order',
         'kingdom_label': 'kingdom',
         'genus_label': 'genus'}

In [22]:
for k in FIELDS.keys():
    if k == "URI":
        print(FIELDS[k])


uri

In [ ]: