pandas: Indexing, Selection & GroupBy

Selection & Filtering

# Column selection
df['name']                          # Series
df[['name', 'salary']]              # DataFrame (list of columns)

# Row selection by position (iloc)
df.iloc[0]                          # first row as Series
df.iloc[0:5]                        # first 5 rows
df.iloc[:, 0:2]                     # all rows, first 2 columns
df.iloc[[0, 2, 4]]                  # specific rows
df.iloc[0, 1]                       # row 0, col 1 (scalar)

# Row selection by label (loc)
df.loc[0]                           # row with index label 0
df.loc[0:5]                         # rows 0–5 inclusive (label-based!)
df.loc[0, 'salary']                 # specific cell
df.loc[[0, 2], ['name', 'salary']]  # specific rows and columns

# Boolean filtering
df[df['salary'] > 60000]
df[(df['salary'] > 60000) & (df['age'] < 35)]
df[(df['dept'] == 'Engineering') | (df['dept'] == 'Design')]
df[df['status'].isin(['active', 'pending'])]
df[~df['name'].str.contains('Alice')]  # negate

# query() — readable string-based filtering
df.query('salary > 60000 and age < 35')
df.query('dept in ["Engineering", "Design"]')
df.query('name == @name_variable')   # use variable with @

# where / mask
df.where(df > 0, other=0)           # keep values > 0, replace rest with 0
df.mask(df > 0, other=np.nan)       # replace values > 0 with NaN

Apply & Vectorized Operations

# apply — row or column-wise (slow; prefer vectorized)
df['grade'] = df['score'].apply(lambda x: 'A' if x >= 90 else 'B' if x >= 80 else 'C')
df['full_name'] = df.apply(lambda row: f"{row['first']} {row['last']}", axis=1)

# map — element-wise on Series
df['dept_code'] = df['dept'].map({'Engineering': 'ENG', 'Design': 'DES'})

# Vectorized string operations (.str)
df['name'].str.upper()
df['name'].str.lower()
df['email'].str.split('@').str[1]   # domain
df['name'].str.startswith('A')
df['desc'].str.contains('python', case=False, na=False)
df['name'].str.strip()
df['phone'].str.replace(r'[^0-9]', '', regex=True)
df['name'].str.len()

# Date operations (.dt)
df['date'] = pd.to_datetime(df['date'])
df['year'] = df['date'].dt.year
df['month'] = df['date'].dt.month
df['day_of_week'] = df['date'].dt.day_name()
df['days_since'] = (pd.Timestamp.now() - df['date']).dt.days

GroupBy & Aggregation

# GroupBy basics
grouped = df.groupby('dept')
grouped['salary'].mean()              # mean salary per dept
grouped['salary'].agg(['mean', 'min', 'max', 'count'])

# Multiple columns
df.groupby(['dept', 'level'])['salary'].mean()

# agg with different functions per column
df.groupby('dept').agg({
    'salary': ['mean', 'sum'],
    'age': 'mean',
    'name': 'count',
})

# Named aggregations (pandas 0.25+)
df.groupby('dept').agg(
    avg_salary=('salary', 'mean'),
    total_salary=('salary', 'sum'),
    headcount=('name', 'count'),
    max_age=('age', 'max'),
)

# Custom aggregation
df.groupby('dept')['salary'].agg(lambda x: x.quantile(0.9))  # 90th percentile

# transform — broadcast result back to original index
df['dept_avg_salary'] = df.groupby('dept')['salary'].transform('mean')
df['salary_deviation'] = df['salary'] - df['dept_avg_salary']

# filter — keep groups satisfying a condition
df.groupby('dept').filter(lambda x: x['salary'].mean() > 70000)

# Pivot table
pd.pivot_table(df,
    values='salary',
    index='dept',
    columns='level',
    aggfunc='mean',
    fill_value=0,
)

# crosstab
pd.crosstab(df['dept'], df['gender'], margins=True)