Data wragling 数据预处理
Intro
Parsing CSV Files
# Your task is to read the input DATAFILE line by line, and for the first 10 lines (not including the header)
# split each line on "," and then for each line, create a dictionary
# where the key is the header title of the field, and the value is the value of that field in the row.
# The function parse_file should return a list of dictionaries,
# each data line in the file being a single list entry.
# Field names and values should not contain extra whitespace, like spaces or newline characters.
# You can use the Python string method strip() to remove the extra whitespace.
# You have to parse only the first 10 data lines in this exercise,
# so the returned list should have 10 entries!
import os
DATADIR = ""
DATAFILE = "beatles-diskography.csv"
def parse_file(datafile):
data = []
with open(datafile, "rb") as f:
header = f.readline().split(",")
counter = 0
for line in f:
if counter == 10:
break
fields = line.split(",")
entry = {}
for i, value in enumerate(fields):
entry[header[i].strip()] = value.strip()
data.append(entry)
counter += 1
return data
def test():
# a simple test of your implemetation
datafile = os.path.join(DATADIR, DATAFILE)
d = parse_file(datafile)
firstline = {'Title': 'Please Please Me', 'UK Chart Position': '1', 'Label': 'Parlophone(UK)', 'Released': '22 March 1963', 'US Chart Position': '-', 'RIAA Certification': 'Platinum', 'BPI Certification': 'Gold'}
tenthline = {'Title': '', 'UK Chart Position': '1', 'Label': 'Parlophone(UK)', 'Released': '10 July 1964', 'US Chart Position': '-', 'RIAA Certification': '', 'BPI Certification': 'Gold'}
assert d[0] == firstline
assert d[9] == tenthline
test()
Using CSV Module
import os
import pprint
import csv
DATADIR = ""
DATAFILE = "beatles-diskography.csv"
def parse_csv(datafile):
data = []
with open(datafile, "rb") as sd:
r = csv.DictReader(sd)
for line in r:
data.append(line)
return data
if __name__ == "__main__":
datafile = os.path.join(DATADIR, DATAFILE)
d = parse_csv(datafile)
pprint.pprint(d)
Intro to XLRD
import xlrd
datafile = "2013_ERCOT_Hourly_Load_Data.xls"
def parse_file(datafile):
workbook = xlrd.open_workbook(datafile)
sheet = workbook.sheet_by_index(0)
data = [[sheet.cell_value(r, col)
for col in range(sheet.ncols)]
for r in range(sheet.nrows)]
print "\nList Comprehension"
print "data[3][2]:",
print data[3][2]
print "\nCells in a nested loop:"
for row in range(sheet.nrows):
for col in range(sheet.ncols):
if row == 50:
print sheet.cell_value(row, col),
### other useful methods:
print "\nROWS, COLUMNS, and CELLS:"
print "Number of rows in the sheet:",
print sheet.nrows
print "Type of data in cell (row 3, col 2):",
print sheet.cell_type(3, 2)
print "Value in cell (row 3, col 2):",
print sheet.cell_value(3, 2)
print "Get a slice of values in column 3, from rows 1-3:"
print sheet.col_values(3, start_rowx=1, end_rowx=4)
print "\nDATES:"
print "Type of data in cell (row 1, col 0):",
print sheet.cell_type(1, 0)
exceltime = sheet.cell_value(1, 0)
print "Time in Excel format:",
print exceltime
print "Convert time to a Python datetime tuple, from the Excel float:",
print xlrd.xldate_as_tuple(exceltime, 0)
return data
data = parse_file(datafile)
How to find max time and min time --- check your homework
Intro to JSON
Data in More Complex Formats
Intro to XML
#!/usr/bin/env python
# Your task here is to extract data from xml on authors of an article
# and add it to a list, one item for an author.
# See the provided data structure for the expected format.
# The tags for first name, surname and email should map directly
# to the dictionary keys
import xml.etree.ElementTree as ET
article_file = "exampleResearchArticle.xml"
def get_root(fname):
tree = ET.parse(fname)
return tree.getroot()
def get_authors(root):
authors = []
for author in root.findall('./fm/bibl/aug/au'):
data = {
"fnm": None,
"snm": None,
"email": None
}
# YOUR CODE HERE
fnm = author.find("fnm")
if fnm is not None:
data["fnm"] = fnm.text
snm = author.find("snm")
if snm is not None:
data["snm"] = snm.text
email = author.find("email")
if email is not None:
data["email"] = email.text
authors.append(data)
return authors
def test():
solution = [{'fnm': 'Omer', 'snm': 'Mei-Dan', 'email': '[email protected]'}, {'fnm': 'Mike', 'snm': 'Carmont', 'email': '[email protected]'}, {'fnm': 'Lior', 'snm': 'Laver', 'email': '[email protected]'}, {'fnm': 'Meir', 'snm': 'Nyska', 'email': '[email protected]'}, {'fnm': 'Hagay', 'snm': 'Kammar', 'email': '[email protected]'}, {'fnm': 'Gideon', 'snm': 'Mann', 'email': '[email protected]'}, {'fnm': 'Barnaby', 'snm': 'Clarck', 'email': '[email protected]'}, {'fnm': 'Eugene', 'snm': 'Kots', 'email': '[email protected]'}]
root = get_root(article_file)
data = get_authors(root)
assert data[0] == solution[0]
assert data[1]["fnm"] == solution[1]["fnm"]
test()
Handling Attributes
空tag
def get_authors(root):
authors = []
for author in root.findall('./fm/bibl/aug/au'):
data = {
"fnm": None,
"snm": None,
"email": None,
"insr": []
}
# YOUR CODE HERE
fnm = author.find("fnm")
if fnm is not None:
data["fnm"] = fnm.text
snm = author.find("snm")
if snm is not None:
data["snm"] = snm.text
email = author.find("email")
if email is not None:
data["email"] = email.text
# find attribute
for insr in author.iter("insr"):
data["insr"].append(insr.get("iid"))
authors.append(data)
return authors
How many form elements
Using Chrome Dev Tool
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Please note that the function 'make_request' is provided for your reference only.
# You will not be able to to actually use it from within the Udacity web UI.
# Your task is to process the HTML using BeautifulSoup, extract the hidden
# form field values for "__EVENTVALIDATION" and "__VIEWSTATE" and set the appropriate
# values in the data dictionary.
# All your changes should be in the 'extract_data' function
from bs4 import BeautifulSoup
import requests
import json
html_page = "page_source.html"
def extract_data(page):
data = {"eventvalidation": "",
"viewstate": ""}
with open(page, "r") as html:
# do something here to find the necessary values
soup = BeautifulSoup(html, "lxml")
data["eventvalidation"] = soup.find(id="__EVENTVALIDATION")["value"]
data[ "viewstate"] = soup.find(id="__VIEWSTATE")["value"]
return data
def make_request(data):
eventvalidation = data["eventvalidation"]
viewstate = data["viewstate"]
r = requests.post("http://www.transtats.bts.gov/Data_Elements.aspx?Data=2",
data={'AirportList': "BOS",
'CarrierList': "VX",
'Submit': 'Submit',
"__EVENTTARGET": "",
"__EVENTARGUMENT": "",
"__EVENTVALIDATION": eventvalidation,
"__VIEWSTATE": viewstate
})
return r.text
def test():
data = extract_data(html_page)
assert data["eventvalidation"] != ""
assert data["eventvalidation"].startswith("/wEWjAkCoIj1ng0")
assert data["viewstate"].startswith("/wEPDwUKLTI")
test()
Data Cleaning
Measurs of data quality
- validity: conforms to a schema
- accurracy: conforms to gold standard
- completeness: all records?
- consistency: matches other data
- uniformity: some units
Blueprint for cleaning
- audit your data
create a data cleaning plan:
- indentify causes
- define operationn
- test
execute the plan
manually correct
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import xml.etree.cElementTree as ET
from collections import defaultdict
import re
osm_file = open("chicago.osm", "r")
street_type_re = re.compile(r'\S+\.?$', re.IGNORECASE)
street_types = defaultdict(int)
def audit_street_type(street_types, street_name):
m = street_type_re.search(street_name)
if m:
street_type = m.group()
street_types[street_type] += 1
def print_sorted_dict(d):
keys = d.keys()
keys = sorted(keys, key=lambda s: s.lower())
for k in keys:
v = d[k]
print "%s: %d" % (k, v)
def is_street_name(elem):
return (elem.tag == "tag") and (elem.attrib['k'] == "addr:street")
def audit():
for event, elem in ET.iterparse(osm_file):
if is_street_name(elem):
audit_street_type(street_types, elem.attrib['v'])
print_sorted_dict(street_types)
if __name__ == '__main__':
audit()
Auditing a cross-field constraint
def audit_population_density(input_file):
for row in input_file:
population = ensure_float(row['population'])
area = ensure_float(row['areaLand'])
population_density = ensure_float(row['populationDensity'])
if population and area and population_density:
calculated_density = population / area
if math.fabs(calculation_density - population_density) > 10:
print "Possibly bad population density for ", row['name']
def ensure_float(v):
if is_number(v):
return float(v)