Lesson 02 ETL Tutorial 01 Amazon Movie Reviews - adparker/GADSLA_1403 GitHub Wiki

# Pretty printer
import pprint

list_of_dicts = []
dict_of_data = {}
# Opens a handle to the file, but does not read anything yet.
fh = open('movies.small.txt')

# This is more memory efficient.
# Reads one line at a time.
for line in fh:
    # Strip off leading and trailing white-space.
    line = line.strip()
    if line != '':
        # Assume it's safe to split:
        label, data = line.split(': ', 1)
        dict_of_data[label] = data
    else:
        # We're at the end of a record. Everything we want is in the
        # dictionary.
        list_of_dicts.append(dict_of_data)

# Look at the first three items.
pprint.pprint(list_of_dicts[0:3])

>>> fh = open('movies.small.txt')

>>> lines = fh.readlines()

# Not super necessary in this case, but in general you should close things you open. :)
>>> fh.close()

>>> lines[0]
'product/productId: B003AI2VGA\n'

>>> lines[0:10]
['product/productId: B003AI2VGA\n', 'review/userId: A141HP4LYPWMSR\n', 'review/profileName: Brian E. Erland "Rainbow Sphinx"\n', 'review/helpfulness: 7/7\n', 'review/score: 3.0\n', 'review/time: 1182729600\n', 'review/summary: "There Is So Much Darkness Now ~ Come For The Miracle"\n', '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).\n', '\n', 'product/productId: B003AI2VGA\n']

>>> pprint.pprint(lines[0:10])
['product/productId: B003AI2VGA\n',
 'review/userId: A141HP4LYPWMSR\n',
 'review/profileName: Brian E. Erland "Rainbow Sphinx"\n',
 'review/helpfulness: 7/7\n',
 'review/score: 3.0\n',
 'review/time: 1182729600\n',
 'review/summary: "There Is So Much Darkness Now ~ Come For The Miracle"\n',
 '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).\n',
 '\n',
 'product/productId: B003AI2VGA\n']

>>> lines[0]
'product/productId: B003AI2VGA\n'
>>> lines[0].strip()
'product/productId: B003AI2VGA'

>>> lines[8]
'\n'
>>> lines[8].strip()
''  # This is a string that is zero length. The Empty String.

# Create a cleaned version.
>>> cleanlines = [ line.strip() for line in lines ]

cleanlines = []
for line in lines:
    cleaned = line.strip()
    cleanlines.append(cleaned)

>>> cleanlines[0]
'product/productId: B003AI2VGA'
>>> cleanlines[8]
''

list_of_lists = []
group = []
for line in cleanlines:
    if line != '':
        # Add to the current group
        group.append(line)
    else:
        # Starting a new group.
        # Append the group to the grouped lines and then
        list_of_lists.append(group)
        # Point 'group' to a newly created list.
        # This does not affect the list we just appended to groupedlines
        group = []

>>> from pprint import pprint ## Saves some typing later
>>> pprint(list_of_lists[100])
['product/productId: B000063W1R',
 'review/userId: A2CU4L3OK00M23',
 'review/profileName: C. Reich',
 'review/helpfulness: 1/1',
 'review/score: 5.0',
 'review/time: 1189036800',
 'review/summary: A classic movie',
 'review/text: There is nothing bad to say about this movie.  The story, the acting, everything is well done.  I bought it to watch over and over.']

>>> line = list_of_lists[0][3]
>>> line
'review/helpfulness: 7/7'
>>> line.split(': ', 1)
['review/helpfulness', '7/7']

['review/helpfulness', '7/7']

[ { ... }, { ... }, # bunch of dictionaries
{ ... 'review/helpfulness', '7/7', ... }, # a specific item in a dictionary
{ ... } ]

list_of_dicts = []
for group in list_of_lists:
    # Create a new dict for each group.
    group_dict = {}
    for line in group:
        # Split on ': ' once, giving me two pieces.
        longkey, value = line.split(': ', 1)
        # Get the second part of product/productId
        shortkey = longkey.split('/')[1]
        group_dict[shortkey] = value
    # We finished the group. Save it into the list_of_dicts.
    list_of_dicts.append(group_dict)

>>> len(list_of_dicts)
5554
>>> len(list_of_lists)
5554
>>> list_of_dicts[0]
>>> pprint(list_of_dicts[0])
{'helpfulness': '7/7',
 'productId': 'B003AI2VGA',
 'profileName': 'Brian E. Erland "Rainbow Sphinx"',
 'score': '3.0',
 'summary': '"There Is So Much Darkness Now ~ Come For The Miracle"',
 '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).',
 'time': '1182729600',
 'userId': 'A141HP4LYPWMSR'}

# Modify in place.
import datetime
for review in list_of_dicts:
    # Convert to float
    review['score'] = float(review['score'])
    # Convert to readable time
    int_timestamp = int(review['time'])
    datetime_obj = datetime.datetime.fromtimestamp(int_timestamp)
    str_iso_datetime = datetime_obj.isoformat()
    review['time'] = str_iso_datetime

# Modify in place some more!!
# Convert helpfulness to a float.
for review in list_of_dicts:
    helpful_str, numberof_str = review['helpfulness'].split('/')
    helpful_int = int(helpful_str)
    numberof_int = int(numberof_str)
    if numberof_int == 0: # Why is this needed?
        numberof_int = 1.0
    review['helpfulness'] = float(helpful_int)/numberof_int
    review['helpful'] = helpful_int
    review['total'] = numberof_int

>>> pprint(list_of_dicts[501])
{'helpful': 1,
 'helpfulness': 0.5,
 'productId': '0790747324',
 'profileName': 'Third Grade Teacher',
 'score': 5.0,
 'summary': 'Travel in Time with HG Wells',
 'text': "Several 3rd grade classes in my school viewed this movie (rated G)after reading a version of the book.  We'd read it chapter by chapter and reviewed with EOG-style questions.  Wow-talk about a page-turner!  The children could not wait to read the next chapter just as soon as we finished the last.  Watching the movie was a fun way to see the images we'd created in our minds come to life.  This was the original version (I think the best) and because it is rated G, I felt very comfortable showing it to my students.",
 'time': '2009-05-24T17:00:00',
 'total': 2,
 'userId': 'A2QP12447GKEDU'}

writer = csv.DictWriter(open('small.tsv', 'w'),  # file to write to
                        ['productId', 'helpful', 'score', 'profileName'], # columns to output
                        delimiter='\t',    # delimiter (separator)
                        extrasaction='ignore')  # ignore columns in the dict if the are skipped

writer.writeheader()   
for review in list_of_dicts:
    writer.writerow(review)