Python Forum

Hi, I have tried several codes to set my column widths without success. Is there a simple code similar to VBA I could use?
I would like to add column widths to the code I am building below. Do I need to 'import numpy as np' or anything else?

Please let me know of any other improvements I can make to my code.

Thanks in advance.

import pandas as pd 

inspdata=pd.read_excel('Inspections data.xlsx')

#remove columns 
inspdata.drop(['Notice Creation','count()'],axis=1, inplace=True)

#replace header row names
inspdata.columns=['EForm ID','Contractor','Address','Area','Classification','Program','Notice Ref #','Category','Inspection Type',
'Result','Actual Date','Created Date','Status','CIO','PO']

#delete first 4 rows below the header
inspdata.drop([0,1,2,3],axis=0,inplace=True) #keeps header row

#replace text
inspdata['Classification'].replace(['VACQREP','VACAP'],['VAC','VAC'],inplace=True)

print(inspdata.head(10))

inspdata.to_excel('Inspections Data Python.xlsx',index=False)

Is there a simple Python code similar to the below VBC code?

'VBA code to format column widths
Range("A1").ColumnWidth = 15
Range("B1").ColumnWidth = 30

you can set max column width with set_options and also set precision for floats, much more.

Thanks Larz60 but I'm new to Python so I still can't figure that out. Could you write the code to set the width for two of my columns, EForm ID & Contractor, cheers

(Jan-14-2024, 05:01 AM)1418 Wrote: [ -> ]Thanks Larz60 but I'm new to Python so I still can't figure that out. Could you write the code to set the width for two of my columns, EForm ID & Contractor, cheers

you should be able to do using

data_frame.style.set_column_width({column_name: width})

before you save changes. Where data_frame is the name of your data frame. You should be able to figure it out from here

Thanks sgrey, I tried the below code but it didn't work, what have I done wrong, cheers

Error:
inspdata.style.set_column_width({'EForm Id':100,'Contractor':100})

(Jan-14-2024, 06:22 AM)1418 Wrote: [ -> ]Thanks sgrey, I tried the below code but it didn't work, what have I done wrong, cheers

Error:
inspdata.style.set_column_width({'EForm Id':100,'Contractor':100})

Sorry, it seems I used wrong api. It looks like it is somewhat difficult with just pandas without additional libraries. See this for a variety of ways to do it https://stackoverflow.com/questions/1732...xcelwriter

sgrey, I tried both these without success. I'm using Visual Studio, are you suggesting I need to download additional libraries? Or should I use Pycharm?

Error:
inspdata.set_column_width(columns=['EForm Id'],width=100, ['Contractor'],width=100)
inspdata.set_column_width(columns=['EForm Id', 'Contractor'],width=100)

(Jan-14-2024, 07:41 AM)1418 Wrote: [ -> ]sgrey, I tried both these without success. I'm using Visual Studio, are you suggesting I need to download additional libraries? Or should I use Pycharm?

Error:
inspdata.set_column_width(columns=['EForm Id'],width=100, ['Contractor'],width=100)
inspdata.set_column_width(columns=['EForm Id', 'Contractor'],width=100)

it doesn't matter what IDE you use. Pay attention to all of the code and the description they posted. It's not just one line call, you have to use more complex code and another library in addition to it.
Look at the top answer. You probably can use it as-is if you just add it at the end of your code and install xlsxwriter.

Can you tell me what additional library I need, cheers

(Jan-14-2024, 07:52 AM)1418 Wrote: [ -> ]Can you tell me what additional library I need, cheers

First you can try this answer

writer = pd.ExcelWriter('/path/to/output/file.xlsx') 
df.to_excel(writer, sheet_name='sheetName', index=False, na_rep='NaN')

for column in df:
    column_length = max(df[column].astype(str).map(len).max(), len(column))
    col_idx = df.columns.get_loc(column)
    writer.sheets['sheetName'].set_column(col_idx, col_idx, column_length)

writer.save()

which should adjust all your columns to a reasonable width, unless you have a really long text in them.

or manual way

col_idx = df.columns.get_loc('columnName')
writer.sheets['sheetName'].set_column(col_idx, col_idx, 15)

note that you need to use ExcelWriter from pandas in both examples

or install xlsxwriter or styleframe or openpyxl or whatever else they used in the example and do it that way