Find and remove duplicates

From LemonWiki共筆
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Find and remove duplicates in Excel

Find duplicates in Excel

Finding duplicate rows that differ in one column

Finding duplicate rows that differ in multiple columns

Counging

Remove duplicates in Excel


Find and remove duplicates in Cygwin/BASH

Finding duplicate values in Cygwin/BASH

  • uniq command on Cygwin of Win Os windows.png or Linux Os linux.png : uniq -d <file.txt> > <duplicated_items.txt>[1]


Remove duplicate values in Cygwin/BASH

GNU Coreutils: sort invocation OS: Linux Os linux.png , cygwin of Win Os windows.png .

Data preparation:

case 1: To remove duplicate lines of entire paragraph

  • sort -us -o <output_unique.file> <input.file> in a large text file (GB)[2]
  • 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

Case 2: Ignore first n line(s) & remove duplicate lines[3][4][5]

  • (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

Find and remove duplicates in MySQL

Find duplicates in MySQL

Finding duplicate value that differ in one column

Finding duplicate value that differ in one column[6]

-- 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;

Another approach: Add content_hash column which its value is MD5(content_column)[7]

-- To find the duplicated records with same content
SELECT count(*) as count, content_hash
FROM `YOUR_TABLE_NAME`
GROUP BY content_hash
HAVING count >= 2

Finding duplicate rows that differ in multiple columns

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

Select deduplicated/duplicated records


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
)

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`) ;

Remove duplicates in MySQL


Find and remove duplicates in Google Spreadsheet

Initial contents of cell A2 which its separator is comma or whitespace. The second parameter of SPLIT function is ", " (a comma followed by a space):

apple, banana, apple

To remove duplicates from Cell A2, use the following formula: ((learn more about the UNIQUE function from Google Help[9] or Microsoft support[10])

If cell A2's content is separated by a comma or whitespace:
= JOIN(", ",UNIQUE(TRANSPOSE(SPLIT(A2,", "))))

If cell A2's content is separated solely by a comma:
= JOIN(", ", UNIQUE(TRANSPOSE(ARRAYFORMULA(TRIM(SPLIT(A2, ","))))))

Alternatively, activate the `by_column=true` parameter in the second argument of the UNIQUE function:

= JOIN(", ",UNIQUE(SPLIT(A2,", "), 1)))

Cell A2's contents after removing duplicates:

apple, banana

Find and remove duplicates in PHP

Find duplicates in PHP

Remove duplicates in PHP

Find and remove duplicates in JavaScript

Remove duplicates in JavaScript


Other issues

  • symbol e.g. data-mining or data_mining

Related articles

References