Data Analytics - Capstone Project
This project is a comprehensive data analytics solution developed as the capstone project for the Perscholas bootcamp. The project focuses on extracting credit card data from various sources, transforming it using Apache Spark, loading it into a MySQL database, and visualizing the results through Power BI
Data ingestion module to extract credit data from JSON files and RESTful APIs Apache Spark for performing complex data transformations and data quality checks MySQL database for efficient storage of transformed financial data Interactive visualizations and dashboards created using PowerBI
- Python
- Apache Spark
- MySQL
- Power BI
- Git
The Credit Card System database is an independent system developed for managing activities such as registering new customers and approving or canceling requests, etc., using the architecture. A credit card is issued to users to enact the payment system. It allows the cardholder to access financial services in exchange for the holder's promise to pay for them later. Below are three files that contain the customer’s transaction information and inventories in the credit card information.
- CDW_SAPP_CUSTOMER.JSON: This file has the existing customer details.
- CDW_SAPP_CREDITCARD.JSON: This file contains all credit card transaction information.
- CDW_SAPP_BRANCH.JSON: Each branch’s information and details are recorded in this file.
a. For “Credit Card System,” create a Python and PySpark SQL program to read/extract the following JSON files according to the specifications found in the mapping document.
- CDW_SAPP_BRANCH.JSON
- CDW_SAPP_CREDITCARD.JSON
- CDW_SAPP_CUSTOMER.JSON
Data loading into Database
Once PySpark reads data from JSON files, and then utilizes Python, PySpark, and Python modules to load data into RDBMS(SQL), perform the following:
a. Create a Database in SQL(MySQL), named “creditcard_capstone.”
b. Create a Python and Pyspark Program to load/write the “Credit
Card System Data” into RDBMS(creditcard_capstone).
Tables should be created by the following names in RDBMS:
CDW_SAPP_BRANCH
CDW_SAPP_CREDIT_CARD
CDW_SAPP_CUSTOMER
Once data is loaded into the database, we need a front-end (console) to see/display data. For that, create a console-based Python program to satisfy System Requirements 2 (2.1 and 2.2).
Functional Requirements 2.1
-
Used to display the transactions made by customers living in a given zip code for a given month and year. Order by day in descending order.
-
Used to display the number and total values of transactions for a given type.
-
Used to display the total number and total values of transactions for branches in a given state.
##Functional Requirements 2.2
-
Used to check the existing account details of a customer.
-
Used to modify the existing account details of a customer.
-
Used to generate a monthly bill for a credit card number for a given month and year
-
Used to display the transactions made by a customer between two dates. Order by year, month, and day in descending order.
After data is loaded into the database, users can make changes from the front end, and they can also view data from the front end. Now, the business analyst team wants to analyze and visualize the data. Use Power BI for the below requirements:
Export following tables as a CSV file, using this link: https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html -CDW_SAPP_BRANCH -CDW_SAPP_CREDIT_CARD -CDW_SAPP_CUSTOMER
Import all CSV files into Power BI.
Find and plot which transaction type has a high rate of transactions.
Find and plot which state has a high number of customers.
Find and plot the sum of all transactions for the top 10 customers, and which customer has the highest transaction amount.
Banks deal in all home loans. They have a presence across all urban, semi-urban, and rural areas. Customers first apply for a home loan; after that, a company will validate the customer's eligibility for a loan. Banks want to automate the loan eligibility process (in realtime) based on customer details provided while filling out the online application form. These details are Gender, Marital Status, Education, Number of Dependents, Income, Loan Amount, Credit History, and others. To automate this process, they have the task of identifying the customer segments to those who are eligible for loan amounts so that they can specifically target these customers. Here they have provided a partial dataset.
https://raw.githubusercontent.com/platformps/LoanDataset/main/loan_data.json
The above URL allows you to access information for loan application information. This dataset has all of the required fields for a loan application. You can access data from a REST API by sending an HTTP request and processing the response
Create a Python program to GET (consume) data from the above API endpoint for the loan application dataset.
Find the status code of the above API endpoint.
Once Python reads data from the API, utilize PySpark to load data into RDBMS (SQL). The table name should be CDW-SAPP_loan_application in the database.
After the data is loaded into the database, the business analyst team wants to analyze and visualize the data.
Use Power BI for the below requirements:
Export “CDW-SAPP_loan_application” table as a CSV file, click the link for instructions: https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html Import the CSV file into Power BI.
Find and plot the percentage of applications approved for self-employed applicants.
Find the percentage of rejection for married male applicants.
Find and plot the top three months with the largest transaction data.
Find and plot which branch processed the highest total dollar value of healthcare transactions.
