Python for Excel Lecture Notes

Introduction

• Python integration in Excel is now available in the Beta channel of Office 365.
• This feature is transformative for Excel users, regardless of programming knowledge.
• Python can be accessed through the Formulas tab under a new Python section.
• You can insert custom Python formulas or explore pre-built Python samples.

Getting Started with Python in Excel

Entering Python Mode

• Method 1: Go to Insert > Python, then insert a Custom Python Formula. The formula box changes to a Python box.
• Method 2: Type =PY, press Tab to switch to Python mode.

Basic Example

• Reference a data range in Python: highlight cells, which are wrapped in an XL function in the formula bar.
• Press Ctrl + Enter to run the Python script and obtain a DataFrame.
• Switch between Python Object view and Excel Values view using a dropdown.

DataFrame Overview

• DataFrame: A two-dimensional data structure from the Pandas library, essential for data analysis.
• Click the card icon to preview the first and last rows.
• Rename DataFrames for easier reference in future Python scripts.

Key Functionalities in Pandas Library

Descriptive Statistics

• Use df.describe() to get summary statistics like count, mean, and standard deviation.
• Reference specific columns using square brackets or dot notation (e.g., df['Product'] or df.Product).

Basic Calculations

• Sum columns using the .sum() method (e.g., df.sales.sum() for total sales).
• Compute mean with the .mean() method.

Grouping and Aggregation

• Group by Date: df.groupby('date').sales.sum() produces total sales for each date.
• Group by Month: Use pd.Grouper(key='date', freq='M') within groupby to aggregate by month.

Creating Visualizations

• Plotting basics: Use the plot method to visualize data in cells (e.g., forchart.plot(kind='line')).
• Customize x and y axes and chart types (e.g., line, area).
• Access detailed syntax and documentation via Pandas library resources and AI tools like ChatGPT or Bing Chat.

Calculation Logic

• Python cells calculate from left to right and top to bottom. Ensure DataFrame definitions precede their references.

Advanced Examples

Data Restructuring

• Use pd.melt to restructure data columns (e.g., merging Quantity and Sales columns into a single column).

Data Querying

• Use the Pandas query method to filter data (e.g., df.query('sales > 2000 and Product.str.contains("black")')).

Advanced Visualizations

• Create small multiples and customize y-axis labels for consistent comparison.
• Use AI tools to assist with complex script requirements.

Library Imports and Regular Expressions

• Python allows importing of external libraries like re for regular expressions.
• Use regular expressions to identify patterns (e.g., finding URLs within text).
• Dynamic references: Updates in the source table automatically reflect in the results.

Integration with Power Query

• Python can connect to Power Queries, allowing data manipulations without physical loading into the workbook.

Conclusion

• Python for Excel is highly versatile and opens new dimensions for data analysis in Excel.
• Viewer interaction: Encouragement to share thoughts and feedback in comments.