I have the following replicable code:
import streamlit as st
import pandas as pd
import streamlit.components.v1 as components
import st_aggrid as st_ag
import numpy as np
from datetime import datetime
from st_aggrid import JsCode
from st_aggrid import AgGrid, GridOptionsBuilder, GridUpdateMode, DataReturnMode
from st_aggrid.shared import GridUpdateMode
from streamlit_modal import Modal
data = {
'Categoria': ['Receita', 'Receita', 'Gastos', 'Gastos', 'Lucro'],
'Subcategoria': ['Mercadorias', 'Serviços', 'Custos', 'Despesas', 'Total'],
'jan-2023': [10000, 5000, 4000, 4000, 7000],
'fev-2023': [12000, 6000, 5000, 4500, 8500],
'jan~fev-2023':[0.2,0.2,0.25,0.13,0.21],
'mar-2023': [11000, 6500, 4700, 4500, 8300],
'abr-2023': [13000, 7000, 5500, 4500, 10000]
}
df = pd.DataFrame(data)
def gerar_jscode_formatacao_linha(lista_formatacao):
condicoes = [x]
for coluna, valor, cor in lista_formatacao:
condicao = f"""
if (params.node.group && params.node.key === '{valor}') {{
return {{'background-color': '{cor}', 'font-weight': 'bold'}};
}} else if (params.data && params.data['{coluna}'] === '{valor}') {{
return {{'background-color': '{cor}', 'font-weight': 'bold'}};
}}
"""
condicoes.append(condicao)
condicoes_str = ' else '.join(condicoes)
js_code = JsCode(f"""
function(params) {{
{condicoes_str}
return null;
}};
""")
return js_code
def Tabela(df, clsValor=None, lts=None, fixedColumns=None, ListaFormatoPercet=None, listaFormatacaoAdicional=None, parNumeroLinhas=None, colunaTotal=None, formatarLinhas = None, mesclarLinhas = None):
formatoValor = st_ag.JsCode("""
function(params){
return (params.value == null) ? params.value: params.value.toLocaleString('pt-BR',{'style': 'currency', 'currency':'BRL'});
}
""")
formato_percent = st_ag.JsCode("""
function(params){
return (params.value == null) ? params.value: params.value.toLocaleString('pt-BR',{'style': 'percent','minimumFractionDigits':2});
}
""")
def format_currency(df, columns_to_format, rows_to_format):
gb = GridOptionsBuilder.from_dataframe(df, enableRowGroup=True, enableValue=True, enablePivot=True)
# Converter o conjunto de linhas para uma lista
rows_list = list(rows_to_format)
# Converter a lista para uma string no formato JavaScript
rows_js = str(rows_list)
for column in columns_to_format:
gb.configure_column(
column,
type=["numericColumn"],
valueFormatter=JsCode(f"""
function(params) {{
const rows = {rows_js};
if (rows.includes(params.node.rowIndex)) {{
return new Intl.NumberFormat('pt-BR', {{
style: 'currency',
currency: 'BRL'
}}).format(params.value);
}} else {{
return params.value;
}}
}}
""")
)
return gb
def merge_column_values(df, column_name):
df[column_name] = df[column_name].mask(df[column_name].duplicated(), "")
return df
if parNumeroLinhas is None:
numeroLinhas = 10
else:
numeroLinhas = len(df[parNumeroLinhas].unique())
if numeroLinhas > 30:
numeroLinhas = 30
if mesclarLinhas is not None:
df = merge_column_values(df, mesclarLinhas)
if formatarLinhas is not None:
options = format_currency(df, formatarLinhas[0],formatarLinhas[1])
else:
options = GridOptionsBuilder.from_dataframe(df, enableRowGroup=True, enableValue=True, enablePivot=True)
options.configure_side_bar()
if clsValor is not None:
for col in clsValor:
options.configure_column(col, aggFunc='sum', valueFormatter=formatoValor, columnGroupShow='open')
if ListaFormatoPercet is not None:
for col in ListaFormatoPercet:
options.configure_column(col, aggFunc='sum', valueFormatter=formato_percent, columnGroupShow='open')
options.configure_selection('multiple', use_checkbox=True)
if fixedColumns is not None:
options.configure_default_column(editable=True)
for i, col in enumerate(fixedColumns):
options.configure_column(col, rowGroup=True, hide=True, rowGroupIndex=i, pinned='left')
if colunaTotal is not None:
for coluna in colunaTotal:
options.configure_column(coluna, pinned='right')
if listaFormatacaoAdicional is not None:
try:
jscode = gerar_jscode_formatacao_linha(listaFormatacaoAdicional)
options.configure_grid_options(getRowStyle=jscode)
except:
pass
options.configure_grid_options(suppressAggFuncInHeader=True, groupDefaultExpanded=True, suppressHorizontalScroll=True)
options.configure_grid_options(domLayout='autoHeight')
gb = options.build()
gb['autoGroupColumnDef'] = {'headerName': 'Grupos',
'cellRendererParams': {'suppressCount': True},
'field': 'Grupo',
'pinned': 'left'}
df = df.loc[~((df.select_dtypes(include=[np.number]).fillna(0) == 0).all(axis=1))]
selection = AgGrid(df, enable_enterprise_modules=True, gridOptions=gb,
height=(60 + 30 * numeroLinhas),
update_mode=GridUpdateMode.MODEL_CHANGED,
columns_auto_size_mode=True,
allow_unsafe_jscode=True)
fixed_columns = ['Categoria','Subcategoria']
listaFormatoValor = ['jan-2023', 'fev-2023','mar-2023','abr-2023']
percent = ['jan~fev-2023']
Tabela(df,
fixedColumns = fixed_columns,
clsValor= listaFormatoValor,
ListaFormatoPercet=percent)
My problem: When using grouped data and calculating the percentage variation between the columns, the values in the smallest sublevel are correct, for example: merchandise in Jan-2023 10k and in Feb-2023 12k. The variation was 20%. In services the variation was also 20%. When grouping, it adds the values resulting in 40%. However, the correct value is 20%. How do I adjust this?