How to format dash datatable in a transposed dataframe?

Dash Python
1

Hello guys,

I have a DataFrame and I would like to apply some format styles to it… But the problem is that as I need to transpose the table and the value IDs are the columns and the “original” columns are now presented in the rows, as you can see in the image below:

image
image984×296 13.4 KB

I would like to find a way to format the values as following:
Hours Col should be 1,253 instead of 1253
Value Col should be $ 31,950,000 instead of 31950000

Below is a Minimal Reproducible Example to help you to reproduce and understand the problem I’m facing;

from dash import html, Dash, dash_table
import pandas as pd

list_of_vals=[
    {'Val ID': '9729', 'Year Col': '2017', 'Hours Col': 1253.0, 'Value Col': 31950000.0}, 
    {'Val ID': '9733', 'Year Col': '2016', 'Hours Col': 2541.0, 'Value Col': 29995000.0},
    {'Val ID': '9837', 'Year Col': '2019', 'Hours Col': 566.0, 'Value Col': 34900000.0},
    {'Val ID': '9507', 'Year Col': '2013', 'Hours Col': 2732.0, 'Value Col': 26000000.0},
    {'Val ID': '9805', 'Year Col': '2018', 'Hours Col': 1543.0, 'Value Col': 31500000.0}
]

df = pd.DataFrame(list_of_vals)
df = df.set_index("Val ID").T
df = df.reset_index().rename(columns={"index": "Val ID"})
data_list = [item for item in df.to_dict(orient="records")]

columns_list = list(df.columns)
app = Dash(
    __name__,
    title="Data table test",
)
cond_to_index = ["Val ID"]

app.layout = html.Div(
    [
        html.H2("Data Table Test"),
        dash_table.DataTable(
        data_list,
        [
            {
                "name": i,
                "id": i,
                "editable": True if i != "Val ID" else False,
            }
            for (n, i) in enumerate(columns_list)
        ],
        # fixed_columns={"headers": True, "data": 1},
        style_header={"fontWeight": "bold"},
        style_table={"width": "100%"},
        style_header_conditional=[
            {
                "if": {"column_id": col},
                "textAlign": "left" if col == "Val ID" else "center",
                "fontSize": "14px",
            }
            for col in columns_list
        ],
        style_data_conditional=[
            {
                "if": {"column_id": col},
                "fontWeight": "bold" if col in cond_to_index else "normal",
                "width": "250px" if col in cond_to_index else "150px",
                "minWidth": "250px" if col in cond_to_index else "150px",
                "maxWidth": "250px" if col in cond_to_index else "150px",
                "textAlign": "left" if col in cond_to_index else "center",
                "fontSize": "12px",
                "paddingLeft": "4px" if col in cond_to_index else "0px",
            }
            for col in columns_list
        ],
        id="test-table",
    )], style={"maxWidth":"500px"}
)

if __name__ == "__main__":
    app.run_server(debug=True)

Does someone have any experience regarding this issue?

2

Hi @kabure

With DataTable, it’s not possible to format data by row. You would have to make the numeric data strings and format the data before it’s sent to the table. (Which isn’t great if it’s editable, like in your example.

However, this is possible to do with Dash AG Grid. Here’s an example:

from dash import html, Dash
import dash_ag_grid as dag
import pandas as pd

list_of_vals = [
    {
        "Val ID": "9729",
        "Year Col": "2017",
        "Hours Col": 1253.0,
        "Value Col": 31950000.0,
    },
    {
        "Val ID": "9733",
        "Year Col": "2016",
        "Hours Col": 2541.0,
        "Value Col": 29995000.0,
    },
    {"Val ID": "9837", "Year Col": "2019", "Hours Col": 566.0, "Value Col": 34900000.0},
    {
        "Val ID": "9507",
        "Year Col": "2013",
        "Hours Col": 2732.0,
        "Value Col": 26000000.0,
    },
    {
        "Val ID": "9805",
        "Year Col": "2018",
        "Hours Col": 1543.0,
        "Value Col": 31500000.0,
    },
]

df = pd.DataFrame(list_of_vals)
df = df.set_index("Val ID").T
df = df.reset_index().rename(columns={"index": "Val ID"})
data_list = [item for item in df.to_dict(orient="records")]
columns_list = list(df.columns)

app = Dash()

app.layout = html.Div(
    [
        dag.AgGrid(
            rowData=data_list,
            columnDefs=[{"field": "Val ID", "editable": False}]
            + [
                {
                    "field": c,
                    "valueFormatter": {"function": "FormatNumbersByRow(params)"},
                    "type": "numericColumn",
                    "cellDataType": "number",
                }
                for c in columns_list
                if c != "Val ID"
            ],
            defaultColDef={"editable": True, "flex": 1},
            dashGridOptions={
                "undoRedoCellEditing": True,
                "undoRedoCellEditingLimit": 20,
            },
        )
    ]
)


if __name__ == "__main__":
    app.run_server(debug=True)

Put this is the dashAgGridFunctions.js file in /assets

var dagfuncs = (window.dashAgGridFunctions = window.dashAgGridFunctions || {});

dagfuncs.Intl = Intl;

dagfuncs.FormatNumbersByRow = function(params) {   
    if (params.data["Val ID"] == "Hours Col") {
        return Intl.NumberFormat("en-US").format(params.value);
    }
    if (params.data["Val ID"] == "Value Col") {
        return Intl.NumberFormat("en-US", {
            style: "currency",
            currency: "USD",
            minimumFractionDigits: 0,
            maximumFractionDigits: 0,
          }).format(params.value);
    }
}

If you are new to AG Grid, you might find this article helpful:

1 Like
3

Hello @AnnMarieW thank you so much for this example;

Do you know to tell me if there’s a way to not allow the user to edit specific rows?

I tried to reproduce the same logic of your example, and it doesn’t work as expected :confused:

image
image718×462 48 KB

One of the requirements I have is that some of the rows can’t be edited/inputted.

So, based on my example above, let’s say that the user should not be able to edit “Year Col” row… Is it possible?

Thank you in advance!

4

Sure - add this editable function to the column defs:


               {
                    "field": c,
                    "valueFormatter": {"function": "FormatNumbersByRow(params)"},
                    "type": "numericColumn",
                    "cellDataType": "number",
                    "editable": {"function": "params.data['Val ID'] !== 'Year Col'"},
                }

More info here in the Conditional Editing section:

1 Like
5

Thank you very much @AnnMarieW you’re awesome

1 Like