import streamlit as st import pandas as pd import io import xlsxwriter def main(): st.title("My Response Refiner") # Example text st.write("Hello, this is an app specialized for sorting and visualizing the customers' responses!") # File upload section uploaded_file = st.file_uploader("Upload your data file", type=["csv", "xlsx"]) if uploaded_file is not None: try: # Check file type and read the file accordingly if uploaded_file.name.endswith(".csv"): df = pd.read_csv(uploaded_file) elif uploaded_file.name.endswith(".xlsx"): df = pd.read_excel(uploaded_file) color_order = ['#36516E', '#99CCE0', '#D6DCE5', '#A5A5A5', '#636363', '#74A9E8'] # Set color palette using the color mapping color_mapping = { "#36516E": [ "Very knowledgeable", "Completely related", "Completely agree", "Extremely high increase", "Completely not challenging", "Very confident", "Completely satisfied", "Very good", "Very easy", "Entire organisation", "Extremely High increase", "매우 잘 알고 있음", "매우 그렇다", "전혀 어렵지 않았다", "매우 만족", "매우 도움이 되었다" ], "#99CCE0": [ "Some knowledge", "Related", "Agree", "High increase", "Not challenging", "Some confidence", "Satisfied", "Good", "Easy", "Region only", "어느 정도 알고 있음", "그렇다", "어렵지 않았다", "만족", "도움이 되었다" ], "#D6DCE5": [ "Slightly related", "Neutral", "Moderate increase", "Adequate", "Department only", "보통이다", "보통이었다", "보통" ], "#A5A5A5": [ "Limited knowledge", "Not sure", "Slight increase", "Challenging", "Limited confidence", "Dissatisfied", "Difficult", "Team only", "기본적인 내용만 알고 있음", "그렇지 않다", "어려웠다", "불만족", "도움이 되지 않았다" ], "#636363": [ "No knowledge at all", "Completely unrelated", "Disagree", "No increase at all", "Completely challenging", "No confidence", "Completely dissatisfied", "Poor", "Very difficult", "Self only", "지식이 전혀 없음", "전혀 그렇지 않다", "매우 어려웠다", "매우 불만족", "전혀 도움이 되지 않았다" ], "#74A9E8": [ "No authority" ] } # Helper function to get the color based on the response def get_response_color(response): for color, responses in color_mapping.items(): if response in responses: return color return "#36516E" # Default color if not found # Get all columns that start with "Question" question_columns = [col for col in df.columns if col.startswith("Question")] # Create a dictionary to store the new column names new_column_names = {} # Iterate through the question columns for col in question_columns: # Get the first non-null value in the column first_value = df[col].dropna().iloc[0] if not df[col].dropna().empty else col # Create the new column name for the Question column new_column_names[col] = f"Question: {first_value}" # Find the index of the current column col_index = df.columns.get_loc(col) # Check if there's a next column and rename it if col_index + 1 < len(df.columns): next_col = df.columns[col_index + 1] new_column_names[next_col] = f"Response: {first_value}" # Rename the columns df.rename(columns=new_column_names, inplace=True) list_of_columns = list(df.columns) # Multiselect dropdown to allow user to choose the columns to keep columns_to_keep = st.multiselect("Select the columns you want to keep:", list_of_columns) if columns_to_keep: # Create a new DataFrame with only the selected columns df_selected = df[columns_to_keep] # Get all columns that start with "Response: " response_columns = [col for col in df_selected.columns if col.startswith("Response:")] # Iterate through the response columns for i, col in enumerate(response_columns): # Count the frequency of each response in this specific column frequency = df_selected[col].value_counts() col2 = col.replace("Response: ", "") # Create a new column name for the frequency freq_col_name = f"Frequency: {col2}" # Add the frequency column right next to the response column insert_position = df_selected.columns.get_loc(col) + 1 df_selected.insert(insert_position, freq_col_name, df_selected[col].map(frequency)) # Create a BytesIO object to save the Excel file in memory excel_buffer = io.BytesIO() writer = pd.ExcelWriter(excel_buffer, engine='xlsxwriter') workbook = writer.book # Keep track of used sheet names used_sheet_names = {} # Loop through each triplet of columns, process each as a separate DataFrame, and save to an Excel sheet for i in range(0, len(df_selected.columns), 3): # Ensure we have complete triplets of columns (question, response, frequency) if i + 2 < len(df_selected.columns): question_col = df_selected.columns[i] response_col = df_selected.columns[i + 1] frequency_col = df_selected.columns[i + 2] # Get the question text (after "Question: " prefix) question_text = question_col.replace("Question: ", "").strip() # Prepare the DataFrame for this triplet # Step 1: Select the desired columns df_triplet = df_selected[[question_col, response_col, frequency_col]] # print(df_triplet[response_col]) # Step 2: Drop rows where response_col has NaN values df_triplet.dropna(subset=[response_col], inplace=True) # print(df_triplet[response_col]) # Step 3: Drop duplicate rows based on response_col and apply the change in place df_triplet.drop_duplicates(subset=[response_col], inplace=True) print(df_triplet[response_col].iloc[0]) if isinstance(df_triplet[response_col].iloc[0], float): # Assuming response_col contains all float values df_triplet[response_col] = (df_triplet[response_col] * 100).fillna(0).astype(int).astype(str) + '%' # df_triplet[response_col] = df_triplet[response_col].astype(str) # print(type(df_triplet[response_col].iloc[0])) # print(df_triplet[response_col].iloc[0][-1]) if df_triplet[response_col].iloc[0][-1]=="%": df_triplet["response_numeric"] = pd.to_numeric(df_triplet[response_col].str.rstrip('%'), errors='coerce') # Sort the DataFrame by the numeric values in response_numeric df_triplet = df_triplet.sort_values(by='response_numeric', ascending=True) df_triplet['color'] = df_triplet[response_col].apply(get_response_color) else: # df_triplet = df_triplet.sort_values(by=response_col, ascending=True) # Apply the response color function df_triplet['color'] = df_triplet[response_col].apply(get_response_color) # Set the color column as a categorical variable and apply the correct ordering df_triplet['color'] = df_triplet['color'].astype("category") df_triplet['color'] = df_triplet['color'].cat.set_categories(color_order, ordered=True) # Sort by color and use inplace=True to make sure it's modified in place df_triplet.sort_values(by="color", inplace=True) # Ensure a valid sheet name valid_sheet_name = "".join([c for c in question_text if c.isalnum() or c in [' ', '_']]).replace(" ", "_") valid_sheet_name = valid_sheet_name[:29] # If sheet name is already used, append a unique number to avoid duplication if valid_sheet_name in used_sheet_names: used_sheet_names[valid_sheet_name] += 1 valid_sheet_name = f"{used_sheet_names[valid_sheet_name]}_{valid_sheet_name}" else: used_sheet_names[valid_sheet_name] = 1 # Write the DataFrame to an Excel sheet df_triplet.to_excel(writer, sheet_name=valid_sheet_name, index=False) # Create a worksheet for this triplet worksheet = writer.sheets[valid_sheet_name] # Add chart selection option chart_type = st.selectbox("Select chart type for analysis:", ["Pie Chart", "Bar Chart"], key=f"chart_type_{i//3}") # Create the appropriate chart based on the user's selection if chart_type == "Pie Chart": # Create a pie chart object chart = workbook.add_chart({'type': 'pie'}) # Define the data for the chart categories_range = f"='{valid_sheet_name}'!B2:B{len(df_triplet) + 1}" # Response column (Category names) values_range = f"='{valid_sheet_name}'!C2:C{len(df_triplet) + 1}" # Frequency column # Add series to the chart with Category Names (responses), values, and percentages chart.add_series({ 'categories': categories_range, 'values': values_range, 'data_labels': { 'category': True, # Show Category Names (Response) 'percentage': True, # Show percentage 'value': False, # Show value 'position': 'outside_end', # Position labels outside the pie chart 'font': {'size': 20, 'bold': False, 'color': '#36516E'} # Set font size to 14 and color to #36516E }, 'points': [{'fill': {'color': row_color}} for row_color in df_triplet['color']] # Apply the sorted colors }) # Disable the chart legend chart.set_legend({'none': True}) # Insert the chart into the worksheet worksheet.insert_chart('E5', chart) elif chart_type == "Bar Chart": # Create a bar chart object chart = workbook.add_chart({'type': 'column'}) # Define the data for the chart categories_range = f"='{valid_sheet_name}'!B2:B{len(df_triplet) + 1}" # Response column (Category names) values_range = f"='{valid_sheet_name}'!C2:C{len(df_triplet) + 1}" # Frequency column # Add series to the chart with Category Names (responses) and values chart.add_series({ 'categories': categories_range, 'values': values_range, 'data_labels': { 'value': True, # Show value on the bars 'font': {'size': 14, 'bold': False, 'color': '#36516E'} # Set font size to 14 and color to #36516E }, 'fill': {'color': '#36516E'}, 'legend_key': False # Don't show the legend key }) # Set x-axis title chart.set_x_axis({ 'visible': True, # Hide x-axis titles 'label_position': 'low', # Position labels at the bottom 'font': {'size': 16, 'color': '#36516E'}, # Axis label size and color }) # Set y-axis title chart.set_y_axis({ 'visible': True, # Hide y-axis titles 'font': {'size': 16, 'color': '#36516E'}, 'major_gridlines': {'visible': False}, }) # Disable the chart legend chart.set_legend({'none': True}) # Insert the chart into the worksheet worksheet.insert_chart('E5', chart) writer._save() # Download button for the Excel file st.download_button( label="Download Excel file", data=excel_buffer.getvalue(), file_name="Processed_Survey_Responses.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ) except Exception as e: st.error(f"An error occurred: {e}") if __name__ == "__main__": main()