Once I become familiar with a dataset, I process it ready for analysis. This includes any cleaning and transformation that may be required.

Data Cleaning

I clean the data to make sure it is complete and correct, otherwise my analysis may lead to inaccurate insights. The process I use varies depending on the source.

Generally, I check for and complete the following activities:

• only keep data relevant to the problem (reduces complexity and increases efficiency)

• check for and remove duplicate observations (accuracy)

• check for and update misspelled values (ensures consistent categorisation)

• check for null values and consider if they need to be removed, populated, or remain empty (accuracy)

• remove extra spaces (consistency)

• check the range of values in each column to ensure they are appropriate (outliers may be an error or could be integral to the analysis)

I verify any changes made to ensure they were executed appropriately.

Transformation

Transforming data helps me organise the data and makes it easier to use.

During this process, I:

• review and update column names (this ensures they are meaningful)

• combine or separate data where required (e.g. separating Date/Time for efficiency)

• check and update required column data types (this ensures they match the data)

• denormalise the data (for performance and dimension modelling)

I perform other transformations during analysis as required by the project. I adhere to Matthew Roche's general rule of "data should be transformed as far upstream as possible, and as far downstream as necessary". Meaning, if I'm making a transformation, I make it as close to the source as practically possible. This helps to:

• increase efficiency and performance (transform once and reuse the data)  

• reduce maintenance (single or fewer locations to maintain logic)

• introduce consistency and simplicity (the same data can be used across multiple applications)

Tools

The tools I use to inspect and process data vary depending on the source and requirements of the project.

Below is an example where I used R for my '2021 Cyclistic Riders' project.