Excel Automation Using Python

Discover numpy and pandas, Python's fast data science libraries for array, series, and dataframe operations; learn their import, export, and Excel automation capabilities.

Install and import numpy, use the NP alias, and create one-dimensional arrays with zeros, ones, linspace, and random numbers; learn numpy's element-wise operations and broadcasting over lists.

Explore higher dimensional arrays in NumPy, including creating two-dimensional and three-dimensional arrays, stacking matrices, and inspecting shapes. Learn to reshape with NumPy.reshape and flatten to one dimension.

Explore numpy array operations with ndarrays, learning max, argmax, sorts (including axis-aware sorting), mean, std, and element-wise operations, while handling NaN and pursuing dot products.

Master NumPy indexing and slicing on 1D and 2D arrays, including colon notation, negative indices, views versus copies, boolean indexing, and bespoke indexing.

Explore using pandas to analyze the US flight database, group by origin, and compute mean departure delays, illustrating how pandas handles large data more efficiently than Excel.

Explore Pandas series as a one-dimensional NumPy-based array with labels; learn how to create it from NumPy arrays or dictionaries, and see how it builds the DataFrame.

Explore how Pandas data frame combines multiple series with shared indices into a two-dimensional tabular structure and how to create and index it manually.

Master handling Python DataFrame data types in pandas by casting with astype, converting dates with to_datetime, managing errors, and formatting with strftime to extract month or year.

Learn to detect and handle NaN values in pandas dataframes using isna/isnull, count NaNs by column, and apply dropna or fillna with axis, in place, and backfill or forward fill.

Learn to merge, concatenate, and join data frames in Excel automation with Python, exploring axis options, ignore index, and left, right, inner, and outer joins.

Import travel data with pandas, compute total transportation cost and the peak-cost day using numpy, processing subway, ferry, taxi, and bus with flat and per-km rates in a 2d array.

Transition from Excel to NumPy to compute daily commuting costs by converting yes/no to 1/0 and applying dot products. Sum results and identify the max day with argmax and strftime.

Split the sales data into 48 month-year groups using pandas groupby, exporting each group to its own Excel file to demonstrate practical data partitioning in Python.

Learn to solve a Pandas assignment by importing data, creating a month-year key from order date, grouping by that key, and exporting each group to separate Excel files.

Explore automating Excel workflows by interacting with the operating system using Python. Create folders, move and sort thousands of files by year with the os module from the standard library.

Master the Python os library to manage directories and files: get and change the current working directory, create and remove folders, launch files, and join paths cross-platform.

Learn to handle files in Python with the OS module, performing copy, move, and rename operations, joining paths with os.path.join, and using util for copying and moving.

Harness the Python glob library to gather all filenames in a folder and subfolders using wildcards, filter by patterns like 2016, and prepare files for Excel automation.

Organize files by year in python by scanning a folder, extracting year from the last four characters of the file name, ignoring non-matching files, creating year folders, and moving files.

Organize excel files by year using Python with os and glob. Extract year from file names, create year folders when needed, and move files into those folders.

Write code that goes into each folder from the previous assignment, accesses every file, and aggregates them into a single consolidated spreadsheet.

Import os, glob, and pandas to collect excel files one level down, consolidate them into a single dataframe with consistent columns, and export to excel.

Explore how to implement vlookup in Python by performing a left merge of sales data with zip code income dataset to fetch mean income, handling non-unique keys and first-match behavior.

Convert a Vlookup template into a reusable Python function that merges left and right data frames by left and right keys and returns the right value column.

Learn to create and customize an excel pivot table, summarize profit by category and region, and replicate the view with pandas group by.

Implement a Python pivot table by selecting rows (segment, category, subcategory) and columns (region), aggregate profit with groupby, unstack to columns, prune columns, and export to Excel or HTML.

Learn how to convert a pivot table template into a reusable function using pandas pivot_table, including data frame, values, index, columns, and aggfunc (sum or mean).

Apply the if function in Excel to calculate tax adjusted profit across furniture, office supplies, and technology using nested if statements.

Use numpy's np.where to implement the if function and build a profit net tax column. Cover nested conditions for furniture, office supplies, and technology, and verify with Excel.

Explore how to manipulate text in Excel, including concatenate, trim, left/right, mid extraction, and upper and lower case, with emphasis on leading spaces and Python replication in a future lecture.

Explore how to replicate Excel string functions in Python using Pandas dot str, indexing, and split to extract year and state codes, then concatenate, trim, and adjust case.

Replicate four popular Excel functions—countif, countifs, sumif, and sumifs—in Python with pandas indexing for automated data analysis, as part of Excel automation using Python.

Explore how to replicate Excel’s countif, countifs, sumif, and sumifs in Python using a sales data frame, filtering by quantity and city starts with fort, and summing profit.

Create and customize line charts and pivot charts in Excel, explore region-level visualizations, and understand Excel's limitations with a large dataset as a lead into Python visualization.

Master matplotlib for Python visualizations, learn both object oriented and pyplot interfaces, and combine pandas visuals to create line, scatter, bar, pie, histogram, and 3D plots.

Explore pandas visualization with dataframe dot plot, grouping sales and profit by region, customizing plots with kind, title, subplots, layout, legend, and stacked options.

Explore matplotlib's object oriented interface using figure and axis objects as canvas and plots, controlling attributes like titles, colors, ticks, legends, and markers for versatile Python visuals.

Explore matplotlib's object-oriented interface by creating a figure canvas, adding axes, and plotting with dot plots and configurable dimensions, including subplots and styling options like color and markers.

Learn to customize matplotlib plots by adding a figure super title, individual axis titles, axis labels, and fixed y-tick values for two adjacent plots.

Learn to style matplotlib plots with themes, adjust colors, add legends and labels, and create and configure subplots using figure and axis objects for multi-plot canvases.

Combine pandas visualization with the matplotlib interface to visualize city sales using groupby. Plot top 15 and laggard cities on a shared canvas with axis objects and titles.

Organize Pandas visualizations using the matplotlib OO interface, refine plots by removing axis labels, add a 100,000 vertical line, and format dollars with a function formatter.

Recreate the two side by side pie charts using the sales data: category-level sales breakdown with actual sales in thousands of dollars in a legend, and segment-level sales breakdown.

Create side-by-side pie charts showing category and segment sales breakdowns, using matplotlib, subplots, and custom titles; print percentage labels with two decimals and optional ggplot style.

Master advanced pie chart techniques: explode the largest slice, provide segment level breakdown with a labeled legend using box_to_anchor, and show absolute sales values via a callable.

Visualize 2017 cumulative sales with a line plot, and add a secondary plot showing annual sales progression from 2014 to 2017, using dollar and K tick labels.

Plan the visualization by extracting year from order date, computing 2017 cumulative sales, and formatting y ticks, using pandas time series and dot aggregate for grouping in Python.