From Hoarded Expertise to Automated Insights: Distilling Data Workflows

import pandas as pd
from IPython.display import display

# NOTE: This cell assumes 'df2' (the result of the aggregation step) is available.

print("Adjusting SEMRush columns that were not part of competitor-columns pivot...")

# Assign aggregating function to each metric
# The chosen functions are critical for creating a single, best-case summary per keyword:
# - 'min' for Position: Gives the *best* rank achieved across all competitors who rank.
# - 'max' for Search Volume/Number of Results/Timestamp: Captures the highest value reported.
# - 'sum' for Traffic/Traffic Cost: Aggregates the total value across all competitor results.
# - 'mean' for Difficulty/CPC/Competition: Averages the difficulty/cost across all reporting sources.
# - 'first' for categorical data (URLs, Intents, SERP Features): Chooses the first encountered value.
agg_funcs = {
    'Position': 'min',
    'Search Volume': 'max',
    'CPC': 'mean',
    'Traffic': 'sum',
    'Traffic (%)': 'mean',
    'Traffic Cost': 'sum',
    'Keyword Difficulty': 'mean',
    'Previous position': 'first',
    'Competition': 'mean',
    'Number of Results': 'max',
    'Timestamp': 'max',
    'SERP Features by Keyword': 'first',
    'Keyword Intents': 'first',
    'Position Type': 'first',
    'URL': 'first',
    'Competitor URL': 'first',
    'Client URL': 'first'
}

# Apply the aggregation across the combined dataset (df2)
agg_df = df2.groupby('Keyword').agg(agg_funcs).reset_index()

# Add a derived metric: Keyword word count
agg_df['Number of Words'] = agg_df["Keyword"].apply(lambda x: len(x.split()))

# Drop the 'Position' column: It was only used for the pivot/min operation,
# but it's redundant/misleading now that the competitor position data is in pivot_df.
agg_df.drop(columns=['Position'], inplace=True)

print("Table of aggregates prepared.")

display(agg_df)

# Store the aggregated metrics in the pipeline
pip.set(job, 'keyword_aggregate_df_json', agg_df.to_json(orient='records'))

# The explicit story:
# For each unique 'Keyword' in df2:
#   Look at all rows for that Keyword.
#   For the 'Position' column in those rows, find the minimum value.
#   For the 'Search Volume' column, find the maximum value.
#   For the 'CPC' column, calculate the average value.
#   For the 'URL' column, just take the first one you find.
#   ...and so on for all columns in agg_funcs
agg_df = df2.groupby('Keyword').agg(agg_funcs).reset_index()

# In Notebooks/gap_analyzer_sauce.py
import pandas as pd
from pipulate import pip # Ensure pip is imported

# (Keep previously added functions)
# ...

def aggregate_semrush_metrics(job: str, df2: pd.DataFrame):
    """
    Aggregates metrics in the combined SEMRush DataFrame (df2) by Keyword,
    stores the aggregated DataFrame in pip state, and returns it.

    Args:
        job (str): The current Pipulate job ID.
        df2 (pd.DataFrame): The combined master DataFrame.

    Returns:
        pd.DataFrame: The aggregated DataFrame (agg_df), or an empty DataFrame on error.
    """
    if df2.empty:
        print("⚠️ Input DataFrame (df2) is empty. Cannot perform aggregation.")
        return pd.DataFrame()

    print("📊 Aggregating SEMRush metrics per keyword...")

    # --- CORE LOGIC (Moved from Notebook) ---
    try:
        agg_funcs = {
            'Position': 'min', # Best position achieved
            'Search Volume': 'max', # Highest reported volume
            'CPC': 'mean', # Average CPC
            'Traffic': 'sum', # Total traffic (though often dropped later)
            'Traffic (%)': 'mean', # Average traffic % (though often dropped later)
            'Traffic Cost': 'sum', # Total traffic cost (though often dropped later)
            'Keyword Difficulty': 'mean', # Average difficulty
            'Previous position': 'first', # Arbitrary choice for non-aggregatable
            'Competition': 'mean', # Average competition score
            'Number of Results': 'max', # Highest number of results
            'Timestamp': 'max', # Latest timestamp
            'SERP Features by Keyword': 'first', # Arbitrary choice
            'Keyword Intents': 'first', # Arbitrary choice
            'Position Type': 'first', # Arbitrary choice
            'URL': 'first', # Arbitrary choice (often competitor URL if client doesn't rank)
            'Competitor URL': 'first', # First competitor URL found for the keyword
            'Client URL': 'first' # First client URL found (might be NaN)
        }

        # Check which columns actually exist in df2 before aggregating
        valid_agg_funcs = {k: v for k, v in agg_funcs.items() if k in df2.columns}
        missing_agg_cols = [k for k in agg_funcs if k not in df2.columns]
        if missing_agg_cols:
            print(f"  ⚠️ Columns not found for aggregation, will be skipped: {', '.join(missing_agg_cols)}")

        agg_df = df2.groupby('Keyword').agg(valid_agg_funcs).reset_index()

        # Calculate 'Number of Words' only if 'Keyword' column exists
        if 'Keyword' in agg_df.columns:
             agg_df['Number of Words'] = agg_df["Keyword"].astype(str).apply(lambda x: len(x.split()))
        else:
             print("  ⚠️ 'Keyword' column not found after aggregation. Cannot calculate 'Number of Words'.")


        # Drop 'Position' - It's misleading after aggregation here.
        # The real position data is kept in the pivot_df generated earlier.
        if 'Position' in agg_df.columns:
            agg_df.drop(columns=['Position'], inplace=True)

        print("  ✅ Table of aggregates prepared.")
        rows, cols = agg_df.shape
        print(f"  Rows: {rows:,}, Columns: {cols:,}")


        # --- OUTPUT (to pip state) ---
        pip.set(job, 'keyword_aggregate_df_json', agg_df.to_json(orient='records'))
        print(f"💾 Stored aggregated DataFrame in pip state for job '{job}'.")
        # ---------------------------

        # --- RETURN VALUE ---
        return agg_df # Return the DataFrame for display and next step

## The Architect's Blueprint: Modularizing the Aggregation Logic

    except Exception as e:
        print(f"❌ An error occurred during aggregation: {e}")
        pip.set(job, 'keyword_aggregate_df_json', pd.DataFrame().to_json(orient='records'))
        return pd.DataFrame() # Return empty DataFrame

# %% editable=true slideshow={"slide_type": ""}
from IPython.display import display
import gap_analyzer_sauce # Ensure module is imported

# This function now handles:
# 1. Defining aggregation rules for each metric.
# 2. Grouping df2 by Keyword and applying aggregations.
# 3. Calculating 'Number of Words'.
# 4. Dropping the aggregated 'Position' column.
# 5. Storing the resulting agg_df in pip state.
# 6. Returning agg_df for display and use in the next step.
# It receives df2 directly from the previous cell's variable.
agg_df = gap_analyzer_sauce.aggregate_semrush_metrics(job, df2)

# Display the aggregated data
display(agg_df)

# Optional verification
# print("\nVerification:")
# print(f"  Agg DF stored: {'keyword_aggregate_df_json' in pip.read_state(job)}")
# loaded_agg_df = pd.read_json(StringIO(pip.get(job, 'keyword_aggregate_df_json', '[]'))) # Use StringIO for verification
# print(f"  Agg DF rows in state: {len(loaded_agg_df)}")

import pandas as pd
from pathlib import Path
from IPython.display import display

# NOTE: This cell assumes 'job', 'pivot_df', 'agg_df', and 'filter_file' are defined in prior cells.

# --- PATH DEFINITION ---
# The filter file path is already defined in a previous step, but included here for clarity
filter_file = Path("data") / f"{job}_filter_keywords.csv"

# --- REQUIRED SUPPORT FUNCTION (Surgically Ported from botifython.py) ---

def reorder_columns_surgical(df, priority_column, after_column):
    """
    Surgical port of bf.reorder_columns: Moves a column immediately after a specified column.
    """
    if priority_column in df.columns and after_column in df.columns:
        columns = df.columns.drop(priority_column).tolist()
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        return df[columns]
    elif priority_column not in df.columns:
        print(f"⚠️ Warning: Priority column '{priority_column}' not found for reorder.")
    elif after_column not in df.columns:
        print(f"⚠️ Warning: After column '{after_column}' not found for reorder.")
    return df


print("Merging Pivot Data with Aggregate Data...")

# 1. Merge Pivot Data (Keyword as index) with Aggregate Data (Keyword as index/column)
pivotmerge_df = pd.merge(pivot_df.reset_index(), agg_df, on='Keyword', how='left')

print("Pivot and Aggregate Data Joined.\n")
rows, columns = pivotmerge_df.shape
print(f"Rows: {rows:,}")
print(f"Cols: {columns:,}")

# --- FILTERING LOGIC ---

print("\nBrand and Negative Filters being applied...")
# 2. Optionally Filter Brand & Negative Keywords
if filter_file.exists():
    df_filter = pd.read_csv(filter_file, header=0)
    
    # Ensure all list items are strings before joining into a regex pattern
    kw_filter = [str(f) for f in df_filter["Filter"].dropna().tolist()]
    
    if kw_filter:
        # Use re.escape to handle special characters in keywords and then join with '|' (OR)
        pattern = '|'.join([re.escape(keyword) for keyword in kw_filter])
        
        # Apply the filter: keep rows where Keyword DOES NOT contain the pattern
        filtered_df = pivotmerge_df[~pivotmerge_df["Keyword"].str.contains(pattern, case=False, na=False)]
        print(f"✅ Filter applied using {len(kw_filter)} terms from '{filter_file}'.")
    else:
        filtered_df = pivotmerge_df
        print("⚠️ Filter file exists but contains no terms. Skipping filter application.")
else:
    filtered_df = pivotmerge_df
    print(f"☑️ No filter file found at '{filter_file}'. Skipping negative filtering.")

rows_filtered, columns_filtered = filtered_df.shape
print(f"Rows: {rows_filtered:,} ({rows:,} - {rows_filtered:,} = {rows - rows_filtered:,} rows removed)")


# --- REORDERING AND FINAL POLISH ---

# 3. Apply Reordering Logic (Using the surgically defined function)
# NOTE: The original logic chains reorders based on previously moved columns.
temp_df = filtered_df.copy() # Use a temporary variable for clarity during chained operations

temp_df = reorder_columns_surgical(temp_df, "Search Volume", after_column="Keyword")
temp_df = reorder_columns_surgical(temp_df, "Number of Words", after_column="CPC")
temp_df = reorder_columns_surgical(temp_df, "CPC", after_column="Number of Words")
temp_df = reorder_columns_surgical(temp_df, "Number of Results", after_column="Position Type")
temp_df = reorder_columns_surgical(temp_df, "Timestamp", after_column="Number of Results")
temp_df = reorder_columns_surgical(temp_df, "Competitor URL", after_column="Client URL")

# 4. Final Arrange (Verbatim column ordering and sorting)
# The manual reorder logic below overrides the custom function, but we include it verbatim:
rest_of_columns = [col for col in temp_df.columns if col not in ['Keyword', 'Search Volume']]
new_column_order = ['Keyword', 'Search Volume'] + rest_of_columns

# The conditional block from the original (verbatim)
if 'Keyword' in temp_df.columns:
    temp_df = temp_df[['Keyword'] + ['Search Volume'] + [col for col in temp_df.columns if col not in ['Keyword', 'Search Volume']]]

# Apply the intended final order
filtered_df = temp_df[new_column_order]

# Final sorting and column drops
arranged_df = filtered_df.sort_values(by='Search Volume', ascending=False)
arranged_df.drop(columns=["Previous position", "Traffic", "Traffic (%)", "Traffic Cost"], inplace=True)

print("\nFinal Keyword Table Prepared.")

# Store the final result in the pipeline
pip.set(job, 'filtered_gap_analysis_df_json', arranged_df.to_json(orient='records'))

display(arranged_df)

import pandas as pd
from pathlib import Path
from IPython.display import display
import re # Added import

# NOTE: This cell assumes 'job', 'pivot_df', 'agg_df', and 'filter_file' are defined in prior cells.

# --- PATH DEFINITION ---
# The filter file path is already defined in a previous step, but included here for clarity
filter_file = Path("data") / f"{job}_filter_keywords.csv"

# --- REQUIRED SUPPORT FUNCTION (Surgically Ported from botifython.py) ---

def reorder_columns_surgical(df, priority_column, after_column):
    """
    Surgical port of bf.reorder_columns: Moves a column immediately after a specified column.
    """
    if priority_column in df.columns and after_column in df.columns:
        columns = df.columns.drop(priority_column).tolist()
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        return df[columns]
    elif priority_column not in df.columns:
        print(f"⚠️ Warning: Priority column '{priority_column}' not found for reorder.")
    elif after_column not in df.columns:
        print(f"⚠️ Warning: After column '{after_column}' not found for reorder.")
    return df


print("Merging Pivot Data with Aggregate Data...")

# 1. Merge Pivot Data (Keyword as index) with Aggregate Data (Keyword as index/column)
# Ensure 'Keyword' is a column in both before merging
pivot_df_reset = pivot_df.reset_index()
agg_df_reset = agg_df # agg_df should already have Keyword as a column due to reset_index() earlier

pivotmerge_df = pd.merge(pivot_df_reset, agg_df_reset, on='Keyword', how='left')


print("Pivot and Aggregate Data Joined.\n")
rows, columns = pivotmerge_df.shape
print(f"Rows: {rows:,}")
print(f"Cols: {columns:,}")

# --- FILTERING LOGIC ---

print("\nBrand and Negative Filters being applied...")
# 2. Optionally Filter Brand & Negative Keywords
if filter_file.exists():
    df_filter = pd.read_csv(filter_file, header=0)

    # Ensure all list items are strings before joining into a regex pattern
    kw_filter = [str(f) for f in df_filter["Filter"].dropna().tolist()]

    if kw_filter:
        # Use re.escape to handle special characters in keywords and then join with '|' (OR)
        pattern = '|'.join([re.escape(keyword) for keyword in kw_filter])

        # Apply the filter: keep rows where Keyword DOES NOT contain the pattern
        filtered_df = pivotmerge_df[~pivotmerge_df["Keyword"].str.contains(pattern, case=False, na=False)]
        print(f"✅ Filter applied using {len(kw_filter)} terms from '{filter_file}'.")
    else:
        filtered_df = pivotmerge_df
        print("⚠️ Filter file exists but contains no terms. Skipping filter application.")
else:
    filtered_df = pivotmerge_df
    print(f"☑️ No filter file found at '{filter_file}'. Skipping negative filtering.")

rows_filtered, columns_filtered = filtered_df.shape
print(f"Rows: {rows_filtered:,} ({rows:,} - {rows_filtered:,} = {rows - rows_filtered:,} rows removed)")


# --- REORDERING AND FINAL POLISH ---

# 3. Apply Reordering Logic (Using the surgically defined function)
# NOTE: The original logic chains reorders based on previously moved columns.
temp_df = filtered_df.copy() # Use a temporary variable for clarity during chained operations

temp_df = reorder_columns_surgical(temp_df, "Search Volume", after_column="Keyword")
temp_df = reorder_columns_surgical(temp_df, "Number of Words", after_column="CPC") # Original logic might error if CPC missing
temp_df = reorder_columns_surgical(temp_df, "CPC", after_column="Number of Words")
temp_df = reorder_columns_surgical(temp_df, "Number of Results", after_column="Position Type") # Original might error
temp_df = reorder_columns_surgical(temp_df, "Timestamp", after_column="Number of Results")
temp_df = reorder_columns_surgical(temp_df, "Competitor URL", after_column="Client URL") # Original might error

# 4. Final Arrange (Verbatim column ordering and sorting)
# Ensure essential columns exist before attempting reorder
essential_cols = ['Keyword', 'Search Volume']
if all(col in temp_df.columns for col in essential_cols):
     rest_of_columns = [col for col in temp_df.columns if col not in essential_cols]
     new_column_order = essential_cols + rest_of_columns
     # Check if the generated order is valid before applying
     if set(new_column_order) == set(temp_df.columns):
          filtered_df = temp_df[new_column_order]
     else:
          print("⚠️ Warning: Column mismatch during final arrange. Skipping reorder.")
          filtered_df = temp_df # Fallback to previous state
else:
     print("⚠️ Warning: Essential columns ('Keyword', 'Search Volume') missing. Skipping final arrange.")
     filtered_df = temp_df # Fallback

# Final sorting and column drops (check if columns exist before dropping)
sort_col = "Search Volume" if "Search Volume" in filtered_df.columns else "Keyword" # Fallback sort
arranged_df = filtered_df.sort_values(by=sort_col, ascending=False)

cols_to_drop = ["Previous position", "Traffic", "Traffic (%)", "Traffic Cost"]
existing_cols_to_drop = [col for col in cols_to_drop if col in arranged_df.columns]
if existing_cols_to_drop:
    arranged_df.drop(columns=existing_cols_to_drop, inplace=True)

print("\nFinal Keyword Table Prepared.")

# Store the final result in the pipeline
pip.set(job, 'filtered_gap_analysis_df_json', arranged_df.to_json(orient='records'))

display(arranged_df)

# In Notebooks/gap_analyzer_sauce.py
import pandas as pd
from pathlib import Path
import re # Added import
import json # Added import
from pipulate import pip # Ensure pip is imported

# (Keep previously added functions)
# ...

# --- Helper Function (Make private or ensure it's defined once) ---
# Assuming _extract_registered_domain is already defined above

def _reorder_columns_surgical(df, priority_column, after_column):
    """
    Private helper: Moves a column immediately after a specified column. Handles missing columns.
    """
    # Check if columns exist *before* trying to manipulate
    if priority_column not in df.columns:
        print(f"  ⚠️ Reorder Warning: Priority column '{priority_column}' not found. Skipping.")
        return df
    if after_column not in df.columns:
        print(f"  ⚠️ Reorder Warning: After column '{after_column}' not found. Placing '{priority_column}' at end.")
        # Fallback: Move to end if after_column is missing
        cols = [col for col in df.columns if col != priority_column] + [priority_column]
        return df[cols]

    # Proceed with reordering if both columns exist
    columns = df.columns.drop(priority_column).tolist()
    try:
        after_column_index = columns.index(after_column)
        columns.insert(after_column_index + 1, priority_column)
        return df[columns]
    except ValueError:
         # This case should ideally not happen if after_column check passed, but good to have safeguard
         print(f"  ⚠️ Reorder Warning: Index for '{after_column}' not found unexpectedly. Placing '{priority_column}' at end.")
         cols = [col for col in df.columns if col != priority_column] + [priority_column]
         return df[cols]


# --- Main Function ---
def merge_filter_arrange_data(job: str, pivot_df: pd.DataFrame, agg_df: pd.DataFrame):
    """
    Merges pivot and aggregate DataFrames, applies keyword filters, reorders/drops columns,
    sorts the result, stores it in pip state, and returns the final DataFrame.

    Args:
        job (str): The current Pipulate job ID.
        pivot_df (pd.DataFrame): The pivoted DataFrame from the previous step.
        agg_df (pd.DataFrame): The aggregated DataFrame from the previous step.

    Returns:
        pd.DataFrame: The final, arranged DataFrame, or an empty DataFrame on error.
    """
    if pivot_df.empty or agg_df.empty:
        print("⚠️ Input DataFrame(s) (pivot_df or agg_df) are empty. Cannot merge and filter.")
        return pd.DataFrame()

    print("🧩 Merging Pivot Data with Aggregate Data...")

    # --- PATH DEFINITION ---
    filter_file = Path("data") / f"{job}_filter_keywords.csv"

    # --- CORE LOGIC (Moved from Notebook) ---
    try:
        # 1. Merge DataFrames
        # Ensure 'Keyword' is a column for merging (pivot_df might have it as index)
        pivot_df_reset = pivot_df.reset_index() if 'Keyword' not in pivot_df.columns else pivot_df
        # agg_df should already have 'Keyword' as a column from its creation

        # Check if 'Keyword' column exists in both before merge
        if 'Keyword' not in pivot_df_reset.columns or 'Keyword' not in agg_df.columns:
             raise ValueError("Missing 'Keyword' column in one of the DataFrames for merging.")

        pivotmerge_df = pd.merge(pivot_df_reset, agg_df, on='Keyword', how='left')
        print("  ✅ Pivot and Aggregate Data Joined.")
        rows_pre_filter, cols_pre_filter = pivotmerge_df.shape
        print(f"     Rows: {rows_pre_filter:,}, Columns: {cols_pre_filter:,}")

        # --- Filtering ---
        print("\n🧹 Applying Brand and Negative Filters...")
        kw_filter = [] # Initialize filter list
        if filter_file.exists():
            try:
                df_filter = pd.read_csv(filter_file, header=0)
                # Ensure Filter column exists and handle potential NaNs robustly
                if 'Filter' in df_filter.columns:
                     kw_filter = df_filter["Filter"].dropna().astype(str).tolist()
                else:
                     print(f"  ⚠️ Filter file '{filter_file}' exists but missing 'Filter' column.")

            except Exception as e:
                print(f"  ⚠️ Error reading filter file '{filter_file}': {e}")

        if kw_filter:
            pattern = '|'.join([re.escape(keyword) for keyword in kw_filter])
            # Ensure 'Keyword' column exists before filtering
            if 'Keyword' in pivotmerge_df.columns:
                 filtered_df = pivotmerge_df[~pivotmerge_df["Keyword"].astype(str).str.contains(pattern, case=False, na=False)]
                 print(f"  ✅ Filter applied using {len(kw_filter)} terms from '{filter_file}'.")
            else:
                 print("  ⚠️ 'Keyword' column missing. Cannot apply filter.")
                 filtered_df = pivotmerge_df # Skip filtering
        else:
            filtered_df = pivotmerge_df
            if filter_file.exists():
                 print("  ⚠️ Filter file exists but contains no terms. Skipping filter application.")
            else:
                 print(f"  ☑️ No filter file found at '{filter_file}'. Skipping negative filtering.")

        rows_post_filter, _ = filtered_df.shape
        removed_count = rows_pre_filter - rows_post_filter
        print(f"     Rows after filter: {rows_post_filter:,} ({removed_count:,} rows removed)")


        # --- Reordering and Final Polish ---
        print("\n✨ Reordering columns and finalizing...")
        temp_df = filtered_df.copy()

        # Chain reorders using the robust helper
        temp_df = _reorder_columns_surgical(temp_df, "Search Volume", after_column="Keyword")
        temp_df = _reorder_columns_surgical(temp_df, "Number of Words", after_column="CPC")
        temp_df = _reorder_columns_surgical(temp_df, "CPC", after_column="Number of Words")
        temp_df = _reorder_columns_surgical(temp_df, "Number of Results", after_column="Position Type")
        temp_df = _reorder_columns_surgical(temp_df, "Timestamp", after_column="Number of Results")
        temp_df = _reorder_columns_surgical(temp_df, "Competitor URL", after_column="Client URL")

        # Final column arrangement (Keyword, Search Volume first)
        essential_cols = ['Keyword', 'Search Volume']
        if all(col in temp_df.columns for col in essential_cols):
            rest_of_columns = [col for col in temp_df.columns if col not in essential_cols]
            new_column_order = essential_cols + rest_of_columns
            # Defensively check if all original columns are still in the new order
            if set(new_column_order) == set(temp_df.columns):
                 arranged_df = temp_df[new_column_order]
                 print("  ✅ Final column order applied.")
            else:
                 print("  ⚠️ Column mismatch during final arrange. Using previous order.")
                 arranged_df = temp_df # Fallback
        else:
            print("  ⚠️ Essential columns ('Keyword', 'Search Volume') missing. Skipping final arrange.")
            arranged_df = temp_df # Fallback

        # Final sorting and column drops
        sort_col = "Search Volume" if "Search Volume" in arranged_df.columns else "Keyword"
        arranged_df = arranged_df.sort_values(by=sort_col, ascending=False)

        cols_to_drop = ["Previous position", "Traffic", "Traffic (%)", "Traffic Cost"]
        existing_cols_to_drop = [col for col in cols_to_drop if col in arranged_df.columns]
        if existing_cols_to_drop:
            arranged_df.drop(columns=existing_cols_to_drop, inplace=True)
            print(f"  ✂️ Dropped columns: {', '.join(existing_cols_to_drop)}")

        print("\n✅ Final Keyword Table Prepared.")
        final_rows, final_cols = arranged_df.shape
        print(f"   Final Rows: {final_rows:,}, Final Columns: {final_cols:,}")


        # --- OUTPUT (to pip state) ---
        pip.set(job, 'filtered_gap_analysis_df_json', arranged_df.to_json(orient='records'))
        print(f"💾 Stored final arranged DataFrame in pip state for job '{job}'.")
        # ---------------------------

        # --- RETURN VALUE ---
        return arranged_df # Return the DataFrame for display

    except Exception as e:
        print(f"❌ An error occurred during merge/filter/arrange: {e}")
        pip.set(job, 'filtered_gap_analysis_df_json', pd.DataFrame().to_json(orient='records'))
        return pd.DataFrame() # Return empty DataFrame

# %% editable=true slideshow={"slide_type": ""}
from IPython.display import display
import gap_analyzer_sauce # Ensure module is imported

# This function now handles:
# 1. Merging pivot_df and agg_df.
# 2. Reading the filter keyword list from the CSV file.
# 3. Applying the brand/negative keyword filter.
# 4. Reordering columns for readability.
# 5. Dropping unnecessary columns (Traffic metrics, Previous position).
# 6. Sorting the final DataFrame by Search Volume.
# 7. Storing the final arranged_df in pip state.
# It receives pivot_df and agg_df directly from previous cell variables.
arranged_df = gap_analyzer_sauce.merge_filter_arrange_data(job, pivot_df, agg_df)

# Display the final, arranged DataFrame
display(arranged_df)

# Optional verification
# print("\nVerification:")
# print(f"  Final Arranged DF stored: {'filtered_gap_analysis_df_json' in pip.read_state(job)}")
# loaded_arranged_df = pd.read_json(StringIO(pip.get(job, 'filtered_gap_analysis_df_json', '[]'))) # Use StringIO
# print(f"  Final Arranged DF rows in state: {len(loaded_arranged_df)}")