Export tables to spreadsheet

export_to_spreadsheet.py

from pandas import DataFrame, ExcelWriter, Series


# assuming you have tables:
participants_sumnary = DataFrame()
measured_things = DataFrame()
correlation_results = DataFrame()
model_results = DataFrame()


def adjust_column_sizes(worksheet, df):
    """Make columns have width matching the text inside"""
    df = df.reset_index()
    # https://stackoverflow.com/a/40535454/6646912
    # CC-BY-SA 4.0 alichaudry
    # https://creativecommons.org/licenses/by-sa/4.0/
    # modified to handle hyperlinks
    for idx, col in enumerate(df):
        series = df[col]
        if series.dtype == 'object':
            series = series.str.replace(r'=HYPERLINK\("(.*)", "(.*)"\)', r'\2')
        max_len = max((
            series.astype(str).map(len).max(),
            len(str(series.name))
        )) + 1
        worksheet.set_column(idx, idx, max_len)


# if a there is a hyperlink
measured_things['uniprot_link'] = measured_things['uniprot_link'].str.replace(
    '<a href="(.*)" target="_blank">(.*)</a>',
    r'=HYPERLINK("\1", "\2")'
)


sheets = {
    'Legend': (
        Series({
            'Table 1': 'Summary of the included participants',
            'Table 2': 'Summary of measured things with links to database X',
            'Table 3': 'Correlation using method X',
            'Table 4': 'Linear regression model adjusted for X, Y, Z'
            # more tables if needed
        })
        .rename('Legend')
        .rename_axis('Sheet')
        .to_frame()
    ),
    'Table 1': participants_sumnary.set_index(['characteristic']),
    'Table 2': measured_things.set_index(['measured_thing']),
    'Table 3': correlation_results,
    'Table 4': model_results,
}


# requires you to install `xlsxwriter`, e.g. with `pip` or `conda`
with ExcelWriter('Some_useful_name.xlsx', engine='xlsxwriter') as writer:
    for sheet_name, df in sheets.items():
        df.to_excel(writer, sheet_name=sheet_name)
        adjust_column_sizes(writer.sheets[sheet_name], df)