Reference no: EM133056251

Programming for Data Analysts

Learning Outcome 1: Critically evaluate the principles of programming and apply them in a business context
Learning Outcome 2: Critically evaluate the use of code libraries in programming for a business context
Learning Outcome 3: Construct a programming solution to solve a defined business problem

Assessment Overview

This assessment is made up of two parts, part 1 is an interactive Python notebook and part 2 is a work based report.

Summative Assessment

Part 1 Business Problem

Zappy Financial Services is a local company that provides small business loans. Last year, loan applications increased by over 200%, largely as a result of a concerted online push to establish a strong digital presence. Almost all loan applications and business leads are generated from search engines and digital ads, reflecting the decision to increase ad spend on SEO channels such as Google, Facebook, LinkedIn etc.

Despite a strong digital marketing approach, the current loan application process remains manual. All applications are reviewed and approved by the loan team, which given the recent increase in

volumes, has resulted in skills shortages, longer loan approval times and increased potential operational and control risk. It is clear that the current operating model constrains further growth.

Loan decisions are categorised as either "approved" or "rejected."

You have been tasked with providing a scalable solution - that addresses key resourcing and control risks. The ExCo has instructed you to develop several partial automation processes that will complement the existing loans team, freeing up time for greater one-on-one customer contact. Your role is to provide a data-driven solution while working with a variety of key stakeholders each with varying objectives such as marketing, internal audit, compliance etc.

The current loan application process requires the online completion of information, including gender, marital status, number of dependents, education, income etc. To date, several of these factors have been considered in the approval decision.

An in-house database administrator was able to compile a PDF of past applications which we are hoping to map to previous loan approval outcomes. This data is sourced from in-house databases and several manually updated spreadsheets from the loan department.

The two files provided by the DBA are:

• A file in PDF format called ‘Loans_Database_Table.pdf'
• An Excel file, called ‘Zappy Loan Data.xlsx'
The first file is generated by the ERP database and contains all the loan records from the previous year, and it includes a status field for each application, allowing us to map inputs to outcomes for a possible supervised machine learning exercise.

The Excel file is maintained by the sales team and it is currently being saved in a shared folder. This increases the chance of duplication and missing values.

As the lead with the most coding and data analytics knowledge, as well as a deep appreciation for the need to balance business growth with a robust control environment, you will be leading this project.

You will deliver an Interactive Python Notebook - . ipynb file with the code used, including markdown text to explain scripts, libraries used, and the logic.

The notebook which you create should highlight some of the key findings which you have in the data and the insights which you can provide to the business.

You will need to reflect the learnings throughout this module and consider the learning outcomes particularly ILO 3: Construct a programming solution to solve a defined business problem as you create your answer.

A. Loan Data Automation

Loading the data from loan files create an .ipynb notebook using a relevant tool (eg. Jupyter Notebooks / Google Colab etc...). Extract the two datasets provided by the DBA containing information about past loan records. The numeric values stored in each column of the loan dataset are:

 Gender: 1-Male, 2-Female
 Married: 0-Single, 1-Married
 Dependents: 0, 1, 2, 3+
 Graduate: 0-No, 1-Yes
 Self_employed: 0-No, 1-Yes
 Credit_History: 0-No, 1-Yes
 Property_Area: 1-Urban, 2-Semiurban, 3-Rural

You should use Python to load the information of these datasets in memory.

Please add comments to your notebook, explaining the steps taken to load the data, how you treated the PDF data, the libraries called and the overall procedure. Recall this will be used for training colleagues in future.

B. Preparing and cleaning the loan data

Check the datasets and make sure the data that comes from these two files is valid. Ensure your loan data is correctly indexed on the Loan_ID column.

Provide an explanation of the steps taken to ensure data preparation for analysis such as the correction of duplicates, missing values, outliers etc.

C. Descriptive analysis on current loan data

Your notebook file should contain some basic Exploratory Data Analysis (EDA) of the data. This should include items such as:
 The percentage of female applicants that had their loan approved
 The average income of all applicants
 The average income of all applicants that are self-employed
 The average income of all applicants that are not self-employed
 The average income of all graduate applicants
 The percentage of graduate applicants that had their loan status approved
In addition, you should provide a comment in your report of the potential ethical and regulatory implications of using certain data in the decision-making process.

D. Simple prediction for future loan applications

Using the data provided, create a predictive model for new loan applications.

Using the data you have prepared, train your model to predict whether a new application is likely to be classified as ‘approved' or ‘rejected' by mapping the input columns of:

 Gender

 Married
 Self_Employed
 Graduate
 Credit_History
 Property_Area

To the output column under ‘Loan_Status'.

Begin by splitting your dataset into a training / test set - making sure to explain the process in your notebook.

Use the training data to train your model, and then evaluate the output against the test data. Your notebook should include a short explanation of the techniques, libraries, tools, and objective function used to evaluate model precision.

Part 2 Work Based Report

The second part of this assessment should be considered in the context of your own workplace (which is your current employer). You will need to reflect the learnings throughout this module and consider the learning outcomes particularly:

LO 1: Critically evaluate the principles of programming and apply them in a business context ILO 2: Critically evaluate the use of code libraries in programming for a business context
In preparation for this part of the assessment ensure you:

• Engage with your employer on the report you will write - a good starting point is a discussion with your line manager
• Ensure the scope of the report is manageable, achievable and clearly articulates what your focus is
• Be sure to view this from a work context and consider your unique environment
• Consider the formative work you have already completed as this should inform your summative assessment

Work Based Report guidance

Develop a report for your workplace of no more than 2500 words (and specifically for your line manager) highlighting the key opportunities which you feel there are for the application of a programming solution (which would include code libraries which you should highlight) which you have learnt in this module. The solution should be relevant to your environment and provide work related activities for you to move forward with as a project should your business wish to. This report must leverage sound academic (secondary) research as a basis and should seek to develop a reasoned argument which supports your chosen solution.

Attachment:- Programming for Data Analysts.rar