pattern matching

Pattern matching is used to find repeat characters and or numbers that are in a specific format. As a result, you can use it within a data quality project.

Value Error 

ValueError: pattern contains no capture groups

admin 3669 Views 0 Comments , , , ,

Estimated reading time: 2 minutes

In Python, there are a number of re-occurring value errors that you will come across.

In this particular error it is usually related to when you are running regular expressions as part of a pattern search.

So how does the problem occur?

In the below, the aim of the code is to purely create a data frame, that can then be searchable.

To search the data frame we will use str.extract

import pandas as pd
rawdata = [['Joe', 'Jim'],
           ['Jane', 'Jennifer'],
           ['Ann','Alison']]
datavalue = pd.DataFrame(data=rawdata, columns=['A', 'B'])

We then add the below code to complete the extract of the string “Joe”.

a = datavalue['A'].str.extract('Joe')
print(a)

But it gives the below error, what we are trying to solve for:

ValueError: pattern contains no capture groups
Process finished with exit code 1

But why did the error occur , and how can we fix it?

In essence when you try to complete a str.extract, the value you are looking for should be enclosed in brackets i.e ()

In the above, it views ‘Joe’ as an incorrect value to be passed into the str.extract function, and returns the error.

So to fix this problem, we would change this line to:

a = datavalue['A'].str.extract('(Joe)')

As a result the program runs without error, and returns the below result:

     0
0  Joe
1  NaN
2  NaN

The full corrected code to be used is then:

import pandas as pd
rawdata = [['Joe', 'Jim'],
           ['Jane', 'Jennifer'],
           ['Ann','Alison']]
datavalue = pd.DataFrame(data=rawdata, columns=['A', 'B'])
a = datavalue['A'].str.extract('(Joe)')
print(a)
SQL SQLite strings 

How to use wildcards in SQL

admin 3213 Views 0 Comments , , , , ,

Estimated reading time: 6 minutes

Following on from from our previous posts on SQL, this post will help to explain how to use wildcards in your query.

What would you use a wild card in the first place?

When a data analyst dealing with a large dataset, it is most likely that they will not know every piece of data.

As a result data will come from multiple sources and will be in different formats.

In some instances when you select rows using certain values using SQL, this will work as you know the exact values.

Using SQL wild cards will aid the programmer in being able to get specific pieces of data that may cause data quality errors, but they may not know where the problem is, or what makes up the error.

So lets look at a data set and start to apply some of the logic above , to a practical example.

We are going to use SLlite again, below is the table we are going to run our query off.

As you will see we have three columns with data in it, the examples below will work off the “name” column.

Name has a number of data points that are quite similar, so lets start showing you how to actually use the wild card.

Filter the data for all values before the last value using %l

The output below is basically going to the name column only and asking it to return values , that have “l” at the end.

What the SQL is instructed to do is to look at each string, and where there is an “l” at the end, and characters before it, then return those records.

This is what using wildcards does, the % basically is saying give me any value before “l”, which has to be at the end.

As none of the values have “l” at the end it returns blank, which is correct.

If we rerun this , with %y, we get four values returned:

Filter the data for all values that start with A%

As a follow on from above say you want to find records that begin with A, but you don’t know what comes after the “A”?

Below, correctly it returns only three, and it is not concerned what comes afterwards.

Filter the data for all values where a “g” is in the middle?

In the above we looked at the start and end points of the string, and it return records that matched the criteria.

There maybe a scenario where you want to look for records, with a particular value that may be in the middle of the string.

In this example, we know that “g” occurs at the fourth position, so it will return all records where g is in that position, regardless of what is on either side.

In applying %%% it is basically saying return anything, if the fourth character which is g, irrespective of what is in the previous three characters.

Filter the data for all values where there is a space in the record

There are going to be records that have spaces in them, and sometimes that may or may not be wanted.

In order to find those records, we would apply the below wildcard in the SQL

Filter the data for all values start with an “H” and end with a “y”

In a dataset, you may want to find records that begin and end with specific values, but you are not sure or bothered what is in between.

Below we have changed the “%” for “_” in the query. This change allows us to ask for a start and end character.

Something to note, between the “H” and “y” there are five underscores (_) in there. Each one represents the no of values between the first and last character. If the string was only three letters long, then you would use one _ and so on.

Summary and conlusion

In this post, we have described what a wild card is and its uses. They are very handy for searching for a combination of value or values when you are not sure what else is in the string.

This is quite commonly used in pattern searching, and in data cleansing , most systems would incorporate it especially if automating tasks , it allows clean data to process without it coming to a stand still.

On our YouTube channel you can subscribe to find out more information about data cleansing, SQL and lots of different tips and techniques, below is the video for this post:

A list of wild card operators are as follows:

Wild cardDescription
%Either before or after a character, represents any character that could appear but is unknown.
_This is a single character of any value that may appear in a wildcard search, represents a space between characters.
^Inside brackets beside characters, tells the program to not return those characters.
–Inside a bracket and between characters, represents the range to be found of the characters it is in between.
[]If you place characters inside this bracket, it requests the program to return any of those characters in the output.

We have lots of posts on this website that will help you build your data analytics skills.

regular expressions strings 

how to remove spaces from a string

admin 2296 Views 0 Comments , , , ,

Have you encountered a problem where you have some spaces in a string that you do not need?

Data analytics can throw up lots of challenges we hope to help you solve this problem easily.

This problem can be quite common and can cause problems as follows:

  • Comparing strings does not work as expected.
  • Automated processes may fail as the presence of a space may make it fall over.

So lets look at some common ways to find these problems and then fix them.

Removing spaces at the start or end of the file using strip()

A common way to achieve this , will remove white spaces at the start and send of your string, BUT not in the middle.

This would be very helpful where you want to format some strings before loading them into a database, as the white spaces can cause the load to fail.

spacedstring = " Please remove my spaces! "
print("Before",spacedstring)
print("After",spacedstring.strip())

Output:
Before:  Please remove my spaces! 
After: Please remove my spaces!

Removing all spaces using str.replace()

Quite simply this removes all the white spaces in the string, resulting in all the characters form one long string.

The first part of the function looks to identify empty spaces, then the second part looks to remove it.

spacedstring = " Please remove my spaces! "
print(spacedstring.replace(" ", ""))

Output = Pleaseremovemyspaces!

Remove spaces using a regular expression

A regular expression is widely used across a number of languages , as a result is a very powerful way to search for pattern matching.

It looks to create a combination of characters that the computer programme understands and applies to a string.

The re.compile() method saves the regular expression for re use, which is know to be more efficient.

With re.sub() , it takes the string spacedstring, looks for “pattern” in it and removes it and replaces it with “”, which basically is nothing.

import re
spacedstring = " Please remove my spaces! "
pattern = re.compile(r'\s+')
sentence = re.sub(pattern, '', spacedstring)
print(sentence)

Output = Pleaseremovemyspaces!

Using the join and split function together

Using the below code has two steps:

(A)The split function() first of all splits all the words in a string into a list. By doing this it removes any spaces in the original string.

(B) the .join then pulls them all back together.

(C) Finally as you have “” at the start, it tells the program not to include any spaces between the words.

spacedstring = " Please remove my spaces! "
print("".join(spacedstring.split()))

Output = Pleaseremovemyspaces!

So there you have a number of different ways to remove spaces inside a string.

One final things to note about regular expressions covered above:

  • They are generally quite short in nature, less code needs to be written to implement.
  • When they were written, the performance was front and centre, as a result, they are very quick.
  • They work over a number of different languages, so no changes need to made.