How to auto adjust the column width of a excel sheet using pandas

Question

I am trying to auto-adjust the column cells to the excel data i used this code which i found in stack overflow

df=pd.read_excel(r"location of the file")
writer = pd.ExcelWriter(r'C:\Users\Aparna\Downloads\columnwidth1.xlsx') 
df.to_excel(writer, sheet_name='my_analysis', index=False, na_rep='NaN')

# Auto-adjust columns' width
for column in df:
    column_width = max(df[column].astype(str).map(len).max(), len(column))
    col_idx = df.columns.get_loc(column)
    writer.sheets['my_analysis'].set_column(col_idx, col_idx, column_width)

writer.save()

i used this code for increasing the column width but it throws attribute error Attribute Error: 'DataFrame' object has no attribute 'map' and the saved excel sheet is not able to open getting file extention is not valid how to over come this here I am sharing the raw data along with expected output https://docs.google.com/spreadsheets/d/1_iemqwkTYdUCoXKj3O8JNnq3V1ePY5sSRuFD9zDJRPA/edit?usp=sharing this is the link for data.

https://stackoverflow.com/questions/39535447/attributeerror-dataframe-object-has-no-attribute-map this might be helpful for your case. — Tenesh Vignesan, Oct 31 '22 at 05:51
but iam using python and xlsx writer i dont have idea on pyspark — aparna podili, Oct 31 '22 at 06:02

Jason Baker · Accepted Answer · 2022-10-31T06:32:54.353

1

This will format column width.

df = pd.read_excel(r"location of the file")

with pd.ExcelWriter(r"C:\Users\Aparna\Downloads\columnwidth1.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, startrow=0, sheet_name="my_analysis", index=False)

    for col in pd.Series(range(len(df.columns))).tolist():
        header_name = df.columns[col]
        header_len = len(header_name)
        column_len = df[header_name].map(len).max()
        total_len = sum([header_len, 2]) if header_len > column_len else sum([column_len, 2])

        writer.sheets["my_analysis"].set_column(col, col, total_len)

edited Oct 31 '22 at 06:32

answered Oct 31 '22 at 06:13

Jason Baker

3,170
2
12
15

for this code i am unable to find the excel file not getting any output – aparna podili Oct 31 '22 at 06:23
how are you calling it? Also I assume you are on windows double check the path. – Jason Baker Oct 31 '22 at 06:24
yes windows only but not able to find the file double checked – aparna podili Oct 31 '22 at 06:27
did you call the function and pass your df as an argument?? – Jason Baker Oct 31 '22 at 06:28
no, i stored my data in df – aparna podili Oct 31 '22 at 06:29
try edit, I removed the function and reformatted the file path to remove those possible sticking points – Jason Baker Oct 31 '22 at 06:33
TypeError: object of type 'int' has no len() getting this error sir can you use the data which i shared the google drive link above – aparna podili Oct 31 '22 at 06:40
Your original data needs cleaned up first such as blank column names – Jason Baker Oct 31 '22 at 14:20

How to auto adjust the column width of a excel sheet using pandas

1 Answers1