title: Melting on selected columns with na.rm = T, removes the data row completely although remaining columns have valid data
body: <p>I'm a beginner in R and huge fan of data.table features in R
  This is perhaps my first Question on R in the forum and apologies for any ill-formatting of the code/text.</p>
  
  <p>I happen to work with a data set of large number of categorical variables
  I tried to create a mock data set to explain the problem:</p>
  
  <pre><code>structure(list(ID = 1234:1237, 
  AgeC = c("25-30", "31+", "25-30", "20-24"), 
  GenderC = c("female", "male", "female", "female"), 
  doyoubuyappleseveryday = c(NA, 1L, NA, NA), 
  doyoubuyapplesonceinaweek = c(1L, NA, NA, NA), 
  doyoubuyapplesonceinamonth = c(NA, NA, NA, NA), 
  doyoubuypearseveryday = c(NA, NA, NA, NA), 
  doyoubuypearsonceinaweek = c(NA, NA, NA, 1L), 
  doyoubuypearssonceinamonth = c(NA, NA, NA, NA), 
  doyoueatappleseveryday = c(NA, NA, NA, NA), 
  doyoueatapplesonceinaweek = c(1L, NA, 1L, NA), 
  doyoueatapplesonceinamonth = c(NA, NA, NA, NA), 
  doyoueatpearseveryday = c(NA, 1L, NA, NA), 
  doyoueatpearsonceinaweek = c(NA, NA, NA, 1L), 
  doyoueatpearsonceinamonth = c(1L, NA, NA, NA)), 
  .Names = c("ID", "AgeC", "GenderC", "doyoubuyappleseveryday", 
      "doyoubuyapplesonceinaweek", "doyoubuyapplesonceinamonth", "doyoubuypearseveryday", 
      "doyoubuypearsonceinaweek", "doyoubuypearssonceinamonth", "doyoueatappleseveryday", 
      "doyoueatapplesonceinaweek", "doyoueatapplesonceinamonth", "doyoueatpearseveryday", 
      "doyoueatpearsonceinaweek", "doyoueatpearsonceinamonth"), 
      row.names = c(NA, -4L), class = c("data.table", "data.frame"), .internal.selfref = &lt;pointer: 0x0000000000380788&gt;)
  </code></pre>
  
  <p>I was trying to reshape the data set with selected columns.. using melt()/dcast()</p>
  
  <p>The idea is, with a series of melt()/dcast()  operations , I can reduce the variables to 6., by creating appropriate new variables that can take the categorical values.</p>
  
  <p>In the first set, I chose to eliminate the columns  which have "buy" as a match string , using melt()/dcast functions as follows :</p>
  
  <pre><code>id.cols.buy.m &lt;-names(repro.dt)[-grep("buy",names(repro.dt))]
  repro.buy.m&lt;-data.table(melt(repro.dt, id.vars = id.cols.buy.m, 
                          measure.vars = grep("buy",names(repro.dt),value=T ),
                          na.rm=T,variable.name = "buy.fruit", 
                          value.name = "buy.freq"))
  </code></pre>
  
  <p>The resulting data set, drops the complete row with ID = 1236 since "buy" variables for this ID are all 'NA'. This is a loss of data to me, since in the next sequence of reshape operations with "eat" as match string, I plan to use the cleaned version of data set from previous reshape/dcast.</p>
  
  <p>Something as follows :</p>
  
  <pre><code>#create a new variable "fruit"
  repro.buy.m$fruit = apply(repro.buy.m, 1, function(u){
  bool = sapply(fruit[,1], function(x) grepl(x,u[['buy.fruit']]))
  if(any(bool)) fruit[bool] else NA
  })
  
  
  #create a fruit purchase freq column 
  
  repro.buy.m$freq.pur= apply(repro.buy.m, 1, function(u){
  bool = sapply(freq.levels[,1], function(x) grepl(x, u[['buy.fruit']]))
  if(!is.na(u[['buy.freq']])) freq.levels[bool] else NA
  })
  
  #now drop the redundant colums that have "buy"  as match string--
  
  id.cols.buy.m &lt;- colnames(repro.buy.m)[-grep("buy",names(repro.buy.m))]
  f &lt;- as.formula(paste(paste(id.cols.buy.m, collapse = " + "), "~ buy.fruit"))
  
  repro.buy.c&lt;-data.table(dcast(data = repro.buy.m, f, 
                                value.var   ="buy.freq",
                                function(x)   length(unique(x))))
  
  repro.buy.c&lt;-repro.buy.c[, which(grepl("buy", colnames(repro.buy.c))):=NULL]
  </code></pre>
  
  <p>with the above step, all my "buy" variables -- 6 of them are now reduced to 2
  <strong><em>However, I lost one ID who does not buy any fruits but eats fruits</em></strong>
  If I do not use na.rm = T, the row is retained , however it shoots up another problem of duplicating rows when I reshape for variables with "eat" as a match string</p>
  
  <p>My final goal is to merge the categorical variables with a single column called "fruit" and the associated columns "freq.pur" and "freq.eat" with NA's as applicable,and grouped by ID.</p>
  
  <p>something like this (this is for a single ID) :</p>
  
  <pre><code>structure(list(fruit = c("apples", "pears"), freq.pur = c("onceinaweek",NA),
  freq.et = c("onceinaweek", "onceinamonth")), 
  .Names = c("fruit", "freq.pur", "freq.et"), 
  row.names = c(NA, -2L), class = c("data.table", "data.frame"),
  .internal.selfref = &lt;pointer: 0x0000000000380788&gt;, sorted = "fruit")
  </code></pre>
  
  <p>With this example data set , I would request any help on</p>
  
  <ol>
  <li>How to remove NA values for the selected columns during partial and
  sequential reshaping of data -- so as to avoid duplicate rows after reshape</li>
  <li>How to merge columns of different lengths within the same data table joined by a single categorical variable with unique values (in this case "fruit") , grouped by ID -- again to remove meaning-less rows</li>
  </ol>
  
  <p>Best Regards</p>
  
  <p>Edit : 04/21 : 10:15IST (mock data table for expected output)</p>
  
  <pre><code>structure(list(ID = c(1234L, 1234L, 1235L, 1235L, 1236L, 1237L), 
  AgeC = c("25-30", "25-30", "31+", "31+", "25-30", "20-24"), 
  GenderC = c("female", "female", "male", "male", "female", 
  "female"), freq.pur = c(NA, "onceinaweek", "everyday", "everyday", 
  NA, "onceinaweek"), freq.et = c("onceinamonth", "onceinaweek", 
  "everyday", "everyday", "onceinaweek", "onceinaweek"), fruit = c("pears", 
  "apples", "apples", "pears", "apples", "pears")), 
  .Names = c("ID","AgeC", "GenderC", "freq.pur", "freq.et", "fruit"),
  row.names = c(NA,-6L), class = c("data.table", "data.frame"),
  </code></pre>
  
  <p>.internal.selfref = )</p>
  
  <p>EDIT : 04/25 </p>
  
  <p>I was able to work on a solution for this , with the help of a few "unique" calls on the intermediate structures and few auxiliary variables, and I was able to test all the use cases. My final data table is as below :</p>
  
  <pre><code>dput(repro.buy.eat.final)
  
  structure(list(ID = c(1210L, 1210L, 1234L, 1234L, 1234L, 1235L, 
  1235L, 1237L, 1237L, 1238L, 1238L, 1239L), 
  AgeC = c("25-30", "25-30", "25-30", "25-30", "25-30", "31+", 
  "31+", "20-24", "20-24", "25-30", "25-30", "25-30"), 
  GenderC = c("female", "female", "female", "female", "female", "male", 
  "male", "female", "female", "male", "male", "male"), 
  fruit = c("apples", "apples", "apples", "pears", "apples", "apples",
  "pears", "pears", "pears", "apples", "pears", "pears"), 
  freq.et = c("everyday", NA, NA, "onceinamonth", "onceinaweek", NA,
  "everyday", "onceinaweek", NA, "onceinamonth", NA, NA), 
  freq.pur = c(NA, "onceinaweek", "onceinaweek", NA, NA, "everyday", NA, 
  NA,  "onceinaweek", NA, "everyday", "onceinaweek")), 
  row.names = c(NA, -12L), 
  class = c("data.table", "data.frame"),
  .internal.selfref = &lt;pointer: 0x0000000002830788&gt;, 
  .Names = c("ID", "AgeC", "GenderC", "fruit", "freq.et", "freq.pur"))
  </code></pre>
  
  <p>In the resulting set, I want to merge similar fruits with similar "buy" and "eat" frequencies. I found somewhat relevant answer here :
  <a href="https://stackoverflow.com/questions/11364992/r-merge-of-rows-in-same-data-table-concatenating-certain-columns">R: Merge of rows in same data table, concatenating certain columns</a>,
  however, I did not know how to apply a condition to match the frequencies, although I can group by ID and fruits. 
  I'm looking for some help here.
  I could share the code snippets here.. if this post had not become so long. I'm yet to get a hold on the formatting style to append the data table in the tabular view.</p>
  
  <p>The test cases, I used :</p>
  
  <ol>
  <li>did not buy fruits , but ate fruits-   </li>
  <li>did not eat fruits, but bought fruits</li>
  <li>buy and eat same fruits same freq   </li>
  <li>buy and eat same fruits different freq</li>
  <li>buy and eat diff fruits same freq  </li>
  <li>buy and eat diff fruits diff freq</li>
  </ol>
  
  <p>Best Regards</p>