Exploratory Data Analysis
Fitri Rachmawati
2024-03-06
Background
Hello, welcome to my Rmd !
I’ll be using Retail.csv data in this LBB
Retail.csv data is a description of the data of retail companies engaged
in one-stop shopping for office supplies, home appliances, and lifestyle
products
Let’s dig deeper into the data and name the object
star_hardware
Import Data
First, we can input data
# code here
star_hardware <- read.csv("data_input/retail.csv")Data Inspection
Then, using the functions head() and tail(), we will see the top data and the last data
head(star_hardware)#> Row.ID Order.ID Order.Date Ship.Date Ship.Mode Customer.ID
#> 1 1 CA-2016-152156 11/8/16 11/11/16 Second Class CG-12520
#> 2 2 CA-2016-152156 11/8/16 11/11/16 Second Class CG-12520
#> 3 3 CA-2016-138688 6/12/16 6/16/16 Second Class DV-13045
#> 4 4 US-2015-108966 10/11/15 10/18/15 Standard Class SO-20335
#> 5 5 US-2015-108966 10/11/15 10/18/15 Standard Class SO-20335
#> 6 6 CA-2014-115812 6/9/14 6/14/14 Standard Class BH-11710
#> Segment Product.ID Category Sub.Category
#> 1 Consumer FUR-BO-10001798 Furniture Bookcases
#> 2 Consumer FUR-CH-10000454 Furniture Chairs
#> 3 Corporate OFF-LA-10000240 Office Supplies Labels
#> 4 Consumer FUR-TA-10000577 Furniture Tables
#> 5 Consumer OFF-ST-10000760 Office Supplies Storage
#> 6 Consumer FUR-FU-10001487 Furniture Furnishings
#> Product.Name Sales
#> 1 Bush Somerset Collection Bookcase 261.9600
#> 2 Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back 731.9400
#> 3 Self-Adhesive Address Labels for Typewriters by Universal 14.6200
#> 4 Bretford CR4500 Series Slim Rectangular Table 957.5775
#> 5 Eldon Fold 'N Roll Cart System 22.3680
#> 6 Eldon Expressions Wood and Plastic Desk Accessories, Cherry Wood 48.8600
#> Quantity Discount Profit
#> 1 2 0.00 41.9136
#> 2 3 0.00 219.5820
#> 3 2 0.00 6.8714
#> 4 5 0.45 -383.0310
#> 5 2 0.20 2.5164
#> 6 7 0.00 14.1694tail(star_hardware)#> Row.ID Order.ID Order.Date Ship.Date Ship.Mode Customer.ID
#> 9989 9989 CA-2017-163629 11/17/17 11/21/17 Standard Class RA-19885
#> 9990 9990 CA-2014-110422 1/21/14 1/23/14 Second Class TB-21400
#> 9991 9991 CA-2017-121258 2/26/17 3/3/17 Standard Class DB-13060
#> 9992 9992 CA-2017-121258 2/26/17 3/3/17 Standard Class DB-13060
#> 9993 9993 CA-2017-121258 2/26/17 3/3/17 Standard Class DB-13060
#> 9994 9994 CA-2017-119914 5/4/17 5/9/17 Second Class CC-12220
#> Segment Product.ID Category Sub.Category
#> 9989 Corporate TEC-PH-10004006 Technology Phones
#> 9990 Consumer FUR-FU-10001889 Furniture Furnishings
#> 9991 Consumer FUR-FU-10000747 Furniture Furnishings
#> 9992 Consumer TEC-PH-10003645 Technology Phones
#> 9993 Consumer OFF-PA-10004041 Office Supplies Paper
#> 9994 Consumer OFF-AP-10002684 Office Supplies Appliances
#> Product.Name
#> 9989 Panasonic KX - TS880B Telephone
#> 9990 Ultra Door Pull Handle
#> 9991 Tenex B1-RE Series Chair Mats for Low Pile Carpets
#> 9992 Aastra 57i VoIP phone
#> 9993 It's Hot Message Books with Stickers, 2 3/4" x 5"
#> 9994 Acco 7-Outlet Masterpiece Power Center, Wihtout Fax/Phone Line Protection
#> Sales Quantity Discount Profit
#> 9989 206.100 5 0.0 55.6470
#> 9990 25.248 3 0.2 4.1028
#> 9991 91.960 2 0.0 15.6332
#> 9992 258.576 2 0.2 19.3932
#> 9993 29.600 4 0.0 13.3200
#> 9994 243.160 2 0.0 72.9480Check dimension data:
dim(star_hardware)#> [1] 9994 15Check names each columns:
names(star_hardware)#> [1] "Row.ID" "Order.ID" "Order.Date" "Ship.Date" "Ship.Mode"
#> [6] "Customer.ID" "Segment" "Product.ID" "Category" "Sub.Category"
#> [11] "Product.Name" "Sales" "Quantity" "Discount" "Profit"From the inspection of the data, we can conclude:
* Star Hardware data contain 9994 of rows and 15 of coloumns
* Column name of the data : “Row.ID”, “Order.ID”, “Order.Date”,
“Ship.Date”, “Ship.Mode”, “Customer.ID”, “Segment”,“Product.ID”,
“Category”, “Sub.Category”, “Product.Name”, “Sales”, “Quantity”,
“Discount”, “Profit”
Data Cleansing
Check data type for each column :
str(star_hardware)#> 'data.frame': 9994 obs. of 15 variables:
#> $ Row.ID : int 1 2 3 4 5 6 7 8 9 10 ...
#> $ Order.ID : chr "CA-2016-152156" "CA-2016-152156" "CA-2016-138688" "US-2015-108966" ...
#> $ Order.Date : chr "11/8/16" "11/8/16" "6/12/16" "10/11/15" ...
#> $ Ship.Date : chr "11/11/16" "11/11/16" "6/16/16" "10/18/15" ...
#> $ Ship.Mode : chr "Second Class" "Second Class" "Second Class" "Standard Class" ...
#> $ Customer.ID : chr "CG-12520" "CG-12520" "DV-13045" "SO-20335" ...
#> $ Segment : chr "Consumer" "Consumer" "Corporate" "Consumer" ...
#> $ Product.ID : chr "FUR-BO-10001798" "FUR-CH-10000454" "OFF-LA-10000240" "FUR-TA-10000577" ...
#> $ Category : chr "Furniture" "Furniture" "Office Supplies" "Furniture" ...
#> $ Sub.Category: chr "Bookcases" "Chairs" "Labels" "Tables" ...
#> $ Product.Name: chr "Bush Somerset Collection Bookcase" "Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back" "Self-Adhesive Address Labels for Typewriters by Universal" "Bretford CR4500 Series Slim Rectangular Table" ...
#> $ Sales : num 262 731.9 14.6 957.6 22.4 ...
#> $ Quantity : int 2 3 2 5 2 7 4 6 3 5 ...
#> $ Discount : num 0 0 0 0.45 0.2 0 0 0.2 0.2 0 ...
#> $ Profit : num 41.91 219.58 6.87 -383.03 2.52 ...We can see that there are some data type that do not match.
Then we need to convert it into corect data type
star_hardware$Ship.Mode <- as.factor(star_hardware$Ship.Mode)
star_hardware$Segment <- as.factor(star_hardware$Segment)
star_hardware$Category <- as.factor(star_hardware$Category)
star_hardware$Sub.Category <- as.factor(star_hardware$Sub.Category)
star_hardware$Order.Date <- as.Date(star_hardware$Order.Date, format = "%m/%d/%y")
star_hardware$Ship.Date <- as.Date(star_hardware$Ship.Date, format = "%m/%d/%y")
str(star_hardware)#> 'data.frame': 9994 obs. of 15 variables:
#> $ Row.ID : int 1 2 3 4 5 6 7 8 9 10 ...
#> $ Order.ID : chr "CA-2016-152156" "CA-2016-152156" "CA-2016-138688" "US-2015-108966" ...
#> $ Order.Date : Date, format: "2016-11-08" "2016-11-08" ...
#> $ Ship.Date : Date, format: "2016-11-11" "2016-11-11" ...
#> $ Ship.Mode : Factor w/ 4 levels "First Class",..: 3 3 3 4 4 4 4 4 4 4 ...
#> $ Customer.ID : chr "CG-12520" "CG-12520" "DV-13045" "SO-20335" ...
#> $ Segment : Factor w/ 3 levels "Consumer","Corporate",..: 1 1 2 1 1 1 1 1 1 1 ...
#> $ Product.ID : chr "FUR-BO-10001798" "FUR-CH-10000454" "OFF-LA-10000240" "FUR-TA-10000577" ...
#> $ Category : Factor w/ 3 levels "Furniture","Office Supplies",..: 1 1 2 1 2 1 2 3 2 2 ...
#> $ Sub.Category: Factor w/ 17 levels "Accessories",..: 5 6 11 17 15 10 3 14 4 2 ...
#> $ Product.Name: chr "Bush Somerset Collection Bookcase" "Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back" "Self-Adhesive Address Labels for Typewriters by Universal" "Bretford CR4500 Series Slim Rectangular Table" ...
#> $ Sales : num 262 731.9 14.6 957.6 22.4 ...
#> $ Quantity : int 2 3 2 5 2 7 4 6 3 5 ...
#> $ Discount : num 0 0 0 0.45 0.2 0 0 0.2 0.2 0 ...
#> $ Profit : num 41.91 219.58 6.87 -383.03 2.52 ...The data types change was successful!
Check missing value on all data:
anyNA(star_hardware)#> [1] FALSECheck missing value each of columns:
colSums(is.na(star_hardware))#> Row.ID Order.ID Order.Date Ship.Date Ship.Mode Customer.ID
#> 0 0 0 0 0 0
#> Segment Product.ID Category Sub.Category Product.Name Sales
#> 0 0 0 0 0 0
#> Quantity Discount Profit
#> 0 0 0Good! No missing value
Now, Star Hardware dataset is ready to be processed and analyzed.
Data Explanation
summary(star_hardware)#> Row.ID Order.ID Order.Date Ship.Date
#> Min. : 1 Length:9994 Min. :2014-01-03 Min. :2014-01-07
#> 1st Qu.:2499 Class :character 1st Qu.:2015-05-23 1st Qu.:2015-05-27
#> Median :4998 Mode :character Median :2016-06-26 Median :2016-06-29
#> Mean :4998 Mean :2016-04-30 Mean :2016-05-03
#> 3rd Qu.:7496 3rd Qu.:2017-05-14 3rd Qu.:2017-05-18
#> Max. :9994 Max. :2017-12-30 Max. :2018-01-05
#>
#> Ship.Mode Customer.ID Segment Product.ID
#> First Class :1538 Length:9994 Consumer :5191 Length:9994
#> Same Day : 543 Class :character Corporate :3020 Class :character
#> Second Class :1945 Mode :character Home Office:1783 Mode :character
#> Standard Class:5968
#>
#>
#>
#> Category Sub.Category Product.Name
#> Furniture :2121 Binders :1523 Length:9994
#> Office Supplies:6026 Paper :1370 Class :character
#> Technology :1847 Furnishings: 957 Mode :character
#> Phones : 889
#> Storage : 846
#> Art : 796
#> (Other) :3613
#> Sales Quantity Discount Profit
#> Min. : 0.444 Min. : 1.00 Min. :0.0000 Min. :-6599.978
#> 1st Qu.: 17.280 1st Qu.: 2.00 1st Qu.:0.0000 1st Qu.: 1.729
#> Median : 54.490 Median : 3.00 Median :0.2000 Median : 8.666
#> Mean : 229.858 Mean : 3.79 Mean :0.1562 Mean : 28.657
#> 3rd Qu.: 209.940 3rd Qu.: 5.00 3rd Qu.:0.2000 3rd Qu.: 29.364
#> Max. :22638.480 Max. :14.00 Max. :0.8000 Max. : 8399.976
#> ✨ Summary :
- The data’s first order and shipment occurred on January 2014.
- Standard Class delivery was the the most popular, and Same Day day was the lowest for shipping for shipping option.
- Star Hardware has the most buyers from the consumer segment and home office segment was the lowest.
- In category, office supplies was the best seller compared to other category.
- In sub.category, other stuff the most popular, while art was the least popular.
- Star Hardware achieved a maximum sales value of 22638.480, a minimum sales value of 0.444, and an average sales value of 229.858
- Within 4 years, the maximum order quantity received by Star Hardware was 14, with an average value of 3.79
- The maximum amount of discount for Star Hardware was 80%, but the average amount of discount was around 15.62%
- Star Hardware has ever had a loss of 6599.978 and a profit of 8399.976, with an average profit of 28.657
Data Manipulation and Transformation
- How much total order quantity since Star Hardware selling the product ?
sum(star_hardware$Quantity)#> [1] 37873Answer : Star Hardware has sold a total quantity of 37873 items.
- How much total profit can be obtained by Star Hardware ?
sum(star_hardware$Profit)#> [1] 286397Answer : Star Hardware has a total profit in 4 years of 286397
- Which has category has the highest profit ?
xtabs(formula = Profit ~ Category, data = star_hardware)#> Category
#> Furniture Office Supplies Technology
#> 18451.27 122490.80 145454.95Answer : Technology Category has a highest profit
plot(star_hardware$Category, star_hardware$Profit)
sd(star_hardware$Profit)#> [1] 234.2601As a result we, discovered the possibility of outliers. Based on calculations that value of Standard Deviation is around 200, indicating that its still within the tolerated range, allowing the process to continue.
- Which ship mode has the highest sales ?
xtabs(Sales~ Ship.Mode, star_hardware)#> Ship.Mode
#> First Class Same Day Second Class Standard Class
#> 351428.4 128363.1 459193.6 1358215.7Answer : Standard class ship mode has a highest sales
- What is total profit generated by each category and segment pair ?
xtabs(formula = Profit ~ Category + Segment, data = star_hardware)#> Segment
#> Category Consumer Corporate Home Office
#> Furniture 6991.079 7584.816 3875.378
#> Office Supplies 56330.321 40227.320 25933.160
#> Technology 70797.810 44166.998 30490.141Answer : In terms of profit, it shows that the most profit in the technology category with buyers from the consumer segment.
- Highest gain in which transaction ?
star_hardware[star_hardware$Profit==8399.976, ]#> Row.ID Order.ID Order.Date Ship.Date Ship.Mode Customer.ID
#> 6827 6827 CA-2016-118689 2016-10-02 2016-10-09 Standard Class TC-20980
#> Segment Product.ID Category Sub.Category
#> 6827 Corporate TEC-CO-10004722 Technology Copiers
#> Product.Name Sales Quantity Discount Profit
#> 6827 Canon imageCLASS 2200 Advanced Copier 17499.95 5 0 8399.976Answer : The highest profit of 8399.976, comes from segment corporate, technology category and buying 5 items without discount.
- Highest loss in which transaction ?
star_hardware[star_hardware$Profit==-6599.978, ]#> Row.ID Order.ID Order.Date Ship.Date Ship.Mode Customer.ID
#> 7773 7773 CA-2016-108196 2016-11-25 2016-12-02 Standard Class CS-12505
#> Segment Product.ID Category Sub.Category
#> 7773 Consumer TEC-MA-10000418 Technology Machines
#> Product.Name Sales Quantity Discount
#> 7773 Cubify CubeX 3D Printer Double Head Print 4499.985 5 0.7
#> Profit
#> 7773 -6599.978Answer : The lowest profit of -6599.978, comes from segment consumer, technology category and buying 5 items with a 70% discount.
- Which the highest average profit value for each category and ship mode ?
cat_ship <- aggregate(x = Profit ~ Category + Ship.Mode, data = star_hardware, FUN = "mean")
cat_ship[order(cat_ship$Profit, decreasing = TRUE), ]#> Category Ship.Mode Profit
#> 3 Technology First Class 91.370643
#> 6 Technology Same Day 88.478483
#> 12 Technology Standard Class 76.829286
#> 9 Technology Second Class 71.454116
#> 8 Office Supplies Second Class 23.496673
#> 2 Office Supplies First Class 20.220142
#> 5 Office Supplies Same Day 19.704047
#> 11 Office Supplies Standard Class 19.405933
#> 7 Furniture Second Class 9.897568
#> 1 Furniture First Class 9.379044
#> 10 Furniture Standard Class 8.301855
#> 4 Furniture Same Day 6.700407Answer : Technology category and first class ship mode has a highest profit.
- Which the highest sales value for each category and Shipmode ?
cat_ship2 <- aggregate(x = Sales ~ Category + Ship.Mode, data = star_hardware, FUN = "mean")
cat_ship2[order(cat_ship2$Sales, decreasing = TRUE), ]#> Category Ship.Mode Sales
#> 6 Technology Same Day 613.5088
#> 3 Technology First Class 463.0086
#> 12 Technology Standard Class 456.9714
#> 9 Technology Second Class 388.5834
#> 7 Furniture Second Class 366.0165
#> 10 Furniture Standard Class 349.2239
#> 1 Furniture First Class 338.6255
#> 4 Furniture Same Day 328.9813
#> 8 Office Supplies Second Class 139.4818
#> 11 Office Supplies Standard Class 117.6309
#> 2 Office Supplies First Class 111.3542
#> 5 Office Supplies Same Day 89.2346Answer : Technology category and same day ship mode has a highest sales
- Which the highest average profit value for each category, segment, shipmode ?
cat_ship3 <- aggregate(x = Profit ~ Category + Segment + Ship.Mode, data = star_hardware, FUN = "mean")
cat_ship3[order(cat_ship3$Profit, decreasing = TRUE), ]#> Category Segment Ship.Mode Profit
#> 9 Technology Home Office First Class 140.4287065
#> 12 Technology Consumer Same Day 107.9766310
#> 18 Technology Home Office Same Day 104.9948696
#> 33 Technology Corporate Standard Class 90.9921822
#> 6 Technology Corporate First Class 80.3914911
#> 21 Technology Consumer Second Class 79.7247320
#> 3 Technology Consumer First Class 77.5889221
#> 36 Technology Home Office Standard Class 76.9501894
#> 27 Technology Home Office Second Class 71.0441029
#> 30 Technology Consumer Standard Class 68.4622364
#> 24 Technology Corporate Second Class 58.7929897
#> 26 Office Supplies Home Office Second Class 42.4075000
#> 8 Office Supplies Home Office First Class 26.7881092
#> 25 Furniture Home Office Second Class 26.6548643
#> 23 Office Supplies Corporate Second Class 26.3686081
#> 17 Office Supplies Home Office Same Day 23.8781906
#> 5 Office Supplies Corporate First Class 21.6471206
#> 32 Office Supplies Corporate Standard Class 20.9527652
#> 14 Office Supplies Corporate Same Day 19.1634406
#> 29 Office Supplies Consumer Standard Class 18.8105655
#> 35 Office Supplies Home Office Standard Class 18.5664683
#> 11 Office Supplies Consumer Same Day 18.5131482
#> 13 Furniture Corporate Same Day 17.9464179
#> 2 Office Supplies Consumer First Class 16.9617439
#> 20 Office Supplies Consumer Second Class 16.1739932
#> 22 Furniture Corporate Second Class 13.1950752
#> 1 Furniture Consumer First Class 12.6752378
#> 31 Furniture Corporate Standard Class 11.5923305
#> 16 Furniture Home Office Same Day 10.2530200
#> 4 Furniture Corporate First Class 8.9415971
#> 34 Furniture Home Office Standard Class 8.1487024
#> 28 Furniture Consumer Standard Class 6.3523951
#> 19 Furniture Consumer Second Class 3.2365119
#> 7 Furniture Home Office First Class 0.9878356
#> 10 Furniture Consumer Same Day 0.5836848
#> 15 Technology Corporate Same Day -0.3903118Answer : Most buyers choose first class for shipping option, and home office was the highest using it
Explanatory Text & Business Recomendation
Explanatory
In this study case, Star Hardware is a retail company engaged in one-stop shopping business for selling office supplies, home appliances and lifestyle products. The company sells three main categories : Furniture, Office Supplies and Technology.
In 4 years, Star Hardware has sold a total of 37.873 items with a total profit value of 286.397. The highest profit in category with a value of 145.454,95 generated from Technology Category , but the lowest profit with a total of 6.599,98 that occured on November 2016 also came from Technology Category.
There are four shipping options: First Class, Same Day, Second Class, and Standard Class. Based in data, First Class ship mode has the highest average profit and Same Day ship mode has the highest average sales when compared to other shipping options. Home office segment is the highest segment that purchases technology product in the first class.
Business Recomendation
Based on calculations, it show that Technology category sells with the highest sales and profit. However, the highest losses were also recorded in the Technology category.
The losses received by the Star Hardware because of the Technology category discount given was too hight (70%). So, you should calculate and analysis it first before deciding what percentage of the discount will be given in order to still get the advantage.
Can increase the target market in the Corporate and Home Office segment, with first class and standard class option shipping by giving them more discounts or vouchers to be interested in repurchasing.