Course Code: BP101T
Unit IV Overview
Course: BP101T.
Unit IV: Data Handling with Pandas.
Pandas organises tabular data into labelled structures and provides tools for importing, inspecting, cleaning, selecting and summarising records. Pharmaceutical applications include pharmacokinetic (PK) study tables and adverse drug reaction (ADR) reports.
Topics Covered – Complete Syllabus Coverage
| Required topic | Coverage |
|---|---|
| Introduction to Pandas | Sections 4.1-4.2 |
| Series and DataFrame | Sections 4.2-4.4 |
| CSV and Excel: PK and ADR examples | Sections 4.5-4.7 |
| head, tail, info and describe | Sections 4.8-4.9 |
| Cleaning and missing values | Sections 4.10-4.13 |
| Conditional filtering and selection | Sections 4.15-4.16 |
| Grouping and aggregation | Sections 4.17-4.18 |
Learning outcomes
Build labelled tables; import CSV and Excel files with appropriate types; inspect structure and missingness; apply justified cleaning; select observations; group records and calculate interpretable summaries.
Scientific setting
All datasets here are fictitious and created for teaching. PK concentration values illustrate data handling rather than fitting or validating a drug model. ADR report summaries illustrate counts and proportions; they do not establish that a drug caused an event.
- A reported adverse event is not automatically a confirmed ADR.
- Spontaneous reports generally lack a complete exposed-population denominator. Report counts cannot be interpreted as incidence rates.
- Preserve identifiers, units, missingness and the observational unit before making summaries.
Prerequisites
Revise lists, dictionaries, CSV structure and NumPy arrays from Unit III. Pandas builds on those ideas but adds row and column labels.
4.1 Pandas purpose and environment setup
Pandas is a Python library for labelled data analysis. It is particularly useful for rectangular tables in which each row is an observation and each column is a variable. It is usually imported with the alias pd.
Official installation guides: Install Pandas and Install openpyxl.
Install – step by step
- Step 1: select the project’s Python interpreter or notebook kernel.
- Step 2: open its terminal and run python -m pip install pandas openpyxl. openpyxl supports common .xlsx operations.
- Step 3: restart a running notebook kernel if the newly installed package is not detected.
- Step 4: run import pandas as pd and print(pd.version) to verify the environment.
Output
4.666666666666667| Tool | Primary strength | Typical unit example |
|---|---|---|
| Python list | Simple general collection | Sample IDs |
| NumPy ndarray | Homogeneous numerical arrays | Concentration arithmetic |
| Pandas DataFrame | Named tabular fields and labels | PK or ADR records |
Pandas is not a replacement for understanding the dataset. It can read a file with an incorrect schema and still create a DataFrame. Check field names, types, units and row meaning explicitly.
Create an analysis folder
- Use separate folders or filenames for raw input and derived outputs.
- Save scripts beside an explanation of the schema and the cleaning rules.
- Use synthetic or appropriately de-identified healthcare data for classroom work.
Memory trick
Pandas puts labels around tabular values; labels help access data but do not guarantee scientific meaning.
Diagram labels: Import a table; inspect its schema and records; clean using documented rules; select analysis records; group and summarise; export reproducible results.
Diagram explanation: Each stage depends on the checks before it. A summary is interpretable only when the row meaning, cleaning decisions and denominator are known.
4.2 Series: a labelled one-dimensional structure
A Series is one-dimensional data with an index. Its values may represent one measurement variable; its index labels identify the observations. The name attribute can describe the variable.
import pandas as pd s = pd.Series( [5.0, 6.0, 3.0], index=[“A01”, “A02”, “A03″], name=”concentration_mg_ml”, ) print(s.loc[“A02”]) print(s.iloc[0]) print(s.name)Output
6.0 5.0 concentration_mg_mlloc selects by label; iloc selects by integer position. A label may look numeric and still mean a label rather than a position. Prefer explicit loc and iloc to avoid ambiguity.
| Property | Meaning |
|---|---|
| index | Observation labels |
| name | Variable description |
| dtype | Value representation |
| size | Number of values |
| isna() | Missing-value mask |
Series from a dictionary
import pandas as pd s = pd.Series({“A01”: 5.0, “A02”: 6.0}) print(s.index.tolist()) print(s.tolist())Output
[‘A01’, ‘A02’] [5.0, 6.0]Label alignment
import pandas as pd a = pd.Series([1, 2], index=[“A”, “B”]) b = pd.Series([10, 20], index=[“B”, “A”]) print((a + b).tolist())Output
[21, 12]Arithmetic aligns matching labels. A receives 1 + 20; B receives 2 + 10. It does not blindly combine first positions. Unmatched labels may produce missing results, so inspect alignment before interpreting a calculated Series.
Exam point
Series has one value axis plus labels. DataFrame has rows and columns.
4.3 DataFrame: rows, columns and observational units
A DataFrame is a two-dimensional labelled table. Each column is a Series, and columns may use different dtypes. The row index identifies rows but is not automatically a validated unique sample identifier.
import pandas as pd df = pd.DataFrame( { “sample_id”: [“A01”, “A02”, “A03”], “mass_mg”: [250.0, 180.0, 120.0], “volume_ml”: [50.0, 30.0, 40.0], } ) print(df.shape) print(df.columns.tolist()) print(df[“mass_mg”].tolist())Output
(3, 3) [‘sample_id’, ‘mass_mg’, ‘volume_ml’] [250.0, 180.0, 120.0]| Selection | Result |
|---|---|
| df[“mass_mg”] | Series: one column |
| df[[“mass_mg”]] | DataFrame: one-column table |
| df.shape | (rows, columns) |
| df.columns | Column labels |
| df.index | Row labels |
Double brackets supply a list of column names. The difference between a Series and a one-column DataFrame matters when a later function expects a particular structure.
One row must have a clear meaning
- Laboratory table: one row per sample or replicate.
- PK table: one row per participant-time observation.
- ADR table: one row per report, drug-event combination or event, depending on its schema.
A table with six rows is not necessarily six unique people. Repeated observations must retain a participant identifier and a time field so that within-person structure can be recognised.
Diagram labels: A DataFrame has two labelled axes: row labels identify observation positions or IDs, column labels name variables, and each column is a Series with a dtype.
Diagram explanation: The figure describes structure. Uniqueness and the meaning of an observation must be established separately from the DataFrame’s labels.
4.4 Constructing tables and managing the index
A dictionary of equal-length column lists is a common DataFrame constructor. A list of row dictionaries is another convenient form. Missing keys can create missing entries, which should be inspected rather than silently accepted.
import pandas as pd rows = [ {“id”: “A01”, “value”: 5.0}, {“id”: “A02”, “value”: 6.0}, ] df = pd.DataFrame(rows) indexed = df.set_index(“id”) print(indexed.loc[“A02”, “value”]) print(indexed.index.is_unique)Output
6.0 Trueset_index() normally returns a new DataFrame unless an in-place option is used. Using an identifier as an index improves named access but does not make duplicate identifiers disappear.
Duplicate labels
import pandas as pd df = pd.DataFrame( {“id”: [“A”, “A”], “value”: [1, 2]} ) print(df[“id”].is_unique) print(df[“id”].duplicated().tolist())Output
False [False, True]The default duplicated() marks later repetitions, not the first occurrence. Use keep=False to flag every member of a duplicate group. A repeated participant ID may be expected in a longitudinal table; a repeated sample ID may require investigation.
Resetting the index
import pandas as pd df = pd.DataFrame( {“value”: [5, 6]}, index=[“A01”, “A02″] ) reset = df.reset_index(names=”sample_id”) print(reset.columns.tolist())Output
[‘sample_id’, ‘value’]reset_index() can move labels back into a normal column. reset_index(drop=True) discards the old index instead; do not discard meaningful identifiers inadvertently.
Memory trick
An index is an access label; an identifier is a data meaning. They may coincide, but they are not inherently the same thing.
4.5 Reading CSV with an explicit schema
pd.read_csv() imports a text table into a DataFrame. Header names, delimiter, encoding, type rules and missing-value markers affect the result. Specify identifier columns as text when leading zeros must be retained.
import pandas as pd, io text = ( “sample_id,time_h,concentration_mg_l\n” “001,0,0\n” “001,1,4.2\n” ) df = pd.read_csv( io.StringIO(text), dtype={“sample_id”: “string”}, ) print(df[“sample_id”].tolist()) print(df[“time_h”].tolist())Output
[‘001’, ‘001’] [0, 1]Import a file – step by step
- Step 1: place pk_data.csv in the project’s raw-data folder.
- Step 2: confirm the header names, units, delimiter and row meaning.
- Step 3: call pd.read_csv() with the identifier dtype and any declared missing-value markers.
- Step 4: inspect shape, head(), dtypes and missing counts.
- Step 5: compare a few imported records with the original file.
| Argument | Purpose |
|---|---|
| sep | Choose delimiter |
| dtype | Control representation |
| na_values | Declare additional missing markers |
| usecols | Read selected fields |
| encoding | Interpret text bytes |
| keep_default_na | Control default missing-marker list |
Default missing-marker recognition can turn strings such as NA into missing values. If NA is a valid category code, use an explicit missingness policy, for example keep_default_na=False with suitable declared markers.
Common error
Automatic numeric conversion can remove leading zeros from IDs. Keep identifier dtype explicit.
4.6 Reading Excel workbooks and checking sheets
pd.read_excel() reads spreadsheet data. An Excel workbook can contain multiple sheets, titles above the table and display formatting that differs from stored values. Choose the intended sheet and header row.
Excel setup
- Step 1: install the required engine for the file format; openpyxl is commonly used for .xlsx files.
- Step 2: inspect the workbook’s sheet names and locate the rectangular data table.
- Step 3: select the sheet_name and header or skiprows parameters as required.
- Step 4: preserve ID types and inspect imported dtypes and missingness.
| Parameter | Meaning |
|---|---|
| sheet_name=”Reports” | Read a named worksheet |
| sheet_name=0 | Read first worksheet |
| sheet_name=None | Return a dictionary of worksheets |
| header=0 | Use first table row for column labels |
| skiprows | Skip introductory rows |
| usecols | Select required columns |
Reproducible miniature workbook
import pandas as pd from pathlib import Path path = Path(“teaching_example.xlsx”) source = pd.DataFrame( {“id”: [“A01”, “A02”], “value”: [5, 6]} ) source.to_excel( path, index=False, sheet_name=”Samples” ) loaded = pd.read_excel( path, sheet_name=”Samples” ) print(loaded.shape) print(loaded[“value”].tolist()) path.unlink()Output
(2, 2) [5, 6]The example writes and reads a small workbook using the same declared sheet. It demonstrates the mechanism, not validation of an external workbook.
Scientific caution
Spreadsheet colours, merged titles and number formats are not a substitute for an explicit machine-readable schema.
4.7 PK and ADR dataset schemas
PK and ADR tables differ in observational unit and interpretation. Their column types must reflect those differences before filtering or aggregation.
PK teaching schema
| Field | Meaning | Typical representation |
|---|---|---|
| subject_id | Repeated-measure identifier | Text |
| route | Oral / IV teaching group | Category text |
| time_h | Elapsed time after reference event | Numeric, hours |
| concentration_mg_l | Measured concentration | Numeric, mg/L |
| status | Review or measurement state | Category text |
The reference event for time must be stated. A below-quantification result is not automatically zero or simply missing; retain its qualifier and use a study-appropriate analytical policy.
ADR teaching schema
| Field | Meaning |
|---|---|
| report_id | Unique report identifier |
| drug | Reported suspect or associated product |
| event | Reported event term |
| serious | Regulatory seriousness flag |
| age_years | Age if recorded |
| report_date | Date with a documented convention |
Seriousness is not the same as symptom severity. A severe symptom is not necessarily classified as serious under regulatory criteria, and a serious outcome can occur without a “severe” symptom label. The table must not collapse those concepts.
Row counts and report counts
- A report with two drugs and two events may expand into several rows.
- Counting rows after expansion can overstate unique reports.
- Use report_id.nunique() when the analytical question concerns distinct reports.
- Record counts, report proportions and incidence rates require different denominators.
Core interpretation
A reported association is a signal for assessment, not proof of causation. Spontaneous-report counts alone cannot estimate incidence.
4.8 Inspecting head, tail, shape and dtypes
Initial inspection checks what was imported before making changes. head(n) shows the first n rows; tail(n) shows the last n. Both default to five rows. They show samples of the table, not complete validation.
import pandas as pd df = pd.DataFrame( { “id”: [“A01”, “A02”, “A03”], “value”: [5.0, 6.0, 3.0], } ) print(df.head(1).to_string(index=False)) print(df.tail(1).to_string(index=False)) print(df.shape)Output
id value A01 5.0 id value A03 3.0 (3, 2)Inspect field representation
import pandas as pd df = pd.DataFrame( { “time_h”: [0, 1, 2], “value”: [0.0, 4.2, 3.1], } ) print(df[“time_h”].dtype.kind) print(df[“value”].dtype.kind)Output
i fdtype.kind i indicates a signed integer representation; f indicates floating-point. An apparently numeric column may be stored as text because one cell contains a marker or malformed value.
| Inspection | Question answered |
|---|---|
| head() | Do the first records look as expected? |
| tail() | Does the final part import correctly? |
| shape | How many rows and columns? |
| columns | Are names correct? |
| dtypes | Are types suitable? |
| index | What labels are being used? |
Hidden problems to consider
- A title row may have become a header.
- An unnamed first column may be a previously exported index.
- Whitespace or different case may change column names.
- An entire concentration column may be text because of one non-numeric entry.
Memory trick
Look at beginning, end, dimensions and types before calculating.
4.9 info and describe: structure versus summary
df.info() prints a structural overview including columns, non-null counts and dtypes. It is useful for spotting missing entries and unexpected representations. It returns None rather than a summary DataFrame.
import pandas as pd, io df = pd.DataFrame( {“id”: [“A”, “B”], “value”: [5.0, None]} ) buffer = io.StringIO() df.info(buf=buffer) print(“Data columns” in buffer.getvalue()) print(df[“value”].notna().sum())Output
True 1The exact info text depends on version and settings. print(df.info()) prints the structural overview and then None, which is usually unnecessary.
Descriptive statistics
import pandas as pd s = pd.Series([5.0, 6.0, 3.0]) stats = s.describe() print(stats[“count”]) print(f”{stats[‘mean’]:.2f}”) print(stats[“min”], stats[“max”])Output
3.0 4.67 3.0 6.0| Numeric output | Meaning |
|---|---|
| count | Non-missing observations |
| mean | Arithmetic average |
| std | Sample standard deviation by default |
| min / max | Observed extremes |
| 25%, 50%, 75% | Quartiles; 50% is the median |
describe() normally summarises numeric columns in a mixed DataFrame. include=”all” adds summaries suitable for other types. Categorical summaries include counts of unique values and the most frequent value.
Limits of a summary
- A mean alone cannot show whether the distribution is skewed or has unusual observations.
- A count is a non-missing count for that variable, not necessarily the total row count.
- A summary of repeated measurements does not automatically describe independent participants.
- Quartile calculation conventions can differ between tools; use consistent methods when comparing results.
Exam distinction
info explains structure. describe gives statistical summaries. Neither establishes clinical validity.
4.10 Cleaning column names, text and duplicates
Cleaning should apply documented, reversible rules. Preserve the raw DataFrame and create a working copy. A change should address a known inconsistency rather than make data merely look tidy.
import pandas as pd raw = pd.DataFrame( { ” Sample_ID “: [” a01 “, “A02″], ” Value “: [5.0, 6.0], } ) df = raw.copy() df.columns = ( df.columns.str.strip().str.lower() ) df[“sample_id”] = ( df[“sample_id”].str.strip().str.upper() ) print(df.columns.tolist()) print(df[“sample_id”].tolist())Output
[‘sample_id’, ‘value’] [‘A01’, ‘A02’]Here the convention is deliberately defined: trim column labels, use lowercase field names and uppercase sample identifiers. Do not apply such a rule blindly when case or spacing is meaningful.
Duplicates
import pandas as pd df = pd.DataFrame( { “id”: [“A”, “A”, “B”], “value”: [5, 5, 6], } ) print(df.duplicated().tolist()) print(df.drop_duplicates().shape)Output
[False, True, False] (2, 2)drop_duplicates() compares all columns by default. subset=[“id”] changes the comparison to the ID alone. Two measurements with the same participant ID but different times are not necessarily duplicates.
| Cleaning action | Evidence needed |
|---|---|
| Trim text | Whitespace is accidental |
| Change case | Case-insensitive schema |
| Drop repeated row | Confirmed accidental duplicate |
| Remove impossible value | Variable-specific validity rule |
| Change category spelling | Documented mapping |
Record the changes
- Count duplicate rows before and after the action.
- Keep a list of replacements and their rationale.
- Check that cleaning has not merged distinct identifiers.
Memory trick
Detect, explain, change, verify. Keep the original.
4.11 Missing values: detection and meaning
Pandas uses missing markers appropriate to the dtype, including NaN, NaT and pd.NA. Use isna() or notna() to detect missing values. Equality to NaN is not a reliable missingness test.
import pandas as pd s = pd.Series([5.0, None, 3.0]) print(s.isna().tolist()) print(s.notna().sum()) print(s.mean())Output
[False, True, False] 2 4.0The mean uses two recorded observations, 5 and 3. It does not divide by all three rows. Many Pandas reductions omit missing values by default; state the available count alongside a mean.
Missing counts and proportions
import pandas as pd df = pd.DataFrame( { “value”: [5.0, None, 3.0], “age”: [20, 21, None], } ) print(df.isna().sum().to_dict())Output
{‘value’: 1, ‘age’: 1}| Marker or value | Interpretation |
|---|---|
| None / NaN / pd.NA | Recognised missingness for suitable type |
| Empty string | May remain ordinary text |
| 0 | Observed numeric zero |
| Below quantification | Qualified result; not automatically zero |
| Not applicable | May differ from not recorded |
Why a field is missing
- A measurement may not have been taken.
- A value may have failed import conversion.
- The source may use a missing code that was not declared.
- A field may be not applicable rather than unknown.
These situations can require different analysis policies. Keep a reason field when available. Replacing every missing measurement with zero hides these distinctions and changes estimates.
Exam point
Missingness is information about data availability, not a measurement result.
4.12 Dropping, filling and documenting missing data
dropna() removes rows or columns meeting a missingness rule. fillna() replaces missing entries. Neither action is universally correct; choose a policy justified by the variable, study design and analysis.
import pandas as pd df = pd.DataFrame( { “id”: [“A”, “B”, “C”], “value”: [5.0, None, 3.0], “note”: [None, “repeat”, None], } ) kept = df.dropna(subset=[“value”]) print(kept[“id”].tolist()) print( df[“note”] .fillna(“Not recorded”) .tolist() )Output
[‘A’, ‘C’] [‘Not recorded’, ‘repeat’, ‘Not recorded’]Only missing value entries cause row removal in this example. A missing note does not exclude a valid concentration. Filling descriptive text with a label differs from fabricating a numeric measurement.
| Method | Effect | Potential concern |
|---|---|---|
| dropna(subset=…) | Remove rows missing required fields | Changes included population |
| fillna(text label) | Make absent text explicit | Label is not a source observation |
| fillna(mean) | Replace numeric missing values | Changes variability; needs justification |
| ffill() | Use preceding available value | Requires meaningful ordering |
| Interpolation | Estimate between observations | Requires an appropriate model |
Forward filling needs an ordered context
Do not carry a concentration forward across different participants or arbitrary record order. Even within one participant, forward fill can distort a concentration-time profile and is not a default PK rule.
Audit procedure
- Step 1: count missing values in every required variable.
- Step 2: state which variables are necessary for the planned analysis.
- Step 3: apply the approved policy and preserve the source.
- Step 4: report included and excluded records and the reason.
- Step 5: consider whether conclusions depend strongly on that policy.
Memory trick
A replacement is a method decision, not a recovered original measurement.
4.13 Numeric conversion, dates and category mapping
pd.to_numeric() converts suitable text to numeric values. errors=”coerce” turns unconvertible entries into missing values. This is useful for detection only when the newly created missingness is reviewed and reported.
import pandas as pd raw = pd.Series([“5.0”, “bad”, “3.0”]) converted = pd.to_numeric( raw, errors=”coerce” ) print(converted.tolist()) print(converted.isna().tolist())Output
[5.0, nan, 3.0] [False, True, False]The original text “bad” is not a valid concentration. Keep the raw field or a conversion-failure flag so the error can be traced.
Explicit date formats
import pandas as pd raw = pd.Series([“2026-01-05”, “invalid”]) dates = pd.to_datetime( raw, format=”%Y-%m-%d”, errors=”coerce” ) print(dates.isna().tolist())Output
[False, True]An ambiguous date such as 05/01/2026 can mean different dates under different conventions. An explicit documented format avoids guessing. Timezone and reference event also matter for elapsed-time analysis.
Category mapping
import pandas as pd s = pd.Series([“yes”, “no”, “unknown”]) flag = s.map({“yes”: True, “no”: False}) print(flag.isna().tolist())Output
[False, False, True]Unknown values become missing after an incomplete mapping. Review unmapped codes; do not automatically convert unknown to False.
| Conversion | Before applying |
|---|---|
| Numeric | Check units and special markers |
| Date | State ordering and format |
| Boolean category | Map every allowed code |
| Identifier | Preserve text and leading zeros |
Common error
astype(bool) on non-empty strings makes both “yes” and “no” truthy. Use an explicit mapping.
4.14 Derived columns and validity flags
A derived column is calculated from recorded fields. Its name should identify its quantity and unit. A separate validity flag helps make exclusions visible rather than silently dropping problematic observations.
import pandas as pd, numpy as np df = pd.DataFrame( { “mass_mg”: [250.0, 180.0, -1.0], “volume_ml”: [50.0, 30.0, 20.0], } ) valid = ( np.isfinite(df[“mass_mg”]) & np.isfinite(df[“volume_ml”]) & (df[“mass_mg”] >= 0) & (df[“volume_ml”] > 0) ) df[“valid”] = valid df[“concentration_mg_ml”] = np.nan df.loc[valid, “concentration_mg_ml”] = ( df.loc[valid, “mass_mg”] / df.loc[valid, “volume_ml”] ) print(df[“valid”].tolist()) print(df[“concentration_mg_ml”].tolist())Output
[True, True, False] [5.0, 6.0, nan]Assignment occurs only for valid rows. The invalid row remains in the table with a visible flag and missing derived result. This allows a reviewer to see what was excluded.
Boolean combinations
- Use & for elementwise AND, | for elementwise OR and ~ for Boolean NOT.
- Put each comparison inside parentheses because operator precedence differs from ordinary prose.
- Do not use Python and/or directly between whole Series; their overall truth value is ambiguous.
| Expression | Purpose |
|---|---|
| (a > 0) & (b > 0) | Both row conditions |
| (route == “Oral”) | (route == “IV”) | Either category |
| ~mask | Invert a Boolean mask |
| df.loc[mask, “result”] = value | Explicit conditional assignment |
Version-aware practice
Use a single .loc assignment instead of chained selection such as df[mask][“result”] = value. Chained assignment is unreliable and becomes especially problematic with Copy-on-Write behaviour.
4.15 Selecting rows and columns with loc and iloc
loc uses row and column labels; iloc uses integer positions. Label-based slicing with loc normally includes both labelled endpoints. Positional slicing with iloc excludes the stop position.
import pandas as pd df = pd.DataFrame( { “time_h”: [0, 1, 2], “value”: [0.0, 4.2, 3.1], }, index=[“A”, “B”, “C”], ) print(df.loc[“B”, “value”]) print(df.iloc[1, 1]) print(df.loc[“A”:”B”].index.tolist()) print(df.iloc[0:2].index.tolist())Output
4.2 4.2 [‘A’, ‘B’] [‘A’, ‘B’]| Selection | Meaning |
|---|---|
| df.loc[“B”, “value”] | Row label B, named column |
| df.iloc[1, 1] | Second row, second column |
| df.loc[:, [“time_h”, “value”]] | All rows, chosen columns |
| df.iloc[:2, :] | First two rows, all columns |
An integer index label is still a label for loc. df.loc[1] asks for label 1, while df.iloc[1] asks for the second position. These may refer to different records.
Selecting a study subset
import pandas as pd df = pd.DataFrame( { “id”: [“A”, “B”, “C”], “value”: [1.0, 5.0, 9.0], } ) selected = df.loc[ df[“value”] >= 5, [“id”, “value”] ] print(selected[“id”].tolist())Output
[‘B’, ‘C’]The mask selects rows; the list selects columns. Keep identifiers in the selected result so that measurements remain traceable.
Memory trick
loc = labels; iloc = integer locations. State both axes when the intended selection could be unclear.
4.16 Conditional filtering, sorting and subsets
A filter applies a Boolean rule to each row. Scientific selection rules should be stated before analysis. A convenient threshold discovered after seeing the results can produce a biased subset.
import pandas as pd df = pd.DataFrame( { “route”: [“Oral”, “IV”, “Oral”], “time_h”: [0.0, 1.0, 2.0], “value”: [0.0, 8.0, 4.0], } ) mask = (df[“route”] == “Oral”) & ( df[“time_h”] > 0 ) print(df.loc[mask, “value”].tolist())Output
[4.0]Membership and missingness filters
import pandas as pd s = pd.Series([“Oral”, “IV”, “Other”, None]) print(s.isin([“Oral”, “IV”]).tolist()) print(s.notna().tolist())Output
[True, True, False, False] [True, True, True, False]isin() is clearer than repeating several equality comparisons. Missing categories are not ordinary observed groups and should be handled explicitly.
Sort related fields together
import pandas as pd df = pd.DataFrame( { “subject_id”: [“S1”, “S1”, “S1”], “time_h”: [2.0, 0.0, 1.0], “concentration”: [3.0, 0.0, 4.0], } ) ordered = df.sort_values( [“subject_id”, “time_h”] ) print(ordered[“time_h”].tolist()) print(ordered[“concentration”].tolist())Output
[0.0, 1.0, 2.0] [0.0, 4.0, 3.0]Sorting the whole table preserves the pairing of time and concentration. Sorting columns independently destroys that relationship. For repeated-measures data, sort within participant before plotting or applying an order-dependent operation.
Filter audit
- Record how many rows satisfied the rule.
- Count unique participants when the question concerns people rather than observations.
- Retain the rule and raw data so the selected population can be reproduced.
Exam point
A filtered row count and a unique-subject count answer different questions.
4.17 GroupBy: split, apply and combine
groupby() groups rows by one or more labels. A summary function is then applied within each group, and the results are combined. This is the split-apply-combine pattern.
import pandas as pd df = pd.DataFrame( { “batch”: [“A”, “A”, “B”, “B”], “value”: [4.0, 6.0, 3.0, 5.0], } ) means = df.groupby(“batch”)[“value”].mean() print(means.to_dict())Output
{‘A’: 5.0, ‘B’: 4.0}Diagram labels: Split rows by batch label; apply a mean within each batch; combine the results into one mean per batch.
Diagram explanation: Batch A has values 4 and 6, giving mean 5. Batch B has 3 and 5, giving mean 4. The summary collapses each group into a result; it does not preserve every original observation.
More than one grouping field
import pandas as pd df = pd.DataFrame( { “route”: [“Oral”, “Oral”, “IV”], “time_h”: [1.0, 2.0, 1.0], “value”: [4.0, 3.0, 8.0], } ) result = df.groupby([“route”, “time_h”])[ “value” ].mean() print(len(result))Output
3Grouping by route and time produces one group per observed combination. This can prepare a concentration-time summary when participants are comparable and the design permits aggregation.
| Method | Summary purpose |
|---|---|
| mean() | Average non-missing values |
| median() | Middle value |
| min() / max() | Extremes |
| sum() | Total |
| count() | Non-missing values in a column |
| size() | Rows in a group |
| nunique() | Distinct observed values |
Denominator check
count and size differ when a measurement is missing. Report the appropriate denominator for the stated question.
4.18 Aggregation, count and size in practice
agg() allows several summary functions or named outputs. A well-designed result includes the number of available observations, so a mean is not presented without its supporting count.
import pandas as pd df = pd.DataFrame( { “batch”: [“A”, “A”, “B”, “B”], “value”: [4.0, None, 3.0, 5.0], } ) summary = df.groupby(“batch”).agg( n_rows=(“value”, “size”), n_observed=(“value”, “count”), mean_value=(“value”, “mean”), ) print(summary.to_string())Output
n_rows n_observed mean_value batch A 2 1 4.0 B 2 2 4.0Both groups contain two rows. Batch A has one observed value; batch B has two. Their means happen to be equal, but their information content differs.
Missing grouping labels
groupby drops missing group labels by default. dropna=False can retain a missing-key group for auditing. Do not assign an unknown drug or batch to a known group simply to avoid a missing category.
import pandas as pd df = pd.DataFrame( {“group”: [“A”, None], “value”: [1, 2]} ) print(df.groupby(“group”).size().sum()) print( df.groupby(“group”, dropna=False) .size() .sum() )Output
1 2Sample standard deviation
A sample SD needs at least two non-missing observations in the usual calculation. A group with one observation can have a mean but no meaningful sample SD. Do not replace that absent SD with zero.
| Summary state | Interpretation |
|---|---|
| n = 0 | No observed mean |
| n = 1 | One value; sample SD unavailable |
| n >= 2 | Sample SD can be calculated |
| Repeated subjects | Independence still needs assessment |
Memory trick
Every mean needs a count; every count needs a defined unit.
4.19 Worked PK data handling
This miniature PK dataset contains two fictitious subjects measured at three times. The aim is to inspect, order and summarise concentration by time, without estimating pharmacokinetic parameters.
import pandas as pd df = pd.DataFrame( { “subject_id”: [“S1”] * 3 + [“S2”] * 3, “time_h”: [ 0.0, 1.0, 2.0, 0.0, 1.0, 2.0, ], “concentration_mg_l”: [ 0.0, 4.0, 3.0, 0.0, 6.0, 5.0, ], } ) ordered = df.sort_values( [“subject_id”, “time_h”] ) summary = ordered.groupby(“time_h”).agg( n=(“concentration_mg_l”, “count”), mean_mg_l=( “concentration_mg_l”, “mean”, ), ) print(summary.to_string())Output
n mean_mg_l time_h 0.0 2 0.0 1.0 2 5.0 2.0 2 4.0Manual check
| Time | Values (mg/L) | Mean (mg/L) |
|---|---|---|
| 0 h | 0, 0 | 0 |
| 1 h | 4, 6 | 5 |
| 2 h | 3, 5 | 4 |
The observed mean peaks at 1 h among these sampled times. This does not prove the exact peak time between samples. The two subjects are repeated across time, so six rows do not represent six independent people.
Stepwise workflow
- Step 1: define subject_id as text and record the time reference and concentration unit.
- Step 2: inspect counts, time order, duplicates and missing observations.
- Step 3: preserve all participant-time pairings during sorting.
- Step 4: summarise only comparable records at common times and report the available count.
- Step 5: export the summary for plotting in Unit V.
Interpretation limit
This arithmetic summary is not a population PK model, a bioequivalence analysis or proof of treatment effect.
4.20 Worked ADR report summaries
The teaching table below has one row per report and one reported drug per row. Under this deliberately simplified schema, counting unique report IDs by drug gives distinct reports.
import pandas as pd df = pd.DataFrame( { “report_id”: [ “R1”, “R2”, “R3”, “R4”, ], “drug”: [“A”, “A”, “B”, “B”], “serious”: [ True, False, False, True, ], } ) summary = df.groupby(“drug”).agg( reports=(“report_id”, “nunique”), serious_reports=(“serious”, “sum”), ) summary[“serious_report_percent”] = ( 100 * summary[“serious_reports”] / summary[“reports”] ) print(summary[“reports”].to_dict()) print( summary[ “serious_report_percent” ].to_dict() )Output
{‘A’: 2, ‘B’: 2} {‘A’: 50.0, ‘B’: 50.0}The result is the percentage of submitted teaching reports marked serious within each drug group. It is not the percentage of treated patients experiencing a serious reaction. The denominator is reports, not exposed people.
Interpretation checks
- Confirm that report_id is unique under this simplified table design.
- If one report expands into multiple event rows, deduplicate at the report level before summing seriousness.
- Unknown seriousness must remain unknown; do not treat it as False.
- Comparing submitted counts across drugs does not establish comparative clinical risk.
| Measure | Denominator | Meaning |
|---|---|---|
| Report count | None: count only | Submitted records |
| Serious-report proportion | Eligible reports | Composition of reports |
| Incidence | Defined exposed population / time | New events in an at-risk population |
Essential limitation
Spontaneous reporting is affected by reporting practices and incomplete information. A submitted report does not establish causation or provide an incidence denominator.
4.21 Export and reproducibility
A reproducible analysis records the raw source, schema, cleaning rules, selections and calculation code. Exported tables should retain identifiers and units while avoiding unintended index columns.
import pandas as pd, io df = pd.DataFrame( { “sample_id”: [“A01”, “A02”], “value_mg_ml”: [5.0, 6.0], } ) buffer = io.StringIO() df.to_csv(buffer, index=False) loaded = pd.read_csv( io.StringIO(buffer.getvalue()) ) print(loaded.columns.tolist()) print(loaded.shape)Output
[‘sample_id’, ‘value_mg_ml’] (2, 2)Export to CSV or Excel
summary.to_csv( “output/pk_summary.csv”, index=False ) summary.to_excel( “output/pk_summary.xlsx”, index=False, sheet_name=”Summary”, )These commands assume summary is an existing DataFrame and the output folder exists. If group labels are stored in the index, reset_index() first or export the index intentionally; otherwise the group labels can be lost.
Stepwise final check
- Step 1: create the output folder and choose a filename different from the raw input.
- Step 2: ensure all necessary group identifiers are columns or intentionally exported index labels.
- Step 3: export and reopen the result.
- Step 4: compare columns, row count and a manually checked summary value.
- Step 5: save the script with the input filename, unit definitions and cleaning log.
Memory trick
Raw source + rules + code + verified output = reproducible table workflow.
Faculty discussion
A cleaned file alone hides how the result was obtained. Ask students to explain one exclusion and its effect on the denominator, then demonstrate that rerunning the script produces the same values.
4.22 Quick revision
| Concept | Recall |
|---|---|
| Series | One-dimensional labelled values |
| DataFrame | Rows and columns; mixed column types |
| read_csv / read_excel | Import with declared schema |
| head / tail | Preview beginning / end |
| info | Structure and non-null counts |
| describe | Variable-appropriate summaries |
| isna / notna | Missingness masks |
| dropna / fillna | Remove / replace under a justified policy |
| loc / iloc | Label / position selection |
| & / | / ~ | Elementwise Boolean operations |
| groupby | Split-apply-combine |
| count / size | Observed values / total rows |
| nunique | Distinct values |
| ADR reports | Counts do not equal incidence |
4.23 Possible examination questions
Possible short questions
- Distinguish Series from DataFrame.
- Why specify an ID dtype during import?
- Compare info() and describe().
- Explain missing-value detection and the danger of zero replacement.
- Compare loc with iloc.
- Distinguish count, size and nunique.
- Explain split-apply-combine.
- Why is a spontaneous-report count not an incidence rate?
Possible long questions
- Develop a CSV/Excel import and inspection workflow for a PK table.
- Explain documented cleaning, duplicates and missing values.
- Write a conditional filter with safe .loc assignment.
- Summarise concentrations by time with counts and means.
- Develop an ADR report summary and explain its denominator limitations.
Practical questions with expected checks
- Import IDs 001 and 002 without losing leading zeros.
- For values 5, missing and 3, report count 2 and mean 4.
- For batch A values 4 and 6, report mean 5 with n = 2.
- Export a grouped table while preserving its group labels.
Exam answer structure
State row meaning, field units and types; show import and inspection; justify cleaning; provide the selection and aggregation code; show a verified result and explain what it does and does not establish.