Disclaimer:

1. The findings from this project are for educational purposes only and should not be used for clinical decision-making.
2. Analysis scripts are provided at the bottom of the page and are written for the purpose of learning and should not be used for production without further testing

Key Achievements 🏆​

• Processed 20+ years of FAERS XML data (2004–2025) across 84 quarterly releases
• Detected 111,918 drug-reaction signals; identified GLP-1 class-wide GI safety profile
• Built multi-dimensional interactive visualizations filterable by gender, age, and indication

Tools & Skills

Layer	Tools
1. ETL	Python (xml.tree)
2. Data Cleaning	PostgreSQL
3. Analysis	R (Tidyverse, DBI, RPostgres)
4. Visualization	ggplot, Plotly
5. Methods	PRR, ROR, Chi-square, IC

สิ่งที่จะพบในโปรเจกต์

1. What is pharmacovigilance?

2. Introducing to the US crucial database for Pharmacovigilance: FAERS

3. FAERS data analysis methodology.

• ETL (Extract, Transform, Load) using Python
• Data cleaning using PostgreSQL
• Descriptive profiling using R (tidyverse, DBI, RPostgres)
• Signal detection using R

4. Findings & Conclusion

• Discriptive profiling findings
• Statistical signal detection findings using R (ggplot2+plotly)
  • Heatmaps to explore drug-reaction matrices
  • Interactive volcano plots of the Information Component lower bound (IC025) against the Log10 Chi-square statistic
  • Interactive GLP-1 Safety Signals forest plot
  • Interactive GLP-1 Safety Signals by sub-population

5. Discussions

• Limitations and future work

6. Full analysis scripts on GitHub

Introduction

What is pharmacovigilance?

Pharmacovigilance is the science of monitoring the safety of medicines after they reach the market. As part of this, the U.S. Food and Drug Administration (FDA) maintains the Adverse Event Reporting System (FAERS), a massive database tracking reported drug side effects.

What is FAERS database?

The FDA Adverse Event Reporting System (FAERS) is a publicly available, national database containing millions of reports on adverse events (side effects) and medication errors.

These reports are submitted voluntarily by healthcare professionals and consumers, as well as mandatorily by pharmaceutical manufacturers.

What would be expected from this analysis?

FAERS data can uncover hidden safety patterns that wern’t caught during initial clinical trials. For example, drug combination side effects or rare side effects in specific demographics. But this analysis focuses solely on drug-reaction pairs. Because I want to start with simple task first, which will lead to more complex analysis later on.

ระเบียบวิธีวิเคราะห์ข้อมูล

FAERS Analysis pipeline workflow:

Data source

The raw data comes from the FDA’s FAERS quarterly data releases, provided as massive, deeply nested XML files containing millions of patient and drug records from 2004Q1-2025Q4.

Data available here: FAERS Database

การเตรียมและการทำความสะอาดข้อมูล

1. ETL (Extract, Transform, Load)

I started with reading related documentations and sampling XML files to understand the structure and content of the data. Then, with the help of AIs, I developed `faers_etl.py` script using `xml.etree.ElementTree` library. I tested it on small subsets of the data first, and increased the data size to handle the massive XML files without crashing. Finally, I loaded the processed data into a structured PostgreSQL database.

The primary challenge with FAERS XML files is their size (2.2 GBs in total). Loading the entire DOM into memory would cause a crash (I had already crashed and burned my quota on Colab). My solution uses `iterparse` to process the file report-by-report, clearing the memory immediately after each insertion:

def process_file(xml_path: Path, conn):
    # Stream-parse one XML file — memory-safe for large files
    context = ET.iterparse(xml_path, events=("end",))
    
    for event, elem in context:
        if elem.tag != "safetyreport":
            continue
            
        # Parse and insert into DB
        report = parse_safety_report(elem)
        _insert(cur, "safety_report", report)
        
        # CRITICAL: Discard the element from memory immediately
        elem.clear()

2. Data cleaning

Utilizing SQL (`faers_clean.sql`), I performed extensive data normalization and cleaning:

1. Date & Age Standardization: Converted string dates to standard formats, handled low-precision dates, and normalized various age units (months, days) into a single `ageyears` column.
2. Label Decoding: Translated cryptic numeric codes into readable text (e.g., patient sex, reaction outcomes).
3. Drug & Indication Normalization: Extracted missing active ingredients from product names using regex, stripped chemical salt suffixes, and standardized medical indications.
4. Sender Normalization: Consolidated various pharmaceutical company subsidiaries into standardized parent company names.
5. Severity Reconciliation: Created a reliable master “seriousness” flag to fix logical inconsistencies in the raw FDA data.
6. Deduplication: Built a clinical “fingerprint” (matching demographics, dates, drugs, and reactions) to identify and remove duplicate reports submitted by multiple sources.
7. Final Analytical View: Compiled all the cleaned and filtered data into a final view (`v_analysis`) to serve as a reliable source of truth for the next statistical phase.

One of the most complex tasks was identifying duplicate reports sent by different sources (e.g., a doctor and a manufacturer). I developed a “clinical fingerprint” strategy that matches reports based on a combination of demographics and medical data:

-- 10.1 Create a clinical fingerprint for each safety_report
-- A fingerprint consists of demographics, country, date, and aggregated suspect drugs + reactions.
CREATE TEMP TABLE report_fingerprint AS
SELECT
    sr.safetyreportid,
    sr.occurcountry,
    sr.receivedate,
    p.patientsex,
    p.ageyears,
    -- Aggregate suspect drugs into a single sorted string
    (SELECT STRING_AGG(DISTINCT d.substance_std, '|' ORDER BY d.substance_std) 
     FROM drug d 
     WHERE d.safetyreportid = sr.safetyreportid AND d.drugcharacterization = 1) AS suspect_drugs,
    -- Aggregate reactions into a single sorted string
    (SELECT STRING_AGG(DISTINCT r.reactionmeddrapt, '|' ORDER BY r.reactionmeddrapt)
     FROM reaction r
     WHERE r.safetyreportid = sr.safetyreportid) AS reactions
FROM safety_report sr
JOIN patient p ON sr.safetyreportid = p.safetyreportid
WHERE sr.duplicate IS NULL; -- Ignore already flagged duplicates

การวิเคราะห์เชิงพรรณนา (Descriptive Profiling)

I utilized R with packages (tidyverse, DBI, RPostgres) to plot descriptive profiling for better understanding of the dataset. Key insights from the data profiling include:

Signal detection

In pharmacovigilance, we look for “disproportionate signals”: when a specific side effect is reported for a drug more often than expected by chance.

For readers who are not familiar with pharmacovigilance, here is a quick guide:

Signal detection principle

Important question: Is this drug-event combination reported more than expected?

To answer this, we construct a 2×2 contingency table:

Where:

• a: Number of reports for the target drug with the target reaction
• b: Number of reports for the target drug with other reactions
• c: Number of reports for other drugs with the target reaction
• d: Number of reports for other drugs with other reactions
• n: Total number of reports

Let’s apply these variables to the metrics for signal detection.

Metrics for signal detection

1. PRR (Proportional Reporting Ratio)

PRR asks: “Is this side effect reported proportionally more for this drug than for all others?”

PRR is the ratio between:

• Proportion of reports for a specific drug-event pair
• Proportion of reports for other drugs with the same event

Interpret as:

• PRR = 1: No association
• PRR > 1: signal detected
• PRR < 1: signal not detected/protective effect (rare)

2. ROR (Reporting Odds Ratio)

ROR asks: “How much more likely is this drug-reaction pair to appear in the data compared to any other drug-reaction combination?”

• Is a cross-product ratio
• From the case-control perspective
• Interpret as:
  • ROR = 1: No association
  • ROR > 1: signal detected
  • ROR < 1: signal not detected/protective effect (rare)

3. Chi-square χ² (with Yates’ continuity correction): Test of independence

χ² asks: “Is the association between this drug and this reaction statistically real, or could it just be random chance?”

• Test whether a,b,c,d are independent (null hypothesis)
• Interpret as:
  • χ² <= 4: fail to reject null hypothesis at ~95% confidence level -> signal is not statistically significant
  • χ² > 4: reject null hypothesis at ~95% confidence level -> signal is statistically significant

4. Information Component (IC; with Bayesian correction): Test of disproportionality

IC asks: “How much more often is this drug-reaction pair reported than we’d expect if the two were completely unrelated?”

• Measures information gain from the observation (Log2 ratio between observed and expected counts of the event-drug pair)
• Interpret as:
  • IC = 0: no information gain (Observed = Expected)
  • IC > 0: signal detected, IC = 1: 2x more than expected, IC = 2: 4x more than expected, …
  • IC < 0: signal not detected/protective effect (rare)

5. IC025: Signal Stability

IC025 asks: “Even in the worst-case statistical scenario, is this signal still strong enough to be considered real and not a random chance from small sample sizes?”

• Lower bound of 95% Confidence Interval of IC
• Interpret as:
  • IC025 < 0: signal are not statistically significant at 95% confidence level
  • IC025 >= 0: signal detected with 95% confidence level

For signal detection methods selection, I borrowed methodology from many organizations for robustness:

• PRR, ROR, and χ² from European Medicines Agency (EMA)
• IC/BCPNN, and IC025 from WHO Uppsala Monitoring Centre (VigiBase)
• Thresholds: PRR ≥ 2, χ² ≥ 4 from UK MHRA (Medicines and Healthcare products Regulatory Agency), added: a >= 3 & IC025 > 0 in this analysis

I utilized R to apply these statistical methods to find strong associations.

Confounding control

Confouding by indication is when the indication of the drug is filled as an adverse effect of the drug. For example, a diabetes drug will have high reports of high blood sugar simply because the patients have diabetes. I built a filter to significantly reduce these logical overlaps so we only flag unexpected side effects.

To ensure the detection of new safety signals rather than symptoms of the underlying disease, I implemented a string-matching filter in R. This removes any case where the reported reaction is already mentioned as the reason for taking the drug:

# Indication Overlap Logic (Confounding Control)
df_filtered <- df_raw %>%
  mutate(
    rxn_clean = str_to_lower(str_trim(reactionmeddrapt)),
    ind_clean = str_to_lower(str_trim(indication_std))
  ) %>%
  filter(
    is.na(ind_clean) | 
    (rxn_clean != ind_clean & !str_detect(ind_clean, fixed(rxn_clean)))
  )

Visualization & Findings

I created interactive visualizations using R (ggplot, Plotly) to make the findings accessible. This includes:

1. Heatmaps of drug-reaction matrices

The Safety Signal Intensity Heatmap visualizes the association strength (IC025) between the top 40 drugs and the top 40 reported adverse reactions. Darker blue cells indicate a higher lower bound of the Information Component (IC), representing a statistically robust signal. Findings from this heatmap include:

2. Interactive volcano plots

This visualization displays safety signals by plotting the Information Component lower bound (IC025) against the Log10 Chi-square statistic.

Key Findings: The plot clearly isolates a “Strong Signals” quadrant (top-right) where drug-reaction pairs meet both rigorous Bayesian and Frequentist criteria. By filtering for the “Alimentary tract and metabolism” ATC class, GLP-1 receptor agonists (such as Semaglutide and Tirzepatide) stand out in the upper-right quadrant. Their data points appear as massive, red bubbles representing high case volumes and PRR values for gastrointestinal adverse events. This immediate visual confirmation justifies selecting GLP-1 agonists for a targeted deep-dive analysis.

3. Interactive GLP-1 Safety Signals

4. Interactive GLP-1 Safety Signals by sub-population

The Sub-population Safety Signal Heatmap is a multi-dimensional tool designed to uncover how safety signals vary across different patient demographics.

Conclusion

This FAERS data analysis pipeline effectively demonstrates how statistical pharmacovigilance techniques combined with interactive visualizations can isolate and interpret genuine drug safety signals from background noise.

By applying both Bayesian (IC) and Frequentist methods (PRR, ROR), I identified GLP-1 receptor agonists as a drug class of exceptionally high interest due to their overwhelmingly strong safety signals. Through our targeted deep-dive visualizations, several key clinical insights emerged:

Ultimately, this project showcases the power of transforming massive, complex raw data into granular, actionable clinical insights that can inform personalized patient care and enhance drug safety monitoring.

Discussion

While this pipeline successfully extracts actionable insights from raw FAERS data, analyzing real-world pharmacovigilance data presents several inherent challenges that leave room for future improvement:

Drug Name Standardization

Raw adverse event reports use tens of thousands of different names, misspellings, or abbreviations for the same drug. In this iteration, I implemented a custom fuzzy matching algorithm to standardize medicinal product names into generic active substances. Initial explorations using external APIs (like OpenFDA and RxNorm) yielded inconsistent results: such as erroneously mapping “0.9 % Normal saline” to “tolnaftate”. Moving forward, integrating flexible methods like Retrieval-Augmented Generation (RAG) or Large Language Models (LLMs) could provide the contextual understanding necessary for highly accurate, automated drug mapping.

Cross-sender deduplication

The FAERS database frequently contains duplicate reports submitted by different entities (e.g., a physician, a pharmacist, and the manufacturer reporting the same single event). Although this project employs duplication flag and clinical fingerprinting to identify and exclude overlapping reports across different senders, this deduplication strategy is not perfect due to sparse patient-specific identifiers such as age, bodyweight, etc. This may lead to inflated case counts and biased statistical signals. Future enhancements could explore probabilistic record linkage to improve accuracy.

Indication Filtering and Confounding

“Confounding by indication” is a persistent hurdle. While my custom Indication filtering successfully reduces direct logical overlaps. However, some disease-driven signals still occasionally slip through (such as “Blood glucose increased” or “Drug ineffective”). Developing more nuanced clinical ontologies to filter downstream disease complications, or accurately accounting for off-label usage, would help isolate only the truly unexpected adverse drug reactions.

Advanced Signal Detection Methods

The current pipeline utilizes a robust blend of Frequentist (PRR, ROR, Chi-square) and Bayesian (Information Component via BCPNN) methodologies. However, the signal detection capabilities can be further enhanced by incorporating more sophisticated empirical Bayes methods, such as the Multi-item Gamma Poisson Shrinker (MGPS) to calculate the Empirical Bayes Geometric Mean (EBGM). These algorithms, frequently utilized by the FDA, are particularly effective at minimizing false positives in extremely sparse data and detecting complex multi-drug interactions (polypharmacy).

Analysis scripts

My GitHub repo

Thank you for making it this far, this is my first complete health data analysis project. This project taught me many things. I will make sharper analysis, stay tuned for my next project!

Disclaimer (again!?): This project is for educational purposes. Findings should not be used for clinical decision-making.