Conditional formatting in flextable based on percentage values

  • Conditional formatting in flextable based on percentage values

     clint.thomson updated 3 weeks, 5 days ago 2 Members · 3 Posts
  • clint.thomson

    Member
    November 4, 2020 at 3:22 pm

    Hello,

    Well, after David’s first office hours where he covered parameterized reporting, I’ve taken on the task myself with great success!

    After some feedback from colleagues, I’ve been asked to apply some conditional formatting to indicate high and low performance flags in my tables.

    I have attached a Word mockup of what I’m hoping to perform in Markdown. The second column indicates the conditional formatting I’ve been asked to add. I am knitting to Word from Markdown using the flextable and officer packages.

    I’m wondering if anyone could suggest how I might implement a color scheme of >= 75% (blue), 50-74% (yellow), and < 50% (red)? Is it possible to have colored flags appear in the adjacent cells? Note that the formatting is based only on the value in the “program agree” column, not “department agree”.

    I also note that percentage values are stored as characters. For example, the values in the rds file I imported into markdown are stored as single characters (i.e. “78% (n=45)” is an entry for a single cell). It seems this could make conditional formatting in Markdown somewhat more difficult. Would it be better to import numeric values into Markdown (i.e. “.78”?)

    Any insights or suggestions on this would be greatly appreciated.

    Thank you!

  • David

    Organizer
    November 5, 2020 at 7:50 am

    Great questions, Clint!

    On conditional formatting, I’d suggest checking out this section of the flextable documentation.

    On the second question, you almost certainly want to use multiple values here. In addition to the % program agree column, you’ll want to have a separate column for the n as well as for percent that is numeric. I put together some code to demonstrate how this works. See here.

    I’ll plan to go over this in office hours on Friday as well. Hope you can join!

    • clint.thomson

      Member
      November 5, 2020 at 1:41 pm

      Thanks for this, David. The col_keys feature seems to enable you to perform the formatting on columns from the data frame that you don’t actually want to include in the flextable. Good to know. I’ll see you at office hours tomorrow!

Log in to reply.

Original Post
0 of 0 posts June 2018
Now
The R for the Rest of Us community is live! Join regular office hours, ask questions in the forum, and more!