Reading, Filtering, and Sorting Data with Pandas

Pandas is one of the most powerful and widely used libraries in the Python data science ecosystem, and for good reason: it provides a rich, expressive toolkit for loading, inspecting, transforming, and slicing structured data. At the heart of Pandas is the DataFrame, a two-dimensional labeled data structure that behaves like a spreadsheet or a SQL table — rows and columns each carry labels, and you can manipulate them with concise, readable code. Whether you are processing sensor readings from an engineering test, analyzing material properties from a lab spreadsheet, or exploring survey results, the workflow almost always begins with reading a file, selecting the data you care about, filtering out irrelevant rows, and sorting what remains into a meaningful order. This topic walks through each stage of that workflow in depth.

Before anything else, you need to import the library. The universal convention is:

import pandas as pd

The alias pd is not enforced by Python, but it is so universally adopted that any code you encounter online or in textbooks will use it, so it is wise to follow the same convention.

Reading Data from Files

The most common way to get data into a Pandas DataFrame is to read it from a CSV (comma-separated values) file. A CSV file stores tabular data as plain text, with commas separating values in each row and newline characters separating rows. Pandas makes loading such a file trivially simple:

df = pd.read_csv('filepath.csv')

This single line reads the entire file, uses the first row as the column headers, assigns an automatically generated integer index (0, 1, 2, …) to the rows, and returns a fully formed DataFrame. The variable name df is a common shorthand for "DataFrame" and is used throughout most Pandas code.

Consider a file called bridge_loads.csv that looks like this in plain text:

span_id,material,load_kN,deflection_mm
1,steel,450.0,2.3
2,concrete,320.0,4.1
3,steel,510.0,2.7
4,aluminum,290.0,5.5

After calling pd.read_csv('bridge_loads.csv'), Pandas creates a DataFrame with four columns (span_id, material, load_kN, deflection_mm) and four rows indexed 0 through 3. You can immediately inspect it with df.head() or print(df).

Sometimes one of the columns already serves as a meaningful unique identifier for each row — for instance, span_id above. Rather than keeping a redundant default integer index alongside it, you can tell Pandas to promote that column to the index at import time using the index_col parameter:

df = pd.read_csv('bridge_loads.csv', index_col='span_id')

Now the DataFrame's row labels are 1, 2, 3, 4 (drawn from span_id) rather than 0, 1, 2, 3. This is particularly useful when your data has a natural key — a sample ID, a timestamp, a part number — because it lets you look up rows by that meaningful label instead of by an arbitrary integer position. You can also pass an integer to index_col (e.g., index_col=0) to designate the first column by position instead of by name.

Real-world data is rarely clean. Sensors malfunction, surveyors leave fields blank, and legacy databases encode missing values with strings like "N/A", "--", "none", or even domain-specific placeholders like "999" or "missing". Pandas automatically recognises common null indicators (NaN, NULL, empty strings, NA), but it will not know about unusual ones unless you tell it. The na_values parameter accepts a string or list of strings that should be treated as NaN during import:

df = pd.read_csv('sensors.csv', na_values=['--', '999', 'missing'])

With this call, any cell containing --, 999, or the word missing will be stored as NaN in the resulting DataFrame, enabling you to use Pandas' built-in missing-value handling methods (df.isna(), df.dropna(), df.fillna()) seamlessly.

Beyond CSV, pd.read_excel(), pd.read_json(), pd.read_sql(), and several other readers follow the same general pattern and share many of the same parameters, so the skills you learn with read_csv transfer broadly.

Selecting Columns and Rows

Once you have a DataFrame loaded, the next essential skill is isolating exactly the data you need. Pandas offers several selection mechanisms, and understanding which one to use in a given situation prevents subtle bugs.

The most direct way to retrieve a single column is bracket notation with the column name as a string:

loads = df['load_kN']

This returns a Series — a one-dimensional labeled array. A Series shares the same row index as the parent DataFrame and supports most of the same operations (arithmetic, aggregation, plotting). If you want two or more columns and want to keep them together as a DataFrame rather than extracting separate Series objects, pass a list of column names inside the brackets:

subset = df[['material', 'load_kN']]

Notice the double brackets: the outer brackets are the DataFrame selection operator, and the inner brackets define a Python list. The result is a new DataFrame with only those two columns. This distinction — single string gives a Series, list of strings gives a DataFrame — is one of the most commonly confused aspects of Pandas for beginners.

For more precise row-and-column selection, Pandas provides two dedicated indexers: loc and iloc.

df.loc[row_label, col_label] selects by label. Both the row and column arguments use the actual index labels and column names you see in the DataFrame. For example, if the row index is the span_id column from earlier:

# Single cell
df.loc[2, 'load_kN']          # returns 320.0

# Entire row as a Series
df.loc[3]

# Specific row and multiple columns
df.loc[2, ['material', 'deflection_mm']]

# Slice of rows (inclusive on both ends with loc)
df.loc[1:3]

An important subtlety: when you slice with loc, both endpoints are included. df.loc[1:3] returns rows with labels 1, 2, and 3. This differs from normal Python slicing, where the stop is exclusive.

df.iloc[row_int, col_int] selects by integer position, completely ignoring the index labels. This is useful when you want the third row regardless of what its label is, or when you are iterating programmatically:

# First row, first column (position 0, 0)
df.iloc[0, 0]

# First five rows (positions 0–4), all columns
df.iloc[0:5]

# Last row
df.iloc[-1]

With iloc, slicing follows standard Python rules: the stop index is exclusive. df.iloc[0:5] gives positions 0, 1, 2, 3, and 4 — five rows, not six.

A useful mental model: use loc when you know the names of what you want, and use iloc when you know the positions.

Filtering Rows with Boolean Conditions

Selecting predefined rows or columns is useful, but the real analytical power comes from boolean filtering — dynamically selecting rows based on whether they satisfy a condition. This is sometimes called boolean indexing or boolean masking.

The mechanism works in two steps. First, you create a boolean Series — a Series of True and False values — by applying a comparison to a column:

mask = df['load_kN'] > 400

This produces a Series aligned to the same index as df, where each entry is True if that row's load_kN exceeds 400 and False otherwise. Then you pass that mask back into the DataFrame as an index:

heavy_loads = df[df['load_kN'] > 400]

Pandas returns only the rows where the mask is True. In the bridge example, this would return spans 1 and 3 (with loads of 450 and 510), leaving out spans 2 and 4. The original DataFrame df is untouched; heavy_loads is a new filtered view.

You can combine multiple conditions using the bitwise operators & (AND) and | (OR). Each individual condition must be wrapped in parentheses because of Python's operator precedence rules — failing to do so is a common source of errors:

# Rows where load exceeds 400 AND material is steel
steel_heavy = df[(df['load_kN'] > 400) & (df['material'] == 'steel')]

# Rows where material is steel OR deflection is more than 4mm
flexible_or_steel = df[(df['material'] == 'steel') | (df['deflection_mm'] > 4.0)]

You can also negate a condition with the tilde operator ~:

# All rows where material is NOT steel
not_steel = df[~(df['material'] == 'steel')]

For situations where writing out the column references feels verbose, Pandas offers df.query(), which accepts a string expression that is parsed and evaluated against the DataFrame's columns. Column names are referenced directly inside the string, making it read almost like natural language:

heavy_steel = df.query('load_kN > 400 and material == "steel"')

query supports and, or, not, as well as &, |, and ~. You can even reference external Python variables inside a query string using the @ prefix:

threshold = 400
heavy = df.query('load_kN > @threshold')

Both approaches — boolean indexing and query — produce identical results. Boolean indexing is more idiomatic in general code, while query is often preferred when writing exploratory or readable analysis scripts, especially when column names are long and the condition is complex.

Sorting DataFrames

Sorting helps you identify extremes, prepare data for rank-based analysis, and present results in a logical order. The primary method is df.sort_values().

To sort by a single column in ascending order (smallest to largest for numbers, A to Z for strings):

df_sorted = df.sort_values('load_kN')

This returns a new DataFrame with the rows reordered — the original df is not changed unless you pass inplace=True. To reverse the order:

df_sorted_desc = df.sort_values('load_kN', ascending=False)

Now the row with the highest load appears first. This is immediately useful for finding the most heavily loaded spans or the most deflected members.

When you need to sort by more than one criterion — for example, first by material alphabetically, and then within each material group by load in descending order — pass lists to both by and ascending:

df_multi = df.sort_values(
    by=['material', 'load_kN'],
    ascending=[True, False]
)

Here, material is sorted ascending (A–Z) and, wherever two rows share the same material, load_kN is sorted descending within that group. The ascending parameter can take a single boolean (applied to all sort keys) or a list of booleans aligned to the list of column names.

One practical consideration: after sorting, the original row index labels are preserved but appear in a new order, which can look odd (e.g., the index might read 3, 1, 4, 2 instead of 1, 2, 3, 4). If you want a clean sequential integer index after sorting, call reset_index(drop=True):

df_clean = df.sort_values('load_kN', ascending=False).reset_index(drop=True)

The drop=True argument discards the old index rather than promoting it to a column.

Adding and Modifying Columns

Raw data rarely arrives in exactly the form you need for analysis. You will frequently need to create new columns derived from existing ones, transform values, or rename columns for clarity. Pandas makes all of this straightforward.

The simplest way to add a new column is direct assignment. You refer to a column name that does not yet exist on the left side of an assignment, and Pandas creates it:

df['stress_MPa'] = df['load_kN'] / df['area_cm2']

This performs the arithmetic element-wise across all rows simultaneously — no loop required. The result is a new column stress_MPa appended to the DataFrame. You can use any arithmetic combination of existing columns, constants, or NumPy functions:

import numpy as np
df['log_load'] = np.log(df['load_kN'])
df['load_plus_margin'] = df['load_kN'] * 1.15   # 15% safety margin

For transformations that are more complex than simple arithmetic — for instance, applying a custom formula or a lookup — df['col'].apply(func) applies a Python function to each element of a column one at a time:

def classify_load(kn):
    if kn > 500:
        return 'high'
    elif kn > 350:
        return 'medium'
    else:
        return 'low'

df['load_category'] = df['load_kN'].apply(classify_load)

After this call, every row has a new load_category column populated by running classify_load on its load_kN value. You can pass a named function as above, or use a lambda for brief inline transformations:

df['deflection_inches'] = df['deflection_mm'].apply(lambda x: x / 25.4)

apply is flexible but can be slower than vectorised operations (like direct arithmetic) on large datasets, because it iterates in Python rather than delegating to optimised C code. When performance matters with large DataFrames, prefer vectorised expressions; reserve apply for logic that truly cannot be expressed that way.

Finally, df.rename() lets you change column (or index) names without altering any of the underlying data. You pass a dictionary to the columns parameter mapping old names to new names:

df = df.rename(columns={
    'load_kN': 'applied_load_kN',
    'deflection_mm': 'max_deflection_mm'
})

Only the columns you name in the dictionary are changed; all others remain untouched. This is safer than reassigning df.columns entirely (which requires you to list every column name in the correct order). By default, rename returns a new DataFrame; to modify in place, add inplace=True.

Putting it all together, a typical early-stage engineering data workflow might look like this:

import pandas as pd

# 1. Load the data
df = pd.read_csv('bridge_loads.csv', index_col='span_id', na_values=['--', 'N/A'])

# 2. Add a derived column
df['load_per_mm_deflection'] = df['load_kN'] / df['deflection_mm']

# 3. Filter to steel spans only with load above threshold
steel_df = df[(df['material'] == 'steel') & (df['load_kN'] > 400)]

# 4. Sort by stiffness (highest load per mm deflection first)
steel_df = steel_df.sort_values('load_per_mm_deflection', ascending=False)

# 5. Select only the columns needed for the report
report = steel_df[['material', 'load_kN', 'load_per_mm_deflection']]

print(report)

This concise pipeline — read, derive, filter, sort, select — captures the essential rhythm of data preparation with Pandas and scales naturally to datasets with millions of rows.