Sep-25-2018, 06:17 PM
(This post was last modified: Sep-25-2018, 06:17 PM by gauravbhardwajee.)
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).
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()