``
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"))
)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
## 1 70 100 Home
## 2 25000 2000 Store
## 3 1000 150 Home
## 4 80 50 Store
## 5 22000 500 Home
## 6 900 300 Homesupermarket$GrossRevenue <- supermarket$Quantity * supermarket$SellingPrice
supermarket$TotalCost <- supermarket$Quantity * supermarket$CostPrice
supermarket$NetRevenue <- supermarket$GrossRevenue - supermarket$Discount
supermarket$Profit <- supermarket$NetRevenue - supermarket$TotalCostsupermarket## 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# 1. Gold members, Profit >5000, Home delivery
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)# 2. Loss-making transactions
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)# 3. Electronics orders (Quantity >=2, Discount >1000, Profit >0)
subset(supermarket, Category == "Electronics" & 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)# 4. Premium customers (NetRevenue >20000 OR Gold)
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# ProfitCategory
supermarket$ProfitCategory <- ifelse(supermarket$Profit > 10000, "High Profit",
ifelse(supermarket$Profit > 0, "Moderate Profit", "Loss"))
# RiskFlag
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# Total Profit by Membership & Category
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# Average Profit by Gender & DeliveryType
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# Profit Margin
supermarket$ProfitMargin <- (supermarket$Profit / supermarket$NetRevenue) * 100
# Orders where ProfitMargin >30 & NetRevenue >20000
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## 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 ProfitMargin
## 1 Moderate Profit Safe 16.66667
## 2 Moderate Profit Safe 13.04348
## 3 Moderate Profit Safe 15.78947
## 4 Moderate Profit Safe 18.64407
## 5 Moderate Profit Safe 17.24138
## 6 Moderate Profit Safe 16.66667supermarket## 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 ProfitMargin
## 1 Moderate Profit Safe 16.66667
## 2 Moderate Profit Safe 13.04348
## 3 Moderate Profit Safe 15.78947
## 4 Moderate Profit Safe 18.64407
## 5 Moderate Profit Safe 17.24138
## 6 Moderate Profit Safe 16.66667supermarket_sorted <- supermarket[order(-supermarket$Profit, -supermarket$NetRevenue), ]
supermarket_sorted## 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.66667supermarket## 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 ProfitMargin
## 1 Moderate Profit Safe 16.66667
## 2 Moderate Profit Safe 13.04348
## 3 Moderate Profit Safe 15.78947
## 4 Moderate Profit Safe 18.64407
## 5 Moderate Profit Safe 17.24138
## 6 Moderate Profit Safe 16.66667# 1. Gold, Home delivery, Profit >8000
subset(supermarket, Membership == "Gold" & DeliveryType == "Home" & Profit > 8000)## [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)# 2. Discount exceeds 15% & Electronics
subset(supermarket, Discount > 0.15 * GrossRevenue & Category == "Electronics")## [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)# 3. Female Profit >5000 OR Grocery
subset(supermarket, Gender == "Female" & (Profit > 5000 | Category == "Grocery"))## BillID CustomerName Gender Membership Category Quantity CostPrice
## 4 504 Neha Female None Grocery 8 60
## SellingPrice Discount DeliveryType GrossRevenue TotalCost NetRevenue Profit
## 4 80 50 Store 640 480 590 110
## ProfitCategory RiskFlag ProfitMargin
## 4 Moderate Profit Safe 18.64407# 4. Risky transactions
subset(supermarket, Profit < 0 | Discount > 0.25 * GrossRevenue)## [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)# 5. Top 3 profitable transactions
head(supermarket_sorted, 3)## 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
## 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
## ProfitCategory RiskFlag ProfitMargin
## 5 Moderate Profit Safe 17.24138
## 2 Moderate Profit Safe 13.04348
## 6 Moderate Profit Safe 16.66667# 6. Average profit Gold vs Silver
aggregate(Profit ~ Membership, data = supermarket[supermarket$Membership %in% c("Gold","Silver"), ], mean)## Membership Profit
## 1 Gold 416.6667
## 2 Silver 5250.0000# 7. TotalCost > NetRevenue
subset(supermarket, TotalCost > NetRevenue)## [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)# 8. Membership with highest total profit
aggregate(Profit ~ Membership, data = supermarket, sum)## Membership Profit
## 1 Gold 1250
## 2 None 110
## 3 Silver 10500# 9. Delivery type with lowest average profit
aggregate(Profit ~ DeliveryType, data = supermarket, mean)## DeliveryType Profit
## 1 Home 2187.5
## 2 Store 1555.0# 10. High revenue but low margin
subset(supermarket, NetRevenue > 20000 & ProfitMargin < 20)## BillID CustomerName Gender Membership Category Quantity CostPrice
## 2 502 Riya Female Silver Electronics 1 20000
## 5 505 Rohit Male Silver Electronics 2 18000
## SellingPrice Discount DeliveryType GrossRevenue TotalCost NetRevenue Profit
## 2 25000 2000 Store 25000 20000 23000 3000
## 5 22000 500 Home 44000 36000 43500 7500
## ProfitCategory RiskFlag ProfitMargin
## 2 Moderate Profit Safe 13.04348
## 5 Moderate Profit Safe 17.24138supermarket## 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 ProfitMargin
## 1 Moderate Profit Safe 16.66667
## 2 Moderate Profit Safe 13.04348
## 3 Moderate Profit Safe 15.78947
## 4 Moderate Profit Safe 18.64407
## 5 Moderate Profit Safe 17.24138
## 6 Moderate Profit Safe 16.66667