LemonWiki共筆

Data cleaning

Revision as of 18:57, 25 November 2025 by Planetoid (talk | contribs) (→Verify if value is number in MySQL)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Check list edit

  • 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 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. 2026-09-15
  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

Verify the strings are in valid email format edit

Rule: Email contains @ symbol

  • 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

Verify the strings are in valid url format edit

Rule: Begin with http or https

  • Google spreadsheet =REGEXMATCH(A1, "^http(s?)")

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.

Verify the column values are numeric edit

Possible values 

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

Verify if value is number in MySQL edit

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]
  • Find the records which the value of `my_column` is not exactly 8 digits 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
    • 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}$'

Verify if value is number in PHP edit

Verify if value is number in Excel or Google sheet edit

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)

  • Verify the data were generated in N years. Possible abnormal values: 0001-01 00:00:00 occurred in MySQL datetime type. e.g.
  • Verify the data were not newer than today
  • Verify 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)
  • Verify 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 2026-09-15. Which is the same with 2026-09-15 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)

Abnormal values

  • 1970-01-01 08:00:00 (converted time formatted value from August 3, 2017) caused by the string contains special characters e.g. left-to-right mark (LRM)

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

File Validation edit

Verify the file format of downloaded file 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[20] + LEN[21] functions [22]
  • Excel: if the length of text was fixed after removed, you may try to use REPLACE[23] + 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)[24]:
    • 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

  1. ↑ MySQL NULLIF() Function
  2. ↑ MySQL COALESCE() function - w3resource
  3. ↑ How to check if field is null or empty mysql? - Stack Overflow
  4. ↑ MySQL :: MySQL 5.0 Reference Manual :: 3.3.4.6 Working with NULL Values
  5. ↑ 如何判斷 Excel 儲存格的欄位值是 NULL
  6. ↑ SQL TRIM 函數 - 1Keydata SQL 語法教學
  7. ↑ Excel COUNTIFS and COUNTIF with multiple criteria – examples of usage
  8. ↑ Check if number is an Integer
  9. ↑ Excel specifications and limits
  10. ↑ A2
  11. ↑ regex - Mysql REGEXP with . and numbers only - Stack Overflow
  12. ↑ How do I check to see if a value is an integer in MySQL? - Stack Overflow
  13. ↑ How to identify if values in a column is numeric ( Function similar to Isnumeric is SQL)
  14. ↑ GOOGLE 試算表: 數字轉成文字
  15. ↑ DATEVALUE 函數 - Office 支援
  16. ↑ Check if a valid date?
  17. ↑ Maximum life span - Wikipedia
  18. ↑ sql - Calculate Age in MySQL (InnoDb) - Stack Overflow
  19. ↑ regex - How to strip unicode chars (LEFT_TO_RIGHT_MARK) from a string in php - Stack Overflow
  20. ↑ RIGHT、RIGHTB 函數 - Excel - Office.com
  21. ↑ LEN、LENB 函數 - Excel - Office.com
  22. ↑ How to remove first, last or certain characters from text in Excel?
  23. ↑ REPLACE、REPLACEB 函數 - Excel - Office.com
  24. ↑ Re: 請益 mysql空百=？ - 看板 PHP - 批踢踢實業坊

Data factory flow

Retrieved from "https://wiki.planetoid.info/index.php?title=Data_cleaning&oldid=26093"