StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's a <code>data.table</code> based solution. I'll be interested to learn what (if any) improvements can be made to it.</p> <pre><code># Your code library(data.table) source <- data.table(data.frame(key = c("A","B","C","D","A","B","A","B","B","B", "C"), value = c(1,1,1,1,4,4,23,23,26,26,30))) </code></pre> <p>That strange <code>data.table(data.frame(...</code> is because <code>data.table()</code> has an argument called <code>key</code>, too. That's one way to create a <code>data.table</code> with a column called <code>"key"</code>. Capitalising to avoid the argument name conflict illustrates the more standard syntax :</p> <pre><code>source <- data.table(Key = c("A","B","C","D","A","B","A","B","B","B","C"), Value = c(1,1,1,1,4,4,23,23,26,26,30)) </code></pre> <p>Next to avoid needing <code>as.integer()</code> later, we'll change the type of the <code>Value</code> column from <code>numeric</code> to <code>integer</code> now. Remember than <code>1</code> is <code>numeric</code> in R, it is <code>1L</code> that is <code>integer</code>. It is usually better for efficiency to store <code>integer</code> data as <code>integer</code>, than <code>integer</code> as <code>numeric</code>. The next line is easier than typing lots of <code>L</code>s above.</p> <pre><code>source[,Value:=as.integer(Value)] # change type from `numeric` to `integer` </code></pre> <p>Now proceed</p> <pre><code>distance <- 22L setkey(source, Key, Value) # Heart of the solution (following a few explanatory comments): # "J()" : shorthand for 'data.table()' # ".N" : returns the number of rows that matched a line (see ?data.table) # "[[3]]" : as with simple data.frames, extracts the vector in column 3 source[,count:=source[J(Key,Value+distance),.N][[3]]] source key value count [1,] A 1 1 [2,] A 4 0 [3,] A 23 0 [4,] B 1 1 [5,] B 4 2 [6,] B 23 0 [7,] B 26 0 [8,] B 26 0 [9,] C 1 0 [10,] C 30 0 [11,] D 1 0 </code></pre> <p>Note that <code>:=</code> changed <code>source</code> by reference directly, so that's it. But <code>setkey()</code> also changed the order of the original data. If retaining the original order is required, then:</p> <pre><code>source <- data.table(Key = c("A","B","C","D","A","B","A","B","B","B","C"), Value = c(1,1,1,1,4,4,23,23,26,26,30)) source[,Value:=as.integer(Value)] source[,count:=setkey(copy(source))[source[,list(Key,Value+distance)],.N][[3]]] Key Value count [1,] A 1 1 [2,] B 1 1 [3,] C 1 0 [4,] D 1 0 [5,] A 4 0 [6,] B 4 2 [7,] A 23 0 [8,] B 23 0 [9,] B 26 0 [10,] B 26 0 [11,] C 30 0 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload