Hi @AnnMarieW can you make an example about dynamic columns grouping? In your example, we need to define headers name and children, but if I have a multi index pivot table, how can I show it on AG Grid. Thank you.
Hello @hoatran,
Can you provide a pivoted table with multiple indexes?
It’s always easier to have a jumping point. 
Hi @jinnyzor, for example:
import pandas as pd
import numpy as np
import dash_ag_grid as dag
from dash import Dash, html, dcc
#create DataFrame
df = pd.DataFrame({'name': ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'g'],
'school': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'],
'Date' : ['20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809'],
'points': [2800, 1700, 1900, 1400, 2300, 2600, 5000, 10000, 15000],
'rebounds': [5000, 6000, 4000, 7000, 14000, 12000, 9000, 3000, 9000],
'assists': [10000, 13000, 7000, 8000, 4000, 5000, 8000, 10000, 7000]})
df_pivot = pd.pivot_table(df, values='points', index=['Date'], columns = ['school', 'name'], aggfunc=np.sum).reset_index()
A pivot deviates from the typical structure, so will be interesting for sure.
1 Like
Hi @hoatran
You can find more info in the AG Grid docs since there are not too many examples in the dash docs yet:
Here’s an example using the data you provided (Thanks that was helpful):
import pandas as pd
import numpy as np
import dash_ag_grid as dag
from dash import Dash, html, dcc
#create DataFrame
df = pd.DataFrame({'name': ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'g'],
'school': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'],
'Date' : ['20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809'],
'points': [2800, 1700, 1900, 1400, 2300, 2600, 5000, 10000, 15000],
'rebounds': [5000, 6000, 4000, 7000, 14000, 12000, 9000, 3000, 9000],
'assists': [10000, 13000, 7000, 8000, 4000, 5000, 8000, 10000, 7000]})
df_pivot = pd.pivot_table(df, values='points', index=['Date'], columns = ['school', 'name'], aggfunc=np.sum).reset_index()
app = Dash(__name__)
columnDefs = [
{"field": "school", "pivot": True},
{"field": "name", "pivot": True},
{"field": "Date", "rowGroup": True},
{"field": "points", "aggFunc": "sum"},
{"field": "rebounds"},
{"field": "assists"},
]
defaultColDef = {
"flex": 1,
"minWidth": 150,
}
app.layout = html.Div(
[
dag.AgGrid(
id="pivot-ag-grid-example",
columnDefs=columnDefs,
rowData=df.to_dict("records"),
dashGridOptions={
"suppressExpandablePivotGroups": True,
"pivotMode": True,
},
defaultColDef=defaultColDef,
# Pivot groupings is an ag-grid Enterprise feature.
# A license key should be provided if it is used.
# License keys can be passed to the `licenseKey` argument of dag.AgGrid
enableEnterpriseModules=True,
),
]
)
if __name__ == "__main__":
app.run(debug=True)
3 Likes
Thank you for detail answer. I will try it.
1 Like
Here is a working example of a community one, where you pivot it in python and just display the results:
import pandas as pd
import numpy as np
import dash_ag_grid as dag
from dash import Dash, html, dcc
#create DataFrame
df = pd.DataFrame({'name': ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'g'],
'school': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'],
'Date': ['20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809'],
'points': [2800, 1700, 1900, 1400, 2300, 2600, 5000, 10000, 15000],
'rebounds': [5000, 6000, 4000, 7000, 14000, 12000, 9000, 3000, 9000],
'assists': [10000, 13000, 7000, 8000, 4000, 5000, 8000, 10000, 7000]})
df_pivot = pd.pivot_table(df, values='points', index=['Date'], columns = ['school', 'name'], aggfunc=np.sum).reset_index()
app = Dash(__name__)
used = []
def buildChildren(list):
newColumnDef = {}
for i in range(len(list)):
newColumnDef['field'] = list[i]
if i == len(list) - 1:
newColumnDef['field'] = list[i]
return newColumnDef
else:
newColumnDef['children'] = [buildChildren(list[1:])]
used.append(list[i])
return newColumnDef
columnDefs = []
columnDefs.append(buildChildren(df_pivot.columns.names))
renameColumns = {}
for col in df_pivot.columns:
if col[-1] == '':
columnDefs.append({'field': col[0]})
renameColumns[str(col)] = col[0]
else:
renameColumns[str(col)] = col[-1]
if col[0] in used:
for column in columnDefs:
if col[0] == column['field']:
column['children'].append(buildChildren(list(col[1:])))
else:
columnDefs.append(buildChildren(list(col)))
defaultColDef = {
"flex": 1,
"minWidth": 150,
}
df_adjusted = pd.DataFrame(df_pivot.to_records()).rename(columns=renameColumns)
app.layout = html.Div(
[
dag.AgGrid(
id="pivot-ag-grid-example",
columnDefs=columnDefs,
rowData=df_adjusted.to_dict("records"),
defaultColDef=defaultColDef,
),
]
)
if __name__ == "__main__":
app.run(debug=True)
A little more robust:
import pandas as pd
import numpy as np
import dash_ag_grid as dag
from dash import Dash, html, dcc
#create DataFrame
df = pd.DataFrame({'name': ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'g'],
'school': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'],
'Date': ['20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809'],
'points': [2800, 1700, 1900, 1400, 2300, 2600, 5000, 10000, 15000],
'rebounds': [5000, 6000, 4000, 7000, 14000, 12000, 9000, 3000, 9000],
'assists': [10000, 13000, 7000, 8000, 4000, 5000, 8000, 10000, 7000]})
df_pivot = pd.pivot_table(df, values=['points', 'rebounds', 'assists'], index=['Date'], columns=['school', 'name'], aggfunc=np.sum).reset_index()
app = Dash(__name__)
used = []
def buildChildren(list, base = None):
newColumnDef = {}
for i in range(len(list)):
newColumnDef['field'] = list[i]
if i == len(list) - 1:
if base:
newColumnDef['field'] = list[i] + '-' + base
return newColumnDef
else:
newColumnDef['children'] = [buildChildren(list[1:], base)]
used.append(list[i])
return newColumnDef
columnDefs = []
columnDefs.append(buildChildren(df_pivot.columns.names))
renameColumns = {}
for col in df_pivot.columns:
if col[-1] == '':
columnDefs.append({'field': col[0]})
renameColumns[str(col)] = col[0]
else:
renameColumns[str(col)] = col[-1] + '-' + col[0]
if col[0] in used:
for column in columnDefs:
if col[0] == column['field']:
column['children'].append(buildChildren(list(col[1:]), col[0]))
else:
columnDefs.append(buildChildren(list(col), col[0]))
defaultColDef = {
"flex": 1,
"minWidth": 150,
}
df_adjusted = pd.DataFrame(df_pivot.to_records()).rename(columns=renameColumns)
app.layout = html.Div(
[
dag.AgGrid(
id="pivot-ag-grid-example",
columnDefs=columnDefs,
rowData=df_adjusted.to_dict("records"),
defaultColDef=defaultColDef,
),
]
)
if __name__ == "__main__":
app.run(debug=True)
renaming header to the player name:
import pandas as pd
import numpy as np
import dash_ag_grid as dag
from dash import Dash, html, dcc
#create DataFrame
df = pd.DataFrame({'name': ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'g'],
'school': ['A', 'A', 'A', 'B', 'B', 'B', 'C', 'C', 'C'],
'Date': ['20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809', '20230809'],
'points': [2800, 1700, 1900, 1400, 2300, 2600, 5000, 10000, 15000],
'rebounds': [5000, 6000, 4000, 7000, 14000, 12000, 9000, 3000, 9000],
'assists': [10000, 13000, 7000, 8000, 4000, 5000, 8000, 10000, 7000]})
df_pivot = pd.pivot_table(df, values=['points', 'rebounds', 'assists'], index=['Date'], columns=['school', 'name'],
aggfunc=np.sum).reset_index()
app = Dash(__name__)
used = []
def buildChildren(list, base = None):
newColumnDef = {}
for i in range(len(list)):
newColumnDef['field'] = list[i]
if i == len(list) - 1:
if base:
newColumnDef['field'] = list[i] + '-' + base
newColumnDef['headerName'] = list[i]
return newColumnDef
else:
newColumnDef['children'] = [buildChildren(list[1:], base)]
used.append(list[i])
return newColumnDef
columnDefs = []
columnDefs.append(buildChildren(df_pivot.columns.names))
renameColumns = {}
for col in df_pivot.columns:
if col[-1] == '':
columnDefs.append({'field': col[0]})
renameColumns[str(col)] = col[0]
else:
renameColumns[str(col)] = col[-1] + '-' + col[0]
if col[0] in used:
for column in columnDefs:
if col[0] == column['field']:
column['children'].append(buildChildren(list(col[1:]), col[0]))
else:
columnDefs.append(buildChildren(list(col), col[0]))
defaultColDef = {
"flex": 1,
"minWidth": 150,
}
df_adjusted = pd.DataFrame(df_pivot.to_records()).rename(columns=renameColumns)
app.layout = html.Div(
[
dag.AgGrid(
id="pivot-ag-grid-example",
columnDefs=columnDefs,
rowData=df_adjusted.to_dict("records"),
defaultColDef=defaultColDef,
),
]
)
if __name__ == "__main__":
app.run(debug=True)
1 Like
Thank you so much, I think I just need the first one but your 3 three examples are incredible.
1 Like