Dressage Data: Data Cleaning & Database Setup

It's Dressage Data Week! 

Remember when I created a database of my dressage scores and then did nothing with it for over a year? Me too, suddenly, out of the blue. And so I'm finally doing something with it after fourteen months. It's been a really good way to learn pandas (python library) and get more comfortable in VS Code (program to write code in) since I know the dataset inside and out given that I inputted every single piece of data myself. 

But before we get into any cool data stuff, I wanted to walk through the beginnings of the project and what it took to get to this point. This might be quite boring, just warning you. Tomorrow we'll dig into the results. 

This guy got two weeks off while I was gone! Little
did he know that the entire time, I was thinking about
him and running data analytics on his dressage scores

Setup and Data Cleaning

Data Entry
The first thing I did was to map out my database on my ipad. I started to input every single test, using google sheets to store it. I knew I wanted to be able to manipulate the hell out of it, so everything that could have warranted a table became a table. My tables: 

• Overall Results

• Primary Key: overallresultsid

• Foreign Keys: showid, showtestid, testid, horseid, judgeid

• This table had a single record for every test and became my main table

• Collective Scores

• Primary Key: overallresultsid

• Foreign Keys: showid, showtestid, testid

• This table could have been combined into overall results, but for some reason, I decided not to do that. 

• Individual Scores

• Primary Key: overallresultstestidmovement

• Foreign Key: overallresultsid, testid, testidmovement

• This is the insane table, every record is a single movement on a single test sheet that I rode with the comments

• Show

• Primary Key: showid

• Foreign Keys: facilityid

• This is the table for just the show itself, it has the start and end date as well as the show name

• Show Judge

• Primary Key: showid

• Foreign Keys: judgeid

• I decided to use a separate table to connect the judge with the show, there was a reason for this that I'm forgetting, but this table has the judge's rating in it

• Judge

• Primary Key: judgeid

• Foreign Keys: NA

• This table just has the judge's name, gender, and the year they were born (which I have very incomplete data for)

• Horses

• Primary Key: horseid

• Foreign Keys: NA

• Horse info, I have the horse's show name, barn name, year born, and breed in there

• Tests

• Primary Key: testid

• Foreign Keys: NA

• This table is one record per test with the test's year and total points as well as the level and test number

• Test Movements

• Primary Key: testidmovement

• Foreign Keys: testid

• This was another big table, I inputted every test movement into this table and categorized the movements into general movement categories

• Facilities

• Primary Key: facilityid

• Foreign Keys: NA

• This table just has facility info in it, at this point it's just zip code but later I want to have categories like number of arenas or maybe a spookiness rating to try to account for show facilities that may be a bit spooky!

Data entry took me a long time. I opted to do only rated tests but apparently I have received 102 tests (some were with two judges so not 102 rides) at rated shows from 2004 until 2020. That's insane. But I did this while I was on leave and was super burnt out. Highly recommend doing something like that while watching TV to make yourself feel like you're being productive without using your brain. 
After that I threw it into Tableau to play with and then decided that I didn't like Tableau that much, then started really focusing on my job hunt and ignored the data from then on. 


Initial Code/Database Setup

This honestly was the most annoying part of it and it's why I procrastinated on it for fourteen months. My first step was to download Python and VS Code, get them to stop ignoring each other, then install a whole bunch of extensions. One was SQLite, which allowed me to upload the data to a little local database. I also needed some python libraries, below all of my code is written using the pandas library. 

The data path points to where I put my sqlite database, I'm not going to go into that code because
it was insane and also not my code anyway

Data Cleaning

This was not that bad given that I was the person who did all the data entry and I knew I didn't want to do too much data cleaning. I just had some minor things to deal with, which was a great way to learn some very basic python/pandas. 

I ended up recreating the score column because the original data type wasn't particularly helpful. I didn't feel like dealing with explaining to pandas how to handle the percent sign, so I just made a new column. 

Score

I had a few instances where I called a movement with different capitalization (Leg yield vs Leg Yield and Half pass vs Half Pass) so I decided to put all of those into lower case.  

Helpful! It was easy to find all this code by just searching for the
pandas equivalent to the SQL code I already knew

I also inputted two scores wrong, one gave us a 114% and one gave us a 14% so that didn't quite balance out. Luckily I had taken pictures of the tests and was able to update those to normal scores. 

Whoops lol 

And finally, I did a lot of datetime extractions so that I could look at various times. 

Decided to see if I rode better at certain parts of the year, at
certain times, or even if I should hope for three or two digit
numbers (which I'm now realizing isn't a datetime field lol
oh well, I stuffed it there)

Function Creation

After the data was cleaned and ready to go, I started analyzing it. And stopped. And then started again, this time building a function. 

I wanted to just observe the data, come up with some good questions. And so I wanted to look at aggregates. 

My function for the descriptive stats

And finally, it was time to use this function to do some really basic descriptive statistics! 

More on these tomorrow!

Bridleless

Monday morning I headed down to the barn to get a ride in before heading to my offsite. I've been a little overwhelmed due to a variety of reasons lately, one of which is that my saddle isn't fitting me and the way I'm having to ride in it to avoid being harmed by it is now making TC back sore. So it's getting fixed and TC has been on a light schedule being ridden in the TCS and trail riding a lot. 

Not a coincidence that his left lead was best when
there wasn't a way for me to pull on the left rein lol

On Saturday, I rode TC with my neck rope. The TCS gives me no ability to stay with him when he surges, so I've been meaning to put my neck strap on. But that lives in my car and so I decided to use the neck rope instead.

He wanted to stretch down so I ended up dropping the reins and feeling like I could actually ride in the thing! I decided to work up to bridleless riding this fall. 

Sunday we went on the trails, but Monday I grabbed my pivo just in case we could get some fun video of us trying out our bridleless riding. I didn't know how far we'd get but thought it'd be fun!

He spent basically the entire time stretching at
the trot

Turns out we got really far! I started out by just tying my reins in a knot and having them right there for me to grab. But I never grabbed them, so I ended up removing his bridle and then trotting around. He stretched way down and seemed to enjoy it! 

After that, he started offering me everything he does at liberty! We work at liberty on occasion and he loves it, he plays with different behaviors and I shape the cue after clicking for the behavior a few times. We have funny games we play and I've been very slowly shaping a piaffe-like movement as well. 

He offered me turns on the forehand, rein back (which has been terrible with the bridle, interesting!), and jambette. 

After that, we picked up the canter for about a half circle in each direction! I was starting to run out of time at that point and decided to just let us be done there since we'd gotten way further than I thought we would on our first dedicated day of riding bridleless! 

Best boy, he had so much fun!

I'm so pleasantly surprised by how it went! It was such a blast and I felt super confident on him, even in only the TCS and after not a huge amount of work. 

Hopefully we get our saddle back soon so we can go back to normal work, but I think I definitely want to develop this stuff with him. It was a great challenge to be even clearer with my aids and intention and he thought it was great too. 

In addition, this is making me want to get him a side pull, even just to trail ride in. I've always wanted to have something where I had really good brakes but I think we're beyond needing that! We'll see where we end up going with all of this, either way, we're having fun. 

WW: Five Baby Cows!

So, for a long time, every spring, a herd of long horns come in to spend the summer across the road in the neighbor's pasture. Sometimes they have babies born! This year there were five babies!! Two solid brown, two brown and white, and one black and white. 

This one came right up to us!

When the little black and white one was born, he
scared me by laying very still for a long time!

So freaking cute

Being silly

Little babies

TC LOVES them

The solid brown ones seem to be so brave

Two of them look like this, they are mostly brown
with a white dorsal stripe type marking and a 
little white tail!

They left for their winter home last weekend. A couple of weeks ago, were told that they wouldn't be back, but then when the owners came to get them, they said that they would be back in March! One of the owners was COMPLETELY mesmerized by TC, who can feel that energy from a mile away and climbed right into his lap asking for attention, which he got. It was really sweet actually!

I really hope that they are coming back. They really add to the magic of the ranch and I just adore them so much. 

Heat Wave

Well, we are having a heat wave. Please take note that this post is not about me complaining about the heat. I will take heat ANY DAY over fires. This is not an invitation to the universe to cool down the area by putting smoke in between us and the sun. 

It started over the weekend and by Monday it was 112 degrees at the ranch. On Saturday, I managed to ride TC early enough to avoid the heat, we had a great ride. But then it got way too hot for the pony, so I ended up just doing hose downs and going home. 

He now begs to be sprayed with the hose on his
porch, the others are too dignified for that

Sunday I had my jumping lesson on Flounder, who got a nice cold full body hose down afterwards. Then I went and quickly rode both of my horses up on the hill. Just one single lap up there, which takes less than a half hour and is mostly shaded. Neither of them were super sweaty, but they both got a hose down (and so did Rico). Later that night, my parents hosed everyone down before the evening so they could try to feel a bit normal overnight. 

Monday was crazy. The high was 112. No one got ridden and they all just got hosed down along with the barn aisle and their porches to keep it cooler. 

Literally he will go over to whoever
is filling his water and sweetly beg

My plan originally had been to get up super early on Tuesday to ride at like 6am and 7:30am before it got too hot. But I ended up spending the day at urgent care and couldn't ride for a few days. I'm fine, as was apparent when I was on hour five of being at urgent care, but it was still a bummer. 

I did finally get to ride on Friday. I didn't get down there as early as I would have liked, but we got a stretchy ride in before it hit 95, so I consider that good. TC got a quick bath and then when I left, my mom demonstrated to me what my horse asks for when she fills his water up (the horse drinks SO MUCH that apparently they're filling his water twice a day now). 

And that's to be sprayed down on his porch. He is a very spoiled horse. 

So hilarious

Either way, I'm super tired of this heat! But I'll take several more weeks of it if it means we won't get any fires. If there's negotiation to be done, this is my request at least. 

The Return of Counter Canter

I'm really pleased with how TC is looking and feeling these days. He's finally getting to a place where I can really ride him in the more collected paces and have it feel somewhat easy for him. I think this is due to how much spinal integrity he's starting to maintain on his own. He's really learning to hold himself more together instead of relying on finding the outside edges of my aids and ping ponging off of those. 

Because of this, we've started to scratch the surface of moving back toward Second level! In our last lesson, we found a hole in the spinal integrity as he headed into canter, so I figured that I'd challenge him with some counter canter. 

For some reason I only clipped from left lead canter,
it was due to a time constraint, his right lead is better

It was really interesting. I went right first, so his harder lead but his easier direction, and it felt really weak. Like I could not ride it without feeling like I had to chase him a bit. That's fine, but nowhere near where I want it to be. 

The other direction though, well, I've never ridden a better counter canter. It felt unbelievably rideable, super uphill, and just downright easy for him! So that was fascinating. His left lead has always been the weaker one, but this is definitely a little more than usual. But I figure I'll give him a month or two of schooling some counter canter a couple days a week before I start using it as any sort of a diagnostic tool. He could also just be rather uncoordinated. 

I love trail rides on this horse

Beyond that, we also looked at some lengthened and shortened trot. TC has always had a lot of "pushing" power, but most of that power was actually just pulling power with his hind legs out behind him. We've been working hard on the carrying power, engaging his hind legs and lightening the forehand. But I also do need to access the *actual* pushing power rather than him always defaulting to pulling. 

I figure the best way to do this is to put him in his place of carrying, then ask him for a bigger gait, then put him back in his carrying place. It's a lot easier to go back to carrying if you don't completely change from rear wheel drive to front wheel drive. 

This was *very* hard for... me. I want to feel a big surge heading into a lengthening and that surge is only available by him pulling right now. So it was good for me to have to maintain a semblance of carrying in the lengthened trot. 

Best gray horse

But then that lead to some hovering on his part. Another thing that my body automatically rewards because it feels like passage. A hovering trot does not a passage make. A passage has both carrying and pushing power. The hovering is created by him just pulling himself with his neck and front feet and bouncing the hind legs along to create what feels like more suspension but what is actually a large heap of garbage. But like most people, it feels like passage so therefore I get a stupid grin on my face like wheee he's so bouncy and fancy. 

I've been there before though, so I've been working to let him know thank you, but I will not be requiring childlike joy from a hovering trot. I will ask you for more suspension later when you have the strength to do it properly. He's fine with that, just wanted to offer a shitty passage-like trot if I would like to have it. 

We have five baby cows by the way!
More in another post hopefully

All in all, it feels REALLY good to not have the harder work lead to him completely dropping through his thoracic sling and looking terrible again. His body is looking pretty good, not the way I want it to, but we're also struggling with how much food they're getting in pasture, so I think he was being a little underfed. He's getting more calories now so hopefully that helps us out in the topline department. 

He still looks way better than he did before, but now I've gotten greedy. My main goal though is for him to continue looking better. I don't mind if it takes a little while, but if he starts actually LOSING topline, that's when I need to stop and reevaluate. We aren't there yet, but I think this level of work and having enough to maintain weight but maybe not put on muscle (maybe? it's so hard to know when they're on pasture) can explain why he might be plateauing the tiniest bit. 

Seven Years with the Best Gray Horse

Yesterday, TC and I celebrated seven years of ownership! I can't believe I've had him this long. 

Looking very different from when I bought him

Over the last year, I feel like so much has changed with him. He's become unbelievably reliable in so many ways. I trust him like I've never trusted any other horse - I feel like I can read his mind and he can read mine. 

By the time I got to this amount of trust and connection with Rico (and honestly, I could argue that I never got this far with him), Rico was already heading towards retirement. TC is only eleven years old. I can't wait to see where our relationship ends up by the time TC is in his late teens. Rico and I may have gone further in dressage than TC and I may go, but I feel like I'm learning more from TC than I ever did from Rico in a way. Different things at least! 

Such a cool horse

This weekend has been really hot. So on our little anniversary, we just went on a trail ride and I hosed him down and gave him a cookie. Nothing special! But it was really nice to spend the day up in the hills with him. He really is the best trail horse I've ever had. 

What is Wrong with Rico?

Nothing is wrong with Rico, except that I keep disturbing his naps. 

Sorry Rico!

It all started when I saw Rico napping in pasture. TC doesn't seem to lay flat out and nap, and if he's on one end of the spectrum (he's never really wanted to lay down and nap, he seems to prefer standing), Rico is on the other. Rico is the nap KING. That horse will sleep like the dead for hours, and that's just the way he fucking looked when I saw him out there that one day. 

I took some deep breaths and told myself it was okay. Rico is coming up on his 27th birthday. I feel like 27 is getting to the point of non-horse people being like "oh wow, didn't realize horses lived that long" and so my brain has decided that he's officially a rather old horse. When my brain decides this, then I start to prepare for the inevitable and honestly, I've handled it okay when we've lost horses after 27 years old.

But I draw the line at seeing my horse laying dead in the field. I know that on his end, dying peacefully in his field is probably best, but I WOULD FREAK THE FUCK OUT IF I WAS THE ONE TO FIND HIM. 

TELL ME HE DOESN'T LOOK A
BIT... STILL... I am aware that this
is a photograph 

So backing up, I saw Rico napping in the pasture and he was like FLAT OUT. Again, this is not out of the ordinary for him. But for some reason, that looming age of him officially being a "rather old horse" and my brain being like "he's lived a great life, everything I get beyond this is just icing on the cake," I was pretty certain that this very normal behavior was abnormal. 

I walked out there and I saw him breathing and took a deep breath. But omg what if he wasn't going to be able to get up?! What if he was actively dying and I was just going to be there to witness it. I quietly said "hey buddy, hey Rico" and he was like "ugh what the fuck" and got up. He's not the world's most steady horse getting up anymore, but he was still perfectly healthy and mostly just annoyed that I woke him up from his nap. I apologized and gave him a hug. 

Paid well for his trouble

A couple of days later, I got to the barn and saw TC and Rico on their porches, TC was standing quietly with Rico, who was again laying flat out. I was like OH MY GOD IT'S HAPPENING. But then I saw him twitch a leg and calmed down. Then freaked out again because I was certain he was caught in the fence. 

This horse has literally napped on the porch or in his pasture the entire time he's lived at the ranch. But still, I rushed into his stall and was like "hey Rico" and he sat up, very clearly not caught in the fence or in ANY kind of distress. I tried to leave when it was aparent that he was once again stuper annoyed at me for disrupting his nap, but it was too late. He got up and was like "okay bitch, I'm up, I'm FINE, give me a cookie for my troubles," and so I did. 

I'm seriously so sorry, buddy!

Anyway, nothing is wrong with Rico, but there IS something wrong with me. I'm hoping that I stop freaking out so much about him napping. I do want to get him (and TC) a pile of sand for his sacrifice pasture to give him a nice soft place to lay and hopefully some more traction getting up. I also am considering doing an adequan boost as we come into winter to hopefully help him out joint-wise as the weather gets cooler. He's given me so much, I can't empty my bank account making him the soundest unrideable 27 year old horse there ever was, but I can do little things here and there to continue giving back to him.