Replacing data position in table - Matplotlib

Question

i'm trying to create a table with below result in matplotlib , data extracted from MysQl.

i used theses charts inside An application built by PYQT5

but unfortunately this is what i get :

code used :

def dashboard_fleet_statistics_fleets_visisted_current_year(self):
        try:
            mydb = con.connect(host= "localhost", user ="root", password='''''', db="fleet")
            cursor = mydb.cursor()
            cursor.execute('''SELECT  (fleet_code) ,fleet_name,COUNT(fleet_code) AS "No Of Visits",(Year(date_of_visit)) AS "Year Of Visit"
                                FROM vehicle_tyre_parameters
                                WHERE fleet_code != "" AND Year(date_of_visit)= Year(curdate())
                                GROUP BY fleet_code''')
            result4 = cursor.fetchall()

            print(list(result4))
            fleet_code=[]
            fleet_name=[]
            no_of_visits =[]
            fleet_year=[]

            for row in result4 :
                 fleet_code.append(row[0])
                 fleet_name.append(row[1])
                 no_of_visits.append(row[2])
                 fleet_year.append(row[3])

            print(list(fleet_code))
            print(list(fleet_name))
            print(list(no_of_visits))
            print(list(fleet_year))

            fig, ax = plt.subplots()
            values=[fleet_code,fleet_name,no_of_visits,fleet_year]

            table = ax.table(cellText=values,rowLabels=['Fleet Code','Fleet Name','No of Visits','Year of Visit'] ,colWidths=[.5,.5],colLoc='center',loc='center',bbox=[-0.3, 1, 1, 0.275])
            #modify table
            table.set_fontsize(14)
            table.scale(1,4)
            ax.axis('off')

            table[(1, 0)].set_facecolor("white")
            table[(2, 0)].set_facecolor("gray")

            plt.tight_layout()

            #display table
            plt.show()

Appreciate your help to get a table with 1st picture ! Thanks

@lior Here are the data i got from mysql server : Result of fetching mysql : [('FC-1', 'Guirguis ', 5, 2021), ('FC-6', 'Mohamed ', 1, 2021)] Making loop to get the data and below is the results of looping :- ['FC-1', 'FC-6'] ['Guirguis', 'Mohamed '] [5, 1] [2021, 2021] — Guirguis.Botros, Aug 25 '21 at 04:21

liorr · Accepted Answer · 2021-08-25T17:47:47.633

I cannot really access your data, but implementing the example you attached is pretty straightforward. I first put all the information in a pandas dataframe, and then used this neat trick that allows you to convert a dataframe to a matplotlib table. The rest is really playing with the design.

Here is a code snippet:

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

# Create figure
fig, ax = plt.subplots(figsize = (25,2))
fig.patch.set_visible(False)
ax.axis('off')
ax.axis('tight')

# Make some data and put in dataframe
data = [['FC-1', 'NAME', 5, 2021], ['FC-6', 'NAME', 5, 2021]]
df = pd.DataFrame(data, columns=['Fleet code', 'Fleet name', 'Number of visits', 'Year of visit'])

# Create the table from the dataframe:
tab = ax.table(cellText=df.values, colLabels=df.columns, loc='center', cellLoc='center', 
               colColours = ['lightsteelblue', 'lightsteelblue', 'lightsteelblue', 'lightsteelblue'],
               cellColours = [['w','w','w','w'], ['lightgray','lightgray','lightgray','lightgray']])

# Design the table:
# Fonts:
tab.auto_set_font_size(False)
tab.set_fontsize(20)
tab.scale(2, 2)

fig.tight_layout()

plt.show()

Here is the end result:

Replacing data position in table - Matplotlib

1 Answers1