Python for Consultants — Week 2: Pandas
Python for Consultants
Week 2 Learning Guide
Pandas: From Raw Data to Analytical Insights
What You Will Learn This Week
Pandas is Python's primary data analysis library. It gives you a spreadsheet-like table (called a DataFrame) that
you can load, inspect, filter, sort, group, merge, clean, and pivot — all in code. By the end of Week 2, you will be
able to replace most Excel workflows with faster, automated, reusable Python scripts.
Install pandas if you haven't: pip install pandas openpyxl numpy
How to Use the Exercise Files
• Run each daily .py file in VS Code or Jupyter
• Each file builds on the previous — do them in order
• Day 16 is a mini project that uses all Week 2 concepts
• All exercises use consulting-relevant examples: client portfolios, brand data, financials
Week 2 at a Glance
DAY Intro to Pandas
8 Topics: DataFrames, Series, pd.read_csv(), .head(), .shape, .columns, .dtypes
Key syntax: df = [Link](data) | df['col'].sum()
Exercise: Create a DataFrame from a client list, add a computed profit column
DAY Exploring Data
9 Topics: .describe(), .info(), .value_counts(), .nunique(), .isnull().sum()
Key syntax: [Link]() | df['col'].value_counts(normalize=True)
Exercise: Audit a messy dataset: find nulls, count categories, get stats
DAY Filtering & Selecting
10 Topics: Boolean indexing, .loc[], .iloc[], .isin(), .[Link](), .query()
Key syntax: df[df['rev']>80] | [Link]("nps>50 and tier=='Platinum'")
Exercise: Build an at-risk watch list using compound conditions
DAY Sorting & Ranking
11 Topics: .sort_values(), .nlargest(), .nsmallest(), .rank(), composite score
Key syntax: df.sort_values('rev',ascending=False) | [Link](5,'rev')
Exercise: Build a weighted client league table with revenue, NPS, and growth
Page 1 of 4
Python for Consultants — Week 2: Pandas
DAY GroupBy & Aggregation
12 Topics: .groupby().agg(), multiple metrics, .transform(), reset_index()
Key syntax: [Link]('sector').agg(total=('rev','sum'), n=('cl','count'))
Exercise: Generate a sector performance report with avg revenue and NPS
DAY Merging & Joining
13 Topics: [Link]() — inner/left/right/outer, [Link](), chaining merges
Key syntax: [Link](df1,df2,on='id',how='left') | [Link]([q1,q2])
Exercise: Build a master dataset by joining client, financial, and CRM data
DAY Data Cleaning
14 Topics: .fillna(), .dropna(), .astype(), .str methods, .drop_duplicates()
Key syntax: df['col'].[Link]().[Link]() | [Link]([Link]())
Exercise: Clean a messy CRM export: strip text, fix types, remove dupes
DAY Pivot Tables & Apply
15 Topics: pd.pivot_table(), .apply(), .melt(), [Link]()
Key syntax:
pd.pivot_table(df,values='rev',index='sector',columns='qtr',aggfunc='sum'
)
Exercise: Build a quarterly growth pivot table and classify clients with .apply()
DAY Mini Project
16 Topics: Brand & Sales Performance Analyzer — all Week 2 concepts applied
Key syntax: Full pipeline: merge → clean → groupby → pivot → growth
analysis
Exercise: Run the project, understand each section, complete the 4 challenges
Quick Reference Cheat Sheet
Loading & Inspecting
df = pd.read_csv('[Link]')
df = pd.read_excel('[Link]', sheet_name='Sheet1')
[Link]() | [Link] | [Link] | [Link]()
[Link]() | [Link]() | [Link]().sum()
Filtering
df[df['revenue'] > 80]
df[(df['sector']=='FMCG') & (df['nps']>=50)]
df[df['tier'].isin(['Platinum','Gold'])]
[Link]("revenue > 50 and region == 'EMEA'")
df[df['client'].[Link]('L',case=False)]
Page 2 of 4
Python for Consultants — Week 2: Pandas
GroupBy & Aggregation
[Link]('sector')['revenue'].sum()
[Link]('sector').agg(
total=('revenue','sum'),
avg_nps=('nps','mean'),
clients=('client','count')
).round(1).reset_index()
# Add sector average to each row (keeps shape)
df['sector_avg'] = [Link]('sector')['revenue'].transform('mean')
Merging
# Left join (keep all left rows)
result = [Link](df1, df2, on='client_id', how='left')
# Chain multiple merges
master = [Link](financials, on='id').merge(crm, on='id', how='left')
# Stack rows vertically
combined = [Link]([q1, q2, q3], ignore_index=True)
Cleaning
df['col'] = df['col'].[Link]().[Link]()
df['col'] = df['col'].fillna(df['col'].median())
df = df.drop_duplicates(subset=['client'], keep='first')
df = [Link](columns={'old_name': 'new_name'})
df['rev'] = df['rev_raw'].[Link]('$','').astype(float)
Pivot Tables
pd.pivot_table(df,
values='revenue', index='sector',
columns='quarter', aggfunc='sum',
margins=True, margins_name='TOTAL'
).round(1)
Common Mistakes to Avoid
• Forgetting reset_index() after groupby — the result has a grouped index that breaks further filtering
• Using = instead of == in .query() or boolean conditions
• Modifying a filtered slice without .copy() — causes SettingWithCopyWarning
• Not checking for nulls after a left merge — unmatched rows get NaN values
Page 3 of 4
Python for Consultants — Week 2: Pandas
• Using [Link] in Excel — always use DXA for consistent results
Coming in Week 3: Visualization
Week 3 transforms your pandas DataFrames into interactive charts and full web dashboards using Matplotlib,
Seaborn, Plotly, and Streamlit. You will build your first client-facing dashboard by Day 24.
Page 4 of 4