Why I'm Spending My Weekend Cleaning CSVs
Phase 1: Building the Data Pipeline
What this phase is about
I'm building the foundation, the unglamorous but critical data pipeline that takes raw data and transforms it into something a machine learning model can actually use.
This is the part where you stare at a CSV file and realize "ticker" should probably be "company_id" and that ESG scores are somehow on different scales and, what even is a GICS sector?
How I approached it
I'm breaking this entire project into phases because I've tried the "build everything at once" approach before. It doesn't work. You end up with a tangled mess where you can't tell if your model is bad because of the algorithm or because you accidentally normalized something twice.
So Phase 1 is purely data work. Load it, validate it, transform it, split it. That's it.
I started by just loading the raw CSV and poking around. What do these columns mean? What ranges do the scores have? Are there missing values? The answer to that last one was yes, obviously yes.
Then I built modules because I'm treating this like actual software. A data_loader.py that handles loading and basic cleaning. A validation.py that checks if the data makes sense. A preprocessing.py that normalizes everything because "rituals".
The feature engineering part was weirdly fun. I was aiming for 15-20 features but ended up with 37. I built it in four layers, each asking a different question:
Internal relationships – How do the three ESG pillars relate within a company? I calculate ESG balance (standard deviation across scores – low means balanced, high means all over the place). I created ratios like Environmental/Social to see if companies prioritize one over another. I flag which pillar is strongest. A 40-point gap between environmental and social scores? That's interesting.
Competitive context – This is where GICS sectors come in. That's the Global Industry Classification Standard that MSCI and S&P Dow Jones created – 11 standardized industry categories so you can compare companies fairly. I calculate industry-relative scores because a 65 in oil & gas (where the median is 45) is totally different from a 65 in tech (where the median is 75). Raw scores lie. I also create percentile rankings within each industry. Being at the 90th percentile means you're beating 90% of your peers.
Consistency checks – Companies get both numeric scores and letter grades (AAA, AA, BBB, etc.). These should align, but they don't always. I check if a company's score suggests they should be AA but they're rated BBB. That misalignment is capturing something – maybe recent events, maybe data lag, maybe something the rating agency knows that the numbers don't show.
Summary patterns – Sometimes you need to zoom out. I average levels and grades across pillars to see overall trends. A company that's consistently "B" everywhere might be more stable than one that's "AAA" in governance and "CCC" in environmental.
I'm also documenting this in a blog because future me will forget why I made certain decisions. Also, maybe someone else is trying to learn this stuff too. The ML tutorials online skip over so much of the messy reality – they hand you a pre-cleaned dataset and say "now train a model!" But that's not how it works in practice.
What worked, what didn't
What worked:
- Modular design. Each piece does one thing. I can test them independently. I can reuse them. This feels right.
- Validation-first mindset. I'm checking data quality before I do anything else. Caught several issues early (duplicate entries, scores outside expected ranges).
- The industry-relative features feel like the strongest addition. ESG scores without industry context are almost meaningless.
- Stratified splitting by industry. The dataset has different numbers of companies per industry, so random splitting would be risky.
What didn't work:
- I initially tried to do feature engineering in one massive function. It was 200+ lines of chaos. Breaking it into four focused functions made everything clearer.
- Spent too long trying to map industries perfectly to GICS sectors. Some companies don't fit neatly. Eventually just made reasonable choices and moved on. Perfect is the enemy of done.
- My first pass at validation was too strict. It flagged things that were unusual but not wrong. Had to calibrate what "bad data" actually means versus what's just "surprising data."
Things I'm still unsure about:
- Did I normalize correctly? I'm treating everything as 0-100 but maybe some scores shouldn't be on the same scale?
- Are 37 features too many for 722 samples? I know the rule of thumb is you want way more samples than features, but I'm planning to use tree-based models which handle high dimensionality better.
- Should I have done more domain research on what makes a good ESG score? Right now I'm treating it as a pure ML problem, but maybe there's ESG-specific knowledge I'm missing.
What's next
Phase 2: Model Development & Training.
Now that I have clean, validated, feature-engineered data sitting in nice parquet files, I can actually start training models. I'm planning to try LightGBM and XGBoost – both gradient boosting frameworks that work well with structured data.
I need to:
- Set up a training pipeline with proper cross-validation
- Tune hyperparameters without overfitting (this is always the hard part)
- Figure out what metrics actually matter for this problem (MAE? RMSE? Something else?)
- Compare model performance on the validation set
- Try to understand why the model makes certain predictions (feature importance, SHAP values, etc.)
Let's see if all this data cleaning pays off.