supermarket <- data.frame(
BillID = c(501, 502, 503, 504, 505, 506),
CustomerName = c("Aman", "Riya", "Karan", "Neha", "Rohit", "Simran"),
Gender = factor(c("Male", "Female", "Male", "Female", "Male", "Female")),
Membership = factor(c("Gold", "Silver", "Gold", "None", "Silver", "Gold")),
Category = factor(c("Grocery", "Electronics", "Clothing", "Grocery", "Electronics", "Clothing")),
Quantity = c(10, 1, 3, 8, 2, 5),
CostPrice = c(50, 20000, 800, 60, 18000, 700),
SellingPrice = c(70, 25000, 1000, 80, 22000, 900),
Discount = c(100, 2000, 150, 50, 500, 300),
DeliveryType = factor(c("Home", "Store", "Home", "Store", "Home", "Home"))
)
#Q2.1
supermarket$GrossRevenue = supermarket$Quantity * supermarket$SellingPrice
supermarket
## BillID CustomerName Gender Membership Category Quantity CostPrice
## 1 501 Aman Male Gold Grocery 10 50
## 2 502 Riya Female Silver Electronics 1 20000
## 3 503 Karan Male Gold Clothing 3 800
## 4 504 Neha Female None Grocery 8 60
## 5 505 Rohit Male Silver Electronics 2 18000
## 6 506 Simran Female Gold Clothing 5 700
## SellingPrice Discount DeliveryType GrossRevenue
## 1 70 100 Home 700
## 2 25000 2000 Store 25000
## 3 1000 150 Home 3000
## 4 80 50 Store 640
## 5 22000 500 Home 44000
## 6 900 300 Home 4500
#Q2.2
supermarket$TotalCost = supermarket$Quantity * supermarket$CostPrice
supermarket
## BillID CustomerName Gender Membership Category Quantity CostPrice
## 1 501 Aman Male Gold Grocery 10 50
## 2 502 Riya Female Silver Electronics 1 20000
## 3 503 Karan Male Gold Clothing 3 800
## 4 504 Neha Female None Grocery 8 60
## 5 505 Rohit Male Silver Electronics 2 18000
## 6 506 Simran Female Gold Clothing 5 700
## SellingPrice Discount DeliveryType GrossRevenue TotalCost
## 1 70 100 Home 700 500
## 2 25000 2000 Store 25000 20000
## 3 1000 150 Home 3000 2400
## 4 80 50 Store 640 480
## 5 22000 500 Home 44000 36000
## 6 900 300 Home 4500 3500
#Q2.3
supermarket$NetRevenue = supermarket$GrossRevenue - supermarket$Discount
supermarket
## BillID CustomerName Gender Membership Category Quantity CostPrice
## 1 501 Aman Male Gold Grocery 10 50
## 2 502 Riya Female Silver Electronics 1 20000
## 3 503 Karan Male Gold Clothing 3 800
## 4 504 Neha Female None Grocery 8 60
## 5 505 Rohit Male Silver Electronics 2 18000
## 6 506 Simran Female Gold Clothing 5 700
## SellingPrice Discount DeliveryType GrossRevenue TotalCost NetRevenue
## 1 70 100 Home 700 500 600
## 2 25000 2000 Store 25000 20000 23000
## 3 1000 150 Home 3000 2400 2850
## 4 80 50 Store 640 480 590
## 5 22000 500 Home 44000 36000 43500
## 6 900 300 Home 4500 3500 4200
# Q 2.4
supermarket$Profit = supermarket$NetRevenue - supermarket$TotalCost
supermarket
## BillID CustomerName Gender Membership Category Quantity CostPrice
## 1 501 Aman Male Gold Grocery 10 50
## 2 502 Riya Female Silver Electronics 1 20000
## 3 503 Karan Male Gold Clothing 3 800
## 4 504 Neha Female None Grocery 8 60
## 5 505 Rohit Male Silver Electronics 2 18000
## 6 506 Simran Female Gold Clothing 5 700
## SellingPrice Discount DeliveryType GrossRevenue TotalCost NetRevenue Profit
## 1 70 100 Home 700 500 600 100
## 2 25000 2000 Store 25000 20000 23000 3000
## 3 1000 150 Home 3000 2400 2850 450
## 4 80 50 Store 640 480 590 110
## 5 22000 500 Home 44000 36000 43500 7500
## 6 900 300 Home 4500 3500 4200 700
# Q3
subset(supermarket, Membership == "Gold" & Profit > 5000 & DeliveryType == "Home")
## [1] BillID CustomerName Gender Membership Category
## [6] Quantity CostPrice SellingPrice Discount DeliveryType
## [11] GrossRevenue TotalCost NetRevenue Profit
## <0 rows> (or 0-length row.names)
subset(supermarket, Profit < 0)
## [1] BillID CustomerName Gender Membership Category
## [6] Quantity CostPrice SellingPrice Discount DeliveryType
## [11] GrossRevenue TotalCost NetRevenue Profit
## <0 rows> (or 0-length row.names)
subset(supermarket, Quantity >= 2 & Discount > 1000 & Profit > 0)
## [1] BillID CustomerName Gender Membership Category
## [6] Quantity CostPrice SellingPrice Discount DeliveryType
## [11] GrossRevenue TotalCost NetRevenue Profit
## <0 rows> (or 0-length row.names)
subset(supermarket, NetRevenue > 20000 | Membership == "Gold")
## BillID CustomerName Gender Membership Category Quantity CostPrice
## 1 501 Aman Male Gold Grocery 10 50
## 2 502 Riya Female Silver Electronics 1 20000
## 3 503 Karan Male Gold Clothing 3 800
## 5 505 Rohit Male Silver Electronics 2 18000
## 6 506 Simran Female Gold Clothing 5 700
## SellingPrice Discount DeliveryType GrossRevenue TotalCost NetRevenue Profit
## 1 70 100 Home 700 500 600 100
## 2 25000 2000 Store 25000 20000 23000 3000
## 3 1000 150 Home 3000 2400 2850 450
## 5 22000 500 Home 44000 36000 43500 7500
## 6 900 300 Home 4500 3500 4200 700
# Q4
supermarket$ProfitCategory <- ifelse(supermarket$Profit > 10000, "High Profit",
ifelse(supermarket$Profit > 0, "Moderate Profit", "Loss"))
supermarket$RiskFlag <- ifelse(supermarket$Discount > 0.2 * supermarket$GrossRevenue | supermarket$Profit < 0, "Risky", "Safe")
supermarket
## BillID CustomerName Gender Membership Category Quantity CostPrice
## 1 501 Aman Male Gold Grocery 10 50
## 2 502 Riya Female Silver Electronics 1 20000
## 3 503 Karan Male Gold Clothing 3 800
## 4 504 Neha Female None Grocery 8 60
## 5 505 Rohit Male Silver Electronics 2 18000
## 6 506 Simran Female Gold Clothing 5 700
## SellingPrice Discount DeliveryType GrossRevenue TotalCost NetRevenue Profit
## 1 70 100 Home 700 500 600 100
## 2 25000 2000 Store 25000 20000 23000 3000
## 3 1000 150 Home 3000 2400 2850 450
## 4 80 50 Store 640 480 590 110
## 5 22000 500 Home 44000 36000 43500 7500
## 6 900 300 Home 4500 3500 4200 700
## ProfitCategory RiskFlag
## 1 Moderate Profit Safe
## 2 Moderate Profit Safe
## 3 Moderate Profit Safe
## 4 Moderate Profit Safe
## 5 Moderate Profit Safe
## 6 Moderate Profit Safe
#Q5.1
aggregate(Profit~Membership+Category, data = supermarket, sum)
## Membership Category Profit
## 1 Gold Clothing 1150
## 2 Silver Electronics 10500
## 3 Gold Grocery 100
## 4 None Grocery 110
#Q5.2
aggregate(Profit~Gender+DeliveryType, data = supermarket, mean)
## Gender DeliveryType Profit
## 1 Female Home 700.000
## 2 Male Home 2683.333
## 3 Female Store 1555.000
supermarket$ProfitMargin = supermarket$Profit/supermarket$NetRevenue * 100
subset(supermarket, ProfitMargin > 30 & NetRevenue > 20000)
## [1] BillID CustomerName Gender Membership Category
## [6] Quantity CostPrice SellingPrice Discount DeliveryType
## [11] GrossRevenue TotalCost NetRevenue Profit ProfitCategory
## [16] RiskFlag ProfitMargin
## <0 rows> (or 0-length row.names)
supermarket[order(-supermarket$Profit), ]
## BillID CustomerName Gender Membership Category Quantity CostPrice
## 5 505 Rohit Male Silver Electronics 2 18000
## 2 502 Riya Female Silver Electronics 1 20000
## 6 506 Simran Female Gold Clothing 5 700
## 3 503 Karan Male Gold Clothing 3 800
## 4 504 Neha Female None Grocery 8 60
## 1 501 Aman Male Gold Grocery 10 50
## SellingPrice Discount DeliveryType GrossRevenue TotalCost NetRevenue Profit
## 5 22000 500 Home 44000 36000 43500 7500
## 2 25000 2000 Store 25000 20000 23000 3000
## 6 900 300 Home 4500 3500 4200 700
## 3 1000 150 Home 3000 2400 2850 450
## 4 80 50 Store 640 480 590 110
## 1 70 100 Home 700 500 600 100
## ProfitCategory RiskFlag ProfitMargin
## 5 Moderate Profit Safe 17.24138
## 2 Moderate Profit Safe 13.04348
## 6 Moderate Profit Safe 16.66667
## 3 Moderate Profit Safe 15.78947
## 4 Moderate Profit Safe 18.64407
## 1 Moderate Profit Safe 16.66667
supermarket[order(supermarket$NetRevenue)]
## Membership BillID Gender Quantity CustomerName Category
## 1 Gold 501 Male 10 Aman Grocery
## 2 Silver 502 Female 1 Riya Electronics
## 3 Gold 503 Male 3 Karan Clothing
## 4 None 504 Female 8 Neha Grocery
## 5 Silver 505 Male 2 Rohit Electronics
## 6 Gold 506 Female 5 Simran Clothing