Abstract
An important part of the data lifecycle is preparing the data for analysis. This includes exploratory data analysis to find problems, data cleaning to solve problems related to 'dirty' data, and data transformation to achieve the format required by data analytics tools. Much data resides in databases, and it is beneficial for the data scientist to be able to carry out as much of this work as possible inside the database. This can be achieved using SQL. This article overviews some of the basic approaches to data exploration, cleaning and wrangling using SQL, including methods to deal with missing data, outliers, and bad values.
TopBackground
Data must be prepared for analysis. Most DM) and ML algorithms make assumptions about the format and other properties of data (Dasu & Johnson, 2003). However, 'data in the wild' rarely conforms to such expectations. Discovering any problems or issues that could render data not ready for analysis and solving them is the goal of the data preparation process.
The process starts with Exploratory Data Analysis (EDA), where data is examined with simple, descriptive statistics in order to determine whether data problems exist. Most datasets often come with dirty data (Wickham, 2014); typical problems found at this stage include missing data, outliers, formatting problems and structure problems. Missing data refers to values that are absent. This can be a serious issue when the number of missing values is high (as a percent of all values) or when the values are not missing at random (that is, certain values are more likely than others to be missing), since this may introduce bias in the analysis. An outlier is a data point that is quite different from other data points and therefore could potentially be the result of an error in data gathering or storage. Some DM and ML algorithms can be greatly affected by the presence of outliers; for instance, linear regression is particularly sensitive to this issue (Dasu & Johnson, 2003). Formatting problems refer to data values that are not encoded in the expected shape or arrangement. This includes issues like numbers that are not written out as numbers (“42”), dates that cannot be recognized as dates by the system (“Feb ten 2020”), and similar. This type of error is not infrequent, since computers expect values to be represented in certain ways, and any deviations may result in values being ignored or misinterpreted. Finally, structure problems arise because data is not structured in the way required by the data analysis algorithm to be used. In a typical dataset, there is a set of attributes that describe the data, and data elements are represented by a tuple or row of values, one for each attribute. However, some datasets do not come with this 'table-like' structure, while most DM and ML algorithms assume it (Wickham, 2014). All these are issues that must be detected and solved before analysis can proceed (Berthold et al., 2010).
Key Terms in this Chapter
Missing Data: data values that should be present in a dataset but are not. Most datasets are based on a tabular model, with a collection of homogeneous records (that is, records that contain values for the same, fixed collection of attributes). When a record is incomplete (it does not contain values for all attributes), we say that there is missing data. This situation creates a problem for many analyses and therefore must be addressed during Data Cleaning.
SQL: Structured Query Language is an industry standard that defines the language used to manage data in a Relational Database System (RDBMS). It includes commands to create, store, and access data. It is a declarative language, with the implementation of the commands left to each system. SQL has become ubiquitous to such an extent that Relational databases are sometimes called SQL databases, and systems that do not follow the Relational model are called NoSQL.
Data Cleaning: Set of activities carried out to take 'dirty' data (data with problems like missing data, outliers, etc.) and transform it into 'clean' or 'tidy' data. Its focus is on solving any issues the data may have so as to get the data ready for further analysis.
Exploratory Data Analysis (EDA): Lightweight analysis of data that uses descriptive statistics, graphics and other tools in order to describe the data and discover whether any problems are present. Data analysts use EDA as a first approach to a dataset in order to gain an understanding of what it means, what kind of values it contains, and what issues the data may have.
Data Wrangling: Another term for Data Cleaning, sometimes it is used in a wider sense to include activities that are not properly Data Cleaning, like getting the data in the format required by some analysis tool or algorithm, changing the structure of the data (by pivoting or getting the data in a certain pre-defined structure, like a graph or a matrix), creating new attributes from existing ones, etc.
Outlier: An outlier is a data value that is very different, in some sense, from most other data values in the same attribute. As such, it lies outside what is considered 'typical' or 'normal' for that attribute. The definition of outlier is context-dependent and often not completely precise. The presence of an outlier may indicate an extreme and exceptional data value faithfully recorded or a regular data value that has not been recorded properly. It may be extremely hard to separate these two cases without domain knowledge.
Data Preparation: Set of activities carried out to prepare data for analysis. Most Data Mining and Machine Learning expect data to be 'clean' (to be well formatted, without missing values, outliers, duplicates or other problems) and to be in a certain format (usually some kind of table). Moreover, some attributes may need special treatment (categorical attributes may need to be standardized or even transformed into numerical; numerical attributes may need to be scaled). Data Preparation is a fundamental activity within the Data Life-Cycle.