[Python] My code don´t read the week 22 and week 28 in dataframe [closed]

For some reason, the code does not read the dataframe correctly; for example, in weeks 22 and 28, which have few data points, it ignores them and returns as if that data did not exist.

PRINCIPAL_FILE = Path(r"C:/Users/4163819/Desktop/Ksmart/AOIReport-YieldDataNEW.xlsx")
SHEET = "Ksmart"
OUT_MATRIX = Path(r"C:/Users/4163819/Desktop/Ksmart/Customer_Index_Last12Weeks.xlsx")

# ───── 1. LER e NORMALIZAR ───────────────────────────────────────
df = pd.read_excel(PRINCIPAL_FILE, sheet_name=SHEET, engine="openpyxl")
df["Day"] = pd.to_datetime(df["Day"], errors="coerce")
df = df.dropna(subset=["Day"])

for txt in ["Machine Name", "Job Name", "CUSTOMER"]:
df[txt] = df[txt].astype(str).str.strip()

# Converter colunas numéricas
num_cols = ["Total Component", "Pass Component", "False Call Component", "NG Component"]
for col in num_cols:
df[col] = (
df[col].astype(str)
.str.replace(",", ".", regex=False)
.str.strip()
.pipe(pd.to_numeric, errors="coerce")
.fillna(0)
)

# ───── 2. DEFINIR as 12 semanas mais recentes (base: Monday) ────
today = pd.Timestamp.today().normalize()
current_monday = today - pd.to_timedelta(today.weekday(), unit="d")
week_dates = pd.date_range(end=current_monday, periods=12, freq="W-MON")

# Criar coluna de segunda-feira ISO de cada linha
df["WeekDate"] = df["Day"].dt.to_period("W").apply(lambda p: p.start_time)

# ───── 3. CALCULAR índice por cliente/semana ─────────────────────
results = []

for monday in week_dates:
window_start = monday - pd.Timedelta(weeks=11)
window_df = df[df["WeekDate"].between(window_start, monday)]

clientes_semana = window_df.loc[window_df["WeekDate"] == monday, "CUSTOMER"].unique()

if len(clientes_semana) == 0:
# Sem produção nesta semana → cria coluna vazia
col_name = f"{monday.isocalendar().year}_W{str(monday.isocalendar().week).zfill(2)}"
empty = pd.DataFrame(columns=["CUSTOMER", col_name])
results.append(empty)
continue

current_clients_df = window_df[window_df["CUSTOMER"].isin(clientes_semana)]

agg = current_clients_df.groupby("CUSTOMER").agg(
Total=("Total Component", "sum"),
Pass=("Pass Component", "sum"),
FalseCall=("False Call Component", "sum"),
NG=("NG Component", "sum"),
).reset_index()

agg["Index"] = (
(agg["Pass"] + agg["FalseCall"] + agg["NG"]) /
agg["Total"].replace(0, pd.NA)
) * 1000

col_name = f"{monday.isocalendar().year}_W{str(monday.isocalendar().week).zfill(2)}"
agg = agg[["CUSTOMER", "Index"]].rename(columns={"Index": col_name})
results.append(agg)

# ───── 4. UNIR tudo em formato MATRIX ────────────────────────────
from functools import reduce
matrix = reduce(lambda left, right: pd.merge(left, right, on="CUSTOMER", how="outer"), results)
matrix = matrix.set_index("CUSTOMER")
matrix = matrix.sort_index()
matrix = matrix[sorted(matrix.columns)] # Ordenar colunas cronologicamente

# ───── 5. SALVAR EM EXCEL ─────────────────────────────────────────
matrix.to_excel(OUT_MATRIX, sheet_name="Customer_Week_Index")

Continue reading...