We use cookies, including third-party cookies from Google to serve personalized ads through AdSense, to operate this site and understand how it is used. By continuing to browse, you accept this use. See our Privacy Policy and Terms of Use for details, including how to opt out of personalized advertising.
Accept
SmartData CollectiveSmartData Collective
  • Analytics
    AnalyticsShow More
    Flat editorial illustration: The article is about joining public web signals with internal business metrics so BI teams can inter
    Alternative Data in BI: Bringing Public Web Data Into Your Dashboards
    23 Min Read
    Flat editorial illustration: The article is about automating the collection, preparation, and delivery of marketing data across p
    From Data to Dashboards: 5 Marketing Reporting Automation Software Picks
    12 Min Read
    Flat editorial illustration: The article contrasts passive follower totals with a behavioral path from attention and trust to cli
    Creator Analytics Beyond Follower Count: Seven Metrics That Measure Audience Behavior
    11 Min Read
    Flat editorial illustration: The article connects AI and marketing analytics with managerial judgment grounded in consumer behavi
    How an Online MBA in Marketing Connects AI and Data Analytics
    11 Min Read
    What Kind of Problem-Solving Distinguishes Data Analysts From Software Engineers -- AI-generated illustration
    What Kind of Problem-Solving Distinguishes Data Analysts From Software Engineers
    7 Min Read
  • Big Data
  • BI
  • Exclusive
  • IT
  • Marketing
  • Software
Search
© 2008-25 SmartData Collective. All Rights Reserved.
Reading: A simple Data Transformation example…
Share
Notification
Font ResizerAa
SmartData CollectiveSmartData Collective
Font ResizerAa
Search
  • About
  • Help
  • Privacy
Follow US
© 2008-23 SmartData Collective. All Rights Reserved.
SmartData Collective > Big Data > Data Mining > A simple Data Transformation example…
Business IntelligenceData Mining

A simple Data Transformation example…

TimManns
TimManns
5 Min Read
A simple Data Transformation example...
Illustration generated with Qwen Image.
SHARE

In my experience of customer focused data mining projects, over 80% of the time is spent preparing and transforming the customer data into a usable format. Often the data is transformed to a ‘single row per customer’ or similar summarised format, and many columns (aka variables or fields) are created to act as inputs into predictive or clustering models. Such data transformation can also be referred to as ETL (extract transform load), although my work is usually as SQL within the data warehouse so it is just the ‘T’ bit.

Granted a lot of the ETL you perform will be data and industry specific, so I’ve tried to keep things very simple. I hope that the example below to transform transactional data into some useful customer-centric format will be generic. Feedback and open discussion might broaden my habits.

Strangely many ‘data mining’ books almost completely avoid the topic of data processing and data transformations. Often data mining books that do mention data processing simply refer to feature selection algorithms or applying a log function to rescale numeric data to act as predictive algorithm inputs. Some mention the various types of means you could create (arithmetic, harmonic, winsorised, etc), or measures of dispersion (range, variance, standard deviation etc).

There seems to be a glaring big gap! I’m specifically referring to data processing steps that are separate from those mandatory or statistical requirements of the modelling algorithm. In my experience relatively simple steps in data processing can yield significantly better results than tweaking algorithm parameters. Some of these data processing steps are likely to be industry or data specific, but I’m guessing many are widely useful. They don’t necessarily have to be statistical in nature.
So (to put my money where my mouth is) I’ve started by illustrating a very simple data transformation that I expect is common. On a public SPSS Clementine forum I’ve attached a small demo data file (I created, and entirely fictitious) and SPSS Clementine stream file that processes it (only useful for users of SPSS Clementine).
Clementine Stream and text data files
my post to a Clementine user forum

More Read

5 Must-Watch YouTube Videos on Big Data
5 Must-Watch YouTube Videos on Big Data
So What is Business Analytics & Its Various Components?
Mixed Feelings About Agile
Indispensable Money Management Apps for Smartphone Users
Big Data: Aspirin for Travelers’ Headaches

I’m hoping that my peers might exchange similar ideas (hint!). A lot of this ETL stuff may be basic, but it’s rarely what data miners talk about and what I would find useful. This is just the start of a series of ETL you could perform.

I’ve also added a poll for feedback whether this is helpful, too basic, etc

– Tim

Example data processing steps

a)Creation of additional dummy columns
Where the data has a single category column that contains one of several values (in this example voice calls, sms calls, data calls etc) we can use a CASE statement to create a new column for each category. We can use 0 or 1 as indicators if the category value occurs in any specific row, but you can also use the value of a numeric field (for example call count or duration of the data is already partly summarised). A new column is created for each category field.

For example;

customercategoryscore
billfood10
billdrink20
benfood15
billdrink25
bendrink20

Can be changed to;

customercategoryscorefood_inddrink_ind
billfood1010
billdrink2001
benfood1510
billdrink2501
bendrink2001

Or even;

customercategoryscorefood_scoredrink_score
billfood10100
billdrink20020
benfood15150
billdrink25025
bendrink20020

b) Summarisation
Aggregate the data so that we have only one row per customer (or whatever your ‘unique identifier’ is) and sum or average the dummy and/or raw columns.
So we could change the previous step to something like this;

customerfood_scoredrink_score
bill1045
ben1520
Share This Article
Facebook Pinterest LinkedIn
Share

Follow us on Facebook

Latest News

Flat editorial illustration: The article connects downstream report failures, data-quality gaps, and conflicting metric definitio
Signs Your Data Architecture Needs an Outside Review
Big Data Exclusive
Data-Driven Pultrusion: Best GFRP Rebar Machines & ROI
Data-Driven Pultrusion: Best GFRP Rebar Machines & ROI
Big Data Exclusive
Flat editorial illustration: The article contrasts tools for gathering fresh interviews with tools for analyzing existing qualita
10 AI Market Research Tools That Turn Qualitative Data into Insights
Artificial Intelligence Big Data Exclusive
Flat editorial illustration: The article compares proxy MCP servers by what an AI agent can do through them: manage proxy account
Proxy MCP Servers: 7 Options and What Each Lets an AI Agent Do
Artificial Intelligence Big Data Data Collection

Stay Connected

1.2KFollowersLike
33.7KFollowersFollow
222FollowersPin

You Might also Like

The Big Data Uprising: It's Not About Big Or Data
AnalyticsBig DataBusiness IntelligenceCloud ComputingData MiningHadoopMapReducePredictive AnalyticsUnstructured Data

The Big Data Uprising: It’s Not About Big Or Data

12 Min Read
The Blame Game
AnalyticsCulture/LeadershipDecision ManagementInside Companies

The Blame Game

6 Min Read
The NSA, Link Analysis and Fraud Detection
AnalyticsBig DataData ManagementData MiningPolicy and GovernancePredictive AnalyticsPrivacyTransparency

The NSA, Link Analysis and Fraud Detection

5 Min Read
Enterprise Data Management - Not Just for Finance
Data WarehousingDecision Management

Enterprise Data Management – Not Just for Finance

3 Min Read

SmartData Collective is one of the largest & trusted community covering technical content about Big Data, BI, Cloud, Analytics, Artificial Intelligence, IoT & more.

Artificial Intelligence for eCommerce: A Closer Look
Artificial Intelligence for eCommerce: A Closer Look
Artificial Intelligence
The Art of Conversation: Enhancing Chatbots with Advanced AI Prompts
The Art of Conversation: Enhancing Chatbots with Advanced AI Prompts
Chatbots

Quick Link

  • About
  • Contact
  • Privacy
Follow US
© 2008-26 SmartData Collective. All Rights Reserved.
Welcome Back!

Sign in to your account

Username or Email Address
Password

Lost your password?