Basic to Advance Python for Excel Automation Part3 (7 hrs)

Name: Basic to Advance Python for Excel Automation Part3 (7 hrs)
Rating: 5.0 (7 reviews)

Excel Automation using Python

Created byajay parmar

Last updated 6/2023

English

English [Auto],

What you'll learn

You shall be able to implement Lists, Tuples, Loops that you had learnt in previous series over here on real data
You shall be able to automate all Excel related Tasks
We will learn how to modify, delete, append the data from excel sheets and workboooks
Any easy or complex job in excel like VLOOKUP, Searching data in 1000s files , working with folders - all practical knowledge is coming your way

Course content

1 section • 21 lectures • 7h 0m total length

How to Change Values17:09
Basic functions - Sum Count Unique & nlargest ,,Value Count etc28:16
Pls download these pycharm modules and few important excel files as well. You may find few changes in the excel files as they were changed time to time, not all but may be one or two. One can create ones dummy data too as file are small and easy to create.
Group By47:25
Loops in Group By17:59
Create a Pivot - Summarization of reports17:05
Create a new Column in pivot25:18
Learn to add a new pivot column in Python by computing east zone contributions, filtering by east, using sum and locator, and troubleshooting a miscalculation.
Project - Calculate Zone Wise percentage21:31
Project - Which shop has highest Items Sold18:48
Learn to consolidate three shop excel files with pandas read_excel, concatenate them, add a shop name column, and group by shop to sum items sold and average damaged pieces.
Your Doubt is solved - Change Values issues explained and resolved15:28
Export to Excel14:46
Learn to export north zone data to a new excel file with pandas to_excel, using an excel writer, and customize sheet name, headers, index, start row, start column.
Create and Open an Excel application13:07
Append Data into Excel10:49
Learn to append data from one Excel file to another using pandas with openpyxl, using read_excel and append mode to combine multiple files.
Append with more parameters explained22:47
Excel Sheet Commands23:28
Project - Compile Data from sheets30:18
Automate compiling data from multiple Excel sheets into a new workbook with Python, pandas, and openpyxl by looping through sheet names, reading data, and appending with start-row management.
Files and Folders data handling28:10
Master folder-based Excel data extraction by looping through files with glob, opening each workbook, iterating sheets, and appending data to a master file with the source workbook name.
Data Compilation into different sheets20:36
How to use OS library - Folders and Files handling9:24
Loop through Folders8:39
Iterate over a folder to list only subfolders, then drill into each subfolder to list its contents by joining paths and filtering with is_directory.
How to refer to Excel sheet Cells15:38
Project -Create Totals as a new column in Excel Sheet14:11

Requirements

If you have seen Part1 and Part2 as well then this is good to go.

Description

Learn how to work with existing Excel files and manage your day to day task fast and easily

How to create new excel files using python code and how to save the workbooks.

How to refer to sheets and ranges in excel across spreadsheets

How to change the data

Your doubts on filter with criterias also answered - What you need to know

How to create Pivots

How to add columns in Pivots and calculate the percentages- Powerful

How to edit the data or tables which has a data in spreadsheets

How to work with folders and files which can have hundreds of file and each of the file may have too many spreadsheets

How to loop through excel or text files only and read the data inside them.

How to apply Vlookup across 1000s files. Lookup the information is no more a tedious and slow process.

Vlookup in python -different approaches

How to use Loops in Spreadsheets and copy and paste the data

Data accumulation across excel workbooks

How to add sheets

How to remove the sheets from spreadsheet

How to move sheets from one index to another

Let us know the Glob module - why it is required and how we can take the knowledge to next level

Know the os library too and how it is useful in excel data management work

Who this course is for:

Data Analysts who want to automate excel work using python language