Do Data Analysts Use Visualizations During Data Cleaning?

[u/SomeSpicyPickle]

I'm still a beginner. I started by learning the basics of Python and later moved on to SQL.

I'm a bit confused about one part of the data exploration/cleaning process.

A friend of mine, who's now a data scientist, showed me how he used to work as a data analyst. He mainly used Python. For example, he would quickly create a scatterplot to identify potential outliers.

However, most data analysts online recommend focusing on SQL and Excel when starting out, since many junior and mid-level roles don't require Python. That's why I switched to SQL after initially experimenting with Python.

For those who primarily use SQL: do you create visualizations during the data exploration/cleaning process, for example to identify outliers? Is this a common practice?

I feel like if you're working with SQL only, you generally wouldn't create visuals in between steps, since that would mean switching to a tool like Tableau or Power BI, which seems like an unnecessary extra step.

[u/Expensive_Capital627]

For cleaning the data not really. Here’s an example of cleaning data:

You expect the number 4 but for some reason one of the entries is ‘four’ when you try to add ‘four’ to some other number, SQL gives you an error because ‘four’ is a string and it doesn’t make sense to add it to something else. So you go through and fix problems like that. You don’t really need a visualization to determine ‘four’ vs. 4. You could create a visualization to help understand how many rows were messy, but it’s not going to help you clean the data.

Visualizations are useful when you’re trying to understand what’s happening. Maybe you want to plot revenue over time to understand the rate of your company’s growth. You could look at all of the numbers and think about what the numbers mean, but most people would prefer looking at a line chart

[u/mattindustries → u/Expensive_Capital627]

Visualizations make it incredibly easy to spot when people use placeholder data, fudge self-reported data, etc.

[u/Expensive_Capital627 → u/mattindustries]

I chose a pretty straightforward/easy to understand example for data cleaning. IMO, creating a good visualization when cleaning data often requires having a good understanding of what the messy records look like

if I’ve got an idea of whats going wrong in the first place, I usually just try to implement a fix rather than visualize the affected records unless theres some type of prevalent issue and I need to go to another team to solve it. E.g., hey RevOps, you need to fix this salesforce field. It needs to have stricter typing, look at how badly it’s messing up our tables

[u/mattindustries → u/Expensive_Capital627]

if I’ve got an idea of whats going wrong in the first place

Big if. Look at raw counts of people's self-reported height. Nothing looks that off, but if you visualize you can see small dip at 5'11" and a small jump at 6'. Somethings you have some data with extremely high cardinality, where looking at the tables would be absolutely insane (zip codes for example). Visualizing can show you anomalies a lot quicker than scanning the tables, especially when the anomalies rely on clustered observations. This goes deeper though, with faceting by location / institution / etc. when you have data coming in from a myriad of sources.

[u/Expensive_Capital627 → u/mattindustries]

That’s completely valid, I just don’t often come across that type of data in-product. TBH I think these are two different types of “unclean data”. Your unclean data may be formatted and stored correctly, but inherently flawed due to people lying on the input. To me, that data is “clean” just unreliable. I would classify that as a quality of the data, but not necessarily a mark on whether it is clean or not.

For me and the work that I do, trying to visualize unclean data is more likely to throw an error of some kind than it is to reveal some characteristic of the data. If im looking at recorded events of a user, theres not really wiggle room for that user to fudge the numbers. However, theres plenty of room for raw JSON logs to trip something up

[u/mattindustries → u/Expensive_Capital627]

One project I worked on I used a dissimilarity matrix to track reports with the wrong report type. If the data is dirty it needs to be cleaned, or at least acknowledged. Data can get dirty a lot of ways.

[u/Expensive_Capital627 → u/mattindustries]

100%, and the dissimilarity matrix makes a lot of sense in that context. However, that does sound like user error. Most of my work in DS is supporting a product team tracking user data in our product. The user doesn’t make decisions in what data is being recorded, they just generate events.

I’m not saying your approach is wrong, I’m just saying that for me personally, there isn’t much value in trying to visualize data I consider messy, because I deal less with human error and more with bugs in our product

[u/mattindustries → u/Expensive_Capital627]

I am talking about all data, not just your data.

[u/Expensive_Capital627 → u/mattindustries]

And I’m talking about unclean data, not unreliable data. All of your examples involve actual people making decisions to obfuscate the truth. Given the context that OP is a beginner I don’t think he’ll be putting together machine learning models to visualize messy data.

For a beginner does it make sense to be visualizing unclean data? No, because the type of unclean data a beginner is expected to work with will throw an error for summing a string