Data cleaning

Check list

• 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

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

VARCHAR and NOT allows NULL value

Using NULLIF() function

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

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, '') = ''
  • 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 = ''
• 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

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

possible column values

1. 2024-04-28
2. 00-00-00 00:00:00
3. NULL

is null

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'
• 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;
• R: is.null(): R: The Null Object
• Excel

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

• JavaScript isNaN() Function

check if field value was not fulfilled: NULL, empty value

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

1. 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; 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;
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)) 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
   • MySQL: MySql select on fields containing null values - Stack Overflow

check if field value was fulfilled

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

find if number or cell value is positive integer

• EXCEL: =IFERROR(IF(AND(INT( value )= value, value>0), TRUE, FALSE), FALSE) 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

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

Verify the strings are in valid email format

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

Rule: Begin with http or https

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

Number precision in Excel

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

raw data: 1234567890123456 ->

• (numeric format 數值格式) 1234567890123450.00 losing precision
• (general format 通用格式) 1.23457E+15 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

Possible values

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

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 SELECT * FROM `my_table` WHERE `my_column` REGEXP '^[0-9]+$'

• 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,\.]+$'

• 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

• is_numeric function
• is_int function

Verify if value is number in Excel or Google sheet

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

Validate the datetime value

Time data: Data was generated in N years

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 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);
    • NOT `my_time_column` < CURDATE()。 ex: CURDATE() is 2024-04-28. Which is the same with 2024-04-28 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

• Excel: WEEKDAY function
  • legal results: 1 (Sunday) ~ 7 (Saturday)
  • illegal result: #VALUE!
• PHP: php - Correctly determine if date string is a valid date in that format - Stack Overflow

Time data: Human birth year (age) data

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

MySQL query is as follows 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

• Byte order mark (BOM)
• Return symbol
• Unicode Character 'NO-BREAK SPACE' (U+00A0)
• Unicode Character 'RIGHT-TO-LEFT MARK' (U+200F)
• Unicode Character 'RIGHT-TO-LEFT MARK' (U+200F)

File Validation

Verify the file format of downloaded file

• PDF file format: Tell if a file is PDF in bash - Stack Overflow

Find and remove duplicates

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

Counting

• Count occurrences of a word in string
• Count number of unique values
  • Excel: Count Unique Values in Excel - Easy Excel Tutorial
  • Google sheet: COUNTUNIQUE - 文件編輯器說明 & How to Use UNIQUE Function in Horizontal Data Range in Google Sheets

Outlier / Anomaly detection

Anomaly detection

unique number of data values

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

Data handling

Remove first, last or certain characters from text

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

Remove leading and trailing spaces from text

• PHP: trim function
• Excel: Excel formula: Remove leading and trailing spaces from text | Exceljet
• MySQL: Using TRIM() function & LENGTH() function

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

Remove other string look like whitespace

Whitespace character

1. IDEOGRAPHIC SPACE (全形空白、全型空白, U+3000):
   • 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

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

Remove tracking parameter from link

Fix garbled message text

Fix garbled message text

Tools

• Mac IvanMathy/Boop: A scriptable scratchpad for developers. In slow yet steady progress. (‎Boop on the Mac App Store) " ... to paste some plain text and run some basic text operations on it. "
• CyberChef (source code available on github) The Cyber Swiss Army Knife - a web app for encryption, encoding, compression and data analysis

Further reading

• Data Cleaning | DATA + DESIGN 資料＋設計
• Data modeling: Data type
• What is a codebook?
• 台灣R軟體Club 「由於真實社會的資料千奇百怪，遺失值、空字串、空格、甚至是假的 "NA" (NA兩邊多了雙引號)、假的"NULL" (NULL兩邊多了雙引號) 。。。，其中一個顯著的差異是，社會實務應用的 R 程式中，通常會多出一堆檢查機制。 ... ...」[Last visited: 2017-12-18]
• 判斷 Excel 欄位值是否錯誤 除了常見的 Excel 錯誤，如何判斷來自其他資料來源的可能錯誤欄位值
• Web user behavior
• 資料品質初探(Data Quality) – 亂點技能的跨界人生 – Medium [Last visited: 2018-01-13]