temp.txt


          col.replace("_currently_infected", "")
          for col in df.columns
          if col.endswith("_currently_infected")
      }
      drugs = {
          col.replace("_currently_on_drug", "")
          for col in df.columns
          if col.endswith("_currently_on_drug")
      }
      return bacteria, drugs
  
  
  def _compute_resistance_stats(
      frame: pd.DataFrame,
      infected_col: str,
      positive_count_col: str,
  ) -> Optional[Tuple[float, float]]:
      required = {infected_col, positive_count_col}
      if frame.empty or any(col not in frame for col in required):
          return None
  
      infected_series = frame[infected_col].astype(float)
      positive_series = frame[positive_count_col].astype(float)
  
      mask = infected_series > 0
      if not mask.any():
          return (np.nan, 0.0)
  
      total_infected = float(infected_series[mask].sum())
      if total_infected <= 0:
          return (np.nan, 0.0)
  
      total_positive = float(positive_series[mask].sum())
      prevalence = total_positive / total_infected
      percent = float(np.clip(prevalence, 0.0, 1.0) * 100.0)
      return (percent, total_infected)
  
  
  def _compute_average_resistant_stats(
      frame: pd.DataFrame,
      sum_any_col: str,
      positive_count_col: str,
  ) -> Optional[Tuple[float, float, bool]]:
      if frame.empty or sum_any_col not in frame:
          return None
  
      sum_any_series = frame[sum_any_col].astype(float)
      positive_series = (
          frame[positive_count_col].astype(float)
          if positive_count_col in frame
          else pd.Series(0.0, index=sum_any_series.index)
      )
  
      sum_values = sum_any_series.to_numpy(dtype=float)
      positive_values = positive_series.to_numpy(dtype=float)
  
      # Use reported positive counts when available; fall back to the summed 
any_r values
      # so that ratios remain within [0, 1] even if the count columns are zero 
or missing.
      denominators = np.where(positive_values > 0.0, positive_values, 0.0)
      fallback_mask = (denominators <= 0.0) & (sum_values > 0.0)
      fallback_used = bool(fallback_mask.any())
      if fallback_used:
          denominators = denominators.copy()
          denominators[fallback_mask] = sum_values[fallback_mask]
  
      valid_mask = denominators > 0.0
      if not np.any(valid_mask):
          return (np.nan, 0.0, fallback_used)
  
      total_any = float(sum_values[valid_mask].sum())
      total_denominator = float(denominators[valid_mask].sum())
      if total_denominator <= 0.0:
          return (np.nan, 0.0, fallback_used)
  
      mean_resistant = total_any / total_denominator
      mean_resistant = float(np.clip(mean_resistant, 0.0, 1.0))
      percent = mean_resistant * 100.0
      return (percent, total_denominator, fallback_used)
  
  
  def _compute_microbiome_stats(
      frame: pd.DataFrame,
      presence_col: str,
      resistant_col: str,
  ) -> Optional[Tuple[float, float]]:
      if frame.empty or presence_col not in frame or resistant_col not in 
frame:
          return None
  
      presence_series = frame[presence_col]
      total_presence = float(presence_series.sum(skipna=True))
      if total_presence <= 0:
          return (np.nan, 0.0)
  
      resistant_series = frame[resistant_col]
      total_resistant = float(resistant_series.sum(skipna=True))
      share = total_resistant / total_presence
      percent = float(share * 100.0)
      return (percent, total_presence)
  
  
> def _calculate_resistance_table(
      df: pd.DataFrame,
      year_df: pd.DataFrame,
      expanded_df: pd.DataFrame,
      resistance_targets: pd.DataFrame,
      *,
      average_targets: Optional[pd.DataFrame] = None,
      microbiome_targets: Optional[pd.DataFrame] = None,
      window_label: Optional[str] = None,
      expanded_label: Optional[str] = None,
      low_sample_threshold: float = 50.0,
  ) -> pd.DataFrame:
      columns = [
          "Bacteria",
          "Drug",
          RESISTANCE_SIM_COL,
          RESISTANCE_TARGET_COL,
          RESISTANCE_DELTA_COL,
          "Average resistant simulation",
          "Average resistant target",
          "Average resistant delta",
          "Microbiome simulation",
          "Microbiome target",
          "Microbiome delta",
          "Infection resistance simulation source: Community (%)",
          "Infection resistance simulation source: HGT (%)",
          "Infection resistance simulation source: Microbiome (%)",
          "Infection resistance simulation source: De Novo (%)",
          "Microbiome HGT Events (Asymptomatic)",
          "Infected person-days",
          "Resistant person-days",
          "Microbiome carrier-days",
          "Note",
      ]
      if resistance_targets is None or resistance_targets.empty:
          resistance_targets = pd.DataFrame(columns=["Bacteria", "drug", 
"target", "reason", "bacteria_slug", "drug_slug"])
  
      if average_targets is None or average_targets.empty:
          average_targets = pd.DataFrame(columns=["Bacteria", "drug", 
"target", "reason", "bacteria_slug", "drug_slug"])
  
      if microbiome_targets is None or microbiome_targets.empty:
          microbiome_targets = pd.DataFrame(columns=["Bacteria", "drug", 
"target", "reason", "bacteria_slug", "drug_slug"])
  
      if resistance_targets.empty and average_targets.empty and 
microbiome_targets.empty:
          return pd.DataFrame(columns=columns)
  
      bacteria_set, drug_set = _extract_bacteria_and_drugs(df)
  
      combo_display: Dict[Tuple[str, str], Tuple[str, str]] = {}
      prevalence_lookup: Dict[Tuple[str, str], Tuple[Optional[float], str]] = 
{}
      average_lookup: Dict[Tuple[str, str], Optional[float]] = {}
      microbiome_lookup: Dict[Tuple[str, str], Optional[float]] = {}
  
      for _, row in resistance_targets.iterrows():
          key = (row["bacteria_slug"], row["drug_slug"])
          if key not in combo_display:
              combo_display[key] = (row.get("Bacteria", key[0]), 
row.get("drug", key[1]))
          prevalence_lookup[key] = (row.get("target"), str(row.get("reason") 
or ""))
  
      for _, row in average_targets.iterrows():
          key = (row["bacteria_slug"], row["drug_slug"])
          if key not in combo_display:
              combo_display[key] = (row.get("Bacteria", key[0]), 
row.get("drug", key[1]))
          average_lookup[key] = row.get("target")
  
      for _, row in microbiome_targets.iterrows():
          key = (row["bacteria_slug"], row["drug_slug"])
          if key not in combo_display:
              combo_display[key] = (row.get("Bacteria", key[0]), 
row.get("drug", key[1]))
          microbiome_lookup[key] = row.get("target")
  
      combo_keys: Set[Tuple[str, str]] = set(combo_display.keys()) | 
set(prevalence_lookup.keys()) | set(average_lookup.keys()) | 
set(microbiome_lookup.keys())
  
      def _sort_key(item: Tuple[str, str]) -> Tuple[str, str]:
          display = combo_display.get(item, item)
          return (str(display[0]).lower(), str(display[1]).lower())
  
      records = []
      for b_slug, d_slug in sorted(combo_keys, key=_sort_key):
          bacteria_name, drug_name = combo_display.get((b_slug, d_slug), 
(b_slug.replace("_", " "), d_slug.replace("_", " ")))
  
          note_parts = []
  
          prevalence_target_raw, prevalence_reason = 
prevalence_lookup.get((b_slug, d_slug), (np.nan, ""))
          if prevalence_reason:
              note_parts.append(prevalence_reason)
          prevalence_target = (
              float(prevalence_target_raw * 100.0)
              if prevalence_target_raw is not None and not 
pd.isna(prevalence_target_raw)
              else np.nan
          )
  
          average_target_raw = average_lookup.get((b_slug, d_slug))
          average_target = (
              float(average_target_raw * 100.0)
              if average_target_raw is not None and not 
pd.isna(average_target_raw)
              else np.nan
          )
  
          microbiome_target_raw = microbiome_lookup.get((b_slug, d_slug))
          microbiome_target = (
              float(microbiome_target_raw * 100.0)
              if microbiome_target_raw is not None and not 
pd.isna(microbiome_target_raw)
              else np.nan
          )
  
          if b_slug not in bacteria_set or d_slug not in drug_set:
              note_parts.append("not modelled in simulation")
              records.append({
                  "Bacteria": bacteria_name,
                  "Drug": drug_name,
                  RESISTANCE_SIM_COL: np.nan,
                  RESISTANCE_TARGET_COL: prevalence_target,
                  RESISTANCE_DELTA_COL: np.nan,
                  "Average resistant simulation": np.nan,
                  "Average resistant target": average_target,
                  "Average resistant delta": np.nan,
                  "Microbiome simulation": np.nan,
                  "Microbiome target": microbiome_target,
                  "Microbiome delta": np.nan,
                  "Infection resistance simulation source: Community (%)": 
np.nan,
                  "Infection resistance simulation source: HGT (%)": np.nan,
                  "Infection resistance simulation source: Microbiome (%)": 
np.nan,
                  "Infection resistance simulation source: De Novo (%)": 
np.nan,
                  "Microbiome HGT Events (Asymptomatic)": np.nan,
                  "Infected person-days": np.nan,
                  "Resistant person-days": np.nan,
                  "Microbiome carrier-days": np.nan,
                  "Note": "; ".join(note_parts) if note_parts else "",
              })
              continue
  
          infected_col = f"{b_slug}_currently_infected"
          sum_any_r_col = f"{b_slug}_sum_any_r_{d_slug}"
          positive_col = f"{b_slug}_infected_with_any_r_positive_{d_slug}"
          microbiome_positive_col = f"{b_slug}_microbiome_r_positive_{d_slug}"
          presence_col = f"{b_slug}_presence_microbiome"
  
          required_cols = [infected_col, sum_any_r_col, positive_col]
          missing_cols = [col for col in required_cols if col not in 
year_df.columns]
          if missing_cols:
              note_parts.append("not modelled in simulation")
              records.append({
                  "Bacteria": bacteria_name,
                  "Drug": drug_name,
                  RESISTANCE_SIM_COL: np.nan,
                  RESISTANCE_TARGET_COL: prevalence_target,
                  RESISTANCE_DELTA_COL: np.nan,
                  "Average resistant simulation": np.nan,
                  "Average resistant target": average_target,
                  "Average resistant delta": np.nan,
                  "Microbiome simulation": np.nan,
                  "Microbiome target": microbiome_target,
                  "Microbiome delta": np.nan,
                  "Infection resistance simulation source: Community (%)": 
np.nan,
                  "Infection resistance simulation source: HGT (%)": np.nan,
                  "Infection resistance simulation source: Microbiome (%)": 
np.nan,
                  "Infection resistance simulation source: De Novo (%)": 
np.nan,
                  "Microbiome HGT Events (Asymptomatic)": np.nan,
                  "Infected person-days": np.nan,
                  "Resistant person-days": np.nan,
                  "Microbiome carrier-days": np.nan,
                  "Note": "; ".join(note_parts) if note_parts else "",
              })
              continue
  
          def compute_with_fallback(compute_fn):
              def _unpack(result: Optional[Tuple[object, ...]]) -> 
Tuple[float, float, bool]:
                  if result is None:
                      return (np.nan, 0.0, False)
                  if not isinstance(result, tuple):
                      raise TypeError("statistic function must return tuple or 
None")
                  if len(result) == 2:
                      return (float(result[0]), float(result[1]), False)
                  if len(result) >= 3:
                      return (float(result[0]), float(result[1]), 
bool(result[2]))
                  raise ValueError("unexpected statistics tuple shape")
  
              value = np.nan
              sample = 0.0
              used_expanded = False
              fallback_flag = False
  
              primary = compute_fn(year_df)
              primary_value, primary_sample, primary_fallback = 
_unpack(primary)
              if not np.isnan(primary_value):
                  value, sample = primary_value, primary_sample
              fallback_flag |= primary_fallback
  
              needs_expanded = (np.isnan(value) or sample < 
low_sample_threshold) and not expanded_df.empty
              if needs_expanded:
                  expanded = compute_fn(expanded_df)
                  expanded_value, expanded_sample, expanded_fallback = 
_unpack(expanded)
                  fallback_flag |= expanded_fallback
                  if not np.isnan(expanded_value) and (np.isnan(value) or 
expanded_sample > sample):
                      value, sample = expanded_value, expanded_sample
                      used_expanded = True
  
              return value, sample, used_expanded, fallback_flag
  
          (