app2.py

import pandas as pd
import numpy as np
import streamlit as st
from scipy.stats import norm


def read_excel_sheets(file_path):
    """Read an Excel file with multiple sheets and return a dictionary of DataFrames."""
    xls = pd.ExcelFile(file_path)
    sheets_data = {sheet: xls.parse(sheet) for sheet in xls.sheet_names}
    return sheets_data

def z_testes(n1, n2, p1, p2):
    """Perform a Z-test for proportions and return the p-value."""
    pooled_p = (n1 * p1 + n2 * p2) / (n1 + n2)
    se = np.sqrt(pooled_p * (1 - pooled_p) * (1 / n1 + 1 / n2))
    z = (p1 - p2) / se
    p_value = 2 * (1 - norm.cdf(abs(z)))
    return p_value

def Z_test_dataframes(sheets_data):
    """Process each sheet's DataFrame and calculate new DataFrames with Z-test results."""
    result_dataframes = {}
    for sheet_name, df in sheets_data.items():
        df = df.set_index(df.columns[0])  # Use the first column as index
        rows, cols = df.shape
        new_df = pd.DataFrame(index=df.index[:-1], columns=df.columns[1:])
        for i, row_name in enumerate(df.index[:-1]):
            for j, col_name in enumerate(df.columns[1:]):
                n1 = df.iloc[-1, 0]  # x_I1
                n2 = df.iloc[-1, j+1]  # x_Ij
                p1 = df.iloc[i, 0]  # x_1J
                p2 = df.iloc[i, j+1]  # x_ij
                p_value = z_testes(n1, n2, p1, p2)
                new_df.iloc[i, j] = p_value

        result_dataframes[sheet_name] = new_df

    return result_dataframes

# Streamlit app
def main():
    st.title("Interactive Excel Z-Test Analysis")

    uploaded_file = st.file_uploader("Upload an Excel file", type=["xlsx"])

    if uploaded_file is not None:
        # Read and process data
        sheets_data = read_excel_sheets(uploaded_file)
        result_dataframes = Z_test_dataframes(sheets_data)

        st.write("### Processed Tables")
        for sheet_name, df in result_dataframes.items():
            st.write(f"#### {sheet_name}")

            # Apply color formatting
            styled_df = df.style.applymap(lambda val: "background-color: lightgreen" if val < 0.05 else "background-color: lightcoral")

            # Display the styled dataframe
            st.dataframe(styled_df, use_container_width=True)

if __name__ == "__main__":
    main()