The Python Institute PCAD-31-02 exam has 48 items, and 30 of them come from two syllabus blocks: Data Acquisition and Pre-Processing (14) and Programming and Database Skills (16). If you can clean a messy file, validate it, move it into a database with safe queries and read the result back into pandas, you have practised the skills behind 62.5% of the exam.
This post does all of that in one small script you can run. Every output shown below was produced by running it with Python 3.12 and pandas 3.0.6, using only sqlite3 from the standard library for the database part.
What does the PCAD-31-02 exam look like?
PCAD (Certified Associate Data Analyst with Python) is the associate-level data exam of the OpenEDG Python Institute. The figures below come from the official PCAD certification page, read on 15 October 2026.
| Item | Value |
|---|---|
| Exam code | PCAD-31-02, active since July 15, 2025 (PCAD-31-01 retired July 14, 2025) |
| Questions | 48 single-select and multiple-select items, some scenario-based |
| Time | 60 minutes, plus the NDA screen |
| Passing score | 75% |
| Price | Exam from $195; exam with retake from $225; practice test $49 |
| Delivery | Online through OpenEDG Testing Service (TestNow) |
| Prerequisites | None formal; PCAP and PCED (or equivalent) recommended |
| Validity | 6 years |
How are the 48 items split across the five blocks?
The Python Institute publishes the split in the PCAD-31-02 exam syllabus, last updated July 15, 2025. Each item is worth up to one point, and the raw score is normalized to a percentage.
| Block | Items | Weight |
|---|---|---|
| 1. Data Acquisition and Pre-Processing | 14 | 29.2% |
| 2. Programming and Database Skills | 16 | 33.3% |
| 3. Statistical Analysis | 4 | 8.3% |
| 4. Data Analysis and Modeling | 9 | 18.8% |
| 5. Data Communication and Visualization | 5 | 10.4% |
Note what that means for your study plan. Blocks 1 and 2 are the bulk, but 30 of 48 is still below the 75% pass mark, so the statistics, pandas and charting blocks cannot be skipped. The script below covers the large blocks; it also touches objective 4.1.1 (cleaning with pandas) on the way.
Why load every column as text first?
Objective 1.2.2 asks you to identify erroneous data before you fix it. If pandas guesses types on load, a bad value can silently change a whole column's type, and you lose the chance to see which rows were wrong. Loading with dtype=str keeps the raw values so the cleaning step decides what each one means.
Here is a small order file with the problems the syllabus lists by name: stray spaces, mixed case, a thousands separator, a missing value written as text, a negative amount, a duplicate row, a date in the wrong format and five spellings of yes and no.
import io
import sqlite3
import pandas as pd
pd.set_option("display.width", 120, "display.max_columns", 10) # print tables in full
RAW = """order_id,customer,country,amount,paid,order_date,ship_date
1001, Ana Lima ,BR,120.50,yes,2026-09-01,2026-09-03
1002,ben okafor,NG,"1,080.00",Y,2026-09-02,2026-09-01
1003,Chen Wei,CN,n/a,no,2026-09-02,2026-09-05
1004,Ana Lima,br,-15,TRUE,2026-09-03,2026-09-04
1004,Ana Lima,br,-15,TRUE,2026-09-03,2026-09-04
1005,Dara Singh,IN,64.00,0,03/09/2026,2026-09-06
1006,Ben Okafor,NG,310.00,1,2026-09-04,2026-09-07
1007,Chen Wei,cn,89.90,yes,2026-09-05,2026-09-05
1008,Dara Singh,IN,455.00,N,2026-09-06,2026-09-09
1009,ana lima,BR,72.25,y,2026-09-07,2026-09-08
"""
df = pd.read_csv(io.StringIO(RAW), dtype=str)
print(len(df), "rows") # 10 rows
Which cleaning steps does Block 1 name, and what do they look like?
Objective 1.2.4 lists them in one sentence: string manipulation, format standardization, boolean normalization, case normalization and string-to-number conversion. Each one is a single line in pandas.
BOOL = {"yes": True, "y": True, "true": True, "1": True,
"no": False, "n": False, "false": False, "0": False}
df = df.drop_duplicates()
df["customer"] = df["customer"].str.strip().str.title()
df["country"] = df["country"].str.strip().str.upper()
df["amount"] = pd.to_numeric(df["amount"].str.replace(",", ""), errors="coerce")
df["paid"] = df["paid"].str.strip().str.lower().map(BOOL)
for col in ("order_date", "ship_date"):
df[col] = pd.to_datetime(df[col], format="%Y-%m-%d", errors="coerce")
df["order_id"] = df["order_id"].astype(int)
print(df.dtypes)
order_id int64
customer str
country str
amount float64
paid bool
order_date datetime64[us]
ship_date datetime64[us]
dtype: object
The key argument is errors="coerce". As the pandas.to_numeric reference explains, it turns a value that cannot be parsed into NaN instead of raising an exception. That is what you want during cleaning: the bad value becomes a marker you can count and report, not a crash halfway through a file.
The same idea applies to dates. Passing an explicit format means 03/09/2026 becomes NaT rather than being guessed as March 9 or September 3. Whether that date was day-first or month-first is a question for the data owner, not for the parser.
What do type, range and cross-field checks look like in pandas?
Objective 1.3.1 names three kinds of validation: type, range and cross-field. Build each check as a boolean column, then keep the rows where no check fired. A table of named checks is easier to report to a stakeholder than a chain of filters.
checks = pd.DataFrame({
"amount_not_numeric": df["amount"].isna(), # type
"order_date_unparsed": df["order_date"].isna(), # type
"amount_not_positive": df["amount"] <= 0, # range
"shipped_before_order": df["ship_date"] < df["order_date"], # cross-field
})
bad = checks.any(axis=1)
print(checks[bad].set_axis(df.loc[bad, "order_id"]))
clean = df[~bad]
print(len(clean), "rows pass")
amount_not_numeric order_date_unparsed amount_not_positive shipped_before_order
order_id
1002 False False False True
1003 True False False False
1004 False False True False
1005 False True False False
5 rows pass
Two details are worth knowing for the exam. First, a comparison with NaN or NaT is always False, so a missing amount does not trip the range check; that is why the type check exists separately. Second, the duplicate of order 1004 was already gone, because drop_duplicates() ran before validation. Removing or imputing a row is a decision with consequences, and objective 1.2.4 asks you to discuss imputation against exclusion. Here exclusion is the honest choice: there is no sensible value to invent for a missing order amount.
How do Python types map to SQLite columns?
Objective 2.4.5 is about converting data types between SQL and Python. The sqlite3 module documentation lists exactly five Python types that go into SQLite without help: None, int, float, str and bytes.
| Python value | SQLite storage | What to do |
|---|---|---|
None |
NULL | Nothing |
int |
INTEGER | Nothing |
float |
REAL | Nothing |
str |
TEXT | Nothing |
bool |
INTEGER (0 or 1) | Store as int(value)
|
datetime or pandas Timestamp
|
No date type | Store as ISO 8601 text |
SQLite has no boolean and no date storage class. The SQLite page on datatypes and type affinity says booleans are stored as the integers 0 and 1, and dates as ISO 8601 text, a real number or an integer. Here is what happens if you skip the conversion:
con = sqlite3.connect(":memory:")
con.execute("""
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
country TEXT NOT NULL,
amount REAL CHECK (amount > 0),
paid INTEGER NOT NULL,
order_date TEXT NOT NULL
)""")
first = clean.iloc[0]
try:
con.execute("INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?)",
(first["order_id"], first["customer"], first["country"],
first["amount"], first["paid"], first["order_date"]))
except sqlite3.ProgrammingError as e:
print("ProgrammingError:", e)
ProgrammingError: Error binding parameter 6: type 'Timestamp' is not supported
So convert each row explicitly and insert the batch with executemany. The with con: block commits on success and rolls back on an exception, and the CHECK constraint in the table is a second line of defence for the range rule:
rows = [
(int(r.order_id), r.customer, r.country, float(r.amount),
int(r.paid), r.order_date.strftime("%Y-%m-%d"))
for r in clean.itertuples(index=False)
]
with con:
con.executemany("INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?)", rows)
print(con.execute("SELECT COUNT(*) FROM orders").fetchone()) # (5,)
try:
with con:
con.execute("INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?)",
(1010, "Eve Park", "KR", -5.0, 1, "2026-09-08"))
except sqlite3.IntegrityError as e:
print("IntegrityError:", e) # CHECK constraint failed: amount > 0
Why does the exam keep asking about parameterized queries?
Two Block 2 objectives, 2.4.4 and 2.4.6, come back to the same point: never build SQL by pasting values into a string. The demonstration takes four lines.
name = "x' OR '1'='1"
unsafe = f"SELECT COUNT(*) FROM orders WHERE customer = '{name}'"
print(con.execute(unsafe).fetchone()) # (5,) every row matched
print(con.execute("SELECT COUNT(*) FROM orders WHERE customer = ?", (name,)).fetchone()) # (0,)
print(con.execute("SELECT COUNT(*) FROM orders WHERE customer = :who",
{"who": "Ana Lima"}).fetchone()) # (2,)
The f-string version closes the quote early and turns the filter into a condition that is always true. With a ? placeholder the driver sends the value separately from the SQL text, so the quote characters are just part of a name that matches nobody. The third line shows the named style, :who with a dictionary, which reads better once a query has several parameters.
How do you get query results back into pandas?
Objective 2.4.1 lists the clauses the exam expects you to combine: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT. The query below uses all of them, with a LEFT JOIN so that an order from a country missing in the lookup table is kept and labelled rather than dropped.
con.executescript("""
CREATE TABLE countries (code TEXT PRIMARY KEY, region TEXT NOT NULL);
INSERT INTO countries VALUES ('BR', 'Americas'), ('NG', 'Africa'), ('IN', 'Asia');
""")
QUERY = """
SELECT COALESCE(c.region, 'Unmapped') AS region,
COUNT(*) AS orders,
ROUND(SUM(o.amount), 2) AS revenue,
MIN(o.order_date) AS first_order
FROM orders AS o
LEFT JOIN countries AS c ON c.code = o.country
WHERE o.paid = :paid
GROUP BY region
HAVING COUNT(*) >= :min_orders
ORDER BY revenue DESC
LIMIT 5
"""
summary = pd.read_sql_query(QUERY, con, params={"paid": 1, "min_orders": 1})
print(summary)
print(summary.dtypes)
region orders revenue first_order
0 Africa 1 310.00 2026-09-04
1 Americas 2 192.75 2026-09-01
2 Unmapped 1 89.90 2026-09-05
region str
orders int64
revenue float64
first_order str
dtype: object
The params argument of pandas.read_sql_query passes values to the driver, so the placeholder rule still holds. Look at the last dtype, though: first_order came back as text, because that is how SQLite stored it. Add parse_dates=["first_order"] to the call and the column comes back as datetime64. The round trip from Timestamp to text and back is exactly the kind of conversion objective 2.4.5 has in mind.
The Chen Wei order lands in "Unmapped" because CN is not in the lookup table. With an INNER JOIN that paid order would vanish from the revenue total without any warning, which is a classic scenario question.
Is there a short video on preparing for PCAD?
This three-minute video from the EduSum channel gives preparation tips for the PCAD exam. It is about study habits rather than code, so it pairs well with the hands-on script above.
What should you practise after this script?
Run the script, then break it on purpose. Add a row whose paid value is maybe and see what map() produces; it gives NaN, and the column is no longer boolean. Then write a validation check for it. That loop of breaking the data and then catching the break is a fast way to learn Blocks 1 and 2.
To track the rest of the syllabus objective by objective, this PCAD-31-02 syllabus breakdown with exam details lists every block and topic on one page.
For Blocks 3 to 5, write small scripts of the same kind: a bootstrap of the mean with NumPy, a linear and a logistic regression with a train and test split, and one Matplotlib chart with labelled axes, a title and a legend.
When you want to see how the scenario items are phrased, work through these PCAD sample questions with answers and note which block each one belongs to.
Frequently asked questions
1. How many questions are on the PCAD-31-02 exam, and what score do I need to pass?
The exam has 48 single-select and multiple-select items, and you have 60 minutes plus the NDA screen. You pass with a cumulative score of at least 75%.
2. How much does the PCAD exam cost?
The Python Institute lists the exam from $195, the exam with a retake from $225, the exam with a practice test from $215 and all three from $245. The practice test alone is $49.
3. Is PCAD-31-01 still available?
No. PCAD-31-01 was retired on July 14, 2025, and PCAD-31-02 has been the active version since July 15, 2025. Use the PCAD-31-02 syllabus when you study.
4. Do I need PCAP or PCED before taking PCAD?
There are no formal prerequisites. The Python Institute recommends PCAP and PCED, or equivalent knowledge, plus field-specific skills.
5. How long is the PCAD certification valid, and when can I retake a failed exam?
The certification is valid for 6 years. After a failed attempt you must wait 15 days before you can take the exam again.
Written by the team behind EduSum, a practice-test site for IT and cybersecurity certifications. We are not affiliated with the OpenEDG Python Institute.
Top comments (0)