How to order the columns in a pandas data frame non-alphabetically and to merge the cells of a matplotlib table?

Question

I am trying to plot a table in python. I have it working and such...but when I plot my table, it doesn't plot the Pass/Fail column at the end how I have it written. It seems that the columns are being displayed in alphabetical order.

How do I disable this.
I want to add the last column but just as one row. Basically a big check box but when I do that it gives me an error that the arrays must be the same length, which makes sense...but how can I go around this and just have one large column with no rows..?

import pandas as pd
import matplotlib.pyplot as plt

MinP_M=5
Min_M=6
Per_M=7
Per_G=8
Per2_M=9
PerFlat_M=10
MaxPL_M=11
Max_M=12
GF_M =13


fig1 = plt.figure()
fig1.set_size_inches(8.7,11.75,forward=True)
ax1=fig1.add_subplot(111)

ax1.axis('off')
ax1.axis('tight')

data2={'Min':['%s'%MinP_M,'%s'%Min_M,'',''],
       'Typ':['%s'%Per_M,'%s'%Per_G,'%s'%Per2_M,'+/- %s'%PerFlat_M],
       'Max':['%s'%MaxPL_M,'','%s'%Max_M,'+/- %s'%GF_M],
       'Pass/Fail':['','','','']
     }
df2 = pd.DataFrame(data2)

the_table2=ax1.table(cellText=df2.values,colWidths=[0.15]*5,rowLabels=['A','B','C', 'D'],colLabels=df2.columns,loc='center')

plt.show()

Thomas Kühn · Accepted Answer · 2017-12-14T10:37:51.203

The first part is relatively easy to solve. As you create your pandas data frame using a dict, the order of keywords and thus the order of columns is not fixed. To get the ordering correct, use the columns keyword. The second part was a bit more tricky. The solution I found here is to overlay your original table with a second table and then adding another cell to that second table that has the same height as the four cells of the original table. For that you have to first obtain the cell dictionary from the table instance and sum up the heights of the table rows. Please see the code below:

import pandas as pd
import matplotlib.pyplot as plt

MinP_M=5
Min_M=6
Per_M=7
Per_G=8
Per2_M=9
PerFlat_M=10
MaxPL_M=11
Max_M=12
GF_M =13


fig1 = plt.figure()

##this line entirely messed up the plot for me (on Mac):
##fig1.set_size_inches(8.7,11.75,forward=True)

ax1=fig1.add_subplot(111)

ax1.axis('off')
ax1.axis('tight')

data2={'Min':['%s'%MinP_M,'%s'%Min_M,'',''],
       'Typ':['%s'%Per_M,'%s'%Per_G,'%s'%Per2_M,'+/- %s'%PerFlat_M],
       'Max':['%s'%MaxPL_M,'','%s'%Max_M,'+/- %s'%GF_M],
       'Pass/Fail':['','','','']
     }

##fix the column ordering with a list:
keys = ['Min', 'Typ', 'Max', 'Pass/Fail']
df2 = pd.DataFrame(data2, columns=keys)

##defining the size of the table cells
row_label_width = 0.05
col_width = 0.15
col_height = 0.05

the_table2=ax1.table(
    cellText=df2.values,
    colWidths=[col_width]*4,
    rowLabels=['A','B','C', 'D'],
    colLabels=df2.columns,
    ##loc='center', ##this has no effect if the bbox keyword is used
    bbox = [0,0,col_width*4,col_height*5],
)

celld = the_table2.get_celld()

##getting the heights of the header and the columns:
row_height_tot = 0
for (i,j),cell in celld.items():
    if j==3 and i>0:   #last column, but not the header
        row_height_tot += cell.get_height()    

the_table3=ax1.table(
    cellText=['0'], ##cannot be empty
    colLabels=df2.columns[-1:],
    colWidths=[col_width],
    bbox = [col_width*3,0,col_width,col_height*5],
)
the_table3.add_cell(1,0,col_width,row_height_tot)        

fig1.tight_layout()

plt.show()

I had to turn off some of your formatting options as they gave weird results on my computer. If you want to have the table centred, play with the bbox options in the table commands. The final result looks like this:

Hope this helps.

How to order the columns in a pandas data frame non-alphabetically and to merge the cells of a matplotlib table?

1 Answers1