PYTHON PROJECT FOR PRACTICE

PROGRAM TO AUTOMATE EXCEL TASKS WITH PYTHON

INSTRUCTOR

DAVID ABRAM
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).

PROJECT DESCRIPTION
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,
– Lists.
– List comprehension.
– Formatted String Literals (f-strings).

REQUIREMENTS
You must be familiar with the Pandas library. You will need it to read the Excel files and manage data.

VERSION
Python 3.8.5
Pandas: 1.0.5
Numpy: 1.19.5

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