Data cleaning: Difference between revisions

Jump to navigation Jump to search

Data cleaning (edit)

Revision as of 18:57, 25 November 2025

6,282 bytes added ,  25 November 2025
→‎Verify if value is number in MySQL
 
(126 intermediate revisions by the same user not shown)
Line 1: Line 1:


== check if field value was not fulfilled  ==
== Check list ==
=== by purpose ===
 
* Row count: The number of data entries is a fundamental item for data verification and is easy to observe and check. For instance, one can compare the number of entries displayed on a webpage to the number of entries after exporting to a CSV file.
* Duplicate data
 
== Check if field value was not fulfilled  ==
=== By purpose ===
<table border="1" style="width: 100%">
<table border="1" style="width: 100%">
   <tr style="background-color: #555555; color: #ffffff;">
   <tr style="background-color: #555555; color: #ffffff;">
     <td style="background-color: #777; color: #ffffff;">purpose</td>
     <td style="background-color: #777; color: #ffffff;">Purpose</td>
     <td style="background-color: #777; color: #ffffff;">method (MySQL query syntax)</td>
     <td style="background-color: #777; color: #ffffff; width: 350px;">Method (MySQL query syntax)</td>
     <td>value1: <br />fulfilled value what I want</td>  
     <td style="vertical-align: top;">Value1: <br />Fulfilled value what I want</td>  
     <td>value2: <br />fulfilled value NOT I want</td>
     <td style="vertical-align: top;">Value2: <br />Fulfilled value NOT I want</td>
     <td>value3: <br />0 value</td>
     <td style="vertical-align: top;">Value3: <br />0</td>
     <td>value4: <br />NULL value</td>
     <td style="vertical-align: top;">Value4: <br />NULL value</td>
     <td>value5: <br />empty or spaces value</td>
     <td style="vertical-align: top;">Value5: <br />Empty or white-spaces characters</td>
   </tr>
   </tr>
   <tr>
   <tr>
     <td>find not fulfilled or empty values <br />(not contains 0)</td>
     <td>values were not fulfilled or empty <br />(not contains 0)</td>
     <td>{{kbd | key=<nowiki>WHERE column_name IS NULL OR LENGTH(TRIM( column_name )) = 0</nowiki>}}</td>  
     <td>{{kbd | key=WHERE column_name IS NULL <br /> OR LENGTH(TRIM( column_name )) = 0}}</td>  
     <td></td>
     <td></td>
     <td></td>
     <td></td>
Line 22: Line 27:
   </tr>
   </tr>
   <tr>
   <tr>
     <td>find not fulfilled or empty values <br />(contains 0)</td>
     <td>values were not fulfilled or empty<br />(contains 0)</td>
     <td></td>  
     <td></td>  
     <td></td>
     <td></td>
Line 31: Line 36:
   </tr>
   </tr>
   <tr>
   <tr>
     <td>find fulfilled and non-empty values <br />(not contains 0)</td>
     <td>values were fulfilled and non-empty<br />(not contains 0)</td>
     <td></td>  
     <td></td>  
     <td>V</td>
     <td>V</td>
Line 40: Line 45:
   </tr>
   </tr>
   <tr>
   <tr>
     <td>find fulfilled and non-empty values <br />(contains 0)</td>
     <td>values were fulfilled and non-empty<br />(contains 0)</td>
     <td>{{kbd | key=<nowiki>WHERE LENGTH(TRIM( column_name )) > 0</nowiki>}}</td>  
     <td>{{kbd | key=<nowiki>WHERE LENGTH(TRIM( column_name )) > 0</nowiki>}}</td>  
     <td>V</td>
     <td>V</td>
Line 49: Line 54:
   </tr>
   </tr>
   <tr>
   <tr>
     <td>find (1) not fulfilled or empty values (2) values NOT I want <br />(not contains 0)</td>
     <td>values (1) were not fulfilled or empty values (2) NOT I want <br />(not contains 0)</td>
     <td>{{kbd | key=<nowiki>WHERE column_name IS NULL OR LENGTH(TRIM( column_name )) = 0 OR column_name LIKE 'values NOT I want'</nowiki>}}</td>  
     <td>{{kbd | key=WHERE column_name IS NULL <br /> OR LENGTH(TRIM( column_name )) = 0 <br /> OR column_name LIKE 'values NOT I want'}}</td>  
     <td></td>
     <td></td>
     <td>V</td>
     <td>V</td>
Line 58: Line 63:
   </tr>
   </tr>
   <tr>
   <tr>
     <td>find (1) not fulfilled or empty values (2) values NOT I want <br />(contains 0)</td>
     <td>values (1) were not fulfilled or empty values (2) NOT I want <br />(contains 0)</td>
     <td></td>  
     <td></td>  
     <td></td>
     <td></td>
Line 68: Line 73:
</table>
</table>


=== by datatype ===
=== By datatype ===
==== VARCHAR and NOT allows NULL value ====
Using NULLIF() function<ref>[https://www.w3schools.com/sql/func_mysql_nullif.asp MySQL NULLIF() Function]</ref>
 
SQL query:
<pre>
SELECT NULLIF(TRIM(`my_column`), "")
</pre>
 
Example result:
 
<pre>
SELECT NULLIF(null, "");
-- return NULL
 
SELECT NULLIF("", "");
-- return NULL
 
SELECT NULLIF(TRIM("  "), "");
-- return NULL
 
SELECT NULLIF(TRIM("not empty string  "), "");
-- return "not empty string"
 
</pre>
 
 
==== VARCHAR and allows NULL value ====
==== VARCHAR and allows NULL value ====
<table border="1" style="width: 100%">
<table border="1" style="width: 100%">
Line 185: Line 216:
** If the cell value is exactly {{kbd | key = NULL}} not {{kbd | key = #NULL!}}, You may use  {{kbd | key = <nowiki>COUNTIF(value, "NULL")</nowiki>}} or  {{kbd | key = <nowiki>EXACT(value, "NULL")</nowiki>}}
** If the cell value is exactly {{kbd | key = NULL}} not {{kbd | key = #NULL!}}, You may use  {{kbd | key = <nowiki>COUNTIF(value, "NULL")</nowiki>}} or  {{kbd | key = <nowiki>EXACT(value, "NULL")</nowiki>}}
* MySQL SQL syntax: {{kbd | key = SELECT * FROM table WHERE column IS NULL;}}<ref>[http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html MySQL :: MySQL 5.0 Reference Manual :: 3.3.4.6 Working with NULL Values]</ref>  
* MySQL SQL syntax: {{kbd | key = SELECT * FROM table WHERE column IS NULL;}}<ref>[http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html MySQL :: MySQL 5.0 Reference Manual :: 3.3.4.6 Working with NULL Values]</ref>  
* R: is.null(): [https://stat.ethz.ch/R-manual/R-devel/library/base/html/NULL.html R: The Null Object]
* Excel<ref>[https://errerrors.blogspot.com/2018/09/check-if-cell-value-is-null-in-excel.html 如何判斷 Excel 儲存格的欄位值是 NULL]</ref>


Find whether a variable is NOT NULL
Find whether a variable is NOT NULL
Line 232: Line 265:
* MySQL: {{kbd | key =<nowiki>SELECT * FROM table_name WHERE column_name != '' AND column_name IS NOT NULL;</nowiki>}}
* MySQL: {{kbd | key =<nowiki>SELECT * FROM table_name WHERE column_name != '' AND column_name IS NOT NULL;</nowiki>}}


== Verify the format of field value ==
== Data Validation ==
related page: [[Regular expression]]
Validate the format of field value. Related page: [[Regular expression]]
 
=== Verify the strings are in valid email format ===
Rule: Email contains @ symbol


=== Email contains @ symbol ===
* EXCEL: {{kbd | key =<nowiki>=IF(ISERR(FIND("@", A2, 1)), FALSE, TRUE)</nowiki>}} only check the field if contains @ symbol or not
* EXCEL: {{kbd | key =<nowiki>=IF(ISERR(FIND("@", A2, 1)), FALSE, TRUE)</nowiki>}} only check the field if contains @ symbol or not
** result: (1) normal condition: return TRUE; (2) exceptional condition: return '''FALSE''' if @ symbol was not found  
** result: (1) normal condition: return TRUE; (2) exceptional condition: return '''FALSE''' if @ symbol was not found  
Line 243: Line 278:
* PHP: [http://www.w3schools.com/php/filter_validate_email.asp PHP FILTER_VALIDATE_EMAIL Filter]
* PHP: [http://www.w3schools.com/php/filter_validate_email.asp PHP FILTER_VALIDATE_EMAIL Filter]
** "Returns the filtered data, or '''FALSE''' if the filter fails." quoted from [http://php.net/manual/en/function.filter-var.php PHP.net]
** "Returns the filtered data, or '''FALSE''' if the filter fails." quoted from [http://php.net/manual/en/function.filter-var.php PHP.net]
=== Verify the strings are in valid url format ===
Rule: Begin with http or https
* Google spreadsheet {{kbd | key =<nowiki>=REGEXMATCH(A1, "^http(s?)")</nowiki>}}


=== Number precision in Excel ===
=== Number precision in Excel ===
Line 256: Line 296:
* If the data was imported from Excel, you should notice the 15 digit precision issue.
* If the data was imported from Excel, you should notice the 15 digit precision issue.


=== Numeric only ===
=== Verify the column values are numeric ===
* PHP: [http://php.net/manual/en/function.is-numeric.php is_numeric]
 
* MySQL:  
Possible values
** find numeric values {{code | code = SELECT * FROM `my_table` WHERE `my_column` REGEXP '^[0-9]+$'}}<ref>[http://stackoverflow.com/questions/14343767/mysql-regexp-with-and-numbers-only regex - Mysql REGEXP with . and numbers only - Stack Overflow]</ref>
 
** find NOT numeric values {{code | code = SELECT * FROM `my_table` WHERE `my_column` NOT REGEXP '^[0-9]+$'}}
<pre>
* Excel: [http://www.techonthenet.com/excel/formulas/isnumber.php ISNUMBER Function]
test data:
3.141592654
1.36184E+14
123,456.789
20740199601
346183773390240
="5"
</pre>
 
==== Verify if value is number in MySQL ====
MySQL:  
 
* Check if a value is integer e.g. 1234567
** Find the records which the value of `my_column` is numeric values entirely {{code | code = SELECT * FROM `my_table` WHERE `my_column` REGEXP '^[0-9]+$'}}<ref>[http://stackoverflow.com/questions/14343767/mysql-regexp-with-and-numbers-only regex - Mysql REGEXP with . and numbers only - Stack Overflow]</ref><ref>[https://stackoverflow.com/questions/75704/how-do-i-check-to-see-if-a-value-is-an-integer-in-mysql How do I check to see if a value is an integer in MySQL? - Stack Overflow]</ref>
 
* Find the records which the value of `my_column` is not exactly 8 digits {{code | code = SELECT * FROM my_table WHERE LENGTH(my_column) != 8 OR my_column NOT REGEXP '^[0-9]{8}$'}}
** The `LENGTH()` function checks if the string length is not 8 characters
** The `REGEXP '^[0-9]{8}$'` pattern validates that the value contains exactly 8 digits from start (^) to end ($)
** Using both conditions ensures catching values with correct length but non-numeric characters, as well as incorrect lengths
 
* Check if a value is integer which may contains comma and dot symbols e.g. 1,234.567 or 3.414
** {{code | code = SELECT * FROM `my_table` WHERE `my_column` REGEXP '^[0-9,\.]+$'}}<ref>[https://community.denodo.com/answers/question/details?questionId=9060g000000XelhAAC&title=How+to+identify+if+values+in+a+column+is+numeric+%28+Function+similar+to+Isnumeric+is+SQL%29 How to identify if values in a column is numeric ( Function similar to Isnumeric is SQL)]</ref>
 
* Check if a value is NOT integer
** Find the records which the value of `my_column` is '''NOT''' numeric values entirely {{code | code = SELECT * FROM `my_table` WHERE `my_column` NOT REGEXP '^[0-9]+$'}}
 


=== Abnormal values of time data ===
If the digit of number is known, the SQL syntax could be more specific
Definition of abnormal values of the time data ([http://en.wikipedia.org/wiki/Time_series time series]) if they
* The {{kbd | key=tax_id}} column is 8 digits only. Find the well-formatted {{kbd | key=tax_id}} records by using {{code | code = SELECT * FROM `tax_id` WHERE `tax_id` REGEXP '^[0-9]{8}$'}}
* were generated 10 years before or
* newer than today


List of the possible abnormal values:
==== Verify if value is number in PHP ====
* {{code | code = 0001-01 00:00:00}} occurred in MySQL {{code | code = datetime}} type
 
* {{code | code = 1900/1/0}} (converted time formatted value from 0), {{code | code = 1900/1/1}} (converted time formatted value from 1), {{code | code = 1900/1/2}} ... occurred in MS Excel
* [http://php.net/manual/en/function.is-numeric.php is_numeric] function
* future data: the date after today
* [https://www.php.net/manual/en/function.is-int.php is_int] function
 
==== Verify if value is number in Excel or Google sheet ====
Excel & [https://www.google.com/sheets/about/ Google Sheets]:
* Using [http://www.techonthenet.com/excel/formulas/isnumber.php ISNUMBER Function]: {{code | code = <nowiki>=INT(ISNUMBER(A1))</nowiki>}}
** Return 1 if the cell value is (1) Numbers (2) Numbers in [https://en.wikipedia.org/wiki/Scientific_notation scientific (exponential) notation] e.g. {{code | code = <nowiki>1.36184E+14</nowiki>}} (3) Decimal numbers e.g. {{code | code = <nowiki>3.141592654</nowiki>}} (4) Negative numbers
** Return 0 if the cell value is (1) Text (2) Numbers that are stored as text e.g. {{code | code = <nowiki>="5"</nowiki>}}
 
*  Google Sheets only: Using [https://support.google.com/docs/answer/3098292?hl=zh-Hant REGEXMATCH], [https://support.google.com/docs/answer/3094140?hl=zh-Hant TRIM] & [https://support.google.com/docs/answer/3093592?hl=zh-Hant CONCAT]<ref>[https://errerrors.blogspot.com/2015/08/google.html GOOGLE 試算表: 數字轉成文字]</ref> functions: {{code | code = <nowiki>=IF(REGEXMATCH(CONCAT("", TRIM(A1)), "^\d+$"), 1, 0)</nowiki>}}
** Return 1 if the cell value is (1) Numbers (2) Numbers that are stored as text e.g. {{code | code = <nowiki>="5"</nowiki>}}
** Return 0 if the cell value is (1) Text (2) Numbers in scientific (exponential) notation e.g. {{code | code = <nowiki>1.23E+16</nowiki>}} (3) Decimal numbers e.g. {{code | code = <nowiki>3.141592654</nowiki>}} (4) Negative numbers
 
=== Time data: Validate the data format ===
[[Validate the datetime value]]
 
=== Time data: Data was generated in N years ===
Define the abnormal values of the time data ([http://en.wikipedia.org/wiki/Time_series time series])
* Verify the data were generated in N years. Possible abnormal values: {{code | code = 0001-01 00:00:00}} occurred in MySQL {{code | code = datetime}} type. e.g.
 
* Verify the data were not newer than today
 
* Verify the year of data were not {{kbd | key=1900}} if the data were imported from Microsoft Excel file. Datevalue<ref>[https://support.microsoft.com/zh-tw/office/datevalue-%E5%87%BD%E6%95%B8-df8b07d4-7761-4a93-bc33-b7471bbff252 DATEVALUE 函數 - Office 支援]</ref> was started from the year {{kbd | key=1900}} e.g.
** {{code | code = 1900/1/0}} (converted time formatted value from 0),  
** {{code | code = 1900/1/1}} (converted time formatted value from 1)
 
* Verify the diversity of data values e.g. [https://en.wikipedia.org/wiki/Variance Variance]


Find the normal values:  
Find the normal values:  
Line 279: Line 367:
** {{code | code = SELECT * FROM `my_table` WHERE ( YEAR( CURDATE() ) - YEAR( `my_time_column`) <= 10 )  AND  ( `my_time_column` < CURDATE() + 1); }}
** {{code | code = SELECT * FROM `my_table` WHERE ( YEAR( CURDATE() ) - YEAR( `my_time_column`) <= 10 )  AND  ( `my_time_column` < CURDATE() + 1); }}
* MySQL: Assume the data was generated in recent 10 years & not newer than current timestamp. More precision to second compared with the above approach.  
* MySQL: Assume the data was generated in recent 10 years & not newer than current timestamp. More precision to second compared with the above approach.  
** :{{code | code = SELECT * FROM `my_table` WHERE ( `my_time_column` >=  CURDATE() - INTERVAL 10 YEAR )  AND  ( `my_time_column` <= CURRENT_TIMESTAMP);}}  
** {{code | code = SELECT * FROM `my_table` WHERE ( `my_time_column` >=  CURDATE() - INTERVAL 10 YEAR )  AND  ( `my_time_column` <= CURRENT_TIMESTAMP);}}  
*** You need to check the {{code | code = SELECT CURRENT_TIMESTAMP);}} if correct or not before you delete the abnormal data (timezone issue)
*** You need to check the {{code | code = SELECT CURRENT_TIMESTAMP);}} if correct or not before you delete the abnormal data (timezone issue)


=== Check if the date valid ===
Abnormal values
* {{code | code = 1970-01-01 08:00:00}} (converted time formatted value from {{code | code =August 3, 2017}}) caused by the string contains special characters e.g. [https://en.wikipedia.org/wiki/Left-to-right_mark left-to-right mark (LRM) ]
 
Check if the date valid
* Excel: [https://support.office.com/en-us/article/WEEKDAY-function-60e44483-2ed1-439f-8bd0-e404c190949a?ui=en-US&rs=en-US&ad=US WEEKDAY function] <ref>[http://www.mrexcel.com/forum/excel-questions/322203-check-if-valid-date.html Check if a valid date?]</ref>
* Excel: [https://support.office.com/en-us/article/WEEKDAY-function-60e44483-2ed1-439f-8bd0-e404c190949a?ui=en-US&rs=en-US&ad=US WEEKDAY function] <ref>[http://www.mrexcel.com/forum/excel-questions/322203-check-if-valid-date.html Check if a valid date?]</ref>
** legal results: {{kbd | key=1}} (Sunday) ~ {{kbd | key=7}} (Saturday)
** legal results: {{kbd | key=1}} (Sunday) ~ {{kbd | key=7}} (Saturday)
Line 288: Line 379:
* PHP: [http://stackoverflow.com/questions/19271381/correctly-determine-if-date-string-is-a-valid-date-in-that-format php - Correctly determine if date string is a valid date in that format - Stack Overflow]
* PHP: [http://stackoverflow.com/questions/19271381/correctly-determine-if-date-string-is-a-valid-date-in-that-format php - Correctly determine if date string is a valid date in that format - Stack Overflow]


== duplicate data ==
=== Time data: Human birth year (age) data ===
=== find duplicate data ===
Based on the existing record, the longest-living person who lived to 122<ref>[https://en.wikipedia.org/wiki/Maximum_life_span Maximum life span - Wikipedia]</ref>.
* EXCEL:
** one column data: [http://www.extendoffice.com/documents/excel/1499-count-duplicate-values-in-column.html How to count duplicate values in a column in Excel?] Using {{kbd | key = COUNTIF(range, criteria)}} {{access | date = 2015-08-25}} or using '''Pivot Tables'''(樞紐分析表)  to find the occurrence of value >= 2
** two columns data: [https://support.microsoft.com/en-us/kb/213367 How to compare data in two columns to find duplicates in Excel] {{access | date = 2015-06-16}} {{exclaim}} It may costs too much time (larger than one hour) if the number of records exceeds 1,000,000


* MySQL:  
MySQL query is as follows<ref>[https://stackoverflow.com/questions/5773405/calculate-age-in-mysql-innodb sql - Calculate Age in MySQL (InnoDb) - Stack Overflow]</ref> where the column {{kbd | key=<nowiki>`birthday`</nowiki>}} is {{kbd | key=<nowiki>date</nowiki>}} type.
** Using {{kbd | key =CONCAT}} for multiple column ex: column_1, column_2
<pre>
<pre>
SELECT tmp.key FROM
WHERE TIMESTAMPDIFF(YEAR, `birthday`, CURDATE()) <= 122
(
SELECT count(*) count, CONCAT(  `column_1`, `column_2`  ) 'key'
FROM `table_name`
GROUP BY CONCAT( `column_1`, `column_2`  )
) tmp
WHERE tmp.count >=2
</pre>
</pre>


For counting purpose: find the count of repeated id (type: int) between table_a and table_b
Using [https://www.w3resource.com/mysql/date-and-time-functions/mysql-unix_timestamp-function.php UNIX_TIMESTAMP() function] to check the abnormality of birthday data is not appropriate. Because the birthdays which are earlier {{kbd | key=<nowiki>1970-01-01 00:00:00 UTC</nowiki>}} will all become zero.
<pre>
 
SELECT count(DISTINCT(id)) FROM table_a WHERE id IN
 
(
=== String contains special characters ===
  SELECT DISTINCT(id) FROM table_b
* [[Byte order mark]] (BOM)
)
* [[Return symbol]]
</pre>
* [http://www.fileformat.info/info/unicode/char/a0/index.htm Unicode Character 'NO-BREAK SPACE' (U+00A0)]
* [https://www.fileformat.info/info/unicode/char/200f/index.htm Unicode Character 'RIGHT-TO-LEFT MARK' (U+200F)]
* [https://www.fileformat.info/info/unicode/char/200f/index.htm Unicode Character 'RIGHT-TO-LEFT MARK' (U+200F)]<ref>[https://stackoverflow.com/questions/1930009/how-to-strip-unicode-chars-left-to-right-mark-from-a-string-in-php regex - How to strip unicode chars (LEFT_TO_RIGHT_MARK) from a string in php - Stack Overflow]</ref>
 
== File Validation ==
 
=== Verify the file format of downloaded file ===
* PDF file format: [https://stackoverflow.com/questions/16152583/tell-if-a-file-is-pdf-in-bash Tell if a file is PDF in bash - Stack Overflow]


* [https://chrome.google.com/webstore/detail/power-tools/dofhceeoedodcaheeoacmadcpegkjobi Power Tools] for Google Spreadsheet {{access | date = 2016-02-26}}
== Find and remove duplicates ==
** Menu: Data -> Remove duplicates
[[Find and remove duplicates]] in Excel/BASH/MySQL/PHP


=== deduplicate ===
== Counting ==
* EXCEL: Data Tools -> Remove Duplicates: [https://support.office.com/en-us/article/Filter-for-unique-values-or-remove-duplicate-values-d6549cf0-357a-4acf-9df5-ca507915b704 Filter for unique values or remove duplicate values] {{access | date = 2015-10-20}}
* PHP: [http://php.net/manual/en/function.array-unique.php PHP: array_unique], [http://php.net/manual/en/function.array-intersect.php PHP: array_intersect]
* MySQL:
** [http://www.mysqltutorial.org/mysql-distinct.aspx MySQL DISTINCT - Eliminate Duplicate Rows in a Result Set]. Using {{kbd | key =GROUP_CONCAT}} to handle the multiple columns<ref>[http://stackoverflow.com/questions/12188027/mysql-select-distinct-multiple-columns sql - MySQL SELECT DISTINCT multiple columns - Stack Overflow]</ref>
** [http://www.w3schools.com/sql/sql_unique.asp 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.
** "{{kbd | key = UNION}} removes duplicates, whereas {{kbd | key = UNION ALL}} does not." source: [http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all sql - What is the difference between UNION and UNION ALL? - Stack Overflow]
* [http://www.gnu.org/software/coreutils/manual/html_node/sort-invocation.html GNU Coreutils: sort invocation] ex: {{kbd | key=sort -us -o output_unique.file input.file}} to remove duplicate lines in a large text file (GB)<ref>[http://unix.stackexchange.com/questions/19641/how-to-remove-duplicate-lines-in-a-large-multi-gb-textfile linux - How to remove duplicate lines in a large multi-GB textfile? - Unix & Linux Stack Exchange]</ref> OS: {{Linux}}, cygwin of {{Win}}
* Google spreadsheet add-on: [https://www.ablebits.com/google-sheets-add-ons/remove-duplicates/howto.php Remove Duplicates for Google Sheets help]


=== counting number of duplicate occurrence ===
* [[Count occurrences of a word in string]]
* MySQL: find the number of duplicate occurrence between list_a & list_b which using the same primary key: column name {{kbd | key = id}}
* Count number of unique values
** {{kbd | key = SELECT count(DISTINCT(`id`)) FROM `list_a` WHERE `id` IN (SELECT DISTINCT(`id`) FROM `list_b`) ; }}
** Excel: [https://www.excel-easy.com/examples/count-unique-values.html Count Unique Values in Excel - Easy Excel Tutorial]
* Excel: [http://superuser.com/questions/307837/how-to-count-number-of-repeat-occurrences microsoft excel - How to count number of repeat occurrences - Super User] ''un-tested''
** Google sheet: [https://support.google.com/docs/answer/3093405?hl=zh-Hant COUNTUNIQUE - 文件編輯器說明] & [https://infoinspired.com/google-docs/spreadsheet/unique-function-in-horizontal-data-range-in-google-sheets/ How to Use UNIQUE Function in Horizontal Data Range in Google Sheets]


== outlier ==
== Outlier / Anomaly detection ==
* Median
[[Anomaly detection]]
* Range Checks


== unique number of data values ==
== unique number of data values ==
Line 354: Line 433:
</pre>
</pre>


==== other text look like whitespace ====
=== Remove other string look like whitespace ===
[https://en.wikipedia.org/wiki/Whitespace_character Whitespace character]
[https://en.wikipedia.org/wiki/Whitespace_character Whitespace character]
# [http://www.fileformat.info/info/unicode/char/3000/index.htm IDEOGRAPHIC SPACE] (全形空白, U+3000)<ref>[https://www.ptt.cc/bbs/PHP/M.1473522044.A.4FC.html Re: 請益 mysql空百=？ - 看板 PHP - 批踢踢實業坊]</ref>:
# [http://www.fileformat.info/info/unicode/char/3000/index.htm IDEOGRAPHIC SPACE] (全形空白、全型空白, U+3000)<ref>[https://www.ptt.cc/bbs/PHP/M.1473522044.A.4FC.html Re: 請益 mysql空百=？ - 看板 PHP - 批踢踢實業坊]</ref>:
#* diaplay: <pre><nowiki><?php $string = "111" . json_decode('"\u3000"') . "222"; echo $string;?></nowiki></pre>
#* diaplay: <pre><nowiki><?php $string = "111" . json_decode('"\u3000"') . "222"; echo $string;?></nowiki></pre>
#* replace with space: <pre><nowiki><?php echo str_replace(json_decode('"\u3000"'), " ", $string);?></nowiki></pre>
#* replace with space: <pre><nowiki><?php echo str_replace(json_decode('"\u3000"'), " ", $string);?></nowiki></pre>
# ASCII Vertical Tab \v
# ASCII Vertical Tab {{kbd | key=<nowiki>\v</nowiki>}}
# ASCII Horizontal Tab (TAB) \t
# ASCII Horizontal Tab (TAB) {{kbd | key=<nowiki>\t</nowiki>}}
# ASCII Backspace \b
# ASCII Backspace {{kbd | key=<nowiki>\b</nowiki>}}
# [[Remove non breaking space]]
 
== Remove control character ==
[https://en.wikipedia.org/wiki/Control_character Control character - Wikipedia] Using PHP to clean control character:
<pre>
$input = 'some string may contains control characters';
$replacement = '';
$result = preg_replace('/[\x00-\x1F]/', $replacement, $input);
</pre>
 
== Remove tracking parameter from link ==
[[Remove tracking parameter from link]]
 
=== Fix garbled message text ===
[[Fix garbled message text]]


== related pages ==
== Tools ==
* {{Mac}} [https://github.com/IvanMathy/Boop IvanMathy/Boop: A scriptable scratchpad for developers. In slow yet steady progress.] ([https://apps.apple.com/us/app/boop/id1518425043?mt=12 ‎Boop on the Mac App Store]) " ... to paste some plain text and run some basic text operations on it. "
* [https://gchq.github.io/CyberChef/ CyberChef] (source code available on [https://github.com/gchq/CyberChef github])  The Cyber Swiss Army Knife - a web app for encryption, encoding, compression and data analysis
 
== Further reading ==
* [https://g0v.gitbooks.io/data-design/content/book/ch08-data-cleaning.html Data Cleaning | DATA + DESIGN 資料＋設計]
* [https://g0v.gitbooks.io/data-design/content/book/ch08-data-cleaning.html Data Cleaning | DATA + DESIGN 資料＋設計]
* Data modeling: [[Data type]]
* Data modeling: [[Data type]]
* [http://www.icpsr.umich.edu/icpsrweb/NAHDAP/support/faqs/2006/01/what-is-codebook What is a codebook?]
* [https://www.facebook.com/groups/1210634969026548/permalink/1540468379376537/ 台灣R軟體Club] 「由於真實社會的資料千奇百怪，遺失值、空字串、空格、甚至是假的 "NA" (NA兩邊多了雙引號)、假的"NULL" (NULL兩邊多了雙引號) 。。。，其中一個顯著的差異是，社會實務應用的 R 程式中，通常會多出一堆檢查機制。 ... ...」{{access | date = 2017-12-18}}
* [https://docs.google.com/spreadsheets/d/1UyumfruGjps7xqd4aSB-NKfmu54NIscI2OV9Uht8F7M/edit?usp=sharing  判斷 Excel 欄位值是否錯誤] 除了常見的 Excel 錯誤，如何判斷來自其他資料來源的可能錯誤欄位值
* [[Web user behavior]]
* [https://medium.com/bryanyang0528/%E8%B3%87%E6%96%99%E5%93%81%E8%B3%AA%E5%88%9D%E6%8E%A2-data-quality-b765eb56a7c2?fbclid=IwAR3NBb2BtFm9O3FeY7JgQ5HLE5VG5nFe3m5Zx8zNW9XkvOUPlqV9hXmaoXI 資料品質初探(Data Quality) – 亂點技能的跨界人生 – Medium] {{access | date = 2018-01-13}}


== references ==
== References ==
<references/>
<references/>
{{Template:Data factory flow}}


[[Category:Spreadsheet]] [[Category:Excel]]
[[Category:Spreadsheet]] [[Category:Excel]]
Line 374: Line 479:
[[Category:Data Science]]
[[Category:Data Science]]
[[Category:MySQL]]
[[Category:MySQL]]
[[Category:String manipulation]]
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/17369...26093"

Navigation menu