Warm tip: This article is reproduced from serverfault.com, please click

How to get the number of columns and the width of each column in a Pandas pivot table?

发布于 2020-11-28 00:00:28

I'd like to be able to get the number of columns and the width of each column in a Pandas pivot table. Here is my code:

import pandas as pd
import numpy as np
df = pd.DataFrame({'System_Key':['MER-002', 'MER-003', 'MER-004', 'MER-005', 'MER-006', 'MER-007', 'BAV-378', 'BAV-379', 'BAV-380', 'BAV-381', 'AUD-220', 'AUD-221', 'AUD-222', 'AUD-223'],
                       'Manufacturer':['Mercedes', 'Mercedes', 'Mercedes', 'Mercedes', 'Mercedes', 'Mercedes', 'BMW', 'BMW', 'BMW', 'BMW', 'Audi', 'Audi', 'Audi', 'Audi'],
                       'Region':['Americas', 'Europe', 'Americas', 'Asia', 'Asia', 'Europe', 'Europe', 'Asia', 'Europe', 'Europe', 'Americas', 'Asia', 'Americas', 'Americas'],
                       'Department':[np.nan, 'Sales', np.nan, 'Operations', np.nan, np.nan, 'Accounting', 'Finance', 'Finance', np.nan, 'Finance', 'Finance', 'Finance', np.nan],
                       'Approver':[np.nan, 'Jones, T.', 'Smith, W.', 'Jones, T.', 'Jones, T.', np.nan, np.nan, np.nan, 'Balakrishnan, G.', np.nan, np.nan, np.nan, np.nan, np.nan]
                      })

df = df.applymap(str)

df['Rebate_Plan'] = np.where(df['System_Key'].str.contains('BAV', na=False), 'Jupyter',
                    np.where(df['System_Key'].str.contains('AUD', na=False), 'Uranus',
                    np.where((df['System_Key'].str.contains('MER', na=False)) & (df['Approver'].str.contains('Jones', na=False)), 'Saturn',
                    np.where((df['System_Key'].str.contains('MER')) & (~df['Approver'].str.contains('Jones')), 'Pluto', '*No Plan*'))))

df.replace(['None', 'nan'], np.nan, inplace=True)

pivot_data = pd.pivot_table(data=df, index='Manufacturer', columns='Approver', values='System_Key', aggfunc='count', margins=True, margins_name='TOTALS', fill_value=0)

The result is as follows

print('-' * 60)
print(pivot_data)
print('-' * 60)

----------------------------------------------------------------------
Approver            Balakrishnan, G.    Jones, T.   Smith, W.   TOTALS
Manufacturer                
BMW                 1                   0           0           1
Mercedes            0                   3           1           4
TOTALS              1                   3           1           5
----------------------------------------------------------------------

In this example, notice that the hyphen ('-') line breaks above and below the pivot table terminate at the last character of "TOTALS".

I'd like to be able to get the number and width of the columns so that I can print a variable-length line break above and below the pivot table in the console.

If we don't consider the column width, then the pseudo-code for number of columns would looks something as follows:

if number_of_pivot_table_columns == 2:
    print('-' * 45)
    print(pivot_data)
    print('-' * 45)

elif number_of_pivot_table_columns == 3:
    print('-' * 60)
    print(pivot_data)
    print('-' * 60)

elif number_of_pivot_table_columns == 4:
    print('-' * 75)
    print(pivot_data)
    print('-' * 75)

(Of course, this approach only works if the column width if 15 characters, which will not happen in practice since the person's first initial and last name can be any number of characters.)

Thanks in advance for your help!

Questioner
equanimity
Viewed
0
frankr6591 2020-11-28 08:38:24

use str() to find max len.

outStr = str(pivot_data)
mxLen = max([len(l) for l in outStr.split('\n')])
print('-'*mxLen)
print(outStr)
print('-'*mxLen)