How to use pandas.DataFrame for data manipulation in Python

The fundamental object we operate on within the pandas library is the DataFrame. It is a two-dimensional, size-mutable, and potentially heterogeneous tabular data structure with labeled axes (rows and columns). Before any meaningful manipulation or analysis can occur, we must first construct this object. The assembly of a DataFrame can be approached through several common patterns, each suitable for different initial data states.

The most direct construction involves using a Python dictionary where keys map to column names and values are lists of the data for those columns. This pattern is particularly effective when data is already organized by feature or variable. It’s a clean mapping from a common Python data structure to the core pandas object.

import pandas as pd

# Data organized as a dictionary of lists
data = {
    'OrderID': [10248, 10249, 10250, 10251, 10252],
    'CustomerID': ['VINET', 'TOMSP', 'HANAR', 'VICTE', 'SUPRD'],
    'EmployeeID': [5, 6, 4, 3, 4],
    'OrderDate': ['1996-07-04', '1996-07-05', '1996-07-08', '1996-07-08', '1996-07-09'],
    'Freight': [32.38, 11.61, 65.83, 41.34, 51.30]
}

orders_df = pd.DataFrame(data)

An alternative, and often more intuitive, pattern is to construct the DataFrame from a list of dictionaries. Each dictionary in the list represents a single record or row. This structure aligns well with data retrieved from document stores or APIs that return JSON objects. Pandas correctly infers the column names from the dictionary keys and handles missing values gracefully if some records lack certain keys.

# Data organized as a list of dictionaries (records)
data_records = [
    {'OrderID': 10248, 'CustomerID': 'VINET', 'EmployeeID': 5, 'Freight': 32.38},
    {'OrderID': 10249, 'CustomerID': 'TOMSP', 'EmployeeID': 6, 'Freight': 11.61},
    {'OrderID': 10250, 'CustomerID': 'HANAR', 'EmployeeID': 4, 'Freight': 65.83, 'ShippedDate': '1996-07-16'},
    {'OrderID': 10251, 'CustomerID': 'VICTE', 'EmployeeID': 3, 'Freight': 41.34},
    {'OrderID': 10252, 'CustomerID': 'SUPRD', 'EmployeeID': 4, 'Freight': 51.30, 'ShippedDate': '1996-07-11'}
]

records_df = pd.DataFrame(data_records)

While these in-memory construction patterns are useful for testing and small datasets, the most robust and common pattern for assembling a DataFrame is by reading from an external data source. Comma-Separated Values (CSV) files are a ubiquitous format for data exchange, and pandas provides a highly optimized reader function, read_csv, to handle this. This function encapsulates the complex logic of file parsing, data type inference, and header detection into a single, powerful call. For this example, we will assume we have a file named products.csv in our working directory.

# Assuming 'products.csv' exists with appropriate content
# Example content:
# ProductID,ProductName,SupplierID,CategoryID,UnitPrice
# 1,Chai,1,1,18.00
# 2,Chang,1,1,19.00
# 3,Aniseed Syrup,1,2,10.00
# 4,Chef Anton's Cajun Seasoning,2,2,22.00

products_df = pd.read_csv('products.csv')

Once the DataFrame is assembled, the first step is always to inspect its structure and a sample of its contents to verify that the import process behaved as expected. The head() method is the idiomatic way to view the first few rows of the data object, confirming column names and data types.

Isolating data with locators and conditions

With the DataFrame constructed, our focus shifts to isolating specific subsets of the data. A raw dataset is rarely used in its entirety; analysis almost always begins by selecting rows and columns that satisfy certain criteria. The most basic selection pattern involves using the square bracket notation [], which can feel similar to dictionary key access or list slicing.

To select a single column, we pass its name as a string. This operation returns a Series, a one-dimensional labeled array which is the constituent component of a DataFrame.

# Select the 'CustomerID' column from our orders DataFrame
customer_ids = orders_df['CustomerID']

To select multiple columns, we pass a list of column names. The result of this operation is a new, smaller DataFrame.

# Select 'OrderID' and 'Freight' columns
order_details = orders_df[['OrderID', 'Freight']]

While this bracket notation is convenient, its behavior can be ambiguous when selecting rows. To provide a more explicit and powerful interface, pandas offers two dedicated indexer attributes: loc and iloc. Using these is the idiomatic pattern for data selection as it avoids ambiguity and prevents common errors associated with a technique known as chained indexing.

The loc indexer facilitates label-based selection. It allows us to select data using the actual labels of the index and the names of the columns. The syntax df.loc[row_labels, column_labels] is precise and readable. For loc to be most effective, it helps to have a meaningful index. Let’s re-index our orders_df using the OrderID column.

orders_df_indexed = orders_df.set_index('OrderID')

# Select the entire row for OrderID 10250
order_10250 = orders_df_indexed.loc[10250]

We can also use loc to retrieve a single scalar value by specifying both the row and column label.

# Get the Freight for OrderID 10250
freight_10250 = orders_df_indexed.loc[10250, 'Freight']

In contrast, the iloc indexer performs selection based on integer position, akin to indexing a standard Python list. It ignores the index and column labels entirely. This is useful when the labels are not unique or when the ordinal position of the data is what matters.

# Select the first row (at position 0)
first_order = orders_df.iloc[0]

# Select the first three rows and the first two columns
subset = orders_df.iloc[0:3, 0:2]

The true expressive power of these indexers is realized when they are combined with boolean conditions for filtering. This pattern, known as boolean indexing, involves creating a boolean Series (a mask) where each element is True or False based on a condition. This mask is then used within loc to filter the DataFrame.

# Create a boolean mask for orders handled by EmployeeID 4
is_employee_4 = orders_df['EmployeeID'] == 4

# Use the mask with .loc to select the corresponding rows
employee_4_orders = orders_df.loc[is_employee_4]

Multiple conditions can be chained together using the bitwise operators & (AND) and | (OR). It is a critical syntactical point that each individual condition must be enclosed in parentheses due to Python’s operator precedence.

# Find orders with Freight over 50 AND handled by EmployeeID 4
heavy_orders_by_emp4 = orders_df.loc[(orders_df['Freight'] > 50) & (orders_df['EmployeeID'] == 4)]

This pattern of generating a boolean mask and applying it with .loc is the canonical method for conditional selection. It is not only more efficient than other potential approaches but also allows for filtering rows and selecting columns in a single, atomic, and highly readable operation.

# Get CustomerID and OrderDate for orders with Freight over 50
customer_dates_for_heavy_orders = orders_df.loc[orders_df['Freight'] > 50, ['CustomerID', 'OrderDate']]

Another powerful conditional tool is the isin method, which is used to filter for rows where a column’s value is present within a given list. This is a more concise and often more performant alternative to chaining multiple OR conditions.

# Select orders for a specific list of customers
target_customers = ['VINET', 'SUPRD', 'HANAR']
specific_customer_orders = orders_df.loc[orders_df['CustomerID'].isin(target_customers)]

These selection and filtering techniques form the foundational vocabulary for any data manipulation task. Mastering the distinction and application of loc, iloc, and boolean masking is the prerequisite for the subsequent refactoring of the data’s structure.

Refactoring data through column operations

Data rarely arrives in a state that is immediately suitable for final analysis. A crucial intermediate step is refactoring the data’s structure, which most often involves operations on the columns of the DataFrame. These operations range from creating new, derived columns to modifying or removing existing ones. The goal is to shape the data into a more informative and usable representation.

The most fundamental column operation is the creation of a new column derived from one or more existing columns. Pandas excels at this through vectorized operations, which apply an operation to an entire array (a column or Series) at once without the need for an explicit loop in Python. This approach is not only concise but also significantly more performant. For instance, if we need to calculate a new freight charge that includes a 10% tax, we can perform a single scalar multiplication on the entire ‘Freight’ column.

# Create a new column 'FreightWithTax' by adding a 10% tax
orders_df['FreightWithTax'] = orders_df['Freight'] * 1.10

This direct assignment pattern df['new_column'] = ... is common and effective. However, for a more functional programming style that facilitates method chaining, the assign() method is a superior pattern. It does not modify the original DataFrame in place; instead, it returns a new DataFrame with the new column added. This immutability can prevent unintended side effects in a complex data processing pipeline.

# Use assign() to create a new DataFrame with the added column
orders_with_tax_df = orders_df.assign(FreightWithTax = orders_df['Freight'] * 1.10)

While vectorized operations are efficient for simple arithmetic or logical transformations, more complex, conditional logic often requires a different approach. The apply() method allows us to pass a custom function that will be applied to each element in a column. This provides immense flexibility. For example, we could categorize orders into ‘High’, ‘Medium’, or ‘Low’ cost based on their freight value.

# Define a function to categorize freight cost
def categorize_freight(freight_value):
    if freight_value > 50:
        return 'High'
    elif freight_value > 20:
        return 'Medium'
    else:
        return 'Low'

# Apply this function to the 'Freight' column to create a new one
orders_df['FreightCategory'] = orders_df['Freight'].apply(categorize_freight)

For simpler functions, defining a full function with def can be verbose. A common idiom is to use a lambda function directly within the apply() call for a more compact expression. It’s important to recognize, however, that apply() is essentially a form of iteration and will typically be much slower than a vectorized equivalent. It should be reserved for cases where vectorization is not straightforward.

Another critical refactoring task is ensuring columns have the correct data type. Our orders_df has an ‘OrderDate’ column that was read as a generic object (a string). Performing date-based calculations or filtering on a string column is inefficient and error-prone. The idiomatic solution is to convert this column to a proper datetime type using the pd.to_datetime() helper function.

# The 'OrderDate' column is currently an object (string)
# Convert it to a datetime type for date-based operations
orders_df['OrderDate'] = pd.to_datetime(orders_df['OrderDate'])

Finally, we often need to adjust the schema of the DataFrame itself by renaming or removing columns. The rename() method provides a clean way to change column labels by passing a dictionary that maps old names to new names. Similarly, the drop() method is used to remove columns. When dropping, it is crucial to specify axis=1 to indicate that we are targeting columns, not rows (which are on axis 0).

# Rename columns for clarity or consistency
orders_renamed_df = orders_df.rename(columns={
    'EmployeeID': 'HandlerEmployeeID',
    'CustomerID': 'ClientTag'
})

# Drop the 'FreightWithTax' column we created earlier
orders_df = orders_df.drop(columns=['FreightWithTax'])

These column-centric refactoring patterns-creating, transforming, typing, renaming, and removing-are the building blocks for cleaning and preparing a dataset. Once the individual columns are correctly structured and formatted, we can move towards a higher level of analysis by summarizing and grouping the data.

Deriving new insights through aggregation

After refactoring individual data points and columns, the next logical step is to derive summary statistics from groups of data. This process of aggregation allows us to move from a granular view to a higher-level understanding of the dataset’s characteristics. In pandas, the primary mechanism for this is the groupby() method. This method embodies the powerful “split-apply-combine” strategy, a paradigm that is fundamental to data analysis.

The groupby() operation first splits the DataFrame into smaller groups based on the values in one or more specified columns. Then, an aggregation function (like sum(), mean(), or count()) is applied independently to each of these smaller groups. Finally, the results of these applications are combined back into a new DataFrame or Series, where the index is composed of the unique group keys. Let’s examine this pattern by calculating the average freight cost handled by each employee.

# Calculate the average freight cost per employee
avg_freight_by_employee = orders_df.groupby('EmployeeID')['Freight'].mean()

The result of this operation is a Series where the index contains the unique values from the EmployeeID column and the values are the corresponding mean freight costs. This simple pattern is incredibly potent for generating summary reports. Often, however, a single summary statistic is insufficient. We might need to compute multiple statistics for the same group. Instead of running separate groupby operations, we can use the agg() method to apply a list of aggregation functions in a single pass. This is more efficient and produces a well-structured DataFrame as output.

# Apply multiple aggregation functions to the 'Freight' column
employee_freight_summary = orders_df.groupby('EmployeeID')['Freight'].agg(['count', 'sum', 'mean'])

The true flexibility of this approach is revealed when we must apply different aggregations to different columns. This is achieved by passing a dictionary to agg(), where the keys are the column names to be aggregated and the values specify the aggregation functions. The modern, idiomatic pattern for this is “named aggregation,” which provides explicit control over the output column names and avoids the creation of a complex multi-level column index.

# Use named aggregation to get order count and average freight per customer
customer_summary = orders_df.groupby('CustomerID').agg(
    TotalOrders=('OrderID', 'count'),
    AverageFreight=('Freight', 'mean'),
    MaxFreight=('Freight', 'max')
)

This syntax-where we define a new column name and assign it a tuple of ('source_column', 'agg_function')-is exceptionally clear and produces a clean, flat DataFrame that is easy to work with. The grouping key itself does not have to be a single column. We can pass a list of column names to groupby() to create more granular subgroups based on the unique combinations of values in those columns. For instance, we can analyze freight statistics grouped by both employee and the freight category we created earlier.

# Assuming 'FreightCategory' column exists from the previous section's example
# Group by both employee and freight category
employee_category_summary = orders_df.groupby(['EmployeeID', 'FreightCategory'])['Freight'].agg(['mean', 'count'])

The result of grouping by multiple columns is a DataFrame with a MultiIndex. This hierarchical index represents the unique combinations of the grouping keys. While powerful, a MultiIndex can sometimes complicate subsequent manipulations. The reset_index() method is a common pattern used to convert the index levels back into regular columns, effectively flattening the DataFrame for easier filtering and access.