Stuff I learned Doing My Silly Tkinter Cornhole Project
Cornhole, the sport of midwestern American tailgaters, has arrived in Cambodia, kinda.
(If you don’t know what I’m talking about, meaning you’re in that 80% of my two-person readership [Haven’t gotten to stats yet in my Coursera classes], check out https://www.playcornhole.org/pages/rules for info about the glorious sport of cornhole.)
A bar I frequent now has some boards that are getting some use. They have wanted to start a cornhole league, based on the innings you throw, not based on the games you win.
So what I decided to do a couple of nights ago (don’t make these types of decisions at night) was to create a basic graphic user interface (GUI) for them to record innings. Given a scoresheet with a record of innings thrown (includes name, cornholes thrown, woodies thrown, and total score), I wanted to make a basic interface, with which the league manager could enter scores with a few clicks. These scores would enter a database. Then, on the GUI, the league manager could bring up a few different pre-selected queries. At this stage, the actions I decided I wanted accessible from the GUI were “Full Report” (this dumped all the data into an Excel spreadsheet for the manager to do with what they wanted), 10 top scores of the month, and the average of top players’ best five rounds of the month (that was a challenge!). The last two were shown in the GUI.
So this post is not necessarily about my GUI. It’s pretty simple and anyone reading a blog about data science would laugh at it. And I’m fragile. (“Why don’t you put it on github?” Fine, bring up something else I don’t know how to do! Learning though [https://www.udemy.com/course/git-complete/learn/lecture/1438990#overview].) This post is about recording some of the things I learned in the process of making this project and what resources I found helpful. Once again, this is a post for my future reference, but if it helps you too, all the better.
(And standard warning applies. I’m a beginner at all this stuff. What I tell you I did, may not be the best way to do something, or even a correct way. If there are better ways to accomplish what I spent all night trying to do, please do tell in comments…)
I will give love to a bunch of websites that helped me. These are obviously not the only ones that answer the questions I’m asking. They just happened to be the ones I found, and that spoke to me…
Stuff I learned:
Tkinter, Everything:
I decided to make my GUI in Tkinter. Almost all of my limited Tkinter game comes from the fantastic videos by John Elder of Codemy.com. I have only been freerolling on his youtube videos, but I will probably will buy a membership to Codemy. Even though I really don’t need another learning platform in my life. Or do I. Anywho, his Tkinter series can be found at https://www.youtube.com/playlist?list=PLCC34OHNcOtoC6GglhF3ncJ5rLwQrLGnV.
SQL, Add a New Record:
I know I know that. I just wanted to get the details right. Looked up the grammar at https://www.w3schools.com/sql/sql_insert.asp.
Pandas/SQL, How to Make a Dataframe from a SQLite Database?:
There seems to be a bunch of ways. I went with “from_records()” via RaJa’s answer at https://stackoverflow.com/questions/36028759/how-to-open-and-convert-sqlite-database-to-pandas-dataframe. Why? I dunno. It worked though.
query = cur.execute( ''' SELECT name
,cornholes
,woodies
,total_score
FROM Innings
WHERE month = ?
ORDER BY total_score DESC, cornholes DESC,
name
LIMIT 10''', (highmonth.get(), ))
cols = ["Name", "Cornholes", "Woodies", "Total"]
showmonthhigh_df = pd.DataFrame.from_records(data =
query.fetchall(), columns = cols)Pandas, Is a Dataframe Empty?:
So, I was running the query that wanted the top 10 innings in a month. All good except for the months when nobody played. In those cases the result was “Empty dataframe…” and some crap no non-data person wants to read. So I needed to figure out how to know a dataframe was empty and have the response to a normal person one.
Of course this is pretty easy. I liked user “Zero’s” answer on Stack Overflow. Usually SO is beyond me, but https://stackoverflow.com/questions/19828822/how-to-check-whether-a-pandas-dataframe-is-empty helped. So I went with checking the dataframe’s index length because it timed as the fastest way…
if len(showmonthhigh_df.index) == 0:
reportspace = Label(root, text = "There were no innings played
in " + highmonth.get()+"!")
reportspace.grid(row=4, column=0)
else:
reportspace = Label(root, text =
reportspace.grid(row=4, column=0)Pandas, How to Print a Dataframe Without the Index:
So the dataframe made from the SQL query above would appear in the label called “reportspace” just fine… NO! NOT FINE! IT STILL HAD THAT ANNOYING INDEX STARTING WITH 0!
I wanted the index gone. Thanks to my new old friends at Geeks for Geeks for the help. https://www.geeksforgeeks.org/how-to-print-dataframe-in-python-without-index/ told me to…
df.to_string(index = False)Giving me…
if len(showmonthhigh_df.index) == 0:
reportspace = Label(root, text = "There were no innings played
in " + highmonth.get()+"!")
reportspace.grid(row=4, column=0)
else:
reportspace = Label(root, text =
showmonthhigh_df.to_string(index=False))
reportspace.grid(row=4, column=0)Blam! Index be gone!
SQL, SELECT the Top N Rows for Each Group:
So I have a table with multiple innings for a pile of names. I want to collect the total scores from the top five innings for each player and average them, returning the names and averages. For this problem, I started typing the code and then was like, “Nope. No clue how to do this.”
I searched all over the world (wide web) for an answer. I didn’t find anywhere that addressed the problem clearly and could solve it with SQL (or other) tools I already had in my small toolbox. The closest was an old article by Baron Schwarz (https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/).
In this article, he looked at an example table…
+--------+------------+-------+
| type | variety | price |
+--------+------------+-------+
| apple | gala | 2.79 |
| apple | fuji | 0.24 |
| apple | limbertwig | 2.87 |
| orange | valencia | 3.59 |
| orange | navel | 9.36 |
| pear | bradford | 6.05 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
| cherry | chelan | 6.33 |
+--------+------------+-------+and wanted to get the lowest two prices for each type. He went with…
select type, variety, price
from fruits
where (
select count(*) from fruits as f
where f.type = fruits.type and f.price <= fruits.price
) <= 2;Though, my head still doesn’t fully grasp the logic (I’m close), it was a pretty simple and elegant answer and one I could fit into my project.
Except it didn’t work. I was getting more than five results. I still like and believe in his answer (because my beliefs matter when it comes to coding), but it wasn’t for me. So I needed to go a different route.
Eventually, I figured out that I could start with just one player name at a time by looping through a player list that I already had. Then, with a query and a subquery I can get one player and their top-five average. Then, I can make a list of those! I solved the problem, kind of!
highfives = []
for name in name_list:
cur.execute( '''
SELECT name
,AVG(total_score)
FROM
(SELECT name
,total_score
FROM Innings
WHERE month = ? AND
name = ?
ORDER BY total_score DESC
LIMIT 5)
GROUP BY name
''', (highfivemonth.get(), name)
)
rec = cur.fetchone()
if rec is not None:
highfives.append(rec)Wow, how inelegant is that! Whatever. It worked. Well, it did eventually. Not immediately. Next issue…
SQL, “Sqlite3.interfaceerror: error binding parameter 0 – probably unsupported type.”:
Umm. What does this mean? It had something to go with my query and the value I was substituting in for “name.”
Pretty basic issue I eventually figured out with help, thanks to https://kodlogs.com/34887/sqlite3-interfaceerror-error-binding-parameter-0-probably-unsupported-type. Name needed to be a string! It was something else! So…
str(name) #instead of name… made everything wonderful!
It didn’t (my fault, not Kodlog’s).
Turning Tuples into Strings:
So to get the average of every players top five rounds, I needed to (maybe I didn’t, but with my limited knowledge I did) loop through all the players already in the database. Easy. I actually had that list made already.
name_list = []
for row in cur.execute('SELECT DISTINCT name FROM Innings'):
name_list.append(row)Now to run through the loop we made (and fixed) above and make a list of the names of those who played that month and their “top five” averages.
highfives = []
for name in name_list:
cur.execute( '''
SELECT name
,AVG(total_score)
FROM
(SELECT name
,total_score
FROM Innings
WHERE month = ? AND
name = ?
ORDER BY total_score DESC
LIMIT 5)
GROUP BY name
''', (highfivemonth.get(), str(name))
)
rec = cur.fetchone()
if rec is not None:
highfives.append(rec)(By the way, the “.get()” in “highfivemonth.get()” kept me up hours without solution. Never try to solve these types of problems at night. I figured out the issue in 10 minutes the next morning.)
The result of this query? Nothing. Nada. Empty.
Because, obviously, name_list was a list of tuples, one-item tuples, but tuples nontheless, not strings.
(Actually, though, what I was inserting into the query were strings. As per the correction I made above, I took the tuples and ‘str()’ed them. This made them strings. Strings that looked like tuples. So I guess when faced with a tuple that looked like…
Tuple = (Jared, )Trying to make it into a string did something like…
String(Tuple) = "(Jared, )"A string, but not the one I wanted.)
So when fed into the SQL, the SQL elf is like “Nope, nothing to see here.” Long story long, the list of tuples needs to be a list of strings instead. Thanks to kite.com for telling me to use “join. (Answer found at https://www.kite.com/python/answers/how-to-convert-a-tuple-to-a-string-in-python) Making my list now looks like…
name_list = []
for row in cur.execute('SELECT DISTINCT name FROM Innings'):
name_list.append(''.join(row))Now “name” in the query above was not only a string, but the string I wanted!
Pandas, Creating a Dataframe from a List of Tuples:
So each time I looped through my SQL code getting the average of each players top five rounds (or fewer) for the month, my fetchone() would always give me a tuple. So I ended up with a list of tuples. How do I make that into a dataframe? Um, duh, how do you not make it into a dataframe? My nerdy pals at Geeks For Geeks got my back. From https://www.geeksforgeeks.org/creating-a-pandas-dataframe-using-list-of-tuples/ it’s obviously something like…
# data in the form of list of tuples
data = [list of tuples]
# create DataFrame using data
df = pd.DataFrame(data, columns =[list of column names])
Computering is easy, why am I so dumb?Pandas, Sorting:
Ok, I know I should know this. I forgot. I am used to doing stuff more in SQL than Pandas. Anyway, I reminded myself how to sort dataframes by values at https://datatofish.com/sort-pandas-dataframe/.
df.sort_values(by=['Column'], inplace=True, ascending=False)
duhSo, yeah, learned a lot from this project. Thanks to everyone who makes their knowledge available to dumbasses like me!