i have this code but in the switches for the same ein over the years like in 2015 if the switch is from si to fi then in 2020 again there is the switch from fi to si so i want to get change the code in such a way that i get a column where i can show the switch no. year and whether it is fi to si or si to fi
how do i modify the code

import pandas as pd
import numpy as np
pd.set_option('display.max_columns',None)
pd.set_option('display.max_rows',None)

pit_data = pd.read_pickle("H:/GAIN_McKinsey_PIT__0706.pkl")
pit_data_2015 = pit_data[pit_data['Year']>=2015]
pit_data_2015 = pit_data_2015[pit_data_2015.Product.isin(["Dental","STD","Vision"])]

pit_data_work = pit_data_2015[['EIN', 'EIN_FTE', 'Funding',
                               'Members','Product','Year']]

pit_2015 = pit_data_work[pit_data_work['Year']==2015]
pit_2015 = pit_2015[['EIN', 'EIN_FTE']]
pit_2015 = pit_2015.drop_duplicates(subset=['EIN'])

def create_size_band(df):

   # Function to assign size band based on EIN_FTE

   def get_size_band(fte):

       if pd.isna(fte):

           return 'Unknown'

       elif fte >= 5000:

           return '5000+'

       elif fte >= 1000:

           return '1000-4999' 

       elif fte >= 500:

           return '500-999'

       elif fte >= 100:

           return '100-499'

       else:

           return '<100'

   # Create size band column

   df['Size_Band'] = df['EIN_FTE'].apply(get_size_band)

   # Check if EIN_FTE is consistent within groups

   fte_check = df.groupby('EIN')['EIN_FTE'].agg(['nunique', 'unique'])

   inconsistent_groups = fte_check[fte_check['nunique'] > 1]

   if len(inconsistent_groups) > 0:

       print("\nGroups with inconsistent EIN_FTE values:")

       for ein, row in inconsistent_groups.iterrows():

           print(f"EIN: {ein}")

           print(f"Different FTE values: {row['unique']}")

           print()

   else:

       print("\nAll groups have consistent EIN_FTE values")

   # Show distribution of size bands

   size_band_dist = df['Size_Band'].value_counts().sort_index()

   print("\nSize Band Distribution:")

   print(size_band_dist)

   return df

size_band_df = create_size_band(pit_2015)

use_pit = pit_data_work[['EIN', 'Funding',
                         'Members','Product','Year']]

group_by = use_pit.groupby(['Year', 'Product', 'EIN']).agg(
    SI = ("Members", lambda x: x[use_pit.loc[x.index, "Funding"]=="SI"].sum()),
    FI = ("Members", lambda x: x[use_pit.loc[x.index, "Funding"]=="FI"].sum()),
 ).reset_index()

required_years = range(2015,2023)

# 1. First filter out cases where both SI/FI are zero
ein_nonzero = group_by.groupby(["EIN", "Product"]).filter(
    lambda x: not any((x["SI"] == 0) & (x["FI"] == 0))
)

# 2. Then check for year continuity
ein_valid_df = ein_nonzero.groupby(["EIN", "Product"]).filter(
    lambda x: set(x["Year"].unique()) == set(required_years)
)

# Step 2: Identify switching cases and capture switch years with member counts
switch_cases = []

def detect_switches(group):
    group = group.sort_values("Year")
    for i in range(1, len(group)):
        prev_row = group.iloc[i-1]
        curr_row = group.iloc[i]
        
        prev_fi, curr_fi = prev_row["FI"], curr_row["FI"]
        prev_si, curr_si = prev_row["SI"], curr_row["SI"]
        
        # Detect FI to SI switch - FI must drop to exactly zero
        if prev_fi > 0 and curr_fi == 0:
            switch_cases.append({
                "EIN": curr_row["EIN"],
                "Product": curr_row["Product"],
                "Year": curr_row["Year"],
                "Switch": "FI to SI",
                "Prev_Year": prev_row["Year"],
                "Prev_FI": prev_fi,
                "Prev_SI": prev_si,
                "Curr_FI": curr_fi,
                "Curr_SI": curr_si,
                "Switched_Members": prev_fi  # All FI members from previous year
            })
            
        # Detect SI to FI switch - SI must drop to exactly zero
        elif prev_si > 0 and curr_si == 0:
            switch_cases.append({
                "EIN": curr_row["EIN"],
                "Product": curr_row["Product"],
                "Year": curr_row["Year"],
                "Switch": "SI to FI",
                "Prev_Year": prev_row["Year"],
                "Prev_FI": prev_fi,
                "Prev_SI": prev_si,
                "Curr_FI": curr_fi,
                "Curr_SI": curr_si,
                "Switched_Members": prev_si  # All SI members from previous year
            })

# Apply switch detection
ein_valid_df.groupby(["EIN", "Product"]).apply(detect_switches)

# Convert to DataFrame and fill NaN with 0
switch_df = pd.DataFrame(switch_cases).fillna(0)

ein_valid_df['Total'] = ein_valid_df['SI'] + ein_valid_df['FI']
ein_valid_df['EIN_FI'] = (ein_valid_df['FI']>0).astype(int)
ein_valid_df['EIN_SI'] = (ein_valid_df['SI']>0).astype(int)
ein_valid_df['Total_EIN'] = 1

switch_df['SI_EIN_Switch'] = (switch_df['Switch']=='SI to FI').astype(int)
switch_df['FI_EIN_Switch'] = (switch_df['Switch']=='FI to SI').astype(int)

switch_df = switch_df.rename(columns={
  "Prev_SI" : "SI_Switch_Members",
  "Prev_FI" : "FI_Switch_Members"
})

switch_df["Unique"] = switch_df['Year'].astype(str)+"_"+switch_df['Product'].astype(str)+"_"+switch_df['EIN'].astype(str)

map_df = switch_df[['Unique','FI_Switch_Members','SI_Switch_Members',
                    'SI_EIN_Switch', 'FI_EIN_Switch']]

ein_valid_df["Unique"] = ein_valid_df['Year'].astype(str)+"_"+ein_valid_df['Product'].astype(str)+"_"+ein_valid_df['EIN'].astype(str)

merged_df = ein_valid_df.merge(map_df, on="Unique", how="left").fillna(0)
merged_df = merged_df.merge(size_band_df, on="EIN", how="left").fillna(0)

final_df = merged_df.groupby(['Year', 'Product', 'Size_Band']).agg({
  'SI' : 'sum',
  'FI' : 'sum',
  'Total' : 'sum',
  'EIN_FI' : 'sum',
  'EIN_SI' : 'sum',
  'Total_EIN' : 'sum',
  'FI_EIN_Switch' : 'sum',
  'SI_EIN_Switch' : 'sum',
  'FI_Switch_Members' : 'sum',
  'SI_Switch_Members' : 'sum',
}).reset_index()

final_df = final_df.rename(columns={
  'SI' : 'SI_Members',
  'FI' : 'FI_Members',
  'Total' : 'Total_Members'
})

final_df.to_excel("H:/Final_SI_FI_Switch_Data.xlsx")

also i want to understand on what columns to do group by and merge in detail because i'm always ending up doing groupby and merge on different columns and getting wrong answer please explain this in detail to me