Reading CSV and Excel Files: PK Study Datasets and ADR Reports

Unit IV · Basics of Python Programming for Pharmaceutical Sciences (BP101T) · As per PCI B.Pharmacy Syllabus, NEP 2020

Real pharmaceutical datasets almost never arrive as neatly typed Python lists — they arrive as exported files from clinical systems, laboratory instruments, or pharmacovigilance databases. This post covers how Pandas reads those files into a DataFrame, using two realistic examples that recur throughout this unit: a pharmacokinetic (PK) study dataset tracking plasma drug concentration over time, and an adverse drug reaction (ADR) dataset summarizing safety events across multiple study subjects.

Why File-Reading Skills Matter in Pharmacy Data Work

In pharmaceutical settings, data is typically obtained from clinical data management systems, laboratory information management systems (LIMS), pharmacovigilance platforms, and regulatory databases. These systems commonly export their data in two standard formats: CSV (Comma-Separated Values) files and Excel (.xlsx) workbooks. Pandas includes built-in functions that import these files directly into a DataFrame, automatically interpreting column headings, inferring data types, and assigning index information along the way — turning a raw export into an analysis-ready table in a single line of code.

The Sample Datasets Used in This Topic

Two example datasets are used throughout this section to demonstrate CSV and Excel reading with Pandas.

1. PK Study Dataset (pk_study_data.csv)

This dataset records a plasma concentration-time profile for a single (fictional) study subject, S001, a 28-year-old female weighing 62.4 kg. Each row corresponds to one blood-sampling time point after dosing, giving the subject ID, elapsed time in hours (Time_h), the measured plasma drug concentration in micrograms per millilitre (Concentration_ugmL), the subject’s sex, weight, and age. At the final sampling time of 12 hours, the concentration has dropped below the assay’s detection threshold and is recorded as "BLQ" (below the limit of quantification) rather than a numeric value — a realistic detail that has real consequences for how Pandas interprets that column, as the next post in this unit explores in depth.

As raw CSV text, this same dataset looks like:

SubjectID,Time_h,Concentration_ugmL,Sex,Weight_kg,Age_years
S001,0.0,0.00,F,62.4,28
S001,0.5,3.21,F,62.4,28
S001,1.0,7.85,F,62.4,28
S001,2.0,6.42,F,62.4,28
S001,4.0,3.10,F,62.4,28
S001,6.0,1.50,F,62.4,28
S001,8.0,0.72,F,62.4,28
S001,12.0,BLQ,F,62.4,28

2. Adverse Drug Reaction Dataset (ADR.csv)

This second dataset summarizes fictional adverse-event reports collected across ten different study subjects (S001 through S010). Its columns record the subject ID, the name of the adverse event experienced (Adverse_Event), its severity grade (Severity), how many hours after dosing the event began (Onset_Time_h), the outcome of the event (Outcome), and the assessed likelihood that the event was caused by the study drug (Relation_to_Drug). Some subjects — S001 and S007 — reported no adverse event at all, shown as "None" in both the Adverse_Event and Severity columns with no onset time recorded. Others reported reactions ranging from mild (nausea, rash) to severe (abdominal pain), with an outcome of either “Resolved” or, in one case, “Ongoing,” and a drug relationship judged Unlikely, Possible, or Probable.

SubjectID,Adverse_Event,Severity,Onset_Time_h,Outcome,Relation_to_Drug
S001,None,None,,Resolved,Unlikely
S002,Nausea,Mild,2.5,Resolved,Probable
S003,Headache,,1.0,Resolved,Possible
S004,Dizziness,Moderate,4.0,Resolved,Probable
S005,Abdominal Pain,Severe,0.5,Ongoing,Possible
S006,Diarrhea,,8.0,Resolved,Possible
S007,None,None,,Resolved,Unlikely
S008,Rash,Mild,12.0,Resolved,Unlikely
S009,Vomiting,Moderate,1.5,Resolved,Probable
S010,Headache,,2.0,Resolved,Possible

As a practice exercise, students are encouraged to build their own similar spreadsheet using either of these two layouts and to work through the file-reading techniques below on it, in order to build genuine familiarity with how CSV and Excel files can be manipulated using Pandas.

Reading CSV Files with pd.read_csv()

The most basic way to load a CSV file into a DataFrame is to call pd.read_csv() with just the filename as its argument:

# Basic usage: read a CSV file into a DataFrame
pk_data = pd.read_csv('pk_study_data.csv')

This simple form only works when the CSV file sits in the same folder as the script being run. If the file lives elsewhere, the full file path must be supplied instead of just the filename. A Windows path and a Linux/Mac path look slightly different:

# On Windows
pk_data = pd.read_csv("C:/Users/YourName/Documents/pk_study_data.csv")

# On Linux/Mac
pk_data = pd.read_csv("/home/yourname/documents/pk_study_data.csv")

Reading with Explicit Options

Rather than relying entirely on default behaviour, pd.read_csv() accepts a wide range of named parameters that let a programmer fine-tune exactly how a file gets parsed:

pk_data = pd.read_csv(
    "pk_study_data.csv",
    sep=",",                              # Delimiter character (default is ',')
    header=0,                             # Row number to use as column names (0 = first row)
    index_col=None,                       # Column to use as row labels (None = default integer index)
    na_values=["NA", ".", "BLQ", "LLOQ"], # Strings to treat as missing (NaN)
    parse_dates=["Collection_Time"],      # Parse this column as a datetime
    encoding="utf-8"                      # Character encoding of the file
)

Four of these parameters deserve special attention in a pharmaceutical context:

Reading Excel Files with pd.read_excel()

Since laboratory and clinical systems frequently export workbooks instead of plain text files, Pandas provides an equally simple function for Excel: pd.read_excel(). It works much like read_csv(), but accepts a few Excel-specific parameters because a single workbook can contain multiple sheets.

# Reading an Excel workbook into a DataFrame
adr_data = pd.read_excel(
    "ADR_reports.xlsx",
    sheet_name="Sheet1",                  # Name or index of the worksheet to read
    header=0,                             # Row to use as column names
    na_values=["NA", ".", "Unknown"],     # Strings to treat as missing
    dtype={"SubjectID": str}              # Force SubjectID to stay text, not become numeric
)
print(adr_data.head())

Reading Excel files requires an additional engine library installed alongside Pandas — typically openpyxl for modern .xlsx files — which can be installed with pip install openpyxl. The sheet_name parameter is the key difference from read_csv(): it can take a sheet’s name (as a string), its position (as an integer, starting from 0), or the value None to read every sheet in the workbook at once into a dictionary of DataFrames — useful when a single regulatory submission file bundles PK data, demographics, and ADR logs into separate tabs of one spreadsheet.

Practicing Online with W3Schools

As an alternative to running Python locally, the examples in this section can also be pasted directly into the W3Schools online Pandas practice environment. That environment always works with a single file named data.csv, which by default contains a small sample dataset with the columns Duration, Pulse, Maxpulse, and Calories. Either the PK dataset or the ADR dataset shown above can be pasted straight into that same data.csv section in place of the default sample data.

Because the W3Schools editor always names the uploaded file data.csv regardless of what it was originally called, any code copied from this post needs its filename argument updated before it will run on that platform:

# Original line for the PK dataset
pk_data = pd.read_csv('pk_study_data.csv')
# On W3Schools, change it to:
pk_data = pd.read_csv('data.csv')

# Original line for the ADR dataset
ADR_data = pd.read_csv('ADR.csv')
# On W3Schools, change it to:
ADR_data = pd.read_csv('data.csv')

Frequently Asked Questions

What happens if I call pd.read_csv() on a file that isn’t in the same folder as my script?

Pandas will raise a FileNotFoundError. The fix is to either move the script into the same folder as the data file, or supply the full absolute path to the file (using forward slashes even on Windows, or a raw string) as the argument to read_csv().

Why does the ADR dataset’s SubjectID column need special handling when reading?

Subject IDs like “S001” already contain a letter, so Pandas correctly reads them as text. But numeric-looking IDs such as “001” are at risk of being auto-converted to the integer 1, silently dropping the leading zeros. Explicitly setting dtype={"SubjectID": str} when reading prevents this.

Can the same na_values list be used for both CSV and Excel files?

Yes. The na_values parameter works identically in both pd.read_csv() and pd.read_excel(), and the same list of censoring codes (such as “BLQ” or “LLOQ”) should be applied consistently across both formats to keep an analysis consistent when data arrives from mixed sources.

Summary

Pandas provides pd.read_csv() and pd.read_excel() as the two primary functions for loading pharmaceutical datasets — such as PK study data and ADR reports — from external files into a DataFrame. Both functions accept a rich set of parameters (sep, header, na_values, parse_dates, dtype, and, for Excel, sheet_name) that let a student control exactly how each column and each missing-value code is interpreted at import time. Recognizing pharmaceutical censoring codes like “BLQ” and “LLOQ” via na_values, and protecting identifier columns from unwanted type conversion via dtype, are the two habits most likely to prevent silent data-quality errors before any real analysis begins.

References

• Pharmacy Council of India (PCI) — B.Pharm Regulations, NEP 2020, BP101T Syllabus
• Python Software Foundation — Official Python Documentation, docs.python.org
• Pandas Development Team — pandas.read_csv and pandas.read_excel API Reference, pandas.pydata.org
• W3Schools — Pandas Tutorial and Online Practice Environment, w3schools.com