[Rdkit-devel] pandastool molecules -> excel

[Rex D. <RD...@me...>]

Hi,
I tried to use the rdkit.Chem.PandasTools.SaveXlsxFromFrame function, and found that it didn't work for me:

  *   It was only possible to have one column with molecules.
  *   It moved the molecule column to the left side of the spreadsheet.
  *   It did not preserve hyperlinks.
I'm sending along this alternative.  If someone wants to take it and run with it, that's great, and no need to give me any credit.
If not, I'm sorry, but my workload doesn't permit me to spend time to go through all the usual procedures for contributing.
The technical insight here is to rely on DataFrame.to_excel to fill the spreadsheet.  It preserves all the data (such as links) as stored in the dataframe.  Then the columns that need graphics can be modified after that.
I hope this is useful.
Cheers,
Rex


def to_excel_with_molecules(df, xlsx_name, mol_cols, sheet_name="Sheet1", size=(300, 300)):
    """
    Writes a dataframe into an excel spreadsheet, adding graphic of molecule to specified columns.
    @param df: dataframe to write
    @type df: dataframe
    @param xlsx_name: name of output excel file.  Must end ".xlsx"
    @type xlsx_name: str
    @param mol_cols: a column or list of columns with smiles strings to be expanded.
    If any element of the columns can't be parsed as as SMILES string, it will be left alone.
    @type mol_cols: str or list of str (actually, any iterable of str)
    @param sheet_name: The name of the sheet in the workbook.
    @type sheet_name: str
    @param size: (width,height) of images
    @type size: tuple (could be list)
    @return: None
    @rtype: NoneType
    """
    df = df.copy()

    if type(mol_cols) is str:
        mol_cols = [mol_cols]
    mol_cols = list(mol_cols)

    writer = pd.ExcelWriter(xlsx_name, engine="xlsxwriter")
    df.to_excel(writer, sheet_name=sheet_name, index=False)
    workbook = writer.book
    worksheet = writer.sheets[sheet_name]

    mol_cols = [i for i, col in enumerate(df.columns) if col in mol_cols]
    df.columns = list(range(len(df.columns)))

    for c in mol_cols:
        worksheet.set_column(c, c, width=size[0] / 6)
        for rr, smiles in enumerate(df[c]):
            try:
                r = rr + 1  # row 0 of df will be in row 1 of spreadsheet.
                image_data = BytesIO()
                img = MolToImage(MolFromSmiles(smiles), size=size)
                img.save(image_data, format="PNG")
                worksheet.set_row(r, height=size[1])
                worksheet.insert_image(r, c, "f", {"image_data": image_data})
            except:  # Some are expected to fail, e.g., None when there is no matching molecule.
                pass

    workbook.close()
    return

DISCLAIMER: This email and any file transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender or the system manager and delete or destroy this email and any attachment immediately.