DIMMiN

Created at: Oct. 30, 2025, noon Updated at: Oct. 22, 2025, 4:11 a.m.

2025-10-08-Wednesday

Found a way to accurately join the competition and lot tables (first on explicitly validated rows where a direct link was present, then on rank and farm_name)
Cleaned up competition and lot aggregation code (now there is no explicit script to aggregate this data together)
Now have a dataset with ~4.3k valid rows of different coffees
Started associating each coffee lot with its country and year
Identified a bug where some urls are associated with incorrect lot information in the output of the offline_pipeline_spider.py

Created at: Oct. 6, 2025, noon Updated at: Oct. 6, 2025, 3:19 a.m.

Added a check that a lot's url feature was in associated_lots before adding it as a feature via the offline_pipeline_spider
Validated that most pages with invalid associated lots actually did not have references to lots
Identified a bug where there was some duplicate information, combining data where possible to discover 5,317 unique coffee lots (validated with their individual urls) within the competition page
A direct Inner Join on between composite_lot_data.csv and score_df reveals 5,251 unique rows (indicating a much higher match rate than I saw initially)
Identified (and fixed) a bug with the price_per_unit Float converter code to account for , used to indicate cents.

Created at: Oct. 6, 2025, noon Updated at: Oct. 5, 2025, 4 a.m.

Found out that the price_per_unit is a good marker of the auction table within the competition page
Found an additional 2,000 lot associations for price_per_unit (now at ~4.8k total lots)
Changed the format of associated_lot to a list to eliminate redundant data
Started using the associated_lot feature to determine which tables on a given page have a direct link to lot-level data
Broke trackpad on Elvis so I couldn't use the mouse (this resolved itself on system reboot but was sp00ky).

Created at: Oct. 4, 2025, noon Updated at: Oct. 4, 2025, 2:31 a.m.

Normalized total_lot_value_usd and price_per_unit (thankfully the latter is always in \$/lbs which makes things easier)
Normalized auction_lot_size_kg (though this one needs some work, integer values here typically indicate a 30kg box of coffee instead of a raw weight of kg which itself is usually to two decimal places)
Identified that the most important feature for this analysis will be price_per_unit, therefore it makes the most sense to find features most relevant to this aspect.
Finished a rough draft of the processing for this dataset which currently provides 2,444 coffee lots with a known price_per_unit value. Given that we start with 5,482 unique coffee lots identifiable by their unique urls the biggest bottleneck I've identified right now is associating a coffee's lot with its respective auction results in the competition page.
Added an association between each competition and its respective lots in the coe_scraper/spiders/offline_pipeline_spider.py pipeline procedure. This should make it much easier to join competition page data to its associated lot.
Added a quick validation check within this pipeline to see if the links available in the competition page were also available in my local.

Created at: Oct. 3, 2025, noon Updated at: Oct. 3, 2025, 3:22 a.m.

Created at: Oct. 1, 2025, noon Updated at: Oct. 1, 2025, 4:50 a.m.

Finished applying the data validation hierarchy idea, now working with a composite dataset called composite_raw.csv that I can use to start building my ML-ready dataset
Normalized score, rank, year, country (certainly needs work), and farm_size_square_meters (the rows I checked seemed ok but it could also use a validation check)
Started extracting the different varietys from each row. Decided to stick with 12 "canonical" coffees since we don't want to explode to 70 different varieties when we apply One Hot Encodeing.
Tried multi-shot calling on ollama's API to see if a consensus could be reached about which coffee varieties were present for a given lot. Little success so far but the code is established for other rows if they need to be cleaned.

Created at: Sept. 30, 2025, noon Updated at: Sept. 30, 2025, 4:18 a.m.

Re-defined the goals of the data cleaning step for this analysis (mainly establishing competition pages as a backup and re-orienting my focus on the lot-level data)
Extracted relevant data from existing lot pages in parse_lot_data.py
Combined this relevant data with competition data based on my competition data parser, applying a Left Join on the lot's url field
Established the start of a data validation hierarchy where missing data is filled in using data that is known in a specific order (LLM consensus < auction_table < score_table << lot)

Created at: Sept. 30, 2025, noon Updated at: Sept. 29, 2025, 3:45 a.m.

Found a quick way to merge the most relevant auction and score data at the competition level (however it might be useful to keep in mind that this data will be supplementary to the data provided in the lot page)
Tried using ollama API to extract data from raw HTML of competition page in an attempt to side-step this whole data cleaning process. Didn't work.
Found that I'm missing some pages (like this one) on my local machine even though I grepped the 20210921_coe_spider.log logs and found that a GET request was successfully made. This points to a potential for additional data that needs to be collected.

Created at: Sept. 26, 2025, noon Updated at: Sept. 26, 2025, 4:47 a.m.

Developed a function to identify primary tables (tables that link between competition pages and lots)
Found out I was missing most of the lot competition data (A total of 240 lots) from 2020
Also found out that there are some competition pages that don't have any lot links (indicating that I don't actually have the data I need to use as input for those coffees)
Parsed the Wayback Machine for missing competition / lot data from 2020 and found some of it.
Found out that the Wayback Machine has a useful REST API. Tried working with Claude to build some Middleware for the coe Scrapy Spider so that 404 or unknown links are sent to the most recent snapshot of the HTML page from the Wayback Machine. If I can get this to work it could be a really useful little backup plan for any Web Scraping project.

Created at: Sept. 25, 2025, noon Updated at: Sept. 25, 2025, 4:08 a.m.

Found out how to call the local ollama API to run local models at scale using Leviathan
Fixed a corrupted competition HTML file for Guatemala 2013
Found that Brazil 2025 and Peru 2025 competition do not have any tables of data yet
Identified all unique feature names across my different competition tables, used Claude to create a mapping between the relevant features I need from the competition page and those from my JSON schema that I developed.