make_complete_dataframe_occupation.py

# For each accotype: The first number is the name of the acco, second number is the first row and third number is the last row
to_do_2022 = [
    [1, 4, 9],  # ALPHA
    [11, 14, 23],  # BRAVO
] 


def make_complete_df(columns_to_use, year):
    """Generate the dataframe
        Columns: ['acco_type', 'number', 'date',"month","year", "new_arrival","departure_no_clean", "departure_clean", "back_to_back", "yellow"])

    Args:
        columns_to_use (list with strings): which columns to scrape, eg. from "A to ... ZZ "

    Returns:
        df: dataframe
    """
    list_complete = []
    if year == 2022:
        to_do = to_do_2022
        sh = sh_2022

    for a in columns_to_use: # Generated in another function, is like ["A","B","C",..."EY", "EZ"]
       for t in to_do:
            acco_type = str(sh["a" + str(t[0])].value)
            ii = []
            for x in range(t[1], t[2] + 1):
                ii.append(a + str(x))
            bezet = 0
            number = t[2] - t[1] + 1
            departure_no_clean = 0
            departure_clean = 0
            departure_totaal = 0
            back_to_back = 0
            yellow = 0
            out_of_order = 0
            new_arrival = 0
            try:
                date = str(sh[a + "2"].value)

                date2 = datetime.strptime(date, "%Y-%m-%d %M:%H:%S")
                date3 = datetime.strftime(date2, "%Y-%m-%d")
                month = date2.month
                year = date2.year
            except:
                date3 = "STOP"

            if date3 != "STOP":
                for i in ii:
                    val = sh[i].fill.start_color.rgb
                    try:
                        valx = val[0]
                        valx = val
                    except:
                        valx = sh[i].fill.start_color.theme

                    if valx == "FFFF0000":  # rood
                        departure_no_clean += 1
                    elif valx == "FF7030A0":  # paars
                        departure_clean += 1
                    elif valx == 5:  # bruin
                        departure_totaal += 1
                    elif valx == 0 or valx == 6:  # zwart of grijs
                        out_of_order += 1
                    elif valx == "FF00B0F0":  # lichtblauw / cyaan
                        back_to_back += 1
                    elif valx == "FFFFFF00":  # yellow / bezet
                        yellow += 1
                    elif valx == 9:  # licht groen
                        new_arrival += 1

                row = [
                    acco_type,
                    number,
                    date3,
                    month,
                    year,
                    new_arrival,
                    departure_no_clean,
                    departure_clean,
                    back_to_back,
                    yellow,
                    out_of_order,
                ]
                list_complete.append(row)
    df = pd.DataFrame(
        list_complete,
        columns=[
            "acco_type",
            "number",
            "date",
            "month",
            "year",
            "new_arrival",
            "departure_no_clean",
            "departure_clean",
            "back_to_back",
            "yellow",
            "out_of_order",
        ],
    )
    df["in_house"] = df["yellow"] + df["new_arrival"] + df["back_to_back"]
    df["maand_str"] = df["month"].astype(str)
    df = df[(df["month"] >= start_month) & (df["month"] <= end_month)]
    
    df = df[df["acco_type"].isin(selection_list_accos)]
   
    return df