Find and remove duplicates
Jump to navigation
Jump to search
Find duplicate data
EXCEL
Finding duplicate rows that differ in one column
- one column data: How to count duplicate values in a column in Excel? Using COUNTIF(range, criteria) [Last visited: 2015-08-25] or using Pivot Tables(樞紐分析表) to find the occurrence of value >= 2
Finding duplicate rows that differ in multiple columns
- two or multiple columns data: (approach 1) How to compare data in two columns to find duplicates in Excel [Last visited: 2015-06-16] It may costs too much time (larger than one hour) if the number of records exceeds 1,000,000 (approach 2) Using CONCAT function to concatenate two or multiple columns data. And then use COUNTIF(range, criteria).
Cygwin
- uniq command on Cygwin of Win or Linux : uniq -d <file.txt> > <duplicated_items.txt>[1]
MySQL
Finding duplicate rows that differ in one column
Find the duplicated data for one column[2]
-- Generate test data. CREATE TABLE `table_name` ( `id` int(11) NOT NULL, `content` varchar(5) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `table_name` (`id`, `content`) VALUES (1, 'apple'), (2, 'lemon'), (3, 'apple'); ALTER TABLE `table_name` ADD PRIMARY KEY (`id`); -- Find duplicated data SELECT `content`, COUNT(*) count FROM `table_name` GROUP BY `content` HAVING count > 1; SELECT tmp.* FROM ( SELECT `content`, count(*) count FROM `table_name` GROUP BY `content` ) tmp WHERE tmp.count >1;
Finding duplicate rows that differ in multiple columns
Using CONCAT for multiple columns ex: column_1, column_2
SELECT count(*) count, CONCAT( `column_1`, `column_2` ) 'key' FROM `table_name` GROUP BY CONCAT( `column_1`, `column_2` ) HAVING count > 1;
or
SELECT tmp.key FROM ( SELECT count(*) count, CONCAT( `column_1`, `column_2` ) 'key' FROM `table_name` GROUP BY CONCAT( `column_1`, `column_2` ) ) tmp WHERE tmp.count >=2
other cases
For counting purpose: find the count of repeated id (type: int) between table_a and table_b
SELECT count(DISTINCT(id)) FROM table_a WHERE id IN ( SELECT DISTINCT(id) FROM table_b )
Google Spreadsheet
- Remove duplicates in Google Sheets 30 days free [Last visited: 2019-02-26]
- Power Tools for Google Spreadsheet [Last visited: 2019-02-26]
- Menu: Data -> Remove duplicates
Deduplicate
- EXCEL: Data Tools -> Remove Duplicates: Filter for unique values or remove duplicate values [Last visited: 2015-10-20]
- MySQL: select deduplicated records
- MySQL DISTINCT - Eliminate Duplicate Rows in a Result Set. Using GROUP_CONCAT to handle the multiple columns[3]
- SQL UNIQUE Constraint "Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table." Quoted from w3schools webpage.
- "UNION removes duplicates, whereas UNION ALL does not." source: sql - What is the difference between UNION and UNION ALL? - Stack Overflow
- MySQL: delete duplicated records
- GNU Coreutils: sort invocation OS: Linux , cygwin of Win . More details on Merge multiple plain text files.
- To remove duplicate lines:
- sort -us -o <output_unique.file> <input.file> in a large text file (GB)[4]
- cat <input.file> | grep <pattern> | sort | uniq Processes text line by line and prints the unique lines which match a specified pattern. Equal to these steps: (1) cat <input.file> | grep <pattern> > <tmp.file> (2) sort <tmp.file> | uniq
- Ignore first n line(s) & remove duplicate lines[5][6][7]
- (1) ignore first one line: (head -n 1 <file> && tail -n +2 <file> | sort -us) > newfile
- (2) ignore first two lines: (head -n 2 <file> && tail -n +3 <file> | sort -us) > newfile
- To remove duplicate lines:
- Google spreadsheet add-on: Remove Duplicates for Google Sheets help
Counting number of duplicate occurrence
MySQL: find the number of duplicate occurrence between list_a & list_b which using the same primary key: column name id
- SELECT count(DISTINCT(`id`)) FROM `list_a` WHERE `id` IN (SELECT DISTINCT(`id`) FROM `list_b`) ;
Excel:
- microsoft excel - How to count number of repeat occurrences - Super User long number issue: microsoft excel - Countif incorrectly matches long number - Super User
Other
- symbol e.g. data-mining or data_mining
References
- ↑ shell - How to print only the duplicate values from a text file? - Unix & Linux Stack Exchange
- ↑ Finding duplicate values in MySQL - Stack Overflow
- ↑ sql - MySQL SELECT DISTINCT multiple columns - Stack Overflow
- ↑ linux - How to remove duplicate lines in a large multi-GB textfile? - Unix & Linux Stack Exchange
- ↑ sorting - Is there a way to ignore header lines in a UNIX sort? - Stack Overflow
- ↑ 命令執行的判斷依據: ; , &&, ||
- ↑ Linux tail command help and examples