<jupyter_start><jupyter_text>Lesson 3 Data Quality<jupyter_code>import csv
import re
import datetime<jupyter_output><empty_output><jupyter_text>Quizzes<jupyter_code>with open("autos.csv", "r") as f:
    reader = csv.DictReader(f)
    header = reader.fieldnames
    
    for row in reader:
        
        # filter rows if URI is not from dbpedia.org
        if re.search("dbpedia", row["URI"]):
            pass
        else:
            print(row["URI"])

        # check if productionStartYear contains a year
        if 
            pass
        else:
            print(row["productionStartYear"])
        
            # check if year is in range 1886-2014
            if row["productionStartYear"] in range(1886,2014+1):
                pass
            else:
                print(row["productionStartYear"])
        
        # convert year field type from datetime to int
"""
Your task is to check the "productionStartYear" of the DBPedia autos datafile for valid values.
The following things should be done:
- check if the field "productionStartYear" contains a year
- check if the year is in range 1886-2014
- convert the value of the field to be just a year (not full datetime)
- the rest of the fields and values should stay the same
- if the value of the field is a valid year in the range as described above,
  write that line to the output_good file
- if the value of the field is not a valid year as described above, 
  write that line to the output_bad file
- discard rows (neither write to good nor bad) if the URI is not from dbpedia.org
- you should use the provided way of reading and writing data (DictReader and DictWriter)
  They will take care of dealing with the header.

You can write helper functions for checking the data and writing the files, but we will call only the 
'process_file' with 3 arguments (inputfile, output_good, output_bad).
"""
import csv
import pprint

INPUT_FILE = 'autos.csv'
OUTPUT_GOOD = 'autos-valid.csv'
OUTPUT_BAD = 'FIXME-autos.csv'

def process_file(input_file, output_good, output_bad):

    with open(input_file, "r") as f:
        reader = csv.DictReader(f)
        header = reader.fieldnames
        
        # loop over rows in reader
        for row in reader:
        
            # filter rows if URI is not from dbpedia.org

            # check if productionStartYear contains a year
        
            # check if year is in range 1886-2014

            # convert year field type from datetime to int
        
            # if checks are passed write line to output_good
        
            # else write line to output_bad file

    # This is just an example on how you can use csv.DictWriter
    # Remember that you have to output 2 files
    with open(output_good, "w") as g:
        writer = csv.DictWriter(g, delimiter=",", fieldnames= header)
        writer.writeheader()
        for row in YOURDATA:
            writer.writerow(row)


def test():

    process_file(INPUT_FILE, OUTPUT_GOOD, OUTPUT_BAD)


if __name__ == "__main__":
    test()<jupyter_output><empty_output><jupyter_text>Problem sets<jupyter_code>#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
In this problem set you work with cities infobox data, audit it, come up with a
cleaning idea and then clean it up. In the first exercise we want you to audit
the datatypes that can be found in some particular fields in the dataset.
The possible types of values can be:
- NoneType if the value is a string "NULL" or an empty string ""
- list, if the value starts with "{"
- int, if the value can be cast to int
- float, if the value can be cast to float, but CANNOT be cast to int.
   For example, '3.23e+07' should be considered a float because it can be cast
   as float but int('3.23e+07') will throw a ValueError
- 'str', for all other values

The audit_file function should return a dictionary containing fieldnames and a 
SET of the types that can be found in the field. e.g.
{"field1": set([type(float()), type(int()), type(str())]),
 "field2": set([type(str())]),
  ....
}
The type() function returns a type object describing the argument given to the 
function. You can also use examples of objects to create type objects, e.g.
type(1.1) for a float: see the test function below for examples.

Note that the first three rows (after the header row) in the cities.csv file
are not actual data points. The contents of these rows should note be included
when processing data types. Be sure to include functionality in your code to
skip over or detect these rows.
"""
import codecs
import csv
import json
import pprint

CITIES = 'cities.csv'

FIELDS = ["name", "timeZone_label", "utcOffset", "homepage", "governmentType_label",
          "isPartOf_label", "areaCode", "populationTotal", "elevation",
          "maximumElevation", "minimumElevation", "populationDensity",
          "wgs84_pos#lat", "wgs84_pos#long", "areaLand", "areaMetro", "areaUrban"]

def audit_file(filename, fields):
    #fieldtypes = dict.fromkeys(fields)
    from collections import defaultdict
    fieldtypes = defaultdict(list)

    # open file
    with open(filename, "r") as f:
        reader = csv.DictReader(f)
        
        # loop over rows
        for ix, row in enumerate(reader):
            
            # skip header rows
            if ix < 3:
                next
            
            # process rows
            else:
                
                # loop over fields
                for pos in range(0, len(fields)):
                    
                    # check for NoneType
                    if row[fields[pos]] == "NULL" or row[fields[pos]] == "":
                        if type(None) not in fieldtypes[fields[pos]]:
                            fieldtypes[fields[pos]].append(type(None))
                    
                    # check for list
                    elif row[fields[pos]].startswith("{"):
                        if type(list()) not in fieldtypes[fields[pos]]:
                            fieldtypes[fields[pos]].append(type(list()))
                    
                    # check for int
                    elif isinstance(row[fields[pos]], int):
                        if type(int()) not in fieldtypes[fields[pos]]:
                            fieldtypes[fields[pos]].append(type(int()))
                            
                    # check for float
                    elif isinstance(row[fields[pos]], float):
                        if type(float()) not in fieldtypes[fields[pos]]:
                            fieldtypes[fields[pos]].append(type(float()))
                    
                    # else assign string value
                    else:
                        if type(str()) not in fieldtypes[fields[pos]]:
                            fieldtypes[fields[pos]].append(type(str()))
            
    return fieldtypes

def test():
    fieldtypes = audit_file(CITIES, FIELDS)

    pprint.pprint(fieldtypes)

    assert fieldtypes["areaLand"] == set([type(1.1), type([]), type(None)])
    assert fieldtypes['areaMetro'] == set([type(1.1), type(None)])
    
if __name__ == "__main__":
    test()

#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
In this problem set you work with cities infobox data, audit it, come up with a
cleaning idea and then clean it up.

Since in the previous quiz you made a decision on which value to keep for the
"areaLand" field, you now know what has to be done.

Finish the function fix_area(). It will receive a string as an input, and it
has to return a float representing the value of the area or None.
You have to change the function fix_area. You can use extra functions if you
like, but changes to process_file will not be taken into account.
The rest of the code is just an example on how this function can be used.
"""
import codecs
import csv
import json
import pprint

CITIES = 'cities.csv'


def fix_area(area):

    try:
        # coerce string to float
        area = float(area)
        
    except ValueError:
        
        # omnit NULL and empty strings
        if area == "NULL" or area == "":
            area = None
        
        else:
            # parse area values from list
            area_stripped = area.strip("{").strip("}").split("|")
        
            # compare area values and keep longer value
            if len(area_stripped[0]) > len(area_stripped[1]):
                area = float(area_stripped[0])
            else:
                area = float(area_stripped[1])

    return area

def process_file(filename):
    # CHANGES TO THIS FUNCTION WILL BE IGNORED WHEN YOU SUBMIT THE EXERCISE
    data = []

    with open(filename, "r") as f:
        reader = csv.DictReader(f)

        #skipping the extra metadata
        for i in range(3):
            l = reader.next()

        # processing file
        for line in reader:
            # calling your function to fix the area value
            if "areaLand" in line:
                line["areaLand"] = fix_area(line["areaLand"])
            data.append(line)

    return data


def test():
    data = process_file(CITIES)

    print("Printing three example results:")
    for n in range(5,8):
        pprint.pprint(data[n]["areaLand"])

    assert data[3]["areaLand"] == None        
    assert data[8]["areaLand"] == 55166700.0
    assert data[20]["areaLand"] == 14581600.0
    assert data[33]["areaLand"] == 20564500.0    


if __name__ == "__main__":
    test()
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
In this problem set you work with cities infobox data, audit it, come up with a
cleaning idea and then clean it up.

In the previous quiz you recognized that the "name" value can be an array (or
list in Python terms). It would make it easier to process and query the data
later if all values for the name are in a Python list, instead of being
just a string separated with special characters, like now.

Finish the function fix_name(). It will recieve a string as an input, and it
will return a list of all the names. If there is only one name, the list will
have only one item in it; if the name is "NULL", the list should be empty.
The rest of the code is just an example on how this function can be used.
"""
import codecs
import csv
import pprint

CITIES = 'cities.csv'


def fix_name(name):
    
    # check if name is equal to NULL or empty
    if name == "NULL" or name == "":
        name = []
    
    else:
        # parse name list
        name = name.strip("{").strip("}").strip(" ").split("|")

    return name


def process_file(filename):
    data = []
    with open(filename, "r") as f:
        reader = csv.DictReader(f)
        #skipping the extra metadata
        for i in range(3):
            l = next(reader)
        # processing file
        for line in reader:
            # calling your function to fix the area value
            if "name" in line:
                line["name"] = fix_name(line["name"])
            data.append(line)
    return data


def test():
    data = process_file(CITIES)

    print("Printing 20 results:")
    for n in range(20):
        pprint.pprint(data[n]["name"])

    assert data[14]["name"] == ['Negtemiut', 'Nightmute']
    assert data[9]["name"] == ['Pell City Alabama']
    assert data[3]["name"] == ['Kumhari']

if __name__ == "__main__":
    test()
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
In this problem set you work with cities infobox data, audit it, come up with a
cleaning idea and then clean it up.

If you look at the full city data, you will notice that there are couple of
values that seem to provide the same information in different formats: "point"
seems to be the combination of "wgs84_pos#lat" and "wgs84_pos#long". However,
we do not know if that is the case and should check if they are equivalent.

Finish the function check_loc(). It will recieve 3 strings: first, the combined
value of "point" followed by the separate "wgs84_pos#" values. You have to
extract the lat and long values from the "point" argument and compare them to
the "wgs84_pos# values, returning True or False.

Note that you do not have to fix the values, only determine if they are
consistent. To fix them in this case you would need more information. Feel free
to discuss possible strategies for fixing this on the discussion forum.

The rest of the code is just an example on how this function can be used.
Changes to "process_file" function will not be taken into account for grading.
"""
import csv
import pprint

CITIES = 'cities.csv'


def check_loc(point, lat, longi):
    
    # parse point values
    point = point.split(" ")
    
    # compare wether parsed values are equal to lat and longi
    if point[0] == lat and point[1] == longi:
        return True
    else:
        return False


def process_file(filename):
    data = []
    with open(filename, "r") as f:
        reader = csv.DictReader(f)
        #skipping the extra matadata
        for i in range(3):
            l = reader.next()
        # processing file
        for line in reader:
            # calling your function to check the location
            result = check_loc(line["point"], line["wgs84_pos#lat"], line["wgs84_pos#long"])
            if not result:
                print("{}: {} != {} {}".format(line["name"], line["point"], line["wgs84_pos#lat"], line["wgs84_pos#long"]))
            data.append(line)

    return data


def test():
    assert check_loc("33.08 75.28", "33.08", "75.28") == True
    assert check_loc("44.57833333333333 -91.21833333333333", "44.5783", "-91.2183") == False

if __name__ == "__main__":
    test()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.093214074512123, 'fraction_numerical': 0.014266706091070372, 'mean_word_length': 2.4060926485397784, 'pattern_counts': {'":': 10, '<': 14, '<?xml version=': 0, '>': 14, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '569672', 'license': 'MIT', 'n_tokens_mistral': 3979, 'n_tokens_neox': 3763, 'n_words': 1557, 'path': 'p3/L3_Data_Quality.ipynb', 'repo_name': 'stefanbuenten/nanodegree', 'size': '13528'}