If you’re just doing small batches that are relatively well-formed, then the compare.linkage() or compare.dedup() functions in the RecordLinkage package should be a great starting point. But if you have big batches, then you might have to do some more tinkering. I use the functions jarowinkler(), levenshteinSim(), and soundex() in RecordLinkage to write my own … Read more
This should do the trick: def unique_rows(a): a = np.ascontiguousarray(a) unique_a = np.unique(a.view([(”, a.dtype)]*a.shape[1])) return unique_a.view(a.dtype).reshape((unique_a.shape[0], a.shape[1])) Example: >>> a = np.array([[1, 1], [2, 3], [1, 1], [5, 4], [2, 3]]) >>> unique_rows(a) array([[1, 1], [2, 3], [5, 4]])
First. Sort in the order putting the less desired items last within id groups aa <- a[order(a$id, -abs(a$value) ), ] #sort by id and reverse of abs(value) Then: Remove items after the first within id groups aa[ !duplicated(aa$id), ] # take the first row within each id id value 2 1 2 4 2 -4 … Read more
To get the result without subquery, you have to resort to advanced window function trickery: SELECT sum(count(*)) OVER () AS tickets_count , sum(min(a.revenue)) OVER () AS atendees_revenue FROM tickets t JOIN attendees a ON a.id = t.attendee_id GROUP BY t.attendee_id LIMIT 1; sqlfiddle How does it work? The key to understanding this is the sequence … Read more
It works if you use duplicated twice: df[!(duplicated(df[c(“c”,”d”)]) | duplicated(df[c(“c”,”d”)], fromLast = TRUE)), ] a b c d 1 1 2 A 1001 4 4 8 C 1003 7 7 13 E 1005 8 8 14 E 1006
It is very simple. I tried in SQL Server 2008 DELETE SUB FROM (SELECT ROW_NUMBER() OVER (PARTITION BY EmpId, EmpName, EmpSSN ORDER BY EmpId) cnt FROM Employee) SUB WHERE SUB.cnt > 1
Try the steps described in this article: Removing duplicates from a PostgreSQL database. It describes a situation when you have to deal with huge amount of data which isn’t possible to group by. A simple solution would be this: DELETE FROM foo WHERE id NOT IN (SELECT min(id) –or max(id) FROM foo GROUP BY hash) … Read more
You need the %in% operator. So, df1[!(df1$name %in% df2$name),] should give you what you want. df1$name %in% df2$name tests whether the values in df1$name are in df2$name The ! operator reverses the result.
Some of these approaches seem a little complicated, and I generally do this as: Given table table, want to unique it on (field1, field2) keeping the row with the max field3: DELETE FROM table USING table alias WHERE table.field1 = alias.field1 AND table.field2 = alias.field2 AND table.max_field < alias.max_field For example, I have a table, … Read more
The easiest way to solve the problem is to group the elements based on their value, and then pick a representative of the group if there are more than one element in the group. In LINQ, this translates to: var query = lst.GroupBy(x => x) .Where(g => g.Count() > 1) .Select(y => y.Key) .ToList(); If … Read more