<jupyter_start><jupyter_text>Data Ingestion – Hierarchical Formats<jupyter_code>from src.setup import *
%load_ext rpy2.ipython
%%capture --no-stdout err
%%R
library(tidyverse)<jupyter_output><empty_output><jupyter_text>JSON  What JSON Looks Like<jupyter_code># A response to an HTTP request
response = requests.get('http://localhost:3001/users') 

# Show status code, content-type, and JSON body
print(response.status_code, response.headers['Content-Type'])
response.text
pprint_json(response.text)
with open('data/3001.json', 'w') as fh:
    fh.write(response.text)
%%bash
jq . data/3001.json
%%bash
js="
const fs = require('fs');
let raw = fs.readFileSync('data/3001.json');
let users = JSON.parse(raw);
console.log(users);
"
echo $js | node
with open('data/3001.json') as fh:
    # Could also call `json.load(fh)` to read file
    raw = fh.read()
    users = json.loads(raw)
users
%%R
library(rjson)
result <- fromJSON(file = "data/3001.json")
result[3]<jupyter_output>$`3`
$`3`$name
[1] "<NAME>"

$`3`$password
[1] "<PASSWORD>!"

$`3`$details
$`3`$details$profession
[1] "Unix Creator"<jupyter_text>NaN Handling and Data Types<jupyter_code># An interpreted float, an overflow, and a truncation
json_str = "[1e308, 1e309, 1.2345678901234567890]"
json.loads(json_str)
%%R -i json_str
options(digits = 22)
fromJSON(json_str)
simplejson.loads(json_str, use_decimal=True)
specials = "[NaN, Infinity, -Infinity]"
vals = json.loads(specials)
vals
%%R -i vals
vals = c(NaN, Inf, -Inf)
print(vals)
print("R version of 'enhanced JSON':")
rjson_str = toJSON(vals)  # function from rjson library
print(rjson_str)
%%R
print("Read back in 'enhanced JSON':")
fromJSON(rjson_str)
%%bash
js="JSON.parse('[NaN, Infinity, -Infinity]');"
echo $js | node | cat
json.loads("[NaN, Infinity, -Infinity]", parse_constant=lambda _: "INVALID")
try:
    json.loads("[nan, +Inf, Foobar]", parse_constant=lambda _: "INVALID")
except Exception as err:
    print_err(err)
%%bash
echo "[NaN, inf, -Infinity]" | jq .<jupyter_output>[
  null,
  1.7976931348623157e+308,
  -1.7976931348623157e+308
]<jupyter_text>JSON Lines<jupyter_code>%%bash
cat -n data/jsonlines.log | fmt -w55 | tr -d " "
%%bash
# Extract registrations
grep "registered" data/jsonlines.log |
    sed 's/^.*registered"://' |
    sed 's/}.*/}/'
%%bash
jq '.registered | select(.username != null)' data/jsonlines.log
with open('data/jsonlines.log') as log:
    for line in log:
        record = json.loads(line)
        if 'registered' in record:
            user = record['registered']
            if 'username' in user:
                print(user)<jupyter_output>{'username': 'bar', 'email': '<EMAIL>'}
{'username': 'baz', 'email': '<EMAIL>'}<jupyter_text>GeoJSON<jupyter_code>with open('data/gz_2010_us_050_00_20m.json', encoding='ISO-8859-1') as fh:
    counties = json.load(fh)

counties.keys()
counties['type'], type(counties['features']), len(counties['features'])
counties['features'][999]
fig, ax = plt.subplots(figsize=(8, 5))
patches, colors, ncolor = [], [], 8

for n, county in enumerate(counties['features']):
    # Only use first polygon if multiple discontiguous regions
    poly = np.array(county['geometry']['coordinates'][0])
    poly = poly.reshape(-1, 2)
    polygon = Polygon(poly)
    patches.append(polygon)
    colors.append(n % ncolor)

p = PatchCollection(patches, cmap=cm.get_cmap('Greys', ncolor))
p.set_array(np.array(colors))
ax.add_collection(p)

ax.set_ylim(24, 50)
ax.set_ylabel("Latitude")
ax.set_xlim(-126, -67)
ax.set_xlabel("Longitude")
ax.set_title("Counties of the United States");
plt.savefig("img/(Ch02)Counties of the United States.png")<jupyter_output><empty_output><jupyter_text>Tidy Geography<jupyter_code>fips = pd.read_csv('data/FIPS.tsv', sep='\t')
fips
fips_map = fips.set_index('FIPS').Name
fips_map
def extremes(coords):
    lat, lon = [], []
    # Expect a list of lists of lists
    for region in coords:
        for point in region:
            lat.append(point[1])
            lon.append(point[0])
    # We are assuming western hemisphere here
    north = max(lat)
    south = min(lat)
    east = max(lon)
    west = min(lon)
    return north, south, east, west
def county_summary(features):
    geo_id = []
    state, county_name, area = [], [], []
    north, south, east, west = [], [], [], []

    for county in features:
        props = county['properties']
        polys = county['geometry']['coordinates']
        geo_id.append(props['GEO_ID'])
        # District of Columbia not US state (default to None)
        state_name = fips_map.get(int(props['STATE']), None)
        state.append(state_name)
        county_name.append(props['NAME'])
        area.append(props['CENSUSAREA'] * 2.59)
        n, s, e, w = extremes(polys)
        north.append(n)
        south.append(s)
        east.append(e)
        west.append(w)

    df = pd.DataFrame({
            'geo_id': geo_id,
            'state': state,
            'county': county_name,
            'area': area,
            'northmost': north,
            'southmost': south,
            'eastmost': east,
            'westmost': west
        })
    return df.set_index('geo_id')
def test_counties(df):
    assert (df.northmost > df.southmost).all()
    assert (df.westmost < df.eastmost).all()
    assert (df.area > 0).all()
census_counties = county_summary(counties['features'])

# Sanity checks (if no assertion violated, we are happy)
test_counties(census_counties)
census_counties<jupyter_output><empty_output><jupyter_text>JSON Schema<jupyter_code>response = requests.get('https://geojson.org/schema/GeoJSON.json')
geojson_schema = json.loads(response.text)

if msg := not_valid(counties, geojson_schema):
    print(msg)
else:
    print("Everything is Happy!")
user_schema = json.loads("""
{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "$id": "http://kdm.training/user-schema.json",
  "title": "User",
  "description": "A User of Our Computer System",
  "type" : "object",
  "required": ["name", "password"],
  "properties" : {
     "name" : {"type" : "string"},
     "password": {
         "description": "Use special characters and mixed case",
         "type": "string"},
     "lucky_numbers": {
         "description": "Up to 6 favorite numbers 1-100",
         "type": "array",
         "items": {
           "type": "number",
           "minimum": 1,
           "maximum": 100
         },
         "uniqueItems": true,
         "minItems": 0,
         "maxItems": 6
    }
  }
}
""")
david = json.loads("""
{
  "name": "<NAME>",
  "password": "<PASSWORD>",
  "details": {
    "profession": "Data Scientist",
    "employer": "KDM"
  },
  "lucky_numbers": [12, 42, 55, 87]
}
""")

if msg := not_valid(david, user_schema):
    print(msg)
barbara_feldon = json.loads("""
{
  "name": 99, 
  "password": "<PASSWORD>"
}
""")

if msg := not_valid(barbara_feldon, user_schema):
    print(msg)
intruder = json.loads("""
{
  "password": "<PASSWORD>", 
  "profession": "Hacker"
}
""")

if msg := not_valid(intruder, user_schema):
    print(msg)
the_count = json.loads("""
{
  "name": "Count von Count",
  "password": "<PASSWORD>",
  "lucky_numbers": ["one", "two", "three"]
}
""")

if msg := not_valid(the_count, user_schema):
    print(msg, "\n--------------------")
george = json.loads("""
{
  "name": "<NAME>",
  "password": "<PASSWORD>",
  "lucky_numbers": [1, 2, 3, 4, 5, 6, 7, 8]
}
""")

if msg := not_valid(george, user_schema):
    print(msg)
revolution_9 = json.loads("""
{
  "name": "<NAME>",
  "password": "<PASSWORD>",
  "lucky_numbers": [9, 9, 9]
}
""")

if msg := not_valid(revolution_9, user_schema):
    print(msg)<jupyter_output>[9, 9, 9] has non-unique elements

Failed validating 'uniqueItems' in schema['properties']['lucky_numbers']:
    {'description': 'Up to 6 favorite numbers 1-100',
     'items': {'maximum': 100, 'minimum': 1, 'type': 'number'},
     'maxItems': 6,
     'minItems': 0,
     'type': 'array',
     'uniqueItems': True}

On instance['lucky_numbers']:
    [9, 9, 9]<jupyter_text>XML  User Records<jupyter_code>import xml.etree.ElementTree as ET
tree = ET.parse('data/users.xml')

# Let us first find the attributes and text of a profession
prof = tree.find('user').find('details').find('profession')
print("Body (title):", prof.text.strip())
print("Attributes:  ", prof.attrib)
items = tree.find('user').find('lucky-numbers').findall('item')
lucky_numbers = [int(item.text) for item in items]
lucky_numbers<jupyter_output><empty_output><jupyter_text>Keyhole Markup Language<jupyter_code>ns = {'kml': "http://www.opengis.net/kml/2.2"}
document = ET.parse('data/gz_2010_us_050_00_20m.kml')

root = document.getroot()
kml_doc = root.find('kml:Document', ns)
folder = kml_doc.find('kml:Folder', ns)

# Make sure we have the same number of counties as with GeoJSON
placemarks = folder.findall('kml:Placemark', ns)
print("Count of placemarks:", len(placemarks))
# Show one Placemark element object
placemarks[0]
# The name of the county is comparatively straightforward
print("County name:", placemarks[0].find('kml:name', ns).text)

# Other county info is only distinguished by attribute
sdata = (placemarks[0].find('kml:ExtendedData', ns)
                      .find('kml:SchemaData', ns)
                      .findall('kml:SimpleData', ns))

# We are going to want GEO_ID, STATE and CENSUSAREA
for record in sdata:
    print(record.attrib, record.text)
coords = (placemarks[0].find('kml:Polygon', ns)
                       .find('kml:outerBoundaryIs', ns)
                       .find('kml:LinearRing', ns)
                       .find('kml:coordinates', ns))
pprint(coords.text)
def kml_extremes(coordinates):
    "Pass in a KML coordinates ElementTree object"
    text_points = coordinates.text.split()
    points = [p.split(',') for p in text_points]
    points = [[float(p[0]), float(p[1])] for p in points]
    # We pass a list-of-list-of-lists here
    return extremes([points])

kml_extremes(coords)
def kml_county_summary(placemarks, ns=ns):
    geo_id = []
    state, county_name, area = [], [], []
    north, south, east, west = [], [], [], []

    for placemark in placemarks:
        # Get county name here and below to assure consistency
        name = placemark.find('kml:name', ns).text
        
        # Other county info is distinguished by XML attribute
        sdata = (placemark.find('kml:ExtendedData', ns)
                          .find('kml:SchemaData', ns)
                          .findall('kml:SimpleData', ns))
        # We want Name, GEO_ID, STATE and CENSUSAREA
        for record in sdata:
            rectype = record.attrib['name']  # XML attrib
            if rectype == 'Name':  # String 'Name' (county)
                # If name is recorded differently, problem!
                assert record.text == name
                county_name.append(name)
            elif rectype == 'GEO_ID':
                geo_id.append(record.text)
            elif rectype == 'CENSUSAREA':
                # Convert to km^2 from mi^2
                area.append(float(record.text) * 2.59)
            elif rectype == 'STATE':
                # District of Columbia is not a US state
                state_name = fips_map.get(int(record.text), None)
                state.append(state_name)

        # We are going to "cheat" here a little bit.  
        # Sometimes a placemark has a top level <MultiGeometry>
        # with several Polygons; we will skip that calculation 
        try:
            coordinates = (placemark
                    .find('kml:Polygon', ns)
                    .find('kml:outerBoundaryIs', ns)
                    .find('kml:LinearRing', ns)
                    .find('kml:coordinates', ns))                
            n, s, e, w = kml_extremes(coordinates)
        except AttributeError:
            n, s, e, w = None, None, None, None
            
        north.append(n); south.append(s); 
        east.append(e); west.append(w)

    df = pd.DataFrame({
            'geo_id': geo_id, 'state': state, 
            'county': county_name, 'area': area,
            'northmost': north, 'southmost': south,
            'eastmost': east, 'westmost': west
        })
    return df.set_index('geo_id')
kml_counties = kml_county_summary(placemarks)
kml_counties<jupyter_output><empty_output><jupyter_text>Configuration Files  INI and Flat Custom Formats<jupyter_code>import configparser
cfg = configparser.ConfigParser()
cfg.read('data/example.ini')

print("Sections:   ", cfg.sections())
print("Owner keys: ", [k for k in cfg['owner']])
print("Owner/name: ", cfg['owner']['name'])
print("Port #:     ", cfg['database'].getint('port'))<jupyter_output>Sections:    ['owner', 'database']
Owner keys:  ['name', 'organization']
Owner/name:  <NAME>
Port #:      143<jupyter_text>TOML<jupyter_code>import toml
toml.load(open('data/example.toml'))
with open('data/example-bad.toml') as fh:
    try:
        cfg = toml.load(fh)
    except Exception as err:
        print_err(err)
!cat -n data/example-bad.toml | tail -8
%%R
library(RcppTOML)
parseTOML("data/example.toml")<jupyter_output>List of 5
 $ clients :List of 2
  ..$ data :List of 2
  .. ..$ : chr [1:2] "gamma" "delta"
  .. ..$ : int [1:2] 1 2
  ..$ hosts: chr [1:2] "alpha" "omega"
 $ database:List of 4
  ..$ connection_max: int 5000
  ..$ enabled       : logi TRUE
  ..$ ports         : int [1:3] 8001 8001 8002
  ..$ server        : chr "192.168.1.1"
 $ owner   :List of 2
  ..$ dob : POSIXct[1:1], format: "1979-05-27 15:32:00"
  ..$ name: chr "<NAME>"
 $ servers :List of 2
  ..$ alpha:List of 2
  .. ..$ dc: chr "eqdc10"
  .. ..$ ip: chr "10.0.0.1"
  ..$ beta :List of 2
  .. ..$ dc: chr "eqdc10"
  .. ..$ ip: chr "10.0.0.2"
 $ title   : chr "TOML Example"<jupyter_text>Yet Another Markup Language<jupyter_code>import yaml
order = yaml.load(open('data/example.yaml'))
order
# Is nested dict same object under different keys?
order['ship-to'] is order['bill-to']
with open('data/multidoc.yaml') as stream:
    docs = yaml.load_all(stream)
    print(docs, '\n')
    for doc in docs:
        print(doc)
try:
    yaml.load(open('data/example-bad.yaml'))
except Exception as err:
    print_err(err)
%%bash
cat -n data/example-bad.yaml | sed '15,19p;d' 
try:
    for doc in yaml.load_all(open('data/multidoc-bad.yaml')):
        print(doc)
except Exception as err:
    print_err(err)<jupyter_output>{'name': 'David', 'age': 55}
{'name': 'Mei', 'age': 50}
ScannerError
mapping values are not allowed here   in "data/multidoc-bad.yaml",
line 10, column 12<jupyter_text>NoSQL Databases<jupyter_code># Assume that MongoDB is running on local system
from pymongo import MongoClient
client = MongoClient('mongodb://localhost:27017')
# What databases exist on the local server?
client.database_names()
db_biz = client.business
print("Restaurants:", db_biz.info.count())
for biz in db_biz.info.find(limit=3):
    pprint(biz)
print("Reviews:", db_biz.reviews.count())
for review in db_biz.reviews.find(limit=3):
    pprint(review)
query = {'price': 'cheap', 'name': 'City Kitchen Inc.'}
for review in db_biz.reviews.find(query, limit=4):
    pprint(review)<jupyter_output>{'_id': ObjectId('5f30928db504836031a2c2ea'),
 'name': 'City Kitchen Inc.',
 'price': 'cheap',
 'rating': 3}
{'_id': ObjectId('5f30928db504836031a2c435'),
 'name': 'City Kitchen Inc.',
 'price': 'cheap',
 'rating': 7}
{'_id': ObjectId('5f30928db504836031a2c553'),
 'name': 'City Kitchen Inc.',
 'price': 'cheap',
 'rating': 3}
{'_id': ObjectId('5f30928db504836031a2c5d6'),
 'name': 'City Kitchen Inc.',
 'price': 'cheap',
 'rating': 1}<jupyter_text>Missing Fields<jupyter_code>ratings = []
query = {'name': 'City Kitchen Inc.'}
for review in db_biz.reviews.find(query):
    try:
        ratings.append(review['rating'])
    except KeyError:
        pass

n = len(ratings)
pd.Series(ratings).plot(kind="hist", title=f"{n} ratings")
plt.savefig(f"img/(Ch02){n} ratings.png")
db_biz.reviews.find({'name': 'City Kitchen Inc.'}).count()
list(db_biz.reviews.find({'name': 'City Kitchen Inc.', 'rating': None}))<jupyter_output><empty_output><jupyter_text>Denormalization and Its Discontents<jupyter_code>def has_best_review(name, db=db_biz):
    "Return phone if restaurant has at least one 10 rating"
    query = {'name': name, 'rating': 10}
    review = None
    
    # Fast path has phone in local results
    for review in db.reviews.find(query):
        phone = review.get('phone')
        if phone:
            return f"Call {name} at {phone}! (FAST query)"

    # If there were no ratings of 10, we don't like it!
    if not review:
        return f"Do not bother with {name}!"

    # MUCH SLOWER path is second query
    info = db.info.find_one({'name': name})
    return f"Call {name} at {info['phone']}! (SLOW query)"
has_best_review('Salty Big Take-Out')
has_best_review('City Kitchen Inc.')
has_best_review('Out of Business')
query = {'name': '<NAME>', 
         'rating': 10, 'phone':{"$ne":None}}

db_biz.reviews.find_one(query)
db_biz.info.find_one({'name': '<NAME>-Out'})<jupyter_output><empty_output><jupyter_text>Key/Value Stores<jupyter_code>biz = dbm.open('data/keyval.db')
seed(6)
# Keys are bytes; could convert to strings if desired
sample(list(biz.keys()), 10)
name = b"Tasty Fish Inc."
print("Overview:", biz[b"DESCRIPTION"])
print("Cuisine: ", biz[name + b"::info::cuisine"] )
print("Ratings: ", biz[name + b"::ratings"][:30], "...")
for key, val in biz.items():
    if key.startswith(b'Tasty Fish Inc.::'):
        print(key.decode(), '\t', val[:30].decode())
for key, val in biz.items():
    if key.startswith(b'Fish City Restaurant'):
        print(key, val[:30])<jupyter_output>b'Fish City Restaurant::ratings' b'6;10;4;3;10;5;1;4;7;8;5;2;1;5;'
b'Fish City Restaurant//ratings' b'9'
b'Fish City Restaurant::info::phone' b'+1 851 555 1082'
b'Fish City Restaurant::info::cuisine' b'American'<jupyter_text>Exercises  Create a Relational Model<jupyter_code>dbm.whichdb('data/keyval.db')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16512069736253912, 'fraction_numerical': 0.024865891819400982, 'mean_word_length': 3.5103326612903225, 'pattern_counts': {'":': 41, '<': 80, '<?xml version=': 0, '>': 80, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 2}, 'pii_count': 3, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '535982', 'license': 'MIT', 'n_tokens_mistral': 6686, 'n_tokens_neox': 6296, 'n_words': 1482, 'path': '02-Data_Ingestion-Hierarchical.ipynb', 'repo_name': 'PacktPublishing/Cleaning-Data-for-Effective-Data-Science', 'size': '17970'}