Project: Python Data Associate Practical Exam (Spectrum Shades LLC)

Practical Exam: Spectrum Shades LLC

Spectrum Shades LLC is a prominent supplier of concrete color solutions, offering a wide range of pigments and coloring systems used in various concrete applications, including decorative concrete, precast concrete, and concrete pavers. The company prides itself on delivering high-quality colorants that meet the unique needs of its diverse clientele, including contractors, architects, and construction companies.

The company has recently observed a growing number of customer complaints regarding inconsistent color quality in their products. The discrepancies have led to a decline in customer satisfaction and a potential increase in product returns. By identifying and mitigating the factors causing color variations, the company can enhance product reliability, reduce customer complaints, and minimize return rates.

You are part of the data analysis team tasked with providing actionable insights to help Spectrum Shades LLC address the issues of inconsistent color quality and improve customer satisfaction.

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 ensure the data matches the description provided, including identifying and cleaning all invalid values. You must match all column names and description criteria.

You should start with the data in the file "production_data.csv".
Your output should be a DataFrame named clean_data.
All column names and values should match the table below.

Column Name	Criteria
batch_id	Discrete. Identifier for each batch. Missing values are not possible.
production_date	Date. Date when the batch was produced.
raw_material_supplier	Categorical. Supplier of the raw materials. (1='national_supplier', 2='international_supplier'). Missing values should be replaced with 'national_supplier'.
pigment_type	Nominal. Type of pigment used. ['type_a', 'type_b', 'type_c']. Missing values should be replaced with 'other'.
pigment_quantity	Continuous. Amount of pigment added (in kilograms) (Range: 1 - 100). Missing values should be replaced with median.
mixing_time	Continuous. Duration of the mixing process (in minutes). Missing values should be replaced with mean, rounded to 2 decimal places.
mixing_speed	Categorical. Speed of the mixing process represented as categories: 'Low', 'Medium', 'High'. Missing values should be replaced with 'Not Specified'.
product_quality_score	Continuous. Overall quality score of the final product (rating on a scale of 1 to 10). Missing values should be replaced with mean, rounded to 2 decimal places.

# Write your answer to Task 1 here
import pandas as pd

# Load CSV and parse date
clean_data = pd.read_csv("production_data.csv", parse_dates=["production_date"])

clean_data.columns = ["batch_id", "production_date", "raw_material_supplier", "pigment_type",
    "pigment_quantity", "mixing_time", "mixing_speed", "product_quality_score"]

# batch_id: No missing values allowed
clean_data = clean_data[~clean_data['batch_id'].isnull()]

# raw_material_supplier: replace values and fill missing
clean_data["raw_material_supplier"].replace({1: "national_supplier", 2: "international_supplier"}, inplace=True)
clean_data["raw_material_supplier"].fillna("national_supplier", inplace=True)

# pigment_type: replace missing with 'other' and standardize case
clean_data["pigment_type"] = clean_data["pigment_type"].str.lower()
valid_pigments = ["type_a", "type_b", "type_c"]
clean_data["pigment_type"] = clean_data["pigment_type"].where(clean_data["pigment_type"].isin(valid_pigments), "other")

# pigment_quantity: fill missing with median and clip to 1-100
clean_data["pigment_quantity"].fillna(clean_data["pigment_quantity"].median(), inplace=True)
clean_data["pigment_quantity"] = clean_data["pigment_quantity"].clip(1, 100)

# mixing_time: fill missing with mean (rounded to 2 decimals)
clean_data["mixing_time"].fillna(clean_data["mixing_time"].mean().round(2), inplace=True)

# mixing_speed: fill missing and standardize
clean_data["mixing_speed"].replace("-", "Not Specified", inplace=True)
clean_data["mixing_speed"].fillna("Not Specified", inplace=True)

# product_quality_score: fill missing with mean (rounded to 2 decimals), clip to 1–10
clean_data["product_quality_score"].fillna(clean_data["product_quality_score"].mean().round(2), inplace=True)
clean_data["product_quality_score"] = clean_data["product_quality_score"].clip(1, 10).round(2)

# Convert to category
clean_data["raw_material_supplier"] = clean_data["raw_material_supplier"].astype("category")
clean_data["mixing_speed"] = clean_data["mixing_speed"].astype("category")
clean_data["pigment_type"] = clean_data["pigment_type"].astype("category")

# Task 1 Output
print(clean_data.head())

Task 2

You want to understand how the supplier type and quantity of materials affect the final product attributes.

Calculate the average product_quality_score and pigment_quantity grouped by raw_material_supplier.

You should start with the data in the file 'production_data.csv'.
Your output should be a DataFrame named aggregated_data.
It should include the three columns: raw_material_supplier, avg_product_quality_score, and avg_pigment_quantity.
Your answers should be rounded to 2 decimal places.

# Write your answer to Task 2 here
import pandas as pd

# Load CSV
df = pd.read_csv('production_data.csv')

# Grouping data via raw_material_supplier and calculating average of product_quality_score and pigment_quantity
aggregated_data = df.groupby('raw_material_supplier')[['product_quality_score', 'pigment_quantity']].mean().round(2)

# Renaming columns
aggregated_data = aggregated_data.rename(columns={
    'product_quality_score': 'avg_product_quality_score',
    'pigment_quantity': 'avg_pigment_quantity'
}).reset_index()

# Task 2 Output
print(aggregated_data.head())

Task 3

To get more insight into the factors behind product quality, you want to filter the data to see an average product quality score for a specified set of results.

Identify the average product_quality_score for batches with a raw_material_supplier of 2 and a pigment_quantity greater than 35 kg.

Write a query to return the average avg_product_quality_score for these filtered batches. Use the original production data table, not the output of Task 2.

You should start with the data in the file 'production_data.csv'.
Your output should be a DataFrame named pigment_data.
It should consist of a 1-row DataFrame with 3 columns: raw_material_supplier, pigment_quantity, and avg_product_quality_score.
Your answers should be rounded to 2 decimal places where appropriate.

# Write your answer to Task 3 here
import pandas as pd

# Load CSV
df=pd.read_csv("production_data.csv")

# Filtering data
filtered_data=df[(df["raw_material_supplier"]==2) & (df["pigment_quantity"]>35)]

# Computing for the mean of product_quality_score and pigment_quantity
avg_product_quality_score=filtered_data["product_quality_score"].mean().round(2)
avg_pigment_quantity=filtered_data["pigment_quantity"].mean().round(2)

# Creation of dictionary
dict={'raw_material_supplier':[2],'pigment_quantity':[avg_pigment_quantity], 'avg_product_quality_score':[avg_product_quality_score]}

# Converting dictionary to dataframe
pigment_data=pd.DataFrame(dict)

# Task 3 Output
print(pigment_data)

Task 4

In order to proceed with further analysis later, you need to analyze how various factors relate to product quality. Start by calculating the mean and standard deviation for the following columns: pigment_quantity, and product_quality_score.
These statistics will help in understanding the central tendency and variability of the data related to product quality.

Next, calculate the Pearson correlation coefficient between the following variables: pigment_quantity, and product_quality_score.
These correlation coefficients will provide insights into the strength and direction of the relationships between the factors and overall product quality.

You should start with the data in the file 'production_data.csv'.
Calculate the mean and standard deviation for the columns pigment_quantity and product_quality_score as: product_quality_score_mean, product_quality_score_sd, pigment_quantity_mean, pigment_quantity_sd.
Calculate the Pearson correlation coefficient between pigment_quantity and product_quality_score as: corr_coef
Your output should be a DataFrame named product_quality.
It should include the columns: product_quality_score_mean, product_quality_score_sd, pigment_quantity_mean, pigment_quantity_sd, corr_coef.
Ensure that your answers are rounded to 2 decimal places.

# Write your answer to Task 4 here
import pandas as pd

# Load CSV
df=pd.read_csv("production_data.csv")

# Computing for the mean and std
product_quality_score_mean=df["product_quality_score"].mean().round(2)
product_quality_score_sd=df["product_quality_score"].std().round(2)
pigment_quantity_mean=df["pigment_quantity"].mean().round(2)
pigment_quantity_sd=df["pigment_quantity"].std().round(2)

# Getting the Pearson correlation coefficient
corr_coef=df["pigment_quantity"].corr(df["product_quality_score"]).round(2)

# Creation of dictionary
dict={'product_quality_score_mean':[product_quality_score_mean],'product_quality_score_sd':[product_quality_score_sd],'pigment_quantity_mean':[pigment_quantity_mean],'pigment_quantity_sd':[pigment_quantity_sd],'corr_coef':[corr_coef]}

# Converting dictionary to dataframe
product_quality=pd.DataFrame(dict)

# Task 4 Output
print(product_quality.head())

FORMATTING AND NAMING CHECK

Use the code block below to check that your outputs are correctly named and formatted before you submit your project.

This code checks whether you have met our automarking requirements: that the specified DataFrames exist and contain the required columns. It then prints a table showing ✅ for each column that exists and ❌ for any that are missing, or if the DataFrame itself isn't available.

If a DataFrame or a column in a DataFrame doesn't exist, carefully check your code again.

IMPORTANT: even if your code passes the check below, this does not mean that your entire submission is correct. This is a check for naming and formatting only.

import pandas as pd

def check_columns(output_df, output_df_name, required_columns):
    results = []
    for col in required_columns:
        exists = col in output_df.columns
        results.append({'Dataset': output_df_name, 'Column': col, 'Exists': '✅' if exists else '❌'})
    return results

def safe_check(output_df_name, required_columns):
    results = []
    if output_df_name in globals():
        obj = globals()[output_df_name]
        if isinstance(obj, pd.DataFrame):
            results.extend(check_columns(obj, output_df_name, required_columns))
        elif isinstance(obj, str) and ("SELECT" in obj.upper() or "FROM" in obj.upper()):
            results.append({'Dataset': output_df_name, 'Column': '—', 'Exists': 'ℹ️ SQL query string'})
        else:
            results.append({'Dataset': output_df_name, 'Column': '—', 'Exists': '❌ Not a DataFrame or query'})
    else:
        results.append({'Dataset': output_df_name, 'Column': '—', 'Exists': '❌ Variable not defined'})
    return results

requirements = {
    'clean_data': ['production_date', 'pigment_type', 'mixing_time', 'mixing_speed'],
    'aggregated_data': ['raw_material_supplier', 'avg_product_quality_score', 'avg_pigment_quantity'],
    'pigment_data': ['raw_material_supplier', 'pigment_quantity', 'avg_product_quality_score'],
    'product_quality': ['product_quality_score_mean', 'product_quality_score_sd',
                        'pigment_quantity_mean', 'pigment_quantity_sd', 'corr_coef']
}

all_results = []
for output_df_name, cols in requirements.items():
    all_results += safe_check(output_df_name, cols)

check_results_df = pd.DataFrame(all_results)

print(check_results_df)