EXCEL EXERCISES SOLVED WITH PYTHON

INSTRUCTOR
Ivan Pushin is a Data scientist based in the Netherlands. He holds a Bachelor of Engineering (B.Eng.), Civil engineering.

PROJECT DESCRIPTION
The goal of this challenge is to help spreadsheet users to migrate from Excel to Python. The challenge contains 17 assignments with more than 35 exercises to practice how to implement in Pandas and Python the most common functions and formulas of Excel.
Some of the problems are real with real datasets, based on the tasks different types of professionals must perform with the spreadsheet. The goal of the problems is to replicate real-life problems  so you can learn to solve them with Python instead of Excel, especially  while dealing with large amounts of data . Some of the exercises are available in our Excel projects section.
Replacing Excel with Python is a hard step,  many tasks may seem easier and faster to do in the spreadsheet but in the end it is a manual tool and therefore the risk of errors is very high. Pandas allow you to automatically do the same things with code,  and export the result to excel so the final output is the same except for the fact that the reliability of the result is unbeatable.
The practice is focused on common topics like:
– Consolidate in one data frame tables placed in different Excel worksheets or files.
– Implement functions and use methods to replace Excel formulas or functions, such as SUMIF, VLOOKUP, COUNTIF, INDEX/MATCH, PIVOT TABLES, etc. The problems replicate business cases where this type of functions is a must.
– Clean  data stored in several worksheets. Deal with null values, unwanted characters, duplicates, column types, string manipulation and so on.
– Prepare tables for calculations and data validations.  Combine, filter and merge files or different datasets,  drop unnecessary fields. or apply functions to create new columns based on multiple conditions.

Some of the exercises and projects are:
-– Budget & Costs: Given two spreadsheets with the budget and expenses of 20 different departments of a multinational corporation, compare them and find teams with the highest deficit and surplus. Disclose also the most common type of expense among certain teams.
– Create a mortgage calculator. You will have to calculate monthly payments of a fix interest rate loan and build the amortization schedule.
– Exercises to compare and summarize data through breakdown tables.
– Problems about finding, matching and retrieving values.
– Problems where you will have to write code that yields the same output as lookup functions such as vlookup, hlookup, or reference functions like index and match.
– Etc.

DOWNLOAD / CONTENT
You will receive an email with a ZIP file. If you are a  registered user, the download is always available on your account.
The downloadable zip  is made up of:
1) A PDF with the exercises description.
2) 17 Excel files with the data required.
3) Excel solutions: 15 Excel files with the exercises solved in Excel.
4) Python solutions: A  Notebook file with the solutions. It contains not only the source code but also detailed explanations and comments about how the code works. The code  has been written by a senior developer, so it is clean and easy to understand.
IMPORTANT: to see the solutions (Notebook) you need to have jupyter or ANACONDA package installed on your machine. If you do not have it, you may download it here. It is free.

WHAT YOU WILL PRACTICE
– Libraries: Pandas, Numpy, datetime.
– Import/Export  Excels files.
– Remove, select, rename, filter columns and rows.
– Nulls.
– Data types.
– Conditional slicing.
– Groupby.
– Duplicates.
– Dates.
– Merge and joins.
– Loops (for).
– Lists
– apply + lambda

VERSION
Python 3

Image

CONTACT
If you need additional information, do not hesitate to contact us.