Create Web application using stremlit and AgGrid
import streamlit as st
import numpy as np
import pandas as pd
import altair as alt
from st_aggrid import AgGrid, GridOptionsBuilder
from pyspark.sql import SparkSession
import re
try:
st.set_page_config(layout="wide")
except:
pass
col1, col2 = st.columns([2,8])
with col1:
st.image('logo.jpg',width=100)
spark = SparkSession.builder \
.remote("abc://host:xxx") \
.enableHiveSupport().getOrCreate()
#df = spark.sql("SELECT * FROM db.table")
@st.cache_data()
def load_data():
#df = pd.read_csv('C:/Users/Downloads/xx.csv')
df1 = spark.sql("SELECT * FROM db.table")
df=df1.toPandas()
return df
df=load_data()
#df=df.toPandas()
new_title = '<center><p style="font-family:sans-serif; color:Black; font-size: 20px;><h1 style="text-align:center">Self Service Portal</h1></p></center>'
st.markdown(new_title, unsafe_allow_html=True)
background_color = "#F0FFFF"
st.markdown(
f"""
<style>
.stApp {{
background-color: {background_color};
}}
</style>
""",
unsafe_allow_html=True,
)
#Find Unique Value
cntry_lst=df["customer_market"].unique()
year_lst=df["yearmonth"].unique()
year_lst.sort()
seg_lst=df["customer_segment_current"].unique()
# Add filter condition
#dim_filters=st.sidebar.multiselect('Select Filters',
#['Market','Months','Segment'],default='Market')
#if 'Market' in dim_filters :
#cty=sel=st.multiselect('Select Markets', cntry_lst, default=None,key='cty')
#df = df[df["customer_market"].isin (cty)]
#if 'Months' in dim_filters:
#yrs=st.multiselect('Select Months', year_lst, default=None,key='yrs')
#df = df[df["yearmonth"].isin (yrs)]
#if 'Segment' in dim_filters:
#segment=st.multiselect('Select Segment(s)', cust_seg, default=None,key='seg')
#df = df[df["customer_segment_current"].isin (segment)]
#Apply filters on values
#if 'Market' in dim_filters :
# df = df[df["customer_market"].isin (cty)]
#if 'Months' in dim_filters:
# df = df[df["yearmonth"].isin (yrs)]
#if 'Segment' in dim_filters:
# df = df[df["customer_segment_current"].isin (segment)]
df2=df.rename(columns={
'customer_market':'Market'
,'yearmonth':'Months'
,'customer_segment_current':'Segment'
,'client_count':'Total client base (000)'
,'active_client_count':'Total active client base (000)'
,'wm_client_count': 'WM clients # (000)'
,'wmactive_client_count':'Active WM clients # (000)'
,'l6m_wmactive_client_count' : 'L6 months WM Active clients #s'
,'wm_penetration_percent':'WM Penetration % (1.0.3 1.0.1)'
,'l6m_wmactive_client_count_as_percentage':'L6 months WM Active clients as % of WM clients'
,'wm_penetration_from_ntb_percentage':'WM Penetration from NTB %'
,'aumq_client_count': 'Total AUMQ Client Count'
,'aumq_percentage_total_base':'AUMQ % of Total Base'
,'ytd_ntb_client_count' : 'YTD NTB Clint Count'
,'pp_client_count':'PP Client Count'
,'casa_aum':'Total Casa Aum'
,'deposit_aum':'Total Deposit Aum'
,'wm_aum':'Total Wealth Aum'
,'ytd_total_nnm':'Total YTD NNM'
,'ytd_casa_nnm':'Total Casa NNM'
,'ytd_td_nnm':'Total TD NNM'
})
dm_list=[]
#Matrix list
mat_list={'Matrix':['Total client base (000)'
,'Total active client base (000)'
,'WM clients # (000)'
,'Active WM clients # (000)'
,'L6 months WM Active clients #s'
,'WM Penetration % (1.0.3 1.0.1)'
,'L6 months WM Active clients as % of WM clients'
,'WM Penetration from NTB %'
,'Total AUMQ Client Count'
,'AUMQ % of Total Base'
,'YTD NTB Clint Count'
,'PP Client Count'
,'Total Casa Aum'
,'Total Deposit Aum'
,'Total Wealth Aum'
,'Total YTD NNM'
,'Total Casa NNM'
,'Total TD NNM'
]}
def grbuild(df):
grid_builder = GridOptionsBuilder.from_dataframe(df)
grid_builder.configure_selection(selection_mode='multiple',rowMultiSelectWithClick='True')
grid_builder.configure_default_column(min_column_width=5, resizable=True, filterable=True, sorteable= True)
grid_opt=grid_builder.build()
return grid_opt
def grdata(df):
ls=list(df["selected_rows"].values)
res=[]
val=[]
for item in ls:
s=str(item)
mat=re.search(r"\['(.*?)'\]",s)
if mat:
res.append(mat.group(1))
for i in res:
val.append(i)
return val
#Country
cntry={'Market':cntry_lst}
cntry_df=pd.DataFrame(cntry)
with st.sidebar:
cntry_df=AgGrid(cntry_df,gridOptions=grbuild(cntry_df),heigth=150)
#Matrix
mat_df=pd.DataFrame(mat_list)
with st.sidebar:
mat_df=AgGrid(mat_df,gridOptions=grbuild(mat_df))
#Segment
seg={'Segment':seg_lst}
seg_df=pd.DataFrame(seg)
with st.sidebar:
seg_df=AgGrid(seg_df,gridOptions=grbuild(seg_df),heigth=150)
#Months
mon={'Months':year_lst}
mon_df=pd.DataFrame(mon)
with st.sidebar:
mon_df=AgGrid(mon_df,gridOptions=grbuild(mon_df),heigth=150)
df_cols=[]
#Countries
cntry_col_nm=''
cntry_val=[]
if cntry_df["selected_rows"] is not None:
cntry_col_nm=list(cntry_df["selected_rows"])[0]
cntry_val=grdata(cntry_df)
if len(cntry_val) >0 :
df2 = df2[df2["Market"].isin (cntry_val)]
#Segment
seg_col_nm=''
seg_val=[]
if seg_df["selected_rows"] is not None:
seg_col_nm=list(seg_df["selected_rows"])[0]
seg_val=grdata(seg_df)
if len(seg_val) >0 :
df2 = df2[df2["Segment"].isin (seg_val)]
#Months
mon_col_nm=''
mon_val=[]
if mon_df["selected_rows"] is not None:
mon_col_nm=list(mon_df["selected_rows"])[0]
mon_val=grdata(mon_df)
if len(mon_val) >0 :
df2 = df2[df2["Months"].isin (mon_val)]
#Matrix
mat_val=[]
if mat_df["selected_rows"] is not None:
#mat_col_nm=list(mon_df["selected_rows"])[0]
mat_val=grdata(mat_df)
df_cols=[cntry_col_nm,mon_col_nm,seg_col_nm]
if len(mat_val) >0:
df_cols = df_cols + seg_val + mat_val
if len(df_cols)>0:
new = df2.filter(df_cols, axis=1)
#st.write(new)
grid_builder = GridOptionsBuilder.from_dataframe(new)
grid_builder.configure_default_column(min_column_width=5, resizable=True, filterable=True, sorteable= True)
grid_opt=grid_builder.build()
df_download =AgGrid(new,gridOptions=grid_opt)
#if len(df_cols) >0:
# def convert_for_download(df):
# return df.to_csv().encode("utf-8")
# csv = convert_for_download(df_download)
# st.download_button(
# label="Export Data",
# data=csv,
# file_name="data.csv",
# mime="text/csv",
# )
Comments
Post a Comment