import pandas as pd import gradio as gr import plotly.express as px # --- 1. Helper Functions --- def categorize_rank(r): """Categorizes the rank into SEO buckets.""" if pd.isna(r) or r == 0 or r == "NA": return "Not Talking (Intent Not Correct)" elif r <= 3: return "01–03" elif r <= 10: return "04–10" elif r <= 20: return "11–20" elif r <= 30: return "21–30" elif r <= 40: return "31–40" elif r <= 50: return "41–50" elif r <= 100: return "51–100" else: return "100+" def clean_and_process_sheet(df): """ Takes a raw dataframe from a single sheet, cleans columns, identifies dates, and adds Rank Range columns. """ # Clean Headers df.columns = df.columns.map(lambda x: str(x).strip()) non_date_cols = ["Keyword", "Avg. monthly searches"] available_non_date = [c for c in non_date_cols if c in df.columns] # Identify Date Columns potential_date_cols = [col for col in df.columns if col not in non_date_cols and "Unnamed" not in col] rename_dict = {} for col in potential_date_cols: try: cleaned = str(col).replace("_", " ").replace(".", " ").strip() d = pd.to_datetime(cleaned, errors="coerce", dayfirst=True) if pd.notna(d): rename_dict[col] = d.strftime("%d_%b_%Y") except: pass df.rename(columns=rename_dict, inplace=True) date_cols = list(rename_dict.values()) # Build Processed DataFrame processed_df = df[available_non_date].copy() for col in date_cols: range_col_name = f"{col}_Rank Range" raw_vals = pd.to_numeric(df[col], errors="coerce") processed_df[f"{col}_Rank_Numeric"] = raw_vals.fillna(0).astype(int) processed_df[range_col_name] = raw_vals.apply(categorize_rank) return processed_df def build_chart_and_filters(df): """ Generates the Plotly figure and filter choices for a specific dataframe. """ if df is None or df.empty: return None, [], [] # 1. Prepare Data for Chart viz_data = [] # Identify all date columns by looking for "_Rank Range" range_cols = [c for c in df.columns if "_Rank Range" in c] for range_col in range_cols: # Extract date from column name (e.g., "27_Oct_2025_Rank Range" -> "27_Oct_2025") month_name = range_col.replace("_Rank Range", "") counts = df[range_col].value_counts().reset_index() counts.columns = ['Range', 'Keyword Count'] counts['Month'] = month_name viz_data.append(counts) full_viz_df = pd.concat(viz_data) if viz_data else pd.DataFrame() # 2. Define Sorting Order correct_order = [ "01–03", "04–10", "11–20", "21–30", "31–40", "41–50", "51–100", "100+", "Not Talking (Intent Not Correct)" ] fig = None unique_months = [] if not full_viz_df.empty: unique_months = list(full_viz_df['Month'].unique()) fig = px.bar( full_viz_df, x='Range', y='Keyword Count', color='Month', barmode='group', # Side-by-side bars text_auto=True, title="Keyword Performance Distribution", category_orders={"Range": correct_order} ) return fig, unique_months, correct_order # --- 2. Gradio Event Functions --- def process_upload_initial(file): """ Reads ALL sheets, stores them in State, and renders the FIRST sheet. """ if file is None: return None, None, gr.Dropdown(choices=[]), None, None, gr.Dropdown(choices=[]), gr.Dropdown(choices=[]) excel = pd.ExcelFile(file.name) all_sheets_data = {} # Process every sheet and store in dictionary for sheet_name in excel.sheet_names: raw_df = pd.read_excel(file.name, sheet_name=sheet_name) all_sheets_data[sheet_name] = clean_and_process_sheet(raw_df) sheet_names = list(all_sheets_data.keys()) first_sheet_name = sheet_names[0] first_df = all_sheets_data[first_sheet_name] # Generate View for First Sheet fig, months, ranges = build_chart_and_filters(first_df) return ( all_sheets_data, # State: All Data first_df, # State: Current Sheet Data gr.Dropdown(choices=sheet_names, value=first_sheet_name), # Sheet Selector fig, # Chart first_df, # Table gr.Dropdown(choices=months, value=months[0] if months else None), # Month Filter gr.Dropdown(choices=ranges, value=ranges[0] if ranges else None) # Range Filter ) def change_sheet(selected_sheet, all_data): """ Switches the view when a new sheet is selected from dropdown. """ if not selected_sheet or not all_data: return None, None, None, gr.Dropdown(choices=[]), gr.Dropdown(choices=[]) new_df = all_data[selected_sheet] fig, months, ranges = build_chart_and_filters(new_df) return ( new_df, # Update Current Sheet State fig, # Update Chart new_df, # Update Table gr.Dropdown(choices=months, value=months[0] if months else None), # Update Month Choices gr.Dropdown(choices=ranges, value=ranges[0] if ranges else None) # Update Range Choices ) def filter_table(current_df, selected_month, selected_range): """ Filters the CURRENTLY active sheet's dataframe. """ if current_df is None or current_df.empty: return None target_col = f"{selected_month}_Rank Range" if target_col in current_df.columns: filtered_df = current_df[current_df[target_col] == selected_range] return filtered_df return current_df def reset_table(current_df): return current_df # --- 3. UI Layout --- with gr.Blocks(theme=gr.themes.Soft()) as demo: # STATES all_sheets_state = gr.State({}) # Stores DICTIONARY of all sheets {name: df} current_sheet_state = gr.State(pd.DataFrame()) # Stores currently visible sheet DF gr.Markdown("# 🚀 SEO Multi-Sheet Dashboard") # TOP ROW: Upload & Sheet Selection with gr.Row(): file_input = gr.File(label="Upload Excel (Multiple Sheets Supported)", file_types=[".xlsx"]) with gr.Column(): process_btn = gr.Button("📊 Load File", variant="primary") # NEW: Sheet Selector Dropdown sheet_dropdown = gr.Dropdown(label="📑 Select Sheet to Analyze", choices=[], interactive=True) # VISUALIZATION plot_output = gr.Plot(label="Keyword Distribution") # FILTER ROW gr.Markdown("### 🔍 Filter Data") with gr.Row(): month_dropdown = gr.Dropdown(label="Select Month", choices=[]) range_dropdown = gr.Dropdown(label="Select Rank Range", choices=[]) filter_btn = gr.Button("Apply Filter") reset_btn = gr.Button("Show All Rows") table_output = gr.DataFrame(interactive=False) # --- EVENTS --- # 1. Upload & Process (Loads all sheets, defaults to first) process_btn.click( fn=process_upload_initial, inputs=file_input, outputs=[ all_sheets_state, # Save all sheets current_sheet_state, # Save current sheet sheet_dropdown, # Update dropdown options plot_output, # Show chart table_output, # Show table month_dropdown, # Update filters range_dropdown ] ) # 2. Change Sheet (User selects "RD Monthly" etc.) sheet_dropdown.change( fn=change_sheet, inputs=[sheet_dropdown, all_sheets_state], outputs=[ current_sheet_state, plot_output, table_output, month_dropdown, range_dropdown ] ) # 3. Filter Data (Applied to current sheet) filter_btn.click( fn=filter_table, inputs=[current_sheet_state, month_dropdown, range_dropdown], outputs=table_output ) # 4. Reset Data reset_btn.click( fn=reset_table, inputs=current_sheet_state, outputs=table_output ) if __name__ == "__main__": demo.launch()