R- rbind逻辑

Question

我有这个数据：

source_data <- data.frame(
    "date" = c("2018-01-01", "2018-01-01", "2018-02-01", "2018-02-01"), 
    "nr" = c(0, 1, 0, 1),
    "marketing_fees" = c(500, 600, 800, 900),
    "services_paid" = c(40, 50, 10, 30),
    stringsAsFactors = F)

结果应该是这样的

result <- data.frame(
  "date" = c("2018-01-01", "2018-01-01", "2018-01-01", "2018-01-01", "2018-02-01", "2018-02-01", "2018-02-01", "2018-02-01"), 
  "nr" = c(0, 0, 1, 1, 0, 0, 1, 1),
  "income" = c(500, 40, 600, 50, 800, 10, 900, 30),
  "source" = c("marketing", "services", "marketing", "services", "marketing", "services", "marketing", "services"),
  stringsAsFactors = F)

我唯一能做的就是

result <- rbind(
  source_data %>% 
    filter(date == "2018-01-01") %>% 
    select(date, nr, income = marketing_fees) %>% 
    mutate(source = "marketing"),

  source_data %>% 
    filter(date == "2018-01-01") %>% 
    select(date, nr, income = services_paid) %>% 
    mutate(source = "services"),

  source_data %>% 
    filter(date == "2018-02-01") %>% 
    select(date, nr, income = marketing_fees) %>% 
    mutate(source = "marketing"),

  source_data %>% 
    filter(date == "2018-02-01") %>% 
    select(date, nr, income = services_paid) %>% 
    mutate(source = "services")
)

上面的代码不仅是丑陋的，有很多重复的部分，我不能再这样使用它了，我的数据文件有大约50列和很多数据。如果没有这么多重复的代码，你如何才能获得结果数据？

Answer 1

我们可以使用gather将“wide”重塑为“long”，然后separate列名只返回前缀部分

library(tidyverse)
source_data %>% 
    gather(source, income, marketing_fees:services_paid) %>% 
    separate(source, into = c('source', 'extra')) %>%
    select(-extra) %>% 
    arrange(date, nr)
#        date nr    source income
#1 2018-01-01  0 marketing    500
#2 2018-01-01  0  services     40
#3 2018-01-01  1 marketing    600
#4 2018-01-01  1  services     50
#5 2018-02-01  0 marketing    800
#6 2018-02-01  0  services     10
#7 2018-02-01  1 marketing    900
#8 2018-02-01  1  services     30

Answer 2

library(data.table)
library(magrittr)
result2 <- melt(
  setDT(source_data), 
  id.vars = c("date", "nr"), 
  value.name = "income", 
  variable.name = "source"
)[, source := sub("_.*", "", source)][order(date, nr)]°

         date nr    source income
1: 2018-01-01  0 marketing    500
2: 2018-01-01  0  services     40
3: 2018-01-01  1 marketing    600
4: 2018-01-01  1  services     50
5: 2018-02-01  0 marketing    800
6: 2018-02-01  0  services     10
7: 2018-02-01  1 marketing    900
8: 2018-02-01  1  services     30