T15 Cloud Analytics and AI

Challenge Overview:
This challenge comprises two distinct parts: Analytics and Machine Learning. You will work
with CSV datasets stored in an S3 bucket named "car-data” with a unique prefix followed by a
random number. Within this bucket, there are two essential folders:

1. Inputfile: This folder contains the car_data.csv dataset, which you will utilize for the
Analytics part. Using car_data.csv, you will be cleaning the data and loading the data to s3. Also,
you will be performing various analytics tasks based on the cleaned data.
2. car_cleaned_data: This folder contains the car_cleaned_data.csv dataset, designated for the
Machine Learning tasks.

Analytics and ML pipeline:

Analytics: S3 –> EMR– >s3 / RDS

Machine Learning: S3 -> SageMaker -> S3.

Note:
• You can do any of the parts first, both parts (analytics and machine learning) are
independent.
• Machine learning problem statement: Machine Learning: Predicting Car
Selling Prices

IMPORTANT NOTE: Use US East (N. Virginia) us-east-1 Region only.

Note: Please follow the naming conventions which are given in the problem statement.

Analytics
Note: In this challenge you will be using EMR cluster. Before proceeding with the challenge,
create an EMR cluster. It will take 4 to 8 minutes to create the cluster. While it was creating the
cluster. You can read the problem statement and write the code in the template given in the
project folder.

RDS Cluster
Follow the configuration below for creating the cluster

Create database:

• Choose a database creation method

 Standard create

Engine options

• Engine type:
 Mysql
 All the other options keep the default values

Templates

• Free tier

Availability and durability – use the default values

Settings

• DB instance identifier: database-1

Credentials Settings

• Master username: admin

• Credentials management – self managed
• Master password – Awsuser1 (note: it is case-sensitive)
• Confirm master password - Awsuser1

Instance configuration

• Burstable classes (includes classes)

• Select the [Link] instance from the drop down.

Storage – Use the default values

Connectivity

• Use the default values

 • Public access – Yes

Tags – no change required

Database authentication – Password authentication

Monitoring – Use the default values

Additional configuration

• Database options
 Initial database name – dev
 And then use the default values.

Finally, click on the create database.

EMR Cluster
Note: If you are familiar with creating an EMR cluster, you can proceed with the given
configurations below, if you have any doubts, you can refer to the
Service_Configuration_Instructions pdf.

EMR Cluster Creation - Service_Configuration_Instructions pdf

Follow the configurations below for creating the cluster.

• Name: emr_spark
• EMR version: 7.6.0
• Application Bundle: select the default spark interactive aws
• Hadoop, Spark
• Cluster Configuration:
• Keep only primary node, remove the other two node by clicking Remove
instance group. Primary node EC2 instance type should be [Link]
• Cluster termination and node replacement: Termination Option - Manually
terminate the cluster.
• Security configuration and EC2 key pair: Create a keypair named “emr_spark”
(.pem file)
o And download this inside location
“~/home/labuser/Desktop/Project/wingst15-mock-car-data-
analysis/emr_spark.pem”
• Identity and Access Management (IAM) roles:
 o Amazon EMR service role – Select the “AWS-S3-EMR-REDSHIFT" role (which is
created for this challenge)
o EC2 instance profile for Amazon EMR – Create an instance profile
S3 bucket access - All S3 buckets in this account with read and write
access

Leave the other options as default and click on create cluster (It will take 6 to 8 mins to create
the cluster). Meanwhile, you can read the problem statement and start coding.

Templates:

You can solve this analytics part using either pyspark or scala. You are provided with python
template in [Link] file and [Link] file. You need to complete the code in the
template using pyspark or scala and push the file to EMR using SSH and do a spark-submit or
sbt run. Otherwise, you can use the EMR add step option to run the file.

Open the challenge folder which is present in the Project folder in vs code, then if you are using
python complete the code in the [Link] template. If you are using scala, complete the
code in [Link] file.

Problem Statement
The following functions are given in python and scala templates.

Task1:

read_data

Complete the following operations in the read_data function

The following are the parameters:

• Spark session – spark

• Mention the bucket name inside the bucket_name variable.
• The dataset will be in the s3 location inside the inputfile folder.
• Read the CSV file into a dataframe. Make sure to give only header as true
• In the challenge file, the return statement is already defined, and you need to replace
the df with your final output dataframe.

Task2:

clean_data

Complete the following operations in the clean_data function

The following are the parameters:

• Output of read_data function – input_df

1. Drop the rows which have null values in any of the columns.
2. Drop duplicate rows
3. In the challenge file, the return statement is already defined, and you need to replace
the df with your final output dataframe.

Task3:

S3_load_data

Complete the following operations in the “S3_load_data” function

The following are the parameters:

• Final dataframe to load the data: data (the clean_data output)

• File name for the results: file_name

Mention the bucket name inside the bucket_name variable.

Write a code to store the output to the respective locations using the output_path param.

• Output files should be a single partition CSV file with header.

Task4:

result_1

Complete the following operations in the result_1 function

The following are the parameters:

• Output of clean_data function – input_df

• Fetch the average selling price and rename it as “average_selling_price” and count of
cars and rename it as “car_count” for each “car_name”.
• And fetch only the records which have “car_count” greater than 2 cars.
• In the challenge file, the return statement is already defined, and you need to replace
the df with your final output dataframe.
• This function output data will be loaded into rds table with name
average_selling_price.
Sample Output: average_selling_price table

car_name average_selling_price car_count

 Hyundai i10 Magna 236666.5 6

Task5:

result_2

Complete the following operations in the result_2 function

The following are the parameters:

• Output of clean_data function – input_df

• Create a column named as “price_per_km” which calculates the price per kilometer.
[ HINT: selling_price / km_driven ]
• Filter the data to include only rows where price_per_km is less than 10.
• Round the “price_per_km” to 2 decimals.
• In the challenge file, the return statement is already defined, and you need to replace
the df with your final output dataframe.
• This function output data will load data into rds table with name price_per_km.

Sample Output:

car_na year selling_pr km_driv fuel seller_typ transmi owner price_per

me ice en e ssion _km
Maruti 2008 65000 140000 Petrol Individual Manual First 1.02
Alto LX Owner

Note:
• The column names (column names are case-sensitive) and order should be the same as
given in the sample output for each task.

IMPORTANT
• You have been given two ways to run the spark code i.e. either emr step function or by
using inside the emr cluster.
• Note: If you know how to run the solution on an EC2 instance or using a Step
Function, you can proceed. Otherwise, if you're not sure how to run the solution
(like PySpark or scala code), please refer to this PDF.
• Step_function and Ec2 - Services configuration pdf
 • After running the solution to check output in rds and s3, connect to the mysql
workbench to check if data loaded into tables is successful or not.
• If you are not familiar how to check tables in sql workbench, you can refer this
Services configuration pdf pdf
• How to connect to RDS / Mysql workbench - Services
configuration pdf

You have successfully completed the Analytics part. Keep Going...

Machine Learning: Predicting Car Selling Prices

In this problem, you will build a machine learning model to predict the selling prices of used
cars. You are given a dataset with various features related to the cars, such as the car's age,
mileage, fuel type, etc. Your goal is to preprocess the data, select appropriate features, and
train a model to make accurate price predictions. The final model should achieve an R² score
significantly higher than the baseline.

Note:
- Your bucket name will be starting with the prefix – `car-data` followed by some
random digits. Example: `car-data123456789`.
- Please use your complete bucket name wherever necessary inside the
`[Link]` file. Your exact bucket name MUST be set for the variables
named `bucket` and `BUCKET_NAME`.

Dataset Description:

The dataset contains the following columns:

• car_name: Name of the car.

• year: Year the car was manufactured.
• selling_price: Price at which the car was sold (target variable).
 • km_driven: Kilometers driven by the car.
• fuel: Type of fuel used by the car.
• seller_type: Type of seller (individual/dealer).
• transmission: Type of transmission (manual/automatic).
• owner: Number of previous owners.

Perform the following Cloud Driven Task:

Task 1: Launching an Amazon SageMaker Instance

Objective: Launch an Amazon SageMaker notebook instance to develop and deploy machine
learning models.

Instructions:
1. Access Amazon SageMaker:
- Access the AWS Management Console.
- Navigate to the SageMaker service under the 'Machine Learning' category.
2. Create a New Notebook Instance:
- Create a new notebook instance under the name `car-prediction-notebook `.
- Choose the instance type `[Link]`.
- Configure the necessary permissions to access S3 buckets by newly creating an
appropriate role.
- Create the notebook instance.
3. Access the notebook:
- Wait for the notebook instance status to change to 'InService'.
- Open the JupyterLab dashboard from the SageMaker console to begin your machine
learning project.

Task 2: Uploading the Question Notebook to SageMaker

Instructions:
• Once the Jupyter Lab has been launched, click on the `Upload Files` button on the left-
hand pane below the Menu bar.
• From the dialogue box traverse to the file named `[Link]` present in the Wings
specific folder; choose the file and hit Ok/Open.
• The `[Link]` will be now visible under the File browser of Jupyterlab and use it
to write the solution code. (Note: Select ‘conda_pytorch_p310’ kernel option while
prompted or select manually)
 • Kindly follow the exact instructions given in the problem statement inside the
notebook without modifying the code blocks marked as `Do not modify`.

!! Please follow the instructions provided in `[Link]` for solving the

Machine Learning Tasks.

Once you complete the challenge, make sure all the files are saved in
the specified directory and click on SUBMIT.