CODEWITHSUNDEEP
htmlpython-3.xpandaspandas-styles
abdelgha4
abdelgha4

Reputation: 431

How to concatenate two pandas stylers that have the same columns?

I have two dataframes that have different styles but with the same columns.

This is a minimal example, with less data and simpler styles (I had some complicated highlighting instead of highlight_max)

import pandas as pd

data = pd.DataFrame({'maturity': ['2022-03-11', '2022-04-21', '2022-04-20', '2022-03-11', '2022-04-21'],
              'position': [-1500000, 2.3, -50, 10, -9],
              'price': [12, 51, 62, 10, 90000]})

data_dict = {'pos_data': data[data.position > 0], 'neg_data': data[data.position < 0]}
styled_df = {}
first = True
for name, df in data_dict.items():
    color = 'green' if 'pos' in name else 'red'
    header = pd.DataFrame({'position': [name]}, columns=df.columns)
    styled_df[name] = (
        pd.concat([header, df]).reset_index(drop=True)
                               .style.highlight_max(pd.IndexSlice[1:, 'position'], color=color, axis=0)
                                     .format(precision=2, na_rep='')
                                     .set_table_styles([{'selector': 'tbody td', 'props': [
                                         ('border-style', 'solid'), ('border-width', 'thin'), ('border-color', 'gray'), 
                                         ('border-collapse', 'collapse !important')]}], overwrite=False)
                                     .set_table_styles([{'selector': 'th', 'props': [
                                         ('border-style', 'solid'), ('border-width', 'thin'), 
                                         ('border-collapse', 'collapse !important')]}], overwrite=False)
                                     .set_table_attributes(
                                         'style="border-width: thin; border-collapse :collapse !important;'
                                         ' border-color:black; border-style: solid !important"')
                                     .hide_index()
    )
    if first:
        first = False
    else:
        styled_df[name] = styled_df[name].hide_columns()

email_body = f"<html><body> {''.join(s.to_html() for s in styled_df.values())} </body></html>"
# saving `email_body` to local and open gives:

Current Output:

Current Output

How I can make the two resulting tables share the same column width, (as if they were a concatenated dataframe) ?

Desired Output:

Desired Output

Edit:

What I meant by

some complicated highlighting instead of highlight_max

in my case is something like :

.style.apply(highlight_range, columns=cols_red, low=5,
             high=10, color='red', color_light='light_red', axis=1)
      .apply(highlight_range, columns=cols_blue, low=0,
             high=5,color='blue', color_light='light_blue', axis=1))

instead of .style.highlight_max(pd.IndexSlice[1:, 'position'], color=color, axis=0) above.

where highlight_range is:

def highlight_range(row, columns, low: int, high: int, color: str, color_light: str):
    is_between = pd.Series(data=False, index=row.index)
    is_between[columns] = row.loc[columns].between(low, high, inclusive='left') # <= row <
    if not is_between.any():
        return [''] * len(is_between)
    return [f'background-color: {color_light}' if e else f'background-color: {color}' for e in is_between]

Upvotes: 1

Views: 2391

Answers (1)

Henry Ecker
Henry Ecker

Reputation: 35626

The easiest way to have these two DataFrames appear as a single concatenated DataFrame would be to actually concatenate the two DataFrames then make a Styler. The following solution uses pandas 1.4.2 (the Styler can have significant variance between versions).

We can first compute the styles that we want to apply to the individual cells:

for name in data_dict:
    style_str = f'background-color: {"green" if name == "pos_data" else "red"}'
    # Manually compute styles
    data_dict[name]['style'] = np.where(
        data_dict[name]['position'] == data_dict[name]['position'].max(),
        style_str,
        ''
    )
    # Add Header Row
    data_dict[name] = pd.concat([
        pd.DataFrame({'position': [name]}, columns=data_dict[name].columns),
        data_dict[name]
    ])

Here we use np.where to determine if the current value matches the maximal value by comparing the individual values to the max. Anywhere this condition is true we fill with the style_str everywhere else gets an empty string.

The resulting DataFrames look like:

#pos_data
     maturity  position price                    style
0         NaN  pos_data   NaN                      NaN
1  2022-04-21       2.3    51                         
3  2022-03-11      10.0    10  background-color: green

# neg_data
     maturity   position  price                  style
0         NaN   neg_data    NaN                    NaN
0  2022-03-11 -1500000.0     12                       
2  2022-04-20      -50.0     62                       
4  2022-04-21       -9.0  90000  background-color: red

Notice that the styles correspond to each value.

Now we can just concat the DataFrames:

df = pd.concat(data_dict, ignore_index=True)

     maturity   position  price                    style
0         NaN   pos_data    NaN                      NaN
1  2022-04-21        2.3     51                         
2  2022-03-11       10.0     10  background-color: green
3         NaN   neg_data    NaN                      NaN
4  2022-03-11 -1500000.0     12                         
5  2022-04-20      -50.0     62                         
6  2022-04-21       -9.0  90000    background-color: red

Now it is trivial to use the style column of df to style the position column with apply:

df.style.apply(lambda _: df['style'], subset='position')

Trivially styled table

All together the styling could look something like:

styler = df.style
# Hide the "style" column
styler.hide(axis='columns', subset='style')
# styler.hide_columns(subset='style') pre pandas 1.4.0

# Hide the index
styler.hide(axis='index')
# styler.hide_index() pre pandas 1.4.0

# Apply the values from the style column as the styles
styler.apply(lambda _: df['style'], subset='position')

# Format decimal places and replace NaN
styler.format(precision=2, na_rep='')

# Table styles
styler.set_table_styles([
    {
        'selector': 'tbody td',
        'props': [
            ('border-style', 'solid'), ('border-width', 'thin'),
            ('border-color', 'gray'), ('border-collapse', 'collapse !important')
        ]
    },
    {
        'selector': 'th',
        'props': [
            ('border-style', 'solid'), ('border-width', 'thin'),
            ('border-collapse', 'collapse !important')
        ]
    }
])
# Table attributes
styler.set_table_attributes(
    'style="border-width: thin; border-collapse :collapse !important;'
    ' border-color:black; border-style: solid !important"'
)

Fully styled table

As of pandas 1.3.0, we can get complete HTML from Styler objects with Styler.to_html

email_body = styler.to_html(doctype_html=True)

This generates the following HTML/CSS:

<!DOCTYPE html>
<html>

<head>
  <meta charset="utf-8">
  <style type="text/css">
    #T_7da9e tbody td {
      border-style: solid;
      border-width: thin;
      border-color: gray;
      border-collapse: collapse !important;
    }
    
    #T_7da9e th {
      border-style: solid;
      border-width: thin;
      border-collapse: collapse !important;
    }
    
    #T_7da9e_row2_col1 {
      background-color: green;
    }
    
    #T_7da9e_row6_col1 {
      background-color: red;
    }
  </style>
</head>

<body>
  <table id="T_7da9e" style="border-width: thin; border-collapse :collapse !important; border-color:black; border-style: solid !important">
    <thead>
      <tr>
        <th id="T_7da9e_level0_col0" class="col_heading level0 col0">maturity</th>
        <th id="T_7da9e_level0_col1" class="col_heading level0 col1">position</th>
        <th id="T_7da9e_level0_col2" class="col_heading level0 col2">price</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td id="T_7da9e_row0_col0" class="data row0 col0"></td>
        <td id="T_7da9e_row0_col1" class="data row0 col1">pos_data</td>
        <td id="T_7da9e_row0_col2" class="data row0 col2"></td>
      </tr>
      <tr>
        <td id="T_7da9e_row1_col0" class="data row1 col0">2022-04-21</td>
        <td id="T_7da9e_row1_col1" class="data row1 col1">2.30</td>
        <td id="T_7da9e_row1_col2" class="data row1 col2">51</td>
      </tr>
      <tr>
        <td id="T_7da9e_row2_col0" class="data row2 col0">2022-03-11</td>
        <td id="T_7da9e_row2_col1" class="data row2 col1">10.00</td>
        <td id="T_7da9e_row2_col2" class="data row2 col2">10</td>
      </tr>
      <tr>
        <td id="T_7da9e_row3_col0" class="data row3 col0"></td>
        <td id="T_7da9e_row3_col1" class="data row3 col1">neg_data</td>
        <td id="T_7da9e_row3_col2" class="data row3 col2"></td>
      </tr>
      <tr>
        <td id="T_7da9e_row4_col0" class="data row4 col0">2022-03-11</td>
        <td id="T_7da9e_row4_col1" class="data row4 col1">-1500000.00</td>
        <td id="T_7da9e_row4_col2" class="data row4 col2">12</td>
      </tr>
      <tr>
        <td id="T_7da9e_row5_col0" class="data row5 col0">2022-04-20</td>
        <td id="T_7da9e_row5_col1" class="data row5 col1">-50.00</td>
        <td id="T_7da9e_row5_col2" class="data row5 col2">62</td>
      </tr>
      <tr>
        <td id="T_7da9e_row6_col0" class="data row6 col0">2022-04-21</td>
        <td id="T_7da9e_row6_col1" class="data row6 col1">-9.00</td>
        <td id="T_7da9e_row6_col2" class="data row6 col2">90000</td>
      </tr>
    </tbody>
  </table>
</body>

</html>

Setup and imports used.

import numpy as np
import pandas as pd

data = pd.DataFrame({
    'maturity': ['2022-03-11', '2022-04-21', '2022-04-20', '2022-03-11',
                 '2022-04-21'],
    'position': [-1500000, 2.3, -50, 10, -9],
    'price': [12, 51, 62, 10, 90000]
})

data_dict = {
    # Adding copy to prevent a later SettingWithCopyWarning
    'pos_data': data[data.position > 0].copy(),
    'neg_data': data[data.position < 0].copy()
}

Upvotes: 1

Related Questions