Python Forum

Full Version: Bar Plot with Python ,matplotlib and sqlite3 tables
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I am very new to Python and tying to create a Bar Graph using Python ,matplotlib and sqlite3 tables. So i am reading two csv files with the help of pandas and putting them into the sqlite tables. Next i am creating pivot table like structure to create a Bar Graph. Graph is coming without issue but labels on X-axis are not appearing correctly, and index value on Y - Axis is not coming correctly. I am printing the pivot table output and below is the output :

run_type val Element_Classification
0 Current 6762007.00 new1
1 Current 5260558.83 new2
2 Current 3435044.00 new3
3 Current 9420038.00 new4
4 Prev 6786002.50 old1
5 Prev 5264360.98 old2
6 Prev 3396588.00 old3
7 Prev 9558552.50 old4
Below is output of the Bar Graph : Bar Graph Below is the code which i am using to generate the Bar Graph( i am getting error when i am putting full code).

import pandas
import sqlite3
from tkinter import *
import tkinter
import tkinter.messagebox
import csv
from xlsxwriter.workbook import Workbook
import matplotlib.pyplot as plt
import seaborn as sns


def proces():
	
	conn = sqlite3.connect("abcrecon.db")
		
	c = conn.cursor()
	total_diff_in_standard_earning=0
	tot_curr_standard_earning = 0
	tot_prev_standard_earning = 0
	total_diff_in_standard_earning= int(total_diff_in_standard_earning)
	tot_curr_standard_earning= int(tot_curr_standard_earning)
	tot_prev_standard_earning= int(tot_prev_standard_earning)
	c.execute('''CREATE TABLE if not exists OLD_DATA
												(Country_Code text,
												Employee_Number text,
												Worker_Number text,
												Assignment_Number text,
												Date_Earned text,
												abc_Start_Date text,
												abc_End_Date text,
												abc_Relationship_Number text,
												abc_Name text,
												abc_Action_Id text,
												abc_Rel_Action_Id text,
												placeolder_Name text,
												placeolder_Classification text,
												placeolder_Secondary_Classification text,
												Input_Value_Name text,
												Input_Value text,
												Unit_of_Measure text
												)''')
	c.execute('''delete from OLD_DATA''')											
	c.execute('''CREATE TABLE if not exists NEW_DATA
												(Country_Code text,
												Employee_Number text,
												Worker_Number text,
												Assignment_Number text,
												Date_Earned text,
												abc_Start_Date text,
												abc_End_Date text,
												abc_Relationship_Number text,
												abc_Name text,
												abc_Action_Id text,
												abc_Rel_Action_Id text,
												placeolder_Name text,
												placeolder_Classification text,
												placeolder_Secondary_Classification text,
												Input_Value_Name text,
												Input_Value text,
												Unit_of_Measure text
												)''')											
												
	c.execute('''delete from NEW_DATA''')												
												

											 
	df1 = pandas.read_csv("NEW.csv")
	df2 = pandas.read_csv("OLD.csv")
	df1.to_sql("NEW", conn, if_exists='append', index=False)
	df2.to_sql("OLD", conn, if_exists='append', index=False)
	tkinter.messagebox.showwarning("Information","Please note that upload to staging table is complete, Compare is in progress.")
	c.execute('''select count(distinct Employee_Number) from OLD_DATA''')
	(total_emps_in_prev_run,) = c.fetchone()
	Entry.insert(E1,0,total_emps_in_prev_run)
	print(total_emps_in_prev_run)
		
	
	c.execute('''select count(distinct Employee_Number) from NEW_DATA''')
	(total_emps_in_curr_run,) = c.fetchone()
	Entry.insert(E2,0,total_emps_in_curr_run)
	print(total_emps_in_curr_run)
		
		
	c.execute('''select count(distinct prev_run.Employee_Number) total_person_
					from PAY_CURRENT_RUN_DATA cur_run, OLD_DATA prev_run
					where cur_run.Employee_Number=prev_run.Employee_Number''')
	(unique_common_emp_count,) = c.fetchone()
	Entry.insert(E3,0,unique_common_emp_count)
	print(unique_common_emp_count)	
	
	c.execute('''select (sum(cur_run.Input_Value))  from NEW_DATA cur_run
							where cur_run.placeolder_Classification="Standard Earnings"''')	
	(tot_curr_standard_earning,) = c.fetchone()
	print(tot_curr_standard_earning)
	c.execute('''select (sum(prev_run.Input_Value))  from OLD_DATA prev_run
							where prev_run.placeolder_Classification="Standard Earnings"''')	
	
	(tot_prev_standard_earning,)= c.fetchone()
	print(tot_prev_standard_earning)
	total_diff_in_standard_earning = (tot_curr_standard_earning) - (tot_prev_standard_earning)
	total_diff_in_standard_earning=round(total_diff_in_standard_earning,2)
	Entry.insert(E4,0,total_diff_in_standard_earning)		
	print(total_diff_in_standard_earning)
	
	c.execute('''select (sum(cur_run.Input_Value))  from NEW_DATA cur_run
							where cur_run.placeolder_Classification="Pretax Deductions"''')	
	(tot_curr_pretax_deduction,) = c.fetchone()
	print(tot_curr_pretax_deduction)
	c.execute('''select (sum(prev_run.Input_Value))  from OLD_DATA prev_run
							where prev_run.placeolder_Classification="Pretax Deductions"''')	
	
	(tot_prev_pretax_deduction,)= c.fetchone()
	print(tot_prev_pretax_deduction)
	total_diff_in_pretax_deduction = (tot_curr_pretax_deduction) - (tot_prev_pretax_deduction)
	total_diff_in_pretax_deduction=round(total_diff_in_pretax_deduction,2)
	Entry.insert(E5,0,total_diff_in_pretax_deduction)		
	print(total_diff_in_pretax_deduction)
	
	sql_statement1= '''select "Current" run_type, round(sum(curr.Input_Value),2) val, curr.placeolder_Classification from NEW_DATA curr
					group by curr.placeolder_Classification
					union all
					select "Prev" run_type, round(sum(prev.Input_Value),2) val, prev.placeolder_Classification from OLD_DATA prev 
					group by prev.placeolder_Classification'''
	df1 = pandas.read_sql_query(sql_statement1,conn)
	print(df1)
	#table = df1.pivot_table(values='val', index=['placeolder_Classification'],columns=['run_type'])#,aggfunc = 'sum') #, aggfunc=np.sum)
	table = df1.pivot_table(values='val', index=['placeolder_Classification'],columns=['run_type'],margins = False) #, aggfunc=np.sum)
	#table = df1.pivot_table(values='val',columns=['run_type'])#,aggfunc = 'sum') #, aggfunc=np.sum)
	# use Seaborn styles
	#sns.set() 
	table.plot.bar()
	#plt.clf()
	plt.style.use('ggplot')	
	plt.ylabel('US Dollor')
	plt.xlabel('deduction type)
	plt.title("ABC Graphical View")
	plt.legend() 
	xL = xlim
	yL = ylim	
	#plt.xlim(1.5,n), plt.xticks([])
	#plt.ylim(.25,+.25,), plt.yticks([])
	plt.show()
	
	conn.commit() 
	conn.close()
			

top = tkinter.Tk()	
top.title('ABC Tool')
top.iconbitmap('myicon.ico')
top.geometry("800x500")
top.configure(background='silver')

L1 = Label(top, text="Welcome to ABC Tool!", fg="black",bg = "light green").grid(row=1,column=2,padx=20, pady=20)
B=Button(top, justify=CENTER,text ="Click here to Upload files and Compare",command = proces,bg = "grey").grid(row=10,column=2,sticky=E,padx=20, pady=20)

top.mainloop()