pandas auto adjust column width upon file open xlsxwriter?

Viewed 39

I have a pandas dataframe like below

df1 = pd.DataFrame({'sfassaasfsa_id': [1321352,2211252],'SUB':['Phy','Phy'],'Revasfasf_Q1':[8215215210,1221500],'Revsaffsa_Q2':[6215120,525121215125120],'Reaasfasv_Q3':[20,12],'Revfsasf_Q4':[1215120,11221252]})

I am trying to do the below

a) filter the dataframe based on unique values of SUB

b) store them in multiple files

column_name = "SUB"
col_name = "Reaasfasv_Q3"
for i,j in dict.fromkeys(zip(df1[column_name], df1[col_name])).keys():
    data_output = df1.query(f"{column_name} == @i & {col_name} == @j")
    if len(data_output) > 0:
        output_path = Path.cwd() / f"{i}_{j}_output.xlsx"
        print("output path is ", output_path)
        writer = pd.ExcelWriter(output_path, engine='xlsxwriter')
        data_output.to_excel(writer,sheet_name='results',index=False)
        for column in data_output:
            column_length = max(data_output[column].astype(str).map(len).max(), len(column))
            col_idx = data_output.columns.get_loc(column)
            writer.sheets['results'].set_column(col_idx, col_idx, column_length)
        writer.save()

while the code works fine, however the problem is in adjusting the column_length

How can I adjust the column_length to be fit based on content type.

I expect my output to have a excel files with all columns are properly auto-fit.

Though it may work with my sample data, is there any other better way to auto-adjust column width using pd.ExcelWriter?

0 Answers
Related