PYTHON PROJECT FOR PRACTICE
PROGRAM TO AUTOMATE EXCEL TASKS WITH PYTHON
David Abram is a Data Scientist at MICROSOFT. He has been the CTO of an energy efficiency startup and he has also worked as research engineer at a big multinational of energy storage.
Based in the US, David holds a Ph.D. in Chemical Engineering (Stanford University).
The aim of this project is to automate the work many consulting firms do in a spreadsheet every month/year to calculate the salaries of their consultants and fees charged to their customers. It is a Python real world project.
You will receive two Excel files: the time tracking each consultant fills in and sends every month with the hours worked every business day of the week/year and an annex with the rate for every professional category. You will have to write a program that takes both spreadsheets as inputs and yields the salary of each employee and some statistics about the professional positions (see picture).
DOWNLOAD / CONTENT
You will receive an email with a protected ZIP and a password to access the content. If you are a registered user, the download is always available on your account.
The downloadable zip is made up of:
1) One PDF with the instructions and guidelines, including the project divided in several steps and online resources that may help you finish the challenge step by step.
2) Two spreadsheets: the Time Tracking (12 sheets, 1 per month) and an annex with a country-ISO mapping and consultancy rates for each professional category.
3) A py file and a Notebook file with the project solved (430 lines of code). It contains not only the source code but also detailed explanations and comments about how the code works. You may run the code from the Notebook.
IMPORTANT: to run the program, you need to have jupyter or the 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: you will have to import (and install) the following python libraries: pandas, datetime, os, and locale.
-Once you finish it, you will acquire the basic knowledge to replace common Excel tasks like Vlookup, sums, if+ AND/OR functions, date management, pivot tables, separator settings, currency format, etc, with Python and Pandas .
You will practice and learn the following concepts:
– Write functions and call them.
– Raise exceptions (classes)
– Error handling,
– for Loops,
– Conditionals statements inside a function,
– List comprehension.
– Formatted String Literals (f-strings).
You must be familiar with the Pandas library. You will need it to read the Excel files and manage data.
If you need additional information, do not hesitate to contact us.
Specification: AUTOMATE EXCEL TASKS WITH PYTHON
1 review for AUTOMATE EXCEL TASKS WITH PYTHON
Only logged in customers who have purchased this product may leave a review.
phil jones (verified owner) –
Coming from a business background I was impressed with Pandas and the things you can do with it. Actually I´d say you need a basic understanding of Pandas if you want to solve all the exercises.
What I liked best is that the code is clear and easy to follow. It ´s the first time I read code written by a senior engineer working at a big tech.