A Practical Guide to Pandas for Data Manipulation

Working with data rarely begins with a clean spreadsheet. A sales export may contain dates in several formats, suburb names with inconsistent spelling, blank values, and prices stored as text. Pandas, the Python library for tabular data analysis, provides a practical way to inspect, clean, transform, and combine these datasets before deeper analysis or visualisation.

The examples below suit self-directed learners, analysts, and developers working with CSV files, Excel workbooks, or database extracts. Australian examples such as postcode formats, GST-inclusive prices, state abbreviations, and Melbourne or Sydney sales records make the techniques easier to connect with everyday projects.

From Raw Tables To Tidy Data

Install Pandas alongside Python, usually through a distribution such as Anaconda or with pip install pandas. Import it using the conventional alias, then load a file into a DataFrame, the library’s main two-dimensional data structure:

import pandas as pd

sales = pd.read_csv("sales.csv")
print(sales.head())
print(sales.shape)
print(sales.info())

head() shows the first records, shape returns the number of rows and columns, and info() reveals data types and missing values. These checks should come before calculations. If sale_date appears as object rather than a date type, or price is stored as text, later operations may produce misleading results.

A tidy table generally follows three principles: each variable has its own column, each observation has its own row, and each value occupies one cell. A file containing columns such as product, Sydney, Melbourne, and Brisbane may be readable for humans but awkward for analysis. Reshaping it into product, city, and sales creates a more useful structure.

When importing Australian business data, inspect encoding and separators as well. Some files use semicolons, while exported addresses can include commas. A deliberate import is safer than relying on defaults:

sales = pd.read_csv(
    "sales.csv",
    sep=",",
    encoding="utf-8",
    parse_dates=["sale_date"]
)

Selecting And Cleaning Columns

Selecting columns is straightforward, but explicit selection makes code easier to audit. Use sales["price"] for one Series or sales[["product", "price", "state"]] for a smaller DataFrame. Renaming columns early also prevents confusion caused by spaces, capital letters, or inconsistent labels:

sales = sales.rename(columns={
    "Product Name": "product",
    "Amount ($)": "amount",
    "State/Territory": "state"
})

Text cleaning is often essential when working with Australian addresses and customer records. str.strip() removes accidental spaces, while str.upper() standardises values such as nsw, NSW, and N.S.W. after additional replacement rules. Postcodes should usually remain strings, because a value such as 0800 loses its leading zero if interpreted as an integer.

sales["state"] = sales["state"].str.strip().str.upper()
sales["postcode"] = sales["postcode"].astype("string").str.zfill(4)
sales["amount"] = (
    sales["amount"]
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
    .astype("float")
)

Missing data needs a decision rather than an automatic deletion. isna().sum() provides a quick column-by-column count. You might fill a missing discount with zero, remove a record lacking a transaction date, or preserve an unknown suburb as "Unknown". The correct treatment depends on what the absence means, not simply how convenient the code is.

Dates deserve similar care. Convert them with pd.to_datetime, using dayfirst=True when the source follows the common Australian day-month-year convention. Invalid entries can be identified with errors="coerce", which changes them to missing timestamps for later review.

Filtering Sorting And Creating Fields

Boolean filtering lets you isolate records that meet specific conditions. The expression below selects sales above $100 from Victoria and New South Wales:

selected = sales[
    (sales["amount"] > 100) &
    (sales["state"].isin(["VIC", "NSW"]))
]

Each condition needs parentheses when combined with & or |. For readable filters involving several conditions, query() can be useful:

selected = sales.query("amount > 100 and state in ['VIC', 'NSW']")

Sort the result with sort_values(), and use ascending=False to place the largest transactions first. To obtain a manageable sample for manual inspection, nlargest() is often clearer than sorting the entire DataFrame.

New columns turn raw fields into useful measures. For example, a GST-inclusive Australian price can be calculated from a net amount, while a month field supports seasonal comparisons:

sales["gross_amount"] = sales["net_amount"] * 1.10
sales["month"] = sales["sale_date"].dt.to_period("M")
sales["large_order"] = sales["gross_amount"] >= 500

Avoid changing values row by row with a Python loop unless the operation truly requires it. Vectorised Pandas expressions are generally faster and communicate the transformation more clearly. For complicated categories, np.select() or a small mapping dictionary can replace long chains of conditional statements.

A text column can also be treated as a lightweight source for practice data. For example, a learner could extract product categories from a public Klarna casino review, then clean headings, ratings, and promotional phrases in the same way as any other unstructured web text. The purpose is to practise parsing and validation, not to treat a promotional page as a reliable financial dataset.

Grouping Joining And Reshaping

Grouping is central to summarising information. To calculate total revenue by state, use groupby() with an aggregation:

state_summary = (
    sales.groupby("state", as_index=False)
         .agg(
             orders=("order_id", "nunique"),
             revenue=("gross_amount", "sum"),
             average_order=("gross_amount", "mean")
         )
         .sort_values("revenue", ascending=False)
)

This could reveal differences between New South Wales, Victoria, Queensland, and Western Australia. A grouped result can also be filtered after aggregation, such as retaining regions with more than 100 orders. pivot_table() is helpful when you need a matrix, for example monthly revenue by city.

Joining combines related tables through a shared key. A sales table may contain customer_id, while a separate customer table stores suburb and state. Use merge() and specify the relationship carefully:

enriched = sales.merge(
    customers[["customer_id", "suburb", "state"]],
    on="customer_id",
    how="left",
    validate="many_to_one"
)

The validate argument can expose unexpected duplicate customer records. An inner join keeps only matching rows, while a left join preserves every sale and leaves missing customer details as null values. After a merge, compare row counts and inspect unmatched keys instead of assuming the result is correct.

For data stored in separate monthly files, pd.concat() stacks tables vertically. For wide-to-long transformations, melt() converts repeated month columns into a single variable column. These operations make data suitable for charts, statistical models, and dashboard tools.

Building Reliable Analysis Habits

A dependable Pandas workflow separates loading, cleaning, analysis, and export. Keep a raw copy of the source file, avoid overwriting it, and save cleaned outputs with an informative name such as sales_clean_2025_06.csv. When exporting, use index=False unless the index is a meaningful field:

sales.to_csv("sales_clean.csv", index=False)

Before trusting a result, check its assumptions. Confirm that row counts have not changed unexpectedly, numeric columns contain plausible values, dates fall within the intended period, and categories use consistent spelling. A simple assertion can catch an issue early:

assert sales["order_id"].notna().all()
assert (sales["gross_amount"] >= 0).all()

A small checklist helps keep routine work consistent:

• Confirm column names and data types after import
• Check missing values before calculating totals
• Review duplicate keys before joining tables
• Compare row counts before and after transformations

Performance matters when a file grows beyond ordinary spreadsheet size. Select only required columns during import, use efficient types for repeated categories, and avoid unnecessary copies. For very large extracts, process data in chunks or move filtering and aggregation into the database before loading it into Pandas.

Useful habits for an Australian data project include:

• Keep postcode fields as four-character strings
• Document whether prices include the 10% GST rate
• Standardise state and territory abbreviations
• Record whether dates use local time or UTC

Pandas is most valuable when it makes each transformation visible and testable. The essential memory is simple: inspect the table first, clean values deliberately, transform with clear expressions, validate every join and summary, and preserve the meaning of the original data.