Python Pandas for Journalists

Why pandas for journalists?

pandas is the most widely used data-analysis library for Python. It lets you load, clean, filter, merge, and visualise data in a reproducible script rather than a click-by-click spreadsheet process. Every step is written down, so your analysis can be reviewed, corrected, and re-run when new data arrives. This is particularly valuable for stories that are updated monthly, such as NHS waiting times or crime statistics.

Install Anaconda first — it comes with everything you need. Then open Jupyter Notebook to write and run your code in an interactive notebook that mixes code, output, and text.

Core pandas operations

import pandas as pd

# Read ONS local authority population estimates CSV
df = pd.read_csv('population_estimates_2023.csv')

df.head()        # first 5 rows
df.info()        # column names, types, non-null counts
df.describe()    # count, mean, min, max, quartiles
df.shape         # (rows, columns)
df.columns       # list of column names

# NHS waiting times: trusts with average wait over 18 weeks
long_waits = df[df['avg_wait_weeks'] > 18]

# Multiple conditions
northern = df[
    (df['region'] == 'North West') &
    (df['specialty'] == 'Orthopaedics')
]

# String contains
councils = df[df['authority_name'].str.contains('Council')]

# Average wait per NHS trust per specialty
summary = df.groupby(['trust_name', 'specialty']).agg(
    avg_wait=('wait_weeks', 'mean'),
    total_patients=('patient_count', 'sum')
).reset_index()

summary.sort_values('avg_wait', ascending=False).head(10)

# Merge council spending with deprivation index by local authority
spending = pd.read_csv('council_spend.csv')
deprivation = pd.read_csv('imd_scores.csv')

merged = pd.merge(
    spending,
    deprivation,
    left_on='authority_code',
    right_on='lad_code',
    how='left'   # keep all rows from spending, add deprivation where matched
)

# Check for unmatched rows
merged[merged['imd_score'].isna()]

import matplotlib.pyplot as plt

# Bar chart of top 10 trusts by average wait
top10 = summary.nlargest(10, 'avg_wait')
top10.plot(kind='bar', x='trust_name', y='avg_wait',
           title='NHS Trusts with Longest Average Waits')
plt.tight_layout()
plt.savefig('nhs_waits.png', dpi=150)

# Save to CSV for Datawrapper or further analysis
summary.to_csv('nhs_waits_cleaned.csv', index=False)

# Save to Excel with multiple sheets
with pd.ExcelWriter('analysis.xlsx') as writer:
    summary.to_excel(writer, sheet_name='Summary', index=False)
    long_waits.to_excel(writer, sheet_name='Long waits', index=False)

When to use Python instead of a spreadsheet

• 1The dataset is too large for Excel or Google Sheets (over 1 million rows).
• 2The same analysis needs to run every month as new data arrives — a script is faster than repeating clicks.
• 3You need to merge more than two or three datasets in complex ways.
• 4You want a fully reproducible, reviewable audit trail of your analysis.
• 5You are combining data cleaning and visualisation in one workflow.

Red flags

• ⚠SettingWithCopyWarning — you are modifying a copy of a DataFrame slice; use .loc[] instead.
• ⚠Chained indexing like df['col'][0] — unpredictable behaviour; use df.loc[0, 'col'].
• ⚠Silently dropped rows after a merge — always check len(merged) vs len(original).
• ⚠Object dtype instead of numeric — the column contains text; use pd.to_numeric() with errors='coerce'.
• ⚠Date column parsed as string — use parse_dates=['date_col'] in pd.read_csv().

pandas analysis checklist

• I ran df.info() and df.describe() before any analysis to understand the data.
• I checked df.isna().sum() to count missing values in each column.
• I confirmed the row count after any merge or filter matches my expectations.
• I used .copy() when creating subsets I plan to modify.
• I saved the cleaned DataFrame to a new CSV before starting the analysis.
• My Jupyter notebook runs from top to bottom without errors.

Common mistakes

• ›Not restarting the kernel before sharing a notebook — cells run out of order can produce wrong results.
• ›Using print() to check values mid-analysis rather than df.head() which shows the full structure.
• ›Not specifying encoding when reading CSVs from UK government sources — use encoding='utf-8-sig' for files with a BOM.
• ›Assuming groupby preserves row order — always sort after groupby if order matters.

Related guides

Primary sources

• Anaconda distribution (Python + pandas installer)
• pandas documentation
• Jupyter Notebook documentation
• ONS datasets for practice data

Frequently asked questions