I made a thing! First usable results from my database

[NoOne]

Originally Posted by cavebutter

Yeah, all of the depth chart stuff is in excel using csv output generated by the dB.

The find-a-batter and pitcher are stored procedures accessed directly from the dB.

If you know anything about MySQL server on Mac, I’d love to trouble-shoot it with someone. By default, Mac doesn’t create a my.cnf for MySQL and I need to create one to be able to write to a file from the server. To complicate matters, the server is running, but can’t be restarted or stopped. Something about a missing pid file. Seriously, if you know anything about this, I’d like to pick your brain.


Sent from my iPhone using Tapatalk

i can count how many times i've used a mac on one hand.. maybe 2.

https://stackoverflow.com/questions/...-file-on-macos

read all the comments, it covers more than just the highlighted answer at the top.. couple places to look. command to make sure it's even being used etc. potentially still "not there"

edit: show hidden comments, looks liek there's an answer for that too..

create ../,,/../my.cnf -- looks liek os version will matter where ??

mysql --verbose --help | grep my.cnf -- will tell you if it is even being used after creation

when you google search be as simple and direct as possible.. more is not better : "mysql mac ini file" all i used. "cnf" could have been good too. i shoudl not have included "file"

maybe more relevant to a newer system?

https://dev.mysql.com/doc/refman/5.7...ion-notes.html

if that's not your mysql version, type that into google search too and find the official document instead of stack overflow forum users etc... way more reliable

[torpidbeaver]

This is amazing. I’ve been beating the data visualization drum for a couple years now, but what you made here is amazing!

I’ve been trying to figure out a way to track players’ scouted ratings on excel and google Sheets but hit a wall with the problem of having hundreds or even thousands of players, each with multiple attributes, and each player/attribute further complicated by multiple years. It’s like I need a z-axis too, or something.

I came to a point where I could either make a new sheet for each player and have the X/Y be attributes/years (horribly inefficient, required more work cutting and pasting than I was willing to do), or I could go down the Pivot Table rabbit hole (tried but couldn’t get it to work).

I think the answer is somewhere in the pivot table realm. If anyone has some suggestions or advice (or even better, a working file with equations and visualization built in), I’d just be so delighted for the help.

[cavebutter]

Originally Posted by torpidbeaver

I’ve been trying to figure out a way to track players’ scouted ratings on excel and google Sheets but hit a wall with the problem of having hundreds or even thousands of players, each with multiple attributes, and each player/attribute further complicated by multiple years. It’s like I need a z-axis too, or something.

I came to a point where I could either make a new sheet for each player and have the X/Y be attributes/years (horribly inefficient, required more work cutting and pasting than I was willing to do), or I could go down the Pivot Table rabbit hole (tried but couldn’t get it to work).

This one's tricky because I have not been able to find ratings history in any of the dump files. The only way I can think to do this from the dump files would be to create a new ratings history table and set a trigger to update it with new rows for each player every time you import them. The problem is that there's no date or year associated with the ratings. I suppose you could hard code a date along with it.

What kind of vis would you want to see with this? There's already something similar in 19. Either way, I will add this to my backlog as I haven't played around with triggers yet and sounds like fun.

[stealofhome]

The only way I've thought of getting year is if you do yearly dump, the year is placed on the name of the folder. Then you can relate the year of the folder with the data and use that to track.

[cavebutter]

Originally Posted by stealofhome

The only way I've thought of getting year is if you do yearly dump, the year is placed on the name of the folder. Then you can relate the year of the folder with the data and use that to track.

Yeah, tedious to do in Excel though.

[stealofhome]

There must be a script you can run through some language that would add the folder name to the file name. Then in excel you can lookup a certain location based on a file name that changes by year. I've done it before with OOTP data and used some mass file renaming software to do it. But there has to be a way to automate it.

[cavebutter]

In all my years of playing the game, I never got myself too worked up over splits. I'd glance at them occasionally and make adjustments, but I never dug in too deeply. My feeling is that with a few obvious exceptions, the better player should play regardless of handedness.

Also, while the game makes it easy to view stats vs left or right, there's no easy way to compare them. So here's the career slash line splits for all the batters on Baltimore's ML roster. The charts will pick up the first 5 players on the list. Since Excel can sort based on cell color, you can highlight the players you want compared and sort by that.

[cavebutter]

Baltimore's pitching staff is pretty short because the query is only pulling players who had ML stats for the previous year. I didn't expect this to limit the records so much, but I guess this staff is in transition.

These are career ML pitching splits for players on Baltimore's ML roster:

[cavebutter]

One last item before I put this thread to bed for now:

Global single season and career records. Such a simple idea that doesn't exist in game. What if I just wanted to see the most major league home runs in the world? Not just the US majors but the whole world? Instead of digging through each league's record page, I have a list of all major leagues combined.

No screenshot, but you get the idea.

[torpidbeaver]

Originally Posted by cavebutter

This one's tricky because I have not been able to find ratings history in any of the dump files. The only way I can think to do this from the dump files would be to create a new ratings history table and set a trigger to update it with new rows for each player every time you import them. The problem is that there's no date or year associated with the ratings. I suppose you could hard code a date along with it.

What kind of vis would you want to see with this? There's already something similar in 19. Either way, I will add this to my backlog as I haven't played around with triggers yet and sounds like fun.

So what I've been doing is going to the player list screen at the same time every year, telling the game to dump a set of ratings I've chosen, copy/pasting the long list it dumps into my browser, opening Excel to paste that info in, and adding a column for "Year" into which I type the year of the report.

The one I'd like to see would be like the one in FM. I'll find it again.

Well, I can't seem the find the one I grabbed, but here's another:



So you could click on a player, then click on a list of the attributes, and then a graph shows you how that player has improved (or really: how your scout thinks he has improved or declined) over the years/months.

My pivot tables are a disaster, though. There are these damned numbers on my columns that I can't figure out why they're there. (See attached picture). I was having the same problem with Excel. I just want the damn thing to list the number in the damn column, and NOT try to add it or average it or anything else. Grah. I get so frustrated with these programs, borne out of my ignorance and raised on a diet of constant failure and headache.

It looks much closer to what I want to see if, rather than "Columns," I list the attributes as "Values," but then the stupid thing will insist on making it the "SUM" of the listed values, or some other such function, and I can't figure out what the command is to "DO NOTHING" or "JUST LIST THE NUMBERS, CHUMP"

[MrDov]

I glanced through your blog and couldn't find an answer so sorry if I missed it there but how often do you update your database with new stats? And when you update are you editing existing tables or creating new tables every time?

[cavebutter]

Originally Posted by MrDov

I glanced through your blog and couldn't find an answer so sorry if I missed it there but how often do you update your database with new stats? And when you update are you editing existing tables or creating new tables every time?

The blog really just documented my learning process to code the stats. I got too lazy to write up how I am putting it all into action.

Right now, I am updating the db every month. I manually dump from the game and Hazel moves the csvs to a separate directory. I have a script that I run that truncates the tables (keeps the tables but deletes the records) and reloads all of the data and re-creates all of the supporting tables.

I'm working on a script that will just update existing tables (a trigger that will validate that the data is new and append rather than nuking it and starting over). But that gets tricky with current season stats where records are changed rather than added.

I've also moved on to teaching myself Python so that I can generate new csv's from the db that I can point Excel to. Excel for Mac doesn't have a handy plugin to connect to the db. That means that my Mac Excel workbooks need to have depth-charts exported to csv. That's the biggest pain point right now. Once I solve that, I will update the db weekly.

[cavebutter]

Originally Posted by MrDov

I glanced through your blog and couldn't find an answer so sorry if I missed it there but how often do you update your database with new stats? And when you update are you editing existing tables or creating new tables every time?

Short update on this:

After some tinkering around trying to solve a different problem, I found a way to update the player career batting and pitching stats tables (by far the largest tables) without emptying them out and starting from scratch with every update. It still deletes and re-creates current year entries, but that's a huge improvement.

I also set up Hazel (on my Mac) to automatically update the database every time I dump data from OOTP.

One of these days, I might actually play the game a little bit!