R code split value by % then assign each new % value a new category

user_2924467 0
Viewed 81

I haven't found a way to do this, so asking if there's a simpler way to do this. Here's a sample of the data set:

    Revenue      Product    New Code
1  223,220.00     Apple
2  386,640.40     Apple            
3  19,891.95      Apple

I need to take each revenue row, allocate the revenue by different percentages and then assign each percentage to a new code.

As an example,

For Apple, the revenue should be allocated by:

  • 0.45 goes to ‘A’.
  • 0.50 goes to ‘B’.
  • 0.05 goes to ‘C’.

So, the first value from the data set, Revenue =223,220.00 and should be allocated as below:

    Revenue      Product    New Code
1  100,449        Apple       A
2  111,610        Apple       B         
3  11,161         Apple       C

This would increase the # of rows.

I tried using this code, but wondering if there's an easier way to do this?

    #
# libraries
#
library(dplyr)
#
# load data
#
my_data <- read.csv('sales_data_to_reclassify.csv', stringsAsFactors = FALSE)
#
# get total category revenue
#
Apple_revenue <- sum(my_data[substr(my_data$product, 1, 4) == 'Apple', 'Revenue'])
Apple_rows <- which(substr(my_data$product, 1, 4) == 'Apple')
#
# set the splits
#
splits <- list(A = 0.45,
               B = 0.50,
               C = 0.05)
#
# apply the splits at row level
#
for (i in Apple_rows) {
  #
  # revenue for this row in the original data
  #
  row_revenue = my_data[i, 'Revenue']
  for (label in names(splits)) {
    #
    # grab the row
    #
    new_row <- my_data[i, ]
    #
    # calculate the revenue for this split
    # and update the new row
    #    
    new_row$Revenue <- row_revenue * splits[[label]]
    #
    # assign the label
    #
    new_row$New.Code <- label
    #
    # build a temporary data frame to hold the new rows
    #
    if (label == names(splits)[1]) {
      new_rows <- new_row
    } else {
      new_rows <- rbind(new_rows, new_row)
    }
    rownames(my_data) <- NULL
    Apple_rows <- which(substr(my_data$product, 1, 4) == 'Apple')
  }
  #
  # drop the original row
  #
  my_data <- my_data[-i, ]
  #
  # add in the new rows
  #
  my_data <- rbind(my_data, new_rows)
}
#
# test revenue
#
Apple_new_revenue <-  sum(my_data[substr(my_data$product, 1, 4) == 'Apple', 'Revenue'])
r
2 Answers

Here's a pretty simple dplyr solution: 

df %>% 
  filter(Product %in% c("Apple", "Microsoft", "Samsung") %>%
  mutate(A = Revenue * 0.45,
         B = Revenue * 0.50,
         C = Revenue * 0.05) %>% 
  select(-Revenue) %>% 
  pivot_longer(-Product, values_to = "Revenue") %>% 
  rename(`New Code` = name) %>% 
  select(Revenue, Product, `New Code`)

This gives us: 

  Revenue Product `New Code`
    <dbl> <chr>   <chr>     
1 100449  Apple   A         
2 111610  Apple   B         
3  11161  Apple   C         
4 173988. Apple   A         
5 193320. Apple   B         
6  19332. Apple   C         
7   8951. Apple   A         
8   9946. Apple   B         
9    995. Apple   C

Here's a longer, but similar, base R solution:

# Remove commas from Revenue and convert to numeric
df$Revenue <- as.numeric(gsub(",", "", df$Revenue))

df <- subset(df, df$Product %in% c("Apple", "Microsoft", "Samsung"))

# Calculate percentage distributions
df$A <- df$Revenue * 0.45
df$B <- df$Revenue * 0.50
df$C <- df$Revenue * 0.05

# Reshape data to long
df <- reshape(df, 
        varying = c("A","B","C"),
        v.names = "Revenue",
        direction = "long")

# Sort by ID and recode values
df <- df[order(df$id),]
df$time[df$time == 1] <- "A"
df$time[df$time == 2] <- "B"
df$time[df$time == 3] <- "C"

# Drop ID column
df <- subset(df, select = -c(id))

# Rename 'time' to 'New Code'
names(df)[3] <- "New Code"

Which gives us: 

       Revenue Product New Code
1: 100449.0000   Apple        A
2: 111610.0000   Apple        B
3:  11161.0000   Apple        C
4: 173988.1800   Apple        A
5: 193320.2000   Apple        B
6:  19332.0200   Apple        C
7:   8951.3775   Apple        A
8:   9945.9750   Apple        B
9:    994.5975   Apple        C
user_129679380

Using merge:

# example data - updated to include more products.
my_data  <- read.table(text = "Revenue Product
223220.0 Apple
386640.4 Apple
19891.95 Pear", header = TRUE)

# define shares
splits <- data.frame(Product = rep(c("Apple", "Pear"), c(3, 3)),
                     NewCode = c("A", "B", "C", "X", "Y", "Z"),
                     Share = c(0.45, 0.50, 0.05, 0.2, 0.3, 0.5))

# merge and get revenue shares
res <- merge(my_data, splits, by = "Product")
res$RevenueShare <- res$Revenue * res$Share

res
#   Product   Revenue NewCode Share RevenueShare
# 1   Apple 223220.00       A  0.45   100449.000
# 2   Apple 223220.00       B  0.50   111610.000
# 3   Apple 223220.00       C  0.05    11161.000
# 4   Apple 386640.40       A  0.45   173988.180
# 5   Apple 386640.40       B  0.50   193320.200
# 6   Apple 386640.40       C  0.05    19332.020
# 7    Pear  19891.95       X  0.20     3978.390
# 8    Pear  19891.95       Y  0.30     5967.585
# 9    Pear  19891.95       Z  0.50     9945.975
user_6800680
Related