Data Training from Zomalex

How to clean messy data

Often, we receive a large dataset, possibly in an Excel spreadsheet. The dataset often has many columns. Here are some suggestions about how to check and clean the data.

Firstly inspect the data, and ask the question - is this reasonable?

What is the size of the table ? How many rows, columns?

Is the data really tabular?

Are the data in the rows consistent from row to row?
If there are any calculated columns, is the same calculation used for every row?
Does every cell have one and only one value?
Are there any ‘junk’ rows i.e. rows that do not represent an item of data e.g. subtotals, extra headers, comment lines, footers?
Is it two differnt types of data crammed into one table

Look at the data column by column and ask these questions:

What is the data type of each column?
Are the column names short and meaningful?
Do date columns contain only dates?
Do numeric colummns only contain numbers?
For categorical (text) columns, what is the distribution of the values, and the cardinality?
For numerical columns, what is the min, max and average?

Are there any error cells? i.e. cells with #NA! or other errors?

Are there any missing values? If so:

Is it reasonable to have missing values in this column?
Do the missing values represent “not applicable” or “data not collected” or something else?

Is the data really tidy? Or is the same variable spread across several columns?

Are there any suspicious values, for example:

possible mis-spellings
outliers in numeric columns
values that are much more frequent than we might reasonably expect?

Is the data denormalised - can we split it into a fact table with feerwer columns and one or more dimension tables?

see if columns with names that are likely to represent dimsional attributes e.g. NHS patient id, product, region,… There are often product classifications, people or entities (actors), geograhical or date columns
Are these value repeated - which would suggest that the column belongs in a dimension table

Act to clean the data

shorten and rename the columns - move any long text to a column description metadata (many tools have this)
remove any junk rows
if this is Excel data, fix any #ERR! style errors
move appropriate columns into a dimension table
unpivot the data to go from wide to long (tidy) format.

Sometimes a dataset does not have a numeric column to summed as the quantitaive measure. Each row in the detata may represent for example an event or an employee and there may be no natural column to sum up. In this case, creat a new measure that counts the number of items. for example in Power BI’s DAX language

Number of Items = COUNTROWS('Fact'[Fact Id])