Pandas apply().to_excel() got DataFrame is not callable

user_7552329 0
Viewed 269
    empty_stock_list = [
    {
      'row_index': <num>,
      'column_index': <num>
    },
    ...
    ]

    with pd.ExcelWriter(OUTPUT_FILE, engine='xlsxwriter') as writer:
        df = pd.concat([header_row, data_price], ignore_index=False, sort=False).reset_index(drop=True)
        
        df_color = df.copy()
        df_color.iloc[:,:] = 'font-color: black'
        for empty_stock in empty_stock_list:
            df_color.iloc[empty_stock['row_index'], empty_stock['column_index']] = 'font-color: #FF0000'

        df.style.apply(df_color, axis=None).\
            to_excel(writer, sheet_name=sheet_name, index=False, header=None)

I have this code above, but always get this error: TypeError: 'DataFrame' object is not callable. Basically what I'm trying to do is to make the cell color into a red color if a stock is empty (Based on the data row_index and column_index).

Tried to follow the documentation, but I can't seem to make this right.

Below is the traceback error messages:

Traceback (most recent call last):
  File "main.py", line 129, in <module>
    df.style.apply(df_color, axis=None).\
  File "/home/michaelharley/.local/lib/python3.8/site-packages/pandas/io/formats/style.py", line 229, in to_excel
    formatter.write(
  File "/home/michaelharley/.local/lib/python3.8/site-packages/pandas/io/formats/excel.py", line 734, in write
    writer.write_cells(
  File "/home/michaelharley/.local/lib/python3.8/site-packages/pandas/io/excel/_xlsxwriter.py", line 212, in write_cells
    for cell in cells:
  File "/home/michaelharley/.local/lib/python3.8/site-packages/pandas/io/formats/excel.py", line 688, in get_formatted_cells
    for cell in itertools.chain(self._format_header(), self._format_body()):
  File "/home/michaelharley/.local/lib/python3.8/site-packages/pandas/io/formats/excel.py", line 590, in _format_regular_rows
    for cell in self._generate_body(coloffset):
  File "/home/michaelharley/.local/lib/python3.8/site-packages/pandas/io/formats/excel.py", line 674, in _generate_body
    styles = self.styler._compute().ctx
  File "/home/michaelharley/.local/lib/python3.8/site-packages/pandas/io/formats/style.py", line 625, in _compute
    r = func(self)(*args, **kwargs)
  File "/home/michaelharley/.local/lib/python3.8/site-packages/pandas/io/formats/style.py", line 642, in _apply
    result = func(data, **kwargs)
TypeError: 'DataFrame' object is not callable

I'm using these dependencies:

  • python 3.8.0
  • pandas 1.1.3
  • xlrd 1.2.0
  • XlsxWriter 1.3.7
xlsxwriter pandas dataframe excel python
1 Answers

I think you need create function and pass to Styler.apply and change font-color to color:

def func(df):

    df_color = pd.DataFrame('color: black', index=df.index, columns=df.columns)
    
    for empty_stock in empty_stock_list:
        i = empty_stock['row_index']
        j = empty_stock['column_index']
        df_color.iloc[i,j] = 'color: #FF0000'
    return df_color


with pd.ExcelWriter(OUTPUT_FILE, engine='xlsxwriter') as writer:
    df = pd.concat([header_row, data_price], 
                    ignore_index=False, 
                    sort=False).reset_index(drop=True)
        
    (df.style.apply(func, axis=None)
       .to_excel(writer, sheet_name=sheet_name, index=False, header=None))
user_29010020
Related