The data are transformed in ways that are ideal for mining the data. The data transformation involves steps that are:
1. Smoothing:
It is a process that is used to remove noise from the dataset using some algorithms It allows for highlighting important features present in the dataset. It helps in predicting the patterns. When collecting data, it can be manipulated to eliminate or reduce any variance or any other noise form.
The concept behind data smoothing is that it will be able to identify simple changes to help predict different trends and patterns. This serves as a help to analysts or traders who need to look at a lot of data which can often be difficult to digest for finding patterns that they wouldn’t see otherwise.
2. Aggregation:
Data collection or aggregation is the method of storing and presenting data in a summary format. The data may be obtained from multiple data sources to integrate these data sources into a data analysis description. This is a crucial step since the accuracy of data analysis insights is highly dependent on the quantity and quality of the data used. Gathering accurate data of high quality and a large enough quantity is necessary to produce relevant results.
The collection of data is useful for everything from decisions concerning financing or business strategy of the product, pricing, operations, and marketing strategies.
For example, Sales, data may be aggregated to compute monthly& annual total amounts.
3. Discretization:
It is a process of transforming continuous data into set of small intervals. Most Data Mining activities in the real world require continuous attributes. Yet many of the existing data mining frameworks are unable to handle these attributes.
Also, even if a data mining task can manage a continuous attribute, it can significantly improve its efficiency by replacing a constant quality attribute with its discrete values.
For example, (1-10, 11-20) (age:- young, middle age, senior).
4. Attribute Construction:
Where new attributes are created & applied to assist the mining process from the given set of attributes. This simplifies the original data & makes the mining more efficient.
5. Generalization:
It converts low-level data attributes to high-level data attributes using concept hierarchy. For Example Age initially in Numerical form (22, 25) is converted into categorical value (young, old).
For example, Categorical attributes, such as house addresses, may be generalized to higher-level definitions, such as town or country.
6. Normalization: Data normalization involves converting all data variable into a given range.
Techniques that are used for normalization are:
-
Min-Max Normalization:
- This transforms the original data linearly.
- Suppose that: min_A is the minima and max_A is the maxima of an attribute, P
We Have the Formula:
- Where v is the value you want to plot in the new range.
- v’ is the new value you get after normalizing the old value.
Solved example: Suppose the minimum and maximum value for an attribute profit(P) are Rs. 10, 000 and Rs. 100, 000. We want to plot the profit in the range [0, 1]. Using min-max normalization the value of Rs. 20, 000 for attribute profit can be plotted to:
And hence, we get the value of v’ as 0.11
-
Z-Score Normalization:
- In z-score normalization (or zero-mean normalization) the values of an attribute (A), are normalized based on the mean of A and its standard deviation
- A value, v, of attribute A is normalized to v’ by computing
For example: Let mean of an attribute P = 60, 000, Standard Deviation = 10, 000, for the attribute P. Using z-score normalization, a value of 85000 for P can be transformed to:
And hence we get the value of v’ to be 2.5
-
Decimal Scaling:
- It normalizes the values of an attribute by changing the position of their decimal points
- The number of points by which the decimal point is moved can be determined by the absolute maximum value of attribute A.
- A value, v, of attribute A is normalized to v’ by computing
- where j is the smallest integer such that Max(|v’|) < 1.
For example:
- Suppose: Values of an attribute P varies from -99 to 99.
- The maximum absolute value of P is 99.
- For normalizing the values we divide the numbers by 100 (i.e., j = 2) or (number of integers in the largest number) so that values come out to be as 0.98, 0.97 and so on.
Article Tags :
Data transformation works on the simple objective of extracting data from a source, converting it into a usable format and then delivering the converted data to the destination system. The extraction phase involves data being pulled into a central repository from different sources or locations, therefore it is usually in its raw original form which is not usable. To ensure the usability of the extracted data it must be transformed into the desired format by taking it through a number of steps. In certain cases, the data also needs to be cleaned before the transformation takes place. This step resolves the issues of missing values and inconsistencies that exist in the dataset. The data transformation process is carried out in five stages.
1. Discovery
The first step is to identify and understand data in its original source format with the help of data profiling tools. Finding all the sources and data types that need to be transformed. This step helps in understanding how the data needs to be transformed to fit into the desired format.
2. Mapping
The transformation is planned during the data mapping phase. This includes determining the current structure, and the consequent transformation that is required, then mapping the data to understand at a basic level, the way individual fields would be modified, joined or aggregated.
3. Code Generation
The code, which is required to run the transformation process, is created in this step using a data transformation platform or tool.
4. Execution
The data is finally converted into the selected format with the help of the code. The data is extracted from the source(s), which can vary from structured to streaming, telemetry to log files. Next, transformations are carried out on data, such as aggregation, format conversion or merging, as planned in the mapping stage. The transformed data is then sent to the destination system which could be a dataset or a data warehouse.
Some of the transformation types, depending on the data involved, include:
- Filtering which helps in selecting certain columns that require transformation
- Enriching which fills out the basic gaps in the data set
- Splitting where a single column is split into multiple or vice versa
- Removal of duplicate data, and
- Joining data from different sources
5. Review
The transformed data is evaluated to ensure the conversion has had the desired results in terms of the format of the data.
It must also be noted that not all data will need transformation, at times it can be used as is.
Data Transformation Techniques
There are several data transformation techniques that are used to clean data and structure it before it is stored in a data warehouse or analyzed for business intelligence. Not all of these techniques work with all types of data, and sometimes more than one technique may be applied. Nine of the most common techniques are:
1. Revising
Revising ensures the data supports its intended use by organizing it in the required and correct way. It does this in a range of ways.
- Dataset normalization revises data by eliminating redundancies in the data set. The data model becomes more precise and legible while also occupying less space. This process, however, does involve a lot of critical thinking, investigation and reverse engineering.
- Data cleansing ensures the formatting capability of data.
- Format conversion changes the data types to ensure compatibility.
- Key structuring converts values with built-in meanings to generic identifiers to be used as unique keys.
- Deduplication identifies and removes duplicates.
- Data validation validates records and removes the ones that are incomplete.
- Repeated and unused columns can be removed to improve overall performance and legibility of the data set.
2. Manipulation
This involves creation of new values from existing ones or changing current data through computation. Manipulation is also used to convert unstructured data into structured data that can be used by machine learning algorithms.
- Derivation, which is cross column calculations
- Summarization that aggregates values
- Pivoting which involves converting columns values into rows and vice versa
- Sorting, ordering and indexing of data to enhance search performance
- Scaling, normalization and standardization that helps in comparing dissimilar numbers by putting them on a consistent scale
- Vectorization which helps convert non-numerical data into number arrays that are often used for machine learning applications
3. Separating
This involves dividing up the data values into its parts for granular analysis. Splitting involves dividing up a single column with several values into separate columns with each of those values. This allows for filtering on the basis of certain values.
4. Combining/ Integrating
Records from across tables and sources are combined to acquire a more holistic view of activities and functions of an organization. It couples data from multiple tables and datasets and combines records from multiple tables.
5. Data Smoothing
This process removes meaningless, noisy, or distorted data from the data set. By removing outliers, trends are most easily identified.
6. Data Aggregation
This technique gathers raw data from multiple sources and turns it into a summary form which can be used for analysis. An example is the raw data providing statistics such as averages and sums.
7. Discretization
With the help of this technique, interval labels are created in continuous data in an attempt to enhance its efficiency and easier analysis. The decision tree algorithms are utilized by this process to transform large datasets into categorical data.
8. Generalization
Low level data attributes are transformed into high level attributes by using the concept of hierarchies and creating layers of successive summary data. This helps in creating clear data snapshots.
9. Attribute Construction
In this technique, a new set of attributes is created from an existing set to facilitate the mining process.
Why Do Businesses Need Data Transformation?
Organizations generate a huge amount of data daily. However, it is of no value unless it can be used to gather insights and drive business growth. Organizations utilize data transformation to convert data into formats that can then be used for several processes. There are a few reasons why organizations should transform their data.
- Transformation makes disparate sets of data compatible with each other, which makes it easier to aggregate data for a thorough analysis
- Migration of data is easier since the source format can be transformed into the target format
- Data transformation helps in consolidating data, structured and unstructured
- The process of transformation also allows for enrichment which enhances the quality of data
The ultimate goal is consistent, accessible data that provides organizations with accurate analytic insights and predictions.
Benefits of Data Transformation
Data holds the potential to directly affect an organization’s efficiencies and its bottom line. It plays a crucial role in understanding customer behavior, internal processes, and industry trends. While every organization has the ability to collect an immense amount of data, the challenge is to ensure that this is usable. Data transformation processes empower organizations to reap the benefits offered by the data.
Data Utilization
If the data being collected isn’t in an appropriate format, it often ends up not being utilized at all. With the help of data transformation tools, organizations can finally realize the true potential of the data they have amassed since the transformation process standardizes the data and improves its usability and accessibility.
Data Consistency
Data is continuously being collected from a range of sources which increases the inconsistencies in metadata. This makes organization and understanding data a huge challenge. Data transformation helps making it simpler to understand and organize data sets.
Better Quality Data
Transformation process also enhances the quality of data which can then be utilized to acquire business intelligence.
Compatibility Across Platforms
Data transformation also supports compatibility between types of data, applications and systems.
Faster Data Access
It is quicker and easier to retrieve data that has been transformed into a standardized format.
More Accurate Insights and Predictions
The transformation process generates data models which are then converted to metrics, dashboards and reports which enable organizations to achieve specific goals. The metrics and key performance indicators help businesses quantify their efforts and analyze their progress. After being transformed, data can be used for many use cases, including:
- Analytics which use metrics from one or many sources to gain deeper insights about the functions and operations of any organization. Transformation of data is required when the metric combines data from multiple sources.
- Machine learning which helps businesses with their profit and revenue projections, supports their decision making with predictive modeling, and automation of several business processes.
- Regulatory compliance which involves sensitive data that is vulnerable to malicious attacks