Data Science: An Introduction/Thinking Like a Data Engineer

Data Science: An Introduction

Chapter 06: Thinking Like a Data Engineer



Note to Contributors (remove this section when the chapter is complete)
First, please register yourself with Wikibooks (and list yourself below), so that we know who our co-contributors are. Also, please abide by the Wikibooks Editing Guidelines, Manual of Style, and Policies and Guidelines. Thank you.

Secondly, we only need basic, clear, straightforward information in each chapter. We are not trying to be exhaustive or complete—the value of this book is in the simple synthesis across subjects. There are other venues in which to wax eloquent on the deepness and complexities of a particular subject. Please place yourself in a "beginner's mind" as you make contributions. Please also scope each chapter so that it can be taught in a one-hour class period. If the chapter requires more than an hour to teach, it is probably too detailed.
 * To the extent possible, please use terms and concepts in the way in which they are defined in the Wikipedia and Wiktionary. This way students can refer to the corresponding Wikipedia / Wiktionary page to get a deeper understanding of the concept.

Thirdly, this is a cross-disciplinary book. We want to help people apply data science to all fields. Therefore, we need a wide variety of simple examples and simple exercises.

Fourthly, please adhere to the simple structure of each chapter: Summary of Main Points, Discussion, More Reading, Exercises, and References. We want the More Reading section to link to on-line resources. The References section may contain off-line resources. To start a new page, you should use the wiki markup from this prototype page.

Fifthly, as with any Wikibook please feel free to make corrections, expand explanations, and make additions where necessary, even if it is not "your" chapter. Use the discussion page to explain changes that might be controversial.

Sixthly, some syntax rules:


 * Please bold key terms and phrases the student should learn.
 * Put the name of functions and code snippets using the 'code' tags:
 * Use in-line links  to the Wikipedia, Wiktionary, WikiCommons, Wikibooks, and other Wikimedia Foundation properties.
 * Use references to "external" sources—both on-line and off-line.
 * Use the citations templates to make citations : Template:Cite book, Template:Cite web, Template:Cite journal
 * If you want to add an image or graph, you should load it into the Commons rather than uploading into Wikibooks.
 * If appropriate, add the tag ) when you upload the graph.
 * If using a different package than R standard packages, put the name of the package in bold in parenthesis after each function : (MCMCpack)
 * You can use the third chapter Definitions of Data as an example of how to craft a chapter.

Finally, thank you so much for volunteering to be part of our our team!

Chapter Summary
When a data scientist thinks like a data engineer, they think in terms of tables. The tasks are to define the rows, columns, and cells of the tables; to associated tables with one another; and create systems to ingest, store, and retrieve tables.

(in the following discussion we need more on thinking in tables (row and columns) and how several tables are related to each other (schemas). Maybe throw in Normal Forms and indexing, just for fun. talk about different data management schemes, flat CSV files, RDBMS, no-SQL, etc.)

Discussion
Data engineering is the data part of data science. According to Wikipedia,data engineering involves acquiring, ingesting, transforming, storing, and retrieving data. Data engineering is closely related to Data Collection, Information Engineering, Knowledge Engineering, Information Management and Knowledge Management.

Data engineering starts with an understanding of the general nature of the problems to be solved. A data acquisition and management plan must be formulated which specifies where the data are coming from (RSS feeds, sensor network, pre-existing data repository), the format of the incoming data (text, numbers, images, video), and how the data will be stored and retrieved (file system, database management system). Raw data is "dirty." There will be records in the raw data that do not conform to data definitions that have been agreed upon. For example, in one hospital data set, several young boys aged 7 to 11 gave birth to babies. Clearly, there are mistakes in this data. Part of the data acquisition and management plan is deciding what to do with dirty data (leave it, erase it, infer corrections).

Most of the time, raw data is not in the format that the analytical tools are expecting to see. Indeed, each tool will want to see data in its own particular way. Therefore, one task of data engineering is to transform the data so that it can be consumed by the analytical tools the data science team will use. For example, a team might receive egg laying data with each observation in its own row like the following:

But what the analysis the team wants to do requires all of the observations about each chicken to be in one row only as follows:

Good data engineering requires both the ability to manipulate data and an understanding of the analytic purposes to which the data are going to be used.

In the egg laying example above the first table is in a Normalised form that lends enables further analysis, the second table is formatted to present data to the user. Often the formatting makes implicit assumptions about the questions being asked of the data – such as “what are the trends in egg laying by chicken over time?”. Other questions such as “on how many occasions did a chicken lay no eggs?” are easier to answer with the data in the normalised form.

Often the sources for an analysis are outputs from another system – so for example an egg-laying database may well internally store data in the 3 column format but export a report in the “many columns” format. One of the tasks of a data engineer is to transform captured data, which may well involve re-normalising data from output reports.

Wikipedia defines database normalization as the process of organizing the fields and tables of a relational database to minimize redundancy and dependency – usually by dividing larger tables into smaller (and less redundant) tables and defining relationships between them. The main objectives of normalisation are to:
 * avoid update and deletion anomalies
 * minimize redesign when extending the database structure
 * support general-purpose querying, including future queries that are not anticipated at design time

Suppose the egg-laying data is extended to store the age and colour of each chicken. This could be represented in a table like this:

This table now contains redundant information, since we are storing the age and colour of each chicken 3 times. This becomes inefficient if we are storing data for hundreds of days for each chicken. Furthermore, if Chicken B turns 2 years old we would have to synchronise the changes to records 4, 5 and 6 in order to update the age data. The normalised solution would be to have a separate "chicken" table for chicken-related facts that is linked to the "egg laying" table by a unique identifier or key.

Wikipedia defines a primary key as a unique identifier for a record in a table in a relational database relational_database. Some data sets have naturally unique keys (such as employee_id for an employee table) in other cases a unique key will need to be system generated either as an internal 'one-up' counter or by combining several attributes to create one (such as Chicken_Day in the example above). Other tables can cross-reference to a table by using its primary key. For example, a 'project' table could have a column with employee_id for each team member associated with the project. This 'cross referencing' column is known as a foreign key.

Entity relationship diagrams (also known as logical data models) are used to design relational databases and can be a good way of understanding the structures in a data set. The 3 building blocks of an Entity Relationship model are entities, attributes and relationships. An entity is a discrete and recognisable 'thing', either a physical object such as a car (or a chicken), or concept such as a bank transaction or a phone call. Each entity can be physically represented as a table, where each column of the table is an attribute of the entity (such as employee_id, forename, surname, date of joining). A relationship is a verb that links two or more entities. For example, a chicken 'lays' eggs or an employee 'belongs to' a department. Importantly, relationships also have a cardinality that can be 'one to one', 'many to one', 'one to many' or 'many to many'. For example, a chicken can lay many eggs but each egg is laid by only one chicken so the 'lays' relationship is one to many. Many to many relationships are often a sign that a design needs to be further elaborated. For example, the 'teaches' relationship between teachers and students at a university would be many to many and would require the introduction of entities like class and date to fully understand the relationship. An example entity relationship diagram is shown below:

More advanced data engineering also requires knowledge of computer programming and the Structured Query Language, as well as relational and no-SQL database management systems. For the purposes of this book, we will use the R programming language for simple data engineering tasks.

Assignment/Exercise
This assignment is about reading data sets into R data frames. Assemble into groups of 3 or 4 students. Every single student must do every part of this exercise. The purpose of grouping is to help each other understand what is going on. Some of these assignments require some trial and error. Different students will do different trials and errors, thus all will learn from each other's trials and errors.

Part 1 of 3: Within R create 4 variables, each with 12 of observations.

Part 2 of 3. Load an example data set into a data frame.

Part 3 of 3 - Import an external data set.


 * Find Fisher's Iris Data Set in the Wikipedia.
 * Copy the data table and paste it into Microsoft Excel, Apple Numbers, or Google Docs Spreadsheet
 * Save the dataset in Comma Separated Value (CSV) format on your desktop, with a filename of "iris.csv"
 * Read the dataset into R
 * Inspect the data, make sure it is all there, then look at the data using the functions

Copyright Notice


You are free: Under the following conditions:
 * to Share — to copy, distribute, display, and perform the work (pages from this wiki)
 * to Remix — to adapt or make derivative works
 * Attribution — You must attribute this work to Wikibooks. You may not suggest that Wikibooks, in any way, endorses you or your use of this work.
 * Share Alike — If you alter, transform, or build upon this work, you may distribute the resulting work only under the same or similar license to this one.
 * Waiver — Any of the above conditions can be waived if you get permission from the copyright holder.
 * Public Domain — Where the work or any of its elements is in the public domain under applicable law, that status is in no way affected by the license.
 * Other Rights — In no way are any of the following rights affected by the license:
 * Your fair dealing or fair use rights, or other applicable copyright exceptions and limitations;
 * The author's moral rights;
 * Rights other persons may have either in the work itself or in how the work is used, such as publicity or privacy rights.


 * Notice — For any reuse or distribution, you must make clear to others the license terms of this work.The best way to do this is with a link to the following web page.
 * http://creativecommons.org/licenses/by-nc-sa/3.0/