import pandas as pd

path = r'D:\Evolução categorias\temp_bd2.xlsx'
df_raw = pd.read_excel(path, sheet_name='Planilha1', header=None)

raw_dates = df_raw.iloc[0, 1:].tolist()
valid_dates = []
for i, d in enumerate(raw_dates):
    try:
        ts = pd.Timestamp(d)
        if pd.notna(ts):
            valid_dates.append((i + 1, ts))
    except Exception:
        pass

valid_cols = [c for c, d in valid_dates]
date_labels_all = [d.strftime('%b/%Y') for c, d in valid_dates]

data_raw = {}
for row_idx in range(2, 17):
    row = df_raw.iloc[row_idx]
    cat = str(row[0]).strip()
    if pd.isna(row[0]) or cat in ('nan', '0', ''):
        continue
    values = []
    for c in valid_cols:
        v = row[c]
        values.append(float(v) if pd.notna(v) else 0.0)
    data_raw[cat] = values

n_cols = len(valid_cols)
print(f"Total colunas validas: {n_cols}")
print(f"Ultimos 10 meses: {date_labels_all[-10:]}")

# Check last 10 months
print("\nUltimos 10 meses - algum dado > 0?")
for i in range(n_cols-10, n_cols):
    lbl = date_labels_all[i]
    nonzero = [(cat, data_raw[cat][i]) for cat in data_raw if data_raw[cat][i] > 0]
    print(f"  {lbl}: {len(nonzero)} categorias com dados", nonzero[:2] if nonzero else '')

# Compute last_real
last_real = n_cols - 1
for i in range(n_cols - 1, -1, -1):
    if any(data_raw[cat][i] > 0 for cat in data_raw):
        last_real = i
        break

print(f"\nlast_real = {last_real} => {date_labels_all[last_real]}")

# first_real
first_real = 0
for i in range(n_cols):
    if any(data_raw[cat][i] > 0 for cat in data_raw):
        first_real = i
        break

print(f"first_real = {first_real} => {date_labels_all[first_real]}")
print(f"Total meses no dashboard: {last_real - first_real + 1}")
print(f"Periodo: {date_labels_all[first_real]} a {date_labels_all[last_real]}")