R - rbind logically

Question

I have this dataframe:

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 should look like this

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)

Only way how can I do is this

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

Code above is not just ugly with lot of repeated parts, I can't use it anymore this way sice my dataframe has about 50 columns and lot of dataes. How would you achieve result dataframe without so much repeated codes?

This is reshaping and some elementary text processing as far as I can see. will post an answer as a proof. — s_baldur, Jun 26 '19 at 13:47
Note that this is similar to the logic of reopening mentioned [here](https://stackoverflow.com/questions/56662593/retrieve-values-of-the-data-frame-by-matching-id-and-column-name/56662713#56662713) I don't see any difference. Rules should be similar to everybody and not for somebody else — akrun, Jun 26 '19 at 13:49

score 1 · Accepted Answer · answered Jun 26 '19 at 13:28

We can use gather to reshape from 'wide' to 'long' and then separate the column name to return only the prefix part

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

score 0 · Answer 2 · answered Jun 26 '19 at 13:47

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

R - rbind logically

2 Answers2