Find and remove duplicates: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
(One intermediate revision by the same user not shown)
Line 22: Line 22:


[http://www.gnu.org/software/coreutils/manual/html_node/sort-invocation.html GNU Coreutils: sort invocation] OS: {{Linux}}, cygwin of {{Win}}.
[http://www.gnu.org/software/coreutils/manual/html_node/sort-invocation.html GNU Coreutils: sort invocation] OS: {{Linux}}, cygwin of {{Win}}.
Data preparation:
* [[Alternative_Linux_commands#Merge_multiple_plain_text_files | Merge multiple plain text files]]


case 1: To remove duplicate lines of entire paragraph
case 1: To remove duplicate lines of entire paragraph
Line 30: Line 33:
* (1) ignore first one line: {{kbd | key=<nowiki>(head -n 1 <file> && tail -n +2 <file> | sort -us) > newfile</nowiki>}}   
* (1) ignore first one line: {{kbd | key=<nowiki>(head -n 1 <file> && tail -n +2 <file> | sort -us) > newfile</nowiki>}}   
* (2) ignore first two lines: {{kbd | key=<nowiki>(head -n 2 <file> && tail -n +3 <file> | sort -us) > newfile</nowiki>}}
* (2) ignore first two lines: {{kbd | key=<nowiki>(head -n 2 <file> && tail -n +3 <file> | sort -us) > newfile</nowiki>}}
Relate pages:
* [[Alternative_Linux_commands#Merge_multiple_plain_text_files | Merge multiple plain text files]]


== Find and remove duplicates in MySQL ==
== Find and remove duplicates in MySQL ==
Line 130: Line 130:
== Other issues ==
== Other issues ==
* symbol e.g. data-mining or data_mining
* symbol e.g. data-mining or data_mining
== Related articles ==
* [https://ithelp.ithome.com.tw/questions/10210232 【PYTHON】請問如何避免內容重複抓取 - iT 邦幫忙::一起幫忙解決難題,拯救 IT 人的一天]


== References ==
== References ==

Revision as of 15:51, 25 September 2022

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

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

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