Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You don't need a join operation to get your desired results. You said: <em>'Objective: Select all rows from DT for which Region=="US", Year >= 5 &amp; Year&lt;=8, Cat="A"'</em></p> <pre><code>DT[Region=="US" &amp; Year&gt;=5 &amp; Year &lt;= 8 &amp; Categ=="A"] Year value Region Categ [1,] 5 -0.18631697 US A [2,] 6 1.40059083 US A [3,] 7 0.01848557 US A </code></pre> <hr> <p>But to answer your question about column classes. I managed to get this code to work, which essentially mirrors your code above:</p> <pre><code>&gt; setkey(DT, Region, Categ, Year) &gt; df &lt;- data.frame(Region=c("US", "EU"), Categ=c("A", "B")) &gt; dt2 &lt;- data.table(data.frame(df[1, ], Year=5:8)) Warning message: In data.frame(df[1, ], Year = 5:8) : row names were found from a short variable and have been discarded &gt; dt1[dt2] Region Categ Year value [1,] US A 5 -0.5565422 [2,] US A 6 -0.1805841 [3,] US A 7 1.4474403 [4,] US A 8 NA </code></pre> <hr> <p>The same, with column classes of <code>character</code>:</p> <pre><code>df$Region &lt;- as.character(df$Region) df$Categ &lt;- as.character(df$Categ) #Rebuild int_DT dt2 &lt;- J(cbind(df[1, ], Year=5:8)) Warning message: In data.frame(..., check.names = FALSE) : row names were found from a short variable and have been discarded setkey(dt2, Region) dt1[dt2] Region Year value Categ Categ.1 Year.1 US 1 1.20152558 A A 5 US 2 1.89391079 A A 5 US 3 -1.76022634 A A 5 US 4 0.92454680 A A 5 US 5 -0.55654217 A A 5 ... snip ... US 9 0.67936243 B A 8 US 10 -0.09355764 B A 8 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POWhat are the restrictions for the column classes in data.table?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMatt Dowle
    1. USAndrie
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhat are the restrictions for the column classes in data.table?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks @Andrie. Regarding your first point: The whole process should be very flexible: for instance, I do not know the number of columns I want to use for a match in advance. Therefore, your simple approach does not work for me, as far as I can see. Regarding your thoughts on the issue: 1) does not seem to be a problem; matching the column names of `DT` and `int_DT` does not help. 2) Setting the key (`setkey(int_DT, Region, Categ, Year)`) does work. This, however, let me wonder why it works for factors without setting the key?
      singulars
      1. PO
      1. USChristoph_J
    2. CO@Christoph_J Your approach should work just fine for flexible analysis of data. Good luck.
      singulars
      1. PO
      1. USAndrie
    3. CO@Andrie Column names don't matter, that's not issue. And only x needs to have a key in joins, i can be unkeyed no problem at all. The join columns of i just need to be factor, not character.
      singulars
      1. PO
      1. USMatt Dowle
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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