Import libraries and dataset.¶

In [122]:
import pandas as pd
from datetime import datetime
import calendar

import warnings
warnings.filterwarnings('ignore')
In [123]:
vintage = pd.read_csv("vintage-metagame.csv", names=["rank","player","wins","losses","byes","arch","subarch","deck","details","date","event_type"])

Replace 'NA' values in 'byes' column with 0.¶

In [124]:
vintage.byes = vintage.byes.fillna(0)
vintage.byes = vintage.byes.astype("int")

Apply 'event_type' to each record.¶

In [125]:
vintage["event_type"].replace({"Showcase Qualifier": "Showcase_Qualifier"}, inplace=True)
In [126]:
event_type = vintage.event_type.tolist()
for index,i in enumerate(event_type):
    if isinstance(i, str):
        new = i
    else:
        event_type[index] = new
vintage["event_type"] = event_type

Create column for unique 'event_id'. Format 'event_date' string.¶

In [127]:
event_id = []
dates = vintage.date.tolist()
event_type = vintage.event_type.to_list()
dates_new = []
for index,i in enumerate(dates):
    month = i.split("/")[0].zfill(2)
    day = i.split("/")[1].zfill(2)
    year = i.split("/")[2]
    event_id.append(f"{year}-{month}-{day}-{event_type[index]}")
    dates_new.append(f"{year}-{month}-{day}")
vintage["event_id"] = event_id

Create Events table. Remove duplicate records such that each row represents a unique event.¶

In [128]:
events = pd.DataFrame({"event_id" : event_id, "event_type" : event_type, "date" : dates_new})
events = events.groupby(["event_id"], as_index=False)["event_type", "date"].last()

Add column 'entries' to Events table to represent number of players in each event.¶

In [129]:
players = vintage.groupby(["event_id"], as_index=False)["rank"].max()
In [130]:
events = events.merge(players, on="event_id")
events.rename(columns={"rank" : "entries", "date" : "event_date"}, inplace=True)

Add column 'day_of_week' to Events table.¶

In [131]:
events["day_of_week"] = events["event_date"].apply(lambda x: calendar.day_name[datetime.strptime(x, "%Y-%m-%d").weekday()])
In [132]:
events.head()
Out[132]:
event_id event_type event_date entries day_of_week
0 2021-01-02-Challenge Challenge 2021-01-02 72 Saturday
1 2021-01-03-Challenge Challenge 2021-01-03 59 Sunday
2 2021-01-09-Challenge Challenge 2021-01-09 93 Saturday
3 2021-01-10-Challenge Challenge 2021-01-10 63 Sunday
4 2021-01-16-Showcase Showcase 2021-01-16 152 Saturday

Drop 'details' column from Results table. Drop 'date' and 'event_type' columns because they are now in the Events table.¶

In [133]:
vintage.drop(["details"], axis=1, inplace=True)
vintage.drop(["date"], axis=1, inplace=True)
vintage.drop(["event_type"], axis=1, inplace=True)

Rename 'rank' column to 'finish' because 'rank' is a reserved word in MySQL.¶

In [134]:
vintage.rename(columns={"rank" : "finish"}, inplace=True)
In [135]:
vintage.head()
Out[135]:
finish player wins losses byes arch subarch deck event_id
0 1 discoverN 8 1 0 Combo Breach Tinker Breach 2022-04-24-Challenge
1 2 S063 6 3 0 Combo Breach Laelia Breach 2022-04-24-Challenge
2 3 _INF_ 5 3 0 Blue Tinker Other Blue Tinker Grixis Tinker 2022-04-24-Challenge
3 4 kasa 5 3 0 Bazaar Dredge Dredge 2022-04-24-Challenge
4 5 boulderelf 5 2 0 Combo Breach Grixis Leilia Breach 2022-04-24-Challenge

Replace commas because it breaks CSV importing with MySQL.¶

In [136]:
vintage["arch"] = vintage["arch"].apply(lambda x: str(x).replace(",", ""))
vintage["subarch"] = vintage["subarch"].apply(lambda x: str(x).replace(",", ""))
vintage["deck"] = vintage["deck"].apply(lambda x: str(x).replace(",", ""))

Save and export 'vintage-results' and 'vintage-events' tables to CSV.¶

In [137]:
vintage.to_csv("vintage-results.csv", index=False)
In [138]:
events.to_csv("vintage-events.csv", index=False)