Ecommerce <- data.frame(
Order_ID = c(1001, 1002, 1003, 1004, 1005),
CustomerName = c("Aman", "Riya", "Karan", "Neha", "Rohit"),
Category = factor(c("Electronics", "Clothing", "Electronics", "Home", "Clothing")),
PaymentMethod = factor(c("UPI", "Card", "Card", "Cash", "UPI")),
Quantity = c(1, 3, 2, 1, 4),
PricePerUnit = c(25000, 1500, 20000, 5000, 1200),
Discount = c(2000,300, 1500, 500, NA),
DeliveryDays=c(3,5,2,4,6)
)
str(Ecommerce)
## 'data.frame': 5 obs. of 8 variables:
## $ Order_ID : num 1001 1002 1003 1004 1005
## $ CustomerName : chr "Aman" "Riya" "Karan" "Neha" ...
## $ Category : Factor w/ 3 levels "Clothing","Electronics",..: 2 1 2 3 1
## $ PaymentMethod: Factor w/ 3 levels "Card","Cash",..: 3 1 1 2 3
## $ Quantity : num 1 3 2 1 4
## $ PricePerUnit : num 25000 1500 20000 5000 1200
## $ Discount : num 2000 300 1500 500 NA
## $ DeliveryDays : num 3 5 2 4 6
summary(Ecommerce)
## Order_ID CustomerName Category PaymentMethod Quantity
## Min. :1001 Length:5 Clothing :2 Card:2 Min. :1.0
## 1st Qu.:1002 Class :character Electronics:2 Cash:1 1st Qu.:1.0
## Median :1003 Mode :character Home :1 UPI :2 Median :2.0
## Mean :1003 Mean :2.2
## 3rd Qu.:1004 3rd Qu.:3.0
## Max. :1005 Max. :4.0
##
## PricePerUnit Discount DeliveryDays
## Min. : 1200 Min. : 300 Min. :2
## 1st Qu.: 1500 1st Qu.: 450 1st Qu.:3
## Median : 5000 Median :1000 Median :4
## Mean :10540 Mean :1075 Mean :4
## 3rd Qu.:20000 3rd Qu.:1625 3rd Qu.:5
## Max. :25000 Max. :2000 Max. :6
## NA's :1
Ecommerce$Category
## [1] Electronics Clothing Electronics Home Clothing
## Levels: Clothing Electronics Home
Ecommerce$PricePerUnit
## [1] 25000 1500 20000 5000 1200
Gross_Amount <- Ecommerce$Quantity * Ecommerce$PricePerUnit
Ecommerce <- cbind(
Ecommerce[, 1:6],
GrossAmount = Gross_Amount,
Ecommerce[, 7:8]
)
Ecommerce
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 1 1001 Aman Electronics UPI 1 25000
## 2 1002 Riya Clothing Card 3 1500
## 3 1003 Karan Electronics Card 2 20000
## 4 1004 Neha Home Cash 1 5000
## 5 1005 Rohit Clothing UPI 4 1200
## GrossAmount Discount DeliveryDays
## 1 25000 2000 3
## 2 4500 300 5
## 3 40000 1500 2
## 4 5000 500 4
## 5 4800 NA 6
Net_Amount <- Ecommerce$GrossAmount -
ifelse(is.na(Ecommerce$Discount), 0, Ecommerce$Discount)
discount_index <- which(colnames(Ecommerce) == "Discount")
Ecommerce <- cbind(
Ecommerce[, 1:discount_index, drop = FALSE],
NetAmount = Net_Amount,
Ecommerce[, (discount_index + 1):ncol(Ecommerce), drop = FALSE]
)
Ecommerce
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 1 1001 Aman Electronics UPI 1 25000
## 2 1002 Riya Clothing Card 3 1500
## 3 1003 Karan Electronics Card 2 20000
## 4 1004 Neha Home Cash 1 5000
## 5 1005 Rohit Clothing UPI 4 1200
## GrossAmount Discount NetAmount DeliveryDays
## 1 25000 2000 23000 3
## 2 4500 300 4200 5
## 3 40000 1500 38500 2
## 4 5000 500 4500 4
## 5 4800 NA 4800 6
new_order <- data.frame(
Order_ID = 1006,
CustomerName = "Simran",
Category = factor("Electronics", levels = levels(Ecommerce$Category)),
PaymentMethod = factor("Card", levels = levels(Ecommerce$PaymentMethod)),
Quantity = 2,
PricePerUnit = 18000,
Discount = 2000,
DeliveryDays = 3
)
new_order$GrossAmount <- new_order$Quantity * new_order$PricePerUnit
new_order$NetAmount <- new_order$GrossAmount -
ifelse(is.na(new_order$Discount), 0, new_order$Discount)
new_order <- new_order[, colnames(Ecommerce)]
Ecommerce <- rbind(Ecommerce, new_order)
Ecommerce
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 1 1001 Aman Electronics UPI 1 25000
## 2 1002 Riya Clothing Card 3 1500
## 3 1003 Karan Electronics Card 2 20000
## 4 1004 Neha Home Cash 1 5000
## 5 1005 Rohit Clothing UPI 4 1200
## 6 1006 Simran Electronics Card 2 18000
## GrossAmount Discount NetAmount DeliveryDays
## 1 25000 2000 23000 3
## 2 4500 300 4200 5
## 3 40000 1500 38500 2
## 4 5000 500 4500 4
## 5 4800 NA 4800 6
## 6 36000 2000 34000 3
Tax <- 0.10 * Ecommerce$NetAmount
discount_index <- which(colnames(Ecommerce) == "Discount")
Ecommerce <- cbind(
Ecommerce[, 1:discount_index, drop = FALSE],
Tax = Tax,
Ecommerce[, (discount_index + 1):ncol(Ecommerce), drop = FALSE]
)
Ecommerce
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 1 1001 Aman Electronics UPI 1 25000
## 2 1002 Riya Clothing Card 3 1500
## 3 1003 Karan Electronics Card 2 20000
## 4 1004 Neha Home Cash 1 5000
## 5 1005 Rohit Clothing UPI 4 1200
## 6 1006 Simran Electronics Card 2 18000
## GrossAmount Discount Tax NetAmount DeliveryDays
## 1 25000 2000 2300 23000 3
## 2 4500 300 420 4200 5
## 3 40000 1500 3850 38500 2
## 4 5000 500 450 4500 4
## 5 4800 NA 480 4800 6
## 6 36000 2000 3400 34000 3
new_row <- data.frame(
Order_ID = 1007,
CustomerName = "Arjun",
Category = factor("Home", levels = levels(Ecommerce$Category)),
PaymentMethod = factor("Cash", levels = levels(Ecommerce$PaymentMethod)),
Quantity = 2,
PricePerUnit = 4000,
GrossAmount = 2 * 4000,
Discount = 500,
Tax = 0.10 * (2 * 4000 - 500),
NetAmount = (2 * 4000 - 500),
DeliveryDays = 4
)
Ecommerce <- rbind(
Ecommerce[1:2, ],
new_row,
Ecommerce[3:nrow(Ecommerce), ]
)
rownames(Ecommerce) <- NULL
Ecommerce
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 1 1001 Aman Electronics UPI 1 25000
## 2 1002 Riya Clothing Card 3 1500
## 3 1007 Arjun Home Cash 2 4000
## 4 1003 Karan Electronics Card 2 20000
## 5 1004 Neha Home Cash 1 5000
## 6 1005 Rohit Clothing UPI 4 1200
## 7 1006 Simran Electronics Card 2 18000
## GrossAmount Discount Tax NetAmount DeliveryDays
## 1 25000 2000 2300 23000 3
## 2 4500 300 420 4200 5
## 3 8000 500 750 7500 4
## 4 40000 1500 3850 38500 2
## 5 5000 500 450 4500 4
## 6 4800 NA 480 4800 6
## 7 36000 2000 3400 34000 3
Ecommerce$OrderType <- ifelse(
Ecommerce$NetAmount > 20000,
"High Value",
"Regular"
)
Ecommerce
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 1 1001 Aman Electronics UPI 1 25000
## 2 1002 Riya Clothing Card 3 1500
## 3 1007 Arjun Home Cash 2 4000
## 4 1003 Karan Electronics Card 2 20000
## 5 1004 Neha Home Cash 1 5000
## 6 1005 Rohit Clothing UPI 4 1200
## 7 1006 Simran Electronics Card 2 18000
## GrossAmount Discount Tax NetAmount DeliveryDays OrderType
## 1 25000 2000 2300 23000 3 High Value
## 2 4500 300 420 4200 5 Regular
## 3 8000 500 750 7500 4 Regular
## 4 40000 1500 3850 38500 2 High Value
## 5 5000 500 450 4500 4 Regular
## 6 4800 NA 480 4800 6 Regular
## 7 36000 2000 3400 34000 3 High Value
Ecommerce$Tax <- NULL
colnames(Ecommerce)
## [1] "Order_ID" "CustomerName" "Category" "PaymentMethod"
## [5] "Quantity" "PricePerUnit" "GrossAmount" "Discount"
## [9] "NetAmount" "DeliveryDays" "OrderType"
Ecommerce
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 1 1001 Aman Electronics UPI 1 25000
## 2 1002 Riya Clothing Card 3 1500
## 3 1007 Arjun Home Cash 2 4000
## 4 1003 Karan Electronics Card 2 20000
## 5 1004 Neha Home Cash 1 5000
## 6 1005 Rohit Clothing UPI 4 1200
## 7 1006 Simran Electronics Card 2 18000
## GrossAmount Discount NetAmount DeliveryDays OrderType
## 1 25000 2000 23000 3 High Value
## 2 4500 300 4200 5 Regular
## 3 8000 500 7500 4 Regular
## 4 40000 1500 38500 2 High Value
## 5 5000 500 4500 4 Regular
## 6 4800 NA 4800 6 Regular
## 7 36000 2000 34000 3 High Value
card_orders <- subset(Ecommerce, PaymentMethod == "Card")
card_orders
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 2 1002 Riya Clothing Card 3 1500
## 4 1003 Karan Electronics Card 2 20000
## 7 1006 Simran Electronics Card 2 18000
## GrossAmount Discount NetAmount DeliveryDays OrderType
## 2 4500 300 4200 5 Regular
## 4 40000 1500 38500 2 High Value
## 7 36000 2000 34000 3 High Value
any(is.na(Ecommerce))
## [1] TRUE
colSums(is.na(Ecommerce))
## Order_ID CustomerName Category PaymentMethod Quantity
## 0 0 0 0 0
## PricePerUnit GrossAmount Discount NetAmount DeliveryDays
## 0 0 1 0 0
## OrderType
## 0
Ecommerce$Discount[is.na(Ecommerce$Discount)] <-
mean(Ecommerce$Discount, na.rm = TRUE)
asc_orders <- Ecommerce[order(Ecommerce$NetAmount), ]
asc_orders
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 2 1002 Riya Clothing Card 3 1500
## 5 1004 Neha Home Cash 1 5000
## 6 1005 Rohit Clothing UPI 4 1200
## 3 1007 Arjun Home Cash 2 4000
## 1 1001 Aman Electronics UPI 1 25000
## 7 1006 Simran Electronics Card 2 18000
## 4 1003 Karan Electronics Card 2 20000
## GrossAmount Discount NetAmount DeliveryDays OrderType
## 2 4500 300.000 4200 5 Regular
## 5 5000 500.000 4500 4 Regular
## 6 4800 1133.333 4800 6 Regular
## 3 8000 500.000 7500 4 Regular
## 1 25000 2000.000 23000 3 High Value
## 7 36000 2000.000 34000 3 High Value
## 4 40000 1500.000 38500 2 High Value
desc_orders <- Ecommerce[order(-Ecommerce$NetAmount), ]
desc_orders
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 4 1003 Karan Electronics Card 2 20000
## 7 1006 Simran Electronics Card 2 18000
## 1 1001 Aman Electronics UPI 1 25000
## 3 1007 Arjun Home Cash 2 4000
## 6 1005 Rohit Clothing UPI 4 1200
## 5 1004 Neha Home Cash 1 5000
## 2 1002 Riya Clothing Card 3 1500
## GrossAmount Discount NetAmount DeliveryDays OrderType
## 4 40000 1500.000 38500 2 High Value
## 7 36000 2000.000 34000 3 High Value
## 1 25000 2000.000 23000 3 High Value
## 3 8000 500.000 7500 4 Regular
## 6 4800 1133.333 4800 6 Regular
## 5 5000 500.000 4500 4 Regular
## 2 4500 300.000 4200 5 Regular
category_revenue <- aggregate(NetAmount ~ Category, data = Ecommerce, sum)
category_revenue
## Category NetAmount
## 1 Clothing 9000
## 2 Electronics 95500
## 3 Home 12000
avg_delivery_payment <- aggregate(DeliveryDays ~ PaymentMethod, data = Ecommerce, mean)
avg_delivery_payment
## PaymentMethod DeliveryDays
## 1 Card 3.333333
## 2 Cash 4.000000
## 3 UPI 4.500000
top_3_orders <- Ecommerce[order(-Ecommerce$NetAmount), ][1:3, ]
top_3_orders
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 4 1003 Karan Electronics Card 2 20000
## 7 1006 Simran Electronics Card 2 18000
## 1 1001 Aman Electronics UPI 1 25000
## GrossAmount Discount NetAmount DeliveryDays OrderType
## 4 40000 1500 38500 2 High Value
## 7 36000 2000 34000 3 High Value
## 1 25000 2000 23000 3 High Value
total_revenue <- sum(Ecommerce$NetAmount)
category_revenue$RevenuePercent <-
(category_revenue$NetAmount / total_revenue) * 100
category_revenue
## Category NetAmount RevenuePercent
## 1 Clothing 9000 7.725322
## 2 Electronics 95500 81.974249
## 3 Home 12000 10.300429
high_discount_orders <- subset(
Ecommerce,
Discount > 0.10 * GrossAmount
)
high_discount_orders
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 6 1005 Rohit Clothing UPI 4 1200
## GrossAmount Discount NetAmount DeliveryDays OrderType
## 6 4800 1133.333 4800 6 Regular
Ecommerce$GrossAmount[2] <-
Ecommerce$Quantity[2] * Ecommerce$PricePerUnit[2]
Ecommerce
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 1 1001 Aman Electronics UPI 1 25000
## 2 1002 Riya Clothing Card 3 1500
## 3 1007 Arjun Home Cash 2 4000
## 4 1003 Karan Electronics Card 2 20000
## 5 1004 Neha Home Cash 1 5000
## 6 1005 Rohit Clothing UPI 4 1200
## 7 1006 Simran Electronics Card 2 18000
## GrossAmount Discount NetAmount DeliveryDays OrderType
## 1 25000 2000.000 23000 3 High Value
## 2 4500 300.000 4200 5 Regular
## 3 8000 500.000 7500 4 Regular
## 4 40000 1500.000 38500 2 High Value
## 5 5000 500.000 4500 4 Regular
## 6 4800 1133.333 4800 6 Regular
## 7 36000 2000.000 34000 3 High Value
Ecommerce$GrossAmount[Ecommerce$CustomerName == "Neha"] <-
Ecommerce$Quantity[Ecommerce$CustomerName == "Neha"] *
Ecommerce$PricePerUnit[Ecommerce$CustomerName == "Neha"]
Ecommerce
## Order_ID CustomerName Category PaymentMethod Quantity PricePerUnit
## 1 1001 Aman Electronics UPI 1 25000
## 2 1002 Riya Clothing Card 3 1500
## 3 1007 Arjun Home Cash 2 4000
## 4 1003 Karan Electronics Card 2 20000
## 5 1004 Neha Home Cash 1 5000
## 6 1005 Rohit Clothing UPI 4 1200
## 7 1006 Simran Electronics Card 2 18000
## GrossAmount Discount NetAmount DeliveryDays OrderType
## 1 25000 2000.000 23000 3 High Value
## 2 4500 300.000 4200 5 Regular
## 3 8000 500.000 7500 4 Regular
## 4 40000 1500.000 38500 2 High Value
## 5 5000 500.000 4500 4 Regular
## 6 4800 1133.333 4800 6 Regular
## 7 36000 2000.000 34000 3 High Value