Environment Setup

This guide uses the following software versions:

• Python 3.11
• Pandas 2.2.2
• NumPy 1.23.5

Target Audience

• Beginners new to Python programming
• Individuals familiar with Excel who want to transition into data analysis using code, especially those lacking practical project experience

Key Learning Approach

• Leverage existing Excel knowledge as a foundation
• Reproduce common Excel operations (e.g., pivot tables) using pandas
• Extend beyond Excel capabilities with advanced data manipulation
• Cover full workflow: file merging, filtering, transformation, and complex aggregation

Why CSV Over Excel?

While Excel files are compact, CSV offers significantly faster I/O performance. Consider this benchmark:

File	Hardware	Python	Pandas
320MB foo.csv, 16MB foo.xlsx	i7-7700k, SSD	Anaconda Python 3.5.3	Pandas 0.19.2

Operation	Time
pd.read_csv('foo.csv')	2 seconds
pd.read_excel('foo.xlsx')	15.3 seconds
df.to_csv('hehe.csv', index=False)	10.5 seconds
df.to_excel('hehe.xlsx', sheet_name='sheet1', index=False)	34.5 seconds

CSV is preferred in real-world scenarios like Kaggle competitions or enterprise data exports due to its speed and simplicity.

Understanding DataFrame Basics

A pandas.DataFrame is a two-dimensional labeled data structure. Think of it as a spreadsheet with rows (index) and columns. For example, reading a CSV creates a DataFrame where each row corresponds to a record and each column to a variable.

Stage 1: Data Preparation – Merging and Splitting Files

Loading Initial Datasets

import pandas as pd
import os

# Load store and training datasets
df_stores = pd.read_csv('data/stores.csv')
df_train = pd.read_csv('data/train.csv')

Sample Output – Stores

Store	Type	Size
1	A	151315
2	A	202307
3	B	37392
4	A	205863
5	B	34875

Sample Output – Training Data

Store	Dept	Date	Weekly_Sales	IsHoliday
1	1	2010-02-05	24924.50	False
1	1	2010-02-12	46039.49	True
1	1	2010-02-19	41595.55	False
1	1	2010-02-26	19403.54	False
1	1	2010-03-05	21827.90	False

1.1 Combining Multiple Files

Merging Two DataFrames Vertically

combined_list = [df_train.head(), df_train.head()]
result = pd.concat(combined_list)

Output

Store	Dept	Date	Weekly_Sales	IsHoliday
1	1	2010-02-05	24924.50	False
1	1	2010-02-12	46039.49	True
1	1	2010-02-19	41595.55	False
1	1	2010-02-26	19403.54	False
1	1	2010-03-05	21827.90	False
1	1	2010-02-05	24924.50	False
1	1	2010-02-12	46039.49	True
1	1	2010-02-19	41595.55	False
1	1	2010-02-26	19403.54	False
1	1	2010-03-05	21827.90	False

Key Function: pd.concat()

pd.concat(
    objs,       # List of DataFrames
    axis=0,     # 0 for vertical (row-wise), 1 for horizontal (column-wise)
    join='outer' # 'inner' for intersection, 'outer' for union
)

Merging on Common Keys (Like VLOOKUP)

merged_data = df_train.merge(df_stores, on='Store', how='left')

Result

Store	Dept	Date	Weekly_Sales	IsHoliday	Type	Size
1	1	2010-02-05	24924.50	False	A	151315
1	1	2010-02-12	46039.49	True	A	151315
1	1	2010-02-19	41595.55	False	A	151315
1	1	2010-02-26	19403.54	False	A	151315
1	1	2010-03-05	21827.90	False	A	151315

Automated Merge of All CSVs in a Folder

file_directory = 'data/'
dataframes = []

for filename in os.listdir(file_directory):
    if filename.endswith('.csv'):
        filepath = os.path.join(file_directory, filename)
        df = pd.read_csv(filepath, low_memory=False)
        dataframes.append(df.head(3))  # Use first 3 rows for testing
        print(f"{filename} loaded.")

final_combined = pd.concat(dataframes, ignore_index=True)

1.2 Splitting One File Into Many

Use Case: Save separate files per store.

Step 1: Identify Unique Store IDs

unique_stores = df_train['Store'].unique()

Step 2: Verify Filtering Logic

store_id = 1
subset = df_train[df_train['Store'] == store_id]
assert subset['Store'].nunique() == 1  # Confirm only one store

Step 3: Export Each Store's Data

output_path = 'output/'
for sid in unique_stores:
    filtered_df = df_train[df_train['Store'] == sid]
    filtered_df.to_csv(f"{output_path}store_{sid}.csv", index=False, encoding='utf-8-sig')

Stage 2: Advanced Filtering, Replacement, and Feature Engineering

2.1 Column Selection

selected_cols = merged_data[['Store', 'Weekly_Sales', 'Dept', 'Type']]
# Or using .loc
selected_loc = merged_data.loc[:, ['Store', 'Weekly_Sales', 'Dept', 'Type']]

2.2 Row Filtering

filtered_rows = merged_data[merged_data['Store'] == 1]
# Equivalent using query
filtered_query = merged_data.query("Store == 1")

2.3 Multi-Condition Filtering

Select Specific Rows and Columns

merged_data.loc[merged_data['Store'] == 1, ['Store', 'Weekly_Sales', 'Type']]

Drill Down with Multiple Conditions

cond1 = merged_data['Store'] == 1
cond2 = merged_data['IsHoliday'] == True
holiday_sales_store1 = merged_data.loc[cond1 & cond2]

2.4 Conditional Value Updates

Requirement: Set weekly sales to 1 when Store=1 and IsHoliday=True.

updated_df = merged_data.copy()
updated_df.loc[cond1 & cond2, 'Weekly_Sales'] = 1

2.5 Creating Categorical Labels

Method 1: Sequential Assignment (Simple Cases)

merged_data['Store_Category'] = merged_data['Type']
merged_data.loc[merged_data['Store_Category'] == 'A', 'Store_Category'] = 'Hypermarket'
merged_data.loc[merged_data['Store_Category'] == 'B', 'Store_Category'] = 'Supermarket'
merged_data.loc[merged_data['Store_Category'] == 'C', 'Store_Category'] = 'Convenience'

Method 2: Using numpy.select() (Efficient for Complex Logic)

import numpy as np

is_hyper = merged_data['Type'] == 'A'
is_super = merged_data['Type'] == 'B'

merged_data['Store_Category'] = np.select(
    [is_hyper, is_super],
    ['Hypermarket', 'Supermarket'],
    default='Convenience'
)

Stage 3: Building Dynamic Pivot Tables

Data Preprocessing

merged_data['Date'] = pd.to_datetime(merged_data['Date'])
merged_data['Year'] = merged_data['Date'].dt.year
merged_data['Quarter'] = merged_data['Date'].dt.quarter

3.1 Basic Pivot Table

pivot_base = merged_data.pivot_table(
    index=['Store_Category', 'Quarter'],
    columns='Year',
    values='Weekly_Sales',
    aggfunc='sum'
)

3.2 Add Row Totals

pivot_base['Total'] = pivot_base.sum(axis=1)

3.3 Add Percentage of Grand Total

pivot_base['Pct_Total'] = pivot_base['Total'] / pivot_base['Total'].sum()

3.4 Add Percentage Within Category

pivot_base['Pct_Category'] = pivot_base['Total'] / pivot_base.groupby(level=0)['Total'].transform('sum')

3.5 Top 2 Quarters by Category

top_quarters = pivot_base['Total'].groupby(level=0, group_keys=False).nlargest(2)

Explanation

• level=0: Groups by the first level of the multi-index (Store_Category)
• group_keys=False: Prevents redundant index labels

3.6 Rank Within Categories

pivot_base['Rank'] = pivot_base['Total'].groupby('Store_Category').rank(method='first', ascending=False)

Ranking Methods

• first: Assigns unique ranks, similar to SQL ROW_NUMBER()
• dense: Like DENSE_RANK(), no gaps in ranking
• min: Like RANK(), tied values get the same rank
• average: Tied values receive average rank

3.7 Add Maximum Value Per Group

pivot_base['Max_Value'] = pivot_base.groupby('Store_Category')['Total'].transform('max')

3.8 Insert Subtotals

subtotals = pivot_base.groupby('Store_Category').sum()
subtotals.index = pd.MultiIndex.from_tuples([(cat, 'Subtotal') for cat in subtotals.index])
final_with_subtotals = pd.concat([pivot_base, subtotals]).sort_index()

3.9 Sorting Strategies

# Sort by index
final_with_subtotals.sort_index(ascending=[True, False])

# Sort by values
final_with_subtotals.sort_values(by='Total', ascending=False)

Advanced Concepts and Clarifications

4.1 Understanding Axis Parameter

The axis parameter defines direction of operation:

• axis=0: Along rows (operate column-wise)
• axis=1: Along columns (operate row-wise)

Example

test_df = pd.DataFrame({
    'col1': [42, 13, None, None],
    'col2': [4, 12, None, None],
    'col3': [25, 61, None, None]
})

print(test_df.sum(axis=0))  # Sums down each column
print(test_df.dropna(axis=0))  # Drops rows with NaN

4.2 Apply vs Trnasform

4.2.1 Aggregation Behavior

# transform returns same shape
max_vals = pt.groupby('Store_Category')['Total'].transform('max')

# Filter rows where value equals max in group
pt[pt['Total'] == max_vals]

4.2.2 Multi-Column Operations

def diff_from_max(group):
    return group['Total'] - group['Max_Value']

pt.groupby('Store_Category').apply(diff_from_max)

4.3 Flexible Aggregations in Pivot Tables

flex_pivot = merged_data.pivot_table(
    index=['Store_Category', 'Quarter'],
    columns='Year',
    values=['Weekly_Sales', 'IsHoliday', 'Date', 'Type'],
    aggfunc={
        'Weekly_Sales': 'sum',
        'IsHoliday': 'nunique',
        'Date': 'count',
        'Type': 'nunique'
    }
)

Final Thoughts

• Mastering these patterns enables efficient data processing at scale
• These techniques form the foundation for deeper analytics and visualization
• Transitioning from manual tools to code-based workflows increases reproducibility and reduces errors