With Pandas, how do I set the column width by column number?

Python Help
1

Python 3.12 on Windows 10 Pro.

I think I understand using pandas with xlsxwriter, but I am having a hard time finding any pages that set the column width by column number. For example I want to set the column width of column 0 to 25 (in whatever units Excel uses).

Why do I want to do this? Because column headers can change and they can be long, at 25+ characters sometimes.

This is what I’ve tried. It’s right at the beginning of my function.

    import pandas
    import xlsxwriter
    outdata = [["Name", "MyInt", "Float"], # Headers
    ["Jim Green", 10, 3.14],
    ["Sally Struthers", 20, 2.86],
    ["Nancy Drew", 22, 1.05]
    ]
    
    df=pandas.DataFrame(outdata)
    df.set_width(0, width)

The error I get on the df.set_width() line is AttributeError: 'DataFrame' object has no attribute 'set_width'

EDIT: Some columns in real-world spreadsheets I work with will have 200+ characters and I do not want those columns to be at max width, so I can fit more columns on the screen at once.

2

Something like “set_width” would control how data is visually rendered on some display or while printing. Pandas does have some general options for displaying data, but those are not attached to particular DataFrames (and they are not saved/loaded when you save/load one). A DataFrame is basically just an abstract container of the data + data types; it doesn’t care if/how it’s being displayed. So, the formatting calls need to be done in xlsxwriter. (See Example: Pandas Excel output with column formatting — XlsxWriter
The sample code there deliberately does not set the width. But see also: The Worksheet Class — XlsxWriter)

I’ve never used xlswriter - but I wonder if you just use df.to_excel without specifying anything special (no special formatters, stylers, etc) and then open the Excel file, won’t the columns automatically be rendered with the appropriate width?

3

Yes they are, but in the real spreadsheets I work with, some columns have 200+ characters in a cell. And I don’t want that column super wide, I want to make some columns thinner to make sure more columns appear on screen and on the printed page.

1 Like