Advanced Data Wrangling

Beyond Simple Tables

Most data isn't a simple grid. Often, it has layers or groups. Think of sales data grouped by region, then by store, then by product. A single index can't capture this structure cleanly. This is where multi-indexing comes in.

A MultiIndex, or Hierarchical Index, lets you have multiple index levels on a single axis. It's a powerful way to work with higher-dimensional data in a two-dimensional table format. Instead of flattening your data and creating repetitive columns, you can build the hierarchy directly into the index.

import pandas as pd
import numpy as np

# Create hierarchical index arrays
regions = ['North', 'North', 'South', 'South']
stores = ['Store A', 'Store B', 'Store A', 'Store B']

# Create the MultiIndex
index = pd.MultiIndex.from_arrays([regions, stores],
                                    names=['Region', 'Store'])

# Create a DataFrame with this index
df = pd.DataFrame({'Sales': [150, 200, 100, 120]},
                  index=index)

# Display the DataFrame
print(df)

# Access data for the 'North' region
print('\nSales in North Region:')
print(df.loc['North'])

The code above creates a two-level index: Region and Store. This makes slicing the data intuitive. Using df.loc['North'] neatly pulls all the data for that top-level index group. This is far more efficient and readable than filtering a 'Region' column, especially as your datasets and hierarchies grow more complex.

Fast and Clean Text

Text data is notoriously messy. It's full of typos, extra spaces, and inconsistent capitalization. Cleaning it one entry at a time with a loop is slow and inefficient. Pandas offers a much better way through vectorization with the .str accessor.

This lets you apply string functions to an entire Series at once. The operations are performed in highly optimized, low-level code, not slow Python loops. This makes your data cleaning pipelines dramatically faster.

# Sample DataFrame with messy text
data = {'Product Name': [' product A ', 'Product b', ' PRODUCT C']}
df_text = pd.DataFrame(data)

# Chain vectorized string operations
df_text['Cleaned Name'] = (df_text['Product Name']
                           .str.strip()
                           .str.lower()
                           .str.replace(' ', '_'))

print(df_text)

Notice how we can chain multiple methods together: .str.strip() to remove leading/trailing spaces, .str.lower() for consistent case, and .str.replace() to swap spaces for underscores. This is a clean, readable, and fast way to standardize text.

Taming Complex Data Sources

Data rarely arrives in a perfect CSV. When you pull data from an or work with certain database outputs, you'll often encounter nested structures. This means some values are themselves dictionaries or lists, which can't be placed directly into a DataFrame column.

Trying to unpack this manually is tedious and error-prone. Thankfully, Pandas has a built-in tool, json_normalize, designed specifically for this problem. It intelligently flattens nested data into a clean, tabular format.

from pandas import json_normalize

# Sample nested JSON data (like from an API)
raw_data = [{
    'id': 1,
    'student': 'Alice',
    'info': {'major': 'Physics', 'gpa': 3.8}
}, {
    'id': 2,
    'student': 'Bob',
    'info': {'major': 'Art', 'gpa': 3.2}
}]

# Flatten the data into a DataFrame
df_normalized = json_normalize(raw_data)

print(df_normalized)

The json_normalize function automatically unpacks the info dictionary, creating separate info.major and info.gpa columns. It handles the nested structure, so you can get straight to analysis.

Smarter Ways to Fill the Gaps

Missing data is a constant challenge. While filling gaps with the mean or median is a common first step, these simple methods can distort the relationships within your data. Advanced imputation techniques can create more realistic and accurate replacements.

Two powerful methods are K-Nearest Neighbors (KNN) Imputation and s. These techniques use the other features in your dataset to make intelligent guesses about the missing values.

KNN Imputation works by finding the 'k' most similar data points (the neighbors) to the one with the missing value and then using the average of their values as the replacement. This preserves local patterns in the data.

These advanced methods are available in libraries like scikit-learn and can be integrated directly into your data cleaning workflow.

from sklearn.impute import KNNImputer

# DataFrame with missing values
df_missing = pd.DataFrame({
    'height': [160, 175, 155, np.nan, 180],
    'weight': [65, 80, np.nan, 70, 85],
    'age': [25, 30, 22, 28, 35]
})

# Initialize the imputer
imputer = KNNImputer(n_neighbors=2)

# Fit and transform the data
df_imputed = pd.DataFrame(imputer.fit_transform(df_missing),
                          columns=df_missing.columns)

print("Original Data:")
print(df_missing)
print("\nImputed Data:")
print(df_imputed)

By mastering these techniques, you move from basic data cleanup to sophisticated data wrangling, ensuring the data you feed into your models is as robust and representative as possible.

Practical Data Science and Predictive Modeling