app.py

#pass="Leswhdc2023$!"

import streamlit as st
import pandas as pd
import plotly.express as px
import cx_Oracle as ora
import pandas as pd
from pandas_profiling import ProfileReport


QueryDatabase=False
if QueryDatabase:
    dsn="jdbc:oracle:thin:@//ep15-scan01:1521/cdrpr03_4.uhc.com"
    user="UHG_801117753"
    passw="MiscPassword2023$!" # Fake Password - don't share this or run without changing to your ID
    dsn_tns = ora.makedsn('ep15-scan01', '1521', service_name='cdrpr03_4.uhc.com') 

    # Create a connection object
    conn = ora.connect(user=user, password=passw, dsn=dsn_tns)

    # Create a cursor object
    c = conn.cursor()

    # Execute the SQL query and store the result in a pandas dataframe
    query = """
    select
        count(*) as RecordCount, 
        --Age, SID, MBR_ID, -- Optional toggle - remove these to collapse across members with a record count.
        TOPICID, TOPIC_DESC, TOPIC, PATHWAY, CATEGORY, INTERVENTION_DESC, 
        SYMPTOM_DESC, KNOWLEDGE_DESC, BEHAVIOR_DESC, STATUS_DESC, INT_CATEGORY_ID, RSALINEOFBUSINESS, TARGET, 
        CAREDESCRIPTOR, URGENCY, TOPICSOURCE, SIGNSSYMPTOMS, POC_SGN_SYMP_ID, SPOKENLANGUAGE, HEALTHTOPIC, 
        CATEGORYID, TARGETID, CAREID, CQM, Gender, Race, AgeGroup  
        from 
        (
        select
        aa.SBSCR_ID_TXT SID,
        a.MBR_ID,
        b.POC_PROB_ID TopicID,
        b.POC_PROB_DESC Topic_Desc,
        REPLACE(REPLACE(b.POC_PROB_NM,'/',''),' ','') Topic,
        a.POC_PROB_SRC_DESC Pathway, 
        (select POC_INTRVN_CATGY_NM from POC_INTRVN_CATGY pig where pig.POC_INTRVN_CATGY_ID = d.POC_INTRVN_CATGY_ID) as Category, 
        c.ADD_DESC Intervention_Desc,
        e.ADD_DESC Symptom_Desc,
        a.KNW_OTCOME_RT_ADD_DESC KNOWLEDGE_DESC, 
        a.BHV_OTCOME_RT_ADD_DESC BEHAVIOR_DESC, 
        a.STS_OTCOME_RT_ADD_DESC STATUS_DESC, 
        d.POC_INTRVN_CATGY_ID Int_Category_ID,     
        RSA_POP_TYP_ID,
        (select ref_desc from ref where ref_nm = 'rsaPopulationType' and ref_cd = RSA_POP_TYP_ID) as RSALineOfBusiness,
        (select POC_INTRVN_TGT_NM from POC_INTRVN_TGT pit where pit.POC_INTRVN_TGT_ID = d.POC_INTRVN_TGT_ID) as Target, 
        (select POC_INTRVN_CARE_DESC from POC_INTRVN_CARE pic where pic.POC_INTRVN_CARE_ID = d.POC_INTRVN_CARE_ID) as CareDescriptor,       
        Case to_char(a.POC_PROB_URGNCY_MOD_ID) when '1' then 'Actual' when '3' then 'Potential' else 'Other' end as Urgency, 
        (select ref_desc from ref where ref_nm = 'pocProbSourceType' and ref_cd = a.POC_PROB_SRC_TYP_ID) as TopicSource,
        (select POC_SGN_SYMP_NM from POC_SGN_SYMP pss where pss.POC_SGN_SYMP_ID = e.POC_SGN_SYMP_ID) as SignsSymptoms,
        e.POC_SGN_SYMP_ID,
        CALAP_SPOKEN_LANG_TYP_ID SpokenLanguage,
        REPLACE(b.POC_PROB_NM,'/','') HealthTopic,
        a.POC_PROB_ID HealthTopicID,
        d.POC_INTRVN_CATGY_ID CategoryID,
        d.POC_INTRVN_TGT_ID TargetID,
        d.POC_INTRVN_CARE_ID CareID,
        c.CQM_IND CQM,

        aa.GDR_CD Gender, 
        aa.RACE_CD Race,
        (2023 - EXTRACT(year FROM aa.BTH_DT)) Age,
        Case --Five age groups: 0-18, 19-44, 45-64, 65-84, and 85 and over
                when ((2023 - EXTRACT(year FROM aa.BTH_DT))>=0 and (2023 - EXTRACT(year FROM aa.BTH_DT))<=18) then 'Age0to18' 
                when ((2023 - EXTRACT(year FROM aa.BTH_DT))> 18 and (2023 - EXTRACT(year FROM aa.BTH_DT))<=44) then 'Age19to44' 
                when ((2023 - EXTRACT(year FROM aa.BTH_DT))> 44 and (2023 - EXTRACT(year FROM aa.BTH_DT))<=64) then 'Age44to64' 
                when ((2023 - EXTRACT(year FROM aa.BTH_DT))> 64 and (2023 - EXTRACT(year FROM aa.BTH_DT))<=84) then 'Age64to84' 
                when ((2023 - EXTRACT(year FROM aa.BTH_DT))> 85) then 'Age85andOver' 
                else 'Other' end as AgeGroup
        
    from MBR_POC_PROB a   -- select * from MBR_POC_PROB where MBR_ID=117179570
        join MBR aa on a.MBR_ID = aa.MBR_ID  --and a.POC_PROB_URGNCY_MOD_ID = 1 --actual
        join STG_HSR.POC_PROB b on a.POC_PROB_ID = b.POC_PROB_ID 
        join MBR_POC_PROB_INTRVN c on 
            (a.MBR_POC_PROB_ID = c.MBR_POC_PROB_ID and c.REMV_FROM_PLN_LIST_IND=0)
        join POC_INTRVN d on c.POC_INTRVN_ID = d.POC_INTRVN_ID 
        left outer join MBR_POC_PROB_SGN_SYMP e 
            on (e.MBR_POC_PROB_ID = c.MBR_POC_PROB_ID) 
        where a.POC_PROB_URGNCY_MOD_ID = 1 and 

            -- Optional toggle - 1 versus 120 days.
            a.CHG_DTTM > sysdate - 1    -- 1 Day
            -- a.CHG_DTTM > sysdate - 1    -- 182 Days = 6 Months
            
        ) i
        group by 
        --Age, SID, MBR_ID, -- Optional toggle - remove these to collapse across members with a record count.
        TOPICID, TOPIC_DESC, TOPIC, PATHWAY, CATEGORY, INTERVENTION_DESC, SYMPTOM_DESC, KNOWLEDGE_DESC, BEHAVIOR_DESC, STATUS_DESC,INT_CATEGORY_ID,RSALINEOFBUSINESS, 
        TARGET, CAREDESCRIPTOR, URGENCY, TOPICSOURCE,   SIGNSSYMPTOMS, POC_SGN_SYMP_ID, SPOKENLANGUAGE, HEALTHTOPIC, 
        CATEGORYID, TARGETID, CAREID, CQM, Gender, Race, AgeGroup    
        -- Optional toggle:
        --order by MBR_ID desc
        order by TOPICID desc -- orders by Count
            """
    df = pd.read_sql(query, con=conn)
    # Close the cursor and connection
    c.close()
    conn.close()
    # Show the dataframe in a streamlit grid
    st.dataframe(df)

# automatic visualizer

# st.set_page_config(page_title="File Upload and Profiling", layout="wide")

st.title("File Upload and Profiling")

uploaded_file = st.file_uploader("Upload a CSV file", type="csv")

RunProfiler=False
if uploaded_file is not None:
    if RunProfiler:

        # Load the data using pandas
        df = pd.read_csv(uploaded_file)

        # Generate the pandas profiling report
        profile = ProfileReport(df, explorative=True)

        # Display the pandas profiling report using streamlit
        st.header("Data Profiling Report")
        st.write(profile.to_html(), unsafe_allow_html=True)

        # Display word statistics for each categorical string column
        cat_cols = df.select_dtypes(include='object').columns
        st.header("Word Statistics for Categorical Columns")
        for col in cat_cols:
            st.subheader(col)
            word_count = df[col].str.split().apply(len).value_counts().sort_index()
            st.bar_chart(word_count)

    # Grouped count by each feature
    num_cols = df.select_dtypes(include=['float', 'int']).columns
    st.header("Grouped Count by Each Feature")
    for col in num_cols:
        st.subheader(col)
        count_by_feature = df.groupby(col).size().reset_index(name='count')
        st.bar_chart(count_by_feature)


# Upload a CSV dataset
uploaded_file = st.file_uploader("Upload your dataset", type=["csv"])
if uploaded_file is not None:
    # Load the dataset and display the first 5 rows
    df = pd.read_csv(uploaded_file)
    st.dataframe(df.head())

    # Generate a treemap or sunburst plot based on data types
    numerical_cols = df.select_dtypes(include=["float", "int"]).columns
    categorical_cols = df.select_dtypes(include=["object"]).columns

    fig = px.treemap(df, path=categorical_cols)
    st.plotly_chart(fig)
        
    #if len(numerical_cols) >= 2:
    #    fig = px.scatter_matrix(df, dimensions=numerical_cols)
    #    st.plotly_chart(fig)
    #elif len(categorical_cols) >= 2:
    #    fig = px.treemap(df, path=categorical_cols)
    #    st.plotly_chart(fig)
    #else:
    #    fig = px.sunburst(df, path=categorical_cols + numerical_cols)
    #    st.plotly_chart(fig)