How to merge two tables without adding and deleting columns

user_12148688 0
Viewed 37

I have two tables with information that I would like to join with a testcase as key. I could first join them, then rename the columns and then re-order the dataframe, but is there a more elegant way?


df1 <- data.frame(
  testcase = c('testcase1', 'testcase2', 'testcase3', 'testcase4', 'testcase5'), 
  passed = c('2', '0', '2', '0', '0'), 
  failed = c('0', '2', '2', '0', '2'))

df2 <- data.frame(
  id = c(1:10), testid = c('testcase3', 'testcase1', 'testcase3',   'testcase2', 'testcase5', 'testcase1', 
  'testcase3', 'testcase5', 'testcase2', 'testcase3'), total_passed = rep("", 10), total_failed= rep("", 10), testid = c(510:519), total_items = rep("", 10))

My solution would be the following, but could it be done with less steps?

df3 <- merge(df2, df1, by.x='testid', by.y='testcase')
df3$total_passed <- df3$total_failed <- NULL
df3$total_items <- 10
df3 <- select(df3, id, testid, total_passed = passed,  total_failed= failed, testid, total_items)
rename dataframe r merge
2 Answers

Maybe you can take help of dplyr library :

library(dplyr)

df2 %>%
  inner_join(df1, by = c('testid' = 'testcase')) %>%
  transmute(id, testid, total_passed = passed, total_failed = failed, 
            total_items = 10)

#   id    testid total_passed total_failed total_items
#1   1 testcase3            2            2          10
#2   2 testcase1            2            0          10
#3   3 testcase3            2            2          10
#4   4 testcase2            0            2          10
#5   5 testcase5            0            2          10
#6   6 testcase1            2            0          10
#7   7 testcase3            2            2          10
#8   8 testcase5            0            2          10
#9   9 testcase2            0            2          10
#10 10 testcase3            2            2          10
user_39629140

We can use a join in data.table

library(data.table)
setDT(df2)[df1, c('total_passed', 'total_failed', 'total_items')
         := .(passed, failed, 10), on = .(testid = testcase)]
user_37322710
Related