In my previous role, I had to deal with data cleaning and preparation using SQL. It was the only way available in the company and I didn't question it much. When I started working on my own projects, I found out the main weakness of this approach - a lot of preparation was needed to perform SQL manipulations, and it is not always necessary, especially for small tables.
I also tried Excel and Google Sheet, which is useful for a few tables with not too many rows and columns. But it's very time consuming and requires a lot of manual work.
So I was really excited to learn about Tableau Prep. The main benefit is the ability to visualize the entire data flow and see the preparation process.
It can connect to different data sources and then clean, transform, and join the data with a few clicks. The most useful feature of Tableau Prep is this data overview:
For each column, we can see the unique values and the number of appearances in the dataset. It's a great feature to have a general overview of a dataset and find duplicates, misspellings or values out of confidence interval.
Let's look at a quick example of data cleaning. We will take this dataset from Preppin' Data Blog with the information about how students treveled to school each day of the week.
Let's see what we can do with this data. Fortunately, a lot!
Let's start with pivoting table, to have all the "travel methods" in one column.
We'll turn columns with weekday information in rows and then have 2 new columns, "Day of the week" and "Travel method". I am also renaming days of the week and column names on this step.
Looks more useful! Let's clean up the "Travel Method" a little bit.
We have a lot of misspellings here:
Our goal is to have all variations of "walk" and "scooter" in one correct word. We can achieve this by grouping by spelling:
Boom! Automatically we have 11 values instead of 21. Doing this manually in Excel or even SQL would take much more time.
Now we can manually group "Hooped" and "Jumped" together and correct "Aeroplane" by right-clicking and selecting "Edit Value". We don't question the realistic level in this case 😁
That's how we get less cute, but much more useful data in just a few clicks!
Thanks for reading, see you in the next post 😊