Mercurial > public > sg101

annotate tools/translate_yahoo_group.py @ 319:c5055573e8d7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added a tool to translate the old Yahoo Group posts from JSON to CSV suitable for mysqlimport.
author Brian Neal <bgneal@gmail.com>
date Tue, 08 Feb 2011 03:42:04 +0000
parents
children 66cc2f8c4603
rev   line source
bgneal@319 1 """
bgneal@319 2 translate_yahoo_group.py - A management command to read the old Yahoo group
bgneal@319 3 posts (in JSON format) and output as a .csv file, suitable for use as an
bgneal@319 4 input to mysqlimport into a database.
bgneal@319 5
bgneal@319 6 How to import:
bgneal@319 7 mysqlimport --fields-optionally-enclosed-by=\" --fields-terminated-by=, --fields-escaped-by="" --lines-terminated-by="\r\n" --user=root --password --local --default-character-set=utf8 sg101_yahoo_group /path/to/post.csv
bgneal@319 8
bgneal@319 9 """
bgneal@319 10 from __future__ import with_statement
bgneal@319 11 import csv
bgneal@319 12 import optparse
bgneal@319 13 import datetime
bgneal@319 14 import json
bgneal@319 15 import re
bgneal@319 16 import sys
bgneal@319 17
bgneal@319 18
bgneal@319 19 USAGE = "usage: %prog [options] filename1 filename2 ..."
bgneal@319 20 DESCRIPTION = """\
bgneal@319 21 This command converts the SG101 Yahoo Group posts from JSON and outputs the
bgneal@319 22 data as a .csv file suitable for importing into the new database scheme with
bgneal@319 23 the mysqlimport utility.
bgneal@319 24 """
bgneal@319 25
bgneal@319 26 DATE_RE = re.compile(r"(\d{4})-(\d{1,2})-(\d{1,2})T(\d{1,2}):(\d{1,2}):(\d{1,2})Z")
bgneal@319 27
bgneal@319 28 # My scraping wasn't perfect and I forgot Yahoo truncated long Yahoo IDs. Provide
bgneal@319 29 # this translation table to fix up the names.
bgneal@319 30
bgneal@319 31 NAME_EXPANSIONS = {
bgneal@319 32 "atomic_mosqu...": "atomic_mosquitos",
bgneal@319 33 "balsafliesbe...": "balsafliesbetter",
bgneal@319 34 "bambinheadli...": "bambinheadlights",
bgneal@319 35 "batinyourfac...": "batinyourface2002",
bgneal@319 36 "billyblastof...": "billyblastoff805",
bgneal@319 37 "bluegrassban...": "bluegrassbanjobabe",
bgneal@319 38 "bluesbrother...": "bluesbrotherbrian",
bgneal@319 39 "brother_reyn...": "brother_reynolds",
bgneal@319 40 "cannistraro_...": "cannistraro_80303",
bgneal@319 41 "cantemaza_de...": "cantemaza_de_miye",
bgneal@319 42 "captainraven...": "captainravenwood",
bgneal@319 43 "captainsprin...": "captainspringfield",
bgneal@319 44 "colonel_must...": "colonel_mustache",
bgneal@319 45 "cowboy_playi...": "cowboy_playing_dead",
bgneal@319 46 "creature_fea...": "creature_features_fan",
bgneal@319 47 "c_e_l_t_i_c_...": "c_e_l_t_i_c_g_o_t_h",
bgneal@319 48 "dana_lyn_vin...": "dana_lyn_vincent",
bgneal@319 49 "destishtarte...": "destishtartenfort",
bgneal@319 50 "efrem_the_re...": "efrem_the_retarded_rabbit",
bgneal@319 51 "el_capitan_k...": "el_capitan_kosmo",
bgneal@319 52 "epiphonecasi...": "epiphonecasino2001",
bgneal@319 53 "fenderjaguar...": "fenderjaguar1963",
bgneal@319 54 "fenderplayer...": "fenderplayer52886",
bgneal@319 55 "fiberglassro...": "fiberglassrocket",
bgneal@319 56 "formerlyleft...": "formerlylefthanded",
bgneal@319 57 "guildaristoc...": "guildaristocrat62",
bgneal@319 58 "guitardo_mon...": "guitardo_montalbone",
bgneal@319 59 "guitargeekfe...": "guitargeekfestival",
bgneal@319 60 "harrygpelleg...": "harrygpellegrin2003",
bgneal@319 61 "iamtheorigin...": "iamtheoriginaltoblave",
bgneal@319 62 "indiana_surf...": "indiana_surfguitar",
bgneal@319 63 "invisiblesur...": "invisiblesurfers",
bgneal@319 64 "jimmywilseyl...": "jimmywilseylives",
bgneal@319 65 "johnnywrecki...": "johnnywreckingball",
bgneal@319 66 "killers_from...": "killers_from_space",
bgneal@319 67 "los_twang_ma...": "los_twang_marvels",
bgneal@319 68 "midwest_surf...": "midwest_surf_guy",
bgneal@319 69 "murphy_the_s...": "murphy_the_surfie",
bgneal@319 70 "national_dis...": "national_disunion421",
bgneal@319 71 "onlytwothing...": "onlytwothingstodo",
bgneal@319 72 "photog4surfb...": "photog4surfbands",
bgneal@319 73 "poseidonsurf...": "poseidonsurfband",
bgneal@319 74 "reluctantaqu...": "reluctantaquanauts",
bgneal@319 75 "rich_from_th...": "rich_from_the_balboas",
bgneal@319 76 "roland_bette...": "roland_bettenville",
bgneal@319 77 "rvstewartpro...": "rvstewartproductions",
bgneal@319 78 "screamindeat...": "screamindeathmonkey",
bgneal@319 79 "sheckysgotre...": "sheckysgotreverb",
bgneal@319 80 "skatalite_of...": "skatalite_of_love",
bgneal@319 81 "slippy_the_b...": "slippy_the_backflipping_frog",
bgneal@319 82 "somethingabo...": "somethingaboutwendy",
bgneal@319 83 "storm_static...": "storm_static_sleep",
bgneal@319 84 "supertwangre...": "supertwangreverb",
bgneal@319 85 "surfalicious...": "surfaliciousdude1",
bgneal@319 86 "surf_guitar_...": "surf_guitar_lizzy",
bgneal@319 87 "taylorsnyder...": "taylorsnyderband",
bgneal@319 88 "teenageflami...": "teenageflamingovandals",
bgneal@319 89 "thecaveofthe...": "thecaveofthedead",
bgneal@319 90 "tikitakitiki...": "tikitakitikitakitak",
bgneal@319 91 "troublewitht...": "troublewithtreble",
bgneal@319 92 "tumamaentang...": "tumamaentangadeluchador",
bgneal@319 93 "warped_skate...": "warped_skater_182",
bgneal@319 94 "weird_jack_s...": "weird_jack_stinson",
bgneal@319 95 "whatever5050...": "whatever50505050",
bgneal@319 96 "windanseabea...": "windanseabeachboy",
bgneal@319 97 "Wookiee_Smug...": "Wookiee_Smuggler_Drayyken",
bgneal@319 98 }
bgneal@319 99
bgneal@319 100 def convert_date(s):
bgneal@319 101 """
bgneal@319 102 Converts the timestamp string of the form "yyyy-mm-ddTHH:MM:SSZ" into a
bgneal@319 103 datetime object.
bgneal@319 104 """
bgneal@319 105 m = DATE_RE.match(s)
bgneal@319 106 if m is None:
bgneal@319 107 raise CommandError("Invalid date string: %s" % s)
bgneal@319 108
bgneal@319 109 parts = [int(part) for part in m.groups()]
bgneal@319 110 return datetime.datetime(year=parts[0], month=parts[1], day=parts[2],
bgneal@319 111 hour=parts[3], minute=parts[4], second=parts[5])
bgneal@319 112
bgneal@319 113
bgneal@319 114 def process_line(line, writer):
bgneal@319 115 """
bgneal@319 116 Process one line from the JSON file: create a CSV line for the post.
bgneal@319 117
bgneal@319 118 """
bgneal@319 119 # Remove trailing newline
bgneal@319 120 line = line.strip()
bgneal@319 121
bgneal@319 122 # The JSON files consist of one large array of dicts, one dict per line.
bgneal@319 123 # The first line in the file thus has an opening '[' we need to get rid of.
bgneal@319 124 # The last char in each line is either a ',' or a ']', so we need to
bgneal@319 125 # lose the last char as well. That should leave us with just a dict.
bgneal@319 126 if line.startswith('['):
bgneal@319 127 line = line[1:]
bgneal@319 128 line = line[:-1].strip()
bgneal@319 129 parts = json.loads(line)
bgneal@319 130
bgneal@319 131 # Convert the post date; for some strange reason, some dates were not available
bgneal@319 132 # and the date value will be an empty string. In these cases we'll output a NULL.
bgneal@319 133 post_date = parts['date']
bgneal@319 134 if not post_date:
bgneal@319 135 post_date = 'NULL'
bgneal@319 136 else:
bgneal@319 137 post_date = str(convert_date(post_date))
bgneal@319 138
bgneal@319 139 name = parts['name']
bgneal@319 140 name = NAME_EXPANSIONS.get(name, name)
bgneal@319 141
bgneal@319 142 # write the parts as CSV
bgneal@319 143 writer.writerow((parts['msg_id'],
bgneal@319 144 parts['title'].encode('utf-8'),
bgneal@319 145 post_date,
bgneal@319 146 name.encode('utf-8'),
bgneal@319 147 parts['email'].encode('utf-8'),
bgneal@319 148 parts['msg'].encode('utf-8')))
bgneal@319 149
bgneal@319 150
bgneal@319 151 def main(argv=None):
bgneal@319 152
bgneal@319 153 parser = optparse.OptionParser(usage=USAGE, description=DESCRIPTION)
bgneal@319 154 parser.set_defaults(
bgneal@319 155 progress=False,
bgneal@319 156 output='post.csv',
bgneal@319 157 )
bgneal@319 158 parser.add_option("-p", "--progress", action="store_true",
bgneal@319 159 help="Output a . after every 100 posts to show progress [default: %default]")
bgneal@319 160 parser.add_option("-o", "--output",
bgneal@319 161 help="Name of the output file [default: %default]")
bgneal@319 162
bgneal@319 163 opts, args = parser.parse_args(args=argv)
bgneal@319 164
bgneal@319 165 with open(opts.output, "wb") as out_file:
bgneal@319 166 writer = csv.writer(out_file)
bgneal@319 167
bgneal@319 168 for filename in args:
bgneal@319 169 print "Processing " + filename
bgneal@319 170 with open(filename, "r") as in_file:
bgneal@319 171 posts = 0
bgneal@319 172 for line in in_file:
bgneal@319 173 process_line(line, writer)
bgneal@319 174 posts += 1
bgneal@319 175 if opts.progress and posts % 100 == 0:
bgneal@319 176 sys.stdout.write('.')
bgneal@319 177 sys.stdout.flush()
bgneal@319 178 print
bgneal@319 179
bgneal@319 180
bgneal@319 181 if __name__ == '__main__':
bgneal@319 182 try:
bgneal@319 183 main()
bgneal@319 184 except IOError, ex:
bgneal@319 185 sys.exit("IO Error: %s" % ex)
bgneal@319 186 except KeyboardInterrupt:
bgneal@319 187 sys.exit("Control-C interrupt")