Melting on selected columns with na.rm = T, removes the data row completely although remaining columns have valid data
<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>