Here we go through a number of steps to help you understand better how to approach this, and what benefits it will bring to your data analytics project.
Why would I group in Tableau?
When you are working with large data sets , sometimes it easier to understand its meaning when the data is stored with simialr data items. Grouping the data has the following benefits:
(A) It allows a quick summary of data, and how large that data set is.
(B) Also groupings can alert to small subsets of data you may have not been aware of.
(C) Another benefit is that groups can be shown that have errors, and fixing them will put them in with the correct data.
(D) You can visually see groups, using Tableau will then you to keep them together.
Grouping by using a field in the data pane
The main way to group is when you are in the data pane, right click on the field you want to group by , then click create group.
For this example we can choose a number of values within channel, that we want to group by, here we pick all the items that have the value web.
You will notice that even before we click apply, it shows there are some data quality issues around the name that they are not consistent. You could use this to run metrics to catch these problems and count the no that occur.
When they are fixed then these should not appear anymore.
The output of this appears like this:
And on the screen , with the grouping now assigned, everything for Channel with web in it, is on one area:
Finally sometimes within your group, you may want an “other” category. The purpose of this is to catch items that dont fall into the group you have assigned, and sometimes they may come in later to the dataset as it expands.
You can achieve this as follows:
Giving in the output:
So in summary grouping can help you to identify a no of similar items to keep together, and also it is very useful to track data quality items as they arise and are fixed.
You are probably sitting there hearing about big data and databases, data analytics and machine learning and wonder where a data analyst fits in?
Here we will look to break it down step by step.
Sometimes a data analyst can be confused with a business analyst; there are subtle differences:
Business Analyst: Their role is to document the user’s requirements in a document that is descriptive of what the user wants.
In this case, a document that all parties can agree to is created, and it can be used as part of the project sign-off.
Data Analyst: On the other hand, a data analyst will take the business requirements and translate them into data deliverables.
They use the document to ensure the project has the right data to meet the project objectives in the right place at the right time.
Data Mapping
In different data projects, there will be a need to reconcile the data between systems, a data analysis will help here.
In a data mapping exercise, the data analyst will be expected to look at one or more sources and map them to a destination system.
This ensures a match between the two datasets.
Which results in the ability to reconcile the two systems.
Allows the ability to use data in multiple systems, knowing the consistency is in place.
Consistency of the data types between the systems.
It ensures that data validation errors are kept to a minimum.
Often a Data Analyst will build a traceability matrix, which tracks the data item from creation through to consumption.
Data Quality
In most companies, there will be teams (depending on their size) dedicated to this, and their input will be pivotal to existing and future data use.
Data quality is an important task that could impact internal and external reporting and a company’s ability to make decisions accurately.
Some of the areas that might be looked at include:
(A) Investigate duplicate data – There could be a number of reasons this has to be checked:
Data was manually entered multiple times.
An automated process ran multiple times.
A change to an IT system has unknowingly duplicated data.
(B) Finding errors – This could be completed in conjunction with the data reporting outlined below.
Normally companies will clearly have rules that pick up the data errors that are not expected.
A data analyst will analyse why these errors are occurring.
(C) Checking for missing data.
Data feeds have failed. A request to reload the data will be required.
Data that was not requested as part of the business requirements confirm that this is the case.
(D) Enhancing the data with additional information – Is there additional information that can be added that can enrich the dataset?
(E) Checking data is in the correct format – There are scenarios where this can go wrong, and an example is a date field is populated with text.
Data Reporting
In some of the areas above, we touched on the importance of the quality of data.
Ultimately there may be a need to track:
Data Quality – Build reports to capture the quality of data based on predefined business measurements.
Real-time Reporting – No new customers or customers who have left an organisation.
Track Targets – Is the target set by the business been met daily, weekly, or monthly?
Management Reporting – Build reports that provide input to management packs that provide an overview of how the business performs.
Data Testing
Organisations go through change projects where new data is being introduced or enhanced.
As a result, the data analyst will have a number of tasks to complete:
Write Test Scripts – Write all scripts for record counts, transformations and table-to-table comparisons.
Datatype Validation – Ensures all new data will be the same as the other data where it is stored.
No loss of data – Check all data is imported correctly with no data truncated.
Record count – Write an SQL script that would complete a source-to-the-destination reconciliation.
Data Transformation – Ensure any transformations are applied correctly.
Supporting data projects
Ad hoc projects are common, and sometimes become a priority for businesses as they deal with requirements that result as part of an immediate business need.
Data Analysts will be called upon to support projects where there is a need to ensure the data required is of a standard that meets the project deliverables:
Some common areas where this might occur include:
Extract data where it has been found to have been corrupted.
Investigate data changes, to analyse where a data breach may have occurred.
An external regulatory body has requested information to back up some reports submitted.
A customer has requested all the company’s information on them; usually the case for a GDPR request.
Do you want to validate with Tuples, that is easy, making changes not easy.
In our recent video Python – how do I remove unwanted characters lists were used as a lookup to validate data that we need to be check for invalid data items. The most apparent difference between the two is that tuples are immutable, hence changing their values is not possible, making using them in real-time code a bit hazardous.
So why would you use Tuples?
That is a good question and sometimes not too obvious when you try to put examples down on paper, but here are some cases:
You want a set of values that will never change, no matter what.
Use as a lookup that the program can check against, these could be called anywhere in your code.
Make sure that you only process what is in the tuple; any additional data can be reported as erroneous, a form of error control.
Getting around the change limitations (well kind of)
This video looks at a simple few steps to take in a set of data, validate the id column aginst a tuple set of values and then show the differences on a separate output.
The code is then rerun after we add the original tuple to the error values found, to give a new tuple. As a result, the new output will show up with no errors.
To sum it all up
In a nutshell, Tuples are limited in what they can do, probably the best thing for them is:
Use your code as a reference for re-occurring values that need to be validated.
Don’t use in your code to have updated tuples, use lists instead as you can update them in real-time.
Removal of unwanted errors in your data, the easy way.
The process of importing data can take many formats, but have you been looking for a video on how you do this? Even better are you looking for a video that shows you how to import a CSV file and then data cleanse it, effectively removing any unwanted characters?
As a follow up to Python – how do I remove unwanted characters, that video focused on data cleansing the data created within the code, this video runs through several options to open a CSV file, find the unwanted characters, remove the unwanted characters from the dataset and then return the cleansed data.
How to get in amongst the problem data:
The approach here looked at three different scenarios:
(A) Using a variable that is equal to an open function, and then reading the variable to a data frame.
(B)Using a “with statement” and an open function together, and returning the output to a data frame.
(C) Using read_csv to quickly and efficiently read in the CSV file and then cleanse the dataset.
Some minor bumps in the road that need some thought
There where some challenges with this that you will see in the video:
Options A & B had to deploy additional code just to get the data frame the way we wanted.
The additional lines of code made for more complexity if you were trying to manage it.
In the end, read_csv was probably the best way to approach this; it required less code and would have been easier to manage in the longer run.
As always thanks for watching, please subscribe to our YouTube channel, and follow us on social media!
Removing the unwanted, that is holding you up. A situation has arisen of you having information, which has erroneous data inside it, what do you do?
Data issues are a common scenario faced by many data analytics professionals and the industry as a whole. Data quality now has become more critical, especially as we move more processes online and the digital landscape increases.
Most pieces of data go through a process of been transferred somewhere between systems to be used or reports rely on the accuracy of them. If the data in the source system has quality issues, the problem if not addressed before going somewhere else can then push the data quality issues more throughout an organisation, like a spiders web it expands further.
The next step, looking to fix the problem and planning for it.
To combat this problem professionals need to come up with a plan on how to tackle this, either:
Fix at source
Take the data in before moving it on, and investigate the problems.
Reject the file or part thereof.
All three options above have scenarios around them with costs and implications, depending on the industry, you need to pick the most appropriate way to handle. As an example, in the banking industry payment files can sometimes have data in them that is rejected entirely or in part.
But the bank may decide they will only discard the records with the wrong data and process everything else.
How to go about it and how regular expressions can help
In this video, we look to go through an example of how to cleanse a data set;
(A) We use a list to check what problems we need to find.
(B) Using functions again to process through the data to find the problem and extract them.
(C) Regular expressions also appear as they look to find the special characters in the data set.
The concept of regular expressions is used extensively across several programming languages; it is a good way to test data and find erroneous values. If you are thinking about machine learning, it is quite important to get a more thorough knowledge of how they work. Here is a good link for further reading if you need more information Regular Expression how to
Thanks for watching and if you like, please share and subscribe through the buttons on this page!
Data Analytics Ireland
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy
Privacy Overview
This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
