ANLY482 AY2016-17 T2 Group10 Project Overview: Methodology
Contents
Data Preparation
Data preparation involves cleaning, transformation, and integration, which are standard procedures to standardize data across different datasets for their many formats, errors in data entries and granularity. We will first look at each of the data files, determine best ways to standardize formats and then perform aggregations on more granular data for integration purposes.
MCCP
Invoice Details
Data Cleaning
A brief scan of the entire Invoice Details data table led to 3 main areas to be cleaned.
- Missing values in Price$ column
- Negative values in Sales Qty and Amount$ columns
- Some Postal Code with only 5 digits (because they start with 0)
Handling of missing values in Price$ column
The Price$ column determines the unit price of a specific dosage (SKU) of a drug and it can vary across different customers, time for different reasons (marketing, incentive for new purchase, etc). It becomes important for us to know why some of them have missing values because the unit price of any drug is usually defined before any purchase.
Upon close inspection on the missing values using data filter, we are made known the following:
- 2379 rows with missing
- 1677 rows belong to product E/F
- Most records have sales amount which are $0
- Either Bonus Qty or Sample Qty are positive
This tell us that these rows represented transactions that took place when drugs are given as samples or bonuses to serve as goodwill.
Actions taken:
- We will be assigning a fair value of 0 to the missing values as JMP will ignore rows which have missing values if we were to take into consideration of price in our predictive analysis.
Handling of negative values in Sales Qty and Amount$ columns
The Sales Qty and Amount$ columns indicate the quantity of drug and total amount involved in the transaction for a dosage (SKU) of a drug. Out of pure curiosity of the presence of negative values, we asked Elaine and she explained that negative values are credit amount which are needed to offset the initial sales.
We are made known of the following:
- 1233 rows with both Sales Qty and Amount$ negative
- Have corresponding transactions
- 229 rows with only Amount$ negative, all Sales Qty = 0, all Price$ have missing values
- No corresponding transactions
Actions taken:
- For the 229 rows, there is no indication of what the credit sales could be for and hence, we will filter them out.
- For the 1233 rows, we can simply ignore them as the corresponding transactions will cancel them out.
