python - Plotting only a table based on groupby data from a dataframe?

Question

I ultimately would like to write a table based on "groupby" of a DataFrame to a pdf file.

In order to do so, creating a "plot" of the table seems to be a way to achieve it.

I use the code below. The table is shown nicely using print(), but generates the error when trying to plot the table:

"TypeError: object of type 'numpy.float64' has no len()"

And I simply cannot figure out how to get around it. The code works fine on the "full" DataFrame.

Thanks in advance.

My code:

df = pd.read_csv('Stock_holdings.csv', delimiter=";")

df1 = df.groupby(['Valuta','Risk type 1'])["Holding"].sum()
print(df1)

fig, ax =plt.subplots(figsize=(24,4))
ax.axis('tight')
ax.axis('off')
table = ax.table(cellText=df1.values,colLabels=df1.columns,loc='center')
table.set_fontsize(24)

Outcome of the groupby using print():

Valuta  Risk type 1
DKK     Consumer       2351.00
        Financial      4668.00
        Index          1666.00
        Industrial      231.00
        Medical        1631.00
        Tankers          33.00
        Utility        1900.00
EUR     Consumer        468.00
        Financial      2007.00
        Industrial      849.00
        Tankers        1100.00
NOK     Tankers        1000.00
SEK     Financial       450.00
USD     Consumer        500.00
        Financial      1607.00
        Housing        3560.00
        Index           649.00
        Industrial      990.00
        Medical         562.03
        Tankers        1505.00
Name: Holding, dtype: float64

Below is the initial dataframe (df) - first 5 rows

    Symbol      Virksomhed  Holding  Count Valuta  Pension  Bank Instrument  
0      TNK  Teekay Tankers    505.0      1    USD        0  Saxo     Equity   
1     SLRC   Solar Capital    649.0      1    USD        0  Saxo     Equity   
2      FRO  Frontline NYSE   1000.0      1    USD        0  Saxo     Equity   
3      SKT          Tanger    500.0      1    USD        0  Saxo     Equity   
4  EURN.BR         Euronav   1100.0      1    EUR        0  Saxo     Equity   

  Risk type 1 Aktivklasse  
0     Tankers       Aktie  
1       Index       Aktie  
2     Tankers       Aktie  
3    Consumer       Aktie  
4     Tankers       Aktie  

question from:https://stackoverflow.com/questions/65647518/plotting-only-a-table-based-on-groupby-data-from-a-dataframe

Answer 1

You can use reset_index(). Your groupby aggregation with .sum() returns a pandas series, while the plotting function expects a dataframe (or similar 2D string structure). When printing, a Multiindex dataframe looks similar to a series, so it is easy to assume, you generated a new dataframe for the plot. However, you might have noticed that the printout of your aggregation series does not have a column name, the name Holding is instead printed below.

from matplotlib import pyplot as plt
import pandas as pd

#fake data
import numpy as np
np.random.seed(1234)
n = 20
df = pd.DataFrame({"Valuta": np.random.choice(["DKK", "EUR", "US"], n), 
                   "Risk type 1": np.random.choice(["Consumer", "Financial", "Index", "Industrial", "Medical", "Utility"], n), 
                   "Holding": np.random.randint(100, 500, n), 
                   "Pension": np.random.randint(10, 100, n)})

df1 = df.groupby(['Valuta','Risk type 1'])["Holding"].sum().reset_index()
#print(df1)

fig, ax =plt.subplots(figsize=(8,10))
ax.axis('tight')
ax.axis('off')
my_table = ax.table(cellText=df1.values, colLabels=df1.columns, cellLoc="center", loc='center')
my_table.set_fontsize(24)
my_table.scale(1, 3)
plt.show()

Sample output: