r - Merge with multiple columns by conditions -


i want merge data condition. browsed many websites did not find want.
have 2 data here: 

# dt1 cola1   cola2     colb    colc    cold           area ta43    ti44      s2230   amy     2014-08-08     usa ta63    ti64      t1205   andy    2014-01-01     canada ta28    ti100     l1288   peter   2014-01-08     eu ta28    ti100     l2231   roger   2014-01-08     eu ta92    na        a2206   jean    2014-01-12     china ta14    na        e2240   freda   2014-01-05     japan ta69    ti50      n1029   tina    2014-01-05     mexico  # dt2 cola     colb    colc    cold           type ti64     t1205   andy    2014-01-01     ti100    l1288   peter   2014-01-08     ti100    l2231   roger   2014-01-08     ta92     a2206   jean    2014-01-12      ta14     e2240   freda   2014-01-05     r ta69     n1029   tina    2014-01-05

what want is: 

cola     colb    colc    cold           type   area ti64     t1205   andy    2014-01-01          canada ti100    l1288   peter   2014-01-08          eu ti100    l2231   roger   2014-01-08          eu ta92     a2206   jean    2014-01-12          china ta14     e2240   freda   2014-01-05     r      japan ta69     n1029   tina    2014-01-05          mexico

i explain here:
want mapping dt1 dt2 cola, colb, colc , cold.
if column type in dt2 a , r, merge cola in dt2 cola1 in dt1.
if column type in dt2 i, merge cola in dt2 cola2 in dt1.

any idea data.table way?

this gets desired output dplyr. may use inner_join or right_join depending on you're trying achieve:

library(dplyr) library(tidyr) dt2 %>% mutate(merge_col = ifelse(type == "i","cola2","cola1")) %>%   left_join(dt1 %>% gather(merge_col,cola,cola1,cola2))  # joining, = c("cola", "colb", "colc", "merge_col") # cola  colb  colc       cold type merge_col   area # 1  ti64 t1205  andy 2014-01-01        cola2 canada # 2 ti100 l1288 peter 2014-01-08        cola2     eu # 3 ti100 l2231 roger 2014-01-08        cola2     eu # 4  ta92 a2206  jean 2014-01-12        cola1  china # 5  ta14 e2240 freda 2014-01-05    r     cola1  japan # 6  ta69 n1029  tina 2014-01-05        cola1 mexico

data.table

with data.table can try this, it's exact translation:

merge(   dt2[,merge_col := ifelse(dt2$type == "i","cola2","cola1")],   melt(dt1,id = c("colb","colc","cold","area"),measure=c("cola1","cola2"),"merge_col","cola"),   all.x = true )

adjust parameters all.x , all.y depending on type of join want


Comments

Post a Comment

Popular posts from this blog

Sort a complex associative array in PHP -

this question has answer here: php sort array subarray value 5 answers i have associative array in php like: $newarray1 = array ( [producta] => array ( [link] => http://sanjosespartan.com/blog/products/agile-java-development/ [visitcount] => 4 ) [productd] => array ( [link] => http://sanjosespartan.com/blog/products/intro-to-go/ [visitcount] => 1 ) [productg] => array ( [link] => http://sanjosespartan.com/blog/products/node-js-for-developers/ [visitcount] => 1 ) [productb] => array ( [link] => http://sanjosespartan.com/blog/products/beginning-mysql/ [visitcount] => 1 ) [productc] => array ( [link] => http://sanjosespartan.com/blog/products/computer-networks/ [visitcount] => 1 ) ...
Read more

vb.net - How to ignore if a cell is empty nothing -

how use roscolor() ignore value (like empty cell) ? color row if value upper 5 when there nothing in cell, want ignore roscolor() apply, how? public sub roscolor() integer = 0 quotedatagridview1.rows.count() - 1 step +1 dim val integer val = quotedatagridview1.rows(i).cells(3).value if val = vbempty quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.white elseif val < 5 quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.red elseif val > 5 , val < 10 quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.lightyellow end if next end sub you can check empty value following public sub roscolor() integer = 0 quotedatagridview1.rows.count() - 1 step +1 dim val = quotedatagridview1.rows(i).cells(3).value if isdbnull(val) or val nothing quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.white ...
Read more

recursion - Can every recursive algorithm be improved with dynamic programming? -

i first year undergraduate csc student looking competitive programming. recursion involves defining , solving sub problems. understand, top down dynamic programming (dp) involves memoizing solutions sub problems reduce time complexity of algorithm. can top down dp used improve efficiency of every recursive algorithm overlapping sub problems? dp fail work , how can identify this? the short answer is: yes. however, there constraints. obvious 1 recursive calls must overlap. i.e. during execution of algorithm, recursive function must called multiple times same parameters. lets truncate recursion tree memoization. can use memoization reduce number of calls. however, reduction of calls comes price. need store results somewhere. next obvious constraint need have enough memory. comes not-so obvious constraint. memory access requires time. first need find result stored , maybe copy location. in cases, might faster let recursion calculate result instead of loading somewher...
Read more