How to group your data in Tableau

Estimated reading time: 3 minutes

Have you learnt how to connect to your data in Tableau and now want to understand how to group your Tableau data?

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.

What does a data analyst do?

Estimated reading time: 4 minutes

Livestream #2 – What does a data analyst do?

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.

It 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 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 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 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, 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 businses 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 includes:

  • 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.

Python Tutorial: Pandas groupby columns ( video 2)

Pandas groupby using column values

In this second video how to groupby using pandas and as part of expanding the data analytics information of this website, we are looking to explain how you can use a groupby selection but only using the column values and not the column names.

Below we import our data into a dataframe, and then group as follows:

  • Aggregate function
  • Using the cut function and assigning values to bins.
  • Assigning labels to the data frame output based on the bin values.

 

 

Why would you want to use Pandas groupby and column values?

This video looks to help understand the why going by values might be easier than column names:

  • Column names can change from project to project, using by values allows easy implementation of getting the output regardless of the names used.
  • You could apply this to any Python class, and as long as you can inherit will allow the code to run smoothly.
  • Implementing by value allows a clear understanding of the desired output as the values are clearly understood to generate what is required.
  • You need to understand how data within your data set falls within a particular cohort:
    • This use of values in different programs just needs to change, the underlying logic remains the same.
    • Using column names still means that to group them, the logic still needs to be written.

Regular expressions python

Estimated reading time: 3 minutes

Regular expressions explained

Regular expressions are a set of characters usually in a particular sequence that helps find a match/pattern for a specific piece of data in a dataset.

The purpose is to allow a uniform of set characters that can be reused multiple times, based on the requirements of the user, without having to build each time.

The patterns are similar to those that you would find in Perl.

How are regular expressions built?

To start, in regular expressions, there are metacharacters, which are characters that have a special meaning. Their values are as follows:

. ^ $ * + ? { } [ ] \ | ( )

.e = All occurrences which have one “e”, and value before that e. There can be multiple e, eg ..e means check two characters before e.

^ =Check if a string starts with a particular pattern.

*  = Match zero or more occurrences of a pattern, at least one of the characters can be found.

+ = Looks to match exact patterns, one or more times, and if they are not precisely equal, then nothing is returned.

? =Check if a string after ? exists in a pattern and returns it. If a value before the ? is directly beside the value after ? then returns both values.

—> e.g. t?e is the search pattern. “The” is the string. The result will return only the value e, but if the string is “te”, then it will return te, as the letters are directly beside each other.

da{2} = Check to see if a character has a set of other characters following it. E.g. sees if d has two “a” following it.

[abc] = These are the characters you are looking for in the data. Could also use [a-c] and will give you the same result. Change to uppercase to get only those with uppercase.

\ = Denoting a backslash used to escape all metacharacters, so if they need to be found in a string, they can be. Used to escape $ in a string so they can be found as a literal value.

| = This is used when you want an “or” operator in the logic, i.e. check for one or more values from a pattern, either or both can be present.

() = Looks to group pattern searches or a partial match, to see if they are together or not.

 

Special sequences, making it easier again

\a = Matches if the specified characters are at the start of the string been searched.

\b = Matches if the specified characters are at the beginning or the end of the string been searched.

\B = Matches if the specified characters are NOT at the beginning or the end of the string been searched.

\d = Matches any digits 0-9.

\D = Matches any character is not a digit.

\s = Matches where a string contains a whitespace character.

\S = Matches where a string contains a non-whitespace character.

\w = Matches if digits or character or _ found

\W = Matches if non-digits and or characters or _found

\z = matches if the specified characters are at the end of the string.

 

 

For further references and reading materials, please see the below websites, the last one is really useful in testing any regular expressions you would like to build:

See further reading material here: regular expression RE explained

Another complementary page to the link above regular expression REGEX explained

I found this link on the internet, and would thoroughly recommend you bookmark it. It will also allow you to play around with regular expressions and test them before you put into your code, a very recommended resource Testing regular expressions

 

What are the reserved keywords in Python

What are python reserved keywords?

When coding in the Python language there are particular python reserved words that the system uses, which cannot be accessed as a variable or a function as the computer program uses them to perform specific tasks.

When you try to use them, the system will block it and throws out an error. Running the below code in Python

import keyword
keywordlist = keyword.kwlist
print(keywordlist)

Produces the below keyword values
['False', 'None', 'True', 'and', 'as', 'assert', 'async', 'await', 'break', 'class', 'continue', 'def', 'del',
'elif', 'else', 'except', 'finally', 'for', 'from', 'global', 'if', 'import', 'in', 'is', 'lambda', 'nonlocal',
'not', 'or', 'pass', 'raise', 'return', 'try', 'while', 'with', 'yield']

When writing your code, it is important to follow the following guidelines:

(A) Research the keywords first for the language you are writing in.

(B) Ensure that your programming language highlights keywords when used, so you can fix the issue.

(C) Setup your computer program in debug mode to highlight keywords use.

With some programs running into thousands of lines of code, with additional functions and variables, it can become harder to spot the problem, so good rigour in the initial stages of coding will help down the road any issues that you may find that need to fixed.

This code was run in Python version 3.8

Python tutorial: Create an input box in Tkinter

Using an tkinter input box for your data projects

There may be an occasion as you are building out a data science or data analytics project, checks need to be performed on the dataset as follows:

  •  Big data sets and speed requirements in conjunction with
  • The need to reduce the volume of data returned which is impeding performance

and this is where input boxes and Tkinter can help!

In the below video, we are demonstrating an introduction to using an input box and validating the input.

We demonstrate how to validate the data entered into the tkinter input box and return a message, this will ensure the user gets the correct data.

Types of uses for a tkinter input box are varied, here are some thoughts:

  • Use an input box to return a set of data for a particular day.
  • Using them to filter down the results to a particular cohort of data.
  • Conduct a string search to find data quality issues to be fixed.

Python tutorial: How to create a graphical user interface in Tkinter

How would you like to present your data analytics work better?

When starting your data analytics projects, one of the critical considerations is how to present your results quickly and understandably?

Undoubtedly this is true if you are only going to look at the results yourself.

If the work you do is a repeatable process, a more robust longer-term solution needs to be applied, this is where Tkinter can help, which is a python graphical user interface.

When you are importing tkinter, some of the functionality that can be used include:

  • Use them to build calculators.
  • They can show graphs and bar charts.
  • Show graphics on a screen.
  • Validate user input, through building entry widgets.

Where this all fits in with data analytics?

While going through a set of data and getting some meaning to it can be challenging, using the python graphical user interface tutorial below can help build the screens that will allow a repeatable process to display in a meaningful way.

Using the tkinter widget could help achieve the following:

  • Build a screen that shows data analytics errors in a data set, e.g. The number of blank column values in a dataset.
  • Another application is to run your analytics to show the results on a screen that can be printed or exported.
  • Similarly, you could also have a screen where a user selects several parameters that are fed into the data analytics code and produces information for the user to analyse.

There are many more ways that you could do this, but one of the most important things is that data analytics can be built into a windows environment using Tkinter.

These GUI applications are what the user would be used to currently seeing. As a result, this could help to distribute a solution across an enterprise to lots of different users.

Also, another benefit is that they will work on many different operating systems.

The only thing that needs to happen is that the requirements the user needs are defined, and the developer then builds on those, with the data analytics code run in the background of this program with Tkinter and output into a user-friendly screen for review.

How to create a combobox in tkinter

Estimated reading time: 2 minutes

Here we have delivered a complimentary video on How to create a graphical user interface in Tkinter .

It will demonstrate how a Combobox can be used to select values and then validate the entry chosen.

Using a Combobox in the computer programming world has been around for some time.

It is a useful way to select from a choice and could in many ways in data analytics help as the following examples show:

  • Select a date to filter a data set down to values that are in the dataset.
  • Using matplotlib to plot data points in charts, you could have dynamic values that change the diagram based on values chosen from the Combobox.
  • Utilizing data analytics reports that the user accesses, the Combobox could be used to change the data shown dynamically to allow comparisons.
  • When looking to fix data quality issues, use the Combobox to select values for a date that needs to be fixed, apply the fixes on screen, and then save back to the database.

Developing a Tkinter GUI and the possibilities it brings

In this video, we use ttk, written to help split the behavior of code from the code implementing its appearance.

You can see plenty more on it here ttk information. This is a handy piece of functionality as styling an object can interfere with how it works.

We also have a function that helps with the validation. In the below, it accomplishes the following:

  • Allows the combobox value selected to be retrieved.
  • Validates the entry chosen in the combobox using an if statement.
def checkifireland ():
    x = combolist.get() # asssigns the value inside the combobox to x so it can be processed
    if x == "Ireland":
        messagebox.showinfo("Correct answer", "You will love it in Ireland")
    else:
        messagebox.showinfo("Incorrect answer", "You should visit Ireland first!")

The effectiveness is especially handy as it helps to ensure that the code returned from the Combobox to the function is correct

The below video will take through this step by step and explain the concepts discussed above.

The next steps

There are many informative Python – working with excel videos that are on our YouTube channel.

We are looking to bring them in and show them on a graphical user interface tutorial.

To see how to load a set of values into a combobox from an SQLite database table, have a look at how to load values into a combobox

how to remove unwanted characters from your data

Using r programming functions to cleanse data
In our recent post  Python Tutorial: How do I remove unwanted characters  we walked through the concepts behind data cleansing.

We demonstrated several different approaches to data cleansing, and the use of regular expressions was also shown.

Here we look at that approach using rstudio and the following functions:

How to approach data cleansing

In removing unwanted characters, you want to ensure that you have a defined list of what should not appear and will cause you errors. It is also essential to understand the type of data errors that can occur as follows:

  • Data entry errors
  • Data columns have the incorrect format, e.g. Telphone numbers which have non-numerical characters in them
  • Missing data that is required – e.g. null values
  • Data that does not make sense, e.g. date of birth that is beyond the range of what you would typically expect to see
  • Duplicate values for the same piece of data. The problem here is that this can inflate the no of data errors, and not give a true count of the actual errors.

In the below video we utilise r programming code using the above functions, but also use an if statement to check if an unwanted character is in the data set first before proceeding to remove it and return the cleansed data.

Some of the strategies to help counteract data errors could include:

  • Eliminate manual inputs
  • Controls at point of entry for data, e.g. for dates only allow date formats in the field.
  • Reduce duplication of the data across multiple systems, reduces the no of places that data differences can occur.
  • If integrating different systems with the same data into one network, perform a data cleanse beforehand, reduces the work needed afterwards to clean up the problems that brings.

 

Tkinter python tutorial

Estimated reading time: 3 minutes

Let’s make the introductions 🙂
Tkinter is a package that allows a programmer to build a GUI interface, which then can be opened on a computer screen by a user. There are many different types of GUI apps, but examples include a calculator or a text editor that opens when you click it.

Tkinter would be the most commonly used GUI package in Python, due to its simplicity, but PySimpleGUI, PYQt or PySide are other alternatives. Ensure you research these before using to make sure they suitable for your needs.

Why use Tkinter?

  • Relatively simple and easy to learn, upskilling is quick.
  • A great introduction to the concepts and ideas for building GUI apps, you will get a good grounding in the techniques and approaches needed.
  • Very well documented, so a programmer should be able to find the answer to anything specific they need to understand.

Now we are introduced, let’s see how to utilise it:

Install Python as usual, and make sure that Tkinter is working and you have the correct version. Note that import tkinter is for version 3.x, before that use import Tkinter

Please note that you will see in places when using Python code, that capitalization is important. This will sometimes puzzle you as to why some of your code does not work, usually, the interpreter should flag it for you.

While it is correct to put a capital at the start of a line, the programming language will ignore written English convention. An example is as follows:

list = ['a','b','c']
Print(list)
gives 
NameError: name 'Print' is not defined
whereas
list = ['a','b','c']
print(list)
gives ['a', 'b', 'c'] 
No errors

When saving your python script DO NOT call it tkinter.py as I did, the import statement will not work. Call it something like tkinter_test.py, see red arrow below.

At the start of the video below the code will look like this. the first six lines are the creation of the Tkinter screen its size and any buttons that will appear on it.

Note that all code should appear betwee line two and line six, so that the screen output works and looks correctly.

Added to this code in the following video:

  • Button – which will open our YouTube channel
  • An image
  • A clickable link – Which will bring you to our Home Page

A screenshot of the final output is as follows:

See a link to the Python documentation here Tkinter on python.org


Notice: ob_end_flush(): failed to send buffer of zlib output compression (0) in /home/dataana1/public_html/wp-includes/functions.php on line 4979