import pandas as pd

# Load your budget sheet
df = pd.read_csv("public_set_budget_approval_export_2025-07-03_170556.csv")

# Melt the recurring and non-recurring columns
rec_cols = ['rec_year1', 'rec_year2', 'rec_year3', 'rec_year4', 'rec_year5']
non_rec_cols = ['non_rec_year1', 'non_rec_year2', 'non_rec_year3', 'non_rec_year4', 'non_rec_year5']

# Melt recurring
rec_melted = df.melt(
    id_vars=['hub_id', 'created_date', 'updated_date', 'created_by', 'updated_by'],
    value_vars=rec_cols,
    var_name='year_id',
    value_name='recurring_approved'
)

# Melt non-recurring
non_rec_melted = df.melt(
    id_vars=['hub_id', 'created_date', 'updated_date', 'created_by', 'updated_by'],
    value_vars=non_rec_cols,
    var_name='year_id',
    value_name='non_recurring_approved'
)

# Clean year_id to extract just the year number
rec_melted['year_id'] = rec_melted['year_id'].str.extract(r'(\d)').astype(int)
non_rec_melted['year_id'] = non_rec_melted['year_id'].str.extract(r'(\d)').astype(int)

# Merge recurring and non-recurring on all other columns + year_id
final_df = pd.merge(
    rec_melted,
    non_rec_melted,
    on=['hub_id', 'created_date', 'updated_date', 'created_by', 'updated_by', 'year_id']
)

# Reorder columns
final_df = final_df[['year_id', 'recurring_approved', 'non_recurring_approved',
                     'hub_id', 'created_date', 'updated_date', 'created_by', 'updated_by']]

# Save to new CSV
final_df.to_csv("reshaped_budget_approval.csv", index=False)

print("✅ Done! Saved as reshaped_budget_approval.csv")