atulyamann/flexup-api
0
1 2import pandas as pd3import numpy as np4from datetime import datetime5 6# ============================================================7# DATE RANGE CONFIGURATION 8# ============================================================9start_date = pd.to_datetime('05/31/2026', format='%m/%d/%Y')10end_date = pd.to_datetime('08/16/2026', format='%m/%d/%Y')11 12# Generate date range13date_range = pd.date_range(start=start_date, end=end_date, freq='D')14dates = [f"{dt.month}/{dt.day}" for dt in date_range]15 16print("="*70)17print("FULLY CORRECTED SCRIPT - All metrics fixed")18print("="*70)19print(f"Start Date: {start_date.strftime('%m/%d/%Y')}")20print(f"End Date: {end_date.strftime('%m/%d/%Y')}")21print(f"Total Days: {len(dates)}")22print(f"Generated Dates: {dates}")23print("="*70 + "")24def apply_date_conversion(df, sheet_name):25 """26 Convert date-like column headers (strings, Excel serial numbers, etc.)27 to pd.Timestamp objects. Returns (modified_df, list_of_converted_cols).28 Non-date columns (e.g. 'site', 'FC', 'SU%') are left untouched.29 """30 converted = []31 new_cols = {}32 for col in df.columns:33 if isinstance(col, (pd.Timestamp, datetime)):34 converted.append(col)35 continue36 # Try parsing string dates37 if isinstance(col, str):38 for fmt in ('%m/%d/%Y', '%Y-%m-%d', '%m/%d/%y', '%m-%d-%Y', '%B %d, %Y'):39 try:40 ts = pd.to_datetime(col, format=fmt)41 new_cols[col] = ts42 converted.append(ts)43 break44 except (ValueError, TypeError):45 continue46 # Excel serial-number dates (float/int)47 elif isinstance(col, (int, float)) and 40000 < col < 60000:48 try:49 ts = pd.Timestamp('1899-12-30') + pd.Timedelta(days=int(col))50 new_cols[col] = ts51 converted.append(ts)52 except Exception:53 pass54 if new_cols:55 df = df.rename(columns=new_cols)56 print(f" ✓ {sheet_name}: converted {len(converted)} date columns")57 return df, converted58 59# Define all 292 sites based on the SQL query60all_sites = [61 # AR Sortable sites62 'ABQ1','ACY1','AGS1','AGS2','AKC1','ATL2','AUS2','AUS3','BDL2','BDL3','BDL4',63 'BFI4','BFL1','BHM1','BOI2','BOS3','BTR1','BWI2','CLE2','CLE3','CLT4','CMH1','CMH4','DAB2','DAL3',64 'DCA1','DEN3','DEN4','DET3','DET6','DFW7','DSM5','DTW1','ELP1','EWR4','EWR9','FAT1','FSD1','FTW6',65 'FWA6','GEG1','GRR1','GYR1','HOU2','HOU6','IGQ1','JAN1','JAX2','JFK8','LAS7','LGA9','LGB3','LGB7',66 'LIT1','LUK2','MCO1','MDW7','MEM4','MIA1','MKC6','MKE1','MKE2','MLI1','MQY1','MSP1','MTN1','OAK4',67 'OKC1','OMA2','ORD5','ORF3','ORF4','ORH3','OXR1','PAE2','PCW1','PDX8','PDX9','PSP1','PVD2','RDU1',68 'RIC4','ROC1','SAN3','SAT2','SAT3','SAV4','SBD6','SBN1','SCK6','SHV1',69 'SLC1','SMF1','STL8','SYR1','TLH2','TPA1','TPA4','TUL2','TUS2','TYS1','VGT1',70 # NonSortable sites71 'ABE4','ACY2','AKR1','ALB1','AMA1','BFI3','BNA2','BOS7','BWI4','CHA2',72 'CHO1','CLT3','CMH2','CMH3','DCA6','DEN8','DET1','DET2','DFW6','FAT2','FOE1','FTW5',73 'GEG2','GSO1','HOU8','HSV1','ICT2','IGQ2','ILG1','IND5','JAX3','JVL1','LAS6','LFT1',74 'LGB4','LGB6','LIT2','MCE1','MCO2','MDT1','MDT4','MDW6','MEM6','MGE3','MKC4','OAK3',75 'OKC2','ORD2','PDX7','PHL4','PHL5','PHL6','PHX5','PHX7','PIT2','RIC1','RNO4','SAT1',76 'SAT4','SAV3','SBD2','SCK1','SJC7','SLC2','SMF6','SNA4','STL3','STL4','SWF1','TEB3',77 'TEB4','TEB6','TPA2','TPA3',78 # Canada sites79 'YEG1','YOO1','YOW1','YVR3','YYZ2','YYZ3','YYZ9','YYZ1','YHM1','YOW3','YUL2','YXU1','YYZ4','YYZ7','YEG2','YVR2','YVR4','YXX2','YYC1','YYC4',80 # SDC sites81 'ATL7','AVP8','HGR5','KRB1','KRB2','KRB3','KRB4','KRB6','QXX6','SAV7',82 # TSSL sites83 'ABE2','CAE1','IND1','PHL7','RIC2','AFW1','BFL2','BNA3','CSG1','DAL2',84 'JAX7','MDW4','ONT2','ONT6','PHX3','RDG1','SDF2','SDF8','ABE3','FTW9','IND4','MGE1',85 # IXD sites (nIXD and rIXD)86 'ABQ2','ABS4','GEU2','GEU3','GEU5','HEA2','HGR6','HIA1','HLI2','LBE1',87 'MIT2','PBI3','POC1','POC2','POC3','PPO4','PSC2','QXY8','RYY2','TCY1','TCY2','TMB8',88 'WBW2','ABE8','AVP1','BNA6','CLT2','FTW1','FWA4','GYR2','GYR3','IAH3','IND9','LAN2',89 'LAS1','LAX9','LGB8','MCC1','MDW2','MEM1','MQJ1','ONT8','ORF2','PSP3','RDU2','RDU4',90 'RFD2','RMN3','SBD1','SCK4','SMF3','SWF2','TEB9','VGT2',91 # DG sites92 'BFI9','BWI1','CMH7','MEM2','MLB1',93 # IXD-NonSort sites94 'OLM1','PCA2','QXY4','SBD3','SCK8'95]96 97# Define the metrics98metrics = [99 'FC Shipments Planned Cap',100 'Daily MMV',101 'Daily Delta',102 'Max of % to DCM or RSP',103 'SETWEEKP',104 'MaxWeekCap',105 'METQS hrs',106 'Flex Up from SET up to 55',107 'LaborShareMax',108 'LaborSharePlan',109 'Rate',110 'Flex Up from LS gap from max',111 'RR',112 'Hires',113 'Flex Up from Hires',114 'VTO Hours',115 'Flex Up from VTO Removal',116 'VET hours',117 'Flex up from VET addition',118 'Show hours',119 'VET Ideal hours',120 'VET Flex up'121]122 123# Network and Region mapping function124def get_network_region(site):125 """Map site to Network and Region based on the SQL logic"""126 if site in ['ABQ1','ACY1','AGS1','AGS2','AKC1','ATL2','AUS2','AUS3','BDL2','BDL3','BDL4',127 'BFI4','BFL1','BHM1','BOI2','BOS3','BTR1','BWI2','CLE2','CLE3','CLT4','CMH1','CMH4','DAB2','DAL3',128 'DCA1','DEN3','DEN4','DET3','DET6','DFW7','DSM5','DTW1','ELP1','EWR4','EWR9','FAT1','FSD1','FTW6',129 'FWA6','GEG1','GRR1','GYR1','HOU2','HOU6','IGQ1','JAN1','JAX2','JFK8','LAS7','LGA9','LGB3','LGB7',130 'LIT1','LUK2','MCO1','MDW7','MEM4','MIA1','MKC6','MKE1','MKE2','MLI1','MQY1','MSP1','MTN1','OAK4',131 'OKC1','OMA2','ORD5','ORF3','ORF4','ORH3','OXR1','PAE2','PCW1','PDX8','PDX9','PSP1','PVD2','RDU1',132 'RIC4','ROC1','SAN3','SAT2','SAT3','SAV4','SBD6','SBN1','SCK6','SHV1',133 'SLC1','SMF1','STL8','SYR1','TLH2','TPA1','TPA4','TUL2','TUS2','TYS1','VGT1']:134 network = 'AR Sortable'135 if site in ['BFI9','BFI3','DEN8','GEG2','PDX7','SLC2','BFI1','BFI4','BOI2','DEN3','DEN4','GEG1','PAE2','PDX8','PDX9','SLC1','BFI7','DEN7','PDX6','SLC3']:136 region = 'NorthWest'137 elif site in ['FAT2','LAS6','LGB4','LGB6','MCE1','OAK3','PHX5','PHX7','RNO4','SBD2','SCK1','SJC7','SMF6','SNA4','KRB1','KRB3','KRB4','BFL1','FAT1','GYR1','LAS7','LGB3','LGB7','OAK4','OXR1','PSP1','SAN3','SBD6','SCK6','SMF1','TUS2','VGT1','BFL2','ONT2','ONT6','PHX3']:138 region = 'SouthWest'139 elif site in ['CMH7','AKR1','CMH2','CMH3','DET1','DET2','IND5','PIT2','CLE2','CLE3','CMH1','CMH4','DET3','DET6','DTW1','FWA6','GRR1','LUK2','PCW1','IND1','SDF1','SDF2','SDF8','IND4']:140 region = 'MidWest'141 elif site in ['FOE1','IGQ2','JVL1','MDW6','MKC4','ORD2','STL3','STL4','DSM5','FSD1','IGQ1','MDW7','MKC6','MKE1','MKE2','MLI1','MSP1','OMA2','ORD5','SBN1','STL8','MDW4']:142 region = 'GreatLakes'143 elif site in ['MEM2','BNA2','CHA2','HSV1','LFT1','LIT2','MEM6','MGE3','AGS2','ATL2','BHM1','BTR1','JAN1','LIT1','MEM4','MQY1','SHV1','BNA3','CSG1','MGE1']:144 region = 'MidSouth'145 elif site in ['AMA1','DFW6','FTW5','HOU8','ICT2','OKC2','SAT1','SAT4','ABQ1','AUS2','AUS3','DAL3','DFW7','ELP1','FTW6','HOU2','HOU6','OKC1','SAT2','SAT3','TUL2','AFW1','DAL2','FTW9']:146 region = 'Texas'147 elif site in ['ABE4','ACY2','ALB1','BOS7','PHL4','PHL5','SWF1','TEB3','TEB4','TEB6','AVP8','HGR5','QXX6','BDL2','BDL3','BDL4','BOS3','EWR4','EWR9','JFK8','LGA9','ORH3','PVD2','ROC1','SYR1','ABE2','RDG1','ABE3']:148 region = 'NorthEast'149 elif site in ['BWI1','BWI4','CHO1','CLT3','DCA6','GSO1','ILG1','MDT1','MDT4','PHL6','RIC1','KRB2','ACY1','AKC1','BWI2','CLT4','DCA1','MTN1','ORF3','ORF4','RDU1','RIC4','TYS1','CAE1','PHL7','RIC2']:150 region = 'MidAtlantic'151 elif site in ['MLB1','JAX3','MCO2','SAV3','TPA2','TPA3','ATL7','SAV7','AGS1','DAB2','JAX2','MCO1','MIA1','SAV4','TLH2','TPA1','TPA4','JAX7']:152 region = 'Florida'153 else:154 region = 'UnMapped'155 elif site in ['ABE4','ACY2','AKR1','ALB1','AMA1','BFI3','BNA2','BOS7','BWI4','CHA2','CHO1','CLT3','CMH2','CMH3','DCA6','DEN8','DET1','DET2','DFW6','FAT2','FOE1','FTW5','GEG2','GSO1','HOU8','HSV1','ICT2','IGQ2','ILG1','IND5','JAX3','JVL1','LAS6','LFT1','LGB4','LGB6','LIT2','MCE1','MCO2','MDT1','MDT4','MDW6','MEM6','MGE3','MKC4','OAK3','OKC2','ORD2','PDX7','PHL4','PHL5','PHL6','PHX5','PHX7','PIT2','RIC1','RNO4','SAT1','SAT4','SAV3','SBD2','SCK1','SJC7','SLC2','SMF6','SNA4','STL3','STL4','SWF1','TEB3','TEB4','TEB6','TPA2','TPA3']:156 network = 'NonSortable'157 region = 'NonSortable'158 elif site in ['YEG1','YOO1','YOW1','YVR3','YYZ2','YYZ3','YYZ9','YYZ1','YHM1','YOW3','YUL2','YXU1','YYZ4','YYZ7','YEG2','YVR2','YVR4','YXX2','YYC1','YYC4']:159 network = 'CANADA'160 if site in ['YEG2','YVR2','YVR4','YXX2','YYC1','YYC4']:161 region = 'Sortable-West'162 elif site in ['YHM1','YOW3','YUL2','YXU1','YYZ4','YYZ7']:163 region = 'Sortable-East'164 elif site == 'YYZ1':165 region = 'CA-Returns'166 else:167 region = 'NonSortable'168 elif site in ['ATL7','AVP8','HGR5','KRB1','KRB2','KRB3','KRB4','KRB6','QXX6','SAV7']:169 network = 'SDC'170 region = 'SDC'171 elif site in ['ABE2','CAE1','IND1','PHL7','RIC2','AFW1','BFL2','BNA3','CSG1','DAL2','JAX7','MDW4','ONT2','ONT6','PHX3','RDG1','SDF2','SDF8','ABE3','FTW9','IND4','MGE1']:172 network = 'TSSL'173 region = 'TSSL'174 elif site in ['ABQ2','ABS4','GEU2','GEU3','GEU5','HEA2','HGR6','HIA1','HLI2','LBE1','MIT2','PBI3','POC1','POC2','POC3','PPO4','PSC2','QXY8','RYY2','TCY1','TCY2','TMB8','WBW2','ABE8','AVP1','BNA6','CLT2','FTW1','FWA4','GYR2','GYR3','IAH3','IND9','LAN2','LAS1','LAX9','LGB8','MCC1','MDW2','MEM1','MQJ1','ONT8','ORF2','PSP3','RDU2','RDU4','RFD2','RMN3','SBD1','SCK4','SMF3','SWF2','TEB9','VGT2']:175 network = 'IXD'176 if site in ['ABQ2','ABS4','GEU2','GEU3','GEU5','HEA2','HGR6','HIA1','HLI2','LBE1','MIT2','PBI3','POC1','POC2','POC3','PPO4','PSC2','QXY8','RYY2','TCY1','TCY2','TMB8','WBW2']:177 region = 'NIXD'178 else:179 region = 'RIXD'180 elif site in ['BFI9','BWI1','CMH7','MEM2','MLB1']:181 network = 'DG'182 region = 'DG'183 elif site in ['OLM1','PCA2','QXY4','SBD3','SCK8']:184 network = 'IXD-NonSort'185 region = 'IXD-NonSort'186 else:187 network = 'UnMapped'188 region = 'UnMapped'189 190 return network, region191 192# Load all source sheets193print("Loading source data sheets from test3.xlsx...")194 195try:196 source_file = r"C:\Users\atulyam\Desktop\Flexup\test7tf.xlsx"197 198 pc_df = pd.read_excel(source_file, sheet_name='PC')199 mmv_df = pd.read_excel(source_file, sheet_name='MMV')200 ls_df = pd.read_excel(source_file, sheet_name='LS')201 max_ls_df = pd.read_excel(source_file, sheet_name='MAX LS')202 su_df = pd.read_excel(source_file, sheet_name='SU')203 vto_df = pd.read_excel(source_file, sheet_name='VTO')204 vet_df = pd.read_excel(source_file, sheet_name='VET')205 show_df = pd.read_excel(source_file, sheet_name='Show')206 met_hrs_df = pd.read_excel(source_file, sheet_name='MET hrs') # ← ADDED THIS LINE207 metqs_df = pd.read_excel(source_file, sheet_name='METQS hrs')208 rate_df = pd.read_excel(source_file, sheet_name='Rate') # ← NEW: load Rate tab209 hires_df = pd.read_excel(source_file, sheet_name='Hires') # ← FIX: was missing210 rr_raw_df = pd.read_excel(source_file, sheet_name='RR')211 print("✓ All source sheets loaded successfully")212 for drop_col in ['Concept', 'Shift', 'Flow']:213 if drop_col in metqs_df.columns:214 metqs_df = metqs_df.drop(columns=[drop_col])215 for drop_col in ['Concept', 'Aspect', 'Flow', 'Shift']:216 if drop_col in met_hrs_df.columns:217 met_hrs_df = met_hrs_df.drop(columns=[drop_col])218 for drop_col in ['Concept', 'Aspect', 'Flow', 'Shift', 'concept', 'aspect', 'flow', 'shift']:219 if drop_col in met_hrs_df.columns:220 met_hrs_df = met_hrs_df.drop(columns=[drop_col])221 # ── NORMALIZE DATE COLUMNS ON EVERY SHEET ────────────────────────────────222 # apply_date_conversion() converts string/serial date headers → pd.Timestamp223 # so that date_mapping lookups work correctly across all sheets224 pc_df, _ = apply_date_conversion(pc_df, 'PC')225 ls_df, _ = apply_date_conversion(ls_df, 'LS')226 vto_df, _ = apply_date_conversion(vto_df, 'VTO')227 vet_df, _ = apply_date_conversion(vet_df, 'VET')228 show_df, _ = apply_date_conversion(show_df, 'Show')229 met_hrs_df, _ = apply_date_conversion(met_hrs_df, 'MET hrs')230 metqs_df, _ = apply_date_conversion(metqs_df, 'METQS hrs')231 rate_df, _ = apply_date_conversion(rate_df, 'Rate')232 hires_df, _ = apply_date_conversion(hires_df, 'Hires')233 # Add this after loading su_df234 su_df, _ = apply_date_conversion(su_df, 'SU') # Convert date columns to Timestamps235 su_lookup = su_df.set_index('site') # Create lookup indexed by site236 # Note: mmv_df, max_ls_df, su_df don't use date columns as headers — skip them237 238 print("✓ Date columns normalized on all sheets")239 # ── PROCESS RR DATA: DIVIDE RUN RATE BY 7 AND SPREAD ACROSS WEEK ─────────240 # Convert 'Run Week' to datetime (week start date)241 rr_raw_df['Run Week'] = pd.to_datetime(rr_raw_df['Run Week'], format='%m/%d/%Y')242 243 # Create a row for each day of the week (7 days) with daily_rr = Run Rate / 7244 rr_expanded = []245 for _, row in rr_raw_df.iterrows():246 week_start = row['Run Week']247 daily_rr = row['Run Rate'] / 7248 site = row['Site']249 250 # Generate 7 days starting from week_start251 for day_offset in range(7):252 day_date = week_start + pd.Timedelta(days=day_offset)253 rr_expanded.append({254 'site': site,255 'date': day_date,256 'daily_rr': daily_rr257 })258 259 rr_daily_df = pd.DataFrame(rr_expanded)260 261 # Pivot: rows = site, columns = date, values = daily_rr262 rr_pivot = rr_daily_df.pivot_table(263 index='site',264 columns='date',265 values='daily_rr',266 aggfunc='sum' # sum in case multiple rows exist for same site+date267 ).fillna(0)268 269 print(f"✓ RR data processed: {len(rr_pivot)} sites × {len(rr_pivot.columns)} dates")270 271 # ── BUILD DATE MAPPING FROM PC SHEET ─────────────────────────────────────272 # After conversion, PC date columns are pd.Timestamp objects273 date_cols_pc = [col for col in pc_df.columns if isinstance(col, (pd.Timestamp, datetime))]274 print(f"✓ Found {len(date_cols_pc)} datetime columns in PC sheet")275 276 277 278 279 # Create date mapping280 date_mapping = {}281 for dt_col in date_cols_pc:282 date_str = f"{dt_col.month}/{dt_col.day}"283 date_mapping[date_str] = dt_col284 285 # Validate dates286 missing_dates = [d for d in dates if d not in date_mapping]287 if missing_dates:288 print(f"⚠ WARNING: Missing dates: {missing_dates}")289 dates = [d for d in dates if d in date_mapping]290 291 print(f"✓ Processing {len(dates)} dates")292 293 # Set up lookup dataframes294 pc_lookup = pc_df.set_index('site')295 ls_lookup = ls_df.set_index('site')296 vto_lookup = vto_df.set_index('site')297 vet_lookup = vet_df.set_index('site')298 show_lookup = show_df.set_index('site')299 met_hrs_lookup = met_hrs_df.set_index('site') # ← ADDED THIS LINE300 metqs_lookup = metqs_df.set_index('site')301 rate_lookup = rate_df.set_index('site') # ← NEW: Rate tab lookup302 hires_lookup = hires_df.set_index('site')303 rr_lookup = rr_pivot # ← NEW: RR lookup (already indexed by site)304 305 306 print("✓ Lookup dataframes created")307 print("=== METQS DEBUG ===")308 print(f"metqs_lookup index (sites): {metqs_lookup.index[:5].tolist()}")309 print(f"metqs_lookup columns: {metqs_lookup.columns.tolist()}")310 print(f"PC lookup columns (sample): {[c for c in pc_lookup.columns if isinstance(c, pd.Timestamp)][:5]}")311 print(f"Test site in metqs: {'AUS3' in metqs_lookup.index}")312 if 'AUS3' in metqs_lookup.index:313 print(f"metqs row for AUS3: {metqs_lookup.loc['AUS3'].head(10)}")314except Exception as e:315 print(f"Error loading source file: {e}")316 raise317def create_site_metrics(site_code):318 """Create a DataFrame with calculated metrics for a given site - FULLY CORRECTED VERSION"""319 network, region = get_network_region(site_code)320 321 # Pre-process MMV: build a dict of {week_start_date: weekly_mmv / 7}322 mmv_site_data = mmv_df[mmv_df['FC'] == site_code]323 mmv_weekly = {}324 if len(mmv_site_data) > 0:325 for col in mmv_site_data.columns:326 if col == 'FC':327 continue328 try:329 col_date = pd.to_datetime(col)330 val = mmv_site_data.iloc[0][col]331 if pd.notna(val) and val != '':332 mmv_weekly[col_date] = float(val) / 7333 except:334 continue335 def get_daily_mmv(dt_col):336 """Find which week this date belongs to and return that week's MMV / 7"""337 dt_date = pd.to_datetime(dt_col)338 for week_start, daily_val in mmv_weekly.items():339 week_end = week_start + pd.Timedelta(days=6)340 if week_start <= dt_date <= week_end:341 return daily_val342 return 0343 site_data = []344 345 for metric in metrics:346 row = {347 'Network': network,348 'Region': region,349 'Site': site_code,350 'Metric': metric351 }352 353 for date_str in dates:354 try:355 dt_col = date_mapping.get(date_str)356 357 if dt_col is None:358 value = ''359 elif metric == 'FC Shipments Planned Cap':360 value = pc_lookup.loc[site_code, dt_col] if site_code in pc_lookup.index else ''361 elif metric == 'Daily MMV':362 value = get_daily_mmv(dt_col) if get_daily_mmv(dt_col) > 0 else ''363 elif metric == 'Daily Delta':364 if site_code in pc_lookup.index and get_daily_mmv(dt_col) > 0:365 value = get_daily_mmv(dt_col) - pc_lookup.loc[site_code, dt_col]366 else:367 value = ''368 elif metric == 'Max of % to DCM or RSP':369 if site_code in pc_lookup.index and get_daily_mmv(dt_col) > 0:370 fc_val = pc_lookup.loc[site_code, dt_col]371 dcm_ratio = fc_val / get_daily_mmv(dt_col) if get_daily_mmv(dt_col) != 0 else 0372 373 # Get RSP from SU sheet (station percentage per day)374 # SU sheet now has date columns with station_percentage values375 try:376 if site_code in su_lookup.index and dt_col in su_lookup.columns:377 rsp_val = su_lookup.loc[site_code, dt_col]378 # Convert from percentage (e.g., 36.11) to ratio (0.3611)379 if not pd.isna(rsp_val) and rsp_val != '':380 rsp_ratio = rsp_val / 100381 else:382 rsp_ratio = 0383 else:384 rsp_ratio = 0385 except:386 rsp_ratio = 0387 388 value = max(dcm_ratio, rsp_ratio)389 else:390 value = ''391 elif metric == 'SETWEEKP':392 # ← MODIFIED THIS SECTION TO USE MET HRS LOOKUP393 value = met_hrs_lookup.loc[site_code, dt_col] if site_code in met_hrs_lookup.index else 0394 elif metric == 'MaxWeekCap':395 value = 55396 elif metric == 'METQS hrs':397 value = metqs_lookup.loc[site_code, dt_col] if site_code in metqs_lookup.index and dt_col in metqs_lookup.columns else ''398 elif metric == 'Flex Up from SET up to 55':399 if site_code in pc_lookup.index and get_daily_mmv(dt_col) > 0:400 fc_val = pc_lookup.loc[site_code, dt_col]401 delta = get_daily_mmv(dt_col) - fc_val402 # Get SETWEEKP from MET hrs lookup403 met_val = metqs_lookup.loc[site_code, dt_col] if site_code in metqs_lookup.index else 40404 #and dt_col in metqs_lookup.columns405 if met_val == '' or (isinstance(met_val, float) and pd.isna(met_val)):406 met_val = 40407 met_val = float(met_val)408 max_week_cap = 55409 # New formula: (MET hours / MaxWeekCap) * FC - FC410 max_flex = (max_week_cap / met_val) * fc_val-fc_val if met_val != 0 else 0411 value = max(0, min(delta, max_flex))412 else:413 value = ''414 elif metric == 'LaborShareMax':415 try:416 current_date = pd.to_datetime(f"{date_str}/2026", format='%m/%d/%Y')417 matching_rows = max_ls_df[418 (max_ls_df['site'] == site_code) &419 (max_ls_df['start_date'] == current_date)420 ]421 value = matching_rows['input_value'].sum()422 value = '' if value == 0 else value423 except:424 value = ''425 elif metric == 'LaborSharePlan':426 value = ls_lookup.loc[site_code, dt_col] if site_code in ls_lookup.index else ''427 elif metric == 'Rate':428 # ── CHANGED: pull directly from Rate tab (rate_lookup), same pattern as shw_hrs ──429 # Previously: Rate = FC Shipments Planned Cap / Show hours (calculated)430 # Now: Rate = direct lookup from test3.xlsx → Rate sheet, indexed by site431 value = rate_lookup.loc[site_code, dt_col] if site_code in rate_lookup.index else ''432 elif metric == 'Flex Up from LS gap from max':433 if site_code in pc_lookup.index and site_code in ls_lookup.index and get_daily_mmv(dt_col) > 0:434 fc_val = pc_lookup.loc[site_code, dt_col]435 delta = get_daily_mmv(dt_col) - fc_val436 try:437 current_date = pd.to_datetime(f"{date_str}/2026", format='%m/%d/%Y')438 matching_rows = max_ls_df[439 (max_ls_df['site'] == site_code) &440 (max_ls_df['start_date'] == current_date)441 ]442 ls_max = matching_rows['input_value'].sum()443 except:444 ls_max = 0445 ls_plan = ls_lookup.loc[site_code, dt_col]446 if site_code in show_lookup.index:447 show_val = show_lookup.loc[site_code, dt_col]448 rate = fc_val / show_val if show_val != 0 and show_val != '' and not pd.isna(show_val) else 0449 else:450 rate = 0451 ls_gap_flex = (ls_max - ls_plan) * rate452 value = max(0, min(delta, ls_gap_flex)) if ls_max > 0 else ''453 else:454 value = ''455 elif metric == 'RR':456 # ── NEW: RR lookup from processed RR data (daily_rr = Run Rate / 7) ──457 # rr_lookup is indexed by site, columns are pd.Timestamp dates458 value = rr_lookup.loc[site_code, dt_col] if site_code in rr_lookup.index and dt_col in rr_lookup.columns else ''459 elif metric == 'Hires':460 # ── NEW: direct lookup from Hires tab, same pattern as shw_hrs / show_lookup ──461 # =INDEX(Hires!..., MATCH(site,...,0), MATCH(date,...,0))462 value = hires_lookup.loc[site_code, dt_col] if site_code in hires_lookup.index else ''463 elif metric == 'Flex Up from Hires':464 if (site_code in pc_lookup.index and465 site_code in hires_lookup.index and466 site_code in rate_lookup.index and467 site_code in rr_lookup.index and468 get_daily_mmv(dt_col) > 0):469 470 fc_val = pc_lookup.loc[site_code, dt_col]471 delta = get_daily_mmv(dt_col) - fc_val472 rate = rate_lookup.loc[site_code, dt_col]473 474 # ── Determine the week start (Sunday) for this date ──475 dt_date = pd.to_datetime(dt_col)476 dow = dt_date.dayofweek # Monday=0, Sunday=6477 week_start = dt_date - pd.Timedelta(days=(dow + 1) % 7)478 week_end = week_start + pd.Timedelta(days=6)479 480 # ── Sum Hires across the whole week ──481 weekly_hires = 0482 for wc in hires_lookup.columns:483 try:484 wc_date = pd.to_datetime(wc)485 if week_start <= wc_date <= week_end:486 h = hires_lookup.loc[site_code, wc]487 if pd.notna(h) and h != '':488 weekly_hires += float(h)489 except:490 continue491 492 # ── Get weekly RR from raw data (not divided by 7) ──493 rr_val = 0494 dt_date = pd.to_datetime(dt_col)495 rr_match = rr_raw_df[496 (rr_raw_df['Site'] == site_code) &497 (rr_raw_df['Run Week'] <= dt_date) &498 (rr_raw_df['Run Week'] + pd.Timedelta(days=6) >= dt_date)499 ]500 if len(rr_match) > 0:501 rr_val = float(rr_match['Run Rate'].iloc[0])502 503 # Guard rate504 if rate == '' or (isinstance(rate, float) and pd.isna(rate)):505 rate = 0506 rate = float(rate)507 508 # ── DEBUG: uncomment to see values ── 509 #print(f"Site={site_code}, Date={dt_col}, RR={rr_val}, Weekly_Hires={weekly_hires}, Rate={rate}, Delta={delta}")510 511 # ── (Weekly_RR - Weekly_Hires) × Rate / 7 ──512 flex_hires = (rr_val - weekly_hires) * rate / 7513 value = max(0, min(delta, flex_hires))514 else:515 value = ''516 517 elif metric == 'VTO Hours':518 value = vto_lookup.loc[site_code, dt_col] if site_code in vto_lookup.index else ''519 elif metric == 'Flex Up from VTO Removal':520 if site_code in pc_lookup.index and site_code in ls_lookup.index and site_code in vto_lookup.index and get_daily_mmv(dt_col) > 0:521 fc_val = pc_lookup.loc[site_code, dt_col]522 delta = get_daily_mmv(dt_col) - fc_val523 vto_val = vto_lookup.loc[site_code, dt_col]524 ls_val = ls_lookup.loc[site_code, dt_col]525 rate = rate_lookup.loc[site_code, dt_col]526 # Guard against NaN / empty527 if vto_val == '' or (isinstance(vto_val, float) and pd.isna(vto_val)):528 vto_val = 0529 if rate == '' or (isinstance(rate, float) and pd.isna(rate)):530 rate = 0531 532 vto_val = float(vto_val)533 rate = float(rate)534 value = max(0, min(delta, vto_val * rate))535 else:536 value = ''537 elif metric == 'VET hours':538 value = vet_lookup.loc[site_code, dt_col] if site_code in vet_lookup.index else ''539 elif metric == 'Flex up from VET addition':540 if site_code in pc_lookup.index and site_code in show_lookup.index and site_code in vet_lookup.index and get_daily_mmv(dt_col) > 0:541 # Get SETWEEKP first — skip if 0542 setweekp = met_hrs_lookup.loc[site_code, dt_col] if site_code in met_hrs_lookup.index else 0543 if setweekp == '' or (isinstance(setweekp, float) and pd.isna(setweekp)):544 setweekp = 0545 setweekp = float(setweekp)546 547 #if 548 #setweekp == 0:549 #value = ''550 #else:551 fc_val = pc_lookup.loc[site_code, dt_col]552 delta = get_daily_mmv(dt_col) - fc_val553 vet_val = vet_lookup.loc[site_code, dt_col]554 show_val = show_lookup.loc[site_code, dt_col]555 556 if show_val != '' and not pd.isna(show_val):557 vet_ideal = 0 if setweekp > 0 else show_val * 0.1558 else:559 vet_ideal = 0560 561 if vet_val != '' and not pd.isna(vet_val):562 vet_flex_up = max(0, vet_ideal - vet_val)563 else:564 vet_flex_up = vet_ideal565 566 rate = rate_lookup.loc[site_code, dt_col]567 vet_capacity = vet_flex_up * rate568 value = max(0, min(delta, vet_capacity))569 570 else:571 value = ''572 elif metric == 'Show hours':573 value = show_lookup.loc[site_code, dt_col] if site_code in show_lookup.index else ''574 elif metric == 'VET Ideal hours':575 if site_code in show_lookup.index:576 show_val = show_lookup.loc[site_code, dt_col]577 setweekp = met_hrs_lookup.loc[site_code, dt_col] if site_code in met_hrs_lookup.index else 0578 if setweekp == '' or (isinstance(setweekp, float) and pd.isna(setweekp)):579 setweekp = 0580 setweekp = float(setweekp) 581 #setweekp = 40582 if show_val != '' and not pd.isna(show_val):583 value = 0 if setweekp > 0 else show_val * 0.1584 else:585 value = ''586 else:587 value = ''588 elif metric == 'VET Flex up':589 if site_code in vet_lookup.index and site_code in show_lookup.index:590 setweekp = met_hrs_lookup.loc[site_code, dt_col] if site_code in met_hrs_lookup.index else 0591 if setweekp == '' or (isinstance(setweekp, float) and pd.isna(setweekp)):592 setweekp = 0593 setweekp = float(setweekp)594 595 #if setweekp == 0:596 # value = ''597 #else:598 vet_val = vet_lookup.loc[site_code, dt_col]599 show_val = show_lookup.loc[site_code, dt_col]600 if show_val != '' and not pd.isna(show_val) and vet_val != '' and not pd.isna(vet_val):601 vet_ideal = 0 if setweekp > 0 else show_val * 0.1602 value = 0 if vet_val > vet_ideal else vet_ideal - vet_val603 else:604 value = ''605 else:606 value = ''607 else:608 value = ''609 610 except Exception as e:611 value = ''612 613 row[date_str] = value614 615 site_data.append(row)616 617 return pd.DataFrame(site_data)618# Temporary debug — remove after confirming619test_site = 'AUS3'620test_date = date_mapping.get('5/31')621print(f"=== DEBUG: Testing lookups for {test_site} on 3/8 ===")622print(f"met_hrs_lookup columns: {met_hrs_lookup.columns.tolist()[:10]}")623print(f"met_hrs value: {met_hrs_lookup.loc[test_site, test_date] if test_site in met_hrs_lookup.index else 'NOT FOUND'}")624print(f"PC value: {pc_lookup.loc[test_site, test_date] if test_site in pc_lookup.index else 'NOT FOUND'}")625print(f"Rate value: {rate_lookup.loc[test_site, test_date] if test_site in rate_lookup.index else 'NOT FOUND'}")626print(f"Hires value: {hires_lookup.loc[test_site, test_date] if test_site in hires_lookup.index else 'NOT FOUND'}")627print(f"Show value: {show_lookup.loc[test_site, test_date] if test_site in show_lookup.index else 'NOT FOUND'}")628# Generate for all sites629print(f"Processing {len(all_sites)} sites...")630all_sites_data = []631 632for i, site in enumerate(all_sites, 1):633 if i % 50 == 0:634 print(f" Processed {i}/{len(all_sites)} sites...")635 site_df = create_site_metrics(site)636 all_sites_data.append(site_df)637 638combined_df = pd.concat(all_sites_data, ignore_index=True)639 640print("" + "="*60)641print("CORRECTED DataFrame Created:")642print(f"Total sites: {len(all_sites)}")643print(f"Total rows: {len(combined_df)} ({len(metrics)} metrics × {len(all_sites)} sites)")644 645# Count non-empty values646non_empty_count = 0647for date in dates:648 non_empty_count += combined_df[date].astype(str).str.strip().ne('').sum()649 650print(f"✓ Total non-empty metric values: {non_empty_count:,}")651# Save to Excel652output_file = r"C:\Users\atulyam\Desktop\Flexup\demotest48.xlsx"653with pd.ExcelWriter(output_file, engine='openpyxl') as writer:654 combined_df.to_excel(writer, sheet_name='SiteLevel(2)', index=False)655 656print(f"✓ Excel file created: {output_file}")657print(f"✓ Contains {len(all_sites)} sites with {len(metrics)} metrics each")658print(f"✓ Total rows: {len(combined_df)}")659print(f"✓ All formulas have been replicated and calculated")660print(f"Network breakdown:")661print(combined_df.groupby('Network')['Site'].nunique())662print("="*60)663