Information Technology (9626) | Data analysis and visualisation

Introduction: Turning Data into Knowledge

Hello! Welcome to the exciting chapter on Data Analysis and Visualisation. You’ve already mastered the fundamental tools—spreadsheets, databases, and modelling—in your AS studies. Now, we move up to A Level, where we learn how to use those tools to tackle complex, real-world data problems.

This topic is vital because simply having data isn't enough; you must be able to clean it, combine it, interpret it, and show it to others so they can make smart decisions. Think of yourself as a data detective, turning messy piles of raw information into beautiful, insightful stories!

17.1 Data Analysis and Visualisation

The core requirement of this section is to apply your existing AS skills (Sections 8, 9, 10) to a higher level of complexity: dealing with imperfect data from multiple sources and presenting the resulting insights visually.

1. Transforming and Cleaning Data

Before you can analyse data, you must ensure it is reliable. Transforming and cleaning data is the process of correcting errors, handling missing values, and making sure the data is consistently formatted.

Analogy: If you're building a house, cleaning the data is like preparing the foundation. If the foundation is weak (dirty data), the house (your analysis) will fall apart.

Key Cleaning Tasks to Extract Meaningful Information:

• Standardising Formats: Ensuring all dates look the same (e.g., DD/MM/YYYY) and all text entries are consistent (e.g., "UK" instead of "United Kingdom").
• Handling Outliers: Identifying and deciding what to do with data points that are drastically different from the rest (e.g., a salary entry of 5,000,000, which might be a typo).
• Removing Duplicates: Ensuring each unique entity (e.g., customer, transaction) is represented only once.
• Dealing with Missing Data: Either filling in gaps using reasonable estimates or deciding to exclude incomplete records.

Key Takeaway: Clean data leads directly to meaningful information. Garbage In, Garbage Out (GIGO) is the golden rule here!

2. Getting Data from Different Sources

In real-world IT systems, data rarely sits neatly in one place. You often have to work with data exported from two different databases, spreadsheets, or logs. This requires specific techniques to manage multi-source input.

Comparing and Consolidating Data

This involves bringing together information from different files that share a common field (like a product ID or employee name) and merging the useful bits into a single, cohesive dataset.

• Comparing: Checking the datasets against each other to identify similarities and differences. Example: Comparing the stock levels recorded in the warehouse database against the stock levels recorded in the online sales system.
• Consolidating: Combining the data, often summing or calculating totals from multiple sources based on a key criterion. Example: Combining the monthly sales spreadsheets from all five regional branches into one master sheet to find the total sales per product line.

Splitting and Merging Data Fields

These are essential transformation techniques often achieved using spreadsheet functions (like LEFT, RIGHT, MID, FIND, CONCATENATE, or the 'Text to Columns' tool).

1. Splitting Data into Discrete Fields

This means taking one field and separating its contents into two or more distinct fields. You do this when a field holds multiple pieces of information that need to be analysed separately.

• Example: A field named 'ProductCode' contains A456-RED. You might need to split this into 'Product_ID' (A456) and 'Colour' (RED).
• Why? If you want to sort by colour, the colour must be in its own discrete field.

2. Merging and Combining Data into Required Fields

This is the opposite: taking two or more discrete fields and joining them together, usually for presentation purposes (like creating a mailing label).

• Example: Combining 'First Name' and 'Last Name' fields, separated by a space, to create a single 'Full Name' field.
• Common Mistake to Avoid: Forgetting to include necessary characters, like spaces or commas, when joining text fields!

3. Displaying Data to Communicate Information (Visualisation)

Once the data is clean and prepared, the final, crucial step is presenting it so that users can quickly grasp the findings without wading through thousands of rows. This is where Visualisation comes in.

Pivot Table Reports

Pivot tables are powerful summary tools used primarily in spreadsheet software (but also accessible in databases through cross-tab queries).

• Definition: A pivot table dynamically summarises data from a larger dataset by grouping, averaging, counting, or summing the data based on user-defined rows, columns, and filters.
• Purpose: They allow users to quickly rotate (or "pivot") the data structure to see different perspectives without manually writing complex formulas for every grouping.
• Example: Imagine you have transaction data for a whole year. A pivot table can quickly show you the Total Revenue (Value) for each Month (Row) split by Product Category (Column).
• Benefits: They make complex data summaries instantaneous, dynamic, and easy to adjust based on stakeholder questions.

Pivot Charts

A Pivot Chart is simply a graphical representation of the summary data presented in a pivot table.

Did you know? If you change the grouping or filtering on the pivot table, the pivot chart automatically updates to reflect the new summary—that's the "dynamic" nature of visualisation.

• Purpose: To communicate trends, patterns, and insights identified in the pivot table report visually.
• Appropriate Chart Types: You must select a chart type appropriate for the data and the message you want to convey (you learned about Bar, Pie, and Line charts in AS Level Spreadsheets, Section 8.4).
  • Bar Chart: Good for comparing discrete categories (e.g., sales across different regions).
  • Line Graph: Excellent for showing trends over time (e.g., monthly sales performance).
  • Pie Chart: Used for showing proportions (parts of a whole, e.g., market share of different products).
• Efficiency: A well-designed pivot chart communicates in seconds what a pivot table report might take minutes to understand.

Key Takeaway: Pivot tables summarise what happened. Pivot charts quickly reveal the story behind the numbers and help in efficient communication.

Summary of Data Analysis and Visualisation

This chapter is about applying your existing IT skills in an integrated way to solve business problems. Successful data analysis and visualisation require three things:

1. Preparation: Cleaning and transforming dirty data (splitting, merging, consolidating).
2. Analysis: Using powerful summary tools like pivot table reports to interpret complex data relationships.
3. Communication: Presenting findings clearly and efficiently using dynamic pivot charts.

Keep practising these skills, especially working with data from different sources, and you'll master this crucial A Level topic!