ETL-Notes.ipynb

{
 "metadata": {
  "name": "ETL-Notes"
 }, 
 "nbformat": 2, 
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code", 
     "collapsed": true, 
     "input": [
      "review_list = []"
     ], 
     "language": "python", 
     "outputs": [], 
     "prompt_number": 1
    },
    {
     "cell_type": "code", 
     "collapsed": true, 
     "input": [
      "# Read file into a list", 
      "# Strip new lines", 
      "for line in open('movies.sample.txt'):", 
      "    review_list.append(line.strip())", 
      "    "
     ], 
     "language": "python", 
     "outputs": [], 
     "prompt_number": 2
    }, 
    {
     "cell_type": "code", 
     "collapsed": false, 
     "input": [
      "# Preview our data", 
      "review_list[0:10]"
     ], 
     "language": "python", 
     "outputs": [
      {
       "output_type": "pyout", 
       "prompt_number": 3, 
       "text": [
        "['product/productId: B003AI2VGA',", 
        " 'review/userId: A141HP4LYPWMSR',", 
        " 'review/profileName: Brian E. Erland \"Rainbow Sphinx\"',", 
        " 'review/helpfulness: 7/7',", 
        " 'review/score: 3.0',", 
        " 'review/time: 1182729600',", 
        " 'review/summary: \"There Is So Much Darkness Now ~ Come For The Miracle\"',", 
        " 'review/text: Synopsis: On the daily trek from Juarez, Mexico to El Paso, Texas an ever increasing number of female workers are found raped and murdered in the surrounding desert. Investigative reporter Karina Danes (Minnie Driver) arrives from Los Angeles to pursue the story and angers both the local police and the factory owners who employee the undocumented aliens with her pointed questions and relentless quest for the truth.<br /><br />Her story goes nationwide when a young girl named Mariela (Ana Claudia Talancon) survives a vicious attack and walks out of the desert crediting the Blessed Virgin for her rescue. Her story is further enhanced when the \"Wounds of Christ\" (stigmata) appear in her palms. She also claims to have received a message of hope for the Virgin Mary and soon a fanatical movement forms around her to fight against the evil that holds such a stranglehold on the area.<br /><br />Critique: Possessing a lifelong fascination with such esoteric matters as Catholic mysticism, miracles and the mysterious appearance of the stigmata, I was immediately attracted to the \\'05 DVD release `Virgin of Juarez\\'. The film offers a rather unique storyline blending current socio-political concerns, the constant flow of Mexican migrant workers back and forth across the U.S./Mexican border and the traditional Catholic beliefs of the Hispanic population. I must say I was quite surprised by the unexpected route taken by the plot and the means and methods by which the heavenly message unfolds.<br /><br />`Virgin of Juarez\\' is not a film that you would care to watch over and over again, but it was interesting enough to merit at least one viewing. Minnie Driver delivers a solid performance and Ana Claudia Talancon is perfect as the fragile and innocent visionary Mariela. Also starring Esai Morales and Angus Macfadyen (Braveheart).',", 
        " '',", 
        " 'product/productId: B003AI2VGA']"
       ]
      }
     ], 
     "prompt_number": 3
    }, 
    {
     "cell_type": "code", 
     "collapsed": true, 
     "input": [
      "# Grouping review into dict of dicts based on the empy string item", 
      "grouped_reviews = []", 
      "new_group = []", 
      "for line in review_list:", 
      "    if line != '':", 
      "        new_group.append(line)", 
      "    else:", 
      "        grouped_reviews.append(new_group)", 
      "        new_group = []"
     ], 
     "language": "python", 
     "outputs": [], 
     "prompt_number": 4
    }, 
    {
     "cell_type": "code", 
     "collapsed": false, 
     "input": [
      "grouped_reviews[10]"
     ], 
     "language": "python", 
     "outputs": [
      {
       "output_type": "pyout", 
       "prompt_number": 5, 
       "text": [
        "['product/productId: B00006HAXW',", 
        " 'review/userId: A2TX99AZKDK0V7',", 
        " 'review/profileName: Henrique Peirano',", 
        " 'review/helpfulness: 22/23',", 
        " 'review/score: 4.0',", 
        " 'review/time: 1039564800',", 
        " 'review/summary: I expected more.',", 
        " \"review/text: I have the Doo Wop 50 and 51 DVDs, and was anxiously waiting for Rock, Rhythm and Doo Wop to arrive. From the first video, which featured the cr&egrave;me de la cr&egrave;me of the 50s groups, to Doo Wop 51, it was clear that there was a giant leap forward in terms of production. Better lighting, better stage layout, and better sound quality.<br />Sadly, in terms of editing, they went overboard this time. Excessive use of pre-recorded clapping and cheering really irritated me. There are times where you can hardly hear the singing, because the pre-recorded cheering was so loud. Imagine 5 stadiums hosting the Super Bowl all crammed into one theater. It's simply too artificial. We want not only the authentic groups, but also the authentic reaction from who was there. And believe me, we don't need any extra cheering to get more goosebumps from watching these wonderful groups.<br />For whoever edited the show, I would also be careful with too much filming of the spectators during the songs and make sure to look back at Doo Wop 51 for better camera angles of the groups.<br />Is this DVD worth buying? Definately, because groups/singers Fred Parris &amp; The Satins, Little Anthony &amp; The Imperials, Little Richard, The Limelites, Jerry Butler, Lloyd Price, Bobby Lewis, and the absolutely fantastic Contours make it all worthwhile.<br />I hope TJ Lubinsky keeps on making shows like this, they're a wonderful experience for all ages.\"]"
       ]
      }
     ], 
     "prompt_number": 5
    }, 
    {
     "cell_type": "code", 
     "collapsed": false, 
     "input": [
      "sample_line = grouped_reviews[10][3]", 
      "sample_line"
     ], 
     "language": "python", 
     "outputs": [
      {
       "output_type": "pyout", 
       "prompt_number": 6, 
       "text": [
        "'review/helpfulness: 22/23'"
       ]
      }
     ], 
     "prompt_number": 6
    }, 
    {
     "cell_type": "code", 
     "collapsed": true, 
     "input": [
      "list_of_dict = []", 
      "", 
      "for review in grouped_reviews:", 
      "    new_dict = {}", 
      "    for item in review:", 
      "        rev_key, rev_value = item.split(': ', 1)", 
      "        clean_rev_key = rev_key.split('/')[1]", 
      "        new_dict[clean_rev_key] = rev_value", 
      "    list_of_dict.append(new_dict)", 
      "    "
     ], 
     "language": "python", 
     "outputs": [], 
     "prompt_number": 7
    }, 
    {
     "cell_type": "code", 
     "collapsed": false, 
     "input": [
      "string = '1231231231:4fdfsdfsdf:3423423423:'", 
      "# Splits by the first occurence", 
      "string.split(':', 1)[1]"
     ], 
     "language": "python", 
     "outputs": [
      {
       "output_type": "pyout", 
       "prompt_number": 8, 
       "text": [
        "'4fdfsdfsdf:3423423423:'"
       ]
      }
     ], 
     "prompt_number": 8
    }, 
    {
     "cell_type": "code", 
     "collapsed": false, 
     "input": [
      "list_of_dict[3]"
     ], 
     "language": "python", 
     "outputs": [
      {
       "output_type": "pyout", 
       "prompt_number": 9, 
       "text": [
        "{'helpfulness': '1/1',", 
        " 'productId': 'B003AI2VGA',", 
        " 'profileName': 'golgotha.gov',", 
        " 'score': '3.0',", 
        " 'summary': 'distantly based on a real tragedy',", 
        " 'text': 'THE VIRGIN OF JUAREZ (2006)<br />directed by Kevin James Dobson<br />approx. 1 hour 25 minutes<br /><br />The town of Juarez, Chihuahua in Mexico is just across the border from El Paso, Texas.  It is home to several factories owned by American companies who employ Mexicans at very low wages.  Since the early 1990s, Juarez has also been the murder site of hundreds of women.  There is a lot of debate as to whether the deaths are the work of a drug gang or a serial killer.  It has also been suggested that due to the local police department\\'s failure to solve the crimes, new rapists and thugs have moved to the area knowing that they will never be caught.  The situation is an overwhelming tragedy and this movie uses the story as a backdrop.<br /><br />We start off seeing how unsafe women feel as they walk and work in Juarez.  Then we meet the hard-headed American journalist played by Minnie Driver.  She aggressively questions the local police and befriends a Catholic priest.  Within the first twenty minutes or so, we see the terrifying reality of the situation in Juarez.<br /><br />At this point, the story jumps from something that might have happened to something that is only found in this movie.  A woman named Mariela survives an brutal attack but is in critical condition in a small hospital.  While the priest is speaking to her, she lets out a howl... and her hands begin bleeding*.  This \"stigmata\" is meant to show that she has been blessed by the Virgin Mary.  Our reporter protagonist clashes with various authorities on what should be done.  Meanwhile Mariela goes underground and attracts a devoted following, eventually landing her own radio show (!) and living under the protection of gang members, whom she refers to as \"modern warriors\".  The only thing missing is \"Jes&uacute;s Malverde\".<br /><br />Now I understand that we don\\'t know everything about the Juarez murders and that a screenwriter has to \"fill in the blanks\" when crafting an exciting dramatization.  However if you are going to deviate so far from the true story, then don\\'t preface your movie as being \"based on the ongoing unsolved murders in Cuidad Juarez\".  There is plenty of interesting and legitimate information in the REAL stories of Juarez and there is no need to fabricate.  This movie may \"raise the issue\" of the Juarez women, but it isn\\'t really about them at all!<br /><br />If you are interested in learning more about the killings in Juarez, there are a few documentaries.  A recent one from Las Perlas Del Mar Films is simply titled \\'JUAREZ\\' and is a good introduction.  Zulma Aguiar\\'s \\'JUAREZ MOTHERS FIGHT FEMICIDE\\' (focuses on the families) is also easy to find.  There is another one which I haven\\'t seen titled \\'BORDER ECHOES\\'.<br /><br />*- note to Hollywood:<br />the nails that crucified Jesus were through his wrists, not his palms!',", 
        " 'time': '1197158400',", 
        " 'userId': 'A1M5405JH9THP9'}"
       ]
      }
     ], 
     "prompt_number": 9
    }, 
    {
     "cell_type": "code", 
     "collapsed": true, 
     "input": [
      "import datetime", 
      "", 
      "for review in list_of_dict:", 
      "    # Convert string into a float", 
      "    review['score'] = float(review['score'])", 
      "    ", 
      "    # Convert integer timestamp into year, month and date", 
      "    time_float = float(review['time'])", 
      "    temp_time = datetime.datetime.fromtimestamp(time_float)", 
      "    format_time = str(temp_time.month) + ':' + str(temp_time.day) + ':' + str(temp_time.year)", 
      "    review['time'] = format_time"
     ], 
     "language": "python", 
     "outputs": [], 
     "prompt_number": 10
    }, 
    {
     "cell_type": "code", 
     "collapsed": false, 
     "input": [
      "list_of_dict[10]"
     ], 
     "language": "python", 
     "outputs": [
      {
       "output_type": "pyout", 
       "prompt_number": 11, 
       "text": [
        "{'helpfulness': '22/23',", 
        " 'productId': 'B00006HAXW',", 
        " 'profileName': 'Henrique Peirano',", 
        " 'score': 4.0,", 
        " 'summary': 'I expected more.',", 
        " 'text': \"I have the Doo Wop 50 and 51 DVDs, and was anxiously waiting for Rock, Rhythm and Doo Wop to arrive. From the first video, which featured the cr&egrave;me de la cr&egrave;me of the 50s groups, to Doo Wop 51, it was clear that there was a giant leap forward in terms of production. Better lighting, better stage layout, and better sound quality.<br />Sadly, in terms of editing, they went overboard this time. Excessive use of pre-recorded clapping and cheering really irritated me. There are times where you can hardly hear the singing, because the pre-recorded cheering was so loud. Imagine 5 stadiums hosting the Super Bowl all crammed into one theater. It's simply too artificial. We want not only the authentic groups, but also the authentic reaction from who was there. And believe me, we don't need any extra cheering to get more goosebumps from watching these wonderful groups.<br />For whoever edited the show, I would also be careful with too much filming of the spectators during the songs and make sure to look back at Doo Wop 51 for better camera angles of the groups.<br />Is this DVD worth buying? Definately, because groups/singers Fred Parris &amp; The Satins, Little Anthony &amp; The Imperials, Little Richard, The Limelites, Jerry Butler, Lloyd Price, Bobby Lewis, and the absolutely fantastic Contours make it all worthwhile.<br />I hope TJ Lubinsky keeps on making shows like this, they're a wonderful experience for all ages.\",", 
        " 'time': '12:10:2002',", 
        " 'userId': 'A2TX99AZKDK0V7'}"
       ]
      }
     ], 
     "prompt_number": 11
    }, 
    {
     "cell_type": "code", 
     "collapsed": false, 
     "input": [
      "# Time Conversion", 
      "temp_time = datetime.datetime.fromtimestamp(time)", 
      "format_time = str(temp_time.month) + ':' + str(temp_time.day) + ':' + str(temp_time.year)", 
      "#temp_time.isoformat()"
     ], 
     "language": "python", 
     "outputs": [
      {
       "ename": "NameError", 
       "evalue": "name 'time' is not defined", 
       "output_type": "pyerr", 
       "traceback": [
        "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)", 
        "\u001b[0;32m/home/verkter/Desktop/Amazon-ETL/<ipython-input-12-ad6ab8896312>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Time Conversion\u001b[0m\n\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mtemp_time\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdatetime\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfromtimestamp\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtime\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0mformat_time\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtemp_time\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmonth\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;34m':'\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtemp_time\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mday\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0;34m':'\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtemp_time\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0myear\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0;31m#temp_time.isoformat()\u001b[0m\n\u001b[0;34m\u001b[0m\u001b[0m\n", 
        "\u001b[0;31mNameError\u001b[0m: name 'time' is not defined"
       ]
      }
     ], 
     "prompt_number": 12
    }, 
    {
     "cell_type": "code", 
     "collapsed": true, 
     "input": [
      "# Output to csv", 
      "", 
      "import csv", 
      "", 
      "write = csv.DictWriter(open('movie.sample.output.tsv', 'w'),", 
      "                       ['productId', 'profileName', 'helpfulness', 'score', 'time'],", 
      "                       delimiter = '\\t',", 
      "                       extrasaction = 'ignore')"
     ], 
     "language": "python", 
     "outputs": [], 
     "prompt_number": 15
    }, 
    {
     "cell_type": "code", 
     "collapsed": true, 
     "input": [
      "write.writeheader()", 
      "for review in list_of_dict:", 
      "    write.writerow(review)"
     ], 
     "language": "python", 
     "outputs": [], 
     "prompt_number": 16
    }, 
    {
     "cell_type": "code", 
     "collapsed": true, 
     "input": [], 
     "language": "python", 
     "outputs": []
    }
   ]
  }
 ]
}