Author: Latheesh NK

Open and Read from an Excel File and plot a chart in Python using matplotlib and tkinter

Today, we are going to see a simple program to read an excel and plot a chart using the data. In this example, we are going to explore few important features like – FileDialog, tkinter etc. Before we go through the details, Let us look at the entire code as below.
import tkinter as tk
from tkinter import filedialog
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg
from matplotlib.figure import Figure
import pandas as pd

root= tk.Tk()

canvas1 = tk.Canvas(root, width = 800, height = 300)
canvas1.pack()

label1 = tk.Label(root, text='Data Analyser')
label1.config(font=('Arial', 20))
canvas1.create_window(400, 50, window=label1)

def getExcel ():
      global df

      import_file_path = filedialog.askopenfilename()
      df = pd.read_excel (import_file_path)
      global bar1
      x = df['Day']
      y = df['Count']

      figure1 = Figure(figsize=(4,3), dpi=100)
      subplot1 = figure1.add_subplot(111)
      subplot1.bar(x,y,color = 'lightsteelblue')
      bar1 = FigureCanvasTkAgg(figure1, root)
      bar1.get_tk_widget().pack(side=tk.LEFT, fill=tk.BOTH, expand=0)
      subplot1.plot(x, y, color='green', linestyle='dashed', linewidth = 3, marker='o', markerfacecolor='blue', markersize=12)

def clear_charts():
      bar1.get_tk_widget().pack_forget()

browseButton_Excel = tk.Button(text='Load File...', command=getExcel, bg='green', fg='white', font=('helvetica', 12, 'bold'))
canvas1.create_window(400, 180, window=browseButton_Excel)

button2 = tk.Button (root, text='Clear Chart', command=clear_charts, bg='green', font=('helvetica', 11, 'bold'))
canvas1.create_window(400, 220, window=button2)

button3 = tk.Button (root, text='Exit!', command=root.destroy, bg='green', font=('helvetica', 11, 'bold'))
canvas1.create_window(400, 260, window=button3)

root.mainloop()
You can run the above code and see the output. Now, let us quickly go segment by segment to understand better. The below are the code to import tkinter, matplotlib and pandas
import tkinter as tk
from tkinter import filedialog
from matplotlib.backends.backend_tkagg import FigureCanvasTkAgg
from matplotlib.figure import Figure
import pandas as pd
create tkinter object and open a Canvas using the below code.
root= tk.Tk()
canvas1 = tk.Canvas(root, width = 800, height = 300)
canvas1.pack()
Let us configure the basic information for the canvas.
label1 = tk.Label(root, text='Data Analyser')
label1.config(font=('Arial', 20))
canvas1.create_window(400, 50, window=label1)
Function Definitions as below to open the file using filedialog and read the excel. You can see the sample data in the excel used in the example code.
def getExcel ():
      global df
 
      import_file_path = filedialog.askopenfilename()
      df = pd.read_excel (import_file_path)
      global bar1
      x = df['Day']
      y = df['Count']
 
      figure1 = Figure(figsize=(4,3), dpi=100)
      subplot1 = figure1.add_subplot(111)
      subplot1.bar(x,y,color = 'lightsteelblue')
      bar1 = FigureCanvasTkAgg(figure1, root)
      bar1.get_tk_widget().pack(side=tk.LEFT, fill=tk.BOTH, expand=0)
      subplot1.plot(x, y, color='green', linestyle='dashed', linewidth = 3, marker='o', markerfacecolor='blue', markersize=12)
 
def clear_charts():
      bar1.get_tk_widget().pack_forget()
Create buttons to perform the events in the requirements and mainloop invokation.
browseButton_Excel = tk.Button(text='Load File...', command=getExcel, bg='green', fg='white', font=('helvetica', 12, 'bold'))
canvas1.create_window(400, 180, window=browseButton_Excel)
 
button2 = tk.Button (root, text='Clear Chart', command=clear_charts, bg='green', font=('helvetica', 11, 'bold'))
canvas1.create_window(400, 220, window=button2)
 
button3 = tk.Button (root, text='Exit!', command=root.destroy, bg='green', font=('helvetica', 11, 'bold'))
canvas1.create_window(400, 260, window=button3)

root.mainloop()
In the next post, we will see more on plotting multi lines with a real time example in the Canvas.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

A glance at Anaconda, Jupyter notebook and Python for beginners

Jupyter notebook is traditional IDE for Python. It is a very popular IDE for most of data professionals as its very easy to install and use.
Jupyter notebook basically includes 2 components – jupyter notebook Server and a Browser. Browser communicates to server and process the requests.Browser usually uses a default localhost:8888 to connect to jupyter server.

Now, let us look at Anaconda, a package manager which allows to install many libraries. When install Anaconda, Python and Jupyter comes along with the installation. To install Anaconda, go to https://anaconda.org and download the latest file which is compatible to the workstation (depending on windows or Mac).

To launch jupyter, launch Anaconda navigator and then select jupiter, which would eventually open a browser where programmers can write and
run the codes.

Writing First Program in Jupyter notebook

1. Create a folder in Desktop to put save our sample work

2. Create python file by clicking New Python 3(in the screenshot)

3. It will open a code blocker where you can write programs

I am a beginner to Python and am writing these posts as I learn things for two main reasons, not to forget and to share with community. I would like to share your thoughts and experiences in comment section, so we all will be part of learning and sharing!

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

WITH RETURNS NULL ON NULL INPUT in SQL Server

While I was going through a review of a sql server function, it is observed there are many checks implemented if any of input parameter is null then return null. Then, it got strike on mind about “WITH RETURNS NULL ON NULL INPUT” in SQL Server. This is available in SQL server from 2015 version, however, I have not seen people used it efficiently. So thought of sharing about this intelligent way of handling such situations.

Let us start with a sample function as below:

Mighty Sample function

create or alter function fn_validateinparam(@col1 varchar(50), @col2 varchar(50))
returns  varchar(100)
as
Begin

	Declare @outputval varchar(100)
	If (@Col1 is null or @Col2 is null)
		set @outputval= NULL
	Else
		set @outputval= 'Valid params'

	return @outputval
End

Select dbo.fn_validateinparam('You','me')
Select dbo.fn_validateinparam('','')
Select dbo.fn_validateinparam('You',NULL)
Select dbo.fn_validateinparam(NULL,NULL)
Select dbo.fn_validateinparam(NULL,'me')

function using WITH RETURNS NULL ON NULL INPUT

The above is a sample function that we can see in many places to check the null param check. There is an option introduced in SQL Server 2005 “WITH RETURNS NULL ON NULL INPUT” to handle this with a grace as below. This way, if any of your input parameters is passed as NULL value, then the function will immediately return NULL value without actually invoking the body of the function. That clears the theory and its time for us to check with sample code as below.

create or alter function fn_Efficientvalidateinparam(@col1 varchar(50), @col2 varchar(50))
returns  varchar(100)
WITH RETURNS NULL ON NULL INPUT
as
Begin

	Declare @outputval varchar(100)
	If (@Col1 is null or @Col2 is null)
		set @outputval= 'Wrong params'
	Else
		set @outputval= 'Valid params'

	return @outputval
End

Select dbo.fn_Efficientvalidateinparam('You','me')
Select dbo.fn_Efficientvalidateinparam('','')
Select dbo.fn_Efficientvalidateinparam('You',NULL)
Select dbo.fn_Efficientvalidateinparam(NULL,NULL)
Select dbo.fn_Efficientvalidateinparam(NULL,'me')

Sample output

The below screenshot explains that the option returns without getting into execution of the function whenever one of the parameter is having NULL value. (Please note I used “Wrong value” to assign to the output variable to understand it actually returns in the beginning or only after the execution.)

If we have not used this technique so far, start using this one, it helps to avoid lots of junk codes in the definition that makes it clean. In addition, it has also observed a small factor of performance improvement for the function.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Associate Referral Program – Senior UX Designer | ADP

Associate Referral Program – Senior UX Designer

RESPONSIBILITIES:

Work Location(s): Chennai

Reports to: Product Development Director

Department: Global Product & Technology

What you’ll do:

• Demonstrate insight-based design.

• Create complex data visualization, UX solutions and information design.

• Work on mapping workflows, user journeys and communicating design intent.

• Create producing wireframes and prototypes and understand agile methods.

• Constantly think of features & designs that better the life of our users.

• Collaborate with development and product management team members to identify design problems and devise elegant solutions.

REQUIREMENTS:

Qualifications you’ll need:

Education: Bachelor’s degree (Mandatory) preferably in Computer Science or Information technology.

Experience:

 Minimum 4+ years UX Design experience.

 Experience in any of the software skills in Sketch, In Vision, Axure is mandatory

 Highly proficient in user journey mapping, information architecture and communicating user flows to stakeholders

 Experience in a cross functional UX team, working with researchers and visual designers.

Please forward it to whom you think would be helpful.

https://mycareer.adp.com/r/latheeshnk/jobs/194100

Palindrome in SQL Server

What is Palindrome?

From Books OnLine, “A palindrome is a word, number, phrase, or other sequence of characters which reads the same backward as forward, such as madam, racecar. There are also numeric palindromes, including date/time stamps using short digits 11/11/11 11:11 and long digits 02/02/2020. Sentence-length palindromes ignore capitalization, punctuation, and word boundaries.”

Code implementation

The below function eliminates characters other than alphanumeric characters. The function is modified to accommodate alphabets to the old function written to extract only numbers.

create or alter function dbo.fn_extractalphanumeric (@ValueString nvarchar(max))
returns nvarchar(max)
as
Begin

Declare @retvaluestring nvarchar(max)

;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4)
Select @retvaluestring =(
SELECT  (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^0-9a-z]'
                       THEN SUBSTRING(string, n, 1)
                      ELSE ''
                  END + ''
           FROM Nums
           WHERE n <= LEN(string)
           FOR XML PATH( '' )) AS stringout
FROM (Select * from (values(@ValueString)) A(string))A)
return @retValuestring
End

Test Script

DECLARE @Sample TABLE(string VARCHAR(2000));
INSERT INTO @Sample
Values( 'hhjjj12345hhhkk'),('-7655'),('asd5-5dffgdfg105'),('hhjjj12345hhhkk'),('aA1b2c3d4ee55fff666gggg7777h'),('Al1')
,('2Bob$'),('!Carl%'),('%D#?:"{}|\][+_)(*&^	%$#@!~`_+?>a*vi()(d&*(&*#?:"{}|\][J+o_n)es(*&^%$#@!~`_+?><,./;:"' ),
('    Eddie     '),('Noon'),('My Gym'),('malayalam'),('11:11'),('11/11/1111'),('racecar')

Select string, dbo.fn_extractalphanumeric(string),
	case when reverse(dbo.fn_extractalphanumeric(string)) = dbo.fn_extractalphanumeric(string) 
			then 'Palindrome' else 'Not Palindrome' End stringtype
From @Sample

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!