17
November
BRITISH INTERNATIONAL
SCHOOL OF SULAYMANIYAH
2024
Information
Technology
Data Analysis and Visualisation
OBJECTIVES
❖ Transforming and cleaning data to extract meaningful information.
❖ Getting data from different sources.
❖ Displaying data to communicate information.
2
Cleaning
Data Transformation & ❖ Data Transformation: Changing data’s format, structure, or values to
make it suitable for analysis or reporting.
❖ Types of Transformation:
➢ Constructive: Adds or replicates data, e.g., combining customer and sales
data.
➢ Destructive: Deletes fields or records to simplify data, e.g., anonymizing
names, converting ages to ranges.
➢ Aesthetic: Standardizes data formats, e.g., using a consistent date or name
format.
➢ Structural: Reorganizes data, e.g., renaming or combining database entities.
➢ Normalization: Organizes data for relational databases, optimizing for
accuracy and efficiency.
3
Data Cleaning
❖ Goals of Clean Data:
➢ High Quality and Validity: Data must meet the analysis requirements.
➢ Accuracy: Data must be correct, e.g., names spelled consistently.
➢ Consistency: Same values in repeated instances, e.g., consistent names
and addresses.
➢ Completeness: All necessary data is included.
➢ Uniformity: Standardized units of measurement and formats.
❖ Data Cleaning Methods:
➢ Use specialized software or database management tools.
➢ Remove anomalies and contradictions, report any unresolved issues.
➢ Cost Consideration: Cleaning can be resource-intensive, both in time and
computational power.
4
Different Sources
Data Integration from ❖ Data Integration: Combining data from multiple sources for unified
analysis or reporting.
❖ Types of Data Sources:
➢ Primary Sources: Original data, e.g., from interviews, surveys, sensor
readings.
➢ Secondary Sources: Derived data, e.g., information compiled from original
sources.
❖ Data Source Locations:
➢ Common sources include text files, spreadsheets, databases, XML, or other
stored data.
➢ Examples include websites combining data for user searches or online
shopping (e.g., customer, stock, and financial data).
5
Data Integration
❖ Methods of Data Integration:
➢ Linking: Dynamic updates from the source file; when the source updates,
linked data changes automatically.
➢ Embedding: Static data copied into a new document; updates in the source
don’t affect the embedded data.
❖ Practical Application in Spreadsheets:
➢ Manual Copy-Paste: Useful if data doesn’t change frequently but requires
manual updates if source data changes.
➢ Linking Across Spreadsheets:
■ Maintains updates across sheets; can use formulas or hyperlinks.
■ Links can be broken if updates are no longer needed but lose automatic update
functionality.
6
Comparison
Data Consolidation and ❖ Data Consolidation:
➢ Data Warehousing: Centralized storage (data warehouse) for quick access to
consolidated data.
➢ ELT Tools: Extract, Load, and Transform tools move data into the warehouse,
sometimes specialized tools are used for complex consolidations.
➢ Alternative Options: Smaller businesses may use Microsoft Access or
LibreOffice Base for data consolidation.
❖ Data Comparison Techniques:
➢ Spreadsheet Functions: Use If statements, Lookup functions.
➢ Complex comparisons can use nested If statements, Concatenate, or various
Lookup functions to bring together data from different sources.
❖ Database Queries for Comparison:
➢ SQL queries join tables, matching records for comparisons across data sets.
➢ Select Query: Defines tables, fields, and matching criteria.
➢ Fields should be the same data type for joining; otherwise, a criterion-based
comparison is needed.
7
Fields
Splitting and Merging Data ❖ Splitting Data: Divides data in a single field into separate fields for easier
searching or analysis.
➢ Examples: Splitting a combined address into street, city, and postal code
fields.
➢ Ideally done before importing data, but can be managed post-import using
queries (e.g., Split() function in Microsoft Access and LibreOffice Base).
❖ Merging Data Fields:
➢ Combining fields at runtime (without changing data structure) for reports or
summaries.
➢ Relational Database Practice: Keep data at the most atomic level (smallest
detail) to ensure flexibility and accuracy.
➢ Methods for Merging:
■ Microsoft Access: Use & operator in queries to concatenate fields with spaces.
■ SQL in Microsoft Access and LibreOffice Base: Select and merge fields as needed
for analysis.
8
Data Visualization
❖ Data Visualization: Conveys data meaningfully; helps users understand
patterns and trends with minimal analysis.
❖ Types of Visualizations:
➢ Graphs and charts to show data relationships and trends.
➢ Pivot tables for summarizing and exploring large datasets.
➢ Best Practices for Effective Visuals:
➢ Titles and Labels: Clear, meaningful titles; clearly labeled axes.
➢ Clarity: Avoid unnecessary clutter; keep visuals simple.
➢ Units and Proportions: Display units of measurement and avoid distorting
data scales.
➢ Data Source: Include source information when applicable for credibility.
❖ Pivot Table Usage:
➢ Format Requirements: Data should be transformed and cleaned, with
headers, no blank rows/columns, and no totals within data.
➢ Benefits: Quickly summarize, explore trends, and compare data dynamically.
9
❖
Data Visualization cont.
Graphs and Charts:
➢ Visualize data trends, making it accessible without needing advanced
analytical skills.
➢ Should include a clear title, labeled axes, minimal clutter, proper units, and
proportional axes.
➢ Include the data source where appropriate.
❖ Pivot Table Reports:
➢ Function: Summarize and explore large datasets dynamically.
➢ Setup: Ensure data is correctly formatted, with headers, no blank
rows/columns, and no pre-existing totals.
➢ Customization: Sort, filter, and organize data within pivot tables for specific
analyses.
➢ Applications: Use pivot tables to identify patterns, trends, and make
comparisons within data.
10
❖
Dashboards
A single view combining multiple visualizations, like charts and tables,
for quick insights.
❖ Present different views of data together for easy access and interaction.
❖ Setup:
➢ Import data into a spreadsheet (e.g., Microsoft Excel).
➢ Format and prepare data with no missing rows/columns, unique records, and
correct table structure.
➢ Use pivot tables, filters, and charts to arrange information for the dashboard.
❖ Interactive Elements:
➢ Filters: Allow users to adjust views dynamically.
➢ Shapes and Tables: Use references to display specific values, such as
summaries.
➢ Pivot Charts: Display pivot table data visually, can be refreshed as data
changes.
11
❖
Dashboards cont.
Components:
➢ Shapes: Display key values or summaries, formatted with labels.
➢ Tables: Use formulas like SUMIF() to reference data directly for meaningful
insights.
➢ Pivot Tables and Charts: Place them on the dashboard for interactive
exploration.
❖ Interactivity:
➢ Enable user filtering through dropdown menus in pivot charts.
➢ Manually refresh pivot tables and charts to update data or enable automatic
refresh on opening the file.
12
❖
Pivot Charts
Visual representation of data in pivot tables for better communication.
➢ Editing: Titles, legends, and colors can be customized.
➢ Limitations: Data cannot be directly edited in pivot charts; changes must be
made in the source data.
❖ In Microsoft Excel pivot charts can be created via the Insert menu with
customization options for appearance.
❖ Pivot in Dashboards:
➢ Manipulating Charts: Use filters within pivot charts to change displayed data
dynamically.
➢ Updating Data: Must refresh manually for changes in underlying data to
reflect in charts.
➢ Considerations: Multiple pivot tables or charts on one dashboard may
influence each other when filters are adjusted, so careful setup is required.
13
❖
Dashboard Customization
Layout and Formatting:
➢ Hide gridlines for a clean look.
➢ Position charts, tables, and shapes strategically to optimize readability and
flow.
➢ Use consistent formatting (e.g., font styles, colors) across elements.
❖ User Interaction:
➢ Filters allow users to interact with data directly on the dashboard.
➢ Pivot chart filters let users adjust data displays without altering the source
data.
➢ Example: Filtering specific product types or sales regions to view relevant
sales data in charts.
14
Best Practices
❖ Data Cleansing and Preparation: Essential for accurate analysis and
reliable visualizations.
❖ Choosing the Right Visuals:
➢ Match visualization type to data (e.g., bar charts for comparisons, line charts
for trends).
❖ Maintaining Simplicity: Avoid excessive information and overly bright
colors; focus on key insights.
❖ Updating and Maintenance:
➢ Ensure data updates are reflected by refreshing pivot tables and charts.
➢ Set up dashboards to automatically refresh if linked to live data sources or
files.
15