Add style to sheet-row from_dataframe()

[aecorn]

Our use-cases will always be passing a pandas dataframe to a sheet, edit, then pass back to a dataframe, which I want to df.compare() with itself prior to editing, to log changes. At the same time Id like to apply static styling to the rows, but the .from_dataframe() method seems to create sheets organized by columns, which is good I guess, as the typing of the columns (like checkboxing on bools), is a nice touch. So what Im after I guess is a method of applying styling to a row, dependant on one of the values in the row, on a sheet created from a dataframe, based on columns.

Code currently used below, commented out lines creates a sheet organized by rows instead, but misses the nice column-typing from from_dataframe(). df is a pandas dataframe, which has a column "Expired", which if True, should color the row blue.

sheet = ipysheet.from_dataframe(df.reset_index(drop = True))

#sheet = ipysheet.sheet(rows = len(df), columns = df.shape[1])
#sheet.column_headers = df.columns.tolist()
#for i, r in df.reset_index(drop = True).iterrows():
#    if r['Expired']:
#        bg = "blue"
#    else:
#        bg = ''
#    row = ipysheet.row(i, r.values, background_color = bg)

sheet[0, 0].style['backgroundColor'] = 'blue'
    
sheet

Currently calling sheet[0, 0].style['backgroundColor'] = 'blue' on a sheet .from_dataframe() results in this error:

IndexError                                Traceback (most recent call last)
<ipython-input-98-831b827d1f21> in <module>
     10     #print (i, r.values, r.values[-1])
     11 
---> 12 sheet[0, 0].style['backgroundColor'] = 'blue'
     13 
     14 sheet

/opt/conda/lib/python3.8/site-packages/ipysheet/sheet.py in __getitem__(self, item)
    121                and cell.row_end == row and cell.column_end == column:
    122                 return cell
--> 123         raise IndexError('no cell was previously created for (row, index) = (%s, %s)'.format(row, column))
    124 
    125 

IndexError: no cell was previously created for (row, index) = (%s, %s)```

[aecorn]

An alternative, would maybe be a .from_dataframe_styler(), method that would except a pandas styler object. With a companion .to_dataframe_styler() ? For example adding a lightblue background color to True values in column 3:

def color(s, width, column):
    if s.loc[column]:
        return ['background-color: lightblue'] * width
    else:
        return ['background-color: white'] * width

s = df.style.apply(color, width=df.shape[1], column=3, axis=1)

s is here a "styler"-object, not a pure dataframe any longer. And produces this error when being passed to .from_dataframe():

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-15-00908acb7031> in <module>
----> 1 sheet = ipysheet.from_dataframe(s)

/opt/conda/lib/python3.8/site-packages/ipysheet/pandas_loader.py in from_dataframe(dataframe)
     49     idx = 0
     50     for c in columns:
---> 51         arr = np.array(dataframe[c].values)
     52         cells.append(Cell(
     53             value=_get_cell_value(arr),

TypeError: 'Styler' object is not subscriptable

[LordHeImchen]

Hello @aecorn, I am currently trying to implement the exact same thing, did you find any solution yet?

My approach is to create the sheet from the dataframe, then pass the column with the checkboxes a renderer which at least colors the cell with the checkbox either green or red according to the value of the checkbox. The renderer is created as shown below:

def format_cells(value): return {"backgroundColor": "green" if value == True else "red", "read_only": False} ipysheet.renderer(code=format_cells, name="checked");

After that, i am creating the sheet and apply the renderer to the column with the checkboxes like this:

sheet = ipysheet.from_dataframe(df) sheet.cells[3].renderer = 'checked'

However, the renderer seems to override the checkboxes. When initially all checkboxes are "False", each cell is colored red (which is okay so far), but there is no checkbox anymore. Instead there is a string saying "false" in each cell of this column and the column is on read_only mode because i cannot modify it in the sheet, while every other column is modifiable. (This is the reason why i tried to assign a read_only property to False in the renderer, which unfortunately did not render any change)

Does anyone have an idea how to solve this problem and maybe even color the whole row depending on a checkbox being ticked - as @aecorn already suggested?