<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><p>Hi Ben,</p>
<p>If the “Date” column (which seems to be just month names) is already in order - meaning you just want to pick the last item for each group, then this is fairly straightforward:</p>
<p>I assume <code>Date</code> is of type “character”.</p>
<h3 id="method1:">Method 1:</h3>
<pre><code>DT[, .SD[.N], by=Group]
# Group Value Date
# 1: 1 yyy July
# 2: 2 qqqq August
</code></pre>
<h3 id="method2:">Method 2:</h3>
<p>In this case, <code>.SD</code> is not optimised for speed yet. So, if this is slow, then you can overcome it by using <code>.I</code> in place of <code>.SD</code> as follows:</p>
<pre><code>DT[DT[, .I[.N], by=Group]$V1]
# Group Value Date
# 1: 1 yyy July
# 2: 2 qqqq August
</code></pre>
<p>Instead of subsetting entire data per group (.SD), we get the row number (.I) in DT for each group (in column V1) and then just subset those rows.</p>
<hr>
<p>If the Date column is not necessarily sorted for each group, then we create an extra column:</p>
<h3 id="method3:">Method 3:</h3>
<pre><code>DT[, idx := chmatch(Date, month.name)]
setkey(DT, Group, idx) # sort by group, idx
DT[DT[, .I[.N], by=Group]$V1]
# Group Value Date idx
# 1: 1 yyy July 7
# 2: 2 qqqq August 8
</code></pre>
<p>Or if you use v1.9.3, you can use <code>setorder</code> instead of <code>setkey</code> which allows for ordering in ascending and descending order:</p>
<h3 id="method4:">Method 4:</h3>
<pre><code>DT[, idx := chmatch(Date, month.name)]
setorder(DT, Group, -idx) # sort by group, and descending order on idx
</code></pre>
<p>Now we’ll need to pick the first element instead of the .Nth (last) element per group.</p>
<pre><code>DT[DT[, .I[1L], by=Group]$V1]
# Group Value Date idx
# 1: 1 yyy July 7
# 2: 2 qqqq August 8
</code></pre>
<p>And alternatively, if you don’t wish to add the extra column, you can use <code>order(.)</code> as follows:</p>
<h3 id="method5:">Method 5:</h3>
<pre><code>DT[order(Group, -chmatch(Date, month.name))][, .SD[1L], by=Group]
</code></pre>
<p>If you want to use <code>.I</code> here, you’ll have to save the first part onto a variable, which essentially means you’ll use up twice the memory of your data set.. So, I’d prefer this least. But just to show all possible ways I could think of.</p>
<p>HTH</p>
<p><style>body{font-family:Helvetica,Arial;font-size:13px}</style><style>body {
font-family: "Helvetica Neue", Helvetica, Arial, sans-serif;
padding:1em;
margin:auto;
background:#fefefe;
}
h1, h2, h3, h4, h5, h6 {
font-weight: bold;
}
h1 {
color: #000000;
font-size: 28pt;
}
h2 {
border-bottom: 1px solid #CCCCCC;
color: #000000;
font-size: 24px;
}
h3 {
font-size: 18px;
}
h4 {
font-size: 16px;
}
h5 {
font-size: 14px;
}
h6 {
color: #777777;
background-color: inherit;
font-size: 14px;
}
hr {
height: 0.2em;
border: 0;
color: #CCCCCC;
background-color: #CCCCCC;
}
p, blockquote, ul, ol, dl, li, table, pre {
margin: 15px 0;
}
a, a:visited {
color: #4183C4;
background-color: inherit;
text-decoration: none;
}
#message {
border-radius: 6px;
border: 1px solid #ccc;
display:block;
width:100%;
height:60px;
margin:6px 0px;
}
button, #ws {
font-size: 12 pt;
padding: 4px 6px;
border-radius: 5px;
border: 1px solid #bbb;
background-color: #eee;
}
code, pre, #ws, #message {
font-family: Monaco;
font-size: 10pt;
border-radius: 3px;
background-color: #F8F8F8;
color: inherit;
}
code {
border: 1px solid #EAEAEA;
margin: 0 2px;
padding: 0 5px;
}
pre {
border: 1px solid #CCCCCC;
overflow: auto;
padding: 4px 8px;
}
pre > code {
border: 0;
margin: 0;
padding: 0;
}
#ws { background-color: #f8f8f8; }
table {
border-collapse: collapse;
font-family: Helvetica, arial, freesans, clean, sans-serif;
color: rgb(51, 51, 51);
font-size: 15px; line-height: 25px;
padding: 0; }
table tr {
border-top: 1px solid #cccccc;
background-color: white;
margin: 0;
padding: 0; }
table tr:nth-child(2n) {
background-color: #f8f8f8; }
table tr th {
font-weight: bold;
border: 1px solid #cccccc;
margin: 0;
padding: 6px 13px; }
table tr td {
border: 1px solid #cccccc;
margin: 0;
padding: 6px 13px; }
table tr th :first-child, table tr td :first-child {
margin-top: 0; }
table tr th :last-child, table tr td :last-child {
margin-bottom: 0; }
.send { color:#77bb77; }
.server { color:#7799bb; }
.error { color:#AA0000; }</style></p><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div> <div id="bloop_sign_1405724367497488128" class="bloop_sign"><div style="font-family:helvetica,arial;font-size:13px">Arun</div></div> <div style="color:black"><br>From: <span style="color:black">Arunkumar Srinivasan</span> <a href="mailto:aragorn168b@gmail.com">aragorn168b@gmail.com</a><br>Reply: <span style="color:black">Arunkumar Srinivasan</span> <a href="mailto:aragorn168b@gmail.com">aragorn168b@gmail.com</a><br>Date: <span style="color:black">July 19, 2014 at 12:51:04 AM</span><br>To: <span style="color:black">bgoldstein</span> <a href="mailto:ben.goldstein@gmail.com">ben.goldstein@gmail.com</a><br>Cc: <span style="color:black">datatable-help@lists.r-forge.r-project.org</span> <a href="mailto:datatable-help@lists.r-forge.r-project.org">datatable-help@lists.r-forge.r-project.org</a><br>Subject: <span style="color:black"> Re: [datatable-help] Subsetting By Row Function <br></span></div><br> <blockquote type="cite" class="clean_bq"><span><div><div></div><div>
<title></title>
<div dir="ltr">Hi Ben,
<div><br></div>
<div>If the "Date" column (which seems to be just month names) is
already in order - meaning you just want to pick the last item for
each group, then this is fairly straightforward:</div>
<div><br></div>
<div>I assume `Date` is of type "character".</div>
<div><br></div>
<div>Method 1:</div>
<div>DT[, .SD[.N], by=Group]</div>
<div>
<div># Group Value Date</div>
<div># 1: 1 yyy July</div>
<div># 2: 2 qqqq August</div>
</div>
<div><br></div>
<div>Method 2:</div>
<div>In this case, `.SD` is not optimised for speed yet. So, if
this is slow, then you can overcome it by using `.I` in place of
`.SD` as follows:</div>
<div><br></div>
<div>DT[DT[, .I[.N], by=Group]$V1]</div>
<div>
<div>
<div># Group Value Date</div>
<div># 1: 1 yyy July</div>
<div># 2: 2 qqqq August</div>
</div>
</div>
<div><br></div>
<div>Instead of subsetting entire data per group (.SD), we get the
row number (.I) in DT for each group (in column V1) and then just
subset those rows.</div>
<div><br></div>
<div>---</div>
<div><br></div>
<div>If </div>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">On Sat, Jul 19, 2014 at 12:40 AM,
bgoldstein <span dir="ltr"><<a href="mailto:ben.goldstein@gmail.com" target="_blank">ben.goldstein@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I
am having trouble defining (and therefore searching) for this
problem. I<br>
have data like this:<br>
<br>
Group Value Date<br>
1 xxx June<br>
1 yyy July<br>
2 zzzz May<br>
2 qqqq August<br>
etc.<br>
<br>
<br>
I want to subset the 'Value' of each 'Group' by the latest 'Date'.
So my<br>
output should be:<br>
<br>
Group Value Date<br>
1 yyy July<br>
2 qqqq August<br>
etc.<br>
<br>
The doBy package has a firstobs() function that works but is quite
slow.<br>
<br>
What would be a data.table way to do this?<br>
<br>
Thank you,<br>
<br>
Ben<br>
<br>
<br>
<br>
--<br>
View this message in context: <a href="http://r.789695.n4.nabble.com/Subsetting-By-Row-Function-tp4694221.html" target="_blank">http://r.789695.n4.nabble.com/Subsetting-By-Row-Function-tp4694221.html</a><br>
Sent from the datatable-help mailing list archive at
Nabble.com.<br>
_______________________________________________<br>
datatable-help mailing list<br>
<a href="mailto:datatable-help@lists.r-forge.r-project.org">datatable-help@lists.r-forge.r-project.org</a><br>
<a href="https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help" target="_blank">https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help</a><br>
</blockquote>
</div>
<br></div>
</div></div></span></blockquote><p></p></body></html>