Pandas Interview Questions

import pandas as pd
import numpy as np

# Sample DataFrame
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
    'age': [25, 30, 35, 28, 32],
    'salary': [50000, 60000, 70000, 55000, 65000],
    'city': ['NY', 'LA', 'SF', 'NY', 'LA']
}, index=['emp_001', 'emp_002', 'emp_003', 'emp_004', 'emp_005'])

# ===== loc (Label-based) =====
# Single cell
print(df.loc['emp_001', 'name'])  # 'Alice'

# Multiple rows/cols
print(df.loc['emp_001':'emp_003', 'name':'salary'])
# IMPORTANT: 'emp_003' IS INCLUDED (inclusive slicing)

# Boolean filtering (VERY COMMON in production)
high_earners = df.loc[df['salary'] > 60000]
print(high_earners)

# Multiple conditions
ny_high_earners = df.loc[(df['city'] == 'NY') & (df['salary'] > 50000)]

# ===== iloc (Integer position) =====
# Single cell
print(df.iloc[0, 0])  # 'Alice' (first row, first col)

# Multiple rows/cols
print(df.iloc[0:3, 0:2])  # First 3 rows, first 2 cols
# IMPORTANT: Row 3 is EXCLUDED (0, 1, 2 only)

# Every Nth row (common in sampling)
every_2nd_row = df.iloc[::2]  # Rows 0, 2, 4

# Last N rows
last_3_rows = df.iloc[-3:]

# ===== at/iat (Scalar access - FASTEST) =====
# 10× faster for single cell than loc/iloc
name = df.at['emp_001', 'name']  # Use at for labels
age = df.iat[0, 1]                # Use iat for positions

# ===== Common Mistakes =====
# ❌ WRONG: Using loc with integer on non-integer index
try:
    df.loc[0]  # KeyError! Index is ['emp_001', 'emp_002', ...]
except KeyError:
    print("Error: loc expects labels, not positions")

# ✅ CORRECT
df.iloc[0]  # Always works

# ❌ WRONG: Assuming iloc is inclusive like loc
print(df.iloc[0:2])  # Only rows 0, 1 (NOT 0, 1, 2)

# ✅ CORRECT: Use loc for inclusive slicing
print(df.loc['emp_001':'emp_002'])  # Includes both endpoints

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'A': [1, np.nan, 3, np.nan],
    'B': ['x', None, 'y', 'z']
})

# Detection
df.isnull()              # Boolean mask
df.isnull().sum()        # Count per column
df.isnull().sum().sum()  # Total count
df.isna().any()          # Any missing per column

# 1. Drop rows with any missing
df.dropna()

# 2. Drop rows where specific columns are missing
df.dropna(subset=['A'])

# 3. Drop only if all values missing
df.dropna(how='all')

# 4. Fill with constant
df.fillna(0)
df.fillna({'A': 0, 'B': 'unknown'})

# 5. Fill with statistics
df['A'].fillna(df['A'].mean())
df['A'].fillna(df['A'].median())
df['A'].fillna(df['A'].mode()[0])

# 6. Forward/backward fill (time series)
df.ffill()  # Forward fill
df.bfill()  # Backward fill

# 7. Interpolation
df['A'].interpolate(method='linear')

import pandas as pd

df = pd.DataFrame({
    'category': ['A', 'A', 'B', 'B', 'A'],
    'product': ['x', 'y', 'x', 'y', 'x'],
    'sales': [100, 150, 200, 250, 120],
    'quantity': [10, 15, 20, 25, 12]
})

# Basic groupby
df.groupby('category')['sales'].sum()

# Multiple columns
df.groupby(['category', 'product'])['sales'].mean()

# Multiple aggregations
df.groupby('category').agg({
    'sales': ['sum', 'mean', 'count'],
    'quantity': 'sum'
})

# Named aggregations (cleaner output)
df.groupby('category').agg(
    total_sales=('sales', 'sum'),
    avg_sales=('sales', 'mean'),
    order_count=('sales', 'count')
)

# Custom functions
df.groupby('category')['sales'].apply(lambda x: x.max() - x.min())

# Transform: returns same shape (broadcast back)
df['sales_normalized'] = df.groupby('category')['sales'].transform(
    lambda x: (x - x.mean()) / x.std()
)

# Apply: returns any shape
df.groupby('category').apply(lambda g: g.nlargest(2, 'sales'))

import pandas as pd

df1 = pd.DataFrame({'key': ['A', 'B', 'C'], 'val1': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['A', 'B', 'D'], 'val2': [4, 5, 6]})

# MERGE - column-based joining
pd.merge(df1, df2, on='key', how='inner')  # Only A, B
pd.merge(df1, df2, on='key', how='left')   # Keep all from df1
pd.merge(df1, df2, on='key', how='outer')  # Keep all
pd.merge(df1, df2, left_on='key', right_on='key')  # Different column names

# JOIN - index-based (set index first)
df1.set_index('key').join(df2.set_index('key'))

# CONCAT - stacking
pd.concat([df1, df2])              # Vertical (default axis=0)
pd.concat([df1, df2], axis=1)      # Horizontal
pd.concat([df1, df2], ignore_index=True)  # Reset index

Inner: Only matching keys
Left:  All from left + matching from right
Right: All from right + matching from left
Outer: All from both (union)

result = pd.merge(df1, df2, on='key', how='outer', indicator=True)
# _merge column shows: 'left_only', 'right_only', 'both'

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'A': [1, 2, 3],
    'B': [4, 5, 6],
    'C': ['x', 'y', 'z']
})

# apply() - works on columns or rows
df[['A', 'B']].apply(np.sum)           # Sum each column
df[['A', 'B']].apply(np.sum, axis=1)   # Sum each row
df[['A', 'B']].apply(lambda x: x.max() - x.min())  # Custom function

# map() - Series only, element-wise
df['C'].map({'x': 'X', 'y': 'Y', 'z': 'Z'})  # Dictionary mapping
df['A'].map(lambda x: x ** 2)                 # Lambda function

# For DataFrames, use apply with axis
df[['A', 'B']].apply(lambda x: x ** 2)  # Each column

# In Pandas 2.1+, use map() instead of applymap()
df[['A', 'B']].map(lambda x: x * 2)

# SLOW - apply with lambda
df['A'].apply(lambda x: x ** 2)

# FAST - vectorized operation
df['A'] ** 2

# Use apply only when vectorization isn't possible

import pandas as pd

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

# Memory per column
df.memory_usage(deep=True)

# Total memory in MB
df.memory_usage(deep=True).sum() / 1024**2

# 1. Use appropriate dtypes on read
df = pd.read_csv('file.csv', dtype={
    'id': 'int32',           # Instead of int64
    'value': 'float32',      # Instead of float64
    'category': 'category'   # Instead of object
})

# 2. Convert existing columns
df['category'] = df['category'].astype('category')

# 3. Downcast numeric types
df['int_col'] = pd.to_numeric(df['int_col'], downcast='integer')
df['float_col'] = pd.to_numeric(df['float_col'], downcast='float')

# 4. Use sparse dtypes for mostly-empty columns
sparse_col = pd.arrays.SparseArray([0, 0, 0, 1, 0, 0, 0, 0])

def reduce_memory(df):
    for col in df.columns:
        col_type = df[col].dtype

        if col_type == 'object':
            if df[col].nunique() / len(df) < 0.5:  # Low cardinality
                df[col] = df[col].astype('category')

        elif col_type == 'int64':
            df[col] = pd.to_numeric(df[col], downcast='integer')

        elif col_type == 'float64':
            df[col] = pd.to_numeric(df[col], downcast='float')

    return df

import pandas as pd

# Process file in chunks
chunk_size = 100_000
chunks = []

for chunk in pd.read_csv('huge_file.csv', chunksize=chunk_size):
    # Process each chunk
    processed = chunk.groupby('category')['value'].sum()
    chunks.append(processed)

# Combine results
result = pd.concat(chunks).groupby(level=0).sum()

# Parquet - columnar, compressed
df.to_parquet('data.parquet', compression='snappy')
df = pd.read_parquet('data.parquet', columns=['col1', 'col2'])  # Read subset

# Feather - fast read/write
df.to_feather('data.feather')
df = pd.read_feather('data.feather')

import dask.dataframe as dd

# Lazy loading - doesn't load into memory
ddf = dd.read_csv('huge_file.csv')

# Same Pandas API
result = ddf.groupby('category')['value'].sum().compute()

# Use PyArrow for better memory efficiency
df = pd.read_csv('file.csv', dtype_backend='pyarrow')

# Or convert existing
df = df.convert_dtypes(dtype_backend='pyarrow')

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'date': ['2023-01', '2023-01', '2023-02', '2023-02'],
    'product': ['A', 'B', 'A', 'B'],
    'region': ['East', 'East', 'West', 'West'],
    'sales': [100, 150, 200, 250],
    'quantity': [10, 15, 20, 25]
})

# Basic pivot table
pt = pd.pivot_table(
    df,
    values='sales',
    index='date',
    columns='product',
    aggfunc='sum'
)

# Multiple aggregations
pt = pd.pivot_table(
    df,
    values=['sales', 'quantity'],
    index='date',
    columns='product',
    aggfunc={'sales': 'sum', 'quantity': 'mean'}
)

# With totals
pt = pd.pivot_table(
    df,
    values='sales',
    index='date',
    columns='product',
    aggfunc='sum',
    margins=True,
    margins_name='Total'
)

# pivot() - simple reshape (no duplicates allowed)
df.pivot(index='date', columns='product', values='sales')

# pivot_table() - handles duplicates
df.pivot_table(index='date', columns='product', values='sales', aggfunc='mean')

import pandas as pd

# Parse strings to datetime
df['date'] = pd.to_datetime(df['date_string'])
df['date'] = pd.to_datetime(df['date_string'], format='%Y-%m-%d')

# Create date range
dates = pd.date_range(start='2023-01-01', end='2023-12-31', freq='D')
dates = pd.date_range(start='2023-01-01', periods=12, freq='ME')

df['date'] = pd.to_datetime(df['date'])

# Extract components
df['year'] = df['date'].dt.year
df['month'] = df['date'].dt.month
df['day'] = df['date'].dt.day
df['dayofweek'] = df['date'].dt.dayofweek  # 0=Monday
df['quarter'] = df['date'].dt.quarter
df['is_weekend'] = df['date'].dt.dayofweek >= 5

# Formatting
df['month_name'] = df['date'].dt.month_name()
df['formatted'] = df['date'].dt.strftime('%Y-%m')

# Localize (no timezone -> timezone)
df['date'] = df['date'].dt.tz_localize('UTC')

# Convert between timezones
df['date_est'] = df['date'].dt.tz_convert('US/Eastern')

# Resample time series
df.set_index('date').resample('M')['sales'].sum()  # Monthly
df.set_index('date').resample('Q')['sales'].mean()  # Quarterly
df.set_index('date').resample('W')['sales'].agg(['sum', 'mean'])

import pandas as pd

df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})

# This triggers warning
filtered = df[df['A'] > 1]
filtered['B'] = 100  # Warning: setting on copy

# Solution 1: Explicit copy
filtered = df[df['A'] > 1].copy()
filtered['B'] = 100  # Safe

# Solution 2: Use .loc for assignment
df.loc[df['A'] > 1, 'B'] = 100  # Modifies original

# Solution 3: Chain in one line
df = df[df['A'] > 1].assign(B=100)

# Pandas 2.0+ with Copy-on-Write
pd.options.mode.copy_on_write = True
filtered = df[df['A'] > 1]
filtered['B'] = 100  # Creates copy automatically

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'date': pd.date_range('2023-01-01', periods=100),
    'price': np.random.randn(100).cumsum() + 100
})
df.set_index('date', inplace=True)

# Moving averages
df['MA_7'] = df['price'].rolling(window=7).mean()
df['MA_30'] = df['price'].rolling(window=30).mean()

# Other statistics
df['rolling_std'] = df['price'].rolling(7).std()
df['rolling_max'] = df['price'].rolling(7).max()
df['rolling_sum'] = df['price'].rolling(7).sum()

# Minimum periods (handle NaN at start)
df['MA_7_min3'] = df['price'].rolling(window=7, min_periods=3).mean()

# Centered window
df['MA_centered'] = df['price'].rolling(window=7, center=True).mean()

# Custom function with apply
df['rolling_range'] = df['price'].rolling(7).apply(
    lambda x: x.max() - x.min()
)

# Faster with raw=True (NumPy array)
df['rolling_custom'] = df['price'].rolling(7).apply(
    lambda x: np.percentile(x, 75), raw=True
)

# EMA - more weight to recent values
df['EMA_7'] = df['price'].ewm(span=7).mean()
df['EMA_decay'] = df['price'].ewm(alpha=0.1).mean()

import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 35],
    'department': ['Sales', 'IT', 'Sales'],
    'salary': [50000, 60000, 55000]
})

# Standard filtering (verbose)
df[(df['age'] > 25) & (df['department'] == 'Sales')]

# query() - cleaner syntax
df.query('age > 25 and department == "Sales"')

# Using variables with @
min_age = 25
dept = 'Sales'
df.query('age > @min_age and department == @dept')

# Column names with spaces
df.query('`Column Name` > 10')

# Create new column without intermediate copies
df.eval('bonus = salary * 0.1')

# Multiple expressions
df.eval('''
    bonus = salary * 0.1
    total_comp = salary + bonus
    age_group = age // 10 * 10
''', inplace=True)

# Conditional expressions
df.eval('is_senior = age >= 30')

# eval uses numexpr for large DataFrames
# Faster for: large datasets, complex expressions
# Similar for: small datasets, simple operations

# Check if numexpr is available
import pandas as pd
print(pd.get_option('compute.use_numexpr'))

import pandas as pd

df = pd.DataFrame({
    'name': ['John Smith', 'Jane Doe', 'Bob Johnson'],
    'email': ['[email protected]', '[email protected]', '[email protected]']
})

# Case transformations
df['name'].str.lower()
df['name'].str.upper()
df['name'].str.title()

# String matching
df['name'].str.contains('John')
df['name'].str.startswith('J')
df['email'].str.endswith('.com')

# Extraction
df['first_name'] = df['name'].str.split(' ').str[0]
df['domain'] = df['email'].str.extract(r'@(\w+\.\w+)')

# Replacement
df['name'].str.replace('John', 'Jonathan')
df['email'].str.replace(r'@\w+', '@company', regex=True)

# Length and padding
df['name'].str.len()
df['name'].str.pad(width=20, side='right', fillchar='.')

# Split into multiple columns
df[['first', 'last']] = df['name'].str.split(' ', expand=True)

# Split into list (no expand)
df['name_parts'] = df['name'].str.split(' ')

# Extract groups
df['phone'] = pd.Series(['123-456-7890', '987-654-3210'])
df[['area', 'exchange', 'number']] = df['phone'].str.extract(
    r'(\d{3})-(\d{3})-(\d{4})'
)

# Find all matches
df['numbers'] = df['phone'].str.findall(r'\d+')

import pandas as pd

df = pd.DataFrame({
    'group': ['A', 'A', 'B', 'B'],
    'value': [10, 20, 30, 40]
})

# Normalize within groups
df['normalized'] = df.groupby('group')['value'].transform(
    lambda x: (x - x.mean()) / x.std()
)

# Add group statistics as new column
df['group_mean'] = df.groupby('group')['value'].transform('mean')
df['group_sum'] = df.groupby('group')['value'].transform('sum')
df['pct_of_group'] = df['value'] / df.groupby('group')['value'].transform('sum')

# Result: same number of rows as original

# Return aggregated result
df.groupby('group')['value'].apply(lambda x: x.sum())

# Return different shape per group
df.groupby('group').apply(lambda g: g.nlargest(1, 'value'))

# Return multiple values per group
df.groupby('group')['value'].apply(lambda x: pd.Series({
    'mean': x.mean(),
    'range': x.max() - x.min()
}))

import pandas as pd
import numpy as np

ages = pd.Series([5, 17, 25, 35, 45, 55, 65, 75, 85])

# Explicit bin edges
bins = [0, 18, 35, 50, 65, 100]
labels = ['Child', 'Young Adult', 'Adult', 'Middle Aged', 'Senior']

pd.cut(ages, bins=bins, labels=labels)

# Equal-width bins
pd.cut(ages, bins=5)  # 5 equal-width bins

# Include lowest value
pd.cut(ages, bins=bins, labels=labels, include_lowest=True)

# Return bin boundaries
pd.cut(ages, bins=5, retbins=True)

# Equal-sized bins (same number of items)
values = pd.Series([1, 2, 3, 4, 5, 6, 7, 8, 9, 100])

pd.qcut(values, q=4)  # Quartiles
pd.qcut(values, q=4, labels=['Q1', 'Q2', 'Q3', 'Q4'])
pd.qcut(values, q=[0, 0.25, 0.5, 0.75, 1])  # Custom quantiles

# Handle duplicates
pd.qcut(values, q=4, duplicates='drop')

# cut: bins by value range
pd.cut([1, 5, 10, 50, 100], bins=3)
# (0.9, 34], (0.9, 34], (0.9, 34], (34, 67], (67, 100]

# qcut: bins by count
pd.qcut([1, 5, 10, 50, 100], q=3)
# (0.99, 7.5], (0.99, 7.5], (7.5, 30], (30, 100], (30, 100]

import pandas as pd

# Convert to categorical
df = pd.DataFrame({'status': ['active', 'inactive', 'active', 'pending'] * 10000})

# Memory before
print(df.memory_usage(deep=True).sum())  # ~360KB

# Convert
df['status'] = df['status'].astype('category')

# Memory after
print(df.memory_usage(deep=True).sum())  # ~40KB (90% reduction!)

from pandas.api.types import CategoricalDtype

# Define ordered category
size_type = CategoricalDtype(
    categories=['small', 'medium', 'large', 'xlarge'],
    ordered=True
)

df['size'] = df['size'].astype(size_type)

# Now comparisons work
df[df['size'] > 'medium']  # Returns 'large' and 'xlarge'
df['size'].min(), df['size'].max()

# One-hot encoding
df = pd.DataFrame({'color': ['red', 'blue', 'green', 'red']})

pd.get_dummies(df, columns=['color'])
# Result: color_blue, color_green, color_red columns

# Drop first to avoid multicollinearity
pd.get_dummies(df, columns=['color'], drop_first=True)

# Prefix
pd.get_dummies(df, columns=['color'], prefix='c')

# Add/remove categories
df['status'].cat.add_categories(['new_status'])
df['status'].cat.remove_unused_categories()

# Rename categories
df['status'].cat.rename_categories({'active': 'ACTIVE'})

import pandas as pd

# From tuples
index = pd.MultiIndex.from_tuples([
    ('A', 2021), ('A', 2022), ('B', 2021), ('B', 2022)
], names=['category', 'year'])

df = pd.DataFrame({'sales': [100, 150, 200, 250]}, index=index)

# From product (all combinations)
index = pd.MultiIndex.from_product(
    [['A', 'B'], [2021, 2022, 2023]],
    names=['category', 'year']
)

# From GroupBy
df = pd.DataFrame({
    'category': ['A', 'A', 'B', 'B'],
    'year': [2021, 2022, 2021, 2022],
    'sales': [100, 150, 200, 250]
})
df_multi = df.set_index(['category', 'year'])

# Select outer level
df.loc['A']  # All years for category A

# Select specific combination
df.loc[('A', 2021)]

# Cross-section
df.xs(2021, level='year')  # All categories for 2021

# Slice
df.loc['A':'B']  # Range of outer index

# Reset to regular columns
df.reset_index()

# Flatten column MultiIndex
df.columns = ['_'.join(col).strip() for col in df.columns.values]

# Wide to long (stack)
df.stack()

# Long to wide (unstack)
df.unstack()
df.unstack(level='year')

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.randn(100000, 10))

# Time operations
%timeit df.sum()

# Line-by-line profiling
%lprun -f function_name function_name(df)

# Memory profiling
%memit df.copy()

# 1. Avoid loops - use vectorization
# SLOW
for i in range(len(df)):
    df.loc[i, 'new_col'] = df.loc[i, 'A'] * 2

# FAST
df['new_col'] = df['A'] * 2

# 2. Use NumPy for complex operations
# SLOW
df['result'] = df.apply(lambda row: complex_function(row), axis=1)

# FAST
df['result'] = np.where(df['A'] > 0, df['A'] * 2, df['A'] * 3)

# 3. Use categorical for strings
df['category'] = df['category'].astype('category')

# 4. Read only needed columns
df = pd.read_csv('file.csv', usecols=['col1', 'col2'])

# 5. Use query() for complex filters
df.query('A > 0 and B < 100')  # Faster than boolean indexing

# Numba JIT compilation
from numba import jit

@jit(nopython=True)
def fast_calculation(arr):
    result = np.empty(len(arr))
    for i in range(len(arr)):
        result[i] = arr[i] ** 2 + arr[i] * 2
    return result

df['result'] = fast_calculation(df['A'].values)

# Swifter for automatic parallelization
import swifter
df['result'] = df['A'].swifter.apply(complex_function)

import pandas as pd

# Wide format
df_wide = pd.DataFrame({
    'name': ['Alice', 'Bob'],
    '2021_sales': [100, 200],
    '2022_sales': [150, 250],
    '2023_sales': [180, 300]
})

# Convert to long format
df_long = pd.melt(
    df_wide,
    id_vars=['name'],
    value_vars=['2021_sales', '2022_sales', '2023_sales'],
    var_name='year',
    value_name='sales'
)

# Result:
#    name        year  sales
# 0  Alice  2021_sales    100
# 1    Bob  2021_sales    200
# ...

df_long['year'] = df_long['year'].str.replace('_sales', '').astype(int)

import pandas as pd

df = pd.DataFrame({
    'A': [1, 2],
    'B': [3, 4]
}, index=['row1', 'row2'])

# Stack columns into rows
stacked = df.stack()
# Result: MultiIndex Series
# row1  A    1
#       B    3
# row2  A    2
#       B    4

# Reverse operation
unstacked = stacked.unstack()

# Unstack specific level
df_multi = df.set_index([['cat1', 'cat1', 'cat2', 'cat2'], [1, 2, 1, 2]])
df_multi.unstack(level=0)  # Unstack first level
df_multi.unstack(level=1)  # Unstack second level

import pandas as pd

df = pd.DataFrame({
    'gender': ['M', 'F', 'M', 'F', 'M', 'F'],
    'department': ['Sales', 'Sales', 'IT', 'IT', 'Sales', 'HR'],
    'salary': [50000, 55000, 60000, 65000, 52000, 48000]
})

# Simple frequency table
pd.crosstab(df['gender'], df['department'])

# With margins (totals)
pd.crosstab(df['gender'], df['department'], margins=True)

# With aggregation
pd.crosstab(
    df['gender'], 
    df['department'], 
    values=df['salary'],
    aggfunc='mean'
)

# Normalize to percentages
pd.crosstab(df['gender'], df['department'], normalize='all')  # All cells
pd.crosstab(df['gender'], df['department'], normalize='index')  # By row
pd.crosstab(df['gender'], df['department'], normalize='columns')  # By column

import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob'],
    'skills': [['Python', 'SQL', 'ML'], ['Java', 'Scala']]
})

# Explode list column
df_exploded = df.explode('skills')

# Result:
#    name  skills
# 0  Alice  Python
# 0  Alice     SQL
# 0  Alice      ML
# 1    Bob    Java
# 1    Bob   Scala

# Reset index after explode
df_exploded = df.explode('skills').reset_index(drop=True)

# Explode multiple columns (same length)
df = pd.DataFrame({
    'id': [1, 2],
    'values': [[1, 2], [3, 4]],
    'labels': [['a', 'b'], ['c', 'd']]
})
df.explode(['values', 'labels'])

import pandas as pd
from pandas import json_normalize

data = [
    {
        'id': 1,
        'name': 'Alice',
        'address': {
            'city': 'NYC',
            'zip': '10001'
        },
        'orders': [{'id': 101, 'amount': 50}, {'id': 102, 'amount': 75}]
    }
]

# Flatten top level
df = json_normalize(data)

# Flatten with nested paths
df = json_normalize(
    data,
    record_path='orders',  # Explode this array
    meta=['id', 'name', ['address', 'city']],  # Include these fields
    meta_prefix='user_'
)

# Read JSON file
df = pd.read_json('data.json')
df = pd.read_json('data.json', orient='records')

import pandas as pd

# Instead of multiple intermediate variables
df = (
    pd.read_csv('data.csv')
    .query('status == "active"')
    .assign(
        year=lambda x: pd.to_datetime(x['date']).dt.year,
        total=lambda x: x['price'] * x['quantity']
    )
    .groupby(['year', 'region'])
    .agg(revenue=('total', 'sum'))
    .reset_index()
    .sort_values('revenue', ascending=False)
)

def add_features(df):
    return df.assign(
        log_value=np.log1p(df['value']),
        is_high=df['value'] > df['value'].median()
    )

def filter_outliers(df, column, n_std=3):
    mean, std = df[column].mean(), df[column].std()
    return df[(df[column] - mean).abs() <= n_std * std]

result = (
    df
    .pipe(add_features)
    .pipe(filter_outliers, 'value', n_std=2)
)

import pandas as pd

# Write to Parquet
df.to_parquet('data.parquet')
df.to_parquet('data.parquet', compression='snappy')  # Default
df.to_parquet('data.parquet', compression='gzip')    # Smaller

# Read Parquet
df = pd.read_parquet('data.parquet')

# Read only specific columns (fast!)
df = pd.read_parquet('data.parquet', columns=['col1', 'col2'])

# With filters (predicate pushdown)
df = pd.read_parquet(
    'data.parquet',
    filters=[('year', '==', 2023)]
)

import pandas as pd

df = pd.DataFrame({
    'price': [100, 200, 300],
    'quantity': [2, 3, 1]
})

# Create multiple columns
df = df.assign(
    total=lambda x: x['price'] * x['quantity'],
    discounted=lambda x: x['total'] * 0.9,
    tax=lambda x: x['total'] * 0.1
)

# Reference previous assignments
df = df.assign(
    subtotal=lambda x: x['price'] * x['quantity'],
    tax=lambda x: x['subtotal'] * 0.1,
    total=lambda x: x['subtotal'] + x['tax']
)

# Direct assignment (modifies in place)
df['new_col'] = df['price'] * 2

# assign() (returns new DataFrame, original unchanged)
df_new = df.assign(new_col=df['price'] * 2)

import pandas as pd

df = pd.DataFrame({
    'date': pd.date_range('2023-01-01', periods=5),
    'price': [100, 105, 102, 110, 108]
})

# Daily percentage change
df['pct_change'] = df['price'].pct_change()

# Percentage change over N periods
df['pct_change_3'] = df['price'].pct_change(periods=3)

# Fill first NaN
df['pct_change'] = df['price'].pct_change().fillna(0)

# Cumulative sum
df['cumsum'] = df['price'].cumsum()

# Cumulative product (for returns)
df['cumulative_return'] = (1 + df['pct_change']).cumprod() - 1

# Cumulative max/min
df['cummax'] = df['price'].cummax()
df['cummin'] = df['price'].cummin()

# Drawdown
df['drawdown'] = df['price'] / df['price'].cummax() - 1

import pandas as pd

df = pd.DataFrame({
    'date': pd.date_range('2023-01-01', periods=5),
    'value': [10, 20, 30, 40, 50]
})

# Previous value (lag)
df['prev_value'] = df['value'].shift(1)
df['prev_2'] = df['value'].shift(2)

# Next value (lead)
df['next_value'] = df['value'].shift(-1)

# Calculate difference from previous
df['diff'] = df['value'] - df['value'].shift(1)
# Same as: df['value'].diff()

# Shift with fill
df['prev_filled'] = df['value'].shift(1, fill_value=0)

import pandas as pd

df = pd.DataFrame({'A': range(1000)})

# Sample n rows
sample = df.sample(n=100)

# Sample fraction
sample = df.sample(frac=0.1)  # 10%

# With replacement (bootstrap)
bootstrap = df.sample(n=1000, replace=True)

# Reproducible sampling
sample = df.sample(n=100, random_state=42)

# Weighted sampling
df['weight'] = [0.1] * 500 + [0.9] * 500
sample = df.sample(n=100, weights='weight')

# Sample within groups
df.groupby('category').sample(n=10)
df.groupby('category').sample(frac=0.1)

# Stratified with sklearn
from sklearn.model_selection import train_test_split
train, test = train_test_split(df, stratify=df['category'], test_size=0.2)

import pandas as pd
import numpy as np

df = pd.DataFrame({'value': [1, 2, 3, 100, 4, 5, -50, 6]})

# Z-score method
z_scores = (df['value'] - df['value'].mean()) / df['value'].std()
outliers = df[abs(z_scores) > 3]

# IQR method
Q1 = df['value'].quantile(0.25)
Q3 = df['value'].quantile(0.75)
IQR = Q3 - Q1
lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR
outliers = df[(df['value'] < lower) | (df['value'] > upper)]

# Percentile method
outliers = df[(df['value'] < df['value'].quantile(0.01)) | 
              (df['value'] > df['value'].quantile(0.99))]

# Remove
df_clean = df[(df['value'] >= lower) & (df['value'] <= upper)]

# Cap (winsorize)
df['value_capped'] = df['value'].clip(lower=lower, upper=upper)

# Replace with NaN
df.loc[abs(z_scores) > 3, 'value'] = np.nan

import pandas as pd

df = pd.DataFrame({'value': [10, 20, 30, 40, 50]})

# Scale to [0, 1]
df['normalized'] = (df['value'] - df['value'].min()) / \
                   (df['value'].max() - df['value'].min())

# Using sklearn
from sklearn.preprocessing import MinMaxScaler
scaler = MinMaxScaler()
df['normalized'] = scaler.fit_transform(df[['value']])

# Scale to mean=0, std=1
df['standardized'] = (df['value'] - df['value'].mean()) / df['value'].std()

# Using sklearn
from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
df['standardized'] = scaler.fit_transform(df[['value']])

import pandas as pd
import numpy as np

df = pd.DataFrame({'A': [1, 2, 3, 4, 5]})

# Keep values >= 3, replace others with NaN
df['A'].where(df['A'] >= 3)
# Result: [NaN, NaN, 3, 4, 5]

# Replace with specific value
df['A'].where(df['A'] >= 3, other=0)
# Result: [0, 0, 3, 4, 5]

# Opposite of where
# Replace values >= 3 with NaN
df['A'].mask(df['A'] >= 3)
# Result: [1, 2, NaN, NaN, NaN]

# Replace with specific value
df['A'].mask(df['A'] >= 3, other=999)
# Result: [1, 2, 999, 999, 999]

# np.where for if-else
df['result'] = np.where(df['A'] >= 3, 'high', 'low')

# Multiple conditions: np.select
conditions = [df['A'] < 2, df['A'] < 4]
choices = ['low', 'medium']
df['category'] = np.select(conditions, choices, default='high')

import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'David'],
    'score': [85, 90, 85, 95]
})

# Default: average rank for ties
df['rank'] = df['score'].rank()
# [1.5, 3, 1.5, 4] - tied scores get average

# Rank methods for ties
df['rank_min'] = df['score'].rank(method='min')    # [1, 3, 1, 4]
df['rank_max'] = df['score'].rank(method='max')    # [2, 3, 2, 4]
df['rank_first'] = df['score'].rank(method='first') # [1, 3, 2, 4]
df['rank_dense'] = df['score'].rank(method='dense') # [1, 2, 1, 3]

# Descending rank
df['rank_desc'] = df['score'].rank(ascending=False)

# Rank within groups
df['rank_in_group'] = df.groupby('category')['score'].rank()

# Percentile rank
df['percentile'] = df['score'].rank(pct=True)

import pandas as pd

df = pd.DataFrame({
    'category': ['A', 'A', 'A', 'B', 'B', 'B'],
    'value': [10, 30, 20, 40, 60, 50]
})

# Top 2 per group using nlargest
df.groupby('category').apply(lambda x: x.nlargest(2, 'value'))

# Top N using head after sort
df.sort_values('value', ascending=False).groupby('category').head(2)

# First/last row per group
df.groupby('category').first()
df.groupby('category').last()
df.groupby('category').nth(0)  # First row
df.groupby('category').nth(-1)  # Last row

# More efficient for large data
df['rank'] = df.groupby('category')['value'].rank(method='first', ascending=False)
top_2 = df[df['rank'] <= 2]