<html><head></head><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px"><div id="yui_3_16_0_ym19_1_1482116771652_3059"><span>Hi</span></div><div id="yui_3_16_0_ym19_1_1482116771652_3085"><span><br></span></div><div dir="ltr" id="yui_3_16_0_ym19_1_1482116771652_3210"><span id="yui_3_16_0_ym19_1_1482116771652_3211">I have searched the last couple of days for a way to do this but have not found a solution. With real data, I have used tidyr to do the task but:</span></div><div dir="ltr" id="yui_3_16_0_ym19_1_1482116771652_3213"><span id="yui_3_16_0_ym19_1_1482116771652_3212">1. It has used all available memory (12gb on older desktop)</span></div><div dir="ltr" id="yui_3_16_0_ym19_1_1482116771652_3215"><span id="yui_3_16_0_ym19_1_1482116771652_3214">2. Future tables will be even larger so would need to be split<br></span></div><div dir="ltr" id="yui_3_16_0_ym19_1_1482116771652_3217"><span id="yui_3_16_0_ym19_1_1482116771652_3216">3. It is is s l ow, perhaps due to lack of free memory.</span></div><div dir="ltr" id="yui_3_16_0_ym19_1_1482116771652_3218"><span><br></span></div><div dir="ltr" id="yui_3_16_0_ym19_1_1482116771652_3220"><span id="yui_3_16_0_ym19_1_1482116771652_3219">The data is provided in a format such that a variable "name" (and there are several like this) actually contains the variable name and indices, i.e. var_09 is the ninth level of that variable. The data analysis needs that level as a separate variable. Code and toy data set are below.</span></div><div dir="ltr" id="yui_3_16_0_ym19_1_1482116771652_3274"><span id="yui_3_16_0_ym19_1_1482116771652_3219"><br></span></div><div dir="ltr" id="yui_3_16_0_ym19_1_1482116771652_3300"><span id="yui_3_16_0_ym19_1_1482116771652_3219"># column split test<br id="yui_3_16_0_ym19_1_1482116771652_3338">library(data.table)<br id="yui_3_16_0_ym19_1_1482116771652_3339">library(tidyr)<br id="yui_3_16_0_ym19_1_1482116771652_3340"># data table for melt and columns split<br id="yui_3_16_0_ym19_1_1482116771652_3341">dt1 <- data.table(a_1 = 1:10, b_2 = 20:29,folks = c("art","brian","ed",<br id="yui_3_16_0_ym19_1_1482116771652_3342"> "rich","dennis","frank", "derrick","paul","fred","numnuts"),<br id="yui_3_16_0_ym19_1_1482116771652_3343"> a_2 = 2:11, b_1 = 21:30)<br id="yui_3_16_0_ym19_1_1482116771652_3344">melt(dt1, id = "folks") # so far so good<br id="yui_3_16_0_ym19_1_1482116771652_3345">dt1[,c("a") := tstrsplit(c(a_1),"_",fixed = TRUE)][,c("a") := tstrsplit(c(a_2),<br id="yui_3_16_0_ym19_1_1482116771652_3346"> "_",fixed = TRUE)][]<br id="yui_3_16_0_ym19_1_1482116771652_3347"># That is not producing what I want<br id="yui_3_16_0_ym19_1_1482116771652_3348"><br id="yui_3_16_0_ym19_1_1482116771652_3349"># tidyr gives what I want<br id="yui_3_16_0_ym19_1_1482116771652_3350">df <- data.frame(a_1 = 1:10, b_2 = 20:29,folks = c("art","brian","ed",<br id="yui_3_16_0_ym19_1_1482116771652_3351"> "rich","dennis","frank", "derrick","paul","fred","numnuts"),<br id="yui_3_16_0_ym19_1_1482116771652_3352"> a_2 = 2:11, b_1 = 21:30)<br id="yui_3_16_0_ym19_1_1482116771652_3353">df %>% gather(value, nums, -folks) %>%<br id="yui_3_16_0_ym19_1_1482116771652_3354"> separate(value, c("varTYpe","varIndex"))</span></div><div id="yui_3_16_0_ym19_1_1482116771652_3078"> </div><div class="signature" id="yui_3_16_0_ym19_1_1482116771652_3079">Carl Sutton<br><div></div></div></div></body></html>