-1

I have a data frame that contains the std, mean and median for several chemical elements. Sample data:

test = pd.DataFrame({('Na', 'std'):{'A': 1.73, 'B':0.95, 'C':2.95}, ('Na', 'mean'):{'A': 10.3, 'B':11, 'C':20}, ('Na', 'median'):{'A':11, 'B':22, 'C':34},('K', 'std'):{'A': 1.33, 'B':1.95, 'C':2.66}, ('K', 'mean'):{'A': 220.3, 'B':121, 'C':290}, ('K', 'median'):{'A':211, 'B':122, 'C':340}})

Example of table:

    Na                  K
    std     mean    med std     mean    med
A   1.73    10.3    11  1.33    220.3   211
B   0.95    11.0    22  1.95    121.0   122
C   2.95    20.0    34  2.66    290.0   340

I want to paint the cells following certain conditions:

  1. I would like to color the two smallest values in the std column for each chemical element (Example: 0.95 and 1.73 for Na, and 1.33 and 1.95 for K);
  2. I would like to color the mean and median columns based on the two smallest values of the function [abs(mean - median)], for all the elements (Example: (10.3, 11) and (11.0, 22) for Na, and (220.3, 211) and (121, 122) for K).

I made these functions to identify the values of cells to be painted following the conditions I want, but I don't know how to implement them in the pd.style function.

def paint1(test):
  val_keep = []
  for element,stats in test:
    if stats == 'std':
      paint1 = test[element].nsmallest(2, 'std')
      for value in paint1['std']:
        val_keep.append(value)
  return val_keep

def paint2(test):
val_keep = []
  for element,stats in test:
    if stats == 'mean':
      diff = abs(test[element]['mean'] - test[element]['median'])
      paint2 = diff.nsmallest(2).index
      for value in paint2:
        val_keep.append((test[element]['mean'][value]))
        val_keep.append(test[element]['median'][value])
  return val_keep

How can I paint the cells using these conditions? I saw other posts using lambda functions to define the styling, but I think the functions I need are more complicated than that.

Eduardo Valentin dos Santos
  • 181
  • 13
  • You forgot to ask a question it seems. –  Feb 08 '22 at 19:25
  • Sorry, I edited the text with the question – Eduardo Valentin dos Santos Feb 08 '22 at 19:27
  • It's hard to copy data from image. It helps people here if they have accessible data. They explain here https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples. TL;DR `print(df.to_dict())`. –  Feb 08 '22 at 19:38
  • 1
    It is very difficult to answer your question without seeing any of your data nor any of the code that you have written that produces your problem. Please review Jon Skeet's excellent blog post [WRITING THE PERFECT QUESTION](https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) for guidance in updating your question. For a more detailed tutorial on asking a good question see Eric S. Raymond's site [How To Ask Questions The Smart Way](http://www.catb.org/~esr/faqs/smart-questions.html) – itprorh66 Feb 08 '22 at 19:39
  • I edited and added the sample data in dict format and the function I'm trying to implement in the pd.style function. – Eduardo Valentin dos Santos Feb 08 '22 at 19:53

1 Answers1

1

Use df.style:

def styler(col):
    chem, stat = col.name
    if stat == 'std':
        return np.where(col.isin(col.nsmallest(2)), 'color: red', '')
    elif stat in ['mean', 'median']:
        delta = (df[(chem, 'mean')] - df[(chem, 'median')]).abs()
        return np.where(delta.isin(delta.nsmallest(2)), 'color: blue', '')
    else:
        return [''] * len(col)

df.style.apply(styler)
Code Different
  • 90,614
  • 16
  • 144
  • 163