from IPython.display import Image
Image('spreadsheet.png')

public static List<Cell> getAllCells() throws IOException {
    List<Cell> cells = new ArrayList<>();
    List<List<Object>> data = getValueRange().getValues();
    for(int rowIndex = 0; rowIndex < MAX_ROW; rowIndex++) {
        for(int columnIndex = 0; columnIndex < MAX_COLUMN; columnIndex++) {
            if(data.size() > rowIndex) {
                if(data.get(rowIndex).size() > columnIndex) {
                    String str = (String) data.get(rowIndex).get(columnIndex);
                    cells.add(new Cell(rowIndex, columnIndex, str));
                } else
                    cells.add(new Cell(rowIndex, columnIndex, ""));
            } else
                cells.add(new Cell(rowIndex, columnIndex, ""));
        }
    }
    return cells;
}

async def ss(self, ctx, server = "1"):
    gc = gspread.service_account(filename='utils/service_account.json')
    if server == "1":
        values = gc.open_by_key("1CrQOxzaXC6iSjwZwQvu6DNIYsCDg-uQ4x5UiaWLHzxg").worksheet("Upcoming Matches").get("c10:w59")
    else:
        values = gc.open_by_key("1CrQOxzaXC6iSjwZwQvu6DNIYsCDg-uQ4x5UiaWLHzxg").worksheet("Upcoming Matches (Server 2)").get("c10:w59")

df = pd.DataFrame.from_records(values)
row = df.loc[0] #get row with the dates
res = None
if os.name == "nt":
    res = row[row == (date.today().strftime("%#m/%d/%Y"))].index
else:
    res = row[row == (date.today().strftime("%-m/%d/%Y"))].index #find index of todays date, and use that index to start from

matches = []
for column in df.iloc[:, res[0] :].columns: #[:, start :] removes the first column. [rows, column]
                                        #remove time column
                                        # if we wanted to make SS past, we would change this to be df.iloc[:, 1:res[0]]
    aDay, aDate = df[column].iloc[1], df[column].iloc[0] #get day and date
    df1 = df.iloc[2:] #remove date/day rows
    day = df1[column] #we iterate through all the days

from IPython.display import Image
Image('day_example.png')

day1 = df1[day.astype(bool)].iloc[:, [0, column]] #Remove all cells which dont have a value in them, whilst also adding the time column to it in a new DF
day1 = day1.replace("^", None).ffill() # Then replace all "^" with a cell that doesnt have a value
                                        # So we can use fill forward, which changes 'Dunce ppm, ^ ^' to
                                        # 'Dunce ppm, dunce ppm, dunce ppm'. Ez grouping

first = day1.groupby([column]) # then get a DF from the changes we did ^

for key, item in first: #Maybe theres a way to do this without iterating at all

df2 = item.iloc[[0, -1]] #get the first and last items of the dataframe. This willgive the time it starts/ends
start_time = df2[0].iloc[0].split(" - ")[0] # get the first row (which gives us start time), get the time column and get time
end =df2[0].tail(1).index.item() # same process but for end

if end == 49: #special case for 11:30pm. we get the next time box which is the alternate method
    end2 = df[0][2] #get the 12am timebox
else:
    end2 = df[0][end+1] #otherwise get the next one along

end2 = end2.split(" - ")[0] #Date

start = parser.parse(" ".join([aDay, aDate, start_time]), tzinfos={"EST": "UTC-4"}) #add the day, date, and start time to get one datetime
end = parser.parse(" ".join([aDay, aDate, end2]), tzinfos={"EST": "UTC-4"}) #same for the end time

matches.append(Match(key, start, end)) # i made my own class but i dont think its useful, maybe someone else can shorten the code here
matches.sort() #since we made a class of matches, we can now decide how they are compared. Check the Match class, we compare by datetimes

class Match:
    def __init__(self, name, datetime, end):
        self.name = name
        self.datetime = datetime
        self.end = end

    def date(self):
        return f"{self.datetime.date()}"

    def start_time(self):
        if os.name == "nt":
            return self.datetime.strftime("%#I:%M%p")
        return self.datetime.strftime("%-I:%M%p")


    def end_time(self):
        if os.name == "nt":
            return self.end.strftime("%#I:%M%p")
        return self.end.strftime("%-I:%M%p")

    def __str__(self):
        if os.name == "nt":
            return f"**{self.name}**\n{self.datetime.strftime('%A')}, {self.datetime.strftime('%B')} {self.datetime.strftime('%#d')}\n{self.start_time()} - {self.end_time()} EST\n"
        return f"**{self.name}**\n{self.datetime.strftime('%A')}, {self.datetime.strftime('%B')} {self.datetime.strftime('%-d')}\n{self.start_time()} - {self.end_time()} EST\n"

    def __lt__(self, other):
        return self.datetime < other.datetime

if matches:
    return await success_embed(ctx, "\n".join(list(map(lambda x: str(x), matches[:7])))) #lambda
await success_embed(ctx, "No upcoming matches")

matches = []
days = df.iloc[0:2, res[0]:22] #if we wanted to make SS past, it would be here
df2 = df.iloc[2:, res[0]:22] #and here

days.iloc[0, :] = days.iloc[0, :] + " " + days.iloc[1, :] #combine days and dates into one row
days = days.iloc[0] #change dataframe into just that one row with days/dates

melted_df = pd.melt(df2) #"melt" all columns into one single column (one after another)
melted_df = melted_df.replace(to_replace=days.index, value=days) #replace numerical index of days to actual date strings

from IPython.display import Image
Image('melt.png')

time_column = pd.concat([df.iloc[2:, 0]]*len(df2.columns)).reset_index(drop=True) #repeat the times

melted_df.iloc[:, 0] = melted_df.iloc[:, 0] + " " + time_column
#then combine days+date with time,
#so our dataframe has columns of [Day, date, time], and [matchname]

melted_df = melted_df.replace(["", None], "#~#~#").replace("^", None).ffill()
# 'detect' all events. THIS INCLUDES TIMES WHERE THERES NO MATCHES!

grouped_df = melted_df.groupby([(melted_df.iloc[:, 1] != melted_df.iloc[:, 1].shift()).cumsum()])
# group by consecutive values

for group_index, group_df in grouped_df: #got it down to one iteration of just detected events
    #GROUP_INDEX/GROUP_DF REPRESENTS ALL THE GROUPS DETECTED IN THE SS! EVEN EMPTY EVENTS! (where nothing is happening)
    if group_df.iloc[0, 1] == "#~#~#": continue #SO WE REJECT THE EMPTY EVENTS

    match_df = group_df.iloc[[0, -1]]
    start_time = match_df.iloc[0][0].split(" - ")[0]
    index = match_df.index[1]+1
    if index == len(melted_df.index): #case for the last day, last time on SS
        index = match_df.index[1]
    end_time = melted_df.iloc[index][0].split(" - ")[0]
    name = match_df.iloc[1][1]

Context¶

Section 0 - How it was done before¶

Section 1 - First solution¶

Section 2 - The Rewrite¶

Section 3 - The Small... and the weird Issue?¶

Section 4 - The Final Touches¶

Section 5 Lore¶