mlwork / app.py
d142111015's picture mlml3197's picture
Update app.py (#3)
3dc9486 verified
Raw History Blame Contribute Delete
15.9 kB
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()