Skip to content

Color Quality Analysis for 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 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 "production_data.csv".
  • Your output should be a dataframe named clean_data.
  • All column names and values should match the table below.
Column NameCriteria
batch_idDiscrete. Identifier for each batch. Missing values are not possible.
production_dateDate. Date when the batch was produced.
raw_material_supplierCategorical. Supplier of the raw materials. (1='national_supplier', 2='international_supplier').
Missing values should be replaced with 'national_supplier'.
pigment_typeNominal. Type of pigment used. ['type_a', 'type_b', 'type_c'].
Missing values should be replaced with 'other'.
pigment_quantityContinuous. Amount of pigment added (in kilograms) (Range: 1 - 100).
Missing values should be replaced with median.
mixing_timeContinuous. Duration of the mixing process (in minutes).
Missing values should be replaced with mean.
mixing_speedCategorical. Speed of the mixing process represented as categories: 'Low', 'Medium', 'High'.
Missing values should be replaced with 'Not Specified'.
product_quality_scoreContinuous. Overall quality score of the final product (rating on a scale of 1 to 10).
Missing values should be replaced with mean.
# Import libraries
import pandas as pd

# Load dataset
data = pd.read_csv('production_data.csv')

# Clean date data
data['production_date'] = pd.to_datetime(data['production_date'], errors='coerce')

# Clean categorical data
data['raw_material_supplier'] = data['raw_material_supplier'].fillna(1).astype('category')
data['pigment_type'] = data['pigment_type'].fillna('other').str.strip().str.lower().astype('category')
data['mixing_speed'] = data['mixing_speed'].replace('-', 'Not Specified').fillna('Not Specified').str.strip().str.title().astype('category')

# Clean continuous data
data['pigment_quantity'] = data['pigment_quantity'].fillna(data['pigment_quantity'].median()).clip(lower=1, upper=100).astype(float)
data['mixing_time'] = data['mixing_time'].fillna(data['mixing_time'].mean()).astype(float)
data['product_quality_score'] = data['product_quality_score'].fillna(data['product_quality_score'].mean()).clip(lower=1, upper=10).astype(float)

# Assign output to a new dataframe
clean_data = data
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 data frame 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.
# Group by supplier and aggregate the data
aggregated_data = clean_data.groupby('raw_material_supplier')[['product_quality_score', 'pigment_quantity']].mean().round(2)

# Reset the index
aggregated_data = aggregated_data.reset_index()

# Rename the columns for clarity
aggregated_data.columns = ['raw_material_supplier', 'avg_product_quality_score', 'avg_pigment_quantity']

aggregated_data

Task 3

Identify all product_quality_score values for batches with a raw_material_supplier of 2 and a pigment_quantity greater than 35 kg. 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 data frame named pigment_data.
  • It should include the three columns: raw_material_supplier, pigment_quantity, and product_quality_score.
  • Your answers should be rounded to 3 decimal places.
# Filter the data
filtered_data = data[(data['raw_material_supplier'] == 2) & (data['pigment_quantity'] > 35)]

# Select the relevant columns and round the values
pigment_data = filtered_data[['raw_material_supplier', 'pigment_quantity', 'product_quality_score']].round(3)

pigment_data.head()

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 data frame 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.
# Calculate mean and standard deviation
agg_data = clean_data[['pigment_quantity', 'product_quality_score']].agg(['mean', 'std']).round(2)
        
# Create dictionary with mean and standard deviation values
agg_dict = {
    'pigment_quantity_mean': agg_data.loc['mean', 'pigment_quantity'],
    'pigment_quantity_sd': agg_data.loc['std', 'pigment_quantity'],
    'product_quality_score_mean': agg_data.loc['mean', 'product_quality_score'],
    'product_quality_score_sd': agg_data.loc['std', 'product_quality_score']    
}

# Create a DataFrame from the dictionary
product_quality = pd.DataFrame([agg_dict])

# Calculate the Pearson correlation coefficient
product_quality['corr_coef'] = clean_data['pigment_quantity'].corr(clean_data['product_quality_score']).round(2)

product_quality