Existe-t-il un moyen d'ajuster automatiquement la largeur des colonnes Excel avec pandas.ExcelWriter ?

Question

On me demande de générer des rapports Excel. J'utilise actuellement pandas de manière assez intensive pour mes données, et j'aimerais donc naturellement utiliser la méthode pandas.ExcelWriter pour générer ces rapports. Cependant, la largeur fixe des colonnes pose un problème.

Le code que j'ai jusqu'à présent est assez simple. Disons que j'ai un cadre de données appelé 'df' :

writer = pd.ExcelWriter(excel_file_path, engine='openpyxl')
df.to_excel(writer, sheet_name="Summary")

J'ai examiné le code de pandas, et je ne vois pas vraiment d'options pour définir la largeur des colonnes. Existe-t-il un moyen de faire en sorte que les colonnes s'adaptent automatiquement aux données ? Ou y a-t-il quelque chose que je puisse faire après coup dans le fichier xlsx pour ajuster la largeur des colonnes ?

(J'utilise la bibliothèque OpenPyXL, et je génère des fichiers .xlsx - si cela fait une différence).

Merci.

Answer 1

Inspiré par La réponse de user6178746 J'ai les éléments suivants :

# Given a dict of dataframes, for example:
# dfs = {'gadgets': df_gadgets, 'widgets': df_widgets}

writer = pd.ExcelWriter(filename, engine='xlsxwriter')
for sheetname, df in dfs.items():  # loop through `dict` of dataframes
    df.to_excel(writer, sheet_name=sheetname)  # send df to writer
    worksheet = writer.sheets[sheetname]  # pull worksheet object
    for idx, col in enumerate(df):  # loop through all columns
        series = df[col]
        max_len = max((
            series.astype(str).map(len).max(),  # len of largest item
            len(str(series.name))  # len of column name/header
            )) + 1  # adding a little extra space
        worksheet.set_column(idx, idx, max_len)  # set column width
writer.save()

Answer 2

Je publie ce message car je viens de rencontrer le même problème et j'ai constaté que la documentation officielle de Xlsxwriter et de pandas indique toujours que cette fonctionnalité n'est pas prise en charge. J'ai bricolé une solution qui a résolu le problème que je rencontrais. J'ai simplement itéré à travers chaque colonne et utilisé worksheet.set_column pour définir la largeur de la colonne == la longueur maximale du contenu de cette colonne.

Une remarque importante, cependant. Cette solution ne s'adapte pas aux en-têtes de colonnes, mais simplement aux valeurs des colonnes. Cela devrait être un changement facile si vous avez besoin d'adapter les en-têtes à la place. J'espère que cela aidera quelqu'un :)

import pandas as pd
import sqlalchemy as sa
import urllib

read_server = 'serverName'
read_database = 'databaseName'

read_params = urllib.quote_plus("DRIVER={SQL Server};SERVER="+read_server+";DATABASE="+read_database+";TRUSTED_CONNECTION=Yes")
read_engine = sa.create_engine("mssql+pyodbc:///?odbc_connect=%s" % read_params)

#Output some SQL Server data into a dataframe
my_sql_query = """ SELECT * FROM dbo.my_table """
my_dataframe = pd.read_sql_query(my_sql_query,con=read_engine)

#Set destination directory to save excel.
xlsFilepath = r'H:\my_project' + "\\" + 'my_file_name.xlsx'
writer = pd.ExcelWriter(xlsFilepath, engine='xlsxwriter')

#Write excel to file using pandas to_excel
my_dataframe.to_excel(writer, startrow = 1, sheet_name='Sheet1', index=False)

#Indicate workbook and worksheet for formatting
workbook = writer.book
worksheet = writer.sheets['Sheet1']

#Iterate through each column and set the width == the max length in that column. A padding length of 2 is also added.
for i, col in enumerate(my_dataframe.columns):
    # find length of column i
    column_len = my_dataframe[col].astype(str).str.len().max()
    # Setting the length if the column header is larger
    # than the max column value length
    column_len = max(column_len, len(col)) + 2
    # set the column length
    worksheet.set_column(i, i, column_len)
writer.save()

Answer 3

Ajuster dynamiquement toutes les longueurs de colonnes

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

---

Ajustement manuel d'une colonne à l'aide de Column Name

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

---

Ajustement manuel d'une colonne à l'aide de Column Index

writer.sheets['sheetName'].set_column(col_idx, col_idx, 15)

---

Si l'un des éléments ci-dessus échoue avec

AttributeError: 'Worksheet' object has no attribute 'set_column'

assurez-vous d'installer xlsxwriter :

pip install xlsxwriter

---

Pour une explication plus complète vous pouvez lire l'article Comment ajuster automatiquement la largeur des colonnes d'Excel avec ExcelWriter de Pandas sur TDS.

Answer 4

Il existe un paquet sympa que j'ai commencé à utiliser récemment, appelé StyleFrame.

il récupère le DataFrame et vous permet de le styliser très facilement...

par défaut, la largeur des colonnes s'ajuste automatiquement.

par exemple :

from StyleFrame import StyleFrame
import pandas as pd

df = pd.DataFrame({'aaaaaaaaaaa': [1, 2, 3], 
                   'bbbbbbbbb': [1, 1, 1],
                   'ccccccccccc': [2, 3, 4]})
excel_writer = StyleFrame.ExcelWriter('example.xlsx')
sf = StyleFrame(df)
sf.to_excel(excel_writer=excel_writer, row_to_add_filters=0,
            columns_and_rows_to_freeze='B2')
excel_writer.save()

vous pouvez également modifier la largeur des colonnes :

sf.set_column_width(columns=['aaaaaaaaaaa', 'bbbbbbbbb'],
                    width=35.3)

MISE À JOUR 1

Dans la version 1.4 best_fit a été ajouté à l'argument StyleFrame.to_excel . Voir le documentation .

MISE À JOUR 2

Voici un exemple de code qui fonctionne pour StyleFrame 3.x.x

from styleframe import StyleFrame
import pandas as pd

columns = ['aaaaaaaaaaa', 'bbbbbbbbb', 'ccccccccccc', ]
df = pd.DataFrame(data={
        'aaaaaaaaaaa': [1, 2, 3, ],
        'bbbbbbbbb': [1, 1, 1, ],
        'ccccccccccc': [2, 3, 4, ],
    }, columns=columns,
)
excel_writer = StyleFrame.ExcelWriter('example.xlsx')
sf = StyleFrame(df)
sf.to_excel(
    excel_writer=excel_writer, 
    best_fit=columns,
    columns_and_rows_to_freeze='B2', 
    row_to_add_filters=0,
)
excel_writer.save()

Answer 5

Il n'y a probablement pas de moyen automatique de le faire pour le moment, mais comme vous utilisez openpyxl, la ligne suivante (adaptée d'une autre réponse de l'utilisateur Bufke sur comment faire manuellement ) vous permet de spécifier une valeur saine (en largeur de caractères) :

writer.sheets['Summary'].column_dimensions['A'].width = 15