Spaces:

jay-analytics
/

Data_Transformation_ranking

Sleeping

Update app.py

7b72661 verified 9 months ago

8.41 kB

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()

	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()