Difference between revisions of "Data cleaning"

From LemonWiki共筆
Jump to navigation Jump to search
 
(35 intermediate revisions by the same user not shown)
Line 1: Line 1:
  
== check if field value was not fulfilled  ==
+
== Check list ==
 +
 
 +
* row count
 +
* duplicate data
 +
 
 +
== Check if field value was not fulfilled  ==
 
=== By purpose ===
 
=== By purpose ===
 
<table border="1" style="width: 100%">
 
<table border="1" style="width: 100%">
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 234: 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]]
  
 
=== Email contains @ symbol ===
 
=== Email contains @ symbol ===
Line 258: Line 289:
 
* 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 ===
+
=== Check if the column value is numeric ===
List of the possible abnormal values:
+
Possibile values
* All numeric values are odd or even if the data were generated by user naturally.
 
  
PHP:  
+
<pre>
* [http://php.net/manual/en/function.is-numeric.php is_numeric]
+
test data:
 +
3.141592654
 +
1.36184E+14
 +
123,456.789
 +
20740199601
 +
346183773390240
 +
="5"
 +
</pre>
  
 
MySQL:  
 
MySQL:  
* 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>
+
 
* 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]+$'}}
+
* 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>
 +
 
 +
* 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]+$'}}
 +
 
  
 
If the digit of number is known, the SQL syntax could be more specific
 
If the digit of number is known, the SQL syntax could be more specific
 
* 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}$'}}
 
* 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}$'}}
 +
 +
 +
PHP:
 +
* [http://php.net/manual/en/function.is-numeric.php is_numeric] function
 +
* [https://www.php.net/manual/en/function.is-int.php is_int] function
 +
  
 
Excel & [https://www.google.com/sheets/about/ Google Sheets]:  
 
Excel & [https://www.google.com/sheets/about/ Google Sheets]:  
Line 281: Line 332:
 
** 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
 
** 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
  
<pre>
+
=== Time data: Validate the data format ===
test data:
+
[[Validate the datetime value]]
3.141592654
 
1.36184E+14
 
20740199601
 
346183773390240
 
="5"
 
</pre>
 
 
 
=== Time data ===
 
  
==== Condition: Data was generated in 10 years ====
+
=== Time data: Data was generated in N years ===
Definition of abnormal values of the time data ([http://en.wikipedia.org/wiki/Time_series time series]) if they
+
Define the abnormal values of the time data ([http://en.wikipedia.org/wiki/Time_series time series])
* were generated 10 years before or
+
* Verfiy the data were generated in N years. Possible abnormal values: {{code | code = 0001-01 00:00:00}} occurred in MySQL {{code | code = datetime}} type.
* newer than today
+
* Verfiy the data were not newer than today
 
+
* Verfiy 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.
List of the possible abnormal values:
+
** {{code | code = 1900/1/0}} (converted time formatted value from 0),  
* {{code | code = 0001-01 00:00:00}} occurred in MySQL {{code | code = datetime}} type
+
** {{code | code = 1900/1/1}} (converted time formatted value from 1)
* {{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
+
* Verfiy the value of data were not {{kbd | key=0000-00-00 00:00:00}}
* future data: the date after today
+
* Verfiy the diversity of data values e.g. [https://en.wikipedia.org/wiki/Variance Variance]
  
 
Find the normal values:  
 
Find the normal values:  
Line 317: Line 360:
 
* 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]
  
==== Condition: Human birth year (age) data ====
+
=== Time data: Human birth year (age) 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>.  
 
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>.  
  
Line 333: Line 376:
 
* [http://www.fileformat.info/info/unicode/char/a0/index.htm Unicode Character 'NO-BREAK SPACE' (U+00A0)]
 
* [http://www.fileformat.info/info/unicode/char/a0/index.htm Unicode Character 'NO-BREAK SPACE' (U+00A0)]
  
== Duplicate data ==
+
== Find and remove duplicates ==
=== Find duplicate data ===
+
[[Find and remove duplicates]] in Excel/BASH/MySQL/PHP
==== EXCEL ====
 
===== Finding duplicate rows that differ in one column =====
 
* 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
 
 
 
===== Finding duplicate rows that differ in multiple columns =====
 
* two or multiple columns data: (approach 1) [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 (approach 2) Using [https://support.office.com/en-us/article/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2 CONCAT function] to concatenate two or multiple columns data. And then use {{kbd | key = COUNTIF(range, criteria)}}.
 
 
 
==== Cygwin ====
 
* [https://www.computerhope.com/unix/uuniq.htm uniq command] on Cygwin of {{Win}} or {{Linux}}: {{kbd | key=<nowiki>uniq -d <file.txt> > <duplicated_items.txt></nowiki>}}<ref>[https://unix.stackexchange.com/questions/52534/how-to-print-only-the-duplicate-values-from-a-text-file shell - How to print only the duplicate values from a text file? - Unix & Linux Stack Exchange]</ref>
 
 
 
==== MySQL ====
 
===== Finding duplicate rows that differ in one column =====
 
Find the duplicated data for one column<ref>[http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql?rq=1 Finding duplicate values in MySQL - Stack Overflow]</ref>
 
<pre>
 
-- 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;
 
</pre>
 
 
 
===== Finding duplicate rows that differ in multiple columns =====
 
Using {{kbd | key =CONCAT}} for multiple columns ex: column_1, column_2
 
<pre>
 
SELECT count(*) count, CONCAT(  `column_1`, `column_2`  ) 'key'
 
FROM `table_name`
 
GROUP BY CONCAT(  `column_1`, `column_2`  )
 
HAVING count > 1;
 
</pre>
 
 
 
or
 
<pre>
 
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
 
</pre>
 
 
 
===== other cases =====
 
For counting purpose: find the count of repeated id (type: int) between table_a and table_b
 
<pre>
 
SELECT count(DISTINCT(id)) FROM table_a WHERE id IN
 
(
 
  SELECT DISTINCT(id) FROM table_b
 
)
 
</pre>
 
 
 
==== Google Spreadsheet ====
 
 
 
* [https://www.ablebits.com/google-sheets-add-ons/remove-duplicates/index.php Remove duplicates in Google Sheets] 30 days free {{access | date = 2019-02-26}}
 
* [https://chrome.google.com/webstore/detail/power-tools/dofhceeoedodcaheeoacmadcpegkjobi Power Tools] for Google Spreadsheet {{access | date = 2019-02-26}}
 
** Menu: Data -> Remove duplicates
 
 
 
=== Deduplicate ===
 
* 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: select deduplicated records
 
** [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]
 
* MySQL: delete duplicated records
 
** [http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql sql - Delete all Duplicate Rows except for One in MySQL? - Stack Overflow]
 
 
 
* [http://www.gnu.org/software/coreutils/manual/html_node/sort-invocation.html GNU Coreutils: sort invocation] OS: {{Linux}}, cygwin of {{Win}}. More details on [[Alternative_Linux_commands#Merge_multiple_plain_text_files | Merge multiple plain text files]].
 
** To remove duplicate lines:
 
*** {{kbd | key=<nowiki>sort -us -o <output_unique.file> <input.file></nowiki>}} 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>
 
*** {{kbd | key=<nowiki>cat <input.file> | grep <pattern> | sort | uniq</nowiki>}} Processes text line by line and prints the '''unique''' lines which match a specified pattern. Equal to these steps: (1) {{kbd | key=<nowiki>cat <input.file> | grep <pattern> > <tmp.file></nowiki>}} (2) {{kbd | key=<nowiki>sort <tmp.file> | uniq</nowiki>}}
 
** Ignore first n line(s) & remove duplicate lines<ref>[https://stackoverflow.com/questions/14562423/is-there-a-way-to-ignore-header-lines-in-a-unix-sort sorting - Is there a way to ignore header lines in a UNIX sort? - Stack Overflow]</ref><ref>[http://linux.vbird.org/linux_basic/0320bash.php#redirect_com 命令執行的判斷依據: ; , &&, ||]</ref><ref>[https://www.computerhope.com/unix/utail.htm Linux tail command help and examples]</ref>
 
*** (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>}}
 
 
 
* 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 ===
 
MySQL: find the number of duplicate occurrence between list_a & list_b which using the same primary key: column name {{kbd | key = id}}
 
* {{kbd | key = SELECT count(DISTINCT(`id`)) FROM `list_a` WHERE `id` IN (SELECT DISTINCT(`id`) FROM `list_b`) ; }}
 
 
 
Excel:
 
* [http://superuser.com/questions/307837/how-to-count-number-of-repeat-occurrences microsoft excel - How to count number of repeat occurrences - Super User] {{exclaim}} long number issue: [https://superuser.com/questions/783840/countif-incorrectly-matches-long-number microsoft excel - Countif incorrectly matches long number - Super User]
 
 
 
=== Other ===
 
* symbol e.g. data-mining or data_mining
 
  
 
== Counting ==
 
== Counting ==
  
[[Count occurrences of a word in string]]
+
* [[Count occurrences of a word in string]]
 +
* Count number of unique values
 +
** Excel: [https://www.excel-easy.com/examples/count-unique-values.html Count Unique Values in Excel - Easy Excel Tutorial]
 +
** 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 / Anomaly detection ==
 
== Outlier / Anomaly detection ==
Anomaly detection of numeric data
+
[[Anomaly detection]]
* Median
 
* Range Checks
 
* All values is event
 
* The values are the same even the column is totally different
 
 
 
Anomaly detection of categorical data (qualitative variable)
 
* Normal distribution e.g. The interest of audiences should be very different NOT coherent
 
 
 
Anomaly detection for time series data
 
* Trend
 
* Dramatically Increase or decrease of rows count for each time period
 
 
 
More on: [https://en.wikipedia.org/wiki/Outlier#Identifying_outliers Outlier - Wikipedia]
 
  
 
== unique number of data values ==
 
== unique number of data values ==
Line 489: Line 415:
 
# ASCII Horizontal Tab (TAB) {{kbd | key=<nowiki>\t</nowiki>}}
 
# ASCII Horizontal Tab (TAB) {{kbd | key=<nowiki>\t</nowiki>}}
 
# ASCII Backspace {{kbd | key=<nowiki>\b</nowiki>}}
 
# ASCII Backspace {{kbd | key=<nowiki>\b</nowiki>}}
# [https://en.wikipedia.org/wiki/Non-breaking_space Non-breaking space] ({{kbd | key=<nowiki>nbsp;</nowiki>}}) Replace Non-breaking space with one whitespace using PHP: {{kbd | key=<nowiki>$result = str_replace("\xc2\xa0", ' ', $original_string);</nowiki>}}<ref>[https://stackoverflow.com/questions/40724543/how-to-replace-decoded-non-breakable-space-nbsp php - How to replace decoded Non-breakable space (nbsp) - Stack Overflow]</ref>
+
# [[Remove non breaking space]]
# [https://en.wikipedia.org/wiki/Sentence_spacing_in_digital_media Sentence spacing in digital media - Wikipedia] e.g. {{kbd | key=<nowiki>&nbsp; &ensp; &emsp;</nowiki>}}
 
 
 
How to display the Non-breaking space In PHP?
 
<pre>
 
$input = '12345678' . hex2bin('c2a0');
 
echo $input . PHP_EOL;
 
## Result of above script: '12345678 ' (one whitespace at the end)
 
 
 
echo bin2hex($input) . PHP_EOL;
 
## Result of above script: 3132333435363738c2a0
 
 
 
echo bin2hex('12345678') . PHP_EOL;
 
## Result of above script: 3132333435363738 (You mat notice the difference of script result is C2A0)
 
 
 
</pre>
 
 
 
How to display the Non-breaking space In MySQL?
 
<pre>
 
SELECT CONCAT('12345678', UNHEX('C2A0'))
 
-- Result of above query: '12345678 ' (one whitespace at the end)
 
 
 
SELECT HEX(CONCAT('12345678', UNHEX('C2A0')))
 
-- Result of above query: 3132333435363738C2A0
 
 
 
SELECT HEX('12345678')
 
-- Result of above query: 3132333435363738 (You mat notice the difference of query result is C2A0)
 
 
 
SELECT LENGTH('12345678')
 
-- Result of above query: 8
 
 
 
SELECT LENGTH(CONCAT('12345678', UNHEX('C2A0')))
 
-- Result of above query: 10
 
</pre>
 
  
 
== Remove control character ==
 
== Remove control character ==
Line 531: Line 424:
 
$result = preg_replace('/[\x00-\x1F]/', $replacement, $input);
 
$result = preg_replace('/[\x00-\x1F]/', $replacement, $input);
 
</pre>
 
</pre>
 +
 +
== Remove tracking parameter from link ==
 +
[[Remove tracking parameter from link]]
  
 
=== Fix garbled message text ===
 
=== Fix garbled message text ===
 
[[Fix garbled message text]]
 
[[Fix garbled message text]]
 +
 +
== Tools ==
 +
* [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. "
  
 
== Further reading ==
 
== Further reading ==
Line 544: Line 443:
 
* [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}}
 
* [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/>
  
Line 551: Line 450:
 
[[Category:Data Science]]
 
[[Category:Data Science]]
 
[[Category:MySQL]]
 
[[Category:MySQL]]
[[Category:Text file processing]]
+
[[Category:String manipulation]]

Latest revision as of 17:46, 1 November 2022

Check list[edit]

  • row count
  • duplicate data

Check if field value was not fulfilled[edit]

By purpose[edit]

Purpose Method (MySQL query syntax) Value1:
Fulfilled value what I want
Value2:
Fulfilled value NOT I want
Value3:
0
Value4:
NULL value
Value5:
Empty or white-spaces characters
values were not fulfilled or empty
(not contains 0)
WHERE column_name IS NULL
OR LENGTH(TRIM( column_name )) = 0
V V
values were not fulfilled or empty
(contains 0)
V V V
values were fulfilled and non-empty
(not contains 0)
V V
values were fulfilled and non-empty
(contains 0)
WHERE LENGTH(TRIM( column_name )) > 0 V V V
values (1) were not fulfilled or empty values (2) NOT I want
(not contains 0)
WHERE column_name IS NULL
OR LENGTH(TRIM( column_name )) = 0
OR column_name LIKE 'values NOT I want'
V V V
values (1) were not fulfilled or empty values (2) NOT I want
(contains 0)
V V V V

By datatype[edit]

VARCHAR and NOT allows NULL value[edit]

Using NULLIF() function[1]

SQL query:

SELECT NULLIF(TRIM(`my_column`), "")

Example result:

SELECT NULLIF(null, "");
-- return NULL

SELECT NULLIF("", "");
-- return NULL

SELECT NULLIF(TRIM("   "), "");
-- return NULL

SELECT NULLIF(TRIM("not empty string   "), "");
-- return "not empty string"


VARCHAR and allows NULL value[edit]

data type of column possible column values method1:
find not fulfilled or empty values
method2:
find fulfilled and non-empty values
method3:
find NULL values
method4:
find not NULL values
VARCHAR and allows NULL fulfilled value ex:123 V V
NULL type:null V V
'NULL' type:string
0 V V
EMPTY ex: '' or space(s) ' ' V V

symbol V: means the column value will be able to find by means of the method


  • method1:
    • SELECT * FROM `my_table` WHERE COALESCE(column_name, '') = ''[2]
    • SELECT * FROM `my_table` WHERE column_name IS NULL OR LENGTH(TRIM( column_name )) = 0
    • SELECT * FROM `my_table` WHERE column_name IS NULL OR column_name = ''[3]
  • method2:
    • SELECT * FROM `my_table` WHERE column_name > ''
    • SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) > 0
    • SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) != 0
  • method3: SELECT * FROM `my_table` WHERE column_name IS NULL
  • method4: SELECT * FROM `my_table` WHERE column_name IS NOT NULL

VARCHAR or numeric[edit]

data type of column possible column values method5:
find values within the range
method6:
find values out of the range, empty & NULL values
VARCHAR or numeric values within the range ex: min ≤ value ≤ max V
values out of range V
NULL V
EMPTY ex: '' or space ' ' V
  • method5: SELECT * FROM `my_table` WHERE column_name BETWEEN min AND max
  • method6: SELECT * FROM `my_table` WHERE ( (COALESCE(column_name, '') = '') OR (column_name NOT BETWEEN min AND max) )

datetime and allows NULL value[edit]

possible column values

  1. 2022-12-03
  2. 00-00-00 00:00:00
  3. NULL

is null[edit]

Fill 0 if the value is NA or NULL

  • MySQL SQL syntax: COALESCE(): SELECT COALESCE(column_name, 0) or SELECT COALESCE(column_name, 'other_filled_value')
    • (1) Using COALESCE() function to replace the NULL value with 0.
    • (2) The case: 0/0 = null should be handled.
  • MySQL SQL syntax: combined IF() & ISNULL():SELECT IF(ISNULL((column_name), 0, column_name) or SELECT IF(ISNULL((column_name), 'other_filled_value', column_name)
  • python: pandas.DataFrame.fillna — pandas 0.16.0 documentation "Fill NA/NaN values using the specified method"

Find whether a variable is NULL. online demo

  • PHP is_null to find type:null null NOT type:string 'null' Icon_exclaim.gif
  • Google spreadsheet / Excel:
    • ISERR(value) " value - The value to be verified as an error type other than #N/A." ex: #NULL!
    • If the cell value is exactly NULL not #NULL!, You may use COUNTIF(value, "NULL") or EXACT(value, "NULL")
  • MySQL SQL syntax: SELECT * FROM table WHERE column IS NULL;[4]
  • R: is.null(): R: The Null Object
  • Excel[5]

Find whether a variable is NOT NULL

  • MySQL SQL syntax: SELECT * FROM table WHERE column IS NOT NULL;

Find whether a variable is NOT #N/A

  • Excel: =NOT(ISERROR(cell_value))

javascript

check if field value was not fulfilled: NULL, empty value[edit]

Icon_exclaim.gif NOT include those data which its field value fulfilled with default value automatically (demo on sqlfiddle)

  1. Good.gif quick solution: find records with NULL value OR empty, space value
    • MySQL solution: SELECT * FROM table_name WHERE column_name IS NULL OR LENGTH(TRIM( column_name )) = 0;
  2. find records with NULL value: (note: not #NULL!)
    • MySQL solution: SELECT * FROM table_name WHERE column_name IS NULL;
    • EXCEL: =EXACT(A2, "NULL")
  3. find records with empty value: (not contains NULL value)
    • MySQL: SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) = 0; Icon_exclaim.gif SQL query SELECT * FROM table_name WHERE column_name IS NOT NULL includes empty value
    • MS SQL Server: SELECT * FROM table_name WHERE LEN( LTRIM(RTRIM(column_name)) ) = 0; [6]
  4. Excel starting date: 1900/1/0 (converted time formatted value from 0), 1900/1/1 (converted time formatted value from 1), 1900/1/2 ...
    • solution: step1: Replace the year > 100 from this year with empty value at EXCEL: =IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)<1914, "", A2)) (this formula also handle empty value and non well-formatted column value ex: 0000-12-31 ) ; step2: change the format of cell to time format
    • trivial approach : EXCEL: =IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)-YEAR(NOW())>100, "", A2)) Icon_exclaim.gif this formula could not handle empty value because it return 0. If I change the format of cell to time format, 0 will become 1900/1/0.
  5. Using PHP
    • empty() function to find 0, null, false, empty string, empty array values.
    • if(empty($var) && $var !== 0 && $var !== "0"){ .. } to find null, false, empty string, empty array values BUT not 0.
  6. check if field value was NULL & not equal to some value

check if field value was fulfilled[edit]

length of string > 0

  • MySQL: SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) != 0; demo[1]

column value is not null or 0

  • Excel: COUNTIFS(criteria_range1, "<>NULL", criteria_range1, "<>0")[7]

find if number or cell value is positive integer

  • EXCEL: =IFERROR(IF(AND(INT( value )= value, value>0), TRUE, FALSE), FALSE)[8] online demo

check numeric range

  • MySQL: SELECT * FROM table_name WHERE column_name BETWEEN min_number AND max_number; the value >= min_number AND value <= max_number ( min_number ≤ value ≤ max_number )

find NOT empty records means records without NULL or empty value:

  • MySQL: SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) != 0;
  • MySQL: SELECT * FROM table_name WHERE column_name != '' AND column_name IS NOT NULL;

Data Validation[edit]

Validate the format of field value. Related page: Regular expression

Email contains @ symbol[edit]

  • EXCEL: =IF(ISERR(FIND("@", A2, 1)), FALSE, TRUE) 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
  • EXCEL: =FIND("@", A2, 2) only check the field if contains @ symbol or not
    • syntax: FIND(find_text, with_text, [start_num]) the start_num is 2 because the position of @ symbol should be larger than 1 (position of first char is 1)
    • result: (1) normal condition: return the number larger than 1; (2) exceptional condition: return #VALUE! if @ symbol was not found
  • PHP: PHP FILTER_VALIDATE_EMAIL Filter
    • "Returns the filtered data, or FALSE if the filter fails." quoted from PHP.net

Number precision in Excel[edit]

Number precision: 15 digits (Excel中最多的有效位數為15位)[9][10]

raw data: 1234567890123456 ->

  • (numeric format 數值格式) 1234567890123450.00 Icon_exclaim.gif losing precision
  • (general format 通用格式) 1.23457E+15 Icon_exclaim.gif losing precision
  • (text format 文字格式) 1234567890123456

large numbers

  • If the data was imported from Excel, you should notice the 15 digit precision issue.

Check if the column value is numeric[edit]

Possibile values

test data:
3.141592654
1.36184E+14
123,456.789
20740199601
346183773390240
="5"

MySQL:

  • Check if a value is integer e.g. 1234567
    • Find the records which the value of `my_column` is numeric values entirely SELECT * FROM `my_table` WHERE `my_column` REGEXP '^[0-9]+$'[11][12]
  • Check if a value is integer which may contains comma and dot symbols e.g. 1,234.567 or 3.414
    • SELECT * FROM `my_table` WHERE `my_column` REGEXP '^[0-9,\.]+$'[13]
  • Check if a value is NOT integer
    • Find the records which the value of `my_column` is NOT numeric values entirely SELECT * FROM `my_table` WHERE `my_column` NOT REGEXP '^[0-9]+$'


If the digit of number is known, the SQL syntax could be more specific

  • The tax_id column is 8 digits only. Find the well-formatted tax_id records by using SELECT * FROM `tax_id` WHERE `tax_id` REGEXP '^[0-9]{8}$'


PHP:


Excel & Google Sheets:

  • Using ISNUMBER Function: =INT(ISNUMBER(A1))
    • Return 1 if the cell value is (1) Numbers (2) Numbers in scientific (exponential) notation e.g. 1.36184E+14 (3) Decimal numbers e.g. 3.141592654 (4) Negative numbers
    • Return 0 if the cell value is (1) Text (2) Numbers that are stored as text e.g. ="5"
  • Google Sheets only: Using REGEXMATCH, TRIM & CONCAT[14] functions: =IF(REGEXMATCH(CONCAT("", TRIM(A1)), "^\d+$"), 1, 0)
    • Return 1 if the cell value is (1) Numbers (2) Numbers that are stored as text e.g. ="5"
    • Return 0 if the cell value is (1) Text (2) Numbers in scientific (exponential) notation e.g. 1.23E+16 (3) Decimal numbers e.g. 3.141592654 (4) Negative numbers

Time data: Validate the data format[edit]

Validate the datetime value

Time data: Data was generated in N years[edit]

Define the abnormal values of the time data (time series)

  • Verfiy the data were generated in N years. Possible abnormal values: 0001-01 00:00:00 occurred in MySQL datetime type.
  • Verfiy the data were not newer than today
  • Verfiy the year of data were not 1900 if the data were imported from Microsoft Excel file. Datevalue[15] was started from the year 1900 e.g.
    • 1900/1/0 (converted time formatted value from 0),
    • 1900/1/1 (converted time formatted value from 1)
  • Verfiy the value of data were not 0000-00-00 00:00:00
  • Verfiy the diversity of data values e.g. Variance

Find the normal values:

  • MySQL: Assume the data was generated in recent 10 years & not newer than today
    • SELECT * FROM `my_table` WHERE ( `my_time_column` >= CURDATE() - INTERVAL 10 YEAR ) AND ( `my_time_column` < CURDATE() + 1);
      • Icon_exclaim.gif NOT `my_time_column` < CURDATE()。 ex: CURDATE() is 2022-12-03. Which is the same with 2022-12-03 00:00:00
    • 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.
    • SELECT * FROM `my_table` WHERE ( `my_time_column` >= CURDATE() - INTERVAL 10 YEAR ) AND ( `my_time_column` <= CURRENT_TIMESTAMP);
      • You need to check the SELECT CURRENT_TIMESTAMP); if correct or not before you delete the abnormal data (timezone issue)

Check if the date valid

Time data: Human birth year (age) data[edit]

Based on the existing record, the longest-living person who lived to 122[17].

MySQL query is as follows[18] where the column `birthday` is date type.

WHERE TIMESTAMPDIFF(YEAR, `birthday`, CURDATE()) <= 122

Using UNIX_TIMESTAMP() function to check the abnormality of birthday data is not appropriate. Because the birthdays which are earlier 1970-01-01 00:00:00 UTC will all become zero.


String contains special characters[edit]

Find and remove duplicates[edit]

Find and remove duplicates in Excel/BASH/MySQL/PHP

Counting[edit]

Outlier / Anomaly detection[edit]

Anomaly detection

unique number of data values[edit]

If the data values were generated by different users, the unique number of data values should be larger than ____

Data handling[edit]

Remove first, last or certain characters from text[edit]

  • Excel: using RIGHT[19] + LEN[20] functions [21]
  • Excel: if the length of text was fixed after removed, you may try to use REPLACE[22] + LEN functions (demo)

Remove leading and trailing spaces from text[edit]

UPDATE `table` 
SET `column` = TRIM( `column` ) 
WHERE LENGTH(TRIM( `column` )) != LENGTH( `column` );

Remove other string look like whitespace[edit]

Whitespace character

  1. IDEOGRAPHIC SPACE (全形空白、全型空白, U+3000)[23]:
    • diaplay:
      <?php $string = "111" . json_decode('"\u3000"') . "222"; echo $string;?>
    • replace with space:
      <?php echo str_replace(json_decode('"\u3000"'), " ", $string);?>
  2. ASCII Vertical Tab \v
  3. ASCII Horizontal Tab (TAB) \t
  4. ASCII Backspace \b
  5. Remove non breaking space

Remove control character[edit]

Control character - Wikipedia Using PHP to clean control character:

$input = 'some string may contains control characters';
$replacement = '';
$result = preg_replace('/[\x00-\x1F]/', $replacement, $input);

Remove tracking parameter from link[edit]

Remove tracking parameter from link

Fix garbled message text[edit]

Fix garbled message text

Tools[edit]

Further reading[edit]

References[edit]