R 代码按 % 拆分值，然后为每个新的 % 值分配一个新类别

Question

我还没有找到一种方法来做到这一点，所以询问是否有更简单的方法来做到这一点。这是数据集的示例:

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

我需要获取每个收入行，按不同的百分比分配收入，然后将每个百分比分配给一个新代码。

举个例子，

对于 Apple，收入应该分配给:

• 0.45 进入“A”。
• 0.50 转到“B”。
• 0.05 转到“C”。

因此，数据集中的第一个值 Revenue =223,220.00 应按如下方式分配:

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

这会增加行数。

我试过使用这段代码，但想知道是否有更简单的方法来做到这一点？

    #
# 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'])

Answer 1

这是一个非常简单的 dplyr 解决方案:

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`)

这给了我们:

  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   

这是一个更长但相似的 base R 解决方案:

# 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"

这给了我们:

       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