Create a HTML Table From Python Using Javascript

Estimated reading time: 5 minutes

So you are using Python and you have data stored in a data frame? You would like to present that on a webpage HTML table, but unsure how to achieve this. Well, read on to see two different methods that will help you.

Pass the data to Javascript which then passes to the HTML the data needed to create the table

In both methods, we are using Python Flask which has an app.py file and HTML files created to present the outcomes.

Step 1 – Read the data and create the data frame

For those familiar with Python Flask, we create the imports that allow us to create the webpage interface to present the data.

Then we create a list called data, which stored the information we need. After this, we create the data frame “df”.

Finally, we create a JSON file,and do some data cleansing.

from flask import Flask, render_template, json
import pandas as pd


app = Flask(__name__)

data = [['Joe','Dublin','100'],['Francois','Paris','100'],['Michael','Liverpool','100']]
df = pd.DataFrame(data, columns = ['Name', 'City','Age'])


json_output = df.to_json()
json_output = json.loads(json_output.replace("\'", '"')) # Removes back slash from JSON generated

Step 2 – Create the output HTML files

Method 1 – Here all this is doing is creating the function to create the webpage “index.html”. Note that name_json=json_output captures the data from step one, and this is what is passed over to the HTML page as a JSON.

In method 2 – We are using to_html which Renders a DataFrame as an HTML table, pure and simple.

As can be seen, it stores the data onto an HTML page that is stored in the templates folder.

#Method 1
@app.route('/')
def index():

    return render_template('index.html', name_json=json_output)

#This is  the start of method 2#
html = df.to_html()
#write html to file
im2_file = open("templates/index_method2.html", "w")
im2_file.write(html)
im2_file.close()

@app.route('/index_method2')
def index_method2():
    return render_template('index_method2.html')


if __name__ == "__main__":
    app.run(debug=True)

Step 3 – Create the HTML tables through javascript

So steps 1 and 2 were getting the data ready so it can be viewed on the web pages, so how are they built?

So let’s walk down through the code, note that a good bit of this is the HTML that is used to present the data on the page.

The first thing to notice is the <style></style> tags. this is for method 1 and applies the boxes around the output.

<pre id=”json”></pre> – This line shows the JSON data as follows:

JSON Output

In the below few lines this is the HTML that creates the table to store the data from method 1:

<table id="json_table"> ===> Method 1 table
    <tr>
        <td> Name</td>
        <td> City</td>
        <td> Age</td>
  </tr>
</table>

The next section has the Javascript that will populate both for method 1 and method 2, I will go through it now:

So the first line is creating a variable that references the method 1 table, and where the JSON data will be loaded to.

The second line is converting the JSON into a format that can be read and shown as the “JSON” screenshot above.

In Line 3 & 4, all we are doing here is creating a variable to store the output of the loop that follows in the subsequent lines.

The final set of lines in the script except for the very last line ( which relates to Method 2) catch the data that is captured as part of the loop and feed it to the table that appears on index.html, as follows:

HTML table created from a python data frame using Javascript.

The final line:

window.open(‘http://127.0.0.1:5000/index_method2’, ‘_blank’);

In the Javascript section, this relates to Method 2 and takes the data directly from the app.py file and outputs it to http://127.0.0.1:5000/index_method2.html

This is a very quick and easy way to create the HTML table, and as can be seen, involves less coding than method 1.

<script>
    var selectvalue = document.getElementById("json_table"), test={{ name_json | tojson }}; ===> First line
    document.getElementById("json").textContent = JSON.stringify(test, undefined, 2); ===> Second line

    const keys = Object.keys(test); ===> Line 3
    for (let i = -1; i < keys.length-1; i) ===> Line 4

    {
        const key = keys[i++];

        console.log(key, test[key]);

        a = JSON.stringify(test["Name"][i])
        b = JSON.stringify(test["City"][i])
        c = JSON.stringify(test["Age"][i])
        const tbl = document.getElementById("json_table");
        const row = tbl.insertRow();
        const cell1 = row.insertCell();
        const cell2 = row.insertCell();
        const cell3 = row.insertCell();
        cell1.innerHTML = a;
        cell2.innerHTML = b;
        cell3.innerHTML = c;

    }

    window.open('http://127.0.0.1:5000/index_method2', '_blank');

  </script>

The Full HTML code

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">

  <title>Document</title>
</head>
<style>
table, td {
  border: 1px solid;
}
</style>
<body>
<pre id="json"></pre>
<div>Method 1 pass the data via JSON to Javascript </div>
<table id="json_table">
    <tr>
        <td> Name</td>
        <td> City</td>
        <td> Age</td>
  </tr>
</table>

  <script>
    var selectvalue = document.getElementById("json_table"), test={{ name_json | tojson }};
    document.getElementById("json").textContent = JSON.stringify(test, undefined, 2);

    const keys = Object.keys(test);
    for (let i = -1; i < keys.length-1; i)

    {
        const key = keys[i++];

        console.log(key, test[key]);

        a = JSON.stringify(test["Name"][i])
        b = JSON.stringify(test["City"][i])
        c = JSON.stringify(test["Age"][i])
        const tbl = document.getElementById("json_table");
        const row = tbl.insertRow();
        const cell1 = row.insertCell();
        const cell2 = row.insertCell();
        const cell3 = row.insertCell();
        cell1.innerHTML = a;
        cell2.innerHTML = b;
        cell3.innerHTML = c;

    }

    window.open('http://127.0.0.1:5000/index_method2', '_blank');

  </script>


</body>
</html>

#how to create a html table from a python dataframe using javascript

The Final output

Method 1
Method 2

How To Check For Unwanted Characters Using Loops With Python

Estimated reading time: 3 minutes

On this website, we have posted about how to remove unwanted characters from your data and How to remove characters from an imported CSV file both will show you different approaches.

In this blog posting, we are going to approach the process by using loops within a function. Essentially we are going to pass a list and then we are going to loop through the strings to check the data against it.

Step 1 – Import the data and create a data frame

The first bit of work we need to complete is to load the data. Here we create a dictionary with their respective key-value pairs.

In order to prepare the data to be processed through the function in step 2, we then load it into a data frame.

import pandas as pd
#Create a dataset locally
data = {'Number':  ["00&00$000", '111$11111','2222€2222','3333333*33','444444444£','555/55555','666666@666666'],
        'Error' : ['0','0','0','0','0','0','0']}

#Create a dataframe and tell it where to get its values from
df = pd.DataFrame (data, columns = ['Number','Error'])

Step 2 – Create the function that checks for invalid data

This is the main piece of logic that gives the output. As you can see there is a list “L” that is fed to the function run.

One thing to note is that *l is passed to the function, as there is more than one value in the list, otherwise the program would not execute properly.

To start off we create a data frame, which extracts using a regular expression the characters we don’t want to have.

Next, we then need to drop a column that is generated with NAN values, as these are not required.

Then we updated the original data fame with the values that we found.

Just in case if there are any NAN values in this updated column “Error”, we remove them on the next line.

The main next is the loop that creates a new column called “Fix”. This holds the values that will be populated into the fix after the data we don’t want is removed and is data cleansed.

The data we do not need is in str.replace.

#Function to loop through the dataset and see if any of the list values below are found and replace them
def run(*l):
    #This line extracts all the special characters into a new column
    #Using regular expressions it finds values that should not appear
    df2 = df['Number'].str.extract('(\D+|^(0)$)') # New dataframe to get extraction we need
    print(df2)
    df2 = df2.drop(1, axis=1) # Drops the column with NAN in it, not required

    df['Error'] = df2[0] # Updates original dataframe with values that need to be removed.
    #This line removes anything with a null value
    df.dropna(subset=['Error'], inplace=True)
    #This line reads in the list and assigns it a value i, to each element of the list.
    #Each i value assigned also assigns an index number to the list value.
    #The index value is then used to check whether the value associated with it is in the df['Number'] column 
    #and then replaces if found
    for i in l:
        df['Fix']= df['Number'].str.replace(i[0],"").str.replace(i[1],"").str.replace(i[2],"").str.replace(i[3],"") \
        .str.replace(i[4],"").str.replace(i[5],"").str.replace(i[6],"")
        print("Error list to check against")
        print(i[0])
        print(i[1])
        print(i[2])
        print(i[3])
        print(i[4])
        print(i[5])
        print(i[6])
    print(df)

#This is the list of errors you want to check for
l = ['$','*','€','&','£','/','@']

Step 3 – Run the program

To run this program, we just execute the below code. All this does is read in the list “L” to the function “run” and then the output in step 4 is produced

run(l)

Step 4 – Output

Error list to check against
$
*
€
&
£
/
@
          Number Error           Fix
0      00&00$000     &       0000000
1      111$11111     $      11111111
2      2222€2222     €      22222222
3     3333333*33     *     333333333
4     444444444£     £     444444444
5      555/55555     /      55555555
6  666666@666666     @  666666666666

Process finished with exit code 0

How To Run Python Validation From Javascript

Estimated reading time: 6 minutes

More and more, website developers are looking to blend different programming technologies to allow them to use functionality, that maybe is not available to them in the current programming language they utilise.

In previous posts how to pass python variables to Javascript and how to run python directly from javascript, we touched on how to use Python and Javascript interchangeably to pass data.

Here in this blog post, we are going to look at data validation using Python, with data captured by Javascript. The purpose here is to allow another way to validate data other than relying on javascript.

Python Validation run from Javascript

There are a number of reasons for this:

  1. You may want to use Python to process the data and provide output.
  2. The data could be passed from Python into a database, you need to run some checks before you proceed.
  3. Python is your core language, you only use Javascript to capture the data and pass it from a website.

There are four validation checks

  1. Validation if a username exists.
  2. Validation if a number has been entered on a username field.
  3. Validation of a password entered.
  4. A validation check to make sure that an email address entered is in the correct format.

We have two files with the requisite code in them as follows:

  1. app.py ===> As this application is built in Python Flask, this file holds the logic to open the website files, and it holds the Python validation checks.
  2. index.html ====> This file holds the HTML that creates the pages, labels, and input boxes that appear in the web browser. It also includes the Javascript that will capture the data to be passed to Python for validation.

Code Overview – App.PY

Below, I will look to explain what is going on as follows:

The variable Regex is used in the email validation to check if the email entered is correct.

def inputcheck ===> This is just checking the username passed over and performing some validations on it.

def inputvalidation ====> This also is checking the username but looking at if numbers only are entered, or if it is empty.

def passvalidation ====> In this piece of logic, it checks if the password is empty, less than five characters, is numbers only.

def emailvalidation ====> All this is doing is checking if the data received from the Javascript is in the correct email format. It references the regex variable above, which is used to confirm if the format is correct or otherwise.

Within each of the functions, there are if statements which are the core validation checks used by Python.

Also, all the popup menus use ctypes, which allows you to access the windows libraries that hold message boxes, customise them and call within your program.

import json
import ctypes
import re
from flask import request,redirect,url_for

from flask import Flask, render_template

app = Flask(__name__)

regex = '^[a-z0-9]+[\._]?[a-z0-9]+[@]\w+[.]\w{2,3}$'

@app.route('/')
def index():
    return render_template('index.html')


@app.route('/inputcheck', methods=['GET','POST'])
def inputcheck():
    output = request.get_json()
    result = json.loads(output) #this converts the json output to a python dictionary
    username_list = ['joe','john']
    if result['username'] in username_list:
        MessageBox = ctypes.windll.user32.MessageBoxW(0, 'Username ' + result['username'] + ' ' + 'exists', "Username check", 0x00001000)
        return render_template('various.html')
    elif result['username'] == '':
        MessageBox = ctypes.windll.user32.MessageBoxW(None, 'You cannot enter an empty value', 'Username check', 0x00001000)
    else:
        MessageBox = ctypes.windll.user32.MessageBoxW(None, 'Username ' + result['username'] + ' ' + 'does not exist', 'Username check', 0x00001000)

@app.route('/inputvalidation', methods=['GET','POST'])
def inputvalidation():
    output = request.get_json()
    result = json.loads(output) #this converts the json output to a python dictionary
    if result['username'].isdecimal():
        MessageBox = ctypes.windll.user32.MessageBoxW(None, 'Your username cannot be numbers',"Number check", 0x00001000)
    elif result['username'] == '':
        MessageBox = ctypes.windll.user32.MessageBoxW(None, 'The username cannot be empty', "Number check",0x00001000)
    else:
        MessageBox = ctypes.windll.user32.MessageBoxW(None, 'Your username looks ok', "Number check", 0x00001000)
    return render_template('index.html')

@app.route('/passvalidation', methods=['GET','POST'])
def passvalidation():
    output = request.get_json()
    result = json.loads(output) #this converts the json output to a python dictionary
    if result['password'].isdecimal():
        MessageBox = ctypes.windll.user32.MessageBoxW(None, 'Your password cannot be numbers',"Password check", 0x00001000)
    elif result['password'] == '':
        MessageBox = ctypes.windll.user32.MessageBoxW(None, 'The password cannot be empty', "Password empty check",0x00001000)
    elif len(result['password']) < 5:
        MessageBox = ctypes.windll.user32.MessageBoxW(None, 'Your username should be greater than five characters', "Password length check", 0x00001000)
    else:
        MessageBox = ctypes.windll.user32.MessageBoxW(None, 'Your password looks ok', "Number check", 0x00001000)
    return render_template('index.html')

@app.route('/emailvalidation', methods=['GET','POST'])
def emailvalidation():
    output = request.get_json()
    result = json.loads(output) #this converts the json output to a python dictionary
    if re.search(regex, result['email']):
        MessageBox = ctypes.windll.user32.MessageBoxW(None, 'Your email is in the correct format', "Email check", 0x00001000)
    else:
        MessageBox = ctypes.windll.user32.MessageBoxW(None, 'Your email is invalid, please correct', "Email check", 0x00001000)
    return render_template('index.html')



if __name__ == "__main__":
    app.run(debug=True)


Code Overview – Index.html

The below code is the page code, that allows the capturing of data through a web browser HTML page.

Between the <style></style> tags these are the CSS properties that format the labels and buttons on the page.

Within the <div></div> tags are where we create the labels and buttons to show on the screen. Also within these, are the references to the on click event function that should run once the buttons are clicked.

Within the <script></script> tags, this is where the javascript is written which captures the data entered into the input boxes within the <div> tags.

Also, you will see within this javascript, there are sections called $.ajax, which is where the data captured by the javascript is stored, and then passed onto the Python script file (app.py)

Note that in each ajax , url:”/APAGENAME” is referenced. These are the sections within the app.py that the data is passed to, and then the Python logic kicks in and validates the data.

<html lang="en">

<head>

    <title>Data Analytics Ireland</title></head>
<style>
.button1 {
    position: absolute;
    top: 50%;
    left: 55%;
    width: 900px;
    height: 300px;
    margin-left: -300px;
    margin-top: -80px;
}



.labels {
    position: absolute;
    top: 50%;
    left: 55%;
    width: 900px;
    height: 300px;
    margin-left: -300px;
    margin-top: -150px;
}

</style>

<body>



<div class="labels" id="mem1" >
<label  for="username">username:</label> <input type="text" id="username" name="username">
<label for="password">password:</label><input type="text" id="password" name="password">
    <label for="email">email:</label><input type="text" id="email" name="password">
</div>


<div class="button1" id="mem2" >
<button type="submit" onclick='myfunction();'>Click here to validate your username</button>
<button type="submit" onclick='myfunctionval();'>Click here to check if a number</button>
    <button type="submit" onclick='myfunctionpass();'>Click here to check password</button>
      <button type="submit" onclick='myfunctionemail();'>Click here to check your email</button>
</div>


<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.5/jquery.min.js"></script>

<script>
    function myfunction() {

        const username = document.getElementById("username").value;
        const password = document.getElementById("password").value;


        const dict_values = {username, password} //Pass the javascript variables to a dictionary.
        const s = JSON.stringify(dict_values); // Stringify converts a JavaScript object or value to JSON.
        console.log(s); // Prints the variables to console window, which are in the JSON format
        //window.alert(s)
        $.ajax({
            url:"/inputcheck",
            type:"POST",
            contentType: "application/json",
            data: JSON.stringify(s)});
}
</script>
<script>
    function myfunctionval() {

        const username = document.getElementById("username").value;
        const password = document.getElementById("password").value;


        const dict_values = {username, password} //Pass the javascript variables to a dictionary.
        const s = JSON.stringify(dict_values); // Stringify converts a JavaScript object or value to JSON.
        console.log(s); // Prints the variables to console window, which are in the JSON format
        //window.alert(s)
        $.ajax({
            url:"/inputvalidation",
            type:"POST",
            contentType: "application/json",
            data: JSON.stringify(s)});
}
</script>

<script>
    function myfunctionpass() {

        const username = document.getElementById("username").value;
        const password = document.getElementById("password").value;


        const dict_values = {username, password} //Pass the javascript variables to a dictionary.
        const s = JSON.stringify(dict_values); // Stringify converts a JavaScript object or value to JSON.
        console.log(s); // Prints the variables to console window, which are in the JSON format
        //window.alert(s)
        $.ajax({
            url:"/passvalidation",
            type:"POST",
            contentType: "application/json",
            data: JSON.stringify(s)});
}
</script>

<script>
    function myfunctionemail() {

        const email = document.getElementById("email").value;


        const dict_values = {email} //Pass the javascript variables to a dictionary.
        const s = JSON.stringify(dict_values); // Stringify converts a JavaScript object or value to JSON.
        console.log(s); // Prints the variables to console window, which are in the JSON format
        //window.alert(s)
        $.ajax({
            url:"/emailvalidation",
            type:"POST",
            contentType: "application/json",
            data: JSON.stringify(s)});
}
</script>




</body>
</html>

How to pass multiple lists to a function and compare

Estimated reading time: 3 minutes

Have you ever been faced with a situation where you have multiple lists you want to compare, and need a function that will read them in and show you what is different between them all?

In this scenario, this can be handy where you have large data sets and need a quick way to find those differences and fix them where appropriate.

Comparing two lists

Let’s look and see what is going on below.

First of all, we have defined two lists. The only difference between the two is that one has a value of six, the other does not.

Next, we have the function “comparelists”. What this is doing is taking in the two lists as parameters (a,b), and then processing them.

The lists are passed as arguments to the parameters in this line ===> comparelists(list1,list2)

The parameters a are assigned to list1, and b is assigned to list2.

The main bit of the function is the list comprehension, and it is doing the following:

  1. x for x is basically creating a variable x, and starting a loop that goes through all the values of b.
  2. Each iteration of x is stored and compared with a.
  3. “If x not in a” completes the comparison and if true returns the value, otherwise moves to the next value.

As a result of this logic, it can be seen that six is the only value returned, and this is what we are expecting based on a visual inspection.

#Reading in two lists
list1 = [1,2,3,4,5] # parameter a below
list2 = [1,2,3,4,5,6] # parameter b below

def comparelists(a,b):
    z = [x for x in b if x not in a] #list comprehension
    print(z)

comparelists(list1,list2)

Output:

[6]

Comparing more than two lists

In the logic above, we saw that two lists can be compared. But what if you want to compare one list against two other lists?

That is easily done, with some tweaking to the code.

As before the three lists are defined, created as arguments in comparelists(list1,list2,list3), and then passed to the function parameters a,b,c.

The only difference in this logic is that the list comprehension is written a little different as follows:

  1. x for x is basically creating a variable x, and starting a loop that goes through all the values of b. ====> This is the same as above
  2. Each iteration of x is stored and compared with a and c ===> This line is different as comparing to two lists now.
  3. “if x not in a and x not in c” ====> Here we have two comparisons
    • Checking for the value x from b in a
    • Checking for the value x from b in c
  4. The value of six is correct as it is not in either a or c.
  5. Note the if statement is specific as to what to check for with the “and” statement. This can be changed to suit your needs.
list1 = [1,2,3,4,5] # parameter a below
list2 = [1,2,3,4,5,6] # parameter b below
list3 = [1,2,3,7,8,9] # parameter c below

def comparelists(a,b,c):
    z = [x for x in b
         if x not in a and x not in c] #list comprehension
    print(z)

comparelists(list1,list2,list3)

Output:
[6]

How to use parameters in Python

Estimated reading time: 5 minutes

Are you using a function and looking to pass values into it to be processed? Well if this is the case you are trying to pass arguments to that function’s parameters so they will return a value for you.

Parameters are just values that are associated with the function. For each function, you are going to have one or more parameters it is expecting to process.

As a result, there are a number of ways they are created, let’s explore how this can be achieved.

Passing arguments directly into a function parameters

In a lot of instances, the argument values to be processed are going to be passed into the function parameters, which then executes some logic and returns a value.

Typical ways this may happen:

  1. A password is entered ( the inputted argument), the function then confirms if it is valid or not.
  2. You may need to check if a value entered ( the inputted argument) is above or below a certain value. It is passed to a function parameter and then the function does the necessary checks, and confirms the result in the output.

The above are two examples, but let’s see this in action.

Below we have a function. The purpose is to take in two arguments passed to it and conduct a calculation on them.

The calculation happens in the variable number 1 or number 2.

Variable number 1 and variable number 2 get their values from parameters a and b respectively.

The line multiplenumber(1,2) is just calling the function and passing the arguments to the parameters a,b which then processes them and the output is printed.

def multiplenumber(a,b):
    number1 = a*2
    number2 = b*3
    print(number1,number2) #===> These values are different to the variables below as they happen as a result of the processing inside the function.

number1=1
number2=2
multiplenumber(number1,number2)
print(number1,number2) #===> These values are outside the function and do not have any relationship to the variables inside the function above, hence are different.

Result:
2 6
1 2

The result of the above piece of logic is called “passing by value“, which essentially means the data is passed from the variables ( number 1 and Number 2) to the function parameters, but when the function is completed processing, it does not impact the original variables outside the function.

This is because the original variables are immutable.

Passing arguments directly into function parameters from a list

In the above example, as outlined, there will be no impact on the variables that pass the arguments into the function, they remain the same.

On the other hand, if the original source of the arguments is a mutable object, it will have its values changed.

Let’s look at a situation where this may occur.

def addnumber(a):
    number=['3']
    a.append(number)
    print(a) #===> This prints the original value of a with the addition of the value 3

number1=[1,2]
addnumber(number1)
print(number1) #===> As number1 is passed to the function, and number1 is allowed to be changed then this will print out the new list value once the function has completed processing.

Result:
[1, 2, ['3']]
[1, 2, ['3']]

So in this instance, as number1 is a list, and this is mutable, its value will change once the function completes its processing.

This can also be called “passing by reference“.

Passing arguments between functions

In another video, we completed called how to pass data between functions this discusses how you can pass the data between two functions and print it out.

Effectively in the below the arguments are name, country, city, and these values are passed to functionb

def functiona():
    name = "Joseph"
    country = "Mars"
    city = "atlantis"
    #this captures the three variables above
    return name,country,city
functiona()

def functionb():
    myname,mycountry,mycity = functiona()
    print(myname)
    print(mycountry)
    print(mycity)

functionb()

Result:
Joseph
Mars
atlantis

Passing arguments using *args as the parameter

In order to understand this, before we look at some code we need to understand what are *args?

*args essentially allows you to pass multiple positional arguments to a function, and this can vary over time.

As a result, you don’t have to assign the positional arguments to more than one variable. The *args take in all the arguments, no matter how long or short they are and then return a value.

def addnumbers(*args):
    result = 2
    for x in args:
        result = result * x
    return result

print(addnumbers(1,2,3))

Result:
12

Passing arguments using **kwargs as the parameter

In contrast to the above, this will process a set of values that have a name attached to them.

As can be seen in the previous example the arguments passed had no name attached to them.

Let’s look closely at an example. In this scenario, the arguments are assigned a value of a,b,c.

def addnumbers(**kwargs):
    result = 2
    for x in kwargs.values():
        result = result * x
    return result

print(addnumbers(a=1,b=2,c=3))

Result:
12

Here it is clear that the logic takes in each value of a,b,c processes it and then returns the result required.

In essence, both *args and **kwargs give you the same answer, it just depends on other parts of your code and how it is processing your data.

For example, in the **kwargs it could be that the values of a,b,c could be populated by other variables within the program.

So in that scenario, the variable that has been passed on can change and not be static.

So in summary:

a. Arguments can be passed multiple ways to functions, the parameters then just process them.

b. Parameters also don’t have to be assigned to a variable, they can be read in through *args.

c. On the other hand you can have multiple arguments read in at once by using the **kwargs.

How to Automate Testing With Python | unittest

Estimated reading time: 5 minutes

So you are working with large amounts of data, and the data is having transformations applied to it or quite simply you need to see as you move data, has it changed in any way.

The question that arises is if I have lots of validations to complete, how can I quickly see if they pass or fail?

Here we will take you through unittest, a Python testing framework that will enable a quick way to validate data you are working with.

Step 1 – Define your test scenarios

Like in any testing, the first step is to define what you want to test and what is your expected outcome. Here we are testing data that was transformed into a different value.

The test then looks at the new values and compares them to a list of values that are expected in the output.

As can be seen, the above two rows will be what we will use to create our test cases, which will be outlined below.

While the below may seem very strict, they emphasise exactly what we are testing for. Either all data passes the test or it does not pass.

In reality, these rules can be amended/relaxed depending on what you need to test for, this is purely for illustrative purposes:

Step 2 – Create our python file that will pass in the transformed data

This is the first file we need, it will be the source file that will be used to transform the data and then pass to the testing module later on.

In this instance, it is called “automate_testing_python.py”

I have created a dictionary here, but you can read in a CSV file or any file that you wish. Just make sure that it is passed to the data frame.

import pandas as pd

data = {'Name_before':['Joe','Liz','Pat'],'Name_after':['Joseph','Elizabeth','Patrick']} #===> Creating a dictionary

df = pd.DataFrame(data) #===> Creating a dataframe


def main():

    transfromations_applied = data['Name_after'] #===> Defines the column you want to test against
    return transfromations_applied #===> Returns the values that will be used in the automation script

The function “main” above defines clearly what column holds the data to be passed to the automated test, in this instance ” transfromations_applied “

Step3 – Create a test script file

In the below there are a number of steps to follows:

  1. Install the package HTMLTestrunner in whatever solution you are using.
  2. Install the Unittest library to the same program. This will perform the tests asked and provide the result outputs.
  3. Next we are importing what was returned from “main” function in the file “automate_testing_python.py”
  4. The next step is the creation of the class, this serves the following purposes:
    1. Defines the exact tests we want to carry out.
    2. In the first method, it clearly defines what values are expected and what should be tested against.
    3. The second method is purely checking that all the values are alphabetic.
  5. The last line is running a file that will run the tests and then return the values to the output html file.
from HTMLTestRunner import HTMLTestRunner
import unittest

from automate_testing_python import main #===> Imports the data that was returned


#This is the code that performs the tests
#Each method is a specific test
class test_values(unittest.TestCase):


def test_ValueEquals(self):

ExpectedValues = ['Joseph','Elizabeth','Patrick']
values_received = main()
self.assertListEqual(ExpectedValues,values_received)

def test_Valueisalphabetic(self):

values_received = main()
for c in values_received:
self.assertTrue(c.isalpha())



if __name__ == "__main__":
HTMLTestRunner.main()

Step 4 – Create the File that will generate the output HTML file with the results.

As a result of the above steps we have created the data, brought it in to test against predefined test scenarios.

Now, all we have to do is present the findings in an HTML file.

In this instance, the output will be easier to understand, and quicker to digest.

In the below code, it follows the below steps:

(A) The first and second lines import the libraries we will need to run this bit of code.

(B) The third line is receiving the values that were generated from the class in step 3.

(C) All the next four lines do, is pull in the data, attach it to the myreport.html file

(D) The last two lines do the following:

  1. Tell the program to take the data received and then use the HTMLTestRunner package to process it, and create the template output.
from HTMLTestRunner import HTMLTestRunner
import unittest
from test_automate_testing_python import test_values

if __name__=='__main__':
    suite=unittest.makeSuite(test_values)
    filename='C:\\Users\haugh\OneDrive\dataanalyticsireland\YOUTUBE\how_to_automate_testing_with_python\myreport.html'
    fp=open(filename,'w')
    runner=HTMLTestRunner.HTMLTestRunner(fp,title=u'Data Analytics Ireland Test Output',description=u'This shows the testing output result')
    runner.run(suite)

Step 5 – Run the file html_report.py

Running this file will also open and run the code in the other files referenced in this post.

As a result, when the program is finished the output will look like this:

Earlier in the program we created it a dictionary (STEP 1) like this:

data = {‘Name_before’:[‘Joe’,’Liz’,’Pat’],’Name_after’:[‘Joseph’,’Elizabeth’,’Patrick’]} #===> Creating a dictionary

and then in Step2 in our class “test_values” we defined the expected outputs.

def test_ValueEquals(self): ExpectedValues = [‘Joseph’,‘Elizabeth’,‘Patrick’]

In this instance, the values are the same, the above output will mark the two tests as a Pass.

But if the values passed in are:

data = {‘Name_before’:[‘Joe’,’Liz’,’Pat’],’Name_after’:[‘Joseph’,’Elizabeth’,‘Patrick1’]} #===> Creating a dictionary

and are compared to

def test_ValueEquals(self): ExpectedValues = [‘Joseph’,‘Elizabeth’,‘Patrick’]

The following output will appear, as the two tests will fail:

So we have created two test cases here, but we could add loads more and then have the automation quickly return the test results for us.

How to Add Formulas to Excel using Python

Estimated reading time: 3 minutes

You may be working on automating some exports to Excel using Python to compare files or just pure and simple adding formulas to an Excel file before you open it up.

Here we explain adding formulas to your Excel output using Numpy or adding the calculations to specific cells in the output.

Adding formulas to specific cells

First of all, let’s look at the normal spreadsheet with some calculations, these have the formulas typed in. The ultimate objective is to have the Python code do this for us, one less step.

As can be seen, the cells have the formulas in them, but this would be a very time-consuming process if you had to do it multiple times, in multiple spreadsheets.

To get around this we can write the Python logic as follows:

  1. Create three lists and three dataframes as follows.
datasetA_list = np.array([1,2,3,4,5,6,7,8,9,10])

datasetB_list = np.array([9,8,65,43,3,21,3,2,1,7])

dataset_list = ('sum','average','median','standard deviation','count','correlation')


datasetA = pd.DataFrame(datasetA_list,columns=['ValueA'])
datasetB = pd.DataFrame(datasetB_list,columns=['ValueB'])
dataset_list_calcs = pd.DataFrame(dataset_list, columns=['Calcs'])

2. Next create a path to where you are going to store the data as follows:

path = 'output.xlsx'

3. In this next step create the workbook and location where the data will be stored. This will load the headings created in step 1 to a particular location on the spreadsheet.

workbook = pd.ExcelWriter(path, engine='openpyxl')
workbook.book = load_workbook(path)
workbook.sheets = dict((ws.title,ws) for ws in workbook.book.worksheets)

datasetA.to_excel(workbook,sheet_name="Sheet1", startrow=1,index=False, header=True,)
datasetB.to_excel(workbook,sheet_name="Sheet1", startrow=1, startcol=2,index=False, header=True)
dataset_list_calcs.to_excel(workbook,sheet_name="Sheet1", startrow=1, startcol=4,index=False, header=True)

4. Load the formulas into cells besides their relevant headings. This should line post these formulas beside the relevant heading created in step 1.

###Creating calculations for datasetA

sheet = workbook.sheets['Sheet1']
sheet['E2'] = 'CalcsA'
sheet['F3'] = '=SUM(A3:A12)'
sheet['F4'] = '=AVERAGE(A3:A12)'
sheet['F5'] = '=MEDIAN(A3:A12)'
sheet['F6'] = '=STDEV(A3:A12)'
sheet['F7'] = '=COUNT(A3:A12)'
sheet['F8'] = '=CORREL(A3:A12,C3:C12)'

###Creating calculations for datasetB

sheet = workbook.sheets['Sheet1']
sheet['H2'] = 'CalcsB'
sheet['H3'] = '=SUM(C3:C12)'
sheet['H4'] = '=AVERAGE(C3:C12)'
sheet['H5'] = '=MEDIAN(C3:C12)'
sheet['H6'] = '=STDEV(C3:C12)'
sheet['H7'] = '=COUNT(C3:C12)'
sheet['H8'] = '=CORREL(A3:A12,C3:C12)'

Use Numpy to create the calculations

a. Create the calculations that you will populate into the spreadsheet, using Numpy

a = np.sum(datasetA_list)
b = np.average(datasetA_list)
c = np.median(datasetA_list)
d = np.std(datasetA_list,ddof=1) ## Setting DDOF = 0 will give a differnt figure, this corrects to match the output.
f = np.count_nonzero(datasetA_list)
g = np.corrcoef(datasetA_list,datasetB_list)

b. Create the headings and assign them to particular cells

sheet['E14'] = 'Numpy Calculations'
sheet['E15'] = 'Sum'
sheet['E16'] = 'Average'
sheet['E17'] = 'Median'
sheet['E18'] = 'Standard Deviation'
sheet['E19'] = 'Count'
sheet['E20'] = 'Correlation'

c. Assign the variables in step a to a set of cells

sheet['F15'] = a
sheet['F16'] = b
sheet['F17'] = c
sheet['F18'] = d
sheet['F19'] = f
sheet['F20'] = str(g)

d. Save the workbook and close it – This step is important, and always include.

workbook.save()
workbook.close()

And the final output looks like…

TypeError: the first argument must be callable

Estimated reading time: 3 minutes

So you may be using Python Classes and have encountered the problem TypeError: First Argument Must be Callable. So what does the problem mean and how can you fix it?

In this article, we are looking to explain how it may occur and the easy fix you can apply to stop the problem in the future.

Let us understand calling Functions/Methods first

Normally in a computer python program, you will have a need to use a function/method as it has the functionality that will save you time, and can continuously be reused.

A classic example is print(“Hello”) that handles all the required logic to show this on a screen. Its output is quickly viewable, and there is not any need to understand what is going on in the background.

Here in this example we have run the function on its own, as a result it does not need to pointed to somehwhere else to run the logic it contains.

For the error we are looking to resolve, this is part of the problem. A function/method can be run on its own, or from within a Class.

The defining differene is that on its own it needs the parenthensis i.e. (), but if if you calling it from within a class, then it has to be handled differently.

Seeing the Type Error First Argument must be callable and fixing it

In the below we have a block of code, that produces the error we are trying to fix.

Following on from what was discussed above, the offending line is highlighted.

In particular the problem lies with printprogress() . This is been called , but in actual fact the problem is that when you have the logic written like this, it gives the error this blog post was setup for.

In essence it is trying to run the program from that exact point, which python does not allow.

Removing the parenthesis then allows the program to go and find the module it is referencing and then run the logic contained within that.

import schedule
import time

class scheduleprint():

    def printprogress(self):
        print("Start of Processing")
        print("Processing Complete")

    def schedule_a_print_job(self, type="Secs", interval=5):

        if type == "Secs": # Fed from the function paramaters
            schedule.every(interval).seconds.do(self.printprogress())===> The problem is here, remove the () after printprogress.
            # Including the parentheses  after printprogess will throw an error as you cant run that method directly from there you can only call it.

        if type == "Mins": # Fed from the function paramaters
            schedule.every(interval).minutes.do(self.printprogress)
            # Including the parentheses  after printprogess will throw an error as you cant run that method directly from there you can only call it.

        while True:
            schedule.run_pending()
            time.sleep(1) # The number of seconds the Python program should pause execution.

run = scheduleprint() # initiating an instance of the class "scheduleprint"
run.schedule_a_print_job() # running the function contained within the class.

In summary to help troubleshoot this problem:

(A) Check your code to see where it is calling a module within a class.

(B) Next make sure that in that call no parenthesis are present, otherwise it wont be able to find the module.

TypeError: ‘str’ object is not callable

Estimated reading time: 2 minutes

This is a common TypeError that you will come across in Python, it is actually easy to diagnose how it occurred.

To start off we need to understand “str” is a function in python, and it converts any value into a string.

As a result , because it is a function your ability to call it has limitations.

So in essence it has parenthesis () beside it, and allows parameters to be passed to it.

So lets first look at how the string function works:

x = str("10")
y = 10

print(type(x))
print(type(y))
print(x)
print(y)

With output:
<class 'str'>
<class 'int'>
10
10

As you will see the above the value 10, on its own is an integer, but when you call the string function, it now becomes a string.

For this reason this , calling a string function , completes a conversion to string of ten, but what if the variable is called str?

Lets take an example below from an input:

str = input("what year where you born?")
print(str(str))

Output:

what year where you born?2021
Traceback (most recent call last):
  File "str obj is not callable.py", line 2, in <module>
    print(str(str))
TypeError: 'str' object is not callable

Process finished with exit code 1

The reason for this error above , is that we have named the variable “str”.

As can be seen the programme is trying to use the first str in the print statement as a function.

As we know by now string variables are not callable.

Accordingly the function str() which the programme is trying to run, fails with the TypeError identified.

For this reason to fix this problem we would change the variable called str to “year”, the error then disappears.

The updated code will work as when calling str(), it is not conflicted with a variable name.

year = input("what year where you born?")
print(str(year))

Output:
"str obj is not callable.py"
what year where you born?2021
2021

Process finished with exit code 0

So to summarise:

  • Strings are not callable
  • Don’t name a variable as str, or any function name, these are reserved words by the system.
  • Functions are callable, strings are not.

Tkinter GUI tutorial python – how to clean excel data

Estimated reading time: 2 minutes

Tkinter is an application within Python that allows users to create GUI or graphical user interfaces to manage data in a more user-friendly way.

We are building our data analytics capability here, and looking to provide the user with the functionality they use in their work or college projects.

We have tested this code over 100,000 records sitting on the Microsoft OneDrive network so in a way, for this reason, its speeds were quite good.

As a result over five tests, they all were under 100s from start to finish.

data cleansing data cleansing fixed

In this Tkinter GUI tutorial python, you will be shown how to find the data errors, clean them and then export the final result to excel.

We will take you through the following:

  • Creation of the Tkinter interface.
  • Methods/ functions to find errors.
  • Methods/functions to clean the data.
  • Exporting the clean data to an excel file.

 

To sum up:

The video walks through the creation of a Tkinter window using a canvas and a frame to store the data frame.

Then it looks at importing the data through pd.read_excel, to load the data into a pandas data frame.

Next, there is a function and or method that will extract the errors through str.extract , which is loaded into separate columns

Finally, I have exported the clean dataset using rawdata.to_excel , and saved the file as a separate new spreadsheet.