Grouping and Aggregating Pharmaceutical Data with groupby()

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

Pharmaceutical data analysis very often boils down to summarising numbers across categories: the average onset time for each severity grade of an adverse reaction, the number of events attributed to each causality category, or the peak concentration reached during a pharmacokinetic study. Pandas handles this entire class of problem through the split-apply-combine approach, built around the groupby() method. This article explains how grouping works, how to compute one or several summary statistics per group, and how to bring several of these techniques together into a short end-to-end analysis workflow.

The Split-Apply-Combine Idea

The split-apply-combine strategy, formally described by statistician Hadley Wickham in 2011 as a general approach to data manipulation, breaks a summarisation task into three conceptual stages: the dataset is first split into groups according to one or more categorical variables; an aggregation or other operation is then applied separately to each group; and the resulting per-group summaries are finally combined back into a single new DataFrame. In Pandas, this entire pattern is implemented through the groupby() method.

It is worth emphasising that calling groupby() on its own does not calculate anything. It simply returns a DataFrameGroupBy object that records how the rows should be divided. The actual calculation only happens once you chain an aggregation method — such as .mean(), .count(), or .agg() — onto that object.

Grouping and Computing a Single Statistic

The simplest use of groupby() selects one column to summarise and applies one aggregation function. The following example groups an adverse drug reaction (ADR) dataset by Severity and computes the mean onset time within each severity category.

ADR_data = pd.read_csv("ADR.csv")

mean_onset_by_severity = (
    ADR_data
    .groupby("Severity")["Onset_Time_h"]
    .mean()
    .reset_index()
    .rename(columns={"Onset_Time_h": "Mean_Onset_Time_h"})
)
print(mean_onset_by_severity)

Output:

  Severity  Mean_Onset_Time_h
0     Mild                7.25
1 Moderate                2.75
2   Severe                0.50

Reading this table: the Mild group averages 7.25 hours to onset (driven by a rash appearing at 12 hours and nausea at 2.5 hours); the Moderate group averages 2.75 hours; and the single Severe event (abdominal pain) had the fastest onset at just 0.5 hours. In this small sample, the most severe reaction also occurred earliest — exactly the kind of pattern a grouped summary is designed to surface quickly from raw pharmacovigilance data. Note the use of .reset_index(), which converts the grouping column from a row index back into an ordinary column, and .rename(), which gives the result column a clearer name than the generic original.

Counting Occurrences per Group

Besides averages, a very common requirement is simply counting how many records fall into each category. Pandas offers two routes to the same answer.

value_counts() for a Quick Tally

ADR_relation_count = ADR_data["Relation_to_Drug"].value_counts()
print(ADR_relation_count)

Output: Possible = 4, Probable = 3, Unlikely = 3. value_counts() is the fastest way to answer “how many of each category are there?” directly on a single column.

groupby() with count() for a DataFrame Result

event_count = (
    ADR_data
    .groupby("Relation_to_Drug")["Adverse_Event"]
    .count()
    .reset_index()
    .rename(columns={"Adverse_Event": "Event_Count"})
)
print(event_count)

This produces the same counts as value_counts(), but as a proper DataFrame with the grouping variable preserved as a column — useful when the result needs to be merged with other tables or plotted directly.

Aggregating Multiple Statistics with agg()

Often a single statistic is not enough — you may need the count, mean, minimum, and maximum for the same grouping all at once. The agg() method allows several aggregation functions to be applied in a single call, and lets you assign clear custom names to each resulting column.

onset_stats = (
    ADR_data
    .groupby("Severity")["Onset_Time_h"]
    .agg(
        Count="count",
        Mean_h="mean",
        Min_h="min",
        Max_h="max"
    )
    .reset_index()
)
print(onset_stats)

Output:

  Severity  Count   Mean_h  Min_h  Max_h
0     Mild      2  7.250000    2.5   12.0
1 Moderate      2  2.750000    1.5    4.0
2   Severe      1  0.500000    0.5    0.5

For the Severe group, which contains only a single record, the minimum and maximum are naturally identical to the mean. Also note that Pandas excludes rows with a missing Severity value from this grouped output entirely by default — NaN is not treated as its own group, which means silently-missing categorical data can disappear from a summary unless it is first imputed (for example, with fillna("Not Recorded") as covered in the data-cleaning stage of this unit).

Grouping by Multiple Columns

The groupby() method also accepts a list of column names, allowing you to examine combinations of two or more categorical variables at once — useful whenever the relationship between two factors, not just one, is of interest.

severity_outcome = (
    ADR_data
    .groupby(["Severity", "Outcome"])["SubjectID"]
    .count()
    .reset_index()
    .rename(columns={"SubjectID": "Subject_Count"})
)
print(severity_outcome)

Output:

  Severity  Outcome  Subject_Count
0     Mild Resolved              2
1 Moderate Resolved              2
2   Severe  Ongoing              1

This shows that every mild and moderate adverse event in the sample dataset had resolved by the time of reporting, while the single severe event (abdominal pain, subject S005) was still ongoing — a two-variable cross-tabulation that a single-column groupby() could not have revealed on its own.

Putting It All Together: A Mini Pharmacovigilance Workflow

The techniques covered across this unit — reading, inspecting, cleaning, filtering, and grouping — combine naturally into a short end-to-end analysis. The following workflow filters ADR records to only drug-related events, aggregates them by severity, and separately identifies the peak pharmacokinetic concentration and its time of occurrence (T_max).

pk_data = pd.read_csv("pk_study_data.csv")
ADR_data = pd.read_csv("ADR.csv")

# Clean: convert BLQ to NaN, fill missing severity
pk_data["Concentration_ugmL"] = pd.to_numeric(
    pk_data["Concentration_ugmL"], errors="coerce"
)
ADR_data["Severity"] = ADR_data["Severity"].fillna("Not Recorded")

# Filter: keep only Probable or Possible drug-related events
drug_related = ADR_data[
    ADR_data["Relation_to_Drug"].isin(["Probable", "Possible"])
]

# Aggregate: summarise by severity
summary = (
    drug_related
    .groupby("Severity")
    .agg(
        Event_Count=("SubjectID", "count"),
        Mean_Onset_h=("Onset_Time_h", "mean"),
        Ongoing=("Outcome", lambda x: (x == "Ongoing").sum())
    )
    .reset_index()
    .sort_values("Mean_Onset_h")
)
print(summary)

# Identify peak PK concentration (T_max)
peak_row = pk_data.loc[pk_data["Concentration_ugmL"].idxmax()]
print(
    f"Peak concentration of {peak_row['Concentration_ugmL']} µg/mL at"
    f" {peak_row['Time_h']} hours (T_max)"
)

The agg() call here also demonstrates named-tuple aggregation syntax — Event_Count=("SubjectID", "count") — plus a custom lambda function that counts how many records in each group have an “Ongoing” outcome, showing that agg() is not limited to Pandas’ built-in statistics. The final step uses idxmax() to find the row index of the highest concentration value, then .loc[] to retrieve the full row — a standard pattern for locating T_max in any pharmacokinetic dataset. In the sample data, the peak concentration is 7.85 µg/mL, occurring at 1.0 hour.

Frequently Asked Questions

What is the actual difference between value_counts() and groupby().count()?

value_counts() operates directly on a single Series and returns a sorted count of each unique value — quick and simple. groupby().count() works on a DataFrame and is more flexible: it preserves the grouping column as a proper DataFrame column (rather than an index), and generalises naturally to multi-column grouping and to computing several different statistics at once.

Why did my severity group disappear after groupby(), even though I could see it in the raw data?

This almost always means that group had a missing (NaN) value in the grouping column. Pandas excludes NaN groups by default. Fix it by imputing the missing category first, for example with df["Severity"] = df["Severity"].fillna("Not Recorded"), before calling groupby().

Can I use agg() with a function I wrote myself, not just built-in ones like mean() or count()?

Yes. agg() accepts any function, including a lambda, as long as it takes a Series and returns a single value. This is exactly how the Ongoing=("Outcome", lambda x: (x == "Ongoing").sum()) example above counts a custom condition within each group.

Summary

The split-apply-combine pattern, implemented in Pandas through groupby(), is the standard way to summarise pharmaceutical data across categories such as severity grade, causality assessment, or treatment arm. A single statistic can be computed by chaining a method like .mean() after groupby(); multiple statistics at once require .agg() with named aggregations; and grouping by a list of columns reveals relationships between two or more categorical variables simultaneously. Remember that groupby() silently drops NaN groups, so missing categorical data should be imputed beforehand. These grouping tools, combined with the filtering and cleaning techniques from earlier in this unit, form a complete, reusable workflow for pharmacovigilance and pharmacokinetic data analysis.

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 Official Documentation, pandas.pydata.org, especially the “Group by: split-apply-combine” user guide
• Wickham, H. (2011). The Split-Apply-Combine Strategy for Data Analysis. Journal of Statistical Software, 40(1).