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!
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)