Introduction To Data Systems: Building From Python

Preface
Who Is This Book for?
Philosophy of This Book
Web Resources
To Students
To Instructors
Software Assumptions
Online Corrigenda
Acknowledgments
Contents
Part I Foundation
1 Introduction
1.1 A Broad View of Data Systems
1.1.1 Reading Questions
1.2 The Sources of Data
1.2.1 Reading Questions
1.3 The Forms of Data
1.3.1 Reading Questions
1.4 Book Organization
1.4.1 Exercises
2 File Systems and File Processing
2.1 File Systems
2.1.1 Hierarchical Organization
2.1.2 Paths
2.1.3 Python File System and Path Facilities
2.1.4 Reading Questions
2.1.5 Exercises
2.2 File Level Operations
2.2.1 File Open and Close
2.2.2 Text File Encoding
2.2.3 Reading Questions
2.2.4 Exercises
2.3 Processing Files for Data
2.3.1 Single Data Item per Line
2.3.2 Multiple Data Items per Line
2.3.3 Reading Questions
2.3.4 Exercises
2.4 JSON File Processing
2.4.1 Writing Data Structures to JSON
2.4.2 Reading Data Structures from JSON
2.4.3 Reading Questions
2.4.4 Exercises
3 Python Native Data Structures
3.1 List Patterns
3.1.1 Accumulation
3.1.2 Unary Vector Operations
3.1.3 Binary Vector Operations
3.1.4 Filter
3.1.5 Reduction
3.1.6 Reading Questions
3.1.7 Exercises
3.1.7.1 Accumulation
3.1.7.2 Unary Vector Operations
3.1.7.3 Binary Vector Operations
3.1.7.4 Filtering
3.2 Dictionaries
3.2.1 Reading Questions
3.2.2 Exercises
3.3 Python Features
3.3.1 Functions as Objects
3.3.2 Lambda Functions
3.3.3 List Comprehensions
3.3.4 Reading Questions
3.3.5 Exercises
3.3.5.1 Functions as Objects
3.3.5.2 Mapping Functions
3.3.5.3 Lambda Functions
3.3.5.4 List Comprehensions
3.4 Representing General Data Sets
3.4.1 Dictionary of Lists
3.4.2 List of Lists
3.4.3 List of Dictionaries
3.4.4 Reading Questions
3.4.5 Exercises
4 Regular Expressions
4.1 Motivation
4.1.1 Reading Questions
4.2 Terminology
4.2.1 Reading Questions
4.3 The Regular Expression Language
4.3.1 Literal Characters
4.3.2 Single Character Wildcard Matching
4.3.2.1 Dot
4.3.2.2 Predefined Single Character Sets
4.3.2.3 User-Defined Single Character Sets
4.3.3 Repetition
4.3.4 Disjunction
4.3.5 Boundaries/Anchors
4.3.6 Grouping
4.3.7 Flags
4.3.7.1 Case Insensitive
4.3.7.2 Multi-line
4.3.7.3 Single Line
4.3.8 Reading Questions
4.3.9 Exercises
4.4 Python Programming with Regular Expressions
4.4.1 Specifying Patterns
4.4.2 The re Module Interface
4.4.3 Reading Questions
4.4.4 Exercises
Part II Data Systems: The Data Models
5 Data Systems Models
5.1 Data Model Framework
5.1.1 Structure
5.1.2 Operations
5.1.3 Constraints
5.1.4 Reading Questions
5.2 Tabular Model Overview
5.2.1 Structure
5.2.2 Operations
5.2.3 Constraints
5.2.4 Reading Questions
5.3 Relational Model Overview
5.3.1 Structure
5.3.2 Operations
5.3.3 Constraints
5.3.4 Reading Questions
5.4 Hierarchical Model Overview
5.4.1 Structure
5.4.2 Operations
5.4.3 Constraints
5.4.4 Reading Questions
6 Tabular Model: Structure and Formats
6.1 Tidy Data
6.1.1 Reading Questions
6.1.2 Exercises
6.2 Tabular Data Format
6.2.1 Format Background
6.2.2 Format for Tabular Data
6.2.2.1 Tabular Format Design
6.2.3 Tabular Format File Processing
6.2.3.1 CSV Parsing [Optional]
6.2.4 Reading Questions
6.2.5 Exercises
6.3 Tabular Structure as pandas DataFrame
6.3.1 DataFrame Creation
6.3.2 Operations Involving Whole Data Frames
6.3.3 Reading Questions
6.3.4 Exercises
7 Tabular Model: Access Operations and Pandas
7.1 Tabular Operations Overview
7.1.1 Access Operations
7.1.2 Computational Operations
7.1.3 Mutation Operations
7.1.4 Advanced Operations
7.1.5 Reading Questions
7.2 Preliminaries and Example Data Sets
7.2.1 Reading Questions
7.3 Access and Computation Operations
7.3.1 Single Column Projection and Vector Operations
7.3.2 Multi-Column Projection of a DataFrame
7.3.3 Row Selection by Slice
7.3.3.1 Position Slicing for Selecting Rows
7.3.3.2 Index Slicing for Selecting Rows
7.3.4 Row Selection by Condition
7.3.5 Combinations of Projection and Selection
7.3.5.1 Access a Single Element
7.3.5.2 Querying a Single Column or Single Row
7.3.5.3 Querying a Subset of a Single Column or Single Row
7.3.5.4 Generalized Projection and Selection
7.3.6 Iteration over Rows and Columns
7.3.7 Reading Questions
7.3.8 Exercises
8 Tabular Model: Advanced Operations and Pandas
8.1 Aggregating and Grouping Data
8.1.1 Aggregating Single Series
8.1.2 Aggregating a Data Frame
8.1.3 Aggregating Selected Rows
8.1.4 General Partitioning and GroupBy
8.1.5 Indicators Grouping Example
8.1.6 Reading Questions
8.1.7 Exercises
8.2 Mutation Operations for a Data Frame
8.2.1 Operations to Delete Columns and Rows
8.2.1.1 Single Column Deletion
8.2.1.2 Multiple Column Deletion
8.2.1.3 Row Deletion
8.2.2 Operation to Add a Column
8.2.3 Updating Columns
8.2.3.1 Update Entire Column
8.2.3.2 Selective Column Assignment
8.2.4 Reading Questions
8.2.5 Exercises
8.3 Combining Tables
8.3.1 Concatenating Data Frames Along the Row Dimension
8.3.1.1 Meaningful Row Index
8.3.1.2 Meaningful Index with Levels
8.3.1.3 No Meaningful Index
8.3.2 Concatenating Data Frames Along the Column Dimension
8.3.2.1 Single Level Row Index and New Columns
8.3.2.2 Introducing a Column Level
8.3.3 Joining/Merging Data Frames
8.3.3.1 Using Index Level
8.3.3.2 Using Specific Columns
8.3.4 Reading Questions
8.3.5 Exercises
8.4 Missing Data Handling
8.4.1 Reading Questions
9 Tabular Model: Transformations and Constraints
9.1 Tabular Model Constraints
9.1.1 Reading Questions
9.1.2 Exercises
9.2 Tabular Transformations
9.2.1 Transpose
9.2.2 Melt
9.2.2.1 [Optional] Stack Examples
9.2.3 Pivot
9.2.3.1 Pivot Table
9.2.4 Reading Questions
9.2.5 Exercises
9.3 Normalization: A Series of Vignettes
9.3.1 Column Values as Mashup
9.3.1.1 Example: Code and Country Mashup
9.3.1.2 Example: Year and Month Mashup
9.3.2 One Relational Mapping per Row
9.3.2.1 Example: One Value Column and One Index Column
9.3.2.2 Example: One Value Column and Two Index Columns
9.3.3 Columns as Values and Mashups
9.3.3.1 Example: Single Variable with Multiple Years
9.3.3.2 Example: Multiple Variables with Multiple Years
9.3.4 Exactly One Table per Logical Mapping
9.3.4.1 Example: Variable Values as Two Tables
9.3.4.2 Example: Separate Logical Mappings in a Single Table
9.3.5 Reading Questions
9.4 Recognizing Messy Data
9.4.1 Focus on Each Column as Exactly One Variable (TidyData1)
9.4.2 Focus on Each Row Giving Exactly One Mapping (TidyData2)
9.4.3 Focus on Each Table Representing One Data Set (TidyData3)
9.4.4 Reading Questions
9.4.5 Exercises
10 Relational Model: Structure and Architecture
10.1 Background
10.1.1 Motivation and Requirements
10.1.2 The Relational Database Solution
10.1.3 Types of Relational Databases
10.1.4 Reading Questions
10.2 Structure
10.2.1 Single Table Characteristics
10.2.1.1 Functional Dependencies
10.2.1.2 Table Keys
10.2.1.3 Illustrative Example
10.2.2 Multiple Table Characteristics
10.2.3 Reading Questions
10.3 Database Architecture
10.3.1 Reading Questions
11 Relational Model: Single Table Operations
11.1 Example Data Sets
11.1.1 Reading Questions
11.2 Projecting Column Fields
11.2.1 Single Column Field Projection
11.2.2 Multiple Column Field Projection
11.2.3 Simple Subquery
11.2.4 Ordering Results
11.2.5 Reading Questions
11.2.6 Exercises
11.3 Selecting and Filtering Rows
11.3.1 Uniqueness Filtering
11.3.2 Row Selection by Filtering
11.3.3 Missing Values
11.3.4 Additional Examples
11.3.5 Reading Questions
11.3.6 Exercises
11.4 Column-Vector Operations
11.4.1 Reading Questions
11.4.2 Exercises
11.5 Aggregation
11.5.1 Counting Rows for Fields
11.5.2 Reading Questions
11.5.3 Exercises
11.6 Partitioning and Aggregating
11.6.1 Reading Questions
11.6.2 Exercises
12 Relational Model: Multiple Tables Operations
12.1 Preliminaries and Example Data Set
12.1.1 Data Set: The school Database Schema
12.1.2 Table Relationships
12.1.3 SQL Execution Plan
12.1.4 Reading Questions
12.1.5 Exercises
12.2 Overview of Join Operations
12.3 Inner Joins
12.3.1 Two Table SQL Inner Join
12.3.2 [Optional] Cartesian Product-Based Inner Join
12.3.3 Inner Join to Fill Redundant Fields
12.3.4 Three-Table Join
12.3.5 Join Table from a Subquery
12.3.6 Reading Questions
12.3.7 Exercises
12.4 Outer Joins
12.4.1 Left and Right Joins
12.4.2 Full Outer Join
12.4.3 Reading Questions
12.4.4 Exercises
12.5 Partitioning and Grouping Information
12.5.1 Reading Questions
12.5.2 Exercises
12.6 Subqueries
12.6.1 Reading Questions
12.6.2 Exercises
13 Relational Model: Database Programming
13.1 Making Connections
13.1.1 The Connection String
13.1.2 Connecting and Closing
13.1.3 Reading Questions
13.1.4 Exercises
13.2 Executing Queries and Basic Retrieval of Results
13.2.1 Basic Query and Fetching Results
13.2.1.1 Result Data
13.2.1.2 Native Data Structure to pandas
13.2.1.3 Database Requests Directly through pandas
13.2.2 Reading Questions
13.2.3 Exercises
13.3 More Advanced Techniques
13.3.1 Record at a Time
13.3.1.1 Result Proxy as an Iterator
13.3.1.2 Fetch One
13.3.2 Chunks
13.3.2.1 Fetch Many
13.3.2.2 Using Pandas with Chunk Size
13.3.3 Working with Multiple Databases
13.3.4 Reading Questions
13.3.5 Exercises
13.4 Incorporating Variables
13.4.1 Python String Composition
13.4.2 Binding Variables
13.4.2.1 Prepare
13.4.2.2 Bind
13.4.2.3 Execute
13.4.3 Reading Questions
13.4.4 Exercises
14 Relational Model: Design, Constraints, and Creation
14.1 Motivation and Process
14.2 Designing Tables
14.2.1 Functional Dependencies
14.2.2 Table Design: Advice and Best Practices
14.2.3 Table Primary Key
14.2.4 Reading Questions
14.2.5 Exercises
14.3 Table Fields
14.3.1 Single Field Issues
14.3.2 Field Relationship Issues
14.3.2.1 List of Values in a Single Field
14.3.2.2 Using Multiple Fields Instead of List of Values
14.3.3 Field Data Types
14.3.4 Field Design: Advice and Best Practices
14.3.5 Reading Questions
14.3.6 Exercises
14.4 Relationships Between Tables
14.4.1 Designing for Many-to-One Relationships
14.4.2 Designing for Many-to-Many Relationships
14.4.3 Reading Questions
14.4.4 Exercises
14.5 Table and Schema Creation
14.5.1 Fields
14.5.2 Table Constraints
14.5.2.1 Primary Key
14.5.2.2 Foreign Key
14.5.2.3 CHECK Constraint
14.5.3 Programming and Development Advice
14.5.4 Reading Questions
14.5.5 Exercises
14.6 Table Population
14.6.1 Examples
14.6.2 Programming for Table Population
14.6.2.1 Example 1: Table Population from Python List of Row Lists
14.6.2.2 Example 2: Table Population using Python CSV DictReader
14.6.2.3 Example 3: Table Population from a pandas DataFrame
14.6.2.4 Example 4: Table Population Using pandas Method
14.6.3 Reading Questions
14.6.4 Exercises
15 Hierarchical Model: Structure and Formats
15.1 Motivation
15.2 Representation of Trees
15.2.1 Terminology
15.2.2 Python Native Data Structures and Nesting
15.2.2.1 Representing Graphs
15.2.2.2 Representing Trees
15.2.3 Traversals and Paths
15.2.4 Reading Questions
15.3 JSON
15.3.1 Reading Questions
15.3.2 Exercises
15.4 XML
15.4.1 XML Structure
15.4.2 Extracting Data from an XML File
15.4.3 Reading Questions
15.4.4 Exercises
Further Exploration
16 Hierarchical Model: Operations and Programming
16.1 Operations Overview
16.1.1 Reading Questions
16.2 JSON Procedural Programming
16.2.1 Access and Traversal Operations Example
16.2.1.1 Example: Simple Table in JSON
Example: Simple Table in JSON
16.2.1.2 Single Table from JSON with Additional Level
Single Table from JSON with Additional Level
16.2.2 Node Creation
16.2.3 Node Attribute Updates
16.2.4 Reading Questions
16.2.5 Exercises
16.3 XML Procedural Operations
16.3.1 Reading and Traversing XML Data
16.3.1.1 Indicators Example
16.3.1.2 School Example
16.3.1.3 Wrangling Instructors
16.3.1.4 Wrangling Departments
16.3.1.5 Wrangling Courses
16.3.2 Creating XML Data
16.3.3 Further Operations
16.3.4 Reading Questions
16.3.5 Exercises
16.4 XPath
16.4.1 Paths in XML Documents
16.4.2 Paths and Expressions in XPath
16.4.3 XPath Syntax
16.4.4 XPath Axes
16.4.5 XPath Predicates and Built-in Functions
16.4.6 Python Programming with XPath
16.4.7 Case Study Example
16.4.8 Reading Questions
16.4.9 Exercises
Further Reading
17 Hierarchical Model: Constraints
17.1 Motivation
17.1.1 Reading Questions
17.2 Well-Formed XML
17.2.1 Reading Questions
17.3 Document Type Definition
17.3.1 Declaring Elements
17.3.2 Declaring Attributes and Entities
17.3.3 Example DTD Declarations
17.3.4 DTD Validation of an XML Document
17.3.5 Exercises
17.4 XML Schema
17.4.1 Root of an XML Schema
17.4.2 Declaring Elements and Attributes
17.4.3 XSD Types
17.4.4 XSD Restrictions
17.4.5 An XSD Example
17.4.6 Validating an XML Document
17.4.7 Exercises
17.5 JSON Schema
17.5.1 Basics of JSON Schema
17.5.2 Validating a JSON Document Using a JSON Schema
17.5.3 Exercises
Part III Data Systems: The Data Sources
18 Overview of Data Systems Sources
18.1 Architecture
18.2 Data Sources
18.2.1 Local Files
18.2.2 Database Systems
18.2.3 Web Servers
18.2.4 API Service
18.2.5 Reading Questions
19 Networking and Client–Server
19.1 The Network Architecture
19.1.1 Host Addressing
19.1.2 Packet Switching and Routing
19.1.3 Summary Characteristics of the Network
19.1.4 Reading Questions
19.2 The Network Protocol Stack
19.2.1 Media Access Protocol Layer
19.2.2 Network Protocol Layer
19.2.3 Transport Protocol Layer
19.2.4 The Socket Interface
19.2.5 Application Protocols
19.2.6 Reading Questions
19.3 Client–Server Model
19.3.1 Server Application
19.3.2 Client Application
19.3.3 Reading Questions
20 The HyperText Transfer Protocol
20.1 Identifying Resources with URLs and URIs
20.1.1 Host Locations
Host Locations
20.1.2 Resource Paths
Resource Paths
20.1.3 URL Syntax
20.1.4 Reading Questions
20.2 HTTP Definition
20.2.1 Message Format
20.2.2 Request Messages
20.2.3 Connections and Message Exchange
20.2.3.1 Client-Side HTTP Steps
Client-Side HTTP Steps
20.2.4 Socket Level Programming Examples
20.2.4.1 Example of Socket-Based GET Request
Example Socket-Based GET Request
20.2.4.2 Example of Socket-Based POST Request
Example Socket-Based POST Request
20.2.5 Request Header Lines
20.2.6 Response Messages
20.2.7 Redirection
20.2.8 Reading Questions
20.2.9 Exercises
20.3 Programming HTTP Using Requests
20.3.1 GET Requests
20.3.1.1 Example 1: GET of HTML
Example 1: GET of HTML
20.3.1.2 Example 2: GET Specifying Headers for Request
Example 2: GET Specifying Headers for Request
20.3.1.3 Example 3: GET with Query Parameters
Example 3: GET with Query Parameters
20.3.2 POST Requests
20.3.2.1 Example 1: POST with Form Data Body
Example 1: POST with Form Data Body
20.3.2.2 Example 2: POST with JSON Body
20.3.3 Response Attributes
20.3.4 Reading Questions
20.3.5 Exercises
20.4 Command Line HTTP with curl
20.4.1 Basics
20.4.1.1 Options Controlling Output
20.4.1.2 Options to Show Response Metadata
20.4.2 Sending Custom Request Header Lines
20.4.3 Query Parameters
20.4.4 POST Requests
20.4.4.1 POST with No Body
POST with no Body
20.4.4.2 POST with Form Data
POST with Form Data
20.4.4.3 POST with JSON Data
POST with JSON Data
20.4.5 Exploring Further
20.4.6 Exercises
21 Interlude: Client Data Acquisition
21.1 Encoding and Decoding
21.1.1 Python Strings and Bytes
21.1.1.1 The Encode Operation: A String to Bytes
The Encode Operation: a String to Bytes
21.1.1.2 The Decode Operation: Bytes to a String
The Decode Operation: Bytes to a String
21.1.2 Prelude to Format Examples
21.1.3 Reading Questions
21.1.4 Exercises
21.2 CSV Data
21.2.1 CSV from File Data
21.2.2 CSV from Network Data
21.2.2.1 Option 1: From String Text
Option 1: From String Text
21.2.2.2 Option 2: From Underlying Bytes
Option 2: From Underlying Bytes
21.2.3 Reading Questions
21.2.4 Exercises
21.3 JSON Data
21.3.1 JSON from File
21.3.2 JSON from Network
21.3.2.1 JSON from String Data in Response
JSON from String Data in Response
21.3.2.2 JSON from Bytes Data in Response Body
JSON from Bytes Data in Response Body
21.3.3 Reading Questions
21.3.4 Exercises
21.4 XML Data
21.4.1 XML from File Data
21.4.2 From Network
21.4.2.1 Using Parse on Bytes
Using parse on Bytes
21.4.2.2 Using fromstring() with Bytes and Strings
Using fromstring() with Bytes and Strings
21.4.3 Reading Questions
21.4.4 Exercises
22 Web Scraping
22.1 HTML Structure and Its Representation of Data Sets
22.1.1 HTML Tables
22.1.2 HTML Lists
22.1.3 Reading Questions
22.2 Web Scraping Examples
22.2.1 Formulating Requests for HTML
22.2.2 Simple Table
22.2.3 Wikipedia Table
22.2.3.1 Goal
Goal
22.2.3.2 Discovery
Discovery
22.2.3.3 Data Extraction
Data Extraction
22.2.4 POST to Submit a Form
22.2.4.1 Goal
Goal
22.2.4.2 Discovery
Discovery
22.2.4.3 Request and Data Extraction
Request and Data Extraction
22.2.5 Reading Questions
22.2.6 Exercises
23 RESTful Application Programming Interfaces
23.1 Motivation and Background
23.1.1 General API Characteristics
General API Characteristics
23.1.2 Principles of REpresentational State Transfer (REST)
Principles of REpresentational State Transfer (REST)
23.1.3 Reading Questions
23.2 HTTP for REST API Requests
23.2.1 Endpoints
23.2.1.1 Root Endpoint
Root Endpoint
23.2.1.2 Non-Root Endpoint
Non-Root Endpoint
23.2.2 Path Parameters
23.2.3 Query Parameters
23.2.3.1 Search for Movies
Search for Movies
23.2.4 Header Parameters
23.2.5 POST and POST Body
23.2.6 Reading Questions
23.2.7 Exercises
23.3 Case Study
23.3.1 Phase 1: Build a Table of Popular Movies
23.3.1.1 Design a Function to Issue Request
Design a Function to Issue Request
23.3.1.2 Understand Results
Understand Results
23.3.1.3 Design Movie Table
Design Movie Table
23.3.1.4 Handle Multiple Pages
Handle Multiple Pages
23.3.2 Phase 2: Build Table of Top Cast Given Movie IDs
23.3.2.1 Understand Movie Credits API
Understand Movie Credits API
23.3.2.2 Goal: Design Cast Table
Goal: Design Cast Table
23.3.3 Summary Comments
23.3.4 Reading Questions
23.3.5 Exercises
24 Authentication and Authorization
24.1 Background
24.1.1 Principals
24.1.2 Authentication and Authorization Concepts
24.1.3 Impersonation
24.1.4 Encryption, Keys, and Signatures
24.1.5 Reading Questions
24.2 Authentication and Privacy
24.2.1 HTTPS
24.2.2 HTTP Authentication
24.2.2.1 Basic Authentication
Basic Authentication
24.2.3 Authentication Considerations
24.2.4 Reading Questions
24.2.5 Exercises
24.3 Authorization
24.3.1 OAuth2 Background
24.3.2 Delegated Authority: Authorization Code Grant Flow
24.3.2.1 Pre-Stage: Application Registration with Provider
24.3.2.2 Stage 1: Client Obtains Code with Cooperating Resource Owner
24.3.2.3 Stage 2: Client Exchanges Code for Bearer Token
24.3.2.4 Stage 3: Client Acquires Data Using Token
24.3.2.5 Stage 4: Client Exchanges Refresh Token for New Token
24.3.3 OAuth Dance Walkthrough
24.3.3.1 Build User Auth URL
24.3.3.2 Delegation by Resource Owner
24.3.3.3 Exchange Code for Token by Client
24.3.3.4 Data Requests
24.3.4 Reading Questions
24.3.5 Exercises
A Custom Software
A.1 The util Module
A.1.1 buildURL
Signature
Description
Parameters
Return
A.1.2 random_string
Signature
Description
Parameters
Return
A.1.3 getLocalXML
Signature
Description
Parameters
Return
A.1.4 read_creds
Signature
Description
Parameters
Return
A.1.5 update_creds
Signature
Description
Parameters
Return
A.1.6 print_text
Signature
Description
Parameters
Return
A.1.7 print_data
Signature
Description
Parameters
Return
A.1.8 print_xml
Signature
Description
Parameters
Return
A.1.9 print_headers
Signature
Description
Parameters
Return
A.2 The mysocket Module
A.2.1 makeConnection
Signature
Description
Parameters
Return
A.2.2 sendString
Signature
Description
Parameters
Return
A.2.3 receiveTillClose
Signature
Description
Parameters
Return
A.2.4 sendBytes
Signature
Description
Parameters
Return
A.2.5 receiveTillSentinel
Signature
Description
Parameters
Return
A.2.6 receiveBySize
Signature
Description
Parameters
Return
A.2.7 sendCRLF
Signature
Description
Parameters
Return
A.2.8 sendCRLFLines
Signature
Description
Parameters
Return
References
Index