Medical Inventory Optimization
Exploratory Data Analysis and Pre-Processing(SQL)
Checking the schema of the dataset to ensure that all columns have the correct format.
DESCRIBE projectfinaldata;
Output:
ixing inconsistent date formats in the Dateofbill column and creating a new table
F
clean_projectfinaldata with transformed date values and the other selected columns from
the ‘projectfinaldata’ table.
CREATE TABLE clean_projectfinaldata AS
SELECT Typeofsales, Patient_ID, Specialisation, Dept,
STR_TO_DATE(REPLACE(Dateofbill,'/','-'),'%m-%d-%Y') AS Dateofbill, Quantity, ReturnQuantity,
Final_Cost, Final_Sales, RtnMRP, Formulation, DrugName, SubCat, SubCat1
FROM projectfinaldata;
ounting the missing and non-missing values for each column and the total number of rows in
C
the 'clean_projectfinaldata' table.
SELECT
COUNT(CASE WHEN TRIM(Typeofsales) = '' OR Typeofsales IS NULL THEN 1 END) AS
typeofsales_missing,
OUNT(CASE WHEN TRIM(Typeofsales) <> '' AND Typeofsales IS NOT NULL THEN 1 END)
C
AS
typeofsales_non_missing,
COUNT(CASE WHEN Patient_ID IS NULL THEN 1 END) AS patient_id_missing,
COUNT(CASE WHEN Patient_ID IS NOT NULL THEN 1 END) AS patient_id_non_missing,
COUNT(CASE WHEN TRIM(Specialisation) = '' OR Specialisation IS NULL THEN 1 END) AS
specialisation_missing,
COUNT(CASE WHEN TRIM(Specialisation) <> '' AND Specialisation IS NOT NULL THEN 1
END) AS specialisation_non_missing,
COUNT(CASE WHEN TRIM(Dept) = '' OR Dept IS NULL THEN 1 END) AS dept_missing,
COUNT(CASE WHEN TRIM(Dept) <> '' AND Dept IS NOT NULL THEN 1 END) AS
dept_non_missing, COUNT(CASE WHEN TRIM(Dateofbill) = '' OR Dateofbill IS NULL THEN 1
END) AS dateofbill_missing,
COUNT(CASE WHEN TRIM(Dateofbill) <> '' AND Dateofbill IS NOT NULL THEN 1 END) AS
dateofbill_non_missing,
COUNT(CASE WHEN Quantity IS NULL THEN 1 END) AS quantity_missing,
COUNT(CASE WHEN Quantity IS NOT NULL THEN 1 END) AS quantity_non_missing,
COUNT(CASE WHEN ReturnQuantity IS NULL THEN 1 END) AS returnquantity_missing,
COUNT(CASE WHEN ReturnQuantity IS NOT NULL THEN 1 END) AS
returnquantity_non_missing, COUNT(CASE WHEN Final_Cost IS NULL THEN 1 END) AS
final_cost_missing,
COUNT(CASE WHEN Final_Cost IS NOT NULL THEN 1 END) AS final_cost_non_missing,
COUNT(CASE WHEN Final_Sales IS NULL THEN 1 END) AS final_sales_missing,
COUNT(CASE WHEN Final_Sales IS NOT NULL THEN 1 END) AS final_sales_non_missing,
COUNT(CASE WHEN RtnMRP IS NULL THEN 1 END) AS rtnmrp_missing,
COUNT(CASE WHEN RtnMRP IS NOT NULL THEN 1 END) AS rtnmrp_non_missing,
COUNT(CASE WHEN TRIM(Formulation) = '' OR Formulation IS NULL THEN 1 END) AS
formulation_missing,
OUNT(CASE WHEN TRIM(Formulation) <> '' AND Formulation IS NOT NULL THEN 1 END)
C
AS
formulation_non_missing,
COUNT(CASE WHEN TRIM(DrugName) = '' OR DrugName IS NULL THEN 1 END) AS
drugname_missing,
COUNT(CASE WHEN TRIM(DrugName) <> '' AND DrugName IS NOT NULL THEN 1 END) AS
drugname_non_missing,
COUNT(CASE WHEN TRIM(SubCat) = '' OR SubCat IS NULL THEN 1 END) AS subcat_missing,
COUNT(CASE WHEN TRIM(SubCat) <> '' AND SubCat IS NOT NULL THEN 1 END) AS
subcat_non_missing,
OUNT(CASE WHEN TRIM(SubCat1) = '' OR SubCat1 IS NULL THEN 1 END) AS
C
subcat1_missing,
COUNT(CASE WHEN TRIM(SubCat1) <> '' AND SubCat1 IS NOT NULL THEN 1 END) AS
subcat1_non_missing,
COUNT(*) AS
total_rows
FROM clean_projectfinaldata;
Output:
eplacing the missing values with ‘unknown’ in the columns Formulation, DrugName,
R
SubCat and SubCat1.
UPDATE clean_projectfinaldata
SET
Formulation = CASE WHEN Formulation = '' THEN 'unknown' ELSE Formulation END;
UPDATE clean_projectfinaldata
SET
DrugName = CASE WHEN DrugName = '' THEN 'unknown' ELSE DrugName END;
UPDATE clean_projectfinaldata
SET
SubCat = CASE WHEN SubCat = '' THEN 'unknown' ELSE SubCat END;
UPDATE clean_projectfinaldata
SET
SubCat1 = CASE WHEN SubCat1 = '' THEN 'unknown' ELSE SubCat1 END;
Showing the columns after replacing the missing values with ‘unknown’:
SELECT Formulation, DrugName, SubCat, SubCat1 FROM clean_projectfinaldata;
reating a new table called `missing_values` by selecting rows from `clean_projectfinaldata`
C
where any of the columns (`Formulation`, `DrugName`, `SubCat`, or `SubCat1`) has the
value 'unknown'.
CREATE TABLE missing_values
AS SELECT *
FROM clean_projectfinaldata
WHERE Formulation =
'unknown'
OR DrugName = 'unknown'
OR SubCat = 'unknown'
OR SubCat1 = 'unknown';
Showing missing_values table and count of records with at least one or more missing values:
SELECT * FROM missing_values;
SELECT COUNT(*) AS missing_records_count FROM missing_values;
Output:
I dentifying duplicate rows based on Patient_ID, Dateofbill, and DrugName excluding rows
where the DrugName is 'unknown'.
SELECT Patient_ID, Dateofbill, DrugName,
COUNT(*) FROM clean_projectfinaldata
WHERE DrugName <> 'unknown'
GROUP BY Patient_ID, Dateofbill, DrugName
HAVING COUNT(*) > 1;
Output:
emoving the duplicate rows from clean_projectfinaldata table and counting the remaining
R
rows.
DELETE FROM clean_projectfinaldata
WHERE (Patient_ID, Dateofbill, DrugName)
IN ( SELECT t.Patient_ID, [Link],
[Link] FROM (
SELECT Patient_ID, Dateofbill,
DrugName FROM
clean_projectfinaldata
GROUP BY Patient_ID, Dateofbill, DrugName
HAVING COUNT(*) > 1
) AS t
);
SELECT COUNT(*) AS total_rows FROM clean_projectfinaldata;
Output:
Set the current database to "med_inventory".
USE med_inventory;
Displaying the table.
SELECT * FROM cleaned_table LIMIT 20;
Output:
Business decisions based on pre-processed data
alculating the first moment (measures of central tendency such as mean, median, mode) for the
C
dataset.
Mean:
SELECT
ROUND(AVG(Quantity), 2) AS mean_quantity,
ROUND(AVG(ReturnQuantity), 2) AS mean_return_quantity, ROUND(AVG(Final_Cost), 2) AS
mean_final_cost,
ROUND(AVG(Final_Sales), 2) AS mean_final_sales,
ROUND(AVG(RtnMRP), 2) AS mean_rtnmrp FROM cleaned_table;
Output:
Median:
SELECT
ROUND(AVG(Final_Cost), 2) AS median_final_cost, ROUND(AVG(Final_Sales), 2) AS
median_final_sales, ROUND(AVG(Quantity), 2) AS median_quantity,
ROUND(AVG(ReturnQuantity), 2) AS median_return_quantity, ROUND(AVG(RtnMRP), 2) AS
median_rtnmrp
FROM (
SELECT Final_Cost, Final_Sales, Quantity, ReturnQuantity, RtnMRP, ROW_NUMBER() OVER
(ORDER BY Final_Cost) AS row_num,
OUNT(*) OVER () AS total_rows FROM cleaned_table
C
) AS subquery
WHERE row_num IN (FLOOR((total_rows + 1) / 2), CEILING((total_rows + 1) / 2));
Mode:
SELECT
mode_quantity.mode_value AS mode_quantity,
mode_return_quantity.mode_value AS mode_return_quantity, mode_final_cost.mode_value AS
mode_final_cost, mode_final_sales.mode_value AS mode_final_sales,
mode_rtnmrp.mode_value AS mode_rtnmrp FROM (
SELECT Quantity AS mode_value, COUNT(*) AS mode_count FROM cleaned_table
GROUP BY Quantity
ORDER BY COUNT(*) DESC LIMIT 1
) AS mode_quantity, (
SELECT ReturnQuantity AS mode_value, COUNT(*) AS mode_count FROM cleaned_table
GROUP BY ReturnQuantity ORDER BY COUNT(*) DESC LIMIT 1
) AS mode_return_quantity, (
SELECT Final_Cost AS mode_value, COUNT(*) AS mode_count FROM cleaned_table
ROUP BY Final_Cost
G
ORDER BY COUNT(*) DESC LIMIT 1
) AS mode_final_cost, (
SELECT Final_Sales AS mode_value, COUNT(*) AS mode_count FROM cleaned_table
GROUP BY Final_Sales
ORDER BY COUNT(*) DESC LIMIT 1
) AS mode_final_sales, (
SELECT RtnMRP AS mode_value, COUNT(*) AS mode_count FROM cleaned_table
GROUP BY RtnMRP
ORDER BY COUNT(*) DESC LIMIT 1
) AS mode_rtnmrp;
Output:
alculating the second moment (measures of dispersion such as variance, standard deviation, range)
C
for the dataset.
Variance:
SELECT
ROUND(VARIANCE(Quantity), 2) AS variance_quantity,
ROUND(VARIANCE(ReturnQuantity), 2) AS variance_return_quantity,
ROUND(VARIANCE(Final_Cost), 2) AS variance_final_cost,
OUND(VARIANCE(Final_Sales), 2) AS variance_final_sales, ROUND(VARIANCE(RtnMRP), 2) AS
R
variance_rtnmrp
FROM cleaned_table;
Output:
Standard Deviation:
SELECT
ROUND(STDDEV(Quantity), 2) AS stddev_quantity,
ROUND(STDDEV(ReturnQuantity), 2) AS stddev_return_quantity, ROUND(STDDEV(Final_Cost), 2)
AS stddev_final_cost,
ROUND(STDDEV(Final_Sales), 2) AS stddev_final_sales, ROUND(STDDEV(RtnMRP), 2) AS
stddev_rtnmrp
FROM cleaned_table;
Output:
Range:
SELECT
MAX(Quantity) - MIN(Quantity) AS range_quantity, MAX(ReturnQuantity) - MIN(ReturnQuantity) AS
range_return_quantity, MAX(Final_Cost) - MIN(Final_Cost) AS range_final_cost,
MAX(Final_Sales) - MIN(Final_Sales) AS range_final_sales, MAX(RtnMRP) - MIN(RtnMRP) AS
range_rtnmrp
FROM cleaned_table;
Output:
Calculating the third moment (skewness) for the dataset.
SELECT 'Quantity' AS column_name,
OUND((SUM(POW(Quantity - (SELECT AVG(Quantity) FROM cleaned_table), 3)) / (COUNT(*) *
R
POW(STDDEV(Quantity), 3))), 2) AS skewness_value
FROM cleaned_table UNION ALL
SELECT 'ReturnQuantity' AS column_name,
OUND((SUM(POW(ReturnQuantity - (SELECT AVG(ReturnQuantity) FROM cleaned_table), 3)) /
R
(COUNT(*) * POW(STDDEV(ReturnQuantity), 3))), 2) AS skewness_value
FROM cleaned_table UNION ALL
SELECT 'Final_Cost' AS column_name,
OUND((SUM(POW(Final_Cost - (SELECT AVG(Final_Cost) FROM cleaned_table), 3)) / (COUNT(*) *
R
POW(STDDEV(Final_Cost), 3))), 2) AS skewness_value
FROM cleaned_table UNION ALL
SELECT 'Final_Sales' AS column_name,
OUND((SUM(POW(Final_Sales - (SELECT AVG(Final_Sales) FROM cleaned_table), 3)) / (COUNT(*)
R
* POW(STDDEV(Final_Sales), 3))), 2) AS skewness_value
FROM cleaned_table UNION ALL
SELECT 'RtnMRP' AS column_name,
OUND((SUM(POW(RtnMRP - (SELECT AVG(RtnMRP) FROM cleaned_table), 3)) / (COUNT(*) *
R
POW(STDDEV(RtnMRP), 3))), 2) AS skewness_value FROM cleaned_table;
Output:
Calculating the fourth moment (kurtosis) for the dataset.
SELECT
OUND((SUM(POWER(Quantity - avg_value, 4)) / (COUNT(Quantity) * POWER(STDDEV(Quantity),
R
4))), 2) AS kurtosis_quantity,
OUND((SUM(POWER(ReturnQuantity - avg_value, 4)) / (COUNT(ReturnQuantity) *
R
POWER(STDDEV(ReturnQuantity), 4))), 2) AS kurtosis_return_quantity,
OUND((SUM(POWER(Final_Cost - avg_value, 4)) / (COUNT(Final_Cost) *
R
POWER(STDDEV(Final_Cost), 4))), 2) AS kurtosis_final_cost,
OUND((SUM(POWER(Final_Sales - avg_value, 4)) / (COUNT(Final_Sales) *
R
POWER(STDDEV(Final_Sales), 4))), 2) AS kurtosis_final_sales,
OUND((SUM(POWER(RtnMRP - avg_value, 4)) / (COUNT(RtnMRP) * POWER(STDDEV(RtnMRP),
R
4))),
2) AS kurtosis_rtnmrp FROM
(SELECT
AVG(Quantity) AS avg_value, STDDEV(Quantity) AS stddev_value, COUNT(Quantity) AS
count_value
FROM projectfinaldata) AS subquery, cleaned_table;
Output: