Spaces:
Running
Running
File size: 2,278 Bytes
bdf9a03 a407fc0 11b0043 a407fc0 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
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() |