Last active
January 9, 2023 15:18
-
-
Save drkane/75b824d45a77e3916f7a93d3a78509f4 to your computer and use it in GitHub Desktop.
Pandas to_excel with a table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# from https://xlsxwriter.readthedocs.io/example_pandas_table.html | |
df.index.name = "org_id" | |
criteria = df["Include"]=="y" | |
sheets = { | |
"Included - active": (criteria & df["active"]), | |
"Included - inactive": (criteria & ~df["active"]), | |
"Excluded": ~criteria, | |
} | |
column_widths = df.apply(lambda x: x.astype(str).str.len().max(), axis=0).apply(lambda x: min(x, 50)).to_dict() | |
with pd.ExcelWriter("output.xlsx") as writer: | |
for sheet_name, mask in sheets.items(): | |
to_add = df[mask].reset_index() | |
to_add.to_excel(writer, sheet_name=sheet_name, startrow=1, header=False, index=False) | |
# Get the xlsxwriter workbook and worksheet objects. | |
workbook = writer.book | |
worksheet = writer.sheets[sheet_name] | |
# Get the dimensions of the dataframe. | |
(max_row, max_col) = to_add.shape | |
# Create a list of column headers, to use in add_table(). | |
column_settings = [{'header': column} for column in to_add.columns] | |
# Add the Excel table structure. Pandas will add the data. | |
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings}) | |
# Make the columns wider for clarity. | |
for col_index, column in enumerate(to_add.columns): | |
worksheet.set_column(col_index, col_index, column_widths.get(column, 12)) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
def to_excel_table(df, writer, sheet_name, column_widths=None, max_col_width=50, startrow=0, do_column_widths=True, **kwargs): | |
df.to_excel(writer, sheet_name=sheet_name, startrow=startrow+1, header=False, index=False, **kwargs) | |
if column_widths is None: | |
column_widths = ( | |
df.apply(lambda x: x.astype(str).str.len().max(), axis=0) | |
.apply(lambda x: min(x, max_col_width)) | |
.to_dict() | |
) | |
# Get the xlsxwriter workbook and worksheet objects. | |
workbook = writer.book | |
worksheet = writer.sheets[sheet_name] | |
# Get the dimensions of the dataframe. | |
(max_row, max_col) = df.shape | |
max_row = startrow + max_row | |
# Create a list of column headers, to use in add_table(). | |
column_settings = [{"header": column} for column in df.columns] | |
# Add the Excel table structure. Pandas will add the data. | |
worksheet.add_table(startrow, 0, max_row, max_col - 1, {"columns": column_settings}) | |
# Make the columns wider for clarity. | |
if do_column_widths: | |
for col_index, column in enumerate(df.columns): | |
worksheet.set_column( | |
col_index, col_index, min(max(column_widths.get(column, 12), len(column)), max_col_width) | |
) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment