Advanced Data Analysis using Numpy & Pandas

Duration: 45 min

Advanced Data Analysis using Numpy & Pandas

Duration: 45 min

GroupBy Operations

GroupBy is one of the most powerful tools in Pandas. It allows you to split your data into groups based on one or more columns, apply a function to each group, and combine the results. This is the foundation of exploratory data analysis.

import pandas as pd
import numpy as np
Create sample dataset

df = pd.DataFrame({
    'department': ['Sales', 'Sales', 'IT', 'IT', 'HR', 'HR', 'HR'],
    'employee': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve', 'Frank', 'Grace'],
    'salary': [50000, 55000, 70000, 75000, 45000, 48000, 50000],
    'year': [2022, 2023, 2022, 2023, 2022, 2023, 2023]
})
Group by single column

grouped = df.groupby('department')
print(grouped['salary'].mean())

department


HR       47666.666667


IT       72500.000000


Sales    52500.000000
Group by multiple columns

grouped = df.groupby(['department', 'year'])
print(grouped['salary'].sum())
Apply multiple aggregations

agg_result = df.groupby('department').agg({
    'salary': ['mean', 'sum', 'count'],
    'employee': 'count'
})
print(agg_result)
Custom aggregation with apply()

def salary_range(group):
    return group['salary'].max() - group['salary'].min()
result = df.groupby('department')['salary'].apply(salary_range)
print(result)
Filter groups

large_depts = df.groupby('department').filter(lambda x: len(x) > 2)
print(large_depts)

Pivot Tables

Pivot tables reshape data to summarize and compare. They're essential for creating crosstabs and multi-dimensional summaries.

import pandas as pd
df = pd.DataFrame({
    'date': ['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02',
             '2023-01-03', '2023-01-03'],
    'product': ['A', 'B', 'A', 'B', 'A', 'B'],
    'sales': [100, 150, 120, 140, 110, 160],
    'region': ['North', 'North', 'South', 'North', 'South', 'South']
})
Basic pivot table

pivot = df.pivot_table(values='sales', index='date', columns='product', aggfunc='sum')
print(pivot)

product     A    B


date


2023-01-01  100  150


2023-01-02  120  140


2023-01-03  110  160
Pivot with multiple index levels

pivot2 = df.pivot_table(values='sales', index=['date', 'region'], 
                        columns='product', aggfunc='mean')
Pivot with multiple aggregations

pivot3 = df.pivot_table(values='sales', index='date', columns='product',
                        aggfunc=['sum', 'mean', 'count'])
unstack (convert index to columns)

unstacked = df.set_index(['date', 'product'])['sales'].unstack()
print(unstacked)
stack (convert columns to index)

stacked = pivot.stack()
print(stacked)

Merge and Join Operations

Merging combines datasets based on common columns or indices. It's analogous to SQL joins.

import pandas as pd
Sample datasets

df1 = pd.DataFrame({
    'emp_id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie'],
    'dept_id': [10, 20, 10]
})
df2 = pd.DataFrame({
    'dept_id': [10, 20, 30],
    'dept_name': ['Sales', 'IT', 'HR']
})
Inner join (only matching rows)

result = pd.merge(df1, df2, on='dept_id', how='inner')
print(result)

  emp_id     name  dept_id dept_name


0      1    Alice       10     Sales


1      2      Bob       20       IT
Left join (all rows from left df)

result = pd.merge(df1, df2, on='dept_id', how='left')
Right join (all rows from right df)

result = pd.merge(df1, df2, on='dept_id', how='right')
Outer join (all rows from both)

result = pd.merge(df1, df2, on='dept_id', how='outer')
Merge on different column names

df3 = pd.DataFrame({'department_id': [10, 20], 'budget': [100000, 150000]})
result = pd.merge(df1, df3, left_on='dept_id', right_on='department_id')
Concatenate dataframes (stack vertically)

df_concat = pd.concat([df1, df1], ignore_index=True)

Window Functions and Rolling Operations

Window functions compute values over a moving window of data, useful for time series analysis:

import pandas as pd
df = pd.DataFrame({
    'date': pd.date_range('2023-01-01', periods=10),
    'price': [100, 102, 101, 105, 103, 108, 110, 107, 112, 115],
    'volume': [1000, 1200, 950, 1500, 1100, 1400, 1600, 1300, 1800, 1900]
})
Rolling mean (moving average)

df['rolling_mean'] = df['price'].rolling(window=3).mean()
print(df[['date', 'price', 'rolling_mean']])
Rolling sum

df['rolling_sum'] = df['volume'].rolling(window=3).sum()
Expanding window (cumulative)

df['cumulative_max'] = df['price'].expanding().max()
df['cumulative_sum'] = df['volume'].expanding().sum()
Shift (lag values)

df['price_lag1'] = df['price'].shift(1)  # Previous value
df['price_change'] = df['price'] - df['price'].shift(1)
Pct_change (percentage change)

df['price_pct_change'] = df['price'].pct_change() * 100
Rank

df['price_rank'] = df['price'].rank()
print(df.head())

Vectorized String Operations

Pandas provides .str accessor for efficient string operations on Series:

import pandas as pd
df = pd.DataFrame({
    'name': ['Alice Johnson', 'Bob Smith', 'Charlie Brown'],
    'email': ['alice@example.com', 'bob@example.com', 'charlie@example.com']
})
String methods

df['first_name'] = df['name'].str.split().str[0]
df['last_name'] = df['name'].str.split().str[1]
df['name_upper'] = df['name'].str.upper()
df['name_lower'] = df['name'].str.lower()
df['name_length'] = df['name'].str.len()
Pattern matching

df['has_a'] = df['name'].str.contains('a', case=False)
df['domain'] = df['email'].str.extract(r'@(.+)')
Replace

df['email_masked'] = df['email'].str.replace(r'(\w).@', r'\1@', regex=True)
print(df)

Performance: NumPy vs Pandas Vectorization

The speed difference between scalar operations and vectorized operations is dramatic:

import pandas as pd
import numpy as np
import time
Create large dataset

n = 1000000
df = pd.DataFrame({
    'a': np.random.randn(n),
    'b': np.random.randn(n)
})
Slow: Python loop

start = time.time()
result_slow = []
for i in range(len(df)):
    result_slow.append(df.iloc[i]['a'] * 2 + df.iloc[i]['b'])
slow_time = time.time() - start
print(f"Python loop: {slow_time:.4f} seconds")
Fast: Vectorized

start = time.time()
result_fast = df['a'] * 2 + df['b']
fast_time = time.time() - start
print(f"Vectorized: {fast_time:.4f} seconds")
print(f"Speedup: {slow_time / fast_time:.1f}x")
Vectorization also works with apply() using NumPy-aware functions

df['result'] = np.sqrt(df['a']2 + df['b']2)

Memory Optimization

For large datasets, optimizing dtypes can dramatically reduce memory usage:

import pandas as pd
df = pd.DataFrame({
    'id': [1, 2, 3, 4, 5],
    'category': ['A', 'B', 'A', 'C', 'B'],
    'value': [10.5, 20.3, 15.7, 25.1, 30.9]
})
Check memory before optimization

print(df.memory_usage(deep=True))
Optimize dtypes

df['id'] = df['id'].astype('int32')  # int64 -> int32
df['category'] = df['category'].astype('category')  # object -> category
df['value'] = df['value'].astype('float32')  # float64 -> float32
Check memory after optimization

print(df.memory_usage(deep=True))
print(f"Memory savings: {(1 - df.memory_usage().sum() / df_original.memory_usage().sum()) * 100:.1f}%")
For read_csv, specify dtypes upfront

dtypes = {
    'id': 'int32',
    'category': 'category',
    'value': 'float32'
}
df = pd.read_csv('large_file.csv', dtype=dtypes)

Quiz

Question 1: What does df.groupby('column').sum() do?

  • A) Sums the entire DataFrame
  • B) Groups by column and sums numeric columns within each group ✓
  • C) Creates a pivot table
  • D) Counts rows per group

Question 2: In pd.merge(df1, df2, how='left'), what rows are included?

  • A) Only rows present in both DataFrames
  • B) All rows from df1, matched rows from df2 ✓
  • C) All rows from both DataFrames
  • D) Only rows not in both DataFrames

Question 3: What does df['price'].rolling(window=3).mean() compute?

  • A) The global mean
  • B) Moving average over windows of 3 rows ✓
  • C) The standard deviation
  • D) Cumulative values

Question 4: Which operation converts categories to data type to save memory?

  • A) df['col'].astype('int32')
  • B) df['col'].astype('category') ✓
  • C) df['col'].astype('float32')
  • D) df['col'].astype('object')

Question 5: What does df['name'].str.upper() do?

  • A) Returns the number of uppercase letters
  • B) Converts all strings to uppercase ✓
  • C) Checks if strings are uppercase
  • D) Removes uppercase letters

Question 6: Vectorized operations are faster than loops because:

  • A) They use Python's native speed
  • B) They run in optimized C code without interpreter overhead ✓
  • C) They use more memory
  • D) They parallelize automatically

← Back ▶ Run in Colab