Getting KeyError when saving style multiindex pandas to excel

Question

When saving a df that has been style into excel, compiler return

KeyError: "['A'] not in index"

import pandas as pd
import numpy as np


def color_negative_red(value):
  if value < 0:
    color = 'red'
  elif value > 0:
    color = 'green'
  else:
    color = 'black'

  return 'color: %s' % color

np.random.seed(0)
arrays = [np.hstack([['One']*2, ['Two']*2]) , ['A', 'B', 'A', 'B']]
columns = pd.MultiIndex.from_arrays(arrays)
df=  pd.DataFrame(np.random.randn(5, 4), columns=list('ABCD'))
df.columns = columns
data=df.style.applymap(color_negative_red, subset=['A'])

data.to_excel('test.xlsx')

Expected output

P.S.: Similar post has been submitted to pandas git: https://github.com/pandas-dev/pandas/issues/42906 for a potential bug

Attack68 · Accepted Answer · 2021-08-06 06:17:49Z

1

This is nothing to do with Styler.to_excel. It is to do with the fact you are using a MultiIndex columns and not supplying a subset in a valid format.

As advised in the docs you must supply a valid .loc indexer.

The solution is to use:

df.style.applymap(color_negative_red, subset=(slice(None), (slice(None), 'A')))

or pandas Indexer if you prefer (these solutions are isomorphic)

ix = pd.IndexSlice
df.style.applymap(color_negative_red, subset=ix[:, ix[:, 'A']])

Note your output will affect only the text color because you have not used the background-color CSS attribute.

answered Aug 6, 2021 at 6:17

Attack68

4,8114 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Corralien · Accepted Answer · 2021-08-05 16:51:13Z

0

Can you try that:

df.style.applymap(color_negative_red, subset=([slice(None), "A"]))

answered Aug 5, 2021 at 16:51

Corralien

121k8 gold badges44 silver badges69 bronze badges

1 Comment

rpb Over a year ago

Thanks for dropping by @Coralien. Compiler throw :NotImplementedError: Index._join_level on non-unique index is not implemented when implementing you suggestion

Collectives™ on Stack Overflow

Getting KeyError when saving style multiindex pandas to excel

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related