Profiling and Auditing Raw Data

What Is Data Profiling?

Before you clean any dataset, you need to understand what is actually in it. Data profiling is the process of examining a raw dataset to collect statistics and metadata about its structure, content, and quality. Profiling answers questions like: How many rows and columns are there? What data type is each column? How many values are missing? Are there duplicates? Are numeric distributions skewed or do they contain outliers? A data audit takes those findings one step further—it judges whether the data is fit for its intended purpose and communicates the results to stakeholders.

Profiling before cleaning is standard practice. Jumping straight into transformation without understanding the data leads to missed problems, incorrect assumptions, and errors that can invalidate an entire analysis.

Safety and Professional Responsibility

Data work carries its own professional safety obligations. Raw datasets frequently contain personally identifiable information (PII) such as names, addresses, inspection scores tied to specific businesses, or GPS coordinates. Before profiling any real dataset:

• Confirm you have authorization to access and process the data.
• Work only in approved, secured environments (never upload sensitive data to a public cloud notebook without explicit permission).
• Do not share profile reports outside the project team unless PII has been removed or masked.
• Follow your organization's data-governance and acceptable-use policies.

In this lab the city inspection CSV is a synthetic dataset provided by your instructor, so PII risk is low—but building these habits now matters for every real-world project you encounter.

Profiling with ydata-profiling in Python

`ydata-profiling` (formerly `pandas-profiling`) generates a detailed HTML report from a pandas DataFrame with just a few lines of code:

```python

import pandas as pd

from ydata_profiling import ProfileReport

df = pd.read_csv("city_inspections.csv")

profile = ProfileReport(df, title="City Inspections Profile", explorative=True)

profile.to_file("inspection_report.html")

```

The HTML report is organized into several sections you must learn to read:

• Overview – row and column counts, total missing cells, duplicate rows, and memory usage.
• Variables – per-column statistics. For numeric columns: mean, median, standard deviation, min, max, and a histogram. For categorical columns: number of distinct values, the most frequent value, and a bar chart.
• Interactions – scatter plots for pairs of numeric columns.
• Correlations – Pearson, Spearman, and Phik correlation matrices.
• Missing values – a heatmap showing which columns are missing values and whether missingness clusters together (a pattern suggesting data was collected or exported incorrectly).
• Warnings – automated flags such as high cardinality, near-zero variance, and columns that are highly correlated with another.

When you annotate findings, be specific. "Column `inspector_id` has 3,412 missing values (6.8 % of rows)" is a finding. "Some data is missing" is not.

Profiling in R with skimr and dplyr

`skimr` provides a compact, readable summary directly in the R console or in an R Markdown document:

```r

library(skimr)

library(dplyr)

df <- read.csv("city_inspections.csv")

skim(df)

```

`skim()` returns a grouped table showing, for each column: the number of missing values, the completion rate, and type-appropriate statistics (mean, SD, quantiles for numeric columns; counts and proportions for character columns). You can chain `dplyr` verbs before `skim()` to profile subsets:

```r

df |> filter(year == 2022) |> skim()

```

Comparing `skimr` output to the ydata-profiling report is a useful cross-check. Minor differences in statistics (for example, slightly different means) may reflect how each tool handles missing values during calculation—one may exclude NA by default while the other may not. Documenting discrepancies is part of building a trustworthy audit.

SQL COUNT and NULL Queries

When data lives in a relational database, SQL is the primary profiling tool. Two patterns are essential:

Count total rows and non-null values per column:

```sql

SELECT

COUNT(*) AS total_rows,

COUNT(inspector_id) AS non_null_inspector,

COUNT(*) - COUNT(inspector_id) AS missing_inspector

FROM city_inspections;

```

Find duplicate keys:

```sql

SELECT inspection_id, COUNT(*) AS cnt

FROM city_inspections

GROUP BY inspection_id

HAVING COUNT(*) > 1;

```

SQL profiling is fast on large tables and produces exact counts, which you can then verify against your Python and R outputs.

Writing the Audit Memo

A data audit memo translates technical findings into language a project manager can act on. The standard structure is:

1. Header – To, From, Date, Subject.

2. Purpose – one sentence stating why the audit was done.

3. Key Findings – numbered list of the top five quality issues, ordered by severity.

4. Business Impact – for each issue, explain what could go wrong in the analysis if the issue is not addressed.

5. Recommended Next Steps – specific, actionable cleaning tasks.

Avoid jargon. Write "14 % of inspection dates are missing" rather than "the temporal attribute exhibits a 14 % null prevalence." A project manager who is not a data scientist must be able to read your memo and make a prioritization decision.

Credential Relevance

Data profiling and auditing appear across all four credentials in this program. CompTIA Data+ covers data quality dimensions and documentation. Google Data Analytics emphasizes the inspect-and-clean cycle. IBM Data Science Professional includes exploratory data analysis as a core competency. Microsoft PL-300 requires understanding data-model quality before building Power BI reports. Mastering this single skill strengthens your preparation for all four exams.