CODEWITHSUNDEEP
rdataframerow
Dragg
Dragg

Reputation: 83

Join rows of a dataframe depending of data of other dataframe

I have a dataframe like this

> print(data)

   req_ID effort satisfaction
1     r01      1           62
2     r02      4           55
3     r03      2           29
4     r04      3           41
5     r05      4           58
6     r06      7           63
7     r07     10           24
8     r08      2           56
9     r09      1           54
10    r10      3           49
11    r11      2           45
12    r12      5           49
13    r13      8           35
14    r14      2           50
15    r15      1           56
16    r16      4           27
17    r17     10           39
18    r18      4           35
19    r19      8           46
20    r20      4           20

And other like this 

> print(comb)

  head_req_ID tail_req_ID
1         r03         r12
2         r11         r13

I have to join the element that been in the comb dataframe, for example, we have r03, r12 in the first row, I know how can be in one line, will be like this

data[3,] <- c("r03+r12", data$effort[3] + data$effort[12],data$satisfaction[3] + data$satisfaction[12])

And I erase 12th row of data

data <- data[-12,]

I don't know how can it be automatic for all rows of comb dataframe.

Upvotes: 0

Views: 69

Answers (2)

Eric Watt
Eric Watt

Reputation: 3230

Using data.table:

dat <- fread("   req_ID effort satisfaction
                 r01      1           62
                 r02      4           55
                 r03      2           29
                 r04      3           41
                 r05      4           58
                 r06      7           63
                 r07     10           24
                 r08      2           56
                 r09      1           54
                 r10      3           49
                 r11      2           45
                 r12      5           49
                 r13      8           35
                 r14      2           50
                 r15      1           56
                 r16      4           27
                 r17     10           39
                 r18      4           35
                 r19      8           46
                 r20      4           20")

dat_comb <- fread("head_req_ID tail_req_ID
                   r03         r12
                   r11         r13")

weird_func <- function(x) {
  message(x)
  dat[req_ID == x[1]] <- list(paste0(c(x[1], x[2]), collapse = "+"), 
                              dat[req_ID == x[1], effort] + dat[req_ID == x[2], effort],
                              dat[req_ID == x[1], satisfaction] + dat[req_ID == x[2], satisfaction])
  dat <<- dat[!req_ID == x[2]]
}

apply(dat_comb, 1, weird_func)

Results in:

#     req_ID effort satisfaction
#  1:     r01      1           62
#  2:     r02      4           55
#  3: r03+r12      7           78
#  4:     r04      3           41
#  5:     r05      4           58
#  6:     r06      7           63
#  7:     r07     10           24
#  8:     r08      2           56
#  9:     r09      1           54
# 10:     r10      3           49
# 11: r11+r13     10           80
# 12:     r14      2           50
# 13:     r15      1           56
# 14:     r16      4           27
# 15:     r17     10           39
# 16:     r18      4           35
# 17:     r19      8           46
# 18:     r20      4           20

Upvotes: 1

www
www

Reputation: 39154

Here is a solution using dplyr. data2 is the final output.

library(dplyr)

data2 <- data %>%
  mutate(req_ID = ifelse(req_ID %in% comb[1, ], paste(comb[1, ], collapse = "+"),
                         ifelse(req_ID %in% comb[2, ], paste(comb[2, ], collapse = "+"),
                                req_ID))) %>%
  group_by(req_ID) %>%
  summarise(effort = sum(effort), satisfaction = sum(satisfaction))

Data Preparation

data <- read.table(text = "   req_ID effort satisfaction
1     r01      1           62
                   2     r02      4           55
                   3     r03      2           29
                   4     r04      3           41
                   5     r05      4           58
                   6     r06      7           63
                   7     r07     10           24
                   8     r08      2           56
                   9     r09      1           54
                   10    r10      3           49
                   11    r11      2           45
                   12    r12      5           49
                   13    r13      8           35
                   14    r14      2           50
                   15    r15      1           56
                   16    r16      4           27
                   17    r17     10           39
                   18    r18      4           35
                   19    r19      8           46
                   20    r20      4           20",
                   header = TRUE, stringsAsFactors = FALSE)

comb <- read.table(text = "head_req_ID tail_req_ID
1         r03         r12
                   2         r11         r13",
                   header = TRUE, stringsAsFactors = FALSE)

Upvotes: 1

Related Questions