MODULE-3

PREPARING DATA TABLES

INTRODUCTION:
Data preparation is an essential phase of any data analysis or data mining project. It involves collecting data
from multiple sources, integrating it into a unified tabular format, characterizing the variables, and
performing extensive cleaning to correct errors, remove redundancies, and handle missing values.
Additional steps include generating new variables, restructuring the data through grouping or splitting,
standardizing scales, and converting text to numeric formats. It is equally important to document every step
and assumption so that the process can be repeated and validated. Properly prepared data leads to accurate
analysis and confident decision-making.

IMPORTANCE OF DATA PREPARATION:

Preparing the data properly is critical because:

1. Affects quality of decisions: If the input data is wrong, incomplete, or inconsistent, then analysis
results will be misleading. Well-prepared data increases confidence in the decisions made from the
analysis.
2. Ensures reliability and validity: Clean and well-structured data makes the analysis more reliable. It
helps to avoid biased or incorrect conclusions.
3. Supports repeatability and future work: If the preparation steps are recorded, the same
methodology can be applied to similar data sets in future projects.

STEPS IN DATA PREPARATION:

1. Data Collection and Integration: Data may come from multiple sources, e.g.: Different databases,
Spreadsheets Text files, External systems or surveys. These different sources must be merged into a
single table (or related set of tables). This process is called data integration.
2. Converting Data into Tabular Form and Characterization: Once the data has been successfully
merged, it should be organized into a clear and well-structured tabular format, where information is
systematically arranged in rows and columns. In this structure, each row represents a single
observation or record, while each column corresponds to a specific variable or feature describing
those observations. Presenting the data in this standardized tabular form enhances clarity, supports
efficient analysis, and enables the use of statistical and data mining tools that rely on structured
inputs. Following this, the dataset must be fully characterized to assess its structure, properties, and
overall quality. This involves determining the types of variables present—such as numerical,
categorical, or ordinal—along with examining their ranges, scales, and units of measurement. It also
includes identifying any missing values, inconsistencies, or potential outliers that could affect
subsequent analysis. A thorough characterization provides a deeper understanding of the dataset and
ensures that appropriate cleaning and transformation techniques can be applied effectively.
3. Data Cleaning: Data cleaning is the process of improving the quality of a dataset by identifying,
correcting, or removing inaccurate, inconsistent, incomplete, or irrelevant information. It ensures that
the data used for analysis is reliable, consistent, and suitable for generating valid conclusions.

 Resolving Ambiguities and Errors: Data cleaning begins with correcting values that are
unclear or incorrect. This includes fixing spelling mistakes, standardizing different formats
of the same value (for example, “M”, “Male”, and “male” are all converted to “Male”), and
correcting impossible or inconsistent values, such as negative ages or invalid dates. These
corrections ensure that the dataset is consistent and logically accurate.
  Removing Redundant Data: Redundant or duplicate information affects both storage and
analysis. Duplicate records are removed to prevent the same entry from influencing results
multiple times. Repeated information that adds no new value is also eliminated. This step
ensures that each entry in the dataset is meaningful and contributes unique information to the
analysis.
 Removing Problematic Data: This step focuses on eliminating observations that are clearly
incorrect or unusable. Examples include corrupted records, incomplete rows, or entries that
do not meet the basic quality requirements. Missing values are handled appropriately using
methods such as deletion, imputation, or marking them for special treatment. This improves
the dataset’s completeness and reliability.
 Eliminating Irrelevant Columns: Variables that are unrelated to the analysis objectives or
add unnecessary noise are removed during data cleaning. Columns that do not contribute any
useful information are dropped to make the dataset simpler, more efficient, and easier to
analyze. This step also helps improve model performance by reducing unnecessary
complexity.

4. Creating New Variables (Derived Columns): Creating new variables, also known as derived
columns, involves generating additional fields from the existing data to provide more meaningful and
useful information for analysis. This process includes calculating values such as the total amount from
price and quantity, computing age from date of birth, or creating ratios and percentages that highlight
important relationships. These new variables make patterns easier to detect by converting raw
information into clearer, more interpretable forms. They also improve the performance of data
analysis and data mining models, as many algorithms work better when provided with well-structured
and informative features. Overall, creating derived variables strengthens the dataset, enhances
analytical accuracy, and supports better decision-making.

Example:
Suppose a dataset contains two columns: Price and Quantity. By creating a new derived column such
as Total = Price × Quantity, the analyst can easily evaluate sales performance. Similarly, if a dataset
includes Date of Birth, a new column Age can be generated, which is far more useful for grouping,
comparing, or building predictive models.

5. Dividing the Table into Subsets: After the data has been properly cleaned and transformed, the
table may be divided into subsets to make the analysis more efficient and focused. Working with
smaller, well-defined subsets helps simplify complex datasets and allows analysts to answer specific
questions more easily. For example, the data may be separated based on region, time period, or
customer type to study targeted patterns or behaviors. In data mining and predictive modeling,
dividing the dataset is also essential for creating training, validation, and testing sets, which
ensures that models are built, tuned, and evaluated fairly. Overall, dividing the table into subsets
improves clarity, supports specialized analysis, and enhances model accuracy.

Importance of Recording the Preparation Steps:

Documenting every step of data preparation is essential because it ensures clarity, transparency, and
repeatability throughout the analytical process. A well-maintained record clearly states what actions were
performed, how they were executed, and why each step was necessary, providing a structured explanation of
the entire workflow. This documentation greatly supports future reuse, as the same sequence of preparation
steps can be applied to new datasets of similar nature or adapted for future projects with comparable
requirements. It also strengthens the processes of validation and troubleshooting, since analysts need to
revisit the assumptions made, the transformations applied, and any filters or cleaning operations used. When
results appear inaccurate or unexpected, a detailed record enables the analyst to trace back through the
documented steps, identify potential issues, and make appropriate corrections. Overall, proper
documentation ensures consistency, reliability, and accountability in data-driven work.
CLEANING THE DATA:

Data cleaning is the process of improving the quality of a dataset by identifying, correcting, or removing
inaccurate, inconsistent, incomplete, or irrelevant information. It ensures that the data used for analysis is
reliable, consistent, and suitable for generating valid conclusions.

Data Cleaning for Different Types of Variables

1. Nominal and Ordinal Variables: Variables measured on a nominal or ordinal scale have a fixed number
of possible categories. Therefore, it is important to inspect all unique values to identify errors, duplicates,
and inconsistencies.

 Each category must correspond to a single, standardized term.

 Variations in spelling, capitalization, and abbreviations should be harmonized.

Example:

A variable Company may contain several representations of the same organization:

 “General Electric Company”

 “General Elec. Co.”
 “GE”
 “Gen. Electric Company”
 “General electric company”
 “G.E. Company”

Since all these refer to the same company, they must be consolidated into one standard label, such as
“General Electric Company.”

Subject matter expertise is often required to determine correct groupings, especially when dealing with
divisions or subsidiaries. For example, a division of General Electric may also need to be classified under
“General Electric Company” for the purpose of the project.

2. Numeric Variables: Numeric variables often require special attention because non-numeric or
improperly formatted values may appear.

 Presence of non-numeric terms, such as “above 50” or “out of range.”

 These cannot be processed in numerical analysis and must be corrected using domain expertise.
 Depending on context, such values should be either converted to appropriate numeric values or the
records should be removed.

Handling Missing Numeric Values:

 Missing values may have a specific meaning depending on the data collection process.
 When appropriate, missing values can be replaced (imputed) based on contextual knowledge or left
as missing with proper coding.

3. Interval and Ratio Scale Variables: Variables measured on interval or ratio scales can take any value
within a continuous range, making them more challenging to clean.

Outliers may occur for several reasons, and understanding their origin is essential before deciding how to
handle them. In some cases, an outlier may result from a measurement error or from using a device with
different calibration settings, making the value inaccurate. However, outliers can also represent legitimate
and meaningful observations that should not be removed, particularly when they reflect real extreme
behavior in the data. Visual tools such as histograms and box plots are highly effective for detecting such
unusual values. For example, a histogram of the variable Height, as shown in Figure 3.1, reveals one data
point that is nearly eight times larger than the average height, clearly indicating an outlier that requires
further investigation.

FIGURE 3.1: Histogram showing an outlier.

A variable may sometimes be recorded using different units of measurement, which can lead to inconsistencies in the
dataset. For example, the variable Weight might contain values measured in both pounds and kilograms, or the
variable Price may include entries recorded in different currencies. Such differences must be standardized to a single,
consistent scale to ensure meaningful comparison and accurate analysis. Additionally, when data has been collected
over long periods, certain time-related changes such as inflation may need to be accounted for. For instance, the
variable Cost of Production collected over many years may reflect price increases due to inflation rather than actual
production efficiency. Furthermore, when merging data from multiple sources, duplicate records are more likely to
occur, and these duplicates must be identified and removed to avoid distorting the analysis. Proper standardization,
adjustment, and deduplication help maintain the accuracy and integrity of the dataset.

REMOVING OBSERVATIONS AND VARIABLES:

A crucial step in data preparation is the initial screening of all variables to determine their relevance and
analytical value. After categorizing the variables based on their type and characteristics, certain variables
may be identified as unsuitable for further analysis. For instance, variables that remain constant across all
observations offer no discriminatory power and should be removed. Likewise, variables containing an
excessively high proportion of missing values contribute little to the analytical process and may weaken the
reliability of the results. In some cases, entire observations may need to be removed when essential variables
contain missing or unusable data. This careful elimination ensures that the dataset retains only meaningful,
well-structured information suitable for subsequent analysis.

In many academic and professional disciplines, the primary focus of data analysis goes beyond simple
tabular datasets of rows and columns. Fields such as life sciences, chemistry, computer science, and
multimedia often work with complex entities such as genes, proteins, biological pathways, chemical
structures, documents, web logs, device outputs, audio files, and video recordings. For these types of data, a
foundational pre-processing step involves the computational generation of relevant attributes that capture
essential characteristics of the objects under study.

For example, when working with chemical datasets, analysts may generate attributes such as molecular
weight, atomic composition, presence or absence of specific functional groups, or structural features. These
derived characteristics convert complex scientific or multimedia data into structured variables that can be
used in statistical and data mining models. This attribute generation step is essential because it transforms
raw, domain-specific information into a form that supports effective analysis, accurate modeling, and
meaningful interpretation.

GENERATING CONSISTENT SCALES ACROSS VARIABLES:

In many situations, data analysis and data mining algorithms cannot process raw data effectively due to
differences in scale, magnitude, or distribution across variables. To address these issues, mathematical
transformations are applied to convert the data into a form that is easier for analytical methods to interpret
and compare.

Normalization is a commonly used transformation technique that converts numerical values into a specified
range using a mathematical function. This step is essential for ensuring that variables with large ranges do
not dominate or exert disproportionate influence on analytical models.

For instance, in customer credit card data, the variable Credit Limit may have values ranging from $500 to
$100,000, while Customer Age may range only from 18 to 100. Without normalization, the Credit Limit
variable would overshadow Age simply because of its larger scale, potentially distorting the analysis.

Normalization ensures that all variables contribute fairly by placing them on comparable scales.

One widely used normalization method is min–max transformation, which maps the values of a variable to a
new scale typically between 0 and 1. This method adjusts each value proportionally within the range of the
original data.

This transformation retains the relative distances between values while rescaling them to a consistent
interval, making the data suitable for clustering, classification, and many machine-learning algorithms.

The formula for min–max normalization is:

where x′i is the new normalized value, xi is the original variable's value, Original Min is the minimum
possible value in the original variable, Original Max is the maximum original possible value, New Min
is the minimum value for the normalized range,
and New Max is the maximum value for the normalized range. Since the minimum and maximum
values for the original variable are needed, if the original data does not contain the full range, either an
estimate of the range is needed or the formula should be restricted to the range specified for future use.
The z-score transformation normalizes the values around the mean of the set, with differences from the
mean being recorded as standardized units, based on the frequency distribution of the variable.

The decimal scaling transformation moves the decimal point to ensure the range is between 1 and −1.
The following formula is used:
where n is the number of digits of the maximum absolute value. For example, if the largest number is
9948 then n would be 4. 9948 would normalize to 9948/104 or 9948/10,000 or 0.9948.

The normalization process is illustrated using the data in

TABLE 3.1: As an example, to calculate the normalized values using the min–max equation for the
variable Weight, first the minimum and maximum values should be identified: OriginalMin = 1613
and OriginalMax = 5140. The new normalized values will be between 0 and 1,
hence NewMin = 0 and NewMax = 1. To calculate the new min–max normalized value for the Ford
maverick using the formula:

TABLE: Normalization of the Variable Weight Using the Min–Max, z-score, and Decimal Scaling
Transformations

Min Decim
z-score
– al
Ma Scaling
x

Car Name Weight (Weight) (Weight) (Weight)

Datsun 1200 1613 0 −1.59 0.161

Car Name Weight (Weight) (Weight) (Weight)

Honda Civic Cvcc 1800 0.053 −1.37 0.18

Volkswagen Rabbit 1825 0.0601 −1.34 0.182

Renault 5 gtl 1825 0.0601 −1.34 0.182

Volkswagen Super 1950 0.0955 −1.19 0.195

Beetle

Mazda glc 4 1985 0.105 −1.15 0.198

 Ford Pinto 2046 0.123 −1.08 0.205

Plymouth Horizon 2200 0.166 −0.898 0.22

Toyota Corolla 2265 0.185 −0.822 0.226

AMC Spirit dl 2670 0.3 −0.345 0.267

Ford Maverick 3158 0.438 0.229 0.316

Plymouth Volare 3940 0.66 1.15 0.394

Premier v8

Car Name Weight (Weight) (Weight) (Weight)

Dodge d200 4382 0.785 1.67 0.438

Pontiac Safari (sw) 5140 1 2.56 0.514

The above table shows some of the calculated normalized values for the min–max normalization, the z-score
normalization, and the decimal scaling normalization.

NEW FREQUENCY DISTRIBUTION:

A variable may not naturally follow a normal frequency distribution, yet some data analysis techniques
require the data to approximate a normal distribution. When this occurs, transformations are applied to
reshape the data so that it better fits the required form. As discussed in the previous chapter, normal
distributions can be visualized and described using various methods. To adjust a variable that does not
conform to normality, mathematical transformations such as taking the log, applying an exponential
function, or using a Box–Cox transformation may be necessary. The Box–Cox transformation is particularly
useful because it provides a systematic way to modify the distribution through a specific formula that shifts
the data toward normality. The formula for a Box–Cox transformation is

where λ is a value greater than 1.

FIGURE 3.2: shows how the distribution of the original variable IC50 (figure on the left) is
transformed to a closer approximation of the normal distribution after the log transformation has been
applied (figure on the right).
FIGURE 3.2: Log transformation converting a variable (IC50) to adjust the frequency distribution.

CONVERTING TEXT TO NUMBERS:

When nominal or ordinal variables are stored as text, many numerical analysis methods cannot process them
directly, making it necessary to convert these categories into numerical form. One common approach is to
assign numerical codes to each category; for example, the values “low,” “medium,” and “high” can be
replaced with 0, 1, and 2 respectively. Another widely used method is the creation of dummy variables,
where each category is converted into a separate column containing 1 to indicate the presence of that
category and 0 to indicate its absence. For instance, in Table 3.2, the variable Color is split into five columns
—one for each color category. However, although the example shows all five columns, in practice only four
dummy variables are required to represent the five colors. Using the dummy variables Black, Blue, Red, and
Green, the encoding would be: Black → 1,0,0,0; Blue → 0,1,0,0; Red → 0,0,1,0; Green → 0,0,0,1; and
White → 0,0,0,0. This structured conversion allows text-based categories to be used efficiently and
accurately in numerical analysis.
TABLE 3.2: Generating a Series of Dummy Variables from the Single Color Variable

Color = Color = Color = Color = Color =

Product ID Color Black Blue Red Green White

89893-22 Black 1 0 0 0 0

849082-35 Blue 0 1 0 0 0

27037-84 Red 0 0 1 0 0

2067-09 Green 0 0 0 1 0

44712-61 White 0 0 0 0 1
 Product ID Color Black Blue Red Green White

98382-34 Blue 0 1 0 0 0

72097-52 Green 0 0 0 1 0

CONVERTING CONTINUOUS DATA TO CATEGORIES:

Converting continuous data into discrete categories may seem to reduce the amount of information
contained in a variable, this transformation is often necessary and appropriate in several situations. In some
cases, even when a variable is measured on an interval or ratio scale, the way the data was collected may not
justify such precision. If the measurement accuracy is limited, the variable becomes a suitable candidate for
conversion into categories that better reflect the true level of variation in the data. Another important reason
for discretizing continuous variables is that certain analytical or data mining techniques can only operate on
categorical data. Converting a numeric variable into discrete values allows these methods to process it
effectively.
For example, a continuous variable such as credit score can be grouped into four meaningful categories
poor, average, good, and excellent. Similarly, a variable like Weight, ranging from 0 to 350 lb, may be
divided into five categories: less than 100 lb, 100–150 lb, 150–200 lb, 200–250 lb, and above 250 lb. Once
such categories are created, every value must be assigned to one of the defined groups and given an
appropriate representative value, such as the mean of that category. The frequency distribution of the
variable is often a valuable tool for determining logical and meaningful boundaries for these categories.
This process can also be applied to nominal variables, particularly when a variable contains a large number
of distinct categories, making meaningful analysis difficult. When each category occurs only a few times,
summarizing the data based on these values may produce unreliable or insignificant results. To address this,
a new generalized variable can be created by mapping the numerous specific categories into broader groups.
For example, in a customer transaction dataset, the variable Company may list individual customer
companies, each with only a small number of observations. By mapping these specific companies into an
aggregated variable such as Industries, the analysis becomes more meaningful. This mapping must be
defined using a clear concept mapping that specifies which company belongs to which industry. Once the
values are grouped into these broader categories, summarizing the dataset using the Industries variable can
reveal more useful and interpretable trends.

COMBINING VARIABLES:

Sometimes the variable needed for analysis does not exist directly in the dataset but can be created from
existing variables through mathematical operations. Techniques such as computing averages, sums, or
differences can be applied to one or more variables to generate a new, meaningful variable. For example, a
project examining a car’s fuel efficiency may rely on measurements taken during various journeys,
including the fuel level at the start of the trip (Fuel Start), the fuel level at the end of the trip (Fuel End), and
the distance traveled (Distance). To evaluate fuel efficiency, an additional variable can be derived using
these values, typically through a formula that relates fuel consumed to the distance covered. This derived
column provides insights that are not directly available from the raw data but are essential for the analysis.
GENERATING GROUPS:

Larger data sets generally require more computational time to analyze, and creating subsets can greatly
improve the speed and efficiency of the analysis. One common approach is to draw a random subset, which
works well when the original dataset closely represents the target population. Subsetting is also important
when data originally collected for operational purposes is later used to answer a different business or
research question. In such cases, a more diverse subset may be needed to better reflect the new target
population. For example, if a car safety organization has conducted safety tests only on specific vehicle
types requested by the government, the accumulated dataset will not represent all cars. If this historical data
is then used to study the general safety of all vehicles, it becomes necessary to select a subset of studies that
more accurately represents the broader population
A third reason for creating subsets arises when building predictive models, where it is essential to keep the
models as simple and manageable as possible. Dividing the dataset into smaller, meaningful subsets based
on domain knowledge can make it easier to develop multiple simpler models rather than one overly complex
model. For instance, a project analyzing factors that influence real estate prices may start with a nationwide
dataset. However, expertise in the real estate market indicates that the factors affecting house prices depend
heavily on geographic location. The variables that influence prices in coastal regions differ from those
relevant in mountainous areas. In such cases, dividing the dataset by location and modeling each region
separately can produce more accurate and interpretable results. When performing this type of subsetting, it is
crucial to document the criteria used, as these same criteria must be applied when assigning new data for
prediction. Additionally, if multiple models produce predictions for the same observation, a method for
combining these predictions must be established to ensure consistent and reliable outputs.

PREPARING UNSTRUCTURED DATA:

In many disciplines, the focus of a data analysis or data mining project is not a simple data table of
observations and variables. For example, in the life sciences, the focus of the analysis is genes, proteins,
biological pathways, and chemical structures. In other disciplines, the focus of the analysis could be
documents, web logs, device readouts, audio or video information, and so on. In the analysis of these types
of data, a preliminary step is often the computational generation of different attributes relevant to the
problem. For example, when analyzing a data set of chemicals, an initial step is to generate variables based
on the composition of the chemical such as its molecular weight or the presence or absence of molecular
components.