Sample Exam: Python Associate Exam - VoltBike Innovations
VoltBike Innovations is a leading company in the electric bicycle (e-bike) industry, specializing in the design and manufacture of high-performance e-bikes. The company is dedicated to advancing urban mobility solutions by delivering state-of-the-art e-bikes with features such as varying motor powers, advanced battery capacities, and efficient charge systems.
Recently, VoltBike Innovations has encountered some challenges in managing production costs while ensuring high levels of customer satisfaction. These issues have led to increased production expenses and variability in costs, impacting overall profitability.
You are part of the data analysis team tasked with providing actionable insights to help VoltBike Innovations address these challenges.
Task 1
Before you can start any analysis, you need to confirm that the data is accurate and reflects what you expect to see.
It is known that there are some issues with the production_data table, and the data team have provided the following data description.
Write a query to return data matching this description. You must match all column names and description criteria.
Create a cleaned version of the dataframe.
- You should start with the data in the file
ebike_data.csv. - Your output should be a dataframe named clean_data.
- All column names and values should match the table below.
| Column Name | Criteria |
|---|---|
| bike_type | Categorical. Type of e-bike. ['standard', 'folding', 'mountain', 'road']. Missing values should be replaced with 'standard'. |
| frame_material | Categorical. Material of the e-bike frame. ['aluminum', 'steel', 'carbon fiber']. Missing values should be replaced with 'unknown'. |
| production_cost | Continuous. Cost of production (in USD). Missing values should be replaced with median. |
| assembly_time | Continuous. Time taken for assembly (in minutes). Missing values should be replaced with mean, rounded to 2 decimal places. |
| top_speed | Continuous. Maximum speed of the e-bike (in km/h). Missing values should be replaced with mean, rounded to 2 decimal places |
| battery_type | Categorical. Type of battery used. ['li-ion', 'nimh', 'lead acid']. Missing values should be replaced with 'other'. |
| motor_power | Continuous. Power output of the motor (in watts). Missing values should be replaced with median. |
| customer_score | Continuous. Customer satisfaction score (rating on a scale of 1 to 10). Missing values should be replaced with mean, rounded to 2 decimal places |
# Task 1
import pandas as pd
# Load CSV
clean_data = pd.read_csv("ebike_data.csv")
clean_data.columns = ["bike_type", "frame_material", "production_cost", "assembly_time", "top_speed", "battery_type", "motor_power", "customer_score"]
# Convert column data types
clean_data["bike_type"] = clean_data["bike_type"].astype("category")
clean_data["battery_type"] = clean_data["battery_type"].astype("category")
clean_data["motor_power"] = clean_data["motor_power"].str.replace("W", " ").astype(float)
# bike_type: fill missing values
clean_data["bike_type"].fillna("standard", inplace=True)
# frame_material: fill missing with 'unknown' and standardize case
clean_data["frame_material"] = clean_data["frame_material"].str.lower()
valid_pigments = ["aluminum", "steel", "carbon fiber"]
clean_data["frame_material"] = clean_data["frame_material"].where(clean_data["frame_material"].isin(valid_pigments), "unknown")
clean_data["frame_material"] = clean_data["frame_material"].astype("category")
# production_cost: fill missing values with median
clean_data["production_cost"].fillna(clean_data["production_cost"].median(), inplace=True)
# assembly_time: fill missing with mean (rounded to 2 decimals)
clean_data["assembly_time"].fillna(clean_data["assembly_time"].mean().round(2), inplace=True)
# top_speed: fill missing with mean (rounded to 2 decimals)
clean_data["top_speed"].fillna(clean_data["top_speed"].mean().round(2), inplace=True)
# battery_type: fill missing with 'other' and replace (-)
clean_data["battery_type"].replace("-", "other", inplace=True)
clean_data["battery_type"].fillna("other", inplace=True)
# motor_power: fill missing values with median
clean_data["motor_power"].fillna(clean_data["motor_power"].median(), inplace=True)
# customer_score: fill missing with mean (rounded to 2 decimals) and clip to 1-10
clean_data["customer_score"].fillna(clean_data["customer_score"].mean().round(2), inplace=True)
clean_data["customer_score"] = clean_data["customer_score"].clip(1, 10).round(2)
# TASK 1 OUTPUT
print(clean_data.head())Task 2
You want to understand how different types of e-bikes influence production costs, assembly times, and customer satisfaction.
Calculate the average production_cost, assembly_time, and customer_score grouped by bike_type.
- You should start with the data in the file
ebike_data.csv. - Your output should be a data frame named
bike_type_data. - It should include the four columns:
bike_type,avg_production_cost,avg_assembly_time, andavg_customer_score. - Your answers should be rounded to 2 decimal places.
# Task 2
import pandas as pd
# Load CSV
df = pd.read_csv("ebike_data.csv")
# Group data and compute mean
bike_type_data = df.groupby("bike_type")["production_cost","assembly_time","customer_score"].mean().round(2).reset_index()
# Renaming columns
bike_type_data.columns = ["bike_type", "avg_production_cost", "avg_assembly_time", "avg_customer_score"]
# TASK 2 OUTPUT
print(bike_type_data.head())Task 3
In order to proceed with further analysis, you need to understand how key production and satisfaction factors relate to each other. Start by calculating the mean and standard deviation for the following columns: production_cost and customer_score. These statistics will help in understanding the central tendency and variability of the data related to e-bike production and customer feedback.
Next, calculate the Pearson correlation coefficient between production_cost and customer_score. This correlation coefficient will provide insights into the strength and direction of the relationship between production costs and customer satisfaction.
- You should start with the data in the file
ebike_data.csv. - Calculate the mean and standard deviation for the columns
production_costandcustomer_scoreas:production_cost_mean,production_cost_sd,customer_score_mean, andcustomer_score_sd. - Calculate the Pearson correlation coefficient between
production_costandcustomer_scoreascorr_coef. - Your output should be a data frame named bike_analysis.
- It should include the columns:
production_cost_mean,production_cost_sd,customer_score_mean,customer_score_sd, andcorr_coef. - Ensure that your answers are rounded to 2 decimal places.
# Task 3
import pandas as pd
# Load CSV
df = pd.read_csv("ebike_data.csv")
# Calculate mean and sd
production_cost_mean = df["production_cost"].mean()
production_cost_sd = df["production_cost"].std()
customer_score_mean = df["customer_score"].mean()
customer_score_sd = df["customer_score"].std()
# Calculate the Pearson correlation coefficient
corr_coef = df["production_cost"].corr(df["customer_score"])
# Create a dictionary
dict = {"production_cost_mean":[production_cost_mean], "production_cost_sd":[production_cost_sd], "customer_score_mean":[customer_score_mean], "customer_score_sd":[customer_score_sd], "corr_coef":[corr_coef]}
# Convert to dataframe
bike_analysis = pd.DataFrame(dict)
# TASK 3 OUTPUT
print(bike_analysis.head())