Pandas

Pandas

What I do

I provide powerful data manipulation and analysis capabilities through DataFrame and Series data structures. I enable you to load data from various sources, clean and preprocess data, perform complex transformations, aggregate and group data, handle missing values, merge datasets, and conduct exploratory data analysis. I am the foundational tool for data science workflows in Python.

When to use me

• Loading and exporting data (CSV, Excel, JSON, SQL databases)
• Cleaning and preprocessing messy datasets
• Filtering and selecting specific data subsets
• Handling missing or corrupted data
• Transforming and creating new features
• Grouping and aggregating data
• Merging and joining multiple datasets
• Time series analysis and resampling
• Statistical analysis on tabular data

Core Concepts

Data Structures

• Series: One-dimensional labeled array with homogeneous data
• DataFrame: Two-dimensional labeled data structure with columns of potentially different types
• Index: Labels for rows and columns, supports hierarchical (multi-index) structures

Data Operations

• Selection: .loc[] for label-based, .iloc[] for integer-based selection
• Filtering: Boolean indexing with conditions
• Sorting: .sort_values() and .sort_index()
• Mapping: .apply(), .map(), .applymap() for element-wise operations

Data Cleaning

• Missing Data: isna(), dropna(), fillna(), interpolate()
• Duplicates: duplicated(), drop_duplicates()
• Data Types: astype(), infer_objects()
• String Operations: .str accessor with regex support

Aggregation

• GroupBy: .groupby() for split-apply-combine operations
• Aggregation: .agg(), .aggregate() with multiple functions
• Transformation: .transform() maintaining original shape
• Window Functions: .rolling(), .expanding(), .ewm()

Merging and Joining

• Concatenation: pd.concat() for combining along axes
• Merging: pd.merge() for SQL-style joins (inner, outer, left, right)
• Join: DataFrame method for index-based joining

Code Examples (Python)

import pandas as pd
import numpy as np

# Creating DataFrames
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'David'],
    'age': [25, 30, 35, 40],
    'salary': [50000, 60000, 75000, 90000],
    'department': ['Engineering', 'Sales', 'Engineering', 'Marketing']
})

# Loading data
df = pd.read_csv('data.csv', parse_dates=['date'], index_col='id')
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
df = pd.read_json('data.json')
df = pd.read_sql('SELECT * FROM table', connection)

# Data selection
df['name']  # Single column as Series
df[['name', 'salary']]  # Multiple columns
df.loc['row1':'row5', 'name':'salary']  # Label-based
df.iloc[0:5, 0:3]  # Integer-based

# Filtering
df[df['age'] > 30]
df[(df['age'] > 30) & (df['department'] == 'Engineering')]
df[df['name'].str.contains('Alice|Bob')]

# Data cleaning
df.isnull().sum()  # Missing values
df.dropna(thresh=len(df.columns)-2)  # Drop rows with too many NaN
df.fillna({'age': df['age'].median()})  # Fill specific columns
df.duplicated().sum()  # Count duplicates
df.drop_duplicates(subset=['name'], keep='first')

# String operations
df['name'].str.lower()
df['email'].str.extract(r'@(.+)$')
df['department'].str.replace('Engineering', 'Eng')

# Type conversion
df['age'] = df['age'].astype(int)
df['salary'] = pd.to_numeric(df['salary'], errors='coerce')

# Sorting
df.sort_values('salary', ascending=False)
df.sort_values(['department', 'salary'], ascending=[True, False])
df.sort_index()

# Grouping and aggregation
df.groupby('department').agg({
    'salary': ['mean', 'max', 'min'],
    'age': 'mean'
})
df.groupby('department')['salary'].transform('mean')

# Pivot tables
pd.pivot_table(df, values='salary', index='department', 
               columns='year', aggfunc='mean')

# Merging
df1 = pd.DataFrame({'id': [1, 2, 3], 'name': ['A', 'B', 'C']})
df2 = pd.DataFrame({'id': [1, 2, 4], 'salary': [50000, 60000, 70000]})
merged = pd.merge(df1, df2, on='id', how='outer')

# Concatenation
pd.concat([df1, df2], ignore_index=True)
pd.concat([df1, df2], axis=1)

# Time series
df.set_index('date', inplace=True)
df.resample('M').mean()  # Monthly aggregation
df.rolling(window=7).mean()  # 7-day rolling average
df.expanding().sum()  # Cumulative sum
df.diff()  # Difference between consecutive values

# Statistical operations
df.describe()
df.corr()
df.cov()
df['salary'].value_counts()
df['department'].nunique()

# Apply functions
def categorize_age(age):
    if age < 30:
        return 'Young'
    elif age < 50:
        return 'Middle'
    else:
        return 'Senior'

df['age_category'] = df['age'].apply(categorize_age)
df['salary_log'] = df['salary'].apply(np.log)

# Exporting data
df.to_csv('output.csv', index=False)
df.to_excel('output.xlsx', sheet_name='Data')
df.to_json('output.json', orient='records')

Best Practices

1. Use vectorized operations: Avoid loops; pandas operations are optimized for vectorized execution.

2. Chain operations for readability: Use method chaining with assign(), pipe(), and successive operations.

3. Specify dtypes explicitly: When loading data, specify column types to avoid inference overhead.

4. Use appropriate index: Set meaningful indices (dates, IDs) for efficient operations.

5. Avoid chained indexing: Use .loc[] and .iloc[] with tuple indices instead of df[col][row].

6. Memory optimization: Use category dtype for low-cardinality strings, downcast for numeric types.

7. Use appropriate file formats: Parquet for speed and compression, CSV for interoperability.

8. Handle SettingWithCopy warnings: Understand pandas copy/view semantics and use .copy() when needed.

Common Patterns

Pattern 1: Exploratory Data Analysis Pipeline

def eda_pipeline(df):
    # Basic info
    info = {
        'shape': df.shape,
        'dtypes': df.dtypes,
        'missing': df.isnull().sum(),
        'describe': df.describe()
    }
    
    # Categorical analysis
    cat_cols = df.select_dtypes(include=['object']).columns
    cat_summary = {col: {'unique': df[col].nunique(), 
                        'top': df[col].value_counts().head()} 
                   for col in cat_cols}
    
    # Numerical analysis
    num_cols = df.select_dtypes(include=[np.number]).columns
    num_summary = df[num_cols].corr()
    
    return info, cat_summary, num_summary

Pattern 2: Data Cleaning Pipeline

def clean_data(df):
    # Remove duplicates
    df = df.drop_duplicates()
    
    # Handle missing values
    numeric_cols = df.select_dtypes(include=[np.number]).columns
    categorical_cols = df.select_dtypes(include=['object']).columns
    
    df[numeric_cols] = df[numeric_cols].fillna(df[numeric_cols].median())
    df[categorical_cols] = df[categorical_cols].fillna(df[categorical_cols].mode().iloc[0])
    
    # Remove outliers (IQR method)
    for col in numeric_cols:
        Q1 = df[col].quantile(0.25)
        Q3 = df[col].quantile(0.75)
        IQR = Q3 - Q1
        df = df[(df[col] >= Q1 - 1.5*IQR) & (df[col] <= Q3 + 1.5*IQR)]
    
    # Standardize text
    for col in categorical_cols:
        df[col] = df[col].str.strip().str.lower()
    
    return df

Pattern 3: Feature Engineering from Raw Data

def engineer_features(df):
    # Create features
    df = df.assign(
        age_group=lambda x: pd.cut(x['age'], bins=[0, 25, 35, 50, 100],
                                   labels=['young', 'adult', 'middle', 'senior']),
        salary_per_year_exp=lambda x: x['salary'] / (x['years_exp'] + 1),
        is_senior=lambda x: (x['age'] > 40).astype(int)
    )
    
    # One-hot encoding
    df = pd.get_dummies(df, columns=['department', 'gender'], drop_first=True)
    
    # Interaction features
    df['exp_salary_product'] = df['years_exp'] * df['salary']
    
    return df