Suppose we have a dataframe with ~10M rows with ~9M duplicate records. What is the most time efficient way of selecting the unique records from this dataframe?
Some sort of sampling algorithm?
Computer Science Stack Exchange is a question and answer site for students, researchers and practitioners of computer science. It only takes a minute to sign up.Sign up to join this community
A reasonable approach would be to build a hashtable that stores all unique records. Scan through the records one at a time, adding each to the hashtable if it is not already present; if it already is in the hashtable, do nothing, and if it is not already in the hashtable, add it to the hashtable and output it.
Another approach would be to sort the records (which will make all duplicates adjacent), then remove duplicates, then return to the original order if needed. This has the benefit that it can scale to even larger databases: if the entire dataset cannot be stored in memory, it is possible to use an external sort and still be efficient. In contrast, a hash table will work great if everything fits in memory, but will be very slow if the database does not fit in memory.