Skip to content

Instantly share code, notes, and snippets.

@eudson

eudson/chart_utils.py

Created May 2, 2023 15:47
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save eudson/86034f8b62255078d0daff49219f4486 to your computer and use it in GitHub Desktop.
Save eudson/86034f8b62255078d0daff49219f4486 to your computer and use it in GitHub Desktop.
Download ZIP
Tem GIST for DashPlotly Python
Raw
chart_utils.py
import functools
import dash_bootstrap_components as dbc
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
from dash import html, dcc, State, dash_table
from dash.dependencies import Input, Output
import dash_daq as daq
from app import app
tree_map_colors = ['#26456e',
'#1c5998',
'#1c73b1',
'#3a87b7',
'#67add4',
'#cacaca',
'#a2c18f',
'#69a761',
'#2f8e41',
'#1e7735']
color_palette_tableau_20 = [
"#4E79A7",
"#A0CBE8",
"#F28E2B",
"#FFBE7D",
"#59A14F",
"#8CD17D",
"#B6992D",
"#F1CE63",
"#499894",
"#86BCB6",
"#E15759",
"#FF9D9A",
"#79706E",
"#BAB0AC",
"#D37295",
"#FABFD2",
"#B07AA1",
"#D4A6C8",
"#9D7660",
"#D7B5A6"
]
color_palette_ide = [
"#ef422e", "#f15341", "#f47567", "#f5867a", "#f6988d", "#f8a9a0",
"#009CB8", "#00ADCC", "#00D0F5", "#0ADAFF",
"#43B16C", "#5CC181", "#6BC78D", "#7ACD98",
"#2D3239", "#363C44", "#49505B", "#525A66",
"#7A002F", "#A3003F", "#B80046", "#CC004E",
"#FFB41F", "#FFBB33", "#FFC247", "#FFC95C",
"#F25D18", "#F36A2B", "#F4783E", "#F69365"
]
color_palette_all = color_palette_tableau_20.append(color_palette_ide)
province_color_sequence = ["#800031", "#f15645", "#48b971", "#00bfdf"]
province_color_map = {
'Cabo Delgado': "#ffcc69",
'Gaza': "#800031",
'Zambézia': "#00bfdf",
'Manica': "#48b971",
'Sofala': "#f15645",
'Nampula': "#3f464f",
}
products_color_map = {
"Pumpkin": "#fc4100",
"Anona Seeds": "#FF681D",
"Arugula": "#FF9E6D",
"Avocado Seeds": "#FFBF9F",
"Beet": "#D62E1F",
"Peanut": "#e14008",
"Rice": "#f36635",
"BROCOLI": "#FF6D0F",
"Cabbage": "#F35C16",
"Carrot": "#FF8030",
"Chard": "#FFA269",
"Cilantro / Coriander": "#ae360c",
"Cucumber": "#FFBA8F",
"Eggplant": "#F46E30",
"Pigeon Peas": "#722206",
"Bonus Pears": "#dd592b",
"Yoke Bean": "#cb6c4b",
"Butter Beans": "#e83d02",
"Cowpeas": "#FF8772",
"Green Beans": "#FFB5AB",
"Common Beans": "#D64123",
"Sesame": "#C24729",
"Sorghum": "#B73E25",
"Watermelon": "#AA2D2A",
"Maize": "#CB3725",
"Garlic": "#F4773D",
"Kale": "#F47A43",
"Leek": "#FF6C00",
"Lettuce": "#F47F4A",
"Lychee Seeds": "#FF9016",
"Macadamia Seeds": "#FFAE60",
"Okra": "#F78138",
"Soya": "#E8503E",
"Onion": "#F6894E",
"Abamectin": "#FFFF8A",
"Acephate": "#F8C567",
"ACEPTANTS": "#f6ed00",
"Acetamiprid": "#FFFF5C",
"ACETELIC": "#FFE17A",
"AGRIDEBEM": "#FDF384",
"Agrofenophos": "#FFDD69",
"Agromectin": "#e8cb66",
"Agrometrine": "#e8cb66",
"AGROPYRIFOS": "#e0bd43",
"AGROZ BAGS": "#ffd852",
"albendazole": "#d7a703",
"ALISERYL": "#bc9200",
"Alluminium Phosphide": "#FDF6DB",
"Amitraz": "#ddac03",
"ANT-KILLER": "#ffb80e",
"Aplus": "#ffc539",
"APRON STAR": "#fffe71",
"Atrazina": "#FFD800",
"ATTACK": "#ffec93",
"BANDIT 35% SL(imidacloprid 350g/l)": "#FEDC56",
"oxytetracycline base": "#e8b315",
"bentozone": "#FFA700",
"BEST": "#FFDA03",
"Beta-Cyfluthrin": "#FEEEAA",
"Bevenil": "#e0b911",
"BEZOANT": "#f3e106",
"BIANI REID": "#DEC668",
"Bromoxynil-octanoate+": "#f4d145",
"BUTASAL": "#fed11e",
"CANINE PLUS": "#fcca01",
"Acaricide Pour on": "#e9ba00",
"Majestic Acaracide": "#e9ba00",
"Cedis": "#f1e309",
"CELAX": "#fff000",
"Celcron": "#f6ef7a",
"Chanfer Seeds": "#fffbba",
"Chlorothalonil": "#fff665",
"Chlorpyrifos": "#FFDA8E",
"Cypermethrine": "#e4cc59",
"CIPRO- PRO": "#f8d324",
"Clomazone": "#eac20a",
"CHLOROPYROPHOS": "#FFDA8E",
"COBOX": "#ddbb1c",
"COCKROCHA": "#c0a41f",
"CONFIADOR": "#cdb517",
"Ax /Axe": "#54B3C7",
"Bag": "#70CDE2",
"Boots": "#badce6",
"Bowl": "#70b1c9",
"Box": "#588A99",
"Bucket": "#00BFDF",
"Canvas": "#3F97B5",
"Catana": "#1CC3E0",
"Chain": "#0083C2",
"Chisel": "#007CBB",
"CONECTOR": "#00548b",
"Cow syringe": "#002B49",
"Empty Bag": "#009DB8",
"Faucet": "#005678",
"Gloves": "#3899AC",
"Hammer": "#0092AB",
"Hand Cart": "#1C91A7",
"handsaw": "#006476",
"Harvest Box": "#185A7D",
"Helmet": "#205563",
"Hoe": "#00BFDF",
"Hoe handle": "#00BFDF",
"Hose Tube": "#38BDD7",
"Knife": "#006476",
"Lamp": "#4891A3",
"LIMA": "#33647f",
"Mask": "#40748E",
"Measuring Tape": "#00D0F5",
"Needle": "#4D86A0",
"Other": "#7BA6BB",
"Pencil": "#96D5EA",
"Pickaxe": "#55c1e9",
"Plastic roll": "#0098B2",
"Plastic Vase": "#006080",
"Pliers": "#006E97",
"Plow beak": "#0084AD",
"Protective goggles": "#009ABF",
"Pulviometer": "#43A8C7",
"Pump": "#0081BC",
"Rain boots": "#00798F",
"Raincoat": "#38B0C7",
"Rake": "#38C6E1",
"Rede Mosq.": "#00ADD8",
"Reflective vest": "#00577D",
"Rope": "#0082CA",
"Saw Blade": "#007084",
"Scale": "#208397",
"Scissors": "#00A1B0",
"NPK": "#369B5E",
"Urea": "#75BE98",
"CAN - Ammonium Nitrate with Calcium": "#3cb878",
"Liquid Fertilizer": "#309157",
"Bokashi": "#349477",
"Seedlings": "#fcfbeb",
"Add": "#fcfbeb",
"Puor on": "#fcfbeb",
"Copper OxychlorIde": "#FFD27A",
"Copper Oxychloride": "#FFD27A",
"CUPRAVIT": "#efd41d",
"Cymoxanil": "#ffde46",
"CYNOCRONE": "#f0c604",
"cypermethrin": "#f1dea7",
"Cypermethrin": "#f1dea7",
"Cypermethrin 10%": "#f1dea7",
"Decis Fort": "#f4eca6",
"DEDLINE DIP": "#eee27c",
"Deltamethrin": "#e5d763",
"Deltamethrin+": "#e5d763",
"DELTRAMETHRINA": "#e5d763",
"DICHLOROFOS": "#fcd283",
"Dimethoate": "#f3e0ad",
"Slug Drug": "#ffdf88",
"Emamectin": "#FFCC69",
"Emamectin Benzoate": "#FFCC69",
"EMAMECTINE": "#FFCC69",
"Imperator": "#f6edcb",
"Sulfur": "#f5e08c",
"FALCRON": "#F9E07A",
"Falfume": "#ebc941",
"Flubendiamide": "#ecc117",
"Folicur": "#fec75c",
"Aluminium Phosphide": "#ffbc39",
"Oxychloride Fungicide": "#ec9e05",
"Glyphosate": "#FEE7B3",
"Glycel": "#df9403",
"GRIPOCEC": "#cc8701",
"Harare": "#e9c885",
"Scythe": "#00ADBC",
"Seringe": "#007079",
"Shovel": "#3196AA",
"Slasher": "#108AA1",
"Other": "#fcfbeb",
"Orange Seeds": "#F19868",
"Auxo Herbicide for/Corn": "#f5c562",
"Dudual herbicide": "#f3ce81",
"Native 30% Herbicide": "#d8a845",
"HEXACONAZOL": "#efb43a",
"Hexaconazole": "#efb43a",
"HIBRIDO": "#cf9c32",
"HIPIR PRO": "#dca024",
"Hitcel": "#eec570",
"Parsley": "#FAB590",
"IMIDACEL": "#e9b64d",
"Imidacloprid": "#FFD88A",
"IMPERADOR": "#d18f07",
"Pepper": "#FA7A35",
"Indoxacarb": "#ddb76a",
"Inonozene": "#f2dd82",
"Actelic Insecticide": "#FFE17A",
"Biomiprid Insecticide": "#ebc524",
"Fortis 5% Insecticide": "#e7bf18",
"Piri Piri": "#FA6B1D",
"INTRAFER": "#fbe4a3",
"INTROVIT": "#fcdc81",
"INVERMETHRINA": "#e8cb78",
"Ivermectin": "#e8cb78",
"K Siol": "#debc1b",
"KAN": "#dfa10c",
"KLORMOZ": "#FFBA13",
"K-OBIOL": "#ffc845",
"korovil": "#fdd773",
"LAB-LAB": "#f9e08e",
"Lambda": "#f6cf3f",
"Slasher": "#108AA1",
"Lambda": "#f6cf3f",
"Lambda-Cyhalothrin": "#f6cf3f",
"LIMOXIN": "#f7d54e",
"Lufenuron": "#fde052",
"Majestic": "#ffce00",
"Malathion": "#ffda27",
"Mancozeb": "#d7bf49",
"mascot": "#f1d384",
"MASTA": "#edd899",
"MELATHION": "#f7e0a5",
"Metalaxyl": "#FFD88A",
"Metalaxyl - Mancozeb": "#FFD88A",
"Metalochlor": "#FEDF9D",
"nemovit": "#cda053",
"Potato Seeds": "#F45B08",
"Ninja": "#f4c400",
"Nozomil": "#f3ce00",
"Oxamyl": "#E0AD61",
"OXYTECTRACLINE": "#f6da40",
"Pendimentalin": "#F7EB62",
"PENSTREP": "#F8EA49",
"Pesticida Amaro": "#e6aa01",
"Belt": "#e6aa01",
"Strong Pesticides": "#e6aa01",
"Hitcel Pesticide": "#e6aa01",
"Karate Pesticide": "#e6aa01",
"Osunca pesticide": "#e6aa01",
"Shumba Pesticide": "#e6aa01",
"Shumba Supermax Pesticide": "#e6aa01",
"Medium Snow Pesticide": "#e6aa01",
"Small Snow Pesticide": "#e6aa01",
"Snow Plus Pesticide": "#e6aa01",
"Pesticida Tricel": "#9A8600",
"Pirimiphos-Methyl": "#FDE300",
"Rosemary": "#F48808",
"Slasher": "#108AA1",
"LAMBDA": "#FFD700",
"Seeds": "#E5810C",
"Spinach": "#EF9C3A",
"Profenophos": "#FFDE00",
"Propineb": "#F4E95D",
"RAKAN": "#F4E600",
"RATAZ": "#F1E000",
"Tangerine Seeds": "#F5B56A",
"REMEDY": "#DBA700",
"RIDOMIL": "#EEDC00",
"RODENT": "#D39100",
"Rodes": "#FFB700",
"Sower": "#0099A9",
"Portion": "#9192b7",
"ROLIM": "#FFC700",
"RONDAPE": "#9D7710",
"SANIWASH": "#B78500",
"Snow": "#e6aa01",
"SPRINGBOK": "#ECAA00",
"STARBACK": "#FFC72A",
"Starback": "#FFD141",
"STELARSTAR": "#FED857",
"SUCCESS": "#FEDB65",
"Sowing Tray": "#B0E3E4",
"Sprayer": "#54CAE2",
"Umblella": "#006168",
"SUPERKILL": "#FFD035",
"SYNECT": "#FFCC20",
"TARANTULA": "#FFC400",
"Tebuconazole": "#B57F00",
"Terbuthylazine": "#B57F00",
"Thiamethoxam": "#FDF0CB",
"THIAMETHOXAN": "#FDF0CB",
"Uniforms": "#00ADCC",
"Tomato": "#F56F27",
"Thunder": "#E5B000",
"utensilio ab": "#007B8B",
"TOPI": "#D6A500",
"TRIPONIL": "#D1A81E",
"TRISULFIN": "#D8AF26",
"utensilio ab": "#0096AA",
"VITOL": "#E0BD48",
"Volamiprid/biomipride": "#E3C973",
"Watering can": "#1C7F91",
"Worm Compost": "#39b54a",
"VOLMITRAZ": "#F6CE46",
"WEEDAL": "#FFCE6E",
"Zakanaka": "#FDF0CB",
"Pirimiphos-Methyl": "#e3e3e3",
"Fertelizante Líquido": "#197b30",
"ABCD": "#e3e3e3",
"xyz produto produto": "#e3e3e3",
"Weeder / Slashaer": "#62B1BD",
"Work overalls": "#38A3B9"
}
district_color_map = {
'Chókwé': "#A3003F",
'Guijá': "#B80046",
'Búzi': "#F36A2B",
'Dondo': "#F4783E",
'Nhamatanda': "#F69365",
'Sussundenga': "#43B16C",
'Vanduzi': "#91D5A9",
'Manica': "#91D5A9",
'Macate': "#5CC181",
'Gondola': "#6BC78D",
'Chimoio': "#6BC78D",
'Macossa': "#7ACD98",
'Maganja da Costa': "#009CB8",
'Mocuba': "#33CBE5",
'Nampula': "#2D3239",
'Nacala': "#2D3239",
'Memba': "#363C44",
'Mecufi': "#F1CE63",
'Chiúre': "#F1CE63",
}
inputs_color_map = {
'Fertilizers': "#f15645",
'Tools': "#00bfdf",
'Seeds': "#48b971",
'Pesticides': "#ffcc69",
'Others': "#3f464f"
}
seeds_color_map = {
'Cereals and Legumes': '#da1c5c',
'Vegetables': '#f47f4a',
'Others': '#c08e59'
}
def get_pie_chart(data, value_attr, label_attr, title, custom_colors_labels, hover_template, hole=None,
custom_color_map=None):
color_map = {}
for index, label in enumerate(custom_colors_labels):
full_color_palette = color_palette_tableau_20
full_color_palette.append(tree_map_colors)
color_map[label] = custom_color_map[label] if custom_color_map else full_color_palette[index]
hole = .0 if hole is None else hole
figure = px.pie(data,
title=title,
values=value_attr,
names=label_attr,
color=label_attr,
color_discrete_map=color_map,
hole=hole)
figure.update_traces(
hovertemplate=hover_template,
title_font_family="Roboto",
title_font_color="#3f464f",
texttemplate='%{percent:.1%}',
textposition='inside'
)
px_update_figure_layout(figure)
return figure
def get_tree_map_chart(data, title):
figure = px.treemap(
data,
title=title,
names='character',
parents='parent',
values='value',
)
figure.update_traces(
root_color="lightgrey",
textinfo="label+value",
hovertemplate='<b>%{label} </b> <br> Total sold: %{value}',
marker=dict(line=dict(width=0)),
tiling=dict(pad=1)
)
px_update_figure_layout(figure)
figure.update_layout(margin=dict(t=50, l=25, r=25, b=25), treemapcolorway=tree_map_colors)
return figure
def get_stacked_bar_chart(df, x, y, stack_by, x_axis_title, y_axis_title, labels_title, chart_title, color_map=None,
remove_hover_data=None, labels=None):
hover_data = {}
for data in remove_hover_data:
hover_data[data] = False
figure = px.bar(
df,
x=x,
y=y,
color=stack_by,
title=chart_title,
color_discrete_map=color_map,
color_discrete_sequence=None if color_map else color_palette_tableau_20,
labels={"x": x_axis_title, "y": y_axis_title, "color": labels_title} if labels is None else labels,
hover_data=hover_data
)
px_update_figure_layout(figure)
return figure
def get_single_bar_chart_multi_color(x, y, text, x_axis_title, chart_title, round_numbers=True):
figure = go.Figure(
data=[
go.Bar(
x=x,
y=y,
text=text,
textposition="auto",
opacity=0.8,
marker_color=color_palette_tableau_20
),
]
)
# set x axes title and tick to only include year given no half year such as 1927.5
figure.update_xaxes(title=x_axis_title, tickformat="d")
# set y axes to fixed selection range, user can only select data in the x axes
figure.update_yaxes(fixedrange=True)
if round_numbers:
figure.update_traces(texttemplate='%{text:.2s}')
# Update figure, set hover to the X-Axis and establish title
figure.update_layout(
barmode="group",
title=chart_title,
paper_bgcolor="white",
font_family='Roboto',
plot_bgcolor="white",
legend=dict(orientation="h", yanchor="bottom", y=1, xanchor="right", x=1),
)
return figure
def get_px_single_bar_chart_multi_color(df, x, y, title, labels, color_map=None, remove_hover_data=None):
hover_data = {}
for data in remove_hover_data:
hover_data[data] = False
figure = px.bar(
df,
x=x,
y=y,
color=x,
color_discrete_map=color_map,
title=title,
labels=labels,
hover_data=hover_data
)
px_update_figure_layout(figure)
figure.update_traces(showlegend=False)
return figure
def get_scatter_plot_chart(data, x, y, hover, labels):
figure = px.scatter(
data, x=x, y=y,
color=y,
hover_data=[hover],
labels=labels,
color_discrete_map=products_color_map,
color_discrete_sequence=color_palette_all,
)
px_update_figure_layout(figure)
figure.update_xaxes(showgrid=False)
figure.update_yaxes(showgrid=False)
figure.update_traces(showlegend=False)
return figure
def px_update_figure_layout(figure):
figure.update_layout(
plot_bgcolor="#d9d9d9",
title_font_family="Roboto",
title_font_color="#3f464f",
font_family="Roboto",
font_color="#3f464f",
paper_bgcolor="#d9d9d9",
hoverlabel=dict(
bgcolor="white",
font=dict(family="Roboto", color="#3f464f")
)
)
figure.update_yaxes(title=None)
# TODO: move to data_utils
def get_simple_dropdown_options(data_frame, attribute):
return [{"label": value, "value": value} for value in
pd.Series(data_frame[attribute].unique())]
def get_custom_dropdown_options(data_frame, label_attr, value_attr):
dropdown_list = data_frame[[f"{label_attr}", f"{value_attr}"]]
dropdown_list.rename(columns={value_attr: "value", label_attr: "label"}, inplace=True)
return dropdown_list.to_dict(orient="records")
def get_page_filters_with_date(location_config_list, period_config_list):
return html.Div(
className="filter-container",
children=[
dbc.Row([
dbc.Col(
dbc.Row(
[
get_dropdown_filter(
config_item["filter_name"],
config_item["filter_id"],
config_item["filter_options"],
4
) for config_item in location_config_list
]),
xs={"size": 8, "offset": 0},
sm={"size": 8, "offset": 0},
md={"size": 8, "offset": 0},
lg={"size": 8, "offset": 0},
),
dbc.Col(
dbc.Row(
[
get_dropdown_filter(
config_item["filter_name"],
config_item["filter_id"],
config_item["filter_options"],
6
) for config_item in period_config_list
]),
xs={"size": 4},
sm={"size": 4},
md={"size": 4},
lg={"size": 4},
)
])]
)
def get_page_filters(config_list):
return html.Div(
className="filter-container",
children=[
dbc.Row([
get_dropdown_filter(
config_item["filter_name"],
config_item["filter_id"],
config_item["filter_options"]
) for
config_item in config_list])
])
def get_dropdown_filter(filter_name, filter_id, filter_options, size=None):
return dbc.Col(
add_to_card(
[
dcc.Dropdown(
id=filter_id,
options=filter_options,
clearable=True,
className="dcc_control",
multi=True,
placeholder=filter_name
)
]),
xs={"size": 4 if size is None else size, "offset": 0},
sm={"size": 4 if size is None else size, "offset": 0},
md={"size": 4 if size is None else size, "offset": 0},
lg={"size": 4 if size is None else size, "offset": 0},
)
def add_to_card(elements):
return dbc.Card(
[element for element in elements],
body=True,
color="light",
inverse=True,
)
def gender_toggle(btn_id):
return html.Div([
html.Span("Add gender disaggregation ", style={'float': 'left'}),
daq.BooleanSwitch(id=btn_id + '-gender-toggle', on=False, color="#3f464f", style={'float': 'left'})],
style={'overflow': 'hidden'})
def download_button(btn_id, column_list, file_name, show_gender_toggle):
@app.callback(
Output(btn_id + '-download', 'data'),
Input(btn_id + '-btn', 'n_clicks'),
State(btn_id + '-data', 'data'),
prevent_initial_call=True,
)
def download(n_clicks, data):
df = pd.read_json(data, orient='split')
df = df[column_list]
return dcc.send_data_frame(df.reset_index(drop=True).to_excel, file_name + ".xlsx",
sheet_name="data")
return html.Div(
[
show_gender_toggle is True and gender_toggle(btn_id),
dbc.Button(
html.Span(["... ", html.I(className="bi bi-download me-2")]),
color="secondary",
outline=True,
id=btn_id + "-btn"
)
],
className="d-grid gap-2 d-md-flex justify-content-md-end",
style={"margin-bottom": "8px"}
)
def chart_definition(chart_id, table_column_list, download_file_name, display=None, show_gender_toggle=None):
return add_to_card(
[
download_button(chart_id, table_column_list, download_file_name, show_gender_toggle),
dcc.Graph(id=chart_id,
config={"displayModeBar": True, "displaylogo": False},
style={"display": "block" if display is None else display},
),
dcc.Download(id=chart_id + "-download"),
dcc.Store(id=chart_id + "-data")
])
def table_definition(table_id, table_column_list=None, download_file_name=None):
return html.Div(
[
download_button(table_id, table_column_list, download_file_name, None),
dash_table.DataTable(
id=table_id,
style_as_list_view=True,
editable=False,
page_size=8,
style_table={
"overflowY": "scroll",
"width": "100%",
"minWidth": "100%",
"marginLeft": "16px",
},
style_header={"backgroundColor": "#fcfbeb", "fontWeight": "bold"},
style_cell={"textAlign": "center", "padding": "8px"},
style_data={"backgroundColor": "#d9d9d9", "borderBottom": "1px solid #fcfbeb"}
),
dcc.Download(id=table_id + "-download"),
dcc.Store(id=table_id + "-data")
])
def format_number(number):
return "{:,}".format(number)
def is_none_or_empty(list_element):
return list_element is None or len(list_element) == 0
def apply_filters(df, year, season, province, district, fair):
return df[
(df['year'].isin(year)) &
(df['season'].isin(season)) &
(df['province'].isin(province)) &
(df['district'].isin(district)) &
(df['fair_id'].isin(fair))
]
Raw
data.py
# Improtant note: This data file would ordinarily be used to connect with a proper database server
# more likely PostgreSQL, but thats me. I do plan on rewritting this in the future for such implementations.
# With that said, this file will be be very slow to run and only to demonstrate data processing using
# functions and pandas along with providing a central file for data references
#
# Import Pandas
import pandas as pd
import sqlalchemy as db
from chart_utils import get_simple_dropdown_options, get_custom_dropdown_options, district_color_map
server_conn_str = "mysql+pymysql://chat:chat@localhost/db"
local_conn_str = "mysql://chat:chat@localhost/db"
test_conn_str = "mysql+pymysql://chat:chat@localhost/db"
engine = db.create_engine(test_conn_str)
with engine.connect() as connection:
dim_location = pd.read_sql("SELECT * FROM dim_location", connection).rename(columns={
'province_name': 'province',
'district_name': 'district'
})
dim_fair = pd.read_sql("SELECT * FROM dim_fair", connection).merge(
dim_location, how='inner', on='location_id').rename(columns={'display_name': 'fair'})
dim_farmer = pd.read_sql("SELECT * FROM dim_farmer WHERE _duplicate_farmer_id = 0 AND was_present = 1", connection)
dim_product_category = pd.read_sql("SELECT * FROM dim_product_category", connection)
dim_seller = pd.read_sql("SELECT * FROM dim_seller", connection)
dim_product_type = pd.read_sql("SELECT * FROM dim_product_type", connection)
fact_sale = pd.read_sql("SELECT * FROM fact_sale", connection)
dim_product = pd.read_sql('SELECT * FROM dim_product', connection).merge(dim_product_category, how='inner',
on='product_category_id')
farmers = dim_farmer.merge(dim_fair, how='inner', on='fair_id')
df_farmers = dim_farmer.drop(columns=['fair_id'])
sales = fact_sale \
.merge(dim_fair, how='inner', on='fair_id') \
.merge(dim_product, how='inner', on='product_id') \
.merge(dim_seller, how='inner', on='seller_id').merge(df_farmers, how='left', on='farmer_id')
# .merge(dim_farmer, how='left', on='farmer_id') \
sales = sales.rename(columns={
'total_price': 'total_sold',
'category_description_en': 'category'
})
all_years = sales['year'].unique()
all_fairs = sales['fair_id'].unique()
all_provinces = sales['province'].unique()
all_districts = sales['district'].unique()
all_seasons = sales['season'].unique()
year_dropdown_list = get_simple_dropdown_options(dim_fair, "year")
season_dropdown_list = get_simple_dropdown_options(dim_fair, "season")
fair_dropdown_list = get_custom_dropdown_options(dim_fair, "fair", "fair_id")
province_dropdown_list = get_simple_dropdown_options(dim_location, "province")
district_dropdown_list = get_simple_dropdown_options(dim_location, "district")
fair_color_map = {}
for index, row in dim_fair.iterrows():
try:
fair_color_map[row['fair']] = district_color_map[row['district']]
except KeyError:
pass
# Import CSV data
# Import team historical statistics
# Some historical team names are correlated with their more modern counter part
# Custome CSV files where created from the original by combining data to allow
# for easier display of historical team data
teams = pd.read_csv("data/update_team.csv")
# Import Players batting data
batters = pd.read_csv("data/update_batting.csv.gz")
# Import custom Fielding data
fielding = pd.read_csv("data/update_fielding.csv.gz")
# Import custom pitching data
pitching = pd.read_csv("data/update_pitching.csv")
# Import Player profile data
players = pd.read_csv("data/update_player.csv")
# Import custom player and team id dataframe
team_players = pd.read_csv("data/player_team.csv")
# Hardcoded list of era names as key value pairs
era_list = [
{"label": "Dead Ball ('03-'19)", "value": "Dead Ball"},
{"label": "Live Ball ('20-'41)", "value": "Live Ball"},
{"label": "Integration ('42-'60)", "value": "Integration"},
{"label": "Expantion ('61-'76)", "value": "Expantion"},
{"label": "Free Agency ('77-'93)", "value": "Free Agency"},
{"label": "Steroid ('94-'05)", "value": "Steroid"},
{"label": "Post-Steroid ('06-'15)", "value": "Post-Steroid"},
{"label": "Statcast ('16-'20)", "value": "Statcast"},
]
# Era markers
era_marks = {
1903: {"label": "1903"},
1919: {"label": "1919"},
1941: {"label": "1941"},
1960: {"label": "1960"},
1976: {"label": "1976"},
1993: {"label": "1993"},
2005: {"label": "2005"},
2015: {"label": "2015"},
2020: {"label": "2020"},
}
# Creates a dynamic list of team names based on era
def dynamicteams(x):
# Hardcoded list of era time spans, wouldnt do it this way if the set where larger
era_time = [
(1903, 1919),
(1920, 1941),
(1942, 1960),
(1961, 1976),
(1977, 1993),
(1994, 2005),
(2006, 2015),
(2016, 2020),
]
# create a filter list of just years and team names
filter_team_yr = teams[["year", "name", "team_id"]]
# filter the above list by year span
filter_year = filter_team_yr[
(filter_team_yr.year >= era_time[x][0])
& (filter_team_yr.year <= era_time[x][1])
] # High Year
# filter_year = filter_year[] # Low Year
# Create a filter list of Team names based on years filtered
filter_teams = filter_year["name"].unique()
filter_team_ids = filter_year["team_id"].unique()
# return unique list of team names as a list of key value pairs, rather than calling a function to create and return the list
# list comp of key value pair
# new is a list of names while x is the name in the list
return [{"label": k, "value": v} for k, v in zip(filter_teams, filter_team_ids)]
def dynamicrange(x):
# Hardcoded data is not typically what i do unless the set is small
era_time = [
(1903, 1919),
(1920, 1941),
(1942, 1960),
(1961, 1976),
(1977, 1993),
(1994, 2005),
(2006, 2015),
(2016, 2020),
]
return [era_time[x][0], era_time[x][1]]
# Calculate On-Base Percentage function
def calculate_obp(df):
# Set lists of team data
AB = df.ab
Ht = df.h
BB = df.bb
HBP = df.hbp
SF = df.sf
# return On-Base Percentage
return (Ht + BB + HBP) / (AB + BB + HBP + SF)
# Calculate Slugging Average
def calculate_slg(df):
# Set lists of player data
AB = df.ab
Ht = df.h
DBL = df.double
TRP = df.triple
HR = df.hr
SNG = Ht - DBL - TRP - HR
# return Slugging Average
return (SNG + 2 * DBL + 3 * TRP + 4 * HR) / AB
# Calculate WOBA
def calculate_woba(df):
# Selected players singles
SNG = df.h - df.double - df.triple - df.hr
# Weighted Plate Appearances
WPA = df.ab + df.bb - df.ibb + df.sf + df.hbp
# weighted on-base average, 2013 https://library.fangraphs.com/offense/woba/
return (
(0.690 * df.bb)
+ (0.722 * df.hbp)
+ (0.888 * SNG)
+ (1.271 * df.double)
+ (1.616 * df.triple)
+ (2.101 * df.hr)
) / WPA
# weighted on-base average, 2019 https://en.wikipedia.org/wiki/WOBA#2019_Formula
# return ((0.690 * df.bb) + (0.719 * df.hbp) + (0.87 * SNG) + (1.217 * df.double) + (1.529 * df.triple) + (1.94 * df.hr)) / WPA
Raw
sales_dashboard_page.py
import datetime
import dash_bootstrap_components as dbc
from dash import html
from dash.dependencies import Input, Output
from numerize import numerize
import chart_utils
from app import app
from chart_utils import get_page_filters_with_date, province_color_map, inputs_color_map, seeds_color_map, \
apply_filters, is_none_or_empty
from data import *
# FILTER UPDATES
from utils import agg_sum
@app.callback([
Output("sales-district-dropdown", "options"),
Output("sales-district-dropdown", "value")
], [Input("sales-province-dropdown", "value")])
def update_filters(selected_province):
filtered_district_options = get_simple_dropdown_options(
dim_location if (selected_province is None or not selected_province) else dim_location[
dim_location['province'].isin(selected_province)],
"district")
return [filtered_district_options, None]
@app.callback([
Output("sales-fair-dropdown", "options"),
Output("sales-fair-dropdown", "value")
], [Input("sales-province-dropdown", "value"), Input("sales-district-dropdown", "value")])
def update_filters(selected_province, selected_district):
if selected_province and (selected_district is None or not selected_district):
fair_opts = dim_fair[dim_fair['province'].isin(selected_province)]
elif selected_district:
fair_opts = dim_fair[dim_fair['district'].isin(selected_district)]
else:
fair_opts = dim_fair
filtered_options = get_custom_dropdown_options(fair_opts, "fair", "fair_id")
return [filtered_options, None]
# Sales Page Callbacks
page_filter = get_page_filters_with_date([
{
"filter_name": "Province",
"filter_id": "sales-province-dropdown",
"filter_options": province_dropdown_list
},
{
"filter_name": "District",
"filter_id": "sales-district-dropdown",
"filter_options": district_dropdown_list
},
{
"filter_name": "Fair",
"filter_id": "sales-fair-dropdown",
"filter_options": fair_dropdown_list
}
],
[
{
"filter_name": "Year",
"filter_id": "sales-year-dropdown",
"filter_options": year_dropdown_list
},
{
"filter_name": "Season",
"filter_id": "sales-season-dropdown",
"filter_options": season_dropdown_list
}
]
)
@app.callback(
[
Output("card-total-sold", "children"),
Output("card-total-fairs", "children"),
Output("card-total-farmers", "children"),
Output("card-total-sellers", "children"),
Output("sales-by-fair", "figure"),
Output("sales-by-province", "figure"),
Output("sales-by-district", "figure"),
Output("sales-by-product-category", "figure"),
Output("sales-by-product-sub-category", "figure"),
Output("sales-by-province-data", "data"),
Output("sales-by-district-data", "data"),
Output("sales-by-fair-data", "data"),
Output("sales-by-product-sub-category-data", "data"),
Output("sales-by-product-category-data", "data"),
Output("fair-row", "style")
],
[
Input("sales-province-dropdown", "value"),
Input("sales-district-dropdown", "value"),
Input("sales-fair-dropdown", "value"),
Input("sales-year-dropdown", "value"),
Input("sales-season-dropdown", "value"),
Input("sales-by-product-category-gender-toggle", "on"),
Input("sales-by-province-gender-toggle", "on"),
Input("sales-by-district-gender-toggle", "on"),
Input("sales-by-fair-gender-toggle", "on")
]
)
def update_sales_page(selected_province, selected_district, selected_fair, selected_year, selected_season,
sales_by_category_gender_toggle, sales_by_province_toggle, sales_by_district_toggle,
sales_by_fair_toggle):
selected_year = all_years if is_none_or_empty(selected_year) else selected_year
selected_province = all_provinces if is_none_or_empty(selected_province) else selected_province
selected_district = all_districts if is_none_or_empty(selected_district) else selected_district
selected_fair = all_fairs if is_none_or_empty(selected_fair) else selected_fair
selected_season = all_seasons if is_none_or_empty(selected_season) else selected_season
filtered_farmers = apply_filters(farmers, selected_year, selected_season, selected_province, selected_district,
selected_fair)
filtered_sales = apply_filters(sales.rename(
columns={'gender': 'Gender'}), selected_year, selected_season, selected_province, selected_district,
selected_fair).sort_values(by=['province_code'])
total_sold = numerize.numerize(filtered_sales['total_sold'].sum(), 1)
total_farmers = chart_utils.format_number(filtered_farmers['farmer_id'].nunique())
total_sellers = chart_utils.format_number(filtered_sales['seller_id'].value_counts().nunique())
total_fairs = chart_utils.format_number(filtered_sales['fair_id'].nunique())
sales_by_fair_download = agg_sum(filtered_sales,
'total_sold',
['province', 'province_code', 'district', 'year', 'season',
'district_code', 'fair', 'realization_date', 'Gender']
)
sales_by_province_download = agg_sum(filtered_sales,
'total_sold',
['province', 'year', 'season', 'province_code', 'Gender']
).sort_values(by=['province_code', 'year', 'season'])
sales_by_district_download = agg_sum(filtered_sales,
'total_sold',
['province', 'province_code', 'year', 'season', 'district', 'district_code']
).sort_values(by=['province_code', 'district_code', 'year', 'season'])
sales_by_product_category_group_by = ['category', 'Gender'] if sales_by_category_gender_toggle is True else [
'category']
sales_by_product_category = agg_sum(filtered_sales, 'total_sold',
sales_by_product_category_group_by)
sales_by_product_category_download = agg_sum(filtered_sales, 'total_sold',
['province', 'district', 'year', 'season', 'fair', 'category',
'Gender'])
seeds_types = ['Others', 'Cereals and Legumes', 'Vegetables']
filtered_sales_by_sub_category = filtered_sales[filtered_sales['sub_category_en'].isin(seeds_types)]
sales_by_product_sub_category = agg_sum(filtered_sales_by_sub_category, 'total_sold', ['sub_category_en'])
sales_by_product_sub_category_download = agg_sum(
filtered_sales_by_sub_category,
'total_sold',
['province', 'district', 'year', 'season', 'fair', 'sub_category_en', 'Gender']
)
sales_by_product_sub_category_download = sales_by_product_sub_category_download.rename(
columns={'sub_category_en': 'seed_type'})
sales_by_product_sub_category = sales_by_product_sub_category.sort_values(by="total_sold", ascending=False)
if sales_by_fair_toggle is True:
sales_by_fair = filtered_sales.groupby(['district_code', 'fair', 'realization_date', 'Gender'])[
'total_sold'].sum().reset_index().sort_values(by=['district_code', 'realization_date'])
sales_by_fair_figure = chart_utils.get_stacked_bar_chart(sales_by_fair,
sales_by_fair.fair,
sales_by_fair.total_sold,
sales_by_fair.Gender,
"Fair",
"Amount",
"Categories",
"Total sold by fair (MZN)",
inputs_color_map,
remove_hover_data=['fair'],
labels={"fair": "Fair",
"total_sold": "Total sold"})
else:
sales_by_fair = filtered_sales.groupby(['district_code', 'fair', 'realization_date'])[
'total_sold'].sum().reset_index().sort_values(by=['district_code', 'realization_date'])
sales_by_fair_figure = chart_utils.get_px_single_bar_chart_multi_color(sales_by_fair,
"fair",
"total_sold",
"Total sold by fair (MZN)",
{"fair": "Fair",
"total_sold": "Total sold"},
fair_color_map,
remove_hover_data=['fair']
)
if sales_by_province_toggle is True:
sales_by_province = filtered_sales.groupby(['province', 'province_code', 'Gender'])[
'total_sold'].sum().reset_index().sort_values(by=['province_code'])
sales_by_province_figure = chart_utils.get_stacked_bar_chart(sales_by_province,
sales_by_province.province,
sales_by_province.total_sold,
sales_by_province.Gender,
"Province",
"Amount",
"Categories",
"Total sold by province (MZN)",
inputs_color_map,
remove_hover_data=['province'],
labels={"province": "Province",
"total_sold": "Total sold"}
)
else:
sales_by_province = filtered_sales.groupby(['province', 'province_code'])[
'total_sold'].sum().reset_index().sort_values(by=['province_code'])
sales_by_province_figure = chart_utils.get_px_single_bar_chart_multi_color(sales_by_province,
"province",
"total_sold",
"Total sold by province (MZN)",
{"province": "Province",
"total_sold": "Total sold"},
province_color_map,
remove_hover_data=['province'])
if sales_by_district_toggle is True:
sales_by_district = filtered_sales.groupby(['district', 'district_code', 'Gender'])[
'total_sold'].sum().reset_index().sort_values(by=['district_code'])
sales_by_district_figure = chart_utils.get_stacked_bar_chart(sales_by_district,
sales_by_district.district,
sales_by_district.total_sold,
sales_by_district.Gender,
"District",
"Amount",
"Categories",
"Total sold by district (MZN)",
inputs_color_map,
remove_hover_data=['district'],
labels={"district": "District",
"total_sold": "Total sold"}
)
else:
sales_by_district = filtered_sales.groupby(['district', 'district_code'])[
'total_sold'].sum().reset_index().sort_values(by=['district_code'])
sales_by_district_figure = chart_utils.get_px_single_bar_chart_multi_color(sales_by_district,
"district",
"total_sold",
"Total sold by district (MZN)",
{"district": "District",
"total_sold": "Total sold"},
district_color_map,
remove_hover_data=['district'])
labels = {
"category": "Category",
"total_sold": "Total sold"
}
if sales_by_category_gender_toggle is True:
sales_by_product_category_figure = chart_utils.get_stacked_bar_chart(sales_by_product_category,
sales_by_product_category.category,
sales_by_product_category.total_sold,
sales_by_product_category.Gender,
"Province",
"Amount",
"Categories",
"Total sold by category ("
"MZN)",
inputs_color_map,
remove_hover_data=['category'],
labels=labels
)
else:
sales_by_product_category_figure = chart_utils.get_px_single_bar_chart_multi_color(sales_by_product_category,
"category",
"total_sold",
"Total sold by category ("
"MZN)",
labels,
inputs_color_map,
remove_hover_data=[
'category'
])
sales_by_product_sub_category_figure = chart_utils.get_pie_chart(sales_by_product_sub_category,
value_attr='total_sold',
label_attr='sub_category_en',
title='Seeds sale, by type of seed (%)',
custom_colors_labels=seeds_types,
hover_template="Total "
"sold (MZN): %{value} </br>",
custom_color_map=seeds_color_map,
)
return [
total_sold, total_fairs, total_farmers, total_sellers, sales_by_fair_figure, sales_by_province_figure,
sales_by_district_figure, sales_by_product_category_figure, sales_by_product_sub_category_figure,
sales_by_province_download.to_json(date_format='iso', orient='split'),
sales_by_district_download.to_json(orient='split'),
sales_by_fair_download.to_json(orient='split'),
sales_by_product_sub_category_download.to_json(orient='split'),
sales_by_product_category_download.to_json(orient='split'),
{'display': 'block' if len(selected_year) == 1 else 'none'}
]
cards = [
chart_utils.add_to_card(
[
html.H2("...", className="card-title", id="card-total-sold"),
html.P("Total sold (MZN)", className="card-text"),
]
),
chart_utils.add_to_card(
[
html.H2("...", className="card-title", id="card-total-fairs"),
html.P("Fairs", className="card-text"),
]
),
chart_utils.add_to_card(
[
html.H2("...", className="card-title", id="card-total-farmers"),
html.P("Beneficiaries", className="card-text"),
]
),
chart_utils.add_to_card(
[
html.H2("...", className="card-title", id="card-total-sellers"),
html.P("Agro dealers", className="card-text"),
]
),
]
salesLayout = html.Div(
[
page_filter,
dbc.Row([dbc.Col(card) for card in cards]),
dbc.Row([
dbc.Col(
chart_utils.chart_definition('sales-by-product-category',
['province', 'district', 'year', 'season', 'fair', 'category', 'Gender',
'total_sold'],
'total_sold_by_category',
show_gender_toggle=True),
xs={"size": 12, "offset": 0},
sm={"size": 12, "offset": 0},
md={"size": 12, "offset": 0},
lg={"size": 6, "offset": 0},
),
dbc.Col(
chart_utils.chart_definition('sales-by-product-sub-category',
['province', 'district', 'year', 'season', 'fair', 'seed_type', 'Gender',
'total_sold'],
'seeds_sale_by_type_of_seed'
),
xs={"size": 12, "offset": 0},
sm={"size": 12, "offset": 0},
md={"size": 12, "offset": 0},
lg={"size": 6, "offset": 0},
),
]),
dbc.Row([
dbc.Col(
chart_utils.chart_definition('sales-by-province',
['province', 'year', 'season', 'Gender', 'total_sold'],
'sales_by_province',
show_gender_toggle=True
),
), ]),
dbc.Row(
[
dbc.Col(chart_utils.chart_definition('sales-by-district',
['province', 'district', 'year', 'season', 'Gender', 'total_sold'],
'sales_by_district',
show_gender_toggle=True)
)
]),
dbc.Row(
[
dbc.Col(chart_utils.chart_definition('sales-by-fair',
['province', 'district', 'year', 'season', 'fair', 'Gender',
'total_sold'],
'sales_by_fair',
show_gender_toggle=True)
)
], style={'display': 'none'}, id='fair-row'
)
],
className="app-page"
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment