EXCEL EXERCISES SOLVED WITH PYTHON

PROJECT DESCRIPTION

The goal of this Python challenge is to help spreadsheet users to migrate from Excel to Python. These excel exercises solved with Python come with 17 assignments including more than 35 Pandas exercises and the data required for each exercise. After finishing, you will know how to implement in Pandas and Python the most common functions and formulas of Excel.
Some of the problems are with real datasets, based on the tasks different types of professionals must perform with the spreadsheet. The goal of the exercises 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, the popular Python library, allows 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.

Practice topics:

1. Consolidate in one data frame tables placed in different Excel worksheets or files.
2. 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.
3. Clean  data stored in several worksheets. Deal with null values, unwanted characters, duplicates, column types, string manipulation and so on.
4. 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.

Exercises and Projects

• 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.
• Python practice questions 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”.

DOWNLOAD / CONTENT

After purchase, you will receive an email with a ZIP file. The download is also always available on your Practity account.
The zip file includes:

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

– Python 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

INSTRUCTOR

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

Pandas Library

This is a Pandas practice project since you will need Pandas for all the exercises. The Python Pandas library is a powerful tool for data manipulation and analysis. It provides easy-to-use data structures and data analysis tools for handling large datasets efficiently. With Pandas, you can read and write data from various file formats, such as CSV, Excel, and SQL databases. It has functions for cleaning, transforming, and merging data, as well as statistical analysis and data visualization capabilities.

Benefits of Pandas

One of the key advantages of the Pandas library is its ability to replace excel spreadsheets with Python code. In Excel, you often perform repetitive tasks manually using functions and formulas. However, with Pandas, you can automate these tasks and perform them much faster. For example, instead of manually copying and pasting data between spreadsheets, you can write a few lines of code to read data from multiple sources, clean and transform it, and then export the final result to a new Excel file.

Functions

Pandas includes built-in functions and methods that can help you replace and automate tasks usually done in Excel. For instance, you can use the “read_excel()” function to read data from an Excel file into a Pandas DataFrame. Once you have loaded the data into a DataFrame, you can use various methods to clean and transform the data. For example, you can use the “dropna()” method to remove rows with missing values, or the “fillna()” method to fill in missing values with a specified value or a calculated value. You can also use the “groupby” method (Pivot tables subsititute) to group the data by a specific column and calculate aggregate statistics, same
Pandas also has functions for merging and joining data from multiple sources. Instead of the Excel lookup functions, you have the “merge()” method to combine two DataFrames based on a common column, while the “join” function allows you to merge DataFrames based on their indexes.
In addition to these basic functions, Pandas also supports advanced operations such as time series analysis, and handling categorical variables. It also integrates well with other Python libraries such as NumPy and Matplotlib, allowing you to perform complex data analysis and visualization tasks.

VERSION
Python 3

Image

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